This topic describes how to use external tables to export AnalyticDB for MySQL data to MaxCompute partitioned tables.

Prerequisites

  • A destination table is created in MaxCompute. For more information, see Create an Alibaba Cloud account and Create a table.

    For example, one of the following tables is created in MaxCompute by executing a table creation statement. For more information, see Table operations. If you have created a destination table, skip this step.

    Note To export data to a MaxCompute partitioned table, you must specify a partition to write data. Data can be exported to multiple partitions only by executing multiple SQL statements. MaxCompute supports up to six levels of partitions. You can reference the preceding operations to create tables that have higher levels of partitions.
    • Create a table that has hash partitions
      CREATE TABLE IF NOT EXISTS odps_table
      (
      uid STRING,
      other STRING,
      ds STRING
       )
      PARTITIONED BY (ds STRING)
      LIFECYCLE 3;     
    • Create a table that has list partitions
      CREATE TABLE IF NOT EXISTS odps_table
      (
      uid STRING,
      other STRING,
      ds STRING
       )
      PARTITIONED BY (ds STRING,other STRING)
      LIFECYCLE 3;     
  • 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.
  • 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

Export data to a MaxCompute table that has hash partitions

In the following example, data is exported from the adb_table table in AnalyticDB for MySQL to the odps_table table that has hash partitions in MaxCompute:

  1. Connect to the AnalyticDB for MySQL cluster. For more information, see Connect to an AnalyticDB for MySQL cluster.
  2. Create an external table that maps to the destination table in MaxCompute.
    CREATE TABLE odps_external_table
    (
    uid string,
    other string,
    ds string
    )
    ENGINE='ODPS'
    TABLE_PROPERTIES='{
    "endpoint":"http://service.odps.aliyun-inc.com/api",
    "accessid":"xxx",
    "accesskey":"xxx",
    "project_name":"xxx",
    "table_name":"odps_table",
    "partition_column":"ds"
    }'
    Parameter Description
    ENGINE='ODPS' The engine that is used for the external table, which is MaxCompute.
    TABLE_PROPERTIES The connection information that is used by AnalyticDB for MySQL to access and write data to MaxCompute.
    endpoint The endpoint of MaxCompute.
    Note AnalyticDB for MySQL can access MaxCompute only by using VPC endpoints.

    For more information about how to view MaxCompute endpoints, see Configure endpoints.

    accessid The AccessKey ID that is used to access the destination table in MaxCompute.
    accesskey The AccessKey secret that is used to access the destination table in MaxCompute.
    project_name The name of the project to which the destination table belongs in MaxCompute.
    table_name The name of the destination table in MaxCompute.
    partition_column The partition field.
  3. Export data from the AnalyticDB for MySQL external table to the MaxCompute table that has hash partitions.
    Note adb_table_column does not contain the partition field.
    insert [overwrite] into odps_external_table partition(ds='20200401')
    select [adb_table_column, ...] from adb_table [where ...]

Export data to a MaxCompute table that has list partitions

In the following example, data is exported from the adb_table table in AnalyticDB for MySQL to the odps_table table that has list partitions in MaxCompute:

  1. Connect to the AnalyticDB for MySQL cluster. For more information, see Connect to an AnalyticDB for MySQL cluster.
  2. Create an external table that maps to the destination table in MaxCompute.
    CREATE TABLE odps_external_table
    (
    uid string,
    other string,
    ds string
    )
    ENGINE='ODPS'
    TABLE_PROPERTIES='{
    "endpoint":"http://service.odps.aliyun-inc.com/api",
    "accessid":"xxx",
    "accesskey":"xxx",
    "project_name":"xxx",
    "table_name":"odps_table",
    "partition_column":"ds,other"
    }'
  3. Export data from the AnalyticDB for MySQL external table to the MaxCompute table that has list partitions.
    Note adb_table_column does not contain the partition field.
    insert [overwrite] into odps_external_table partition(ds='20200401',other='hangzhou')
    select [adb_table_column, ...] from adb_table [where ...]