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

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 takes exporting data to a local device as an example to describe how to export data.

1. Create a table named tbl1 in the test database in 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.

   Log on to the tenant as an administrator (root for a MySQL tenant and SYS for an Oracle tenant), and set the system variable secure_file_priv to specify the path to be accessed during file import or export.

   Notice

   For security reasons, when you set the system variable secure_file_priv, you can use only the local client 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. Use the OBClient to connect to the mysql001 tenant in direct connection mode.

      obclient -hxxx.xxx.xxx.xxx -P2881 -uroot@mysql001 -p****** -A
      

      Alternatively, run the following command to connect to the tenant mysql001 by using a local Unix Socket connection.

      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
   

More information

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.