AnalyticDB：通過外表匯入至數倉版

前提條件

• AnalyticDB for MySQL叢集的產品系列為數倉版。

  說明

  數倉版彈性模式叢集需要在集群資訊頁面的網路資訊地區，開啟啟用ENI網絡的開關。

• AnalyticDB for MySQL叢集與OSS儲存空間位於同一地區。具體操作，請參見開通OSS服務。

樣本資料說明

本樣本將oss_import_test_data.csv檔案上傳至OSS中的testBucketname/adb/目錄，資料行分隔字元為分行符號，資料行分隔符號為半形分號（;）。oss_import_test_data.csv檔案樣本資料如下所示：

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        

操作步驟

1. 在AnalyticDB for MySQL中建立資料庫。

   CREATE DATABASE adb_demo;

2. 建立外表。使用CREATE TABLE文法建立CSV、Parquet或ORC格式的OSS外表。具體文法，請參見OSS外表文法。

   本文樣本以CSV格式非分區外表為例。

   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. 查詢oss_import_test_external_table外表資料。

   說明

   對於CSV格式、Parquet和ORC格式資料檔案，資料量越大，通過外表查詢的效能損耗越大。如果您需要進一步提升查詢效率，建議您按照後續步驟（4~5）將OSS外表資料匯入AnalyticDB for MySQL後再做查詢。

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

4. 在AnalyticDB for MySQL中建立表，用於儲存從OSS外表匯入的資料。

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

5. 執行INSERT語句將OSS外表資料匯入AnalyticDB for MySQL。

   重要

   使用INSERT INTO或INSERT OVERWRITE SELECT匯入資料時，預設是同步執行流程。如果資料量較大，達到幾百GB，用戶端到AnalyticDB for MySQL服務端的串連需要保持較長時間。在此期間，可能會因為網路因素導致串連中斷，進而導致資料匯入失敗。因此，如果您的資料量較大時，推薦使用SUBMIT JOB INSERT OVERWRITE SELECT非同步執行匯入。

   • 方式一：執行INSERT INTO匯入資料，當主鍵重複時會自動忽略當前寫入資料，不進行更新覆蓋，作用等同於INSERT IGNORE INTO，詳情請參見INSERT INTO。樣本如下：

     INSERT INTO adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • 方式二：執行INSERT OVERWRITE匯入資料，會覆蓋表中原有的資料。樣本如下：

     INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

   • 方式三：非同步執行INSERT OVERWRITE匯入資料。 通常使用SUBMIT JOB提交非同步任務，由後台調度，可以在寫入任務前增加Hint（/*+ direct_batch_load=true*/）加速寫入任務。詳情請參見非同步寫入。樣本如下：

     SUBMIT JOB INSERT OVERWRITE adb_oss_import_test
     SELECT * FROM oss_import_test_external_table;

     返回結果如下：

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

     關於非同步提交任務詳情，請參見非同步提交匯入任務。

OSS外表文法

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":"text|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"
}';