AnalyticDB for MySQL allows you to use external tables to import and export data. This topic describes how to use an external mapping table of AnalyticDB for MySQL to export AnalyticDB for MySQL data to Apsara File Storage for HDFS.

Prerequisites

  • The version of the AnalyticDB for MySQL cluster is V3.1.4.4 or later. For more information, see Release notes.
  • An Apsara File Storage for HDFS cluster is created, and a folder is created in the cluster to store the imported AnalyticDB for MySQL data. In this example, the folder is named hdfs_output_test_csv_data.
    Note When you use the INSERT OVERWRITE statement to import data, the original file in the destination folder is overwritten. To avoid the original file from being overwritten, we recommend that you create another destination folder when you export data.
  • The following service access ports for the AnalyticDB for MySQL cluster are configured in the Apsara File Storage for HDFS cluster:
    • namenode: used to read and write metadata of a file system. You can configure the port number by using the fs.defaultFS parameter. The default port number is 8020.

      For detailed configurations, see core-default.xml.

    • datanode: used to read and write data. You can configure the port number by using the dfs.datanode.address parameter. The default port number is 50010.

      For detailed configurations, see hdfs-default.xml.

  • If the AnalyticDB for MySQL cluster is in elastic mode, you must turn on ENI in the Network Information section of the Cluster Information page. Enable ENI

Precautions

  • Only CSV and Parquet files can be exported from an AnalyticDB for MySQL cluster to an Apsara File Storage for HDFS cluster. ORC or TEXT files cannot be exported.
  • AnalyticDB for MySQL clusters do not support the INSERT syntax, such as INSERT INTO VALUES or REPLACE INTO VALUES. The INSERT syntax allows multiple rows to be inserted by using a single statement.
  • You cannot export individual files from AnalyticDB for MySQL to Apsara File Storage for HDFS by using partitioned external tables.
  • When you export data from partitioned external tables, the data files do not contain data of partition columns. The data of partition columns is displayed in the form of Apsara File Storage for HDFS directories.

    For example, three ordinary columns and two partition columns are defined in a partitioned external table. The name of the first-level partition column is p1, and the value of the column is 1. The name of the second-level partition column is p2, and the value of the column is a. You must use the partitioned external table to export data to the adb_data/ directory of an Apsara File Storage for HDFS cluster.

    If p1 is set to 1 and p2 is set to a, the relative path to which data is exported is adb_data/p1=1/p2=a/. The exported CSV or Parquet file contains only the values of the three ordinary columns and does not contain the values of the p1 and p2 columns.

Procedure

  1. Connect to the AnalyticDB for MySQL cluster. For more information, see Connect to an AnalyticDB for MySQL cluster.
  2. Create a source database. For more information, see Create a database.

    In this example, a database named adb_demo is used as the source database in the AnalyticDB for MySQL cluster.

  3. Create a source table and insert data into the source table.

    Execute the following statement to create a source table named adb_hdfs_import_source in the adb_demo source database:

    CREATE TABLE IF NOT EXISTS adb_hdfs_import_source
    (
        uid string,
        other string
    )
    DISTRIBUTED BY HASH(uid);
    Execute the following statement to insert a row of test data into the adb_hdfs_import_source table:
    INSERT INTO adb_hdfs_import_source VALUES ("1", "a"), ("2", "b"), ("3", "c");
  4. Create an external mapping table.

    You can use the following syntax to create an external mapping table in the adb_demo source database. The table is used to export AnalyticDB for MySQL data to Apsara File Storage for HDFS.

    • Execute the following statement to create a standard external mapping table. In the example, the destination table is named hdfs_import_external.
      CREATE TABLE IF NOT EXISTS hdfs_import_external
      (
          uid string,
          other string
      )
      ENGINE='HDFS'
      TABLE_PROPERTIES='{
          "format":"csv",
          "delimiter":",",
          "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_output_test_csv_data"
      }';
    • Execute the following statement to create a partitioned external mapping table. You must define ordinary columns (such as uid and other) and partition columns (such as p1, p2, and p3) in the statement. In the example, the destination table is named hdfs_import_external_par.
      CREATE TABLE IF NOT EXISTS hdfs_import_external_par
      (
          uid string,
          other string,
          p1 date,
          p2 int,
          p3 varchar
      )
      ENGINE='HDFS'
      TABLE_PROPERTIES='{
          "format":"csv",
          "delimiter":",",
          "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_output_test_csv_data"
          "partition_column":"p1, p2, p3"
      }';
    Note
  5. Export the source data from the AnalyticDB for MySQL cluster to Apsara File Storage for HDFS.

