SELECT INTO

Description

This statement enables you to store query results in a variable, file, or table. Specifically:

• 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, unformatted line to an external file.

• 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' [ {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}

Parameter explanation

field_term

• [OPTIONALLY] ENCLOSED BY string: specifies the symbols that wrap the field values. The default is no quotation marks. 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 starting characters of each line.
• TERMINATED BY string: the delimiter that indicates the end of a row. The default delimiter is the line break. For example, ... LINES TERMINATED BY '\n' ... indicates that a row ends 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 degree of parallelism is greater than 1 and SINGLE = FALSE, you can export data 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.

Examples

1. Set the path for exporting files.

   Before you export files, set the system variable secure_file_priv to the path that the export 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 connect to OceanBase Database.

      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 reconnecting to the database, use the SELECT INTO OUTFILE statement to export data. Use a comma to separate the values of the two fields; enclose the values of the string type 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 size of no more than 4 MB for each file.

     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, with a size of no more than 4 MB for each file.

     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, try to use different export paths.
     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 fail with an error if they attempt to use the same file name. 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 retry the export operation.
     For example:
     After the export operation specified by 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 retry the export operation.

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.

References

Export data by using the OUTFILE statement