AnalyticDB：GoogleBigQuery資料移轉至AnalyticDB PostgreSQL版

準備工作

• 已準備需要遷移的GoogleBigQuery服務。
• 已準備用於匯出GoogleBigQuery資料的GoogleCloud Storage服務，並建立儲存分區（Bucket）。
• 已建立擁有訪問GoogleCloud Storage許可權的IAM使用者。
• 已開通阿里雲Object Storage Service服務（OSS），OSS的詳細資料，請參見什麼是Object Storage Service。
• 已建立OSS儲存空間，請參見建立儲存空間。
  說明 建議OSS儲存空間與AnalyticDB PostgreSQL版執行個體在同一地區，便於後續資料匯入資料庫中。
• 已建立AnalyticDB PostgreSQL版執行個體，如何建立執行個體，請參見建立執行個體。

步驟一：將GoogleBigQuery的資料匯出到GoogleCloud Storage

將GoogleBigQuery的資料匯出到GoogleCloud Storage需要使用bq命令列工具，關於bq命令列工具的使用方法，請參見使用bq命令列工具。

1. 使用bq命令列工具查詢GoogleBigQuery資料集中表的DDL語句並下載至本地裝置。具體操作請參見INFORMATION_SCHEMA.TABLES視圖。
   GoogleBigQuery不提供show create table等命令查看錶的DDL指令碼，但GoogleBigQuery允許您使用內建的使用者自訂函數UDF來查詢特定資料集中表的DDL指令碼。
2. 通過bq命令列工具，執行bq extract命令，將GoogleBigQuery資料集中的表依次匯出至GoogleCloud Storage的儲存分區中。匯出相關操作以及資料格式和壓縮類型的說明，請參見匯出表資料。
   匯出命令樣本如下。

   bq extract
   --destination_format AVRO
   --compression SNAPPY
   tpcds_100gb.web_site
   gs://bucket_name/web_site/web_site-**.avro.snappy;

3. 查看GoogleCloud Storage的儲存分區，檢查資料匯出結果。

步驟二：將GoogleCloud Storage上的資料同步到阿里雲OSS

通過線上遷移上雲端服務，將GoogleCloud Storage上的資料同步到阿里雲OSS上。具體操作，請參見遷移實施。

步驟三：建立用於裝載資料的目標表

在AnalyticDB PostgreSQL版執行個體中建立用於裝載GoogleBigQuery的資料的目標表。目標表結構需與源表結構一致，建表文法，請參見CREATE TABLE。

GoogleBigQuery與AnalyticDB PostgreSQL版的資料類型和DDL文法對應關係，請參見文法轉換。

步驟四：將OSS資料匯入AnalyticDB PostgreSQL版執行個體

您可以通過COPY命令或OSS外表將資料匯入AnalyticDB PostgreSQL版：

• 使用COPY命令匯入OSS資料的方法，請參見使用COPY或UNLOAD命令匯入或匯出資料到OSS。

• 使用OSS外表匯入OSS資料的方法，請參見匯入OSS資料到本地表。
  重要 遷移資料時建議使用AVRO格式檔案，但AVRO格式檔案暫不支援STRUCT和GEOGRAPHY資料類型遷移至AnalyticDB PostgreSQL版。

文法轉換

資料類型

DDL

CREATE TABLE

• PARTITION BY（分區表）
  GoogleBigQuery的分區方式主要有以下三大類：

  • 整數定界分割

    對應AnalyticDB PostgreSQL版的數字範圍表分區，可以使用數字資料類型的列作為分區鍵列，AnalyticDB PostgreSQL版數字定界分割表示例如下。

    CREATE TABLE rank (id int, rank int, year int, gender
    char(1), count int)
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (year)
    ( START (2020) END (2023) EVERY (1),
      DEFAULT PARTITION extra );

    其中EVERY對應GoogleBigQuery的整數定界分割表中的INTERVAL。

  • 時間單位列分區

    對應AnalyticDB PostgreSQL版的日期範圍表分區，可以使用單個DATE或TIMESTAMP作為分區鍵列。

    GoogleBigQuery中是通過DAY、MONTH和YEAR來指定分區粒度，在AnalyticDB PostgreSQL版中，您可以通過EVERY(INTERVAL)來指定分區粒度。AnalyticDB PostgreSQL版日期範圍分區表（分區粒度為天）樣本如下。

    CREATE TABLE sales(id int, date date, amt decimal(10,2))
    DISTRIBUTED BY (id)
    PARTITION BY RANGE (date)
    ( START (date '2022-01-01') INCLUSIVE 
        END (date '2023-01-01') EXCLUSIVE
        EVERY (INTERVAL '1 day') );

  • 提取時間分區

    對應AnalyticDB PostgreSQL版的日期範圍表分區。與時間單位列分區轉換方式不同的是，AnalyticDB PostgreSQL版不支援偽時間列，您需要在建目標表的時候添加create_time列。

• CLUSTER BY（聚簇）

  對應AnalyticDB PostgreSQL版DISTRIBUTED BY。GoogleBigQuery每張表中最多有4個聚簇列，而AnalyticDB PostgreSQL版則沒有分布列的數量限制。

• DEFAULT COLLATE

  表示ORDER BY、GROUP BY等可以用於比較的運算的定序（預設為binary定序）。通常情況下，遷移資料時可以忽略該內容。

CREATE EXTERNAL TABLE

GoogleBigQuery與AnalyticDB PostgreSQL版的外表串連資訊有所區別。

• GoogleBigQuery：通過WITH CONNECTION子句指定訪問外部資料的憑證、PROJECT_ID.LOCATION.CONNECTION_ID的形式指定串連名稱。
• AnalyticDB PostgreSQL版：通過LOCATION指定外部資料的憑證和串連資訊。

  AnalyticDB PostgreSQL版的外表支援FILE、

  gpfdist、HTTP等協議。如果您將資料存在OSS中，可以通過oss_fdw外掛程式建立OSS外表擷取資料。

CREATE PROCEDURE

可以通過CREATE FUNCTION代替。

其他SQL適配

MERGE語句

在GoogleBigQuery中，MERGE可以將INSERT、UPDATE和DELETE操作合并成一條語句執行操作，操作過程中，MERGE會對比源表和目標表，對於匹配的tuple（元組）可以進行更新或刪除，對於不匹配的tuple可以選擇插入、更新或刪除。

MERGE語句在AnalyticDB PostgreSQL版中可以通過一個事務來完成。例如以下情境，您需要在表中插入新的庫存商品以及庫存數量，如果商品已存在，則更新現有商品的庫存數量，樣本如下。

BEGIN;
-- other operations
SAVEPOINT sp1;
INSERT INTO wines VALUES('hateau Lafite 2023', '24');
-- Assume the above fails because of a unique key violation,
-- so now we issue these commands:
ROLLBACK TO sp1;
UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2023';
-- continue with other operations, and eventually
COMMIT;

以主鍵進行匹配時，也可以使用INSERT ON CONFLICT實現。

INSERT INTO wines VALUES('Chateau Lafite 2023', '24') ON CONFLICT (winename) DO UPDATE SET
stock = stock + 24;

SELECT語句

• AnalyticDB PostgreSQL版暫不支援SELECT * EXCEPT/REPLACE語句。
• AnalyticDB PostgreSQL版暫不支援通過QUALIFY子句過濾視窗函數中的結果，您可以通過多層巢狀查詢實現。