5.8.4.2 Exporting Query Results to Parquet Files with JSON Syntax

This topic shows the different ways to export query results to Parquet files.

This topic contains the following sections:

• Before You Begin

• JSON Syntax

• Parameter Descriptions for PAR URL

• Parameter Descriptions for Resource Principal

• Example for PAR Files

• Examples for Resource Principal

• What's Next

Before You Begin

• Review the following:

  • Overview of Exporting Query Results

  • Requirements to Export Query Results

JSON Syntax

Use the following SELECT INTO JSON syntax to export the query results into a Parquet file.

SELECT ... INTO OUTFILE WITH PARAMETERS
'begin-object_PAR_JSON "file": 
  begin-array "par": "par_url" [, "prefix": "prefix_value" ] end-array 
  [,"dialect": begin-object dialect_option [, dialect_option]... end-object ]
end-object'

'begin-object_RP_JSON "file": 
   begin-array "bucket": "bucket_name" [, "prefix": "prefix_value" [, "namespace: "namespace_name" ] [,"region": "region_name" ] ] end-array 
   [,"dialect": begin-object dialect_option [, dialect_option]... end-object ]
end-object'
dialect_option:
  {
    {"format": "parquet" } 
    | {"compression": {"uncompressed" | "snappy" | "gzip" | "brotli" | "zstd" | "lz4"} }
  }

Parameter Descriptions for PAR URL

The following parameters are included in the JSON syntax to export the query results to the Object Storage location represented as a PAR URL.

• URL: You can use a PAR URL to access Object Storage. Learn more about Pre-Authenticated Request Requirements to export query results. To learn more about PARs, see Object Storage Pre-Authenticated Requests from the HeatWave on OCI Service Guide.

  For security reasons, the PAR URL is hidden in the log file. For more information and samples, see Redacted PAR URLs in Exported Log Files.

• format: The file format to export the query results. Export the query results as a CSV, Parquet, or JSON (As of MySQL 9.3.1) file. By default, the results are exported to a CSV file.

• compression: The file compression type for CSV format. The supported compression types are uncompressed, snappy, gzip, brotli, zstd and lz4 (as supported by the Lakehouse load).

Parameter Descriptions for Resource Principal

The following parameters are included in the JSON syntax to export the query results to the Object Storage location based on the assigned resource principal. Learn more about Resource Principal Requirements.

• format: The file format to export the query results. Export the query results as a CSV, Parquet, or JSON (As of MySQL 9.3.1) file. By default, the results are exported to a CSV file.

• bucket: The bucket name.

• prefix: The directory located within the bucket to store the export query results. This is an optional parameter.

• namespace: The tenancy namespace.

• region: The region that the tenancy is in.

• compression: he file compression type for CSV format. The supported compression types are uncompressed, snappy, gzip, brotli, zstd and lz4 (as supported by the Lakehouse load).

Example for PAR Files

The following example uses a specified PAR URL to export the query results as a Parquet file.

SELECT sum(l_quantity) from lineitem limit 1000
INTO OUTFILE WITH PARAMETERS
'{"file":[{
     "par":"https://objectstorage.region1.oraclecloud.com/p/PTn9XsVegDBbSz1v-FgDma4G_e_78fJjv1GK04zlSqD9975MPZydieUJAGUypxQb/n/tenancy1/b/usr-bucket/o/", "prefix":"summary/2025"}], 
     "dialect": 
    {"format": "parquet"
    }
}';

Examples for Resource Principal

The following example uses resource principals to export the query results as a parquet file. This sample uses the default values, dialect parameters are not required.

mysql>SELECT sum(l_quantity) from lineitem limit 1000
INTO OUTFILE WITH PARAMETERS
'{"file":[{"region": region1, 
           "namespace": org_namespace, 
           "bucket": usr-bucket,
           "prefix": export/summary/
           "dialect": {"format": "parquet"}
  
         }]
}';

The following example sets the compression to snappy.

mysql> SELECT sum(l_quantity) from lineitem limit 1000
INTO OUTFILE WITH PARAMETERS
'{"file":[{"region": "region1", 
           "namespace": "org_namespace", 
           "bucket": "usr-bucket",
           "prefix": "prefix1/prefix2/"
         }]
           "dialect": {"compression": "snappy", "format": "parquet"}
}';

What's Next

• Review the following:

  • Redacted PAR URLs in Exported Log Files

  • File Name Format for Exported Query Result Files