The SELECT INTO OUTFILE statement is a common method for exporting data. It allows you to specify the fields to be exported, which is useful in scenarios where you do not want to export the primary key fields. When used in conjunction with the LOAD DATA INFILE statement for importing data, it provides a convenient way to import and export data.
Background information
We recommend that you use MySQL Client or OBClient. The client must directly connect to the OceanBase AI Database instance for import and export operations.
Privilege requirements
To execute the
SELECT INTOstatement in a user tenant, you must have theFILEprivilege and theSELECTprivilege on the table. To grant theFILEprivilege to a user, use the following command:GRANT FILE ON *.* TO user_name;Here,
user_nameis the username of the account that executes theSELECT INTOstatement. For more information about privileges in OceanBase AI Database in MySQL-compatible mode, see Permission classification.
Syntax
SELECT [/*+parallel(N)*/] column_list_option
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}
FROM table_name_list
[WHERE where_conditions]
[GROUP BY group_by_list [HAVING having_search_conditions]]
[ORDER BY order_expression_list];
column_list_option:
column_name [, column_name ...]
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>']
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 |
|---|---|
| parallel(N) | Optional. Specifies the degree of parallelism for executing the statement. |
| column_list_option | Specifies the column options. You can use * to select all columns. column_name: the name of the column. |
| file_name | The path and name of the exported file. For more information, see file_name. |
| PARTITION BY part_expr | Optional. Specifies the partitioning method for exported data. The value of part_expr serves as a part of the export path. The system calculates the value of part_expr for each row of data. Rows of data for which the value of part_expr is the same belong to the same partition and are exported to the same directory.
Notice
|
| FORMAT = (external_file_format_list) | Optional. Specifies the attributes related to the file format exported by the FORMAT clause. Use TYPE to specify the export file format: 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 data to an external file. charset_name indicates the name of the character set. |
| field_opt | Optional. Specifies the field options for exporting data. You can use the FIELDS or COLUMNS clause to specify field options. For more information, see field_term. |
| line_opt | Optional. Specifies the start and end characters of each line in the exported file. You can set the start and end characters of each line in the output file by using the LINES clause. For more information, see line_term. |
| file_opt | Optional. Specifies whether to export data to multiple files and the size of each file when data is exported to multiple files. For more information, see file_option. |
| FROM table_name_list | Specifies the objects from which to select data. |
| WHERE where_conditions | Optional. Specifies the filter conditions. Only data that meets the conditions is returned in the query result. |
| GROUP BY group_by_list | Optional. Specifies the grouping columns. Generally, it is used in combination with aggregate functions.
NoteAll columns that are not followed by aggregate functions in the columns specified after the |
| HAVING having_search_conditions | Optional. Specifies the filter conditions for grouped data. The HAVING clause is similar to the WHERE clause, but the HAVING clause can use cumulative functions such as SUM and AVG. |
| ORDER BY order_expression_list | Optional. Specifies the one or more columns to sort the result set in ascending (ASC) or descending (DESC) order. The default order is ascending.
|
file_name
The file_name parameter has the following formats:
When saving the exported file on an OBServer node, the format is:
/\$PATH/\$FILENAME, where the parameters are explained as follows:$PATH: Specifies the path to save the exported file, that is, the path of the exported file on the OBServer node.$FILENAME: Specifies the name of the file to export. WhenSINGLE = FALSE, it specifies the prefix of the exported file. If not specified, a default prefixdatawill be generated, and the system will automatically generate a suffix.
When saving the exported file on HDFS, the format is:
Notice
When using the external table write capability of HDFS, OceanBase AI Database must be used in an environment with the JAVA SDK. For detailed information on deploying the JAVA SDK environment, see Deploy the JAVA SDK environment.
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 through 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 through the
Export format for KERBEROS authentication:
hdfs://\$NAMENODE:\$PORT/\$PATH/\$FILENAME?principal=\$PRINCIPAL&keytab=\$KEYTAB&krb5conf=\$KRB5CONF&configs=\$CONFIGSDescription:
- Kerberos authentication requires providing 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.
- Kerberos authentication requires providing the Kerberos principal (
The parameters are explained as follows:
General parameters:
$NAMENODE: Indicates the IP address of the HDFS NameNode.$PORT: Indicates the port of the HDFS NameNode.$PATH: Indicates the storage path of the file in HDFS.$FILENAME: Indicates 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 form of a list of key-value pairs. For example,configs=dfs.data.transfer.protection=authentication,privacy.
Examples of
file_namefor exported files stored 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 (specified username):
hdfs://10.10.10.1:9000/data/export/sales_data.csv?username=hiveuser
KERBEROS authentication:
Suitable for HDFS clusters that require Kerberos authentication.
Query parameters must be filled in as needed and support multiple configuration items (such as configs).
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 symbol that encloses the field value. By default, no quotation marks are used. For example,ENCLOSED BY '"'indicates that character values are placed between double quotation marks. If theOPTIONALLYkeyword is used, the specified characters are only used to enclose string-type values.TERMINATED BY string: Specifies the symbol between field values. For example,TERMINATED BY ','specifies a comma as the separator between two field values.ESCAPED BY string: Specifies escape characters to handle special characters or parse data in special formats. The default escape character is the backslash (\).
line_term
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 end with a line break character.
file_option
SINGLE [=] {TRUE | FALSE}: Controls whether data is exported to a single file or multiple files.SINGLE [=] TRUE: The default value, indicating that data can only be exported to a single file.SINGLE [=] FALSE: Indicates that 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 size of a single file during export. This option takes effect only whenSINGLE = FALSE.BUFFER_SIZE [=] {int | string}: controls the memory size allocated by each thread for each partition during export (a non-partitioned table 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 shared memory block.
external_file_format_list
When TYPE = 'CSV', the following fields are included:
LINE_DELIMITER: specifies the line delimiter for the CSV file. Default 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. Must be 1 byte. Default isESCAPE ='\'.FIELD_OPTIONALLY_ENCLOSED_BY: optional. Specifies the symbol that wraps field values in the CSV file. Default is empty. Using this option means wrappers are added only for certain types of fields (such as CHAR, VARCHAR, TEXT, JSON, etc.).FIELD_ENCLOSED_BY: optional. Specifies the symbol that wraps field values in the CSV file. Default is empty, meaning no wrapper is added. This option applies the wrapper 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 compression format of the file. Supports four compression formats: NONE, GZIP, DEFLATE, and ZSTD.GZIP/DEFLATE: compresses files using GZIP.ZSTD: compresses files using ZSTD.NONE: indicates the file is uncompressed (default value).
FILE_EXTENSION: optional. Specifies a user-defined file extension. Used only for multi-file exports and is supported only for the CSV format. If this parameter is not specified, the file extension is determined by the format type. The default CSV file extension is.csv.
When TYPE = 'PARQUET', the following fields are included:
COMPRESSION: specifies the compression format for the PARQUET file. The default compressed suffix is.parquet. Supports seven compression formats:UNCOMPRESSED(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 for the PARQUET file, in bytes. You can specify a number or a string like'64MB'. If not specified, the default is256MB. It is recommended to 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. Supports five compression formats:UNCOMPRESSED(indicating the file is uncompressed),SNAPPY,ZLIB,LZ4, andZSTD. If not specified, the default isUNCOMPRESSED.COMPRESSION_BLOCK_SIZE: specifies the block size into which data is divided during compression, in bytes. You can specify a number or a string like'64KB'. If not specified, the default is256KB. It is recommended to use the default value.STRIPE_SIZE: specifies the Stripe size for the ORC file, in bytes. You can specify a number or a string like'64MB'. If not specified, the default is64MB. It is recommended to use the default value.ROW_INDEX_STRIDE: controls the frequency at which index records are written. Defines how many rows are processed before writing an index record. If not specified, the default is10000. It is recommended to use the default value.
Considerations
When multiple export tasks simultaneously export to the same path, issues such as errors or partial data export may occur. This can be avoided by setting appropriate export paths. For example:
SELECT /*+parallel(2)*/ * INTO OUTFILE 'test/data' SINGLE = FALSE FROM t1;SELECT /*+parallel(2)*/ * INTO OUTFILE 'test/data' SINGLE = FALSE FROM t2;If executed simultaneously, errors may occur due to identical export filenames. It is recommended to set the export paths to
test/data1andtest/data2.When
SINGLE = FALSEand the export fails for reasons such asfile 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)*/ * INTO OUTFILE 'test/data' SINGLE = FALSE FROM t1;After a failure, you can delete all files with the
dataprefix in thetestdirectory, or directly delete thetestdirectory and rebuild it, then try the export again.The compression for PARQUET and ORC formats is at the level of internal data blocks within a file, not indicating that the entire file is in a compressed format. Therefore, the compressed suffix applies only to the CSV format, not to PARQUET or ORC formats.
When exporting compressed CSV files, the naming conventions for single and multiple files are slightly different:
For multi-file export (
SINGLE = FALSE), if a compression algorithm is specified, the corresponding suffix is added to the file name. The compressed suffix is placed at the end of the file name. For example, to export a file nameddata_1_0_1, if the compression algorithmgzipis specified, the file name becomesdata_1_0_1.gz.For single-file export (
SINGLE = TRUE), the output file name matches exactly the user-specified file name. That is, if a compression suffix is specified, it is included in the output file name. If no compression suffix is specified, no suffix is added.
The logical order of the
FILE_EXTENSIONfield when exporting CSV format files:When both
COMPRESSIONandFILE_EXTENSIONare specified, the suffix order is.file_extension.compression. For example, to export a file nameddata_0_0_1, specifyingFILE_EXTENSIONasxlsandCOMPRESSIONasgzipresults in an exported file nameddata_0_0_1.xls.gzfor multiple files.Regardless of whether the set
FILE_EXTENSIONstarts with a.or not, the output file extension contains exactly one.. For example, to export a file nameddata, specifyingFILE_EXTENSIONasxlsor.xlsresults in a file nameddata.xls.
Examples
Export data files to local storage
Example 1: Use the SELECT INTO OUTFILE statement to export a CSV file
Log in to the server where the OBServer node to connect to resides.
[xxx@xxx /home/admin/test_data]# ssh admin@xxx.xxx.xxx.xxxSet the file path for importing data.
Set the system variable
secure_file_privto specify the path where you can access files for importing or exporting.Notice
For security reasons, when you set the system variable
secure_file_priv, you can connect to the database only through a local Unix socket to execute the SQL statement that modifies the global variable. For more information, see secure_file_priv.Log in to the server where the OBServer node to connect to resides.
[xxx@xxx /home/admin/test_data]# ssh admin@xxx.xxx.xxx.xxxConnect to the
mysql001tenant through a local Unix socket.obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******Set the import path to
/home/admin/test_data.obclient [test]> SET GLOBAL secure_file_priv = "/home/admin/test_data"; Query OK, 0 rows affected
After reconnecting to the database, use the
SELECT INTO OUTFILEstatement to export data.Export data to a single file in serial mode. Specify the file name as
test_tbl1.csv, the character set asutf8mb4, and the compression format asgzip.obclient [test]>SELECT /*+parallel(2)*/ * INTO OUTFILE '/home/admin/test_data/test_tbl1.csv' FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ',', ENCODING = 'utf8mb4', COMPRESSION = gzip) FROM tbl1;The result is as follows:
Query OK, 9 rows affectedWrite data to multiple files in parallel. Do not specify the file name, and set the maximum size of each file to 4 MB. Specify the character set as
utf8mb4, and the compression format asgzip.obclient [test]>SELECT /*+parallel(2)*/ * INTO OUTFILE '/home/admin/test_data/' FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ',', ENCODING = 'utf8mb4', COMPRESSION = gzip) SINGLE = FALSE MAX_FILE_SIZE = '4MB' FROM tbl1;The result is as follows:
Query OK, 9 rows affectedWrite data to multiple files in parallel. Specify the file name prefix as
dd2024, set the maximum size of each file to 4 MB, specify the character set asutf8mb4, and the compression format asgzip.obclient [test]>SELECT /*+parallel(2)*/ * INTO OUTFILE '/home/admin/test_data/dd2024' FORMAT = (TYPE = 'CSV', FIELD_DELIMITER = ',', ENCODING = 'utf8mb4', COMPRESSION = gzip) SINGLE = FALSE MAX_FILE_SIZE = '4MB' FROM tbl1;The result is as follows:
Query OK, 9 rows affected
Log in to the server and check the exported file information in the
/home/admin/test_datadirectory on the OBServer node.[xxx@xxx /home/admin/test_data]# lsThe result is as follows:
data_0.csv.gz dd2024_0.csv.gz test_tbl1.csvIn this example,
test_tbl1.csvis the file name exported by the serial single-file export example;data_0.csv.gzis the file name exported by the parallel multiple-file export example without a specified file name, with the compression algorithm set togzip; anddd2024_0.csv.gzis the file name exported by the parallel multiple-file export example with a specified file name prefix ofdd2024, with the compression algorithm set togzip.
Example 2: Use the SELECT INTO OUTFILE statement to export a PARQUET file
Log in to the server where the OBServer node to connect to resides.
[xxx@xxx /home/admin/test_data]# ssh admin@xxx.xxx.xxx.xxxSet the file path for importing data.
Set the system variable
secure_file_privto specify the path where you can access files for importing or exporting.Notice
For security reasons, when you set the system variable
secure_file_priv, you can connect to the database only through a local Unix socket to execute the SQL statement that modifies the global variable. For more information, see secure_file_priv.Log in to the server where the OBServer node to connect to resides.
[xxx@xxx /home/admin/test_data]# ssh admin@xxx.xxx.xxx.xxxConnect to the
mysql001tenant through a local Unix socket.obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******Set the import path to
/home/admin/test_data.obclient [test]> SET GLOBAL secure_file_priv = "/home/admin/test_data"; Query OK, 0 rows affected
After reconnecting to the database, use the
SELECT INTO OUTFILEstatement to export data. By using a mapping table, various data types of the user tenant can be converted into data types supported by PARQUET.Export data to a single file in serial mode. Specify the file name as
test_tbl1.parquet, the compression format asSNAPPY, and the ROW GROUP size as128MB.obclient [test]>SELECT /*+parallel(2)*/ * INTO OUTFILE '/home/admin/test_data/test_tbl1.parquet' FORMAT = (TYPE = 'PARQUET' COMPRESSION = 'SNAPPY' ROW_GROUP_SIZE = '128MB') FROM tbl1;The result is as follows:
Query OK, 9 rows affectedWrite data to multiple files in parallel. Do not specify the file name, and set the maximum size of each file to 4 MB. Specify the compression format as
SNAPPY, and the ROW GROUP size as128MB.obclient [test]>SELECT /*+parallel(2)*/ * INTO OUTFILE '/home/admin/test_data/' FORMAT = (TYPE = 'PARQUET' COMPRESSION = 'SNAPPY' ROW_GROUP_SIZE = '128MB') SINGLE = FALSE MAX_FILE_SIZE = '4MB' FROM tbl1;The result is as follows:
Query OK, 9 rows affectedWrite data to multiple files in parallel. Specify the file name prefix as
dd2024, and set the maximum size of each file to 4 MB. Specify the compression format asSNAPPY, and the ROW GROUP size as128MB.obclient [test]>SELECT /*+parallel(2)*/ * INTO OUTFILE '/home/admin/test_data/dd2024' FORMAT = (TYPE = 'PARQUET' COMPRESSION = 'SNAPPY' ROW_GROUP_SIZE = '128MB') SINGLE = FALSE MAX_FILE_SIZE = '4MB' FROM tbl1;The result is as follows:
Query OK, 9 rows affected
Log in to the server and check the exported file information in the
/home/admin/test_datadirectory on the OBServer node.[xxx@xxx /home/admin/test_data]# lsThe result is as follows:
data_0.parquet dd2024_0.parquet test_tbl1.parquetIn this example,
test_tbl1.parquetis the file name exported by the serial single-file export example;data_0.parquetis the file name exported by the parallel multiple-file export example without a specified file name; anddd2024_0.parquetis the file name exported by the parallel multiple-file export example with a specified file name prefix ofdd2024.
Example 3: Use the SELECT INTO OUTFILE statement to export an ORC file
Log in to the server where the OBServer node to connect to resides.
[xxx@xxx /home/admin/test_data]# ssh admin@xxx.xxx.xxx.xxxSet the file path for importing data.
Set the system variable
secure_file_privto specify the path where you can access files for importing or exporting.Notice
For security reasons, when you set the system variable
secure_file_priv, you can connect to the database only through a local Unix socket to execute the SQL statement that modifies the global variable. For more information, see secure_file_priv.Log in to the server where the OBServer node to connect to resides.
[xxx@xxx /home/admin/test_data]# ssh admin@xxx.xxx.xxx.xxxConnect to the
mysql001tenant through a local Unix socket.obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******Set the import path to
/home/admin/test_data.obclient [test]> SET GLOBAL secure_file_priv = "/home/admin/test_data"; Query OK, 0 rows affected
After reconnecting to the database, use the
SELECT INTO OUTFILEstatement to export data. By using a mapping table, various data types of the user tenant can be converted into data types supported by ORC.Export data to a single file in serial mode. Specify the file name as
test_tbl1.orc, the compression format asSNAPPY, the compression block size as256 KB, the stripe size as64 MB, and record an index every10,000rows.obclient [test]>SELECT /*+parallel(2)*/ * INTO OUTFILE '/home/admin/test_data/test_tbl1.orc' FORMAT = (TYPE = 'ORC' COMPRESSION = 'SNAPPY' COMPRESSION_BLOCK_SIZE = 262144 STRIPE_SIZE = 67108864 ROW_INDEX_STRIDE = 10000) FROM tbl1;The result is as follows:
Query OK, 9 rows affectedWrite data to multiple files in parallel. Do not specify the file name, and set the maximum size of each file to 4 MB. Specify the compression format as
SNAPPY, the compression block size as256 KB, the stripe size as64 MB, and record an index every10,000rows.obclient [test]>SELECT /*+parallel(2)*/ * INTO OUTFILE '/home/admin/test_data/' FORMAT = (TYPE = 'ORC' COMPRESSION = 'SNAPPY' COMPRESSION_BLOCK_SIZE = 262144 STRIPE_SIZE = 67108864 ROW_INDEX_STRIDE = 10000) SINGLE = FALSE MAX_FILE_SIZE = '4MB' FROM tbl1;The result is as follows:
Query OK, 9 rows affectedWrite data to multiple files in parallel. Specify the file name prefix as
dd2024, and set the maximum size of each file to 4 MB. Specify the compression format asSNAPPY, the compression block size as256 KB, the stripe size as64 MB, and record an index every10,000rows.obclient [test]>SELECT /*+parallel(2)*/ * INTO OUTFILE '/home/admin/test_data/dd2024' FORMAT = (TYPE = 'ORC' COMPRESSION = 'SNAPPY' COMPRESSION_BLOCK_SIZE = 262144 STRIPE_SIZE = 67108864 ROW_INDEX_STRIDE = 10000) SINGLE = FALSE MAX_FILE_SIZE = '4MB' FROM tbl1;The result is as follows:
Query OK, 9 rows affected
Log in to the server and check the exported file information in the
/home/admin/test_datadirectory on the OBServer node.[xxx@xxx /home/admin/test_data]# lsThe result is as follows:
data_0.orc dd2024_0.orc test_tbl1.orcIn this example,
test_tbl1.orcis the file name exported by the serial single-file export example;data_0.orcis the file name exported by the parallel multiple-file export example without a specified file name; anddd2024_0.orcis the file name exported by the parallel multiple-file export example with a specified file name prefix ofdd2024.
Export data files to HDFS
Notice
When using the write capability of an HDFS external table, OceanBase AI Database must be used in an environment with the JAVA SDK. For detailed information on deploying a JAVA SDK environment, see Deploy the JAVA SDK environment.
Export method for SIMPLE authentication:
Export the data from the lineitem_external table in OceanBase AI Database 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 files and key files. Export the data from the lineitem_external table in OceanBase AI Database 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;
More information
A file exported by using the SELECT INTO OUTFILE method can be imported by using the LOAD DATA statement. For more information, see Import data by using the LOAD DATA statement.