谷歌BigQuery数据迁移至AnalyticDB PostgreSQL版

准备工作

• 已准备需要迁移的谷歌BigQuery服务。
• 已准备用于导出谷歌BigQuery数据的谷歌Cloud Storage服务，并创建存储分区（Bucket）。
• 已创建拥有访问谷歌Cloud Storage权限的IAM用户。
• 已开通阿里云对象存储服务（OSS），OSS的详细信息，请参见什么是对象存储OSS。
• 已创建OSS存储空间，请参见创建存。
  说明 建议OSS存储空间与AnalyticDB PostgreSQL版实例在同一地域，便于后续数据导入数据库中。
• 已创建AnalyticDB PostgreSQL版实例，如何创建实例，请参见创建实例。

步骤一：将谷歌BigQuery的数据导出到谷歌Cloud Storage

将谷歌BigQuery的数据导出到谷歌Cloud Storage需要使用bq命令行工具，关于bq命令行工具的使用方法，请参见使用bq命令行工具。

1. 使用bq命令行工具查询谷歌BigQuery数据集中表的DDL语句并下载至本地设备。具体操作请参见INFORMATION_SCHEMA.TABLES视图。
   谷歌BigQuery不提供show create table等命令查看表的DDL脚本，但谷歌BigQuery允许您使用内置的用户自定义函数UDF来查询特定数据集中表的DDL脚本。
2. 通过bq命令行工具，执行bq extract命令，将谷歌BigQuery数据集中的表依次导出至谷歌Cloud Storage的存储分区中。导出相关操作以及数据格式和压缩类型的说明，请参见导出表数据。
   导出命令示例如下。

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

3. 查看谷歌Cloud Storage的存储分区，检查数据导出结果。

步骤二：将谷歌Cloud Storage上的数据同步到阿里云OSS

通过在线迁移上云服务，将谷歌Cloud Storage上的数据同步到阿里云OSS上。具体操作，请参见迁移实施。

步骤三：创建用于装载数据的目标表

在AnalyticDB PostgreSQL版实例中创建用于装载谷歌BigQuery的数据的目标表。目标表结构需与源表结构一致，建表语法，请参见CREATE TABLE 。

谷歌BigQuery与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（分区表）
  谷歌BigQuery的分区方式主要有以下三大类：

  • 整数范围分区

    对应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对应谷歌BigQuery的整数范围分区表中的INTERVAL。

  • 时间单位列分区

    对应AnalyticDB PostgreSQL版的日期范围表分区，可以使用单个DATE或TIMESTAMP作为分区键列。

    谷歌BigQuery中是通过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。谷歌BigQuery每张表中最多有4个聚簇列，而AnalyticDB PostgreSQL版则没有分布列的数量限制。

• DEFAULT COLLATE

  表示ORDER BY、GROUP BY等可以用于比较的运算的排序规则（默认为binary排序规则）。通常情况下，迁移数据时可以忽略该内容。

CREATE EXTERNAL TABLE

谷歌BigQuery与AnalyticDB PostgreSQL版的外表连接信息有所区别。

• 谷歌BigQuery：通过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语句

在谷歌BigQuery中，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子句过滤窗口函数中的结果，您可以通过多层嵌套查询实现。