4.6.4.1 Export Query Results to Parquet Files with SQL Syntax

Before You Begin

Review the following:
- Overview of Exporting Query Results
- Requirements to Export Query Results

Syntax

Use the following SQL SELECT INTO syntax to export query results to Parquet files.

SELECT ... INTO OUTFILE 
{URL | URI} 'uri'
[FORMAT parquet]
[COMPRESSION 'string']

Parameter Descriptions

The following parameters are included in the SQL syntax to export the query results.

URI: The Object Storage URL to store the export query results. You must specify a URL. As of MySQL 9.4.0, you can use the URI keyword. For earlier versions, you must use the URL keyword.
The URL includes the Object Storage location with the region name, the tenancy, bucket and the directory to store the export query results. You can use one of the following URL types:
- URI: Supported as of MySQL 9.3.1. The Object Storage location is represented as a URI link. The URI must be a prefix URI and end in an unencoded / character.
  You can create the following types of URIs:
  - OCIFS Syntax:
    oci://bucket_name@namespace_name/object_path
    Example:
    oci://bucket_1@tenant_1/data_files/
  - PAR URI Syntax. You can use one of the following formats:
    https://namespace_name.objectstorage.region_name.oci.customer-oci.com/p/PAR_token/n/namespace_name/b/bucket_name/o/object_path
    https://objectstorage.region_name.oraclecloud.com/p/PAR_token/n/namespace_name/b/bucket_name/o/object_path
    Example:
    https://tenant_1.objectstorage.us-ashburn-1.oci.customer-oci.com/p/.../n/tenant_1/b/bucket_1/o/data_files/
  - Native URI Syntax. You can use one of the following formats:
    https://namespace_name.objectstorage.region_name.oci.customer-oci.com/n/namespace_name/b/bucket_name/o/object_path
    https://objectstorage.region_name.oraclecloud.com/n/namespace_name/b/bucket_name/o/object_path
    Example:
    https://tenant_1.objectstorage.us-ashburn-1.oci.customer-oci.com/n/tenant_1/b/bucket_1/o/data_files/
- PAR URL: The Object Storage location is represented as a pre-authenticated link. Learn more about Pre-Authenticated Request Requirements to export query results. To learn more about PARs, see Object Storage Pre-Authenticated Requests in Oracle Cloud Infrastructure Documentation.
  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.
  Syntax:
```
https://objectstorage.region_name.oraclecloud.com/p/PAR_URI/n/namespace_name/b/bucket_name/o/", "prefix":"prefixes"
```
  Example:
```
https://objectstorage.region1.oraclecloud.com/p/PTn9XsVegDBbSz1v-FgDma4G_e_78fJjv1GK04zlSqD9975MPZydieUJAGUypxQb/n/tenancy1/b/usr-bucket/o/", "prefix":"summary/2025"
```
- Non-PAR URL with resource principal: The Object Storage location is represented as a bucket, namespace, and tenancy.
  Syntax:
```
https://objectstorage.region_name.oraclecloud.com/n/namespace_name/b/bucket_name/o/prefixes
```
  Example:
```
https://objectstorage.region1.oraclecloud.com/n/tenancy1/b/usr-bucket/o/summary/2024/Mar/
```
- AWS S3 URL: Available for MySQL HeatWave on AWS. The Object Storage location is represented as an AWS S3 URL. Learn more about MySQL HeatWave on AWS Requirements.
  Syntax:
```
s3://bucket_name/prefixes/
```
  Example:
```
s3://my-bucket/export-results/
```
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). By default, the COMPRESSION type is set to snappy.

Examples

The following example uses default delimiters, and exports the query results to a Parquet file with compression method SNAPPY. The file location is represented as an OCIFS URI. The URI keyword is supported as of MySQL 9.4.0.

mysql>SELECT sum(l_quantity) from lineitem 
INTO OUTFILE URI 
'oci://bucket_1@tenancy1/data_files_1/'
FORMAT parquet
COMPRESSION 'snappy';

The following example uses default delimiters, and exports the query results to a Parquet file with compression method SNAPPY. The file location is represented as resource principals.

mysql>SELECT sum(l_quantity) from lineitem 
INTO OUTFILE URL 
'https://objectstorage.region1.oraclecloud.com/n/tenancy1/b/usr-bucket/o/summary/2024/Mar/'
FORMAT parquet
COMPRESSION 'snappy';

What's Next

Review the following:
- Redacted PAR URLs in Exported Log Files
- File Name Format for Exported Query Result Files