AnalyticDB：匯出至OSS

操作步驟

本樣本將AnalyticDB for MySQL數倉版的adb_demo庫中的source_table表資料匯出至OSS的adb_data檔案夾下。

1. 串連目的地組群，進入來源資料庫。

2. 在adb_demo資料庫中建立外表，詳情請參見不帶分區的資料檔案建立OSS外表。

3. 根據外表類型選擇執行寫入語句，將來源資料寫入到步驟2建立的外表中。不同的外表類型支援的文法，請參見未做分區的普通外表文法支援和分區外表文法支援。

4. 待步驟3的寫入任務結束後，您可登入OSS控制台，在目標檔案夾下查看匯出到OSS的資料檔案。您也可以直接通過AnalyticDB for MySQL查詢匯出到外表的資料。

未做分區的普通外表文法支援

• INSERT SELECT FROM

  • 功能：如果您的資料在其他表中已經存在，可以通過INSERT SELECT FROM將資料複製到外表。將源表的資料寫入外表對應的OSS位置，每次寫入會產生新的OSS資料檔案。

    重要

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

    文法：

    INSERT INTO table_name 
    SELECT select_statement FROM from_statement;       

    例句：

    insert into oss_table select col1, col2, col3 from source_table;

• INSERT OVERWRITE SELECT

  • 功能：INSERT OVERWRITE SELECT用於向表中批量插入資料。如果目標外表中已存在資料，則每次寫入會先刪除原外表路徑下全部資料檔案，再產生新的OSS資料檔案。

    重要

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

    文法：

    INSERT OVERWRITE table_name 
    SELECT select_statement FROM from_statement;

    例句：

    INSERT OVERWRITE oss_table 
    SELECT col1, col2, col3 FROM source_table;

• REPLACE SELECT FROM

  • 功能：由於OSS外表不支援定義主鍵，因此REPLACE SELECT FROM的寫入表現與INSERT SELECT FROM保持一致，都是將資料複製到另外一張表；如果目標表已有資料，已有資料保持不變，新資料追加到新的OSS資料檔案。

• 匯出到OSS單檔案（僅限CSV格式，Parquet格式不允許匯出到單一檔案）

  • 功能：通過hint指定唯一的OSS檔案，將資料匯出到此檔案中。包含overwrite關鍵字時，覆蓋外表TABLE_PROPERTIES中定義的目錄下舊的同名檔案，不影響該目錄下其他檔案。

    重要

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

    版本說明：

    • 3.1.2版本之前的AnalyticDB for MySQL叢集：不支援此功能，檔案名稱由系統自動命名，將會匯出多個檔案。根據任務並發速度動態確定目標檔案數目。

    • 3.1.2及之後版本的AnalyticDB for MySQL叢集：支援通過hint將AnalyticDB for MySQLCSV格式的普通外表匯出到OSS單檔案，使用者在匯出時可自訂檔案名稱。不帶hint的情況下，與3.1.2之前的版本保持一致（匯出多個檔案）。

    文法：

    /*+output_filename=adb.txt*/INSERT [OVERWRITE] table_name 
    SELECT select_statement FROM from_statement;

    例句：

    /*+output_filename=adb.txt*/INSERT [OVERWRITE] oss_table 
    SELECT * FROM source_table;

分區外表文法支援

例如：分區表中定義了2個分區列，3個普通列。其中一級分區名稱為pcol1，分區數值為1；二級分區名稱為pcol2，分區數值為a。分區表資料匯出到OSS的儲存路徑為adb_data/，則向pcol1=1且pcol2=a的外表分區寫出資料時，資料檔案相對路徑目錄為：adb_data/pcol1=1/pcol2=a/；且外表CSV/Parquet資料檔案內不包含pcol1與pcol2這兩列的值，只包含3列普通列的值。

• INSERT INTO PARTITION SELECT FROM

功能：INSERT INTO PARTITION SELECT FROM用於向帶分區的外表中批量插入資料。寫入時在PARTITION欄位中指明所有分區列和分區值；也可以唯寫明高層分區目錄的分區值，低層分區動態產生；或完全不寫分區值，所有層次分區動態產生，此時也就可以無需PARTITION欄位。

寫入時，資料將在對應分區追加寫入，每次寫入會產生新的OSS資料檔案。

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

全靜態分區

INSERT INTO table_name PARTITION(par1=val1,par2=val2,...) 
SELECT select_statement FROM from_statement;

INSERT INTO oss_table_par PARTITION(par1=val1,par2=val2) 
select col1, col2, col3 FROM source_table;

半靜態半動態分區

INSERT INTO table_name PARTITION(par1=val1,par2,...) 
SELECT select_statement FROM from_statement;

INSERT INTO oss_table_par PARTITION(par1=val1,par2) 
SELECT col1, col2, col3, par2col FROM source_table;

全動態分區

INSERT INTO table_name 
SELECT select_statement FROM from_statement;

INSERT INTO oss_table_par
SELECT col1, col2, col3, par1col, par2col FROM source_table;

• INSERT OVERWRITE PARTITION SELECT

功能：和INSERT INTO PARTITION SELECT使用方法相同，功能上的不同在於會清除掉本次執行中涉及到的目標資料分割中之前已有的資料檔案，對於沒有新資料寫入的分區，則不會清除其中的資料檔案。

INSERT OVERWRITE table_name PARTITION(par1=val1,par2=val2,...)[IF NOT EXISTS] 
SELECT select_statement FROM from_statement;

INSERT OVERWRITE oss_table_par PARTITION(par1=val1,par2=val2) IF NOT EXISTS 
SELECT col1, col2, col3 FROM source_table;

• REPLACE INTO PARTITION SELECT FROM

功能：由於外表不支援主鍵，REPLACE INTO PARTITION SELECT FROM行為表現與INSERT INTO PARTITION SELECT FROM一樣。

不支援文法

AnalyticDB for MySQL外表不支援自訂行級寫入的插入文法，具體不支援的文法包括：INSERT INTO VALUES和REPLACE INTO VALUES。