MySQL :: HeatWave User Guide :: 5.8.3.1 Exporting Query Results to CSV Files with SQL Syntax

HeatWave User Guide / ... / Exporting Query Results to CSV Files with SQL Syntax

5.8.3.1 Exporting 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.

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

SELECT ... INTO OUTFILE 
URL '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.

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 a 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/
```
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 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:

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.
Load this data into HeatWave as an aggregate table by using Auto Parallel Load.
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.

 Query OK, 9150 rows affected. 1 warning (1.0313 sec)
 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_aggin 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

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

PREV HOME UP NEXT