SELECT INTO

Description

This statement allows you to store query results in a variable, a file, or both. The statements are as follows:

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

• The SELECT ... INTO DUMPFILE statement writes a single row of result set to an external file without a format.

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

Privilege requirements

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

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]
     | 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}

Parameter explanation

field_term

• [OPTIONALLY] ENCLOSED BY string: specifies the symbols that wrap the field values. The default is no quotation symbols. For example, ENCLOSED BY '"' indicates that character values are enclosed in double quotation marks. If the OPTIONALLY keyword is used, the specified characters are used to wrap only string-type values.
• TERMINATED BY string: specifies the delimiter between field values. For example, TERMINATED BY ',' uses a comma as the delimiter between two field values.
• ESCAPED BY string: specifies the escape character to process special characters or parse data in a special format. The default escape character is the backslash (\).

line_term

• STARTING BY string: specifies the initial characters of each line.
• TERMINATED BY string: the delimiter for ending each row. The default delimiter is the line break. For example, ... LINES TERMINATED BY '\n' ... indicates that each row is ended with a line break.

file_option

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

  • SINGLE [=] TRUE: The default value. Specifies that data can be exported only to a single file.

  • SINGLE [=] FALSE: Specifies that the results can be exported to multiple files.

    Notice

    When the DOP is greater than 1 and SINGLE = FALSE, the data can be exported to multiple files to achieve parallel reading, parallel writing, and increased export speed.

• MAX_FILE_SIZE [=] {int | string}: the maximum size of a single file when exporting data, which takes effect only when SINGLE = FALSE.

• BUFFER_SIZE [=] {int | string}: the size of memory that each thread applies for separately to export data from each partition during the export process. The value 1 MB is used if this parameter is not specified. In the case of no partitioning, it is considered as a single partition.

  Note

  • BUFFER_SIZE is used for performance tuning during export. If sufficient memory is available on the server and you want to increase the export efficiency, you can set this parameter to a large value (for example, 4 MB). If the server memory is limited, you can set this parameter to a small value (for example, 4 KB). If set to 0, a block of shared memory is allocated for all partitions in a single thread.
  • The BUFFER_SIZE parameter is supported in OceanBase Database V4.3.2 and later, starting from V4.3.2 BP1.

Example

Export data files to your local server

1. Set the path where you want to export the files.

   Before you can export files, you need to set the system variable secure_file_priv to a path that can be accessed by the exported files.

   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 the files.

      ssh admin@xxx.xxx.xxx.xxx
      

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

      obclient -S /home/admin/oceanbase/run/sql.sock -usys@oracle001 -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 you reconnect to the database, use the SELECT INTO OUTFILE statement to export data. Use a comma to separate the field values; enclose the string-type values with "; and use line breaks as the end markers.

   • Export data to a single file named test_tbl1.csv.

     SELECT /*+parallel(2)*/ *
     INTO OUTFILE '/home/admin/test_data/test_tbl1.csv'
       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
       LINES TERMINATED BY '\n'
     FROM test_tbl1;
     

     The return result is as follows:

     Query OK, 9 rows affected
     

   • Export data to multiple files without specifying file names, with a maximum file size of 4 MB each.

     SELECT /*+parallel(2)*/ *
       INTO OUTFILE '/home/admin/test_data/'
       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
       LINES TERMINATED BY '\n'
       SINGLE = FALSE MAX_FILE_SIZE = '4MB'
     FROM test_tbl1;
     

     The return result is as follows:

     Query OK, 9 rows affected
     

   • Export data to multiple files with the file name prefix dd2024 and a maximum file size of 4 MB each.

     SELECT /*+parallel(2)*/ *
       INTO OUTFILE '/home/admin/test_data/dd2024'
       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
       LINES TERMINATED BY '\n'
       SINGLE = FALSE MAX_FILE_SIZE = '4MB'
     FROM test_tbl1;
     

     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, you can properly set the export path to avoid such issues.
     For example:
     SELECT /*+parallel(2)*/ * INTO OUTFILE 'test/data' SINGLE = FALSE FROM t1; and SELECT /*+parallel(2)*/ * INTO OUTFILE 'test/data' SINGLE = FALSE FROM t2; may both fail with an error if they attempt to export data to the same file. In this case, you can set the export path 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 in the export directory or delete the export directory and rebuild it, and then attempt to export the data again.
     For example:
     After SELECT /*+parallel(2)*/ * INTO OUTFILE 'test/data' SINGLE = FALSE FROM t1; 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; and dd2024_0_0_0 and dd2024_0_1_0 are the file names in the example of exporting data to multiple files with the file name prefix dd2024.

Export data files to Alibaba Cloud OSS

You can export data files to Alibaba Cloud OSS.

Execute the SELECT INTO OUTFILE statement to export data from the test_tbl2 table to a specified OSS storage location by partition. The data is exported based on 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)*/ *
    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'
FROM test_tbl2;

References

Export data by using the OUTFILE statement