通過外表將OSS中的資料匯入至數倉版 - AnalyticDB

本文介紹如何通過外表查詢OSS資料檔案，並將OSS中的資料檔案匯入AnalyticDB for MySQL數倉版。目前支援的OSS資料檔案格式有Parquet、CSV和ORC。

前提條件

已開通OSS服務並建立儲存空間和專案。具體操作，請參見開通OSS服務、控制台建立儲存空間和建立目錄。
已在OSS服務所在的同一VPC中建立AnalyticDB for MySQL數倉版叢集，並完成設定白名單、建立帳號等準備工作。詳情請參見數倉版的入門使用流程。
AnalyticDB for MySQL數倉版彈性模式叢集，您需要在集群資訊頁面的網路資訊地區，開啟啟用ENI網絡的開關。

樣本資料說明

本樣本將oss_import_test_data.csv檔案上傳至OSS中的<bucket-name>.oss-cn-hangzhou.aliyuncs.com/adb/目錄，資料行分隔字元為分行符號，資料行分隔符號為半形分號（;），檔案樣本資料如下所示：

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
...

操作步驟

本樣本將oss_import_test_data.txt中的資料匯入AnalyticDB for MySQL的adb_demo庫中。

串連目標AnalyticDB for MySQL叢集。詳細操作步驟，請參見串連叢集。
建立目標資料庫。詳細操作步驟，請參見建立資料庫。
本樣本中，AnalyticDB for MySQL叢集的目標庫名為adb_demo。
建立外部映射表。使用CREATE TABLE語句在目標庫adb_demo中建立CSV、Parquet或ORC格式的OSS外部映射表。具體文法，請參見不帶分區的資料檔案建立OSS外表或帶分區的資料檔案建立OSS外表。
查詢OSS資料。本步驟以查詢外部映射表oss_import_test_external_table為例，查詢對應OSS中的資料。
查詢外部映射表和查詢AnalyticDB for MySQL內表文法相同，您可以直接進行查詢，查詢語句如下：
```
SELECT uid, other FROM oss_import_test_external_table WHERE uid < 100 LIMIT 10;
```
說明
對於CSV格式、Parquet和ORC格式資料檔案，資料量越大，通過外表查詢的效能損耗越大。如果您需要進一步提升查詢效率，建議您按照後續步驟將OSS資料匯入AnalyticDB for MySQL後再做查詢。
建立目標表。在目標資料庫adb_demo中建立一張目標表adb_oss_import_test，用於儲存從OSS匯入的資料。建表語句如下：
```
CREATE TABLE IF NOT EXISTS adb_oss_import_test
(
    uid string,
    other string
)
DISTRIBUTED BY HASH(uid);
```
執行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外表

建立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://<bucket-name>/adb_data/",
    "accessid":"LTAIF****5FsE",
    "accesskey":"Ccw****iWjv",
    "delimiter":";",
    "skip_header_line_count":1,
}';

參數	是否必填	說明
`ENGINE='OSS'`	必填	表示該表是外部表格，使用的儲存引擎是OSS。
`TABLE_PROPERTIES`		用於告知AnalyticDB for MySQL如何訪問OSS中的資料。
`endpoint`		OSS的EndPoint（地區節點）。說明目前僅支援AnalyticDB for MySQL通過ECS的VPC網路訪問OSS。登入OSS控制台，單擊目標Bucket，在Bucket概覽頁面查看EndPoint（地區節點）。
`url`		OSS中來源資料檔案或檔案夾的絕對路徑。檔案夾絕對路徑以正斜線（/）結尾。路徑末尾支援萬用字元``，用於匹配該路徑下所有符合該模式的檔案或檔案夾。樣本：檔案：`oss://<bucket-name>/adb/oss_import_test_data.csv`。檔案夾：`oss://<bucket-name>/adb_data/`。說明若指定為來源資料檔案夾的路徑，成功建立外表後，外表中的資料為該檔案夾下的所有資料。 `` 模糊查詢：`oss://<bucket-name>/adb_data/list_file_with_prefix/test*`。說明該模糊查詢樣本將匹配到滿足首碼條件的所有檔案和檔案夾，例如：`oss://<bucket-name>/adb_data/list_file_with_prefix/testfile1`和 `oss://<bucket-name>/adb_data/list_file_with_prefix/test1/file2`。
`accessid`		您在訪問OSS中的檔案或檔案夾時所持有的AccessKey ID。如何擷取您的AccessKey ID和AccessKey Secret，請參見帳號與許可權。
`accesskey`		您在訪問OSS中的檔案或檔案夾時所持有的AccessKey Secret。
`delimiter`		定義CSV資料檔案的資料行分隔符號。例如您可以將資料行分隔符號設定為英文逗號（,）。
`null_value`	選填	定義CSV資料檔案的`NULL`值。預設將空值定義為`NULL`，即`"null_value": ""`。說明 AnalyticDB for MySQL叢集需為V3.1.4.2或以上版本才支援配置該參數。關於版本資訊，請參見新功能發布記錄。
`ossnull`		選擇CSV資料檔案中`NULL`值的對應規則。取值範圍如下： 1（預設值）：表示`EMPTY_SEPARATORS`，即僅將空值定義為`NULL` 。樣本：`a,"",,c --> "a","",NULL,"c"` 2：表示`EMPTY_QUOTES`，即僅將`""`定義為`NULL`。樣本：`a,"",,c --> "a",NULL,"","c"` 3：表示`BOTH`，即同時將空值和`""`定義為`NULL`。樣本：`a,"",,c --> "a",NULL,NULL,"c"` 4：表示`NEITHER`，即空值和`""`均不定義為`NULL`。樣本：`a,"",,c --> "a","","","c"` 說明上述各樣本的前提為`"null_value": ""`。
`skip_header_line_count`		定義匯入資料時需要在開頭跳過的行數。CSV檔案第一行為表頭，若設定該參數為1，匯入資料時可自動跳過第一行的表頭。預設取值為0，即不跳過。
`oss_ignore_quote_and_escape`		是否忽略欄位值中的引號和轉義。預設取值為`false`，即不忽略欄位值中的引號和轉義。說明 AnalyticDB for MySQL叢集需為V3.1.4.2或以上版本才支援設定該參數。關於版本資訊，請參見新功能發布記錄。

