AnalyticDB:Migrate data from a Google BigQuery dataset to an AnalyticDB for PostgreSQL instance

Prerequisites

• A Google BigQuery service is ready for data migration.

• A Google Cloud Storage service is ready for exporting data from BigQuery, and a bucket is created.

• An Identity and Access Management (IAM) user is created and has the permissions to access Cloud Storage.

• Alibaba Cloud Object Storage Service (OSS) is activated. For more information about OSS, see What is OSS?.

• An OSS bucket is created. For more information, see Create a bucket.

  Note

  We recommend that you create an OSS bucket within the same region as the AnalyticDB for PostgreSQL instance to facilitate subsequent data imports.

• An AnalyticDB for PostgreSQL instance is created. For more information, see Create an instance.

Step 1: Export data from the BigQuery dataset to the Cloud Storage bucket

To export data from BigQuery to Cloud Storage, use the bq command-line tool. For more information, see Using the bq command-line tool.

1. Use the bq command-line tool to query the Data Definition Language (DDL) statements for the tables in the BigQuery dataset, and then download the statements to your local device. For more information, see INFORMATION_SCHEMA.TABLES view.

   BigQuery does not provide commands such as show create table to query the DDL scripts of tables. However, BigQuery allows you to use a built-in user-defined function (UDF) to query the DDL scripts of tables in a specified dataset.

2. Use the bq command-line tool to run the bq extract command to export the tables in the BigQuery dataset to the Cloud Storage bucket one by one. For information about the export process, data formats, and compression types, see Exporting table data.

   The following example shows an export command.

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

3. Check the Cloud Storage bucket to verify the data export result.

Step 2: Synchronize data from Cloud Storage to OSS

Use the online data migration service to synchronize data from Cloud Storage to OSS. For more information, see Migrate data.

Step 3: Create a destination table

Create a destination table in the AnalyticDB for PostgreSQL instance to store the BigQuery data. The destination table must use the same schema as the source table. For information about the table creation syntax, see CREATE TABLE.

For information about the data type and DDL mappings between BigQuery and AnalyticDB for PostgreSQL, see Syntax conversion.

Step 4: Import data from OSS to the AnalyticDB for PostgreSQL instance

Import data into AnalyticDB for PostgreSQL by using the COPY command or an OSS foreign table:

• To import data from OSS by using the COPY command, see Use the COPY or UNLOAD command to import or export data to OSS.

• To import OSS data by using an OSS foreign table, see Analyze data lakes with OSS foreign tables.

  Important

  We recommend that you use Avro files to migrate data. However, you cannot migrate data of the STRUCT or GEOGRAPHY type to AnalyticDB for PostgreSQL by using Avro files.

Syntax conversion

Data types

DDL statements

CREATE TABLE

• PARTITION BY (partitioned table)

  BigQuery supports the following partitioning methods:

  • Integer-range partitioning

    This method corresponds to the number-range table partitioning of AnalyticDB for PostgreSQL. You can specify a column of a numeric data type as the partition key column. The following example shows how to specify number-range partitions for AnalyticDB for 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 );

    The EVERY clause corresponds to the INTERVAL clause in the integer-range partitioned table of BigQuery.

  • Time-unit column partitioning

    This method corresponds to the date-range table partitioning of AnalyticDB for PostgreSQL. You can specify a column of the DATE or TIMESTAMP type as the partition key column.

    BigQuery uses DAY, MONTH, and YEAR to specify the partition granularity. AnalyticDB for PostgreSQL uses EVERY(INTERVAL) to specify the partition granularity. The following example shows how to specify date-range partitions by day for AnalyticDB for 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') );

  • Ingestion-time partitioning

    This method corresponds to the date-range table partitioning of AnalyticDB for PostgreSQL. Unlike the conversion method for time-unit column partitioning, AnalyticDB for PostgreSQL does not support pseudo-time columns. You must add a create_time column when you create the destination table.

• CLUSTER BY (clustering)

  This clause corresponds to the DISTRIBUTED BY clause of AnalyticDB for PostgreSQL. Each BigQuery table supports up to four CLUSTER BY columns, whereas AnalyticDB for PostgreSQL does not impose limits on the number of distribution columns.

• DEFAULT COLLATE

  This clause specifies the sort order for comparison operations such as ORDER BY and GROUP BY. The default is binary ordering. In most cases, you can ignore this clause during data migration.

CREATE EXTERNAL TABLE

The connection information about foreign tables differs between BigQuery and AnalyticDB for PostgreSQL.

• BigQuery: Use the WITH CONNECTION clause to specify the credentials for accessing external data and the connection name in the PROJECT_ID.LOCATION.CONNECTION_ID format.

• AnalyticDB for PostgreSQL: Use the LOCATION clause to specify the credentials and connection information for external data.

  AnalyticDB for PostgreSQL foreign tables support the FILE, gpfdist, and HTTP protocols. If you store data in OSS, you can use the oss_fdw extension to create an OSS foreign table and import the data.

CREATE PROCEDURE

You can use the CREATE FUNCTION statement instead.

Other SQL statements

MERGE statement

In BigQuery, the MERGE statement combines INSERT, UPDATE, and DELETE operations into a single statement. The MERGE statement compares the source table with the destination table, updates or deletes matching tuples, and inserts, updates, or deletes non-matching tuples.

In AnalyticDB for PostgreSQL, you can implement the MERGE statement in a single transaction. The following example inserts new products and updates the stock quantity of existing products.

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;

When matched by primary key, you can also use INSERT ON CONFLICT.

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

SELECT statement

• AnalyticDB for PostgreSQL does not support the SELECT * EXCEPT/REPLACE statement.

• AnalyticDB for PostgreSQL does not support the QUALIFY clause for filtering window function results. You can use nested queries instead.