AnalyticDB for MySQL allows you 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 files can be exported from AnalyticDB for MySQL to OSS.

Prerequisites

  • A directory is created in OSS by performing the following operations to store AnalyticDB for MySQL data:
    1. Activate OSS. For more information, see Activate OSS.
      Note Make sure that OSS and AnalyticDB for MySQL are deployed in the same region.
    2. Create a bucket. For more information, see Create buckets.
    3. Create a directory. For more information, see Create directories.

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

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

Procedure

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

  1. Connect to the AnalyticDB for MySQL cluster and the database. For more information, see Connect to an AnalyticDB for MySQL 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 type of the external table, and write the source data to the external table created in Step 2. For more information about the syntax supported for different types of external tables, see Syntax used to write data to an external table that is not partitioned and Syntax used to write data to an external table that is partitioned.
  4. After the write task in Step 3 is completed, log on to the OSS console and view the data exported to the directory in OSS. Alternatively, connect to the AnalyticDB for MySQL cluster to query the data exported to the external table.

Syntax used to write 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 export the data to OSS. Each time the data is written, a new OSS object is generated.
Note The columns that you want to export must all be written to the external table. You cannot specify specific 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 in the original external table path are deleted before a new OSS object is generated.
Note The columns that you want to export must all be written to the external table. You cannot specify specific 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 single 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 The columns that you want to export must all be written to the external table. You cannot specify specific 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 how fast concurrent tasks are executed.
  • 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 specify a name for the object. If data of the external table is exported without hints, multiple objects are exported as 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 data to an external table that is partitioned

When you write data from partitioned tables to objects, the objects do not contain the data of partition key columns. Data 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. Data of the partitioned table 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 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, you can specify all partition columns and their values in the PARTITION field. You can also specify partition values only of high-level partitions. The partition values of low-level partitions are dynamically generated. Alternatively, you may 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.

The columns that you want to export must all be written to the external table. You cannot specify specific 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, existing objects in the destination table are cleared. Objects in 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 statements

AnalyticDB for MySQL V3.0 does not support the following INSERT statements for data writes by row: INSERT INTO VALUES and REPLACE INTO VALUES.