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. For more information about how to activate OSS, see Activate OSS.
      Note Make sure that OSS and AnalyticDB for MySQL are deployed in the same region.
    2. Buckets are created in OSS. For more information, see Create buckets.
    3. A folder is created in OSS. For more information, see Create folders.

      For example, you create the adb_data/ directory in OSS. Data exported from AnalyticDB for MySQL is stored in this directory.

  • An AnalyticDB for MySQL cluster is created, a whitelist is configured, and an account and a database are created. For more information, see Create a cluster.
Note If 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.

Procedure

In this example, AnalyticDB for MySQL imports the source_table data in the adb_demo database to the adb_data folder in OSS.

  1. Connect to an AnalyticDB for MySQL cluster and the database. For more information, see Connect to a cluster.
  2. Create an external table in the adb_demo database. For more information, see Import OSS data to AnalyticDB for MySQL by using external tables.
  3. Select execute statements of write based on the external table type to write source data to the external table created in Step 2. For more information about the syntax supported by different external table types, see Syntax of common external tables that are not partitioned and Syntax of partition external tables.
  4. After the write task in Step 3 ends, you can log on to the OSS console and view the data exported to OSS in the folder.

Syntax of common external tables that are not partitioned

INSERT SELECT FROM
Feature: You can execute the INSERT SELECT FROM statement to copy data to external tables.
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: The OSS external table does not support the definition of a primary key. Therefore, the writing performance of REPLACE SELECT FROM is consistent with that of INSERT SELECT FROM. Data is copied to another table. If the data of destination table exists, 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 insert data to a table at a time. If data already exists in the external table, all objects under the original external table path are deleted before a new OSS object is generated.
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 to the OSS single object only in the CSV format. You cannot export data to a single object in the PARQUET format.
Feature: You can specify a unique OSS object by using hint to export data to this object. When the overwrite keyword is included, the file that has the same name in the directory defined in the TABLE_PROPERTIES external table is overwritten. Other files in the directory are not affected.
Version description:
  • AnalyticDB for MySQL V3.1.2 and earlier versions do not support this feature. The file names are automatically named by the system, and multiple files are exported. AnalyticDB for MySQL V3.1.2 and earlier versions dynamically determine the number of the files based on the concurrent task speed.
  • AnalyticDB for MySQL V3.1.2 and later: allow you to export AnalyticDB for MySQL external tables in the CSV format to the OSS single object by using hint. You can customize the file name when you export files. AnalyticDB for MySQL V3.1.2 and later can export multiple files without hint, which are the same as those of AnalyticDB for MySQL V3.1.2 and earlier versions.
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 of partition external tables

When partition table writes data, the data file does not contain the data of the partition key column data. The data information of the partition key column is displayed in the form of OSS folder.

For example, a partition table defines two partition key columns and three common columns. The hash partition is named pcol1, and the partition value is 1. The subpartition is named pcol2, and the partition value is a. The path for the partition table to export the data to OSS is adb_data/. If you write data to the external table partition when pcol1 is 1 and pcol2 is a, the relative path directory of the data file is: adb_data/pcol1=1/pcol2=a/. The CSV or Parquet data file of the external table does not contain the values of the pcol1 and pcol2 columns. Only the values of three common columns are included.

Note By default, in this section, the write feature of the partition external tables in the CSV and Parquet format is disabled. You can enable the write feature by using hint. The partition external tables in the CSV or Parquet format use the following statement:
/*+oss_parquet_write_enable=true*/ INSERT INTO ... 
INSERT INTO PARTITION SELECT FROM
Feature: You can execute the INSERT OVERWRITE INTO SELECT statement to insert data to a table that has partitions at a time. All partition information must be specified and the partition value cannot be empty.
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;
Status Error message Description
Supported. Append writes to corresponding partitions. All partition information must be specified and the partition value cannot be empty. The number of columns to be written must remain intact. You are not allowed to specify only part of columns to be written.
  • When the partition information is incomplete: Invalid: define partition columns number is partition_num,not equal to insert partition number m.
  • When the partition value is empty: Query execution error: : PARTITION value can not be null.
Writes data from the source table to the corresponding partition folder in the OSS location corresponding to the external table. Each time the data is written, a new OSS object is generated.
REPLACE INTO PARTITION SELECT FROM
Feature: External tables do not support the primary key, so REPLACE INTO PARTITION SELECT FROM behaves the same as INSERT INTO PARTITION SELECT FROM.
Syntax:
REPLACE into table_name PARTITION(par1=val1,par2=val2,...) 
SELECT select_statement FROM from_statement;   
Example:
REPLACE into oss_table_par PARTITION(par1=val1,par2=val2) 
select col1, col2, col3 from source_table;
INSERT OVERWRITE [INTO] PARTITION SELECT
Feature: You can execute the INSERT OVERWRITE [INTO] PARTITION [IF NOT EXISTS] SELECT statement to insert data to a table that has partitions at a time. All partition information must be specified and the partition value cannot be empty. If data already exists in the external table, all objects under the original external table path are deleted before a new OSS object is generated.
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;

Syntax that is not supported

AnalyticDB for MySQL V3.0 does not support the insert syntax for the custom row-level write. Unsupported syntax:

INSERT INTO VALUES
Syntax:
INSERT [IGNORE] 
    INTO table_name 
    [( column_name [, ...] )]
    [VALUES]
    [(value_list[, ...])]
    [query];     
REPLACE INTO VALUES
Syntax:
REPLACE 
    INTO table_name
    [(column_name,...)]
    VALUES
    VALUES ({Constant|NULL|DEFAULT},...),(...),...      
The partition external table does not allow you to write data to the INSERT SELECT FROM statement without specifying a partition
Syntax:
INSERT INTO table_name 
SELECT select_statement FROM from_statement;       
The partition external table cannot be exported to a single OSS object
Syntax:
/*output_filename=adb.txt*/INSERT into table_name PARTITION(par1=val1,par2=val2,...) 
SELECT select_statement FROM from_statement;

/*output_filename=adb.txt*/REPLACE into table_name PARTITION(par1=val1,par2=val2,...) 
SELECT select_statement FROM from_statement;

/*output_filename=adb.txt*/INSERT OVERWRITE [INTO] table_name PARTITION(par1=val1,par2=val2,...)[ IF NOT EXISTS] 
SELECT select_statement FROM from_statement;