Google BigQueryデータセットからAnalyticDB for PostgreSQLインスタンスへのデータの移行 - AnalyticDB

このトピックでは、Google BigQueryデータセットからAnalyticDB for PostgreSQLインスタンスにデータを移行する方法について説明します。

準備

BigQueryが有効化されています。 BigQueryデータセットが作成され、データがデータセットに挿入されます。
Google Cloud Storageが有効化されています。 Cloud Storageバケットが作成されます。
Identity and Access Management (IAM) ユーザーが作成され、Cloud Storageにアクセスする権限があります。
AnalyticDB for PostgreSQLインスタンスが作成されました。詳細については、「インスタンスの作成」をご参照ください。
Alibaba Cloud Object Storage Service (OSS) が有効化されています。詳細については、「」をご参照ください。OSSとは
OSSバケットが作成されます。詳細は、「バケットの作成」をご参照ください。
説明
その後のデータのインポートを容易にするために、AnalyticDB for PostgreSQLインスタンスと同じリージョン内にOSSバケットを作成することを推奨します。

手順1: BigQueryデータセットからCloud Storageバケットにデータをエクスポートする

BigQueryからCloud Storageにデータをエクスポートするには、bq CLIツールを使用する必要があります。詳細については、「bqコマンドラインツールの使用」をご参照ください。

bq CLIツールを使用して、BigQueryデータセットのDDLステートメントを照会し、そのステートメントをコンピューターにダウンロードします。詳細については、「INFORMATION_SCHEMA.TABLES view」をご参照ください。
BigQueryでは、SHOW CREATE TABLEステートメントを使用して、指定されたデータセット内のDDLステートメントをクエリすることはできません。組み込みのユーザー定義関数 (UDF) を使用する必要があります。
bq CLIツールを使用してbq extractコマンドを実行し、BigQueryデータセットからCloud Storageバケットにテーブルデータをエクスポートします。エクスポートプロセス、データ形式、および圧縮タイプの詳細については、「テーブルデータのエクスポート」をご参照ください。
次のサンプルコードは、exportコマンドの例を示しています:
```
bq extract
--destination_format AVRO
--compression SNAPPY
tpcds_100gb.web_site
gs://bucket_name/web_site/web_site-**.avro.snappy;
```
Cloud Storageバケットにデータがエクスポートされているかどうかを確認します。

手順2: Cloud StorageからOSSにデータを同期する

データトランスポートサービスを使用して、Cloud StorageバケットからOSSバケットにデータを同期します。詳細については、「データの移行」をご参照ください。

ステップ3: 宛先テーブルを作成する

AnalyticDB for PostgreSQLインスタンスにBigQueryデータを格納するために使用される宛先テーブルを作成します。ターゲットテーブルは、ソーステーブルと同じスキーマを使用する必要があります。構文の詳細については、「CREATE TABLE」をご参照ください。

BigQueryとAnalyticDB For PostgreSQL間のデータ型とDDLマッピングの詳細については、このトピックの「構文変換」を参照してください。

ステップ4: OSSからAnalyticDB for PostgreSQLインスタンスへのデータのインポート

COPYステートメントまたはOSS外部テーブルを使用して、データをAnalyticDB for PostgreSQLにインポートできます。

COPYステートメントを使用してOSSデータをインポートする方法については、「COPYまたはUNLOADステートメントを使用してOSS外部テーブルとAnalyticDB For PostgreSQLテーブル間でデータをインポートまたはエクスポートする」をご参照ください。
OSS外部テーブルを使用してOSSデータをインポートする方法については、「データレイク分析にOSS外部テーブルを使用する」をご参照ください。
重要
Avroファイルを使用してデータを移行することを推奨します。 STRUCTまたはGEOGRAPHYタイプのデータをAnalyticDB for PostgreSQLに移行することはできません。

構文変換

データ型

BigQueryデータ型	AnalyticDB for PostgreSQLデータ型
INT64	BIGINT
FLOAT64	FLOAT
NUMERIC	DECIMAL
BIGNUMERIC	DECIMAL
BOOL	BOOLEAN
BYTES (2バイトのヘッダー)	BYTES (1バイトのヘッダー)
STRING/STRING()	テキスト /VARCHAR()
日付	日付
日付時刻	TIMESTAMP
時間	時間
TIMESTAMP	TIMESTAMP
INTERVAL	INTERVAL
ARRAY	アレイ []
STRUCT	CREATE TYPE
JSON	JSON
地理	タイプの作成 /地理学

