Related Documentation Download this Manual
PDF (US Ltr) - 2.1Mb
PDF (A4) - 2.1Mb


HeatWave User Guide  /  HeatWave Lakehouse  /  Exporting Query Results to Object Storage

5.7 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. After the results are exported successfully, the number of affected rows is displayed.

Note

In Lakehouse, only queries without full query support can be exported.

During export, you can transform the existing file format of the table loaded from Lakehouse. For example, you can export a table loaded as a csv file back to object storage as parquet file.

You can also store the data in Object Storage and load it to Lakehouse or HeatWave, when required. To reduce billing cost, you can move the historical data from HeatWave, periodically to the Object Storage.

Exporting Query Results to an OCI or AWS Object Storage

Use the following non-JSON syntax for SELECT INTO to export query results from HeatWave and Lakehouse. For more information, see SELECT ... INTO Statement.

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'] ...]

JSON Syntax for Exporting Using a PAR Value

If you are using a PAR value, you can export the query results using the extended SELECT INTO syntax that follows. prefix is optional. For more information, see SELECT ... INTO Statement.

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"
  }
 }'

JSON Syntax for Exporting Using Bucket, Prefix, Namespace, and Region

You can export query results specifying the bucket, prefix, namespace, and region, using the following SELECT INTO syntax. For more information, see SELECT ... INTO Statement.

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"
  }
 }'

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.

  • 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). Compression type is ignored with warning for export.

  • 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". null_value is ignored with warning for export.

  • field_delimiter: Defines one or more characters used to enclose fields. The maximum field delimiter length is 64 characters. The default is ",".

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