AnalyticDB：通過外表匯入HDFS/AWS/Azure/GCP資料

前提條件

• AnalyticDB for MySQL叢集核心版本需為3.1.4及以上。

  說明

  請在雲原生資料倉儲AnalyticDB MySQL控制台集群信息頁面，配寘資訊地區，查看和升級核心版本。

• HDFS資料檔案格式需為CSV、Parquet或ORC。

• 已建立HDFS叢集並在HDFS檔案夾中準備需要匯入的資料，本文樣本中所用檔案夾為hdfs_import_test_data.csv。

• 已在HDFS叢集中為AnalyticDB for MySQL叢集配置如下服務訪問連接埠：

  • namenode：用於讀寫檔案系統元資訊。您可以在fs.defaultFS參數中配置連接埠號碼，預設連接埠號碼為8020。

    詳細配置方式，請參見core-default.xml。

  • datanode：用於讀寫資料。您可以在dfs.datanode.address參數中配置連接埠號碼，預設連接埠號碼為50010。

    詳細配置方式，請參見hdfs-default.xml。

• AnalyticDB for MySQL數倉版彈性模式已開啟ENI訪問。

  重要

  • 登入雲原生資料倉儲AnalyticDB MySQL控制台，在叢集資訊頁面的網路資訊地區，開啟ENI網路開關。

  • 開啟和關閉ENI網路會導致資料庫連接中斷大約2分鐘，無法讀寫。請謹慎評估影響後再開啟或關閉ENI網路。

操作步驟

1. （可選）配置公網訪問能力。

   當您需要通過外表串連其他雲廠商的Object Storage Service（AWS S3、Azure Blob Storage或Google Cloud Storage）時，需確保AnalyticDB for MySQL叢集具備公網訪問能力。

   • 為AnalyticDB for MySQL叢集所在的VPC配置NAT Gateway和Elastic IP Address（EIP）。

     1. 建立公網NAT Gateway。

        公網NAT Gateway需要與AnalyticDB for MySQL執行個體為同一個地區。

     2. 綁定Elastic IP Address（EIP）。

     3. 建立SNAT條目。

        推薦按交換器粒度建立SNAT條目，指定任意交換器即可。

   • 為AnalyticDB for MySQL叢集開啟ENI訪問。

     重要

     • 登入雲原生資料倉儲AnalyticDB MySQL控制台，在叢集資訊頁面的網路資訊地區，開啟ENI網路開關。

     • 開啟和關閉ENI網路會導致資料庫連接中斷大約2分鐘，無法讀寫。請謹慎評估影響後再開啟或關閉ENI網路。

2. 建立目標資料庫。本樣本中，AnalyticDB for MySQL叢集的目標庫名為adb_demo。

   CREATE DATABASE IF NOT EXISTS adb_demo;    

3. 使用CREATE TABLE語句在目標庫adb_demo中建立CSV、Parquet或ORC格式的外表。

   • 使用HDFS資料來源：

     • 建立HDFS外表。

     • 建立帶分區的HDFS外表。

   • 串連其他雲廠商的Object Storage Service（AWS S3、Azure Blob Storage或Google Cloud Storage）：建立外部雲端儲存外表。

4. 建立目標表。

   您可以使用以下語句在目標資料庫adb_demo中建立一張目標表，用於儲存從HDFS匯入的資料：

   • 建立普通外表對應的目標表（本文樣本中目標表名為adb_hdfs_import_test），文法如下。

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

   • 建立帶分區外表對應的目標表時（本文樣本中目標表名為adb_hdfs_import_parquet_partition），需要同時在建立語句中定義普通列（如uid和other）和分區列（如p1、p2和p3），文法如下。

     CREATE TABLE IF NOT EXISTS adb_hdfs_import_parquet_partition
     (
         uid string,
         other string,
         p1 date,
         p2 int,
         p3 varchar
     )
     DISTRIBUTED BY HASH(uid);

