AnalyticDB：匯出至HDFS

前提條件

• AnalyticDB for MySQL叢集需為V3.1.4.4或以上版本。

  說明

  查看湖倉版叢集的核心版本，請參見如何查看執行個體版本資訊。如需升級核心版本，請聯絡支援人員。

• 已建立HDFS叢集，並在HDFS叢集中建立了一個新的檔案夾（本樣本中檔案夾名為hdfs_output_test_csv_data），用於儲存匯入的AnalyticDB for MySQL資料。

  說明

  使用INSERT OVERWRITE進行匯入時，系統會覆蓋目標檔案夾下的原始檔案。為避免原始檔案被覆蓋，建議在匯出時建立一個新的目標檔案夾。

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

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

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

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

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

• 如果您的AnalyticDB for MySQL叢集是彈性模式，您需要在集群資訊頁面的網路資訊地區，開啟啟用ENI網絡的開關。

注意事項

• AnalyticDB for MySQL叢集僅支援匯出檔案格式為CSV和Parquet的資料至HDFS。不支援匯出檔案格式為ORC的資料。

• AnalyticDB for MySQL叢集不支援自訂行級寫入的INSERT文法，如INSERT INTO VALUES和REPLACE INTO VALUES。

• 不支援通過分區外表匯出單個檔案至HDFS。

• 通過分區外表匯出資料時，資料檔案內不包含分區列的資料，分區列的資料資訊以HDFS目錄的形式展現。

  例如，已在分區外表中定義了3個普通列和2個分區列。其中一級分區列的列名為p1，分區列的值為1。二級分區名稱為p2，分區數值為a，現需要通過分區外表將資料匯出到HDFS的adb_data/路徑下。

  那麼當p1=1且p2=a的外表分區匯出資料時，資料檔案相對路徑目錄為adb_data/p1=1/p2=a/，且外表CSV或Parquet資料檔案內不包含p1和p2這兩列，只包含3列普通列的值。

操作步驟

1. 串連目標AnalyticDB for MySQL叢集。詳細操作步驟，請參見串連叢集。

2. 建立來源資料庫。詳細操作步驟，請參見建立資料庫。

   本樣本中，AnalyticDB for MySQL叢集的源庫名為adb_demo。

3. 建立源表並插入來源資料。

   您可以使用以下語句在源庫adb_demo中建立一張源表adb_hdfs_import_source，建表語句如下：

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

   往源表adb_hdfs_import_source中插入一行測試資料，語句如下：

   INSERT INTO adb_hdfs_import_source VALUES ("1", "a"), ("2", "b"), ("3", "c");

4. 建立外部映射表。

   您可以使用以下文法在源庫adb_demo中建立一張外部映射表，用於將AnalyticDB for MySQL資料匯出至HDFS：

   • 建立普通外部映射表（本文樣本中目標表名為hdfs_import_external），文法如下。

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

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

     CREATE TABLE IF NOT EXISTS hdfs_import_external_par
     (
         uid string,
         other string,
         p1 date,
         p2 int,
         p3 varchar
     )
     ENGINE='HDFS'
     TABLE_PROPERTIES='{
         "format":"csv",
         "delimiter":",",
         "hdfs_url":"hdfs://172.17.***.***:9000/adb/hdfs_output_test_csv_data"
         "partition_column":"p1, p2, p3"
     }';

   說明

   • AnalyticDB for MySQL叢集僅支援匯出檔案格式為CSV和Parquet的資料至HDFS。不支援匯出檔案格式為ORC的資料。

   • 建立外表的詳細文法說明，請參見建立HDFS外表和建立帶分區的HDFS外表。

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

   • 如需通過普通外表匯出資料，具體文法，請參見附錄1：資料匯出文法（普通外表）。

   • 如需通過分區外表匯出資料，具體文法，請參見附錄2：資料匯出文法（分區外表）。

後續步驟

