Migrate data by using the OUTFILE statement

This topic describes how to use a SELECT INTO OUTFILE statement to export data.

Compared with mysqldump, SELECT INTO OUTFILE statements allow database adminiistrators (DBAs) to specify the fields to be exported and are preferred when primary key fields do not need to be exported. You can use these statements in combination with the LOAD DATA INFILE statements to facilitate data import and export. OceanBase Database is compatible with this syntax.

Notice

To import data into and export data from OceanBase Database, the client must be connected to the OceanBase Database instance directly.

Syntax

The following syntax is supported for a SELECT INTO OUTFILE statement:

SELECT [column_list] INTO  '/PATH/FILE' | 'oss://$PATH/$FILENAME/?host=$HOST&access_id=$ACCESSID&access_key=$ACCESSKEY'
      [TERMINATED BY 'string']
      {[OPTIONALLY] ENCLOSED BY 'char'}
      [ESCAPED BY 'char']
      [LINES TERMINATED BY 'string']
      [FROM  TABLENAME]
      [WHERE condition]
      [GROUP BY group_expression_list ]
      [HAVING condition]]
      [ORDER BY order_expression_list]

In the syntax:

• The following string specifies the export path of the file: '/PATH/FILE' | 'oss://$PATH/$FILENAME/?host=$HOST&access_id=$ACCESSID&access_key=$ACCESSKEY'. You can export the file to Alibaba Cloud Object Storage Service (OSS).

  Note

  Alibaba Cloud OSS does not support files larger than 5 GB. When you export a file larger than 5 GB to Alibaba Cloud OSS, it is split into multiple smaller files.

• TERMINATED BY: specifies the separator between field values. For example, TERMINATED BY ',' specifies that field values are separated with commas (,).

• ENCLOSED BY: specifies the symbol for enclosing field values. For example, ENCLOSED BY ' " ' indicates that field values are enclosed in double quotation marks (") and ENCLOSED BY ' " ' OPTIONALLY indicates that only string values are enclosed in double quotation marks (").

• ESCAPED BY: specifies the escape character. For example, ESCAPED BY '*' indicates that the asterisk (*) but not the backslash (\) is used as the escape character. The backslash (\) is the default escape character.

• LINES TERMINATED BY: specifies the end of a line. For example, LINES TERMINATED BY ',' indicates that a line ends with a comma (,).

Examples

Use the SELECT INTO OUTFILE statement to export data.

obclient> SELECT * INTO OUTFILE '/tmp/demo01.txt' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM student;

obclient> SELECT * INTO OUTFILE 'oss://oceanbase-test-bucket/tmp/demo01.txt/?host=xxx.aliyun-inc.com&access_id=xxx&access_key=xxx' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM student;