Documentation Home
HeatWave User Guide
Related Documentation Download this Manual
PDF (US Ltr) - 3.8Mb
PDF (A4) - 3.8Mb


HeatWave User Guide  /  ...  /  Export Query Results to ND-JSON Files with Non-JSON Syntax

4.6.5.1 Export 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.

This topic contains the following sections:

Syntax

Use the following SQL SELECT INTO syntax to export query results to ND-JSON files.

Press CTRL+C to copy
SELECT ... INTO OUTFILE {URL | URI} '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:

    • 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 create the following types of URIs:

      • OCIFS Syntax:

        Press CTRL+C to copy
        oci://bucket_name@namespace_name/object_path

        Example:

        Press CTRL+C to copy
        oci://bucket_1@tenant_1/data_files/
      • PAR URI Syntax. You can use one of the following formats:

        Press CTRL+C to copy
        https://namespace_name.objectstorage.region_name.oci.customer-oci.com/p/PAR_token/n/namespace_name/b/bucket_name/o/object_path
        Press CTRL+C to copy
        https://objectstorage.region_name.oraclecloud.com/p/PAR_token/n/namespace_name/b/bucket_name/o/object_path

        Example:

        Press CTRL+C to copy
        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:

        Press CTRL+C to copy
        https://namespace_name.objectstorage.region_name.oci.customer-oci.com/n/namespace_name/b/bucket_name/o/object_path
        Press CTRL+C to copy
        https://objectstorage.region_name.oraclecloud.com/n/namespace_name/b/bucket_name/o/object_path

        Example:

        Press CTRL+C to copy
        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:

      Press CTRL+C to copy
      https://objectstorage.region_name.oraclecloud.com/p/PAR_URI/n/namespace_name/b/bucket_name/o/", "prefix":"prefixes"

      Example:

      Press CTRL+C to copy
      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.

      Syntax:

      Press CTRL+C to copy
      https://objectstorage.region_name.oraclecloud.com/n/namespace_name/b/bucket_name/o/prefixes

      Example:

      Press CTRL+C to copy
      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:

      Press CTRL+C to copy
      s3://bucket_name/prefixes/

      Example:

      Press CTRL+C to copy
      s3://my-bucket/export-results/
  • LINES TERMINATED BY: Defines the line terminator characters that represent the end of a row. The default is "\n".

Examples

The following example exports the query results to an ND-JSON file. The file location is represented as an OCIFS URI. The URI keyword is supported as of MySQL 9.4.0.

Press CTRL+C to copy
SELECT c_varchar as a FROM datatypes_dw UNION ALL SELECT c_json as a FROM datatypes_dw INTO OUTFILE URI 'oci://bucket_1@tenancy1/data_files_1/' FORMAT json;

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

Press CTRL+C to copy
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;