MySQL :: HeatWave User Guide :: 4.6.3.2 Export Query Results to CSV Files with JSON Syntax

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 CSV file.

SELECT ... INTO OUTFILE WITH PARAMETERS
'file_URI_JSON "file":
  begin-array ("uri": "OCIFS_URI") | ("uri": "PAR_URI") | ("uri": "NATIVE_URI"),
  [, "dialect": begin-object dialect_option [, dialect_option]... end-object ]
end-object'
  
'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_key_value:
  {
    {"format": "csv" } 
    | {"field_delimiter": ","} 
    | {"record_delimiter": "\\n"}
    | {"encoding": "utf8mb4"} 
    | {"escape_character": "\\"} 
    | {"quotation_marks": "\""}
    | {"null_value": "\\N"} 
    | {"has_header": {true | false} }
    | {"compression": {"uncompressed" | "snappy" | "gzip" | "brotli" | "zstd" | "lz4"} }
  }

Parameter Descriptions for URI

Supported as of MySQL 9.3.1. The following parameters are included in the JSON syntax to export the query results to the Object Storage location represented as a URI.

URI: 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 specify 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/

dialect: The dialect parameters assist you to organize the data in the exported CSV file.

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.

URI: 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 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.
dialect: The dialect parameters assist you to organize the data in the exported CSV file.

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.

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.
dialect: The dialect parameters assist you to organize the data in the exported CSV file.

Dialect Parameters for CSV Files

The dialect parameters help you to structure the export query results data in the exported CSV file. The dialect parameters are applicable when you export the query results using JSON syntax. These parameters are based on the dialect parameters required to create the external table.

You can use the following dialect parameters to export the query results to CSV 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.
has_header: Specify if the CSV file has a header row. The default is false.
null_value: Represents a string as a null value. The supported null values are "NULL" and "\\N".
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.
field_delimiter: Defines a single character to enclose fields. The delimiter for CSV format is ",". This parameter only supports single character values.
record_delimiter: Defines one or more characters to delimit records. The maximum field delimiter length is 64 characters. The default is "\\n".
encoding: Encoding is ignored with a warning for export. The default is utf8mb4.
escape_character: The escape character to ignore with a warning for export. The default is "\\".
quotation_marks: The quotation marks to ignore with a warning for export. The default is "\"".

Example for URI

The following example uses an OCIFS URI to export query results. It uses default delimiters and specifies a header row in the file.

SELECT * FROM example
INTO OUTFILE WITH PARAMETERS
'{"file":[{
     "uri":"oci://bucket_1@tenancy1/data_files_1/"}], 
     "dialect": 
               {"has_header": true, 
                "format": "csv"}
}';

Example for PAR URL

The following example uses a PAR URL to export query results. It uses default delimiters and specifies a header row in the file.

SELECT * FROM example
INTO OUTFILE WITH PARAMETERS
'{"file":[{
     "par":"https://objectstorage.region1.oraclecloud.com/p/1P4yKjnwXLEPh9Ol0Fbg2r81tvgJfh0XRcCcsr45ggh7uu_t_X302Ea-WdYffl5CL1ON/n/tenancy1/b/usr-bucket/o/", "prefix":"summary/2025"}], 
     "dialect": 
               {"has_header": true, 
                "format": "csv"}
}';

Examples for Resource Principal

The following example uses resource principals to export query results as a CSV file. No dialect parameters are defined, so default values are used.

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

The following example uses resource principals to export query results as a CSV file. A header row is defined in the file.

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": {"has_header": true, "format": "csv"}
}';

What's Next

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