PostgreSQLからSnowflakeへ 移行の検討 -テーブル定義の移行-
sfadminPostgreSQLからSnowflakeへの移行方法を検討したため、備忘録を兼ねて検討した内容とやったことをまとめたいと思います。
「テーブル定義の移行」と「データ移行」を前編と後編に分けて掲載します。
本記事は前編「テーブル定義の移行」となります。
目次
- はじめに
- Snowflakeへの移行を検討した理由
- クエリパフォーマンス
- データレイク
- データのバックアップ・復元
- PostgreSQL ⇒ Snowflake テーブル定義の移行
- Snowflakeのデータ型
- PostgreSQLのデータ型
- PostgreSQL ⇒ Snowflake データ型置換表
- テーブル定義の移行
- まとめ
1. はじめに
Snowflakeとは
クラウド型データプラットフォームで、大容量のデータを扱うために設計されています。
Snowflakeはコンピュートノードとストレージが別々になっているため、ワークロードに応じて必要なデータストレージを必要なだけ割り当てられるという利点があります。
また、Snowflakeはデータ共有が非常に簡単で、ユーザーの役割やグループなどで分けたロール単位でのアクセス制御を行う仕組みをとっています。
Snowflakeはデータの取り込み、統合、分析、安全な共有において必要な性能、柔軟性、スケーラビリティを提供し、パフォーマンスが高いという特徴があります。
2. Snowflakeへの移行を検討した理由
データ連携システムの開発プロジェクトでPostgreSQLをDBとして採用したのですが、将来的にはデータを蓄積し、統計、分析がしたいという要望がありました。
PostgreSQLで大量のデータを蓄積すると、データ連携自体のパフォーマンスが悪くなってしまうことが確認されたため、蓄積データの移行先を検討することになりました。
Snowflakeを移行先として採用し、移行方法を検討した理由は大きく３点あります。
・大容量データの統計、分析が高パフォーマンスでできる「クエリパフォーマンス」
・様々な形式のデータをインポートできる「データレイク」
・複雑な運用設計が必不要である「データのバックアップ・復元」
クエリパフォーマンス
Snowflakeが移行先として最適と考えた大きな理由が「クエリパフォーマンス」の高さです。
Snowflakeテーブルに登録したすべてのデータはマイクロパーティションという単位に自動的に分割され、この構造によって効率的なプルーニングが実行されるため、クエリのパフォーマンスがアップするとのことです。
※プルーニング：クエリの実行に必要なデータだけを取得する機能
Snowflakeではコンピューティングリソースのスケールアップ、またはスケールアウトが容易に設定できるため、データ要件に合わせてサイズの設定ができます。
運用開始後にデータ量が増えてパフォーマンスが劣化しそう、というような心配はしなくてよさそうです。
また、マルチクラスターウェアハウスを使用すれば、パフォーマンスに影響無く同時アクセスが可能なため、使用するユーザー数が増えた場合も問題なさそうです。
参考）マルチクラスターウェアハウス | Snowflake Documentation
データレイク
データを移行後、将来的には様々なシステムからデータを1か所に集め、総合的に分析できるようにしたいという構想があります。
そのため、できる限りデータのインポート（データロード）作業を簡略化したいと考えています。
Snowflakeはデータファイルとしてよく使用されるCSV形式のファイルだけでなく、半構造化データや非構造化データのファイルのインポート機能が標準で備わっています。
エクスポート元のシステムでデータ抽出する際にデータの形式を合わせる必要がなく、臨機応変に対応できそうです。
参考）データロード機能の概要 | Snowflake Documentation
データのバックアップ・復元
データ移行後は、Snowflakeへ日々データ連携をしてデータを蓄積していく計画を立てており、日々積み上げられていくデータのバックアップとその復元方法をどうすべきかが課題でした。
Snowflakeはデータのバックアップと復元を自動的に管理する仕組みがあります。
また、Snowflakeのゼロコピークローン機能は、実際のデータを使用した検証作業で活用できそうですし、データパッチ作業も安心・安全に実施できそうです。
参考）Snowflake Time TravelおよびFail-safe | Snowflake Documentation
データストレージに関する考慮事項 | Snowflake Documentation
3. PostgreSQL ⇒ Snowflake テーブル定義の移行
PostgreSQLからSnowflakeへ移行するために、テーブル定義の移行について検討しました。
Snowflakeのデータ型
Snowflakeは他のDBとの互換性を考慮しているためデータ型が豊富に存在し、PostgreSQLに存在するデータ型も一部サポートされていました。
実際には、Snowflake上でテーブル作成時に同義の型に自動変換される仕組みになっています。
桁数や精度等はデータ型のデフォルトの設定で定義されます。
例）integer型で列のデータ型を定義しテーブル作成 ⇒ 自動的にNUMBER(38, 0)で定義される
桁数や精度等を指定したい場合は、自動変換に頼らず、手動で定義をしてあげる必要があります。
例）integer型で列のデータ型を定義しテーブル作成をするとNUMBER(38, 0)、つまり最大桁数が38桁になるが、
最大桁数を38桁ではなく、9桁で定義したい。
⇒ NUMBER(9, 0)で列のデータ型を定義しテーブル作成する
参考）データ型の概要 | Snowflake Documentation
| Snowflake データ型の分類 | Snowflake サポートされているデータ型 | 説明 | 桁数(精度)やスケールのデフォルト |
|---|---|---|---|
| 数値データ 固定小数点数のデータ型 | NUMBER DECIMAL NUMERIC INT INTEGER BIGINT SMALLINT TINYINT BYTEINT | 全てNUMBER型と同義語のため、 テーブル作成時にNUMBER型に自動変換される 精度とスケールの設定が可能 NUMBER(precision, scale) | NUMBER(38, 0) |
| 数値データ 浮動小数点数のデータ型 | FLOAT FLOAT4 FLOAT8 DOUBLE DOUBLE PRECISION REAL | 全てFLOAT型と同義語のため、 テーブル作成時にFLOAT型に自動変換される 全て64ビットの浮動小数点数として扱われる | - |
| 数値データ 連番のデータ型 | なし | シーケンスオブジェクトは用意されている シーケンスの使用 | Snowflake Documentation | - |
| 文字型 | VARCHAR CHAR CHARACTER NCHAR STRING TEXT NVARCHAR NVARCHAR2 CHAR VARYING NCHAR VARYING | 全てVARCHAR型と同義語のため、 テーブル作成時にVARCHAR型に自動変換される 桁数を指定していれば、その桁数で定義される 未指定であればデフォルトの桁数となる UTF-8文字を保持 | VARCHAR(16777216) |
| バイナリ列データ型 | BINARY VARBINARY | 全てBINARY型と同義語のため、 テーブル作成時にBINARY型に自動変換される 最大長は8 MB（8,388,608バイト） | - |
| 日付/時刻データ 日付のデータ型 | DATE | 日付を保存するための単一の DATE データ型（時間要素はなし） | - |
| 日付/時刻データ 時刻のデータ型 | TIME | 時刻を HH:MI:SS の形式で保存するための単一の TIME データ型 時間精度の範囲は、0（秒）から9（ナノ秒） | TIME(9) |
| 日付/時刻データ タイムスタンプのデータ型 （タイムゾーンなし） | TIMESTAMP DATETIME TIMESTAMP_NTZ TIMESTAMPNTZ TIMESTAMP WITHOUT TIME ZONE | タイムゾーン無しのタイムスタンプ タイムスタンプの精度は、0（秒）から9（ナノ秒）の範囲 | TIMESTAMP_NTZ(9) |
| 日付/時刻データ タイムスタンプのデータ型 （ローカルタイムゾーンあり） | TIMESTAMP_LTZ TIMESTAMPLTZ TIMESTAMP WITH LOCAL TIME ZONE | TIMEZONEセッションパラメーターによって制御される現在のセッションのタイムゾーンで実行される タイムスタンプの精度は、0（秒）から9（ナノ秒）の範囲 | TIMESTAMP_LTZ(9) |
| 日付/時刻データ タイムスタンプのデータ型 （指定されたタイムゾーンあり） | TIMESTAMP_TZ TIMESTAMPTZ TIMESTAMP WITH TIME ZONE | 各レコードに固有のタイムゾーンオフセットで実行される タイムスタンプの精度は、0（秒）から9（ナノ秒）の範囲 | TIMESTAMP_TZ(9) |
| 論理値データ型 | BOOLEAN | TRUE または FALSE | - |
| 半構造化データ型 | VARIANT ARRAY OBJECT | VARIANT の最大サイズは16 MB 半構造化データ型 | Snowflake Documentation | - |
| 構造化データ型 | ARRAY OBJECT MAP | 特定の Snowflakeデータ型 を持つ要素またはキーと値のペアを含む型のことを指す 構造化データ型 | Snowflake Documentation | - |
| 地理空間データ型 | GEOGRAPHY GEOMETRY | 地理空間データ型 | Snowflake Documentation | - |
PostgreSQLのデータ型
PostgreSQLには多種多様なデータ型が用意されています。
PostgreSQLで用意されているデータ型を一覧化し、Snowflakeへ移行可能かどうかを調べてみました。
| PostgreSQL データ型の分類 | PostgreSQL データ型 | Snowflakeに ある○orない× | Snowflakeでの代替手段 |
|---|---|---|---|
| 数値データ 固定小数点数のデータ型 | smallint integer bigint decimal numeric | ○ 全てNUMBER型として定義可能 | - |
| 数値データ 浮動小数点数のデータ型 | real double precision | ○ 全てFLOAT型として定義可能 | - |
| 数値データ 連番のデータ型 | smallserial serial bigserial | × | NUMBER型 + シーケンスオブジェクトの作成 ※データ移行時はシーケンス不要 |
| 通貨型 | money | × | NUMBER型 |
| 文字型 | character varying(n) varchar(n) character(n) char(n) text | ○ 全てVARCHAR型として定義可能 | - |
| バイナリ列データ型 | bytea | ○ BINARY型として定義可能 | |
| 日付/時刻データ 日付のデータ型 | date | ○ DATE型として定義可能 | |
| 日付/時刻データ 時刻のデータ型 （タイムゾーンなし） | time [ (p) ] [ without time zone ] | ○ TIME型として定義可能 | - |
| 日付/時刻データ 時刻のデータ型 （タイムゾーンあり） | time [ (p) ] with time zone | × タイムゾーンありの時刻型はない | TIME型 （タイムゾーンなし） |
| 日付/時刻データ タイムスタンプのデータ型 （タイムゾーンなし） | timestamp [ (p) ] [ without time zone ] | ○ TIMESTAMP_NTZ型として 定義可能 | |
| 日付/時刻データ タイムスタンプのデータ型 （タイムゾーンあり） | timestamp [ (p) ] with time zone | ○ TIMESTAMP_LTZ 型として 定義可能 | |
| 日付/時刻データ 時間間隔のデータ型 | interval [ fields ] [ (p) ] | × | 値の保管が目的であればVARCHAR型 |
| 論理値データ型 | boolean | ○ BOOLEAN型として定義可能 | |
| 列挙型 | ENUM | × | 値の保管が目的であればVARCHAR型 |
| 幾何学的型 | point line lseg box path polygon circle | × | 値の保管が目的であればVARCHAR型 |
| ネットワークアドレス型 | cidr inet macaddr | × | 値の保管が目的であればVARCHAR型 |
| ビット列データ型 | bit(n) bit varying(n) | × | 値の保管が目的であればVARCHAR型 |
| テキスト検索に関する型 | tsvector tsquery | × | 値の保管が目的であればVARCHAR型 |
| UUID型 | uuid | × | 値の保管が目的であればVARCHAR型 |
| XML型 | xml | ○ VARIANT型として定義可能 | |
| JSON型 | json | ○ VARIANT型として定義可能 | |
| 範囲の型 | int4range int8range numrange tsrange tstzrange daterange | × | 値の保管が目的であればVARCHAR型 |
| pg_lsn 型 | pg_lsn | × | 値の保管が目的であればVARCHAR型 |
PostgreSQL ⇒ Snowflake データ型置換表
以上を考慮し、テーブル定義のデータ型の置換表を作成しました。
Snowflakeに用意されていないデータ型は、用途により代替手段を検討する必要がありそうです。
値の保管が目的であり単純に値を移行するだけでよければ、VARCHAR型への移行になると思います。
Snowflakeへ移行する必要があるデータなのか、も検討したほうがよいでしょう。
| PostgreSQL 移行元のデータ型 | ⇒ 桁数(精度)・スケール等の指定 | Snowflake 定義されるデータ型 |
|---|---|---|
| smallint integer bigint decimal numeric | 指定なし ⇒ 移行元のデータ型をそのまま使用する 指定したい ⇒ NUMBER(precision, scale) で定義する | 指定なしの場合 ⇒ NUMBER(38,0) 指定した場合 ⇒ NUMBER(precision, scale) |
| numeric(precision, scale) | 移行元のデータ型をそのまま使用する | NUMBER(precision, scale) |
| real double precision | 移行元のデータ型をそのまま使用する | FLOAT |
| smallserial serial bigserial | 指定なし ⇒ NUMBER で定義する 指定したい ⇒ NUMBER(precision, scale) で定義する | 指定なしの場合 ⇒ NUMBER(38,0) 指定した場合 ⇒ NUMBER(precision, scale) ※連番情報は無く、値のみ |
| money | 指定なし ⇒ NUMBER で定義する 指定したい ⇒ NUMBER(precision, scale) で定義する | 指定なしの場合 ⇒ NUMBER(38,0) 指定した場合 ⇒ NUMBER(precision, scale) |
| character varying(n) varchar(n) character(n) char(n) | 移行元のデータ型をそのまま使用する | VARCHAR(n) |
| text | 指定なし ⇒ 移行元のデータ型をそのまま使用する 指定したい ⇒ VARCHAR(n) | 指定なしの場合 ⇒ VARCHAR(16777216) 指定した場合 ⇒ VARCHAR(n) |
| bytea | BINARY | BINARY |
| timestamp [ (p) ] [ without time zone ] | TIMESTAMP [ (p) ] | TIMESTAMP_NTZ [ (p) ] |
| timestamp [ (p) ] with time zone | TIMESTAMP_LTZ [ (p) ] | TIMESTAMP_LTZ [ (p) ] |
| date | 移行元のデータ型をそのまま使用する | DATE |
| time [ (p) ] [ without time zone ] | TIME [ (p) ] | TIME [ (p) ] |
| boolean | 移行元のデータ型をそのまま使用する | BOOLEAN |
| bit(n) bit varying(n) | VARCHAR(n) | VARCHAR(n) |
| xml | VARCHAR または OBJECT | VARCHAR(16777216) または OBJECT |
| json | VARIANT | VARIANT |
| その他 Snowflakeにデータ型が用意されていない型 | 文字列として移行する場合 ⇒ VARCHAR ※桁数指定の場合：VARCHAR(n) 文字列以外の型として移行する場合 ⇒ 利用目的により要検討 | 文字列として移行した場合 ⇒ VARCHAR(16777216) ※桁数指定の場合：VARCHAR(n) |
テーブル定義の移行
上記置換表に沿って、テーブル定義の移行（Snowflakeにテーブル作成するためのDDLの生成）を実施しました。
今回はひとまずSnowflakeにデータを移行することを優先したため、桁数や精度は厳密に指定しませんでした。
しばらく運用してみて、不都合がでてきたら再検討しようと思います。
またDDL生成に関する注意点として、Snowflakeは他のデータベースとの互換性を考慮し、様々なSQL制約(UNIQUE、PRIMARY KEY、NOT NULLなど)をサポートしていますが、強制する制約は「NOT NULL」制約のみだそうです。
例えばPRIMARY KEYをつけてテーブルを作成したとしても、強制された制約ではないためPK重複データを挿入することが可能となっています。
5. まとめ
本記事では、PostgreSQL からSnowflakeへのテーブル定義の移行について検討した内容をまとめました。
後編の記事では実際にデータを移行した際の検討内容をまとめていきます。