What to do next

After the data is exported, you can view the exported data in the hdfs_output_test_csv_data destination folder by using the Hadoop client. You can also log on to the AnalyticDB for MySQL cluster and execute the following statement to query the data exported by using an external table. The query syntax is the same for partitioned external tables and standard external tables. In the example, the hdfs_import_external standard external table is used.
SELECT * FROM hdfs_import_external LIMIT 100;

Appendix 1: Syntax to export data by using a standard external table

If partition columns are not specified when you create an external table, you can use one of the following methods to export data:

  • Method 1: If the destination table contains data, use the INSERT INTO statement to import data into the external table. Each time when you use this statement to write data from the source table to the Apsara File Storage for HDFS folder, a new Apsara File Storage for HDFS file is generated.
    Note The columns that you want to export must all be written to the external table. INSERT INTO generates new files without overwriting existing files.

    Syntax:

    INSERT INTO <target_table> 
    SELECT <col_name> FROM <source_table>;
    Example:
    INSERT INTO hdfs_import_external
    SELECT col1, col2, col3 FROM adb_hdfs_import_source;
    Note col1, col2, col3 indicate all columns of the external table.
  • Method 2: You cannot define primary keys in external tables for Apsara File Storage for HDFS. REPLACE INTO and INSERT INTO are completely equivalent. They both replicate data into external tables. If the destination table contains data, existing data remains unchanged and new data is appended to the destination file when you import data by executing the REPLACE INTO statement.
    Note
    • The columns that you want to export must all be written to the external table. You cannot specify specific columns to be written.
    • REPLACE INTO generates new files without overwriting existing files.

    Syntax:

    REPLACE INTO <target_table> 
    SELECT <col_name> FROM <source_table>;
    Example:
    REPLACE INTO hdfs_import_external
    SELECT col1, col2, col3 FROM adb_hdfs_import_source;
  • Method 3: Batch insert data into the external table by using the INSERT OVERWRITE INTO statement. If the destination external table already contains data, all files under the external table path are deleted before a new Apsara File Storage for HDFS file is generated.
    Notice
    • The columns that you want to export must all be written to the external table. You cannot specify specific columns to be written.
    • INSERT OVERWRITE INTO overwrites existing data in a directory. Proceed with caution.

    Syntax:

    INSERT OVERWRITE INTO <target_table> 
    SELECT <col_name> FROM <source_table>;
    Example:
    INSERT OVERWRITE INTO hdfs_import_external 
    SELECT col1, col2, col3 FROM adb_hdfs_import_source;
  • Method 4: Export data by asynchronously executing the INSERT OVERWRITE INTO statement. Syntax:
    SUBMIT job INSERT OVERWRITE INTO <target_table> 
    SELECT <col_name> FROM <source_table>;
    Example:
    SUBMIT JOB INSERT OVERWRITE INTO hdfs_import_external  
    SELECT col1, col2, col3 FROM adb_hdfs_import_source;
    The following result is returned:
    +---------------------------------------+
    | job_id                                |
    +---------------------------------------+
    | 2020112122202917203100908203303****** |
    +---------------------------------------+

    You can also check the state of the asynchronous task based on the job_id value. For more information, see Asynchronously submit an import or export task.

Appendix 2: Syntax to export data by using a partitioned external table