SELECT * FROM hdfs_import_external LIMIT 100;

附錄1：資料匯出文法（普通外表）

若建立外表時未指定分區列，您可以根據業務需要選擇如下幾種方式匯出資料：

• 方式一：如果您的資料已存在於目標表中，可以通過INSERT INTO語句將資料匯入外表。使用該語句會將源表的資料寫入外表對應的HDFS位置，每次寫入會產生新的HDFS資料檔案。

  說明

  外表裡的列和需要匯出的列，必須保持列個數的完整。INSERT INTO為增量寫入，會額外產生新的檔案，不會覆蓋舊的歷史檔案。

  文法如下。

  INSERT INTO <target_table> 
  SELECT <col_name> FROM <source_table>;

  樣本如下。

  INSERT INTO hdfs_import_external
  SELECT col1, col2, col3 FROM adb_hdfs_import_source;

  說明

  col1, col2, col3表示外表中的所有列。

• 方式二：HDFS外表不支援定義主鍵，因此REPLACE INTO的寫入表現與INSERT INTO一致，都會將資料複製到外表。如果目標表內已有資料，執行REPLACE INTO語句匯入時，已有資料保持不變，新資料會被追加到目標資料檔案中。

  說明

  • 寫入的外表必須保持列個數的完整，不允許使用者指定唯寫入一部分的列。

  • REPLACE INTO為增量寫入，會額外產生新的檔案，不會覆蓋舊的歷史檔案。

  文法如下。

  REPLACE INTO <target_table> 
  SELECT <col_name> FROM <source_table>;

  樣本如下。

  REPLACE INTO hdfs_import_external
  SELECT col1, col2, col3 FROM adb_hdfs_import_source;

• 方式三：您可以使用INSERT OVERWRITE文法向外表中批量插入資料。如果目標外表中已存在資料，每次寫入會先刪除外表路徑下的全部資料檔案，再產生新的HDFS資料檔案。

  重要

  • 寫入的外表必須保持列個數的完整，不允許指定唯寫入部分的列。

  • INSERT OVERWRITE為覆蓋寫入，會覆蓋匯出目錄內已有的歷史資料，謹慎使用。

  文法如下。

  INSERT OVERWRITE <target_table> 
  SELECT <col_name> FROM <source_table>;

  樣本如下。

  INSERT OVERWRITE hdfs_import_external 
  SELECT col1, col2, col3 FROM adb_hdfs_import_source;

• 方式四：非同步執行INSERT OVERWRITE匯出資料，文法如下。

  SUBMIT job INSERT OVERWRITE <target_table> 
  SELECT <col_name> FROM <source_table>;

  樣本如下。

  SUBMIT JOB INSERT OVERWRITE hdfs_import_external  
  SELECT col1, col2, col3 FROM adb_hdfs_import_source;

  返回結果如下。

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

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

附錄2：資料匯出文法（分區外表）

分區外表在文法中加入PARTITION欄位匯出資料，您還可以通過指定PARTITION欄位中的分區列和分區值來確定是否使用靜態或者動態分區。

• 方式一：您可以使用INSERT INTO PARTITION文法往帶分區的外表中批量插入資料。

  說明

  寫入時，資料將在對應分區追加寫入，每次寫入會產生新的HDFS資料檔案，歷史資料不會被覆蓋；寫入的外表必須保持列個數的完整，不允許使用者指定唯寫入一部分的列。

  • 全靜態分區

    文法如下。

    INSERT INTO <target_table> PARTITION(par1=val1,par2=val2,...) 
    SELECT <col_name> FROM <source_table>;

    樣本如下。

    INSERT INTO hdfs_import_external_par PARTITION(p1='2021-05-06',p2=1,p3='test') 
    SELECT col1, col2, col3, FROM adb_hdfs_import_source;

  • 半靜態半動態分區

    說明

    靜態列必須位於動態列的前面，不允許穿插使用。

    文法如下。

    INSERT INTO <target_table> PARTITION(par1=val1,par2,...) 
    SELECT <col_name> FROM <source_table>;

    樣本如下。

    INSERT INTO hdfs_import_external_par PARTITION(p1='2021-05-27',p2,p3)  
    SELECT col1, col2, col3, FROM adb_hdfs_import_source;

  • 全動態分區（即不需要PARTITION欄位）

    文法如下。

    INSERT INTO <target_table> 
    SELECT <col_name> FROM <source_table>;

    樣本如下。

    INSERT INTO hdfs_import_external_par  
    SELECT col1, col2, col3, FROM adb_hdfs_import_source;

