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

Prerequisites

  • A directory for storing AnalyticDB for MySQL data is created in OSS through the following steps:
    1. Activate OSS
      Note OSS and AnalyticDB for MySQL must be located in the same region.
    2. Create buckets
    3. Create folders
    4. Upload test data files.

      In this example, the oss_import_test_data.txt file is uploaded to the directory bucket-name.oss-cn-hangzhou.aliyuncs.com/adb/ in OSS. The line delimiter is a line break and the column delimiter is a semicolon (;). The sample data in the uploaded object is as follows:

      0001;hello_world_1
      0002;hello_world_2
      0003;hello_world_3
      0004;hello_world_4
      0005;hello_world_5
      0006;hello_world_6
      ...                           
  • 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 oss_import_test_data.txt object is imported to the AnalyticDB for MySQL database named adb_demo.

  2. Execute the CREATE TABLE statement to create an external mapping table named oss_import_test_external_table in the adb_demo database. For more information, see CREATE TABLE.
    CREATE TABLE IF NOT EXISTS oss_import_test_external_table
    (
    uid string,
    other string
    )
    ENGINE='OSS'
    TABLE_PROPERTIES='{
    "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
    "url":"oss://bucket-name/adb/oss_import_test_data.txt",
    "accessid":"LTAIF****5FsE",
    "accesskey":"Ccw****iWjv",
    "delimiter":";",
    "skip_header_line_count":1
    }'                   
    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 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 absolute address of the oss_import_test_external_table object in OSS.
    accessid The AccessKey ID used to access the oss_import_test_external_table object.

    For more information about how to obtain your AccessKey ID and AccessKey secret, see Obtain the AccessKey pair of an account.

    accesskey The AccessKey secret used to access the oss_import_test_external_table object.
    delimiter The column delimiter of OSS objects.
    ossnull The display mode of the NULL value. Default value: EMPTY_SEPARATORS. Valid values:
    • 1. EMPTY_SEPARATORS

      a,"",,c --> "a","",NULL,"c"

    • 2. EMPTY_QUOTES

      a,"",,c --> "a",NULL,"","c"

    • 3. BOTH

      a,"",,c --> "a",NULL,NULL,"c"

    • 4. NEITHER

      a,"",,c --> "a","","","c"

    skip_header_line_count The first row of the OSS object is the table header and will be ignored during data import.
  3. Execute the CREATE TABLE statement to create a destination table named adb_oss_import_test in the adb_demo database to store the data imported from OSS. For more information, see CREATE TABLE.
    CREATE TABLE IF NOT EXISTS adb_oss_import_test
    (
    uid string,
    other string
    )
    DISTRIBUTED BY HASH(uid)                  
  4. Execute the INSERT statement to import data from OSS to AnalyticDB for MySQL.
    • Execute the INSERT INTO statement to import data.
      insert into adb_oss_import_test
      select * from oss_import_test_external_table                   
    • Execute the INSERT OVERWRITE INTO statement to import data.
      insert overwrite into adb_oss_import_test
      select * from oss_import_test_external_table                           
    • Execute the INSERT OVERWRITE INTO statement to import data asynchronously.
      submit job insert overwrite into adb_oss_import_test
      select * from oss_import_test_external_table ;
      +---------------------------------------+
      | job_id                                |
      +---------------------------------------+
      | 2020112122202917203100908203303000715 |

      For more information about how to submit asynchronous tasks, see Submit import or export tasks asynchronously.