AnalyticDB:Use external tables to import data to Data Warehouse Edition

Prerequisites

• An AnalyticDB for MySQL Data Warehouse Edition cluster is created.

  Note

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

• An OSS bucket is created in the same region as the AnalyticDB for MySQL cluster. For more information, see Activate OSS.

Sample data

In this example, the oss_import_test_data.csv object is uploaded to the testBucketname/adb/ directory in OSS. The row delimiter is a line feed, and the column delimiter is a semicolon (;) The following code shows part of the data contained in the oss_import_test_data.csv object:

uid;other
12;hello_world_1
27;hello_world_2
28;hello_world_3
33;hello_world_4
37;hello_world_5
40;hello_world_6        

Procedure

1. Create a database in the AnalyticDB for MySQL cluster.

   CREATE DATABASE adb_demo;

2. Create an external table. You can execute the CREATE TABLE statement to create an OSS external table in the CSV, Parquet, or ORC format. For more information, see the "Syntax for creating OSS external tables" section of this topic.

   In this example, a non-partitioned external table named oss_import_test_external_table in the CSV format is used.

   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://testBucketname/adb/oss_import_test_data.csv",
       "accessid":"LTAIF****5FsE",
       "accesskey":"Ccw****iWjv",
       "delimiter":";",
       "skip_header_line_count":1,
       "charset":"utf-8"
   }'; 

3. Query the data of the oss_import_test_external_table external table.

   Note

   For queries on CSV, Parquet, and ORC objects, a larger amount of data results in greater performance loss. To improve query efficiency, we recommend that you perform the following steps (4 and 5) to import the data of the OSS external table to AnalyticDB for MySQL for query.

   SELECT uid, other FROM oss_import_test_external_table WHERE uid < 100 LIMIT 10;

4. Create a table in the AnalyticDB for MySQL cluster to store data that is imported from the OSS external table.

   CREATE TABLE IF NOT EXISTS adb_oss_import_test
   (
       uid string,
       other string
   )
   DISTRIBUTED BY HASH(uid);

5. Execute an INSERT statement to import data from the OSS external table to AnalyticDB for MySQL.

   Important

   By default, the INSERT INTO and INSERT OVERWRITE SELECT statements import data synchronously. If hundreds of gigabytes of data is imported, the client must maintain a connection with the AnalyticDB for MySQL server for an extended period of time. During this process, the import may fail due to a network disconnection. Therefore, if you want to import a large amount of data, we recommend that you execute the SUBMIT JOB INSERT OVERWRITE SELECT statement to import data asynchronously.

   • Method 1: Execute the INSERT INTO statement to import data. If the primary key has duplicate values, data is not repeatedly inserted and the INSERT INTO statement is equivalent to the INSERT IGNORE INTO statement. For more information, see INSERT INTO.

     INSERT INTO adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • Method 2: Execute the INSERT OVERWRITE statement to import data. If the primary key has duplicate values, the original value is overwritten by the new value.

     INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • Method 3: Execute the INSERT OVERWRITE statement to import data asynchronously. In most cases, the SUBMIT JOB statement is used to submit an asynchronous job. You can add a hint (/*+ direct_batch_load=true*/) before the data import statement to accelerate the job. For more information, see the "Asynchronous writing" section of the INSERT OVERWRITE SELECT topic.

     SUBMIT JOB INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

     Sample result:

     +---------------------------------------+
     | job_id                                |
     +---------------------------------------+
     | 2020112122202917203100908203303****** |

     For information about how to submit asynchronous jobs, see Asynchronously submit an import job.

Syntax for creating OSS external tables

CREATE TABLE [IF NOT EXISTS] table_name
(column_name column_type[, ...])
ENGINE='OSS'
TABLE_PROPERTIES='{
    "endpoint":"endpoint",
    "url":"OSS_LOCATION",
    "accessid":"accesskey_id",
    "accesskey":"accesskey_secret",
    "format":"txt|orc|parquet",
    "delimiter":";",
    "skip_header_line_count":1,
    "charset":"utf-8"
}';

CREATE TABLE adb_csv_hive_format_oss (
  a tinyint,
  b smallint,
  c int,
  d bigint,
  e boolean,
  f float,
  g double,
  h varchar,
  i varchar, -- binary
  j timestamp,
  k DECIMAL(10, 4),
  l varchar, -- char(10)
  m varchar, -- varchar(100)
  n date
) ENGINE = 'OSS' TABLE_PROPERTIES='{
    "format": "csv",
    "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
    "accessid":"LTAIF****5FsE",
    "accesskey":"Ccw****iWjv",
    "url":"oss://testBucketname/adb_data/",
    "delimiter": "\\1",
    "null_value": "\\\\N",
    "oss_ignore_quote_and_escape": "true",
    "ossnull": 2
}';

parquet_partition_classic/
├── p1=2020-01-01
│ ├── p2=4
│ │ ├── p3=SHANGHAI
│ │ │ ├── 000000_0
│           └── 000000_1
│       └── p3=SHENZHEN
│ │     └── 000000_0
│ └── p2=6
│     └── p3=SHENZHEN
│         └── 000000_0
├── p1=2020-01-02
│ └── p2=8
│     ├── p3=SHANGHAI
│     │ └── 000000_0
│     └── p3=SHENZHEN
│         └── 000000_0
└── p1=2020-01-03
    └── p2=6
        ├── p2=HANGZHOU
        └── p3=SHENZHEN
            └── 000000_0

CREATE TABLE [IF NOT EXISTS] table_name
(column_name column_type[, ...])
ENGINE='OSS'
TABLE_PROPERTIES='{
    "endpoint":"endpoint",
    "url":"OSS_LOCATION",
    "accessid":"accesskey_id",
    "accesskey":"accesskey_secret",
    "format":"parquet",
    "partition_column":"p1, p2, p3"
}';