• 方式二：HDFS外表不支援定義主鍵，因此REPLACE INTO PARTITION的寫入表現與INSERT INTO PARTITION一致。

  說明

  寫入的外表必須保持列個數的完整，不允許使用者指定唯寫入一部分的列；REPLACE INTO PARTITION為增量寫入，會額外產生新的檔案，不會覆蓋舊的歷史檔案。

  文法如下：

  • 全靜態分區

    文法如下。

    REPLACE INTO <target_table> PARTITION(par1=val1,par2=val2,...) 
    SELECT <col_name> FROM <source_table>;

    樣本如下。

    REPLACE INTO hdfs_import_external_par PARTITION(p1='2021-05-06',p2=1,p3='test') 
    SELECT col1, col2, col3, FROM adb_hdfs_import_source;

  • 半靜態半動態分區

    說明

    靜態列必須位於動態列的前面，不允許穿插使用。

    文法如下。

    REPLACE INTO <target_table> PARTITION(par1=val1,par2,...) 
    SELECT <col_name> FROM <source_table>;

    樣本如下。

    REPLACE INTO hdfs_import_external_par PARTITION(p1='2021-05-06',p2,p3)  
    SELECT col1, col2, col3, FROM adb_hdfs_import_source;

  • 全動態分區（即不需要PARTITION欄位）

    文法如下。

    REPLACE INTO <target_table> 
    SELECT <col_name> FROM <source_table>;

    樣本如下。

    REPLACE INTO hdfs_import_external_par  
    SELECT col1, col2, col3, FROM adb_hdfs_import_source;

• 方式三：INSERT OVERWRITE PARTITION與INSERT INTO PARTITION使用方法相同，但使用INSERT OVERWRITE PARTITION時，會覆蓋掉本次執行中涉及到的目標資料分割中之前已有的資料檔案，對於沒有新資料寫入的分區，則不會清除其中的資料檔案。

  文法如下。

  INSERT OVERWRITE <target_table> PARTITION(par1=val1,par2=val2,...)[IF NOT EXISTS] 
  SELECT <col_name> FROM <source_table>;

  重要

  • 寫入的外表必須保持列個數的完整，不允許使用者指定唯寫入一部分的列；INSERT OVERWRITE PARTITION為覆蓋寫入，會覆蓋匯出目錄內已有的歷史資料，謹慎使用。

  • IF NOT EXISTS：表示如果外表分區已存在，則不會匯出到這個分區。

  樣本如下。

  INSERT OVERWRITE hdfs_import_external_par PARTITION(p1='2021-05-06',p2=1,p3='test') IF NOT EXISTS 
  SELECT col1, col2, col3 FROM adb_hdfs_import_source;

• 方式四：非同步執行INSERT OVERWRITE匯出資料，文法如下。

  SUBMIT JOB INSERT OVERWRITE <target_table> 
  SELECT <col_name> FROM <source_table>;

  樣本如下。

  SUBMIT JOB INSERT OVERWRITE hdfs_import_external_par PARTITION(p1='2021-05-06',p2=1,p3='test') IF NOT EXISTS
  SELECT col1, col2, col3 FROM adb_hdfs_import_source;

  返回結果如下。

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

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