SELECT INTO

Description

You can execute this statement to store the query result in a variable or write it to a file. Here:

• The SELECT ... INTO OUTFILE statement writes the result set to an external file and allows you to specify the output format.

  Note

  When SELECT ... INTO OUTFILE writes data, it supports using \N to represent NULL.

• The SELECT ... INTO DUMPFILE statement writes a single, unformatted line to an external file.

• The SELECT ... INTO var_list statement writes the result set to variables.

Privilege requirements

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

Here is an example:

You can use the following command to grant the FILE privilege to a user:

GRANT FILE ON *.* TO user_name;

In this example, user_name is the username of the user who needs to execute 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}

Parameter explanation

field_term

• [OPTIONALLY] ENCLOSED BY string: the delimiter that wraps the field values. The default value is no delimiter. For example, ENCLOSED BY '"' specifies to enclose the field values with double quotation marks. If the OPTIONALLY keyword is used, only string-type values are enclosed with the specified delimiter.
• TERMINATED BY string: the delimiter that separates field values. For example, TERMINATED BY ',' specifies to use commas as the delimiters between field values.
• ESCAPED BY string: the escape character to handle special characters or parse data in a special format. The default escape character is the backslash (\).

line_term

• STARTING BY string: the start character of each row.
• TERMINATED BY string: the end character of each row. The default value is the line break character. For example, ... LINES TERMINATED BY '\n' ... specifies to use the line break character as the end character of each row.

file_option

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

  • SINGLE [=] TRUE: the default value, which specifies to export data to a single file.

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

    Notice

    When the parallelism is greater than 1 and SINGLE = FALSE, multiple files can be exported to achieve parallel reading, parallel writing, and accelerated export.

• MAX_FILE_SIZE [=] {int | string}: the size of a single file when data is exported to multiple files. This parameter is effective only when SINGLE = FALSE.

• BUFFER_SIZE [=] {int | string}: the size of memory that is allocated to each thread for each partition. If no partitioning is used, it is allocated to a single partition. The default value is 1 MB.

  Note

  • BUFFER_SIZE is used for tuning the export performance. When the server has sufficient memory and you want to increase the export efficiency, you can set this parameter to a large value (for example, 4 MB). When the server has insufficient memory, you can set it to a small value (for example, 4 KB). If set to 0, a single memory area is shared by all partitions in a thread.
  • Starting from V4.3.2 BP1 of OceanBase Database V4.3.2, the BUFFER_SIZE parameter is supported.

Example

Export data files to the local server

1. Set the file path for exporting files.

   To export files, you need to set the system variable secure_file_priv to a path that the exported file can access.

   Notice

   For security reasons, when you set the system variable secure_file_priv, you can connect to the database only through a local socket to execute the SQL statement that modifies the global variable. For more information, see secure_file_priv.

   1. Log in to the OBServer node where you want to export files.

      ssh admin@xxx.xxx.xxx.xxx
      

   2. Connect to the mysql001 tenant by using 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.

2. After reconnecting to the database, use the SELECT INTO OUTFILE statement to export data. Use commas to separate the field values; enclose the string-type values in "; and use line breaks as the end markers.

   • Export data to a single file named 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
     

   • Export data to multiple files without specifying file names (namely, using the default prefix data for the file names), with no more than 4 MB of data in each file.

     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
     

   • Export data to multiple files with the prefix dd2024 for the file names, with no more than 4 MB of data in each file.

     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 are exporting data to the same path at the same time, errors may occur or only part of the data may be exported. In this case, try to set different export paths.
     For example:
     SELECT /*+parallel(2)*/ * FROM t1 INTO OUTFILE 'test/data' SINGLE = FALSE; and SELECT /*+parallel(2)*/ * FROM t2 INTO OUTFILE 'test/data' SINGLE = FALSE; may both fail with an error if the export file names are the same. In this case, set the export paths to test/data1 and test/data2 respectively.
   • After SINGLE = FALSE, if the export fails due to the file already existing, you can delete all files with the same prefix as the export target in the export directory or delete the export directory and rebuild it, and then attempt to export the data again.
     For example:
     If the SELECT /*+parallel(2)*/ * FROM t1 INTO OUTFILE 'test/data' SINGLE = FALSE; statement fails, you can delete all files with the data prefix in the test directory or directly delete the test directory and rebuild it, and then try to export the data again.

3. Log in to the server and view the exported files in the /home/admin/test_data directory on 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 in the example of exporting data to a single file; data_0_0_0 and data_0_1_0 are the file names in the example of exporting data to multiple files without specifying file names; dd2024_0_0_0 and dd2024_0_1_0 are the file names in the example of exporting data to multiple files with the prefix dd2024 for the file names.

Export data files to Alibaba Cloud OSS

Use the SELECT INTO OUTFILE statement to export data from the test_tbl2 table to specified Alibaba Cloud OSS storage locations by partition. The partitioning key is the combination of the col1 and col2 columns. Rows with the same values in these two columns 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 location is specified by the $DATA_FOLDER_NAME variable. You must also provide the host address, access ID, and access key of Alibaba Cloud OSS.

References

Export data by using the OUTFILE statement