This topic describes how to use external tables and the INSERT INTO statement to export data from AnalyticDB for MySQL to Object Storage Service (OSS). Only CSV and Parquet are supported for this feature. TEXT is not supported.

Prerequisites

  • A directory in which to store AnalyticDB for MySQL data in OSS is created by performing the following operations:
    1. Activate OSS.
      Note Make sure that OSS and AnalyticDB for MySQL are located in the same region.
    2. Create buckets.
    3. Create directories.

      For example, you can create the adb_data/ directory in OSS to store data exported from AnalyticDB for MySQL.

      12573201
  • An AnalyticDB for MySQL cluster, an account, and a database are created. A whitelist is configured. For more information, see Quick start of AnalyticDB for MySQL.
Note If an AnalyticDB for MySQL cluster is in elastic mode, log on to the AnalyticDB for MySQL console and view the cluster information. Enable Elastic Network Interface (ENI) in the Network Information section. 12573202

Procedure

In this example, data is exported from the source_table table of the adb_demo database in the AnalyticDB for MySQL cluster to the adb_data folder in OSS.

  1. Connect to the AnalyticDB for MySQL cluster and database. For more information, see Connect to a cluster.
  2. Create an external table in the adb_demo database. For more information, see Syntax for creating an OSS external table.
  3. Select statements that can be executed to write data based on the external table type and write the source data to the external table created in Step 2. For more information about the syntax supported by different external table types, see Syntax used to write source table data to an external table that is not partitioned and Syntax used to write source table data to a partitioned external table.
  4. After the write task in Step 3 ends, log on to the OSS console and view the data exported to the folder in OSS. Alternatively, use AnalyticDB for MySQL to query data exported from the source table to the external table.

Syntax used to write source table data to an external table that is not partitioned

INSERT SELECT FROM
Feature: If your data is stored in a different table, you can execute the INSERT SELECT FROM statement to copy the data to an external table. You can write data from a source table to an external table, and then the data can be exported to OSS objects. Each time the data is written, a new OSS object is generated.
Note All columns must be written to the external table. You are not allowed to specify only part of columns to be written.
Syntax:
INSERT INTO table_name 
SELECT select_statement FROM from_statement;       
Example:
insert into oss_table select col1, col2, col3 from source_table;
REPLACE SELECT FROM
Feature: OSS external tables do not support primary keys. The write performance of the REPLACE SELECT FROM statement is consistent with that of the INSERT SELECT FROM statement. Data is appended to another table. If data already exists in the destination table, the existing data remains unchanged, and the new data is saved to the new OSS object.
INSERT OVERWRITE INTO SELECT
Feature: You can execute the INSERT OVERWRITE INTO SELECT statement to batch insert data to a table. If data already exists in an external table, all objects under the original external table path are deleted before a new OSS object is generated.
Note All columns must be written to the external table. You are not allowed to specify only part of columns to be written.
Syntax:
INSERT OVERWRITE INTO table_name 
SELECT select_statement FROM from_statement; 
Example:
insert overwrite into oss_table 
select col1, col2, col3 from source_table;
Export data in the CSV format to a single OSS object (Parquet format unsupported)
Feature: You can specify a unique OSS object by using hints and export data to this object. When the overwrite keyword is included, the object that has the same name in the directory defined in the TABLE_PROPERTIES parameter of the external table is overwritten. Other objects in the directory are not affected.
Note All columns must be written to the external table. You are not allowed to specify only part of columns to be written.
Version description:
  • AnalyticDB for MySQL versions earlier than 3.1.2 do not support this feature. Object names are automatically generated by the system, and multiple objects are exported. The system dynamically determines the number of the objects based on the concurrent task speed.
  • AnalyticDB for MySQL version 3.1.2 and later allow you to export data from an AnalyticDB for MySQL external table in the CSV format to a single OSS object by using hints. You can customize the object name when you export data. If data of the external table is exported without hints, the results are the same as those in AnalyticDB for MySQL versions earlier than 3.1.2.
Syntax:
/*output_filename=adb.txt*/INSERT [OVERWRITE] table_name 
SELECT select_statement FROM from_statement;
Example:
/*output_filename=adb.txt*/INSERT [OVERWRITE] oss_table 
SELECT * FROM source_table;

Syntax used to write source table data to a partitioned external table

When you write data from partitioned tables to objects, the objects do not contain the data of partition key columns. Data information of partition key columns is displayed as OSS directories.

For example, two partition key columns and three common columns are defined for a partitioned table. The partition key column in the hash partition is named pcol1, and its value is 1. The partition key column in the list partition is named pcol2, and its value is a. The partitioned table data is exported to the adb_data/ path in OSS. If you write data from the partition whose pcol1 is set to 1 and pcol2 is set to a, the folder of the relative path of the object is: adb_data/pcol1=1/pcol2=a/. The CSV or Parquet object does not contain the values of the pcol1 and pcol2 columns. Only the values of the three common columns are included.

Note Data cannot be exported from multiple partitions of a table to a single OSS object.
INSERT INTO PARTITION SELECT FROM
Feature: You can execute the INSERT INTO PARTITION SELECT FROM statement to batch insert data to an external table that is partitioned. When you write data, specify all partition columns and their values in the PARTITION field. You can also specify partition values only of high-level partitions. Partition values of low-level partitions are dynamically generated. Alternatively, you do not specify the PARTITION field. Partition values of all levels are dynamically generated.

New data is appended to the corresponding partition. Each time the data is written, a new OSS object is generated.

All columns must be written to the external table. You are not allowed to specify only part of columns to be written.

Fully static partition
Syntax:
INSERT into table_name PARTITION(par1=val1,par2=val2,...) 
SELECT select_statement FROM from_statement;
Example:
insert into oss_table_par PARTITION(par1=val1,par2=val2) 
select col1, col2, col3, from source_table;
Static-dynamic partition
Syntax:
INSERT into table_name PARTITION(par1=val1,par2,...) 
SELECT select_statement FROM from_statement;
Example:
insert into oss_table_par PARTITION(par1=val1,par2) 
select col1, col2, col3, par2col from source_table;
Fully dynamic partition
Syntax:
INSERT into table_name 
SELECT select_statement FROM from_statement;
Example:
insert into oss_table_par
select col1, col2, col3, par1col, par2col from source_table;
REPLACE INTO PARTITION SELECT FROM
Feature: External tables do not support primary keys. The write performance of the REPLACE INTO PARTITION SELECT FROM statement is consistent with that of the INSERT INTO PARTITION SELECT FROM statement.
INSERT OVERWRITE [INTO] PARTITION SELECT
Feature: The INSERT OVERWRITE [INTO] PARTITION SELECT statement is used in the same manner as the INSERT INTO PARTITION SELECT statement. However, when you execute the INSERT OVERWRITE [INTO] PARTITION SELECT statement, the existing objects exported from partitions that you want to export are cleared. Objects exported from partitions that have no new data written are not cleared.
Syntax:
INSERT OVERWRITE [INTO] table_name PARTITION(par1=val1,par2=val2,...)[IF NOT EXISTS] 
SELECT select_statement FROM from_statement;
Example:
INSERT OVERWRITE into oss_table_par PARTITION(par1=val1,par2=val2) IF NOT EXISTS 
select col1, col2, col3 from source_table;

Unsupported syntax

AnalyticDB for MySQL V3.0 does not support the insert syntax for data writes by row. INSERT INTO VALUES and REPLACE INTO VALUES are not supported.