AnalyticDB for MySQL支援通過OSS的CSV格式的外表讀寫Hive TEXT檔案。建表語句如下：

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://<bucket-name>/adb_data/",
    "delimiter": "\\1",
    "null_value": "\\\\N",
    "oss_ignore_quote_and_escape": "true",
    "ossnull": 2,
}';

說明

在建立OSS的CSV格式的外表來讀取Hive TEXT檔案時，需注意如下幾點：

Hive TEXT檔案的預設資料行分隔符號為\1。若您需要通過OSS的CSV格式的外表讀寫Hive TEXT檔案，您可以在配置delimiter參數時將其轉義為\\1。
Hive TEXT檔案的預設NULL值為\N。若您需要通過OSS的CSV格式的外表讀寫Hive TEXT檔案，您可以在配置null_value參數時將其轉義為 \\\\N。
Hive的其他基礎資料型別 (Elementary Data Type)（如BOOLEAN）與AnalyticDB for MySQL的資料類型一一對應，但BINARY、CHAR(n)和VARCHAR(n)類型均對應AnalyticDB for MySQL中的VARCHAR類型。

建立OSS Parquet格式或OSS ORC格式外表

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

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_data/",
    "accessid":"LTAIF****5FsE",
    "accesskey":"Ccw****iWjv",
    "format":"parquet"
}';

參數	說明
`ENGINE= 'OSS'`	表示該表是外部表格，使用的儲存引擎是OSS。
`TABLE_PROPERTIES`	用於告知AnalyticDB for MySQL如何訪問OSS中的資料。
`endpoint`	OSS的EndPoint（地區節點）（網域名稱節點）。說明目前僅支援AnalyticDB for MySQL通過OSS中ECS的VPC網路（內網）訪問OSS。登入OSS控制台，單擊目標Bucket，在Bucket概覽頁面查看EndPoint（地區節點）。
`url`	OSS中來源資料檔案或檔案夾的絕對路徑。檔案夾絕對路徑以正斜線（/）結尾。樣本：檔案：`oss://<bucket-name>/adb/oss_import_test_data.parquet`。檔案夾：`oss://<bucket-name>/adb_data/`。說明建表時請將樣本中的`url`替換為實際的OSS路徑。若指定為來源資料檔案夾的路徑，成功建立外表後，外表中的資料為該檔案夾下的所有資料。
`accessid`	您在訪問OSS中的檔案或檔案夾時所持有的AccessKey ID。如何擷取您的AccessKey ID和AccessKey Secret，請參見帳號與許可權。
`accesskey`	您在訪問OSS中的檔案或檔案夾時所持有的AccessKey Secret。
`format`	資料檔案的格式。建立Parquet格式檔案的外表時需設定為`parquet`。建立ORC格式檔案的外表時需設定為`orc`。說明不指定format時，預設格式為CSV。

說明

外表建立語句中的列名需與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的查詢會失敗。

帶分區的資料檔案建立OSS外表

如果OSS資料來源是包含分區的，會在OSS上形成一個分層目錄，類似如下內容：

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

上述資料中p1為第1級分區，p2為第2級分區，p3為第3級分區。對應這種資料來源，一般都希望以分區的模式進行查詢，那麼就需要在建立OSS外表時指明分區列。以Parquet格式為例，建立帶有分區的OSS外表的語句如下：

CREATE TABLE IF NOT EXISTS oss_parquet_partition_table
(
  uid varchar,
  other varchar,
  p1 date,
  p2 int,
  p3 varchar
)
ENGINE='OSS'
TABLE_PROPERTIES='{
  "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
  "url":"oss://<bucket-name>/adb/oss_parquet_data_dir",
  "accessid":"LTAIF****5FsE",
  "accesskey":"Ccw****iWjv",
  "format":"parquet",
  "partition_column":"p1, p2, p3"
}';

說明

TABLE_PROPERTIES中的partition_column屬性必須聲明分區列（本例中的p1、p2、p3）且partition_column屬性裡必須嚴格按照第1級、第2級、第3級的順序聲明（本例中p1為第1級分區，p2為第2級分區，p3為第3級分區）。
列定義中必須定義分區列（本例中的p1、p2、p3）及類型，且分區列需要置於列定義的末尾。
列定義中分區列的先後順序需要與partition_column中分區列的順序保持一致。
可以作為分區列的資料類型有：BOOLEAN、TINYINT、SMALLINT、INT、INTEGER、BIGINT、FLOAT、DOUBLE、DECIMAL、VARCHAR、STRING、DATE、TIMESTAMP。
查詢時分區列和其它資料列的表現和用法沒有區別。
不指定format時，預設格式為CSV。