AnalyticDB：匯入資料並查詢

操作步驟

1. 通過CREATE TABLE，在adb_demo資料庫中建立外表。建立CSV、Parquet或TEXT格式OSS外表的建表文法請參見建立OSS外表文法。

2. 查詢OSS資料。

   查詢外表映射表和查詢AnalyticDB for MySQL內表文法沒有區別，您可以方便地直接進行查詢，如本步驟的範例程式碼所示。

   select uid, other from oss_import_test_external_table where uid < 100 limit 10;

   • 對於資料量較大的CSV或TEXT資料檔案，強烈建議您按照後續步驟匯入AnalyticDB for MySQL後再做查詢，否則查詢效能可能會較差。

   • 對於Parquet格式資料檔案，直接查詢的效能一般也比較高，您可以根據需要決定是否進一步匯入到AnalyticDB for MySQL後再做查詢。

3. 通過CREATE TABLE，在adb_demo資料庫中建立目標表adb_oss_import_test儲存從OSS中匯入的資料。

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

4. 執行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****** |

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

5. 執行以下命令，查詢adb_oss_import_test表的資料。

   SELECT * FROM adb_oss_import_test;

建立OSS外表文法

• 建立OSS CSV格式外表

  • 樣本的oss_import_test_data.txt檔案為CSV格式，本節介紹CSV格式的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",
        "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或以上版本才支援設定該參數。關於版本資訊，請參見新功能發布記錄。

• 建立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格式檔案的外表時，需要注意資料類型的對應關係，具體規則如下：

    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類型，會導致建表失敗。

針對帶有分區的Parquet或CSV資料檔案建立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

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

相關文檔

更多匯入資料方式，請參見支援的資料來源。