5. 將HDFS中的資料匯入至目標AnalyticDB for MySQL叢集中。

   您可以根據業務需要選擇如下幾種方式匯入資料（分區表匯入資料文法與普通表一致，如下樣本中以普通表為例）：

   • （推薦）方式一：使用INSERT OVERWRITE匯入資料。資料大量匯入，效能好。匯入成功後資料可見，匯入失敗資料會復原，樣本如下。

     INSERT OVERWRITE adb_hdfs_import_test
     SELECT * FROM hdfs_import_test_external_table;

   • 方式二：使用INSERT INTO匯入資料。資料插入即時可查，資料量較小時使用，樣本如下。

     INSERT INTO adb_hdfs_import_test
     SELECT * FROM hdfs_import_test_external_table;

   • 方式三：非同步執行匯入資料，樣本如下。

     SUBMIT JOB INSERT OVERWRITE adb_hdfs_import_test
     SELECT * FROM hdfs_import_test_external_table;

     返回結果如下。

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

     您還可以根據上述job_id查看非同步任務的狀態，更多詳情，請參見非同步提交匯入任務。

後續步驟

SELECT * FROM adb_hdfs_import_test LIMIT 100;

建立HDFS外表

• 建立檔案格式為CSV的外表

  語句如下：

  CREATE TABLE IF NOT EXISTS hdfs_import_test_external_table
  (
      uid string,
      other string
  )
  ENGINE='HDFS'
  TABLE_PROPERTIES='{
      "format":"csv",
      "delimiter":",",
      "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_import_test_csv_data/hdfs_import_test_data.csv"
  }';

  參數	是否必填	說明
  ENGINE='HDFS'	必填	外表的儲存引擎說明。本樣本使用的儲存引擎為HDFS。
  TABLE_PROPERTIES		AnalyticDB for MySQL訪問HDFS資料的方式。
  format		資料檔案的格式。建立CSV格式檔案的外表時需設定為csv。
  delimiter		定義CSV資料檔案的資料行分隔符號。本樣本使用的分隔字元為英文逗號（,）。
  hdfs_url		HDFS叢集中目標資料檔案或檔案夾的絕對位址，需要以hdfs://開頭。 樣本：hdfs://172.17.***.***:9000/adb/hdfs_import_test_csv_data/hdfs_import_test_data.csv
  partition_column	選填	定義外表的分區列，用英文逗號（,）切分各列。定義分區列的方法，請參見建立帶分區的HDFS外表。
  compress_type		定義資料檔案的壓縮類型，CSV格式的檔案目前僅支援Gzip壓縮類型。
  skip_header_line_count		定義匯入資料時需要在開頭跳過的行數。CSV檔案第一行為表頭，若設定該參數為1，匯入資料時可自動跳過第一行的表頭。 預設為0，即不跳過。
  hdfs_ha_host_port		如果HDFS叢集配置了HA功能，建立外表時需配置hdfs_ha_host_port參數，格式為ip1:port1,ip2:port2，參數中的IP與Port是主備namenode的IP與Port。 樣本：192.168.xx.xx:8020,192.168.xx.xx:8021

