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

Prerequisites

  • A destination table is created. For more information, see Preparations and Quick Start.

    The following table is created in MaxCompute by executing the table creation statement. For more information, see Table-level operations. If you have created the destination table, skip this step.

    • Create a table that has level-1 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 level-2 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 Quick start of AnalyticDB for MySQL.
Note If AnalyticDB MySQL cluster is in elastic mode, log on to the AnalyticDB MySQL console and view the cluster information. Enable Elastic Network Interface (ENI) in the Network Information section.net

Export data to a MaxCompute table that has level-1 partitions

Data from the adb_table table in AnalyticDB for MySQL is exported to the odps_table table that has level-1 partitions in MaxCompute in the following example:

  1. Connect to an AnalyticDB for MySQL cluster and log on to the source database. For more information, see Connect to a 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 used for the external table, which is MaxCompute.
    TABLE_PROPERTIES The connection information 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 the VPC endpoint.

    For more information, see Configure endpoints.

    accessid The AccessKey ID used to access the destination table in MaxCompute.
    accesskey The AccessKey secret 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 level-1 partitions.
    Note adb_table_column does not contain the partition fields.
    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 level-2 partitions

Data is exported from the adb_table table in AnalyticDB for MySQL to the odps_table table that has level-2 partitions in MaxCompute in the following example:

  1. Connect to an AnalyticDB for MySQL cluster and log on to the source database. For more information, see Connect to a 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 level-2 partitions.
    Note adb_table_column does not contain the partition fields.
    insert [overwrite] into odps_external_table partition(ds='20200401',other='hangzhou')
    select [adb_table_column, ...] from adb_table [where ...]