5.8.5.1 Exporting Query Results to ND-JSON Files with Non-JSON Syntax

The option to export query results as an ND-JSON file is available as of MySQL 9.3.1.

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 ND-JSON files.

SELECT ... INTO OUTFILE 
URL 'uri'
[FORMAT json]
[LINES TERMINATED BY '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.

  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:

  • 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 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.

    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 bucket, namespace and tenancy. Learn more about Resource Principal Requirements.

    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 HeatWave on AWS. The Object Storage location is represented as an AWS S3 URL. Learn more about HeatWave on AWS Requirements.

    Syntax:

    s3://bucket_name/prefixes/

    Example:

    s3://my-bucket/export-results/

• LINES TERMINATED BY: Defines the line terminator characters that represent the end of a row. The default is "\n".

Example

The following example exports the query results to an ND-JSON file. The file location is represented as a PAR URL.

SELECT c_varchar as a FROM datatypes_dw
       UNION ALL
       SELECT c_json as a FROM datatypes_dw 
INTO OUTFILE URL 
'https://objectstorage.region1.oraclecloud.com/p/PTn9XsVegDBbSz1v-FgDma4G_e_78fJjv1GK04zlSqD9975MPZydieUJfsdrypxQbn/tenancy1/b/usr-bucket/o/, "prefix": summary/2024/Mar/'
FORMAT json;

What's Next

• Review the following:

  • Redacted PAR URLs in Exported Log Files

  • File Name Format for Exported Query Result Files