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.
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.
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'] ...]
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"
}
}'
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"
}
}'
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 acsv
, orparquet
file. By default, the results are exported to acsv
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 isfalse
. Represented as HEADER in the non-JSON syntax.null_value
: Represents a string as a null value. null_value is supported oncsv
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.