This topic describes how to use AnalyticDB for MySQL external tables and the INSERT INTO statement to export data from AnalyticDB for MySQL to Object Storage Service (OSS).

Prerequisites

  • A directory for storing AnalyticDB for MySQL data in OSS is created by completing the following steps:
    1. For more information about how to activate OSS, see Activate OSS.
      Note OSS and AnalyticDB for MySQL must be located 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, create the adb_data/ folder in the bucket-name.oss-cn-hangzhou.aliyuncs.com/adb_data/ directory in OSS. The data exported from AnalyticDB for MySQL is stored in this folder.

      Create a folder
      Note When you export data from AnalyticDB for MySQL, the number of objects to be exported is automatically determined based on the transmission rate of concurrent tasks.
  • 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.

Procedure

  1. Connect to an AnalyticDB for MySQL cluster and the target database. For more information, see Connect to AnalyticDB for MySQL

    In this example, the data in the adb_oss_import_test table of the adb_demo database of AnalyticDB for MySQL is exported to the bucket-name.oss-cn-hangzhou.aliyuncs.com/adb_data/ directory in OSS.

    Note For more information about the adb_oss_import_test table, see Import data from OSS to AnalyticDB for MySQL.
  2. Execute the CREATE TABLE statement to create an external mapping table named oss_export_test_external_table in the adb_demo database.
    CREATE TABLE IF NOT EXISTS oss_export_test_external_table
    (
    uid string,
    other string
    )
    ENGINE='OSS'
    TABLE_PROPERTIES='{
    "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
    "url":"oss://bucket-name/adb_data/",
    "accessid":"LTAIF****5FsE",
    "accesskey":"Ccw****iWjv",
    "delimiter":";",
    }'                   
    Parameter Description
    ENGINE='OSS' Specifies the table as an external table whose storage engine is OSS.
    TABLE_PROPERTIES Specifies the connection information used by AnalyticDB for MySQL to access and write data to OSS.
    endpoint The endpoint of the OSS bucket.
    Note AnalyticDB for MySQL can access OSS only from ECS instances over the VPCs.

    You can log on to the OSS console, click the target bucket, and then view the Endpoint on the Overview page.

    url The URL of the folder in OSS. The URL must end with a forward slash (/).
    accessid The AccessKey ID used to access the bucket-name.oss-cn-hangzhou.aliyuncs.com/adb_data/ directory in OSS.

    For more information about how to obtain your AccessKey ID and AccessKey secret, see Alibaba Cloud Management console.

    accesskey The AccessKey secret used to access the bucket-name.oss-cn-hangzhou.aliyuncs.com/adb_data/ directory in OSS.
    delimiter The column delimiter of OSS objects.
  3. Execute the INSERT INTO statement to export data from AnalyticDB for MySQL to OSS
    insert into oss_export_test_external_table
    select * from adb_oss_import_test                 

    After the INSERT INTO statement is executed, you can log on to the OSS console to view the synchronized data in the adb_data folder.