This topic shows the different ways to export query results as a CSV file with JSON syntax.
This topic contains the following sections:
-
Review the following:
Use the following SELECT
INTO
JSON syntax to export the query results
into a CSV 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_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"} }
}
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 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.
dialect
: The dialect parameters assist you to organize the data in the exportedCSV
file.
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.
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 exportedCSV
file.
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 acsv
,parquet
, orjson
(As of MySQL 9.3.1) file. By default, the results are exported to acsv
file.has_header
: Specify if the CSV file has a header row. The default isfalse
.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 areuncompressed
,snappy
,gzip
,brotli
,zstd
andlz4
(as supported by the Lakehouse load). By default, thecompression
type is set tosnappy
.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 isutf8mb4
.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"\""
.
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"}
}';
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"}
}';
-
Review the following: