Export data by using OUTFILE statements

This topic describes how to use a SELECT INTO OUTFILE statement to export data. The SELECT INTO OUTFILE statement allows you to specify the fields to be exported, and is preferred when primary key fields do not need to be exported. You can use this statement in combination with the LOAD DATA INFILE statement to facilitate data import and export.

Background information

OceanBase Database is compatible with the SELECT INTO OUTFILE statement.

Syntax

SELECT column_list_option
INTO OUTFILE file_route_option
    [format_of_field_option]
    [start_and_end_option]
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]...

file_route_option:
    '/path/file' 
    | 'oss://$PATH/$FILENAME/?host=$HOST&access_id=$ACCESS_ID&access_key=$ACCESSKEY'

format_of_field_option:
    {FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']

start_and_end_option:
    LINES 
        [STARTING BY 'string']
        [TERMINATED BY 'string']

Examples

The following example shows how to export data to a local device.

1. Create a table named tbl1 in the test database of the mysql001 tenant and insert data into the table.

   obclient [test]> CREATE TABLE tbl1(col1 INT PRIMARY KEY,col2 varchar(128),col3 INT);
   Query OK, 0 rows affected
   
   obclient [test]> INSERT INTO tbl1 VALUES(1,'one',80),(2,'two',90),(3,'three',100);
   Query OK, 3 rows affected
   Records: 3  Duplicates: 0  Warnings: 0
   
   obclient [test]> SELECT * FROM tbl1;
   +------+-------+------+
   | col1 | col2  | col3 |
   +------+-------+------+
   |    1 | one   |   80 |
   |    2 | two   |   90 |
   |    3 | three |  100 |
   +------+-------+------+
   3 rows in set
   

2. Set a path for the exported file.

   Set the system variable secure_file_priv to specify the path that can be accessed for file import or export.

   Notice

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

   1. Log on to the OBServer node to connect to.

      [xxx@xxx /home/admin]# ssh admin@xxx.xxx.xxx.xxx
      

   2. Execute the following statement to connect to the mysql001 tenant through a local Unix Socket:

      obclient -S /home/admin/oceanbase/run/sql.sock -uroot@mysql001 -p******
      

   3. Set the export path to /home/admin.

      obclient [(none)]> SET GLOBAL secure_file_priv = "/home/admin";
      Query OK, 0 rows affected
      

3. After you reconnect to the database, execute the SELECT INTO OUTFILE statement to export data from the tbl1 table. The name of the exported file is tbl1.sql. The field values are separated with a comma (,). The values of the string type are enclosed within a pair of double quotations marks ("). The end symbol is a line feed.

   obclient [test]> SELECT * INTO OUTFILE '/home/admin/tbl1.sql' 
       FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
       LINES TERMINATED BY '\n' 
       FROM tbl1;
   Query OK, 3 rows affected
   

4. Log on to the OBServer node and check the information about the exported file in the /home/admin directory on your local device.

   [xxx@xxx /home/admin]# cat tbl1.sql
   1,"one",80
   2,"two",90
   3,"three",100
   

References

You can use the LOAD DATA statement to import the files that are exported by using SELECT INTO OUTFILE. For more information, see Import data by using the LOAD DATA statement.