Documentation Home
HeatWave User Guide
Related Documentation
HeatWave Release Notes
Download this Manual
PDF (US Ltr) - 3.8Mb
PDF (A4) - 3.8Mb
HeatWave User Guide  /  ...  /  Export Query Results to CSV Files with SQL Syntax

4.6.3.1 Export Query Results to CSV Files with SQL Syntax

This topic shows the different ways to export query results as a CSV file with SQL syntax.

This topic contains the following sections:

Before You Begin
Syntax

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

SELECT ... INTO OUTFILE 
{URL | URI} 'uri'
[FORMAT csv]
[CHARACTER SET 'charset_name']
[HEADER {ON | OFF}]
[{FIELDS | COLUMNS} 
  [NULL AS 'null_char']
  [TERMINATED BY 'string'] 
  [[OPTIONALLY] ENCLOSED BY 'char']
  [ESCAPED BY 'char'] ...
]
[LINES [TERMINATED BY 'string'] [STARTING 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. As of MySQL 9.4.0, you can use the URI keyword. In 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.

  • HEADER: Specifies if the CSV file has a header row. The default is OFF.

  • NULL AS: Represents a string as a null value. The supported null values are "NULL" and "\N".

  • {COLUMNS | FIELDS} TERMINATED BY: Defines the single character used to enclose fields. The delimiter for CSV format is ",". To export query results to Object Storage, COLUMNS or FIELDS TERMINATED BY only supports single character values.

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

  • LINES STARTING BY: Defines the character(s) that represent the prefix of a line.

  • ESCAPED_BY: The escape character to ignore with a warning for export. The default is "\".

  • FIELDS ENCLOSED BY: The quotation marks to ignore with a warning for export. The default is "\"".

Examples

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

SELECT sum(l_quantity) from lineitem 
INTO OUTFILE URI 
'oci://bucket1@tenancy1/data_files_1/';

The following example uses default delimiters, and exports the query results to a CSV file. The file location is represented as Resource principals. 

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

The following example uses default delimiters, and exports the query results to a CSV file with a header row. The file location is represented as a PAR URL. 

SELECT sum(l_quantity) from lineitem 
INTO OUTFILE URL 
'https://objectstorage.region1.oraclecloud.com/p/PTn9XsVegDBbSz1v-FgDma4G_e_78fJjv1GK04zlSqD9975MPZydieUJAGUypxQbn/tenancy1/b/usr-bucket/o/", "prefix":summary/2024/Mar/'
FORMAT csv 
HEADER ON;
Example to Export the Query Results and Load the Data Using Auto Parallel Load

In the following example:

  1. Create an aggregate table from the TPC-H benchmark—LINEITEM, NATION, ORDERS, and CUSTOMER tables by running a query that sums up sales grouped by year and month using the tables.

  2. Load this data into MySQL HeatWave as an aggregate table by using Auto Parallel Load.

  3. Update this table with new data loaded to the same bucket for new aggregates by using the incremental load feature. See Refresh Data Using Incremental Load.

Therefore, instead of having queries compute results by scanning all the rows for a given range of dates, you can run queries against this aggregate table, saving both time and cost.

The following query computes the sum of quantity and price and groups it by year, month, and day for the year 1992. The tenancy, bucket and the folder to save the query results are provided in the URL parameter mentioned in the INTO OUTFILE clause. 

SELECT SUM(l.l_quantity) 
  sum_quantity,
  SUM(o.o_totalprice) total_price,
  n.n_name nation_name,
  YEAR(o.o_orderdate) year,
  MONTH(o.o_orderdate) month,
  MONTHNAME(o.o_orderdate) month_name,
  DAY(o.o_orderdate) day_number
FROM 
  lineitem l, 
  orders o, 
  nation n,
  customer c
WHERE 
  l.l_orderkey = o.o_orderkey
  and n.n_nationkey = c.c_nationkey
  and c.c_custkey = o.o_custkey
  and YEAR(o.o_orderdate) = '1992'
GROUP BY 
  YEAR(o.o_orderdate),
  nation_name,
  MONTH(o.o_orderdate),
  MONTHNAME(o.o_orderdate),
  DAY(o.o_orderdate)
INTO OUTFILE
  URL 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/mytenancy/b/usr-bucket/o/summary-layer/' 
  FORMAT csv HEADER ON;

After the query runs successfully, a destination folder with a unique system-generated prefix as the folder name is created, and the query results are stored in that folder. Refer to the sample success message. 

 Note Code (3877) : Query results will be written to object storage with prefix : 'summary-layer/29597afd-ab15-11ef-83fa-02001702ecc6'

You can use Auto Parallel Load to load query results (CSV files) into a new aggregate table named lineitem_agg in MySQL HeatWave. Auto Parallel Load automatically gets the schema of the CSV files and creates the necessary table in the specified database and loads all the data from the CSV files into the new aggregate table. 

 SET @input_list = '[{
  "db_name": "lakehouse",
  "tables": [
    "lineitem_agg",{
      "table_name": "lineitem_agg",
      "engine_attribute": {
        "dialect": {
          "format": "csv",  
          "field_delimiter": ",",  
          "has_header": true, 
          "record_delimiter": "\\n", 
          "is_strict_mode": false
        },
        "file":[{
          "region": "us-ashburn-1", 
          "namespace": "mytenancy", 
          "bucket": "usr-bucket",
          "prefix": "summary-layer/"
        }]
      }
    }
  ]
}]';

SET @options = JSON_OBJECT('mode', 'normal', 'refresh_external_tables', 'true');
CALL sys.heatwave_load(CAST(@input_list AS JSON), @options);

After the aggregate table loads successfully, you can use it to do the following.

What's Next

PREV   HOME   UP   NEXT
Related Documentation
HeatWave Release Notes
Download this Manual
PDF (US Ltr) - 3.8Mb
PDF (A4) - 3.8Mb