本文介绍从谷歌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命令行工具。
- 使用bq命令行工具查询谷歌BigQuery数据集中表的DDL语句并下载至本地设备。具体操作请参见INFORMATION_SCHEMA.TABLES视图。谷歌BigQuery不提供
show create table
等命令查看表的DDL脚本,但谷歌BigQuery允许您使用内置的用户自定义函数UDF来查询特定数据集中表的DDL脚本。 - 通过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;
- 查看谷歌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版。
语法转换
数据类型
谷歌BigQuery数据类型 | AnalyticDB PostgreSQL版数据类型 |
---|---|
INT64 | BIGINT |
FLOAT64 | FLOAT |
NUMERIC | DECIMAL |
BIGNUMERIC | DECIMAL |
BOOL | BOOLEAN |
BYTES(2字节头) | BYTES(1字节头) |
STRING/STRING() | TEXT/VARCHAR() |
DATE | DATE |
DATETIME | TIMESTAMP |
TIME | TIME |
TIMESTAMP | TIMESTAMP |
INTERVAL | INTERVAL |
ARRAY | ARRAY[] |
STRUCT | CREATE TYPE |
JSON | JSON |
GEOGRAPHY | CREATE 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中是通过
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子句过滤窗口函数中的结果,您可以通过多层嵌套查询实现。