• 建立HDFS Parquet格式/HDFS ORC格式的外表

  以Parquet格式為例，建立HDFS外表語句如下：

  CREATE TABLE IF NOT EXISTS hdfs_import_test_external_table
  (
      uid string,
      other string
  )
  ENGINE='HDFS'
  TABLE_PROPERTIES='{
      "format":"parquet",
      "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_import_test_parquet_data/"
  }';

  參數	是否必填	說明
  ENGINE='HDFS'	必填	外表的儲存引擎說明。本樣本使用的儲存引擎為HDFS。
  TABLE_PROPERTIES		AnalyticDB for MySQL訪問HDFS資料的方式。
  format		資料檔案的格式。 建立Parquet格式檔案的外表時需設定為parquet。 建立ORC格式檔案的外表時需設定為orc。
  hdfs_url		HDFS叢集中目標資料檔案或檔案夾的絕對位址，需要以hdfs://開頭。
  partition_column	選填	定義表的分區列，用英文逗號（,）切分各列。定義分區列的方法，請參見建立帶分區的HDFS外表。
  hdfs_ha_host_port		如果HDFS叢集配置了HA功能，建立外表時需配置hdfs_ha_host_port參數，格式為ip1:port1,ip2:port2，參數中的IP與Port是主備namenode的IP與Port。 樣本：192.168.xx.xx:8020,192.168.xx.xx:8021

  說明

  • 外表建立語句中的列名需與Parquet或ORC檔案中該列的名稱完全相同（可忽略大小寫），且列的順序需要一致。

  • 建立外表時，可以僅選擇Parquet或ORC檔案中的部分列作為外表中的列，未被選擇的列不會被匯入。

  • 如果建立外表建立語句中出現了Parquet或ORC檔案中不存在的列，針對該列的查詢結果均會返回NULL。

  Parquet檔案與AnalyticDB for MySQL的資料類型映射關係

  Parquet基本類型	Parquet的logicalType類型	AnalyticDB for MySQL的資料類型
  BOOLEAN	無	BOOLEAN
  INT32	INT_8	TINYINT
  INT32	INT_16	SMALLINT
  INT32	無	INT或INTEGER
  INT64	無	BIGINT
  FLOAT	無	FLOAT
  DOUBLE	無	DOUBLE
  FIXED_LEN_BYTE_ARRAY BINARY INT64 INT32	DECIMAL	DECIMAL
  BINARY	UTF-8	VARCHAR STRING JSON（如果已知Parquet該列內容為JSON格式）
  INT32	DATE	DATE
  INT64	TIMESTAMP_MILLIS	TIMESTAMP或DATETIME
  INT96	無	TIMESTAMP或DATETIME

  重要

  Parquet格式外表暫不支援STRUCT類型，會導致建表失敗。

  ORC檔案與AnalyticDB for MySQL的資料類型映射關係

  ORC檔案中的資料類型	AnalyticDB for MySQL中的資料類型
  BOOLEAN	BOOLEAN
  BYTE	TINYINT
  SHORT	SMALLINT
  INT	INT或INTEGER
  LONG	BIGINT
  DECIMAL	DECIMAL
  FLOAT	FLOAT
  DOUBLE	DOUBLE
  BINARY STRING VARCHAR	VARCHAR STRING JSON（如果已知ORC該列內容為JSON格式）
  TIMESTAMP	TIMESTAMP或DATETIME
  DATE	DATE

  重要

  ORC格式外表暫不支援LIST、STRUCT和UNION等複合類型，會導致建表失敗。ORC格式外表的列使用MAP類型可以建表，但ORC的查詢會失敗。

建立帶分區的HDFS外表

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 hdfs_parquet_partition_table
(
  uid varchar,
  other varchar,
  p1 date,
  p2 int,
  p3 varchar
)
ENGINE='HDFS'
TABLE_PROPERTIES='{
  "hdfs_url":"hdfs://172.17.***.**:9000/adb/parquet_partition_classic/",
  "format":"parquet",  //如需建立CSV或ORC格式外表，僅需將format的取值改為csv或orc。
  "partition_column":"p1, p2, p3"  //針對包含分區的HDFS資料，如需以分區的模式進行查詢，那麼在匯入資料至AnalyticDB MySQL時就需要在外表建立語句中指定分區列partition_column。
}';

建立外部雲端儲存外表

{
   "Version": "2012-10-17",
   "Statement": [
     {
       "Effect": "Allow",
       "Action": [
         "s3:Get*",
         "s3:List*",
         "s3:Describe*",
         "s3-object-lambda:Get*",
         "s3-object-lambda:List*"
       ],
       "Resource": "*"
     }
   ]
}

{
   "Version": "2012-10-17",
   "Statement": [
     {
       "Effect": "Allow",
       "Action": [
         "s3:",
         "s3-object-lambda:"
       ],
       "Resource": "*"
     }
   ]
}

CREATE TABLE t2(c1 int, c2 int, p1 int)
ENGINE='hdfs'
TABLE_PROPERTIES='{
  "partition_column":"p1",
  "format" : "parquet",
  "hdfs_url" : "gs://adbtest2/tbls/table1",
  "gcs.project_id":"test-project",
  "gcs.client_email":"adbtest@test-project.iam.gserviceaccount.com",
  "gcs.token_uri":"https://oauth2.googleapis.cn/token",
  "gcs.private_key_id":"xxxx",
  "gcs.private_key":"-----BEGIN PRIVATE KEY-----\nMIIEvgIBADANBgkqhkiG9w0BAQEFA****-----END PRIVATE KEY-----\n"
}'