Documentation Home
HeatWave User Guide
Related Documentation
HeatWave Release Notes
Download this Manual
PDF (US Ltr) - 2.1Mb
PDF (A4) - 2.1Mb
HeatWave User Guide  /  HeatWave Lakehouse  /  Exporting Query Results to Object Storage

5.8 Exporting Query Results to Object Storage

As of MySQL 9.1.1-u1 and later, you can export the query result set generated from queries running on HeatWave and Lakehouse to Object Storage, as csv, or parquet format files. After the results are exported successfully, the number of rows written is displayed to the user.

You can also move the historical data from HeatWave, periodically to the Object Storage, and hence reduce the billing cost.

After the query results are exported to the Object Storage, the export file prefix is added to Section 7.3.7, “The rpd_query_stats Table”. To view the export file prefix, execute the following command: 

mysql> select QUERY_ID,EXPORT_FILE_PREFIX from performance_schema.rpd_query_stats WHERE EXPORT_FILE_PREFIX is NOT NULL;

Pre-Requisites

Ensure that you have the following roles/privileges assigned, to export the query results:

  • A user has to have EXPORT_QUERY_RESULTSEXPORT_QUERY_RESULTS privilege to be able to export query results to object storage. A user with database admin rights can grant this privilege by executing the following MySQL command: 

    mysql> GRANT EXPORT_QUERY_RESULTS ON *.* to user;

    To check if you have the GRANT privileges to export query results, run the following MySQL command: 

    mysql> SHOW GRANTS;

  • Lakehouse Role to access the AWS DB system. This role is required even when Lakehouse is disabled.

Non-JSON Syntax For Exporting Query Results

Export query results from HeatWave and Lakehouse using the following non-JSON SELECT INTO syntax. 

SELECT INTO OUTFILE
URL uri
[FORMAT {CSV | PARQUET}]
[COMPRESSION string] 
[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'] ...]

The following example exports the query results to a csv file. It uses the following input values:

  • FORMAT - Default csv file

  • Directory - summary/2024/Mar/ directory

  • Region - valid region name

  • Tenancy - valid tenancy name

  • Bucket - aa-test

This sample uses the default delimiters. 

mysql> select sum(l_quantity) from lineitem 
into outfile URL 
'https://objectstorage.region_name.oraclecloud.com/n/tenancy_name/b/aa-test/o/summary/2024/Mar/' 
;

The following example exports the query results to a csv file with header row. It uses the following input values:

  • FORMAT - csv file

  • HEADER - ON

  • Directory - summary/2024/Mar/ directory

  • Region name - valid region name

  • Tenancy - valid tenancy name

  • Bucket - aa-test

This sample uses the default delimiters. 

mysql> select sum(l_quantity) from lineitem 
into outfile URL 
'https://objectstorage.region_name.oraclecloud.com/n/tenancy_name/b/aa-test/o/summary/2024/Mar/'FORMAT CSV 
HEADER ON;

The following example exports the query results to a parquet file with compression method SNAPPY. It uses the following input values:

  • Format - parquet file

  • COMPRESSION - snappy

  • Header - ON

  • Directory - summary/2024/Mar/ directory

  • Region name - valid region name

  • Tenancy - valid tenancy name

  • Bucket - aa-test

This sample uses the default delimiters. 

mysql> select sum(l_quantity) from lineitem 
into outfile URL 
'https://objectstorage.region_name.oraclecloud.com/n/tenancy_name/b/aa-test/o/summary/2024/Mar/'FORMAT parquet
COMPRESSION snappy;

JSON Syntax for Exporting Query Results Using a PAR Value

Export the query results by specifying the PAR value in the following extended SELECT INTO syntax. prefix is optional. 

SELECT ... INTO OUTFILE 
    WITH PARAMETERS
'{"file": [{“par": ..., "prefix": ..}],
  "dialect": 
  {
    "format": CSV,
    "field_delimiter": ",", 
    "record_delimiter": "\n", 
    "encoding": "utf8mb4", 
    "escape_character": "\\\\", 
    "quotation_marks": "\"",
    "null_value": "\\n", 
    "has_header": true | false,
    "compression": "snappy"
  }
 }'

PAR pattern ("*https?*/p/?*/n/?*/b/?*/o/*") is added to a list of colon-separated patterns in order for MySQL to ignore statements with PAR for logging purposes. Now by default, the pattern list is: "*IDENTIFIED*:*PASSWORD*:*https?*/p/?*/n/?*/b/?*/o/*"

