SELECT INTO

Purpose

You can use this statement to store the query results in a variable or file. The parameters are described as follows:

• SELECT ... INTO OUTFILE stores the result set in an external file in the specified output format.

  Note

  When you use the SELECT ... INTO OUTFILE statement to export data, you can use \N to represent NULL.

• SELECT ... INTO DUMPFILE writes a single line without any format to an external file.

• SELECT ... INTO var_list stores the result set in variables.

Required privileges

To execute the SELECT INTO statement, you must have the FILE privilege and the SELECT privilege on the corresponding table. For more information about privileges in OceanBase Database, see Privilege types in MySQL mode.

Here is an example:

You can execute the following statement to grant the FILE privilege to a user:

GRANT FILE ON *.* TO user_name;

In the statement, user_name is the username of the account that executes the SELECT INTO statement.

Syntax

select_stmt INTO 
    {OUTFILE 'file_name' [PARTITION BY part_expr] [ {CHARSET | CHARACTER SET} charset_name] [field_opt] [line_opt] [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}

Parameters

field_term

• [OPTIONALLY] ENCLOSED BY string: the characters for enclosing field values. By default, field values are not enclosed with any characters. For example, ENCLOSED BY '"' specifies to enclose character values with double quotation marks (""). If the OPTIONALLY keyword is used, only string values are enclosed with the specified characters.
• TERMINATED BY string: the separator between field values. For example, TERMINATED BY ',' specifies that field values are separated with commas (,).
• ESCAPED BY string: the escape character for processing special characters or parsing data in special formats. The default escape character is backslash (\).

line_term

• STARTING BY string: the start character of each line. By default, no start character is used.
• TERMINATED BY string: the end character of each line. The default value is a line feed. For example, ... LINES TERMINATED BY '\n '... specifies that a line ends with a line feed.

file_option

• SINGLE [=] {TRUE | FALSE}: specifies whether to export data to a single file or multiple files.

  • SINGLE [=] TRUE: exports data to a single file. This is the default value.

  • SINGLE [=] FALSE: exports data to multiple files.

    Notice

    When the DOP is greater than 1 and SINGLE = FALSE is specified, you can export data to multiple files for parallel reading and writing to accelerate the export.

• MAX_FILE_SIZE [=] {int | string}: the maximum size of a single file. The setting takes effect only when SINGLE = FALSE is specified.

• BUFFER_SIZE [=] {int | string}: the size of memory requested by each thread for each partition. The default value is 1 MB. A non-partitioned table is considered to have only one partition.

  Note

  • BUFFER_SIZE is intended for export performance tuning. If the server has sufficient memory and you want to improve the export efficiency, you can set a large value for this parameter, such as 4 MB. If the server has insufficient memory, you can set a small value, such as 4 KB. If the value is set to `0`, the shared memory is used for all partitions in a single thread.
  • OceanBase Database V4.3.2 supports the BUFFER_SIZE parameter in V4.3.2 BP1 and later.

Examples

Export data to a local server

1. Set a path for the exported file.

   To export data to a file, you need to set the system variable secure_file_priv to configure the path that can be accessed for export.

   Notice

   For security reasons, you can connect to the database only through a local socket to execute the SQL statement for setting secure_file_priv. For more information, see secure_file_priv.

   1. Log in to the target OBServer node.

      ssh admin@xxx.xxx.xxx.xxx
      

   2. Connect to the mysql001 tenant through a local Unix socket.

      obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
      

   3. Set the export path to /home/admin/test_data.

      SET GLOBAL secure_file_priv = "/home/admin/test_data";
      

   4. Log out of the database.

2. Reconnect to the database and execute the SELECT INTO OUTFILE statement to export data. Specify to separate two field values with a comma (,), enclose a string value with double quotation marks ("), and end each line with a line feed.

   • Write a single file in serial and specify 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';
     

     The return result is as follows:

     Query OK, 9 rows affected
     

   • Write multiple files in parallel without specifying the file name prefix, so that the file names use the default prefix data. Set the maximum size of each file to 4 MB.

     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';
     

     The return result is as follows:

     Query OK, 9 rows affected
     

   • Write multiple files in parallel. Set the prefix of the file names to dd2024 and the maximum size of each file to 4 MB.

     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';
     

     The return result is as follows:

     Query OK, 9 rows affected
     

   Note

   • When multiple export tasks with the same destination path are executed at the same time, errors may occur or only partial data may be exported. Therefore, you need to plan the export paths reasonably.
     For example, when SELECT /*+parallel(2)*/ * FROM t1 INTO OUTFILE 'test/data' SINGLE = FALSE; and SELECT /*+parallel(2)*/ * FROM t2 INTO OUTFILE 'test/data' SINGLE = FALSE; are executed at the same time, an error may be reported due to the same export file name. In this case, we recommend that you set the export paths to test/data1 and test/data2 respectively.
   • When SINGLE = FALSE is specified and the export task fails due to errors such as `file already exist`, you can clear all files with the same file name prefix as the exported files in the export directory, or delete the export directory and create a new one, and then perform the export operation again.
     For example, when the execution of
     SELECT /*+parallel(2)*/ * FROM t1 INTO OUTFILE 'test/data' SINGLE = FALSE; fails, you can delete all files with the file name prefix data under the test directory, or delete the test directory and create it again, and then try the export operation again.

3. Log in to the server where the OBServer node resides and check the exported files in the /home/admin/test_data directory of the 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 serially exported single file. data_0_0_0 and data_0_1_0 are the parallelly exported files without a file name prefix. dd2024_0_0_0 and dd2024_0_1_0 are the parallelly exported files with the file name prefix dd2024.

Export data to OSS

You can use the SELECT INTO OUTFILE statement to export data by partition from the test_tbl2 table to the specified OSS bucket. The partitioning key is the combination of the col1 and col2 columns. Rows with the same partitioning key values belong to the same partition and are exported to the same directory.

SELECT /*+parallel(3)*/ * FROM test_tbl2
  INTO OUTFILE 'oss://$DATA_FOLDER_NAME/?host=$OSS_HOST&access_id=$OSS_ACCESS_ID&access_key=$OSS_ACCESS_KEY'
    PARTITION BY CONCAT(col1,'/',col2)
    SINGLE = FALSE BUFFER_SIZE = '2MB';

The storage path is specified by the $DATA_FOLDER_NAME variable. You also need to specify the OSS endpoint and AccessKey pair.

References

Export data by using the SELECT INTO OUTFILE statement