本文介绍从谷歌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版

语法转换

数据类型

谷歌BigQuery数据类型AnalyticDB PostgreSQL版数据类型
INT64BIGINT
FLOAT64FLOAT
NUMERICDECIMAL
BIGNUMERICDECIMAL
BOOLBOOLEAN
BYTES(2字节头)BYTES(1字节头)
STRING/STRING()TEXT/VARCHAR()
DATEDATE
DATETIMETIMESTAMP
TIMETIME
TIMESTAMPTIMESTAMP
INTERVALINTERVAL
ARRAYARRAY[]
STRUCTCREATE TYPE
JSONJSON
GEOGRAPHYCREATE TYPE/GEOGRAPHY

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中是通过DAYMONTHYEAR来指定分区粒度,在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子句过滤窗口函数中的结果,您可以通过多层嵌套查询实现。