AnalyticDB:Export data to OSS

Procedure

In this example, data is exported from the source_table table in the adb_demo database of the AnalyticDB for MySQL Data Warehouse Edition (V3.0) 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 the "Create an OSS external table for a non-partitioned object" section of the Use external tables to import data to Data Warehouse Edition topic.

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 the "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" sections of this topic.

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.

    Important

    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;

• INSERT OVERWRITE SELECT

  • Feature: You can execute the INSERT OVERWRITE 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.

    Important

    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 table_name 
    select_statement;

    Example:

    INSERT OVERWRITE 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.

• 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.

    Important

    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

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.

• 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. In this case, 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

INSERT INTO table_name PARTITION(par1=val1,par2=val2,...) 
select_statement;

INSERT INTO oss_table_par PARTITION(par1=val1,par2=val2) 
select col1, col2, col3 FROM source_table;

Static-dynamic partition

INSERT INTO table_name PARTITION(par1=val1,par2,...) 
select_statement;

INSERT INTO oss_table_par PARTITION(par1=val1,par2) 
SELECT col1, col2, col3, par2col FROM source_table;

Fully dynamic partition

INSERT INTO table_name 
select_statement;

INSERT INTO oss_table_par
SELECT col1, col2, col3, par1col, par2col FROM source_table;

• INSERT OVERWRITE PARTITION SELECT

Feature: The INSERT OVERWRITE PARTITION SELECT statement is used in the same manner as the INSERT INTO PARTITION SELECT statement. However, when you execute the INSERT OVERWRITE PARTITION SELECT statement, existing objects in the desired partitions are cleared. Objects in partitions that have no new data written are not cleared.

INSERT OVERWRITE table_name PARTITION(par1=val1,par2=val2,...)[IF NOT EXISTS] 
select_statement;

INSERT OVERWRITE oss_table_par PARTITION(par1=val1,par2=val2) IF NOT EXISTS 
SELECT col1, col2, col3 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.

Unsupported statements

AnalyticDB for MySQL does not allow you to insert data to external tables by row. INSERT INTO VALUES and REPLACE INTO VALUES are not supported.