Purpose
This statement is used to store query results in variables or files. Specifically:
SELECT ... INTO OUTFILEstores the result set in an external file, allowing you to specify the output format.Note
SELECT ... INTO OUTFILEsupports using\Nto representNULLwhen outputting data.SELECT ... INTO DUMPFILEwrites a single, unformatted line to an external file.SELECT ... INTO var_liststores the result set into variables.
Privilege requirements
To execute the SELECT INTO statement, you must have the FILE privilege and the SELECT privilege on the corresponding table. For more information about OceanBase AI Database privileges, see Permission classification.
Example:
To grant the FILE privilege to a user, you can use the following command format:
GRANT FILE ON *.* TO user_name;
Here, user_name is the user who needs to execute the SELECT INTO command.
Syntax
select_stmt
INTO {OUTFILE 'file_name' [PARTITION BY part_expr] [{CHARSET | CHARACTER SET} charset_name] [field_opt] [line_opt] [file_opt]
| OUTFILE 'file_name' [PARTITION BY part_expr] [FORMAT = (external_file_format_list)] [file_opt]
| DUMPFILE 'file_name'
| into_var_list}
field_opt:
{COLUMNS | FIELDS} field_term_list
field_term_list:
field_term [, field_term ...]
field_term:
{[OPTIONALLY] ENCLOSED | TERMINATED | ESCAPED} BY string
line_opt:
LINES line_term_list
line_term_list:
line_term [line_term]
line_term:
{STARTING | TERMINATED} BY string
file_opt:
file_option [, file_option ...]
file_option:
SINGLE [=] {TRUE | FALSE}
| MAX_FILE_SIZE [=] {int | string}
| BUFFER_SIZE [=] {int | string}
external_file_format_list:
type_csv_option
| type_parquet_option
| type_orc_option
type_csv_option:
TYPE = 'CSV'
LINE_DELIMITER = '<string>' | <expr>
FIELD_DELIMITER = '<string>' | <expr>
ESCAPE = '<character>' | <expr>
FIELD_OPTIONALLY_ENCLOSED_BY = '<character>' | <expr>
FIELD_ENCLOSED_BY = '<character>' | <expr>
ENCODING = 'charset'
COMPRESSION = [NONE, GZIP, DEFLATE, ZSTD] | '<string>'
FILE_EXTENSION = ['<string>']
BYTEORDERMARK = {check | nocheck}
type_parquet_option:
TYPE = 'PARQUET',
COMPRESSION = '<string>'
ROW_GROUP_SIZE = '<string>' | <int>
type_orc_option:
TYPE = 'ORC'
COMPRESSION = '<string>'
COMPRESSION_BLOCK_SIZE = '<string>' | <int>
STRIPE_SIZE = '<string>' | <int>
ROW_INDEX_STRIDE = <int>
Parameters
Parameter |
Description |
|---|---|
| select_stmt | Specify the query statement to be executed. select_stmt A result set must be returned. For the specific structure and options of the query statement, see SELECT.
NoteThe content of the |
| file_name | Specifies the path and file name of the exported file. For more information, see file_name. |
| PARTITION BY part_expr | Optional. Specifies the partitioning method for exporting data. The value of part_expr is used as part of the export path, and the value of part_expr is calculated for each row of data. Rows with the same value of part_expr belong to the same partition and are exported to the same directory.
Notice
|
| FORMAT = (external_file_format_list) | Optional. The FORMAT clause is used to specify the format of the exported file. You can use TYPE to specify the file format, which can be CSV, PARQUET, or ORC. TYPE cannot be empty. For more information, see external_file_format_list. |
| CHARSET \ | CHARACTER SET charset_name | Optional. Specifies the character set for exporting to an external file. charset_name indicates the name of the character set. |
| field_opt | Optional. The format of each field in the output file. Valid values: FIELDS or COLUMNS can be specified using the field_term clause. For more information, see field_term. |
| line_opt | Optional. Specifies the start and end characters for each line in the exported file. LINES clause. For more information, see line_term. |
| file_opt | Optional. Controls whether to export to multiple files and the size of each file when exporting to multiple files. For more information, see file_option. |
file_name
file_name has the following format:
When the export file is saved on an OBServer node, the format is:
/\$PATH/\$FILENAME, where each parameter is explained as follows:$PATH: Specifies the path to save the exported file, that is, the path of the export file on the OBServer node.$FILENAME: The name of the file to export. WhenSINGLE = FALSE, it specifies the prefix for the exported file. If not specified, a default prefixdatais generated, and the system automatically adds a suffix.
When saving the export file to HDFS, the format is:
Notice
To use the write capability of an HDFS external table, OceanBase AI Database must be deployed in an environment with the Java SDK. For detailed information about how to deploy a Java SDK environment, see Deploy the Java SDK environment for OceanBase AI Database.
Export format for SIMPLE authentication:
Basic authentication (without Kerberos):
hdfs://\$NAMENODE:\$PORT/\$PATH/\$FILENAME[?username=\$USERNAME]Description:
- When using basic authentication (SIMPLE), you can specify the HDFS username using the
usernameparameter. - If the
usernameparameter is not specified, the HDFS username defined in the OceanBase AI Database configuration is used by default.
- When using basic authentication (SIMPLE), you can specify the HDFS username using the
Export format for KERBEROS authentication:
hdfs://\$NAMENODE:\$PORT/\$PATH/\$FILENAME?principal=\$PRINCIPAL&keytab=\$KEYTAB&krb5conf=\$KRB5CONF&configs=\$CONFIGSNote:
- For Kerberos authentication, you must provide the Kerberos principal (
principal), keytab (keytab), and Kerberos configuration file (krb5conf). - The
usernameparameter is not applicable in this scenario because Kerberos authentication identifies the identity through theprincipal.
- For Kerberos authentication, you must provide the Kerberos principal (
The parameters are described as follows:
General parameters:
$NAMENODE: the IP address of the HDFS NameNode.$PORT: the port of the HDFS NameNode.$PATH: the storage path of the file in HDFS.$FILENAME: the name of the exported file.
Parameters specific to SIMPLE authentication:
?username=\$USERNAME: Optional. Specifies the HDFS username. Ifusernameis not specified, the HDFS username defined in the OceanBase AI Database configuration is used by default.
Parameters specific to KERBEROS authentication:
$PRINCIPAL: the Kerberos principal name, for example,principal=hdfs/hadoop@EXAMPLE.COM.$KEYTAB: the path to the Kerberos keytab file, for example,keytab=/path/to/hdfs.keytab.$KRB5CONF: The path to the Kerberos configuration file. For example,krb5conf=/path/to/krb5.conf.$CONFIGS: Additional HDFS configuration parameters in the key-value pair format. For example,configs=dfs.data.transfer.protection=authentication,privacy.
The following example shows the value of file_name when the exported file is saved on HDFS:
SIMPLE authentication (basic authentication):
Suitable for HDFS clusters without Kerberos authentication.
Example 1 (default username):
hdfs://10.10.10.1:9000/data/export/sales_data.csvExample 2 (Specify username):
hdfs://10.10.10.1:9000/data/export/sales_data.csv?username=hiveuser
KERBEROS authentication:
HDFS clusters that require Kerberos authentication.
Query parameters must be filled in as needed. Multiple parameters (such as configs) are supported.
Example:
hdfs://10.10.10.1:9000/data/export/sales_data.csv?principal=hdfs/hadoop@EXAMPLE.COM&keytab=/etc/security/keytabs/hdfs.keytab&krb5conf=/etc/krb5.conf&configs=dfs.data.transfer.protection=authentication,privacy
field_term
[OPTIONALLY] ENCLOSED BY string: Specifies the characters that enclose field values. By default, no enclosing characters are used. For example,ENCLOSED BY '"'indicates that character values are placed between double quotation marks. If theOPTIONALLYkeyword is used, the specified characters will only be used to enclose string-type values.TERMINATED BY string: specifies the symbol that separates field values. For example,TERMINATED BY ','specifies a comma as the separator between two field values.ESCAPED BY string: Specifies the escape character for handling special characters or parsing data in special formats. The default escape character is the backslash (\).
line_term
STARTING BY string: Specifies the starting character for each line.TERMINATED BY string: Specifies the end character for each line. By default, a line break character is used. For example,... LINES TERMINATED BY '\n' ...indicates that a line will be terminated by a line break character.
file_option
SINGLE [=] {TRUE | FALSE}: Specifies whether to export data to a single file or multiple files.SINGLE [=] TRUE: the default value, which indicates that data can be exported to only one file.SINGLE [=] FALSE: Indicates that the data can be exported to multiple files.Notice
When the degree of parallelism is greater than 1 and
SINGLE = FALSE, data can be exported to multiple files, achieving parallel read/write and improving export speed.
MAX_FILE_SIZE [=] {int | string}: controls the maximum size of a single file during export. This parameter takes effect only whenSINGLE = FALSE.BUFFER_SIZE [=] {int | string}: The memory size allocated by each thread for each partition during export. If a table is not partitioned, it is treated as a single partition. The default value is 1 MB.Note
BUFFER_SIZEis used for export performance tuning. When the machine has sufficient memory and you want to improve export efficiency, you can set a larger value (for example, 4 MB). When the machine memory is insufficient, you can set a smaller value (for example, 4 KB). Setting it to 0 means all partitions in a single thread use a single shared memory block.
external_file_format_list
When TYPE = 'CSV', the following fields are included:
LINE_DELIMITER: the line delimiter for the CSV file. The default value isLINE_DELIMITER='\n'.FIELD_DELIMITER: Optional. Specifies the column delimiter for the CSV file. Default isFIELD_DELIMITER='\t'.ESCAPE: specifies the escape character for the CSV file. It can be only one byte. The default isESCAPE ='\'.FIELD_OPTIONALLY_ENCLOSED_BY: Optional. Specifies the character that encloses field values in a CSV file. The default is an empty string. When this option is used, enclosing characters are added only for fields of certain types, such as CHAR, VARCHAR, TEXT, and JSON.FIELD_ENCLOSED_BY: Optional. Specifies the character that encloses field values in the CSV file. The default is empty, meaning no enclosure character is used. This option applies the enclosure setting to all types of fields.ENCODING: Specifies the character set encoding format of the file. If not specified, the default value isUTF8MB4.COMPRESSION: Optional. Specifies the file compression format. Valid values: NONE, GZIP, DEFLATE, and ZSTD.GZIP/DEFLATE: Compress files using GZIP.ZSTD: Compress files using ZSTD.NONE: The file is not compressed (default).
FILE_EXTENSION: Optional. Specifies a custom file extension, used only for multi-file exports and supported only in CSV format. If this parameter is not specified, the file extension is determined by the format type. The default CSV file extension is.csv.BYTEORDERMARK: Optional. Specifies whether to detect and skip the BOM character. If this attribute is not specified, BOM character detection is not performed. Valid values:check: detects and skips BOM characters (recommended for files in UTF-8 with BOM format).nocheck: specifies not to perform BOM character detection (applicable to UTF-8 files without BOM characters).
Note
BYTEORDERMARKis supported starting with V4.6.2.1.
When TYPE = 'PARQUET', the following fields are included:
COMPRESSION: Specifies the compression format for the PARQUET file. The default compressed suffix is.parquet. Supported formats includeUNCOMPRESSED(indicating the file is uncompressed),SNAPPY,GZIP,BROTLI,ZSTD,LZ4, andLZ4_HADOOP. If not specified, the default isUNCOMPRESSED.ROW_GROUP_SIZE: Specifies the row group size of a PARQUET file, in bytes. You can specify a number or a string in the format of'64MB'. If this option is not specified, the default value256MBis used. We recommend that you use the default value.
When TYPE = 'ORC', the following fields are included:
COMPRESSION: Specifies the compression format for the ORC file. The default compressed suffix is.orc. The following five compression formats are supported:UNCOMPRESSED(indicating the file is not compressed),SNAPPY,ZLIB,LZ4, andZSTD. If this parameter is not specified, the default format isUNCOMPRESSED.COMPRESSION_BLOCK_SIZE: The size of the blocks into which data is divided during compression, in bytes. You can specify a number or a string in the format'64KB'. If this option is not specified, the default value256KBis used. We recommend that you use the default value.STRIPE_SIZE: The size of a stripe in an ORC file, in bytes. You can specify a number or a string in the format of'64MB'. If this option is not specified, the default value64MBis used. We recommend that you use the default value.ROW_INDEX_STRIDE: The parameter that controls the frequency of index records, defining how many rows are indexed at a time. If not specified, the default is10000. It is recommended to use the default value.
Examples
Export data files to local storage
Set the export file path.
To export files, you must first set the system variable
secure_file_privto configure the accessible path for exported files.Notice
For security reasons, when setting the system variable
secure_file_priv, you must connect to the database via a local socket to execute the SQL statement that modifies this global variable. For more information, see secure_file_priv.Log in to the OBServer node of the OceanBase AI Database you want to connect to.
ssh admin@xxx.xxx.xxx.xxxRun the following command to connect to the
mysql001tenant using a local Unix socket connection.obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******Set the export path to
/home/admin/test_data.SET GLOBAL secure_file_priv = "/home/admin/test_data";Log off.
After reconnecting to the database, use the
SELECT INTO OUTFILEstatement to export the data. Specify a comma as the separator between two field values; enclose string-type values with double quotes ("); and use a line break as the end marker.Serially write to a single file, specifying the file name as
test_tbl1.csv.SELECT /*+parallel(2)*/ * FROM test_tbl1 INTO OUTFILE '/home/admin/test_data/test_tbl1.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';Concurrently write to multiple files without specifying file names (using the default prefix
data), with each file not exceeding 4 MB in size.SELECT /*+parallel(2)*/ * FROM test_tbl1 INTO OUTFILE '/home/admin/test_data/' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' SINGLE = FALSE MAX_FILE_SIZE = '4MB';Write multiple files in parallel, specifying the prefix
dd2024for the file names, and ensuring each file does not exceed 4 MB in size.SELECT /*+parallel(2)*/ * FROM test_tbl1 INTO OUTFILE '/home/admin/test_data/dd2024' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' SINGLE = FALSE MAX_FILE_SIZE = '4MB';
Note
- When multiple export tasks export to the same path simultaneously, issues such as errors or partial data export may occur. This can be avoided by setting the export paths appropriately.
For example:SELECT /*+parallel(2)*/ * FROM t1 INTO OUTFILE 'test/data' SINGLE = FALSE;andSELECT /*+parallel(2)*/ * FROM t2 INTO OUTFILE 'test/data' SINGLE = FALSE;may report an error when executed simultaneously due to the same export file name. It is recommended to set the export paths totest/data1andtest/data2. - When
SINGLE = FALSEand the export fails due to reasons such as "file already exist", you can clear all files in the export directory that have the same prefix as the export target, or delete the export directory and rebuild it, then attempt the export again.
For example:SELECT /*+parallel(2)*/ * FROM t1 INTO OUTFILE 'test/data' SINGLE = FALSE;fails. You can delete all files with thedataprefix in thetestdirectory, or directly delete thetestdirectory and rebuild it, then try the export again.
Log in to the server and check the exported file information in the
/home/admin/test_datadirectory of the OBServer node.
[xxx@xxx /home/admin/test_data]# ls
The return result is as follows:
data_0_0_0 data_0_1_0 dd2024_0_0_0 dd2024_0_1_0 test_tbl1.csv
Here, test_tbl1.csv is the file name for exporting an example where a single file is written serially. data_0_0_0 and data_0_1_0 are the file names for exporting examples where multiple files are written in parallel without specifying file names. dd2024_0_0_0 and dd2024_0_1_0 are the file names for exporting examples where multiple files are written in parallel with the prefix dd2024 specified for the file names.
Export data files to HDFS
Notice
To use the HDFS external table write capability, OceanBase AI Database must be deployed in an environment with the Java SDK. For detailed information on deploying a Java SDK environment, see Deploy the Java SDK environment for OceanBase AI Database.
Export method for SIMPLE authentication
Export the data from the OceanBase AI Database table lineitem_external to HDFS and save it as a CSV file.
SELECT * FROM lineitem_external
INTO OUTFILE 'hdfs://${namenode}:${port}/path/to/lineitem.csv'
FORMAT = (
TYPE = 'CSV'
LINE_DELIMITER = '\n'
FIELD_DELIMITER = ','
FIELD_OPTIONALLY_ENCLOSED_BY ='"'
)
SINGLE = TRUE;
Export method for KERBEROS authentication
Unlike the SIMPLE authentication mode, KERBEROS authentication requires additional configuration and key files. Export the data from the OceanBase AI Database table lineitem_external to HDFS and save it as a CSV file.
SELECT * FROM lineitem_external
INTO OUTFILE 'hdfs://${namenode}:${port}/path/to/lineitem.csv?principal=hdfs/hadoop@EXAMPLE.COM&keytab=/path/to/hdfs.keytab&krb5conf=/path/to/krb5.conf&configs=dfs.data.transfer.protection=authentication,privacy'
FORMAT = (
TYPE = 'CSV'
LINE_DELIMITER = '\n'
FIELD_DELIMITER = ','
FIELD_OPTIONALLY_ENCLOSED_BY ='"'
)
SINGLE = TRUE;