DDL ステートメント

テーブルの作成

PARTITION BY
BigQueryは、次のパーティション分割方法をサポートしています。
- 整数範囲のパーティション分割
  このメソッドは、AnalyticDB for PostgreSQLの番号範囲分割方法に対応しています。数値データ型の列をパーティションキーとして指定できます。次のセクションでは、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 );
```
  上記のステートメントでは、EVERYはBigQueryで整数範囲のパーティションを指定するために使用されるINTERVALに対応しています。
- 時間単位の列のパーティション分割
  このメソッドは、AnalyticDB for PostgreSQLの日付範囲の分割方法に対応しています。パーティションキー列として、DATE型またはTIMESTAMP型の列を指定できます。
  BigQueryは、DAY、MONTH、およびYEARを使用してパーティションの粒度を指定します。 AnalyticDB for PostgreSQLは、EVERY(INTERVAL) を使用してパーティションの粒度を指定します。次のセクションでは、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') );
```
- 摂取時間分割
  このメソッドは、AnalyticDB for PostgreSQLの日付範囲の分割方法に対応しています。 BigQueryの取り込み時のパーティション分割をAnalyticDB for PostgreSQLの日付範囲のパーティション分割に変換する場合、AnalyticDB for PostgreSQLは疑似時間列をサポートしていないため、create_timeという名前の列を宛先テーブルに追加する必要があります。
クラスター
CLUSTER BY句は、AnalyticDB for PostgreSQLのDISTRIBUTED BY句に対応します。各BigQueryテーブルは、最大4つのCLUSTER BY列をサポートします。 AnalyticDB for PostgreSQLでは、DISTRIBUTED BY列の数に制限はありません。
デフォルト崩壊
COLLATE句は、ORDER BYやGROUP BYなどの句の結果をソートするルールを指定します。 DEFAULT COLLATEはデフォルトのルールを示し、バイナリです。ほとんどの場合、この句はデータ移行中に省略できます。

外部テーブルの作成

外部テーブルの接続情報は、BigQueryとAnalyticDB for PostgreSQLで異なります。

BigQuery: WITH CONNECTION句を使用して外部データにアクセスするための資格情報を設定し、接続名をPROJECT_ID.LOCATION.CONNECTION_ID形式で指定できます。
AnalyticDB for PostgreSQL: LOCATION句を使用して、外部テーブルにアクセスするための資格情報と接続情報を設定します。
AnalyticDB for PostgreSQLは、外部テーブルのファイル、gpfdist、およびHTTPプロトコルをサポートしています。 OSSにデータを保存している場合は、oss_fdw拡張機能を使用してOSS外部テーブルを作成し、OSSからAnalyticDB for PostgreSQLにデータをエクスポートできます。

CREATE PROCEDURE

AnalyticDB for PostgreSQLでは、CREATE PROCEDUREステートメントをCREATE FUNCTIONステートメントに置き換えることができます。

その他の SQL 文

マージ

BigQueryを使用すると、INSERT、UPDATE、およびDELETE操作をMERGEステートメントに結合できます。ターゲットテーブルにソーステーブルと一致するタプルが含まれている場合、MERGEステートメントはタプルを更新または削除します。ターゲットテーブルにソーステーブルと一致しないタプルが含まれている場合、MERGEステートメントはタプルを挿入、更新、または削除します。

AnalyticDB for PostgreSQLでは、単一のトランザクションを使用してMERGEステートメントを実行できます。次のサンプルステートメントでは、新しい製品の名前と数量がテーブルに挿入され、テーブル内の既存の製品の数量が更新されます。

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;

選択

AnalyticDB for PostgreSQLは、SELECT * EXCEPT/REPLACEステートメントをサポートしていません。
AnalyticDB for PostgreSQLは、ウィンドウ関数の結果をフィルタリングするためのQUALIFY句をサポートしていません。ウィンドウ関数の結果をフィルタリングする場合は、ネストされたクエリを使用できます。