SELECT INTO

Purpose

You can use this statement to store the query results in variables or in a file.

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

• 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 SELECT privilege on the corresponding 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}

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 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 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)*/ *
     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
     

   • Write multiple files in parallel without specifying the file name prefix. Set the maximum size of each file to 4 MB.

     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
     

   • 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)*/ *
       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 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)*/ * INTO OUTFILE 'test/data' SINGLE = FALSE FROM t1; and SELECT /*+parallel(2)*/ * INTO OUTFILE 'test/data' SINGLE = FALSE FROM t2; 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)*/ * INTO OUTFILE 'test/data' SINGLE = FALSE FROM t1; 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)*/ *
  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;

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