The following example exports the query results as a csv file with the header row, using the specified PAR value. It uses the following input values:

  • Format - csv file

  • Header - True

  • Directory - summary/2025

  • Region name - valid region name

  • Tenancy - valid tenancy name

  • Bucket - aa-test

  • Prefix - summary/2025

This sample uses the default delimiters. 

mysql> select sum(l_quantity) from lineitem limit 1000
into outfile URL {"file":[{"par":"https://objectstorage.region_name.oraclecloud.com/p/PTn9XsVegDBbSz1v-FgDma4G_e_78fJjv1GK04zlSqD9975MPZydieUJAGUypxQb/n/tenancy_name/b/aa-test/o/"
, "prefix":"summary/2025"}]
, "dialect": 
	{"has_header": true, 
	 "format": "csv"
}}' ;





'https://objectstorage..oraclecloud.com/n/tenancy_name/b/bucket_name/o/summary/2024/Mar/'FORMAT CSV 
HEADER ON;

JSON Syntax for Exporting Using Resource Principals

Export query results by specifying the resource prinicipals in the following SELECT INTO syntax. 

SELECT ... INTO OUTFILE 
    WITH PARAMETERS
'{"file": [{"bucket": ..., "prefix": .., "namespace: :.., "region":..}],
  "dialect": 
  {
    "format": CSV,
    "field_delimiter": ",", 
    "record_delimiter": "\n", 
    "encoding": "utf8mb4", 
    "escape_character": "\\\\", 
    "quotation_marks": "\"",
    "null_value": "\\n", 
    "has_header": true | false,
    "compression": "snappy"
  }
 }'

The following example exports the query results as a csv file with the header row, using the specified resource principals. It uses the following input values:

  • Region - valid region name

  • Namespace - valid namespace

  • Bucket - aa-test

  • Prefix - export/summary/

This sample uses the default values and hence dialect is not required. 

mysql> select sum(l_quantity) from lineitem limit 1000
INTO OUTFILE WITH PARAMETERS
'{"file":[{"region": region_name, 
		  "namespace": namespace, 
		  "bucket": aa-test,
		  "prefix": export/summary/
		}]
}' ;

The following example exports the query results as a csv file with the header row, using the specified resource principals. It uses the following input values:

  • Format - csv file

  • Header - True

  • Region - valid region name

  • namespace - valid namespace

  • Bucket - aa-test

  • Prefix - export/summary/ 

mysql> select sum(l_quantity) from lineitem limit 1000
INTO OUTFILE WITH PARAMETERS
'{"file":[{"region": region_name, 
		  "namespace": namespace, 
		  "bucket": aa-test,
		  "prefix": export/summary/
  }]
		, "dialect": {"has_header": true, "format": "csv"}
}' ;

Export Query Result Parameters

Parameter description for JSON and non-JSON syntax to export the query results.

  • URI: URL is a mandatory parameter for non-JSON syntax to export the query results. You can use HeatWave on OCI non-PAR URL, HeatWave on OCI-PAR URL or HeatWave on AWS URL to access the Object Storage.

    While creating the PAR URL using the HeatWave Console, enable the options Permit object reads and writes and Enable Object Listing.

    PAR pattern ("*https?*/p/?*/n/?*/b/?*/o/*") is added to a list of colon-separated patterns in order for MySQL to ignore statements with PAR for logging purposes. Now by default, the pattern list is: "*IDENTIFIED*:*PASSWORD*:*https?*/p/?*/n/?*/b/?*/o/*"

  • format: The file format to export the query results. Export the query results as a csv, or parquet file. By default, the results are exported to a csv file.

  • COMPRESSION: The file compression type for Parquet format. The supported compression types are UNCOMPRESSED, SNAPPY, GZIP, BROTLI, ZSTD and LZ4 (as supported by the Lakehouse load).

  • encoding: Defines the character encoding. The default is "utf8mb4". Encoding is ignored with warning for export.

  • has_header: Whether the CSV file has a header row, or not. The default is false. Represented as HEADER in the non-JSON syntax.

  • null_value: Represents a string as a null value. null_value is supported on csv format. Represented as a NULL AS in non-JSON format. The supported null values are "NULL" and "\N".

  • field_delimiter: Defines single character used to enclose fields. The delimiter for csv format is ",". When you are exporting query results to Object Storage, COLUMNS or FIELDS TERMINATED BY only supports single character values.

  • record_delimiter: Defines one or more characters used to delimit records. The maximum field delimiter length is 64 characters. The default is "\\n".

  • escape_character: Defines one or more characters used to enclose special characters. The default is "\\". Escape characters are ignored with warning for export.

  • quotation_marks: Defines one or more characters used to enclose fields. The default is "\"". Quotation marks are ignored with warning for export.


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