When you export data by using a partitioned external table, you must add the PARTITION field to the syntax. You can also specify partition columns and partition values in the PARTITION field to determine whether to use static or dynamic partitions.

  • Method 1: Use the INSERT INTO PARTITION statement to batch insert data into the partitioned external table.
    Note When you write data, the data is appended to the corresponding column. Each time when you write data, a new Apsara File Storage for HDFS file is generated, and existing data is not overwritten. 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 <target_table> PARTITION(par1=val1,par2=val2,...) 
      SELECT <col_name> FROM <source_table>;
      Example:
      INSERT INTO hdfs_import_external_par PARTITION(p1='2021-05-06',p2=1,p3='test') 
      SELECT col1, col2, col3, FROM adb_hdfs_import_source;
    • Static-dynamic partition
      Note Static partitions must be placed in front of dynamic partitions. You cannot change the sequence.
      Syntax:
      INSERT INTO <target_table> PARTITION(par1=val1,par2,...) 
      SELECT <col_name> FROM <source_table>;
      Example:
      INSERT INTO hdfs_import_external_par PARTITION(p1='2021-05-27',p2,p3)  
      SELECT col1, col2, col3, FROM adb_hdfs_import_source;
    • Fully dynamic partition (partition that does not require the PARTITION field)
      Syntax:
      INSERT INTO <target_table> 
      SELECT <col_name> FROM <source_table>;
      Example:
      INSERT INTO hdfs_import_external_par  
      SELECT col1, col2, col3, FROM adb_hdfs_import_source;
  • Method 2: You cannot define primary keys in external tables for Apsara File Storage for HDFS. REPLACE INTO PARTITION and INSERT INTO are completely equivalent.
    Note The columns that you want to export must all be written to the external table. You cannot specify specific columns to be written. REPLACE INTO PARTITION generates new files without overwriting existing files.

    Syntax:

    • Fully static partition
      Syntax:
      REPLACE INTO <target_table> PARTITION(par1=val1,par2=val2,...) 
      SELECT <col_name> FROM <source_table>;
      Example:
      REPLACE INTO hdfs_import_external_par PARTITION(p1='2021-05-06',p2=1,p3='test') 
      SELECT col1, col2, col3, FROM adb_hdfs_import_source;
    • Static-dynamic partition
      Note Static partitions must be placed in front of dynamic partitions. You cannot change the sequence.
      Syntax:
      REPLACE INTO <target_table> PARTITION(par1=val1,par2,...) 
      SELECT <col_name> FROM <source_table>;
      Example:
      REPLACE INTO hdfs_import_external_par PARTITION(p1='2021-05-06',p2,p3)  
      SELECT col1, col2, col3, FROM adb_hdfs_import_source;
    • Fully dynamic partition (partition that does not require the PARTITION field)
      Syntax:
      REPLACE INTO <target_table> 
      SELECT <col_name> FROM <source_table>;
      Example:
      REPLACE INTO hdfs_import_external_par  
      SELECT col1, col2, col3, FROM adb_hdfs_import_source;
  • Method 3: The INSERT OVERWRITE [INTO] PARTITION statement can be used in the same manner as the INSERT INTO PARTITION statement. However, when you execute the INSERT OVERWRITE [INTO] PARTITION statement, existing data in the destination partitions is overwritten. If no new data is written to the partitions, exiting data is not cleared.

    Syntax:

    INSERT OVERWRITE [INTO] <target_table> PARTITION(par1=val1,par2=val2,...)[IF NOT EXISTS] 
    SELECT <col_name> FROM <source_table>;
    Notice
    • The columns that you want to export must all be written to the external table. You cannot specify specific columns to be written. INSERT OVERWRITE [INTO] PARTITION overwrites existing data in a directory. Proceed with caution.
    • IF NOT EXISTS: indicates that if an external table has a partition, data is not imported into this partition.
    Example:
    INSERT OVERWRITE INTO hdfs_import_external_par PARTITION(p1='2021-05-06',p2=1,p3='test') IF NOT EXISTS 
    SELECT col1, col2, col3 FROM adb_hdfs_import_source;
  • Method 4: Export data by asynchronously executing the INSERT OVERWRITE INTO statement. Syntax:
    SUBMIT JOB INSERT OVERWRITE INTO <target_table> 
    SELECT <col_name> FROM <source_table>;
    Example:
    SUBMIT JOB INSERT OVERWRITE INTO hdfs_import_external_par PARTITION(p1='2021-05-06',p2=1,p3='test') IF NOT EXISTS
    SELECT col1, col2, col3 FROM adb_hdfs_import_source;
    The following result is returned:
    +---------------------------------------+
    | job_id                                |
    +---------------------------------------+
    | 2020112122202917203100908203303****** |
    +---------------------------------------+

    You can also check the state of the asynchronous task based on the job_id value. For more information, see Asynchronously submit an import or export task.