SELECT INTO

Description

The statement is used to store the query result in a variable or a file. Specifically:

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

  Note

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

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

• The SELECT ... INTO var_list statement is used to store the result set in variables.

Privilege requirements

To execute the SELECT INTO statement, you must have the FILE privilege and the SELECT privilege on the target 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;

where user_name is the username that will execute the SELECT INTO statement.

Syntax

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

Parameter explanation

field_term

• [OPTIONALLY] ENCLOSED BY string: the characters used to enclose field values. The default value is no enclosing characters. For example, ENCLOSED BY '"' indicates that character values are enclosed in double quotation marks. If you use the OPTIONALLY keyword, only string-type values are enclosed in the specified characters.
• TERMINATED BY string: the delimiter between field values. For example, TERMINATED BY ',' indicates that a comma is used as the delimiter between field values.
• ESCAPED BY string: the escape character used 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 a row. The default value is no start character.
• TERMINATED BY string: the end character of a row. The default value is the line break character. For example, ... LINES TERMINATED BY '\n' ... indicates that a line is terminated by the line break character.

Examples

Export a data file to the local server

1. Set the export file path.

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

   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 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. Reconnect to the database and use the SELECT INTO OUTFILE statement to export data. Use commas to separate the field values; enclose the string-type values in " characters; and use line breaks as the delimiter. Name the file 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 a data file to Alibaba Cloud OSS

Use the SELECT INTO OUTFILE statement to export data from the test_tbl2 table to a specified Alibaba Cloud OSS storage location 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 will be 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';

The $DATA_FOLDER_NAME variable specifies the storage location, and you also need to provide the host address, access ID, and access key of Alibaba Cloud OSS.

References

• Export data by using the OUTFILE statement

• SELECT