AnalyticDB：通過外表匯入OSS資料

企業版、基礎版及湖倉版

1. 進入SQL開發編輯器。

   1. 登入雲原生資料倉儲AnalyticDB MySQL控制台，在左上方選擇叢集所在地區。在左側導覽列，單擊集群清單，然後單擊目的地組群ID。

   2. 在左側導覽列，單擊作業開發 > SQL開發。

2. 匯入資料。

   資料匯入方式分為常規匯入（預設）和彈性匯入。常規匯入在計算節點中讀取來源資料，然後在儲存節點中構建索引，消耗計算資源和儲存資源。彈性匯入在Serverless Spark Job中讀取來源資料和構建索引，消耗Job型資源群組的資源。僅核心版本3.1.10.0及以上且已建立Job型資源群組的企業版、基礎版及湖倉版叢集支援彈性匯入資料。更多內容，請參見資料匯入方式介紹。

   常規匯入

   1. 建立外部資料庫。

      CREATE EXTERNAL DATABASE adb_external_db;

   2. 建立外表。使用CREATE EXTERNAL TABLE語句在外部資料庫adb_external_db中建立OSS外表。本文以adb_external_db.person為例。

      說明

      AnalyticDB for MySQL外表的欄位名稱、欄位數量、欄位順序、欄位類型需要與和OSS檔案相同。

      建立OSS非分區外表

      CREATE EXTERNAL TABLE adb_external_db.person
      (
       id INT,
       name VARCHAR(1023),
       age INT,
       dt VARCHAR(1023)
      )
      ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
      STORED AS TEXTFILE
      LOCATION  'oss://testBucketName/adb/dt=2023-06-15/';

      建立OSS分區外表

      建立OSS分區外表，並添加分區，才能查詢出OSS分區外表的資料。

      1. 建立OSS分區外表。

         CREATE EXTERNAL TABLE adb_external_db.person
         (
          id INT,
          name VARCHAR(1023) ,
          age INT
         )
         PARTITIONED BY (dt STRING)
         ROW FORMAT DELIMITED FIELDS TERMINATED BY  ','
         STORED AS TEXTFILE
         LOCATION  'oss://testBucketName/adb/';

      2. 添加分區。您可以通過ALTER TABLE ADD PARTITION手動添加分區，也可以通過MSCK REPAIR TABLE自動識別並添加分區。

         ALTER TABLE adb_external_db.person ADD PARTITION (dt='2023-06-15') LOCATION 'oss://testBucketName/adb/dt=2023-06-15/';

         說明

         • 增加或刪除OSS外表的分區，請參見ALTER TABLE（外表）。

         • 自動同步OSS外表的分區，請參見MSCK REPAIR TABLE。

      OSS外表的文法說明，請參見CREATE EXTERNAL TABLE。

   3. 查詢資料。

      資料表建立成功後，您可以在AnalyticDB for MySQL中通過SELECT語句查詢OSS的資料。

      SELECT * FROM adb_external_db.person;

      返回結果如下：

      +------+-------+------+-----------+
      | id   | name  | age  | dt        |
      +------+-------+------+-----------+
      |    1 | james |   10 |2023-06-15 |
      |    2 | bond  |   20 |2023-06-15 |
      |    3 | jack  |   30 |2023-06-15 |
      |    4 | lucy  |   40 |2023-06-15 |
      +------+-------+------+-----------+

   4. 在AnalyticDB for MySQL中建立資料庫。如果有已建立的資料庫，可以忽略本步驟。樣本如下：

      CREATE DATABASE adb_demo; 

   5. 在AnalyticDB for MySQL中建立表用於儲存從OSS中匯入的資料。樣本如下：

      說明

      建立的內表和步驟b中建立的外表的欄位名稱、欄位數量、欄位順序、欄位類型必須相同。

      CREATE TABLE IF NOT EXISTS adb_demo.adb_import_test(
          id INT,
          name VARCHAR(1023),
          age INT,
          dt VARCHAR(1023)
      )
      DISTRIBUTED BY HASH(id);

   6. 向表中匯入資料。

      • 方法一：使用INSERT INTO語句匯入資料，當主鍵重複時會自動忽略當前寫入資料，資料不做更新，作用等同於INSERT IGNORE INTO，更多資訊，請參見INSERT INTO。樣本如下：

        INSERT INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

      • 方法二：使用INSERT OVERWRITE INTO語句同步匯入資料，會覆蓋表中原有的資料。樣本如下：

        INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

      • 方法三：使用INSERT OVERWRITE INTO語句非同步匯入資料，更多資訊，請參見非同步寫入。樣本如下：

        SUBMIT JOB INSERT OVERWRITE adb_demo.adb_import_test SELECT * FROM adb_external_db.person;

   彈性匯入

   1. 建立資料庫。如果有已建立的資料庫，可以忽略本步驟。樣本如下：

      CREATE DATABASE adb_demo; 

   2. 建立外表。

      說明

      • AnalyticDB for MySQL外表的欄位名稱、欄位數量、欄位順序、欄位類型需要與和OSS檔案相同。

      • 彈性匯入僅支援CREATE TABLE語句建立外表。

      CREATE TABLE oss_import_test_external_table
      (
        id INT(1023),
        name VARCHAR(1023),
        age INT,
        dt VARCHAR(1023)
      )
      ENGINE='OSS'
      TABLE_PROPERTIES='{
          "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
          "url":"oss://testBucketName/adb/dt=2023-06-15/person.csv",
          "accessid":"accesskey_id",
          "accesskey":"accesskey_secret",
          "delimiter":","
      }';

      重要

      建立外表時，CSV、Parquet、ORC格式的外表支援設定的TABLE_PROPERTIES參數不同：

      • CSV格式：僅支援設定endpoint、url、accessid、accesskey、format、delimiter、null_value和partition_column參數。

      • Parquet格式：僅支援設定endpoint、url、accessid、accesskey、format和partition_column參數。

      • ORC格式：僅支援設定endpoint、url、accessid、accesskey、format和partition_column參數。

      外表支援設定的參數及參數說明，請參見OSS非分區外表和OSS分區外表。

   3. 查詢資料。

      資料表建立成功後，您可以在AnalyticDB for MySQL中通過SELECT語句查詢OSS的資料。

      SELECT * FROM oss_import_test_external_table;

      返回結果如下：

      +------+-------+------+-----------+
      | id   | name  | age  | dt        |
      +------+-------+------+-----------+
      |    1 | james |   10 |2023-06-15 |
      |    2 | bond  |   20 |2023-06-15 |
      |    3 | jack  |   30 |2023-06-15 |
      |    4 | lucy  |   40 |2023-06-15 |
      +------+-------+------+-----------+
      4 rows in set (0.35 sec)

   4. 在AnalyticDB for MySQL中建立表用於儲存從OSS中匯入的資料。樣本如下：

      說明

      建立的內表和步驟b中建立的外表的欄位名稱、欄位數量、欄位順序、欄位類型必須相同。

      CREATE TABLE adb_import_test
      (
        id INT,
        name VARCHAR(1023),
        age INT,
        dt VARCHAR(1023)
      )
      DISTRIBUTED BY HASH(id);

   5. 匯入資料。

      重要

      彈性匯入僅支援通過INSERT OVERWRITE INTO語句匯入資料。

      • 方法一：執行INSERT OVERWRITE INTO彈性匯入資料，會覆蓋表中原有的資料。樣本如下：

        /+*elastic_load=true, elastic_load_configs=[adb.load.resource.group.name=resource_group]*/
        INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_demo.oss_import_test_external_table;

      • 方法二：非同步執行INSERT OVERWRITE INTO彈性匯入資料。通常使用SUBMIT JOB提交非同步任務，由後台調度。

        /*+elastic_load=true, elastic_load_configs=[adb.load.resource.group.name=resource_group]*/
        SUBMIT JOB INSERT OVERWRITE INTO adb_demo.adb_import_test SELECT * FROM adb_demo.oss_import_test_external_table;

        重要

        非同步提交彈性匯入任務時，不支援設定優先權隊列。

        返回結果如下：

        +---------------------------------------+
        | job_id                                |
        +---------------------------------------+
        | 202308151719510210170190**********    |

      使用SUBMIT JOB提交非同步任務後，返回結果僅表示非同步任務提交成功。您可以通過job_id終止非同步任務或查詢非同步任務狀態，判斷任務是否執行成功。具體操作，請參見非同步提交匯入任務。

      Hint參數說明：

      • elastic_load：是否使用彈性匯入方式。取值：true或false（預設值）。

      • elastic_load_configs：彈性匯入方式支援配置的參數。參數需使用方括弧（[ ]）括起來，且多個參數之間以豎線（|）分隔，支援配置的參數如下表所示：

        參數	是否必填	說明
        adb.load.resource.group.name	是	執行彈性匯入任務的Job資源群組名稱。
        adb.load.job.max.acu	否	單個彈性匯入任務最多使用的資源。單位為ACU，最小值為5 ACU。預設值為叢集Shard個數+1。 執行如下語句可查詢叢集Shard個數： SELECT count(1) FROM information_schema.kepler_meta_shards;
        spark.driver.resourceSpec	否	Spark driver的資源規格。預設值為small。取值範圍，請參見Spark應用配置參數說明的型號列。
        spark.executor.resourceSpec	否	Spark executor的資源規格。預設值為large。取值範圍，請參見Spark應用配置參數說明的型號列。
        spark.adb.executorDiskSize	否	Spark executor的磁碟容量，取值範圍為(0,100]，單位為GiB，預設值為10 Gi。更多資訊，請參見指定Driver和Executor資源。

   6. （可選）查看已提交的匯入任務是否為彈性匯入任務。

      SELECT job_name, (job_type = 3) AS is_elastic_load FROM INFORMATION_SCHEMA.kepler_meta_async_jobs where job_name = "2023081818010602101701907303151******";

      返回結果如下：

      +---------------------------------------+------------------+
      | job_name                              | is_elastic_load  |
      +---------------------------------------+------------------+
      | 20230815171951021017019072*********** |       1          |
      +---------------------------------------+------------------+

      is_elastic_load的傳回值為1，表示已提交的匯入任務是彈性匯入任務；若為0，則表示已提交的匯入任務是常規匯入任務。

數倉版

1. 串連叢集，建立資料庫。

   CREATE DATABASE adb_demo;

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

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

   CREATE TABLE IF NOT EXISTS oss_import_test_external_table
   (
       id INT,
       name VARCHAR(1023),
       age INT,
       dt VARCHAR(1023) 
   )
   ENGINE='OSS'
   TABLE_PROPERTIES='{
       "endpoint":"oss-cn-hangzhou-internal.aliyuncs.com",
       "url":"oss://testBucketname/adb/dt=2023-06-15/person.csv",
       "accessid":"accesskey_id",
       "accesskey":"accesskey_secret",
       "delimiter":",",
       "skip_header_line_count":0,
       "charset":"utf-8"
   }'; 

3. 查詢oss_import_test_external_table外表資料。

   說明

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

   SELECT * FROM oss_import_test_external_table;

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

   CREATE TABLE IF NOT EXISTS adb_oss_import_test
   (
      id INT,
      name VARCHAR(1023),
      age INT,
      dt VARCHAR(1023) 
   )
   DISTRIBUTED BY HASH(id);

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外表的文法及相關說明，請參見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":"csv|orc|parquet|text
    "delimiter|field_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":"accesskey_id",
    "accesskey":"accesskey_secret",
    "url":"oss://testBucketname/adb_data/",
    "delimiter": "\\1",
    "null_value": "\\\\N",
    "oss_ignore_quote_and_escape": "true",
    "ossnull": 2
}';

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

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

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