SNOWDAYで紹介しきれなかったRedshiftとSnowflakeの違い42選

REVISIOのエンジニア片岡です。

先日2月14日、Snowflakeの大規模イベントSNOWDAY JAPANに参加・登壇してきました。

登壇資料はこちら。 speakerdeck.com

ANAインターコンチネンタルホテル東京＋オンラインでの開催でしたが、オフラインイベントの独特の雰囲気と盛り上がりを久しぶりに体感できて非常に楽しかったです。

沢山の興味深いセッションや趣向を凝らした会場設営、コミュニティイベント含めてSnowflakeの世界観が感じられたイベントでした。

さて、タイトルにあるようにSNOWDAYでは時間の都合で紹介しきれなかった部分があったので、ここに書いておこうと思います。

RedshiftとSnowflakeは別の製品なので、当然違いは沢山あります。

ただ、Redshiftを色々なシーンで使用している状況から移行しようとするのであれば、ある程度同じ挙動をして欲しいと思うでしょう。

そこで我々が移行するにあたって発見した（何かしらの対応が必要となった）違いについて、一覧を掲載します。

No	内容	Redshift	Snowflake
1	整数同士の除算やAVG	整数で返る	小数で返る
2	VARCHARの桁数指定 `例：VARCHAR(10)`	バイト数で指定	文字数で指定
3	テーブル名、カラム名等のメタデータ	小文字	大文字
4	`::`による型変換をしても元のカラム名で参照できるか（例：`colname::int`）	できる	できないので`colname::int AS colname`のようにする
5	LEAST、GREATESTでNULLが含まれる場合	NULLを除外して評価（全てNULLならNULL）	NULLが1つでもあれば結果がNULLになる
6	VIEW定義でpublicスキーマのオブジェクトを参照する際に”public.”を省略できるか	できる	VIEW作成時にエラーになるので`public.`を付ける必要がある
7	`DELETE テーブル名;` でDELETE可能か	可能	不可。`DELETE FROM テーブル名;`とする必要がある
8	文字列結合する際、`\|\|`以外に`+`でも可能か	可能	不可
9	RANDOM関数の返す値	0.0以上1.0未満を返す	擬似ランダム64ビット整数（非常に大きな整数）を返す
10	1回のSQL文の最大サイズ	16MB	圧縮後1MB
11	LISTAGG関数で一件もヒットしない場合の返り値	NULL	空文字
12	日付カラムに10000年以降の日付を入れられるか	可能	最大は9999/12/31
13	COALESCE関数、NVL関数の引数	引数が1つでもエラーにならない	引数が1つだとエラーになる
14	`SELECT COUNT(table.*)`	COUNT(*)と同じ動作	全てのカラムに1つもNULLがない行数を返す
15	日付差異の関数で`DATEDIFF`の他に`DATE_DIFF`が使えるか	使える	使えない（存在しない）
16	日付加算の関数で`DATE_ADD`が使えるか	使える	使えない（存在しない）
17	日付関数の部分指定を`'dow'::text`のようにキャストできるか	できる	できない（エラー）
18	配列の記述方法	`ARRAY[]`	ARRAYなしの`[]`または`ARRAY_CONSTRUCT()`
19	`LISTAGG(DISTINCT NAME) WITHIN GROUP(ORDER BY ID)`のようにORDERとDISTINCTのカラムが異なっても実行可能か	可能	不可。ORDERとDISTINCTのカラムは完全に同じでないとエラーになる
20	四捨五入する関数	`CEILING`	`CEIL`
21	現在時刻を`'now'`で取得可能か	可能	不可
22	DATE型を整数型と比較可能か	可能	不可
23	現在日付関数	`SYSDATE`	`SYSDATE()`
24	CROSS JOINの場合、`JOIN(SELECT …) JOIN(SELECT …)`のように複数の無名テーブルを同時に使用できるか	可能	エラーになるため別名をつける必要あり
25	日本のタイムゾーンのエイリアス	JST	Japan
26	BOOL型の判定式	`col IS FALSE` `col IS TRUE`	`col= FALSE` `col= TRUE`
27	TABLE作成時のLIKE構文	`CREATE TABLE …(LIKE…)`と()でLIKEを括る	`CREATE TABLE … LIKE…`と()でLIKEを括らない
28	'YYYYMMDD'型の文字列は`::DATE`で日付型に変換可能か	可能	~~不可。`TO_DATE`が必要~~ パラメータ設定すれば可能。`例：alter session set DATE_INPUT_FORMAT = 'YYYYMMDD';`
29	`ROW_NUMBER()`のOVERはOVER()とORDER BYを省略可能か	可能	`OVER(ORDER BY TRUE)`のように必ず引数をつける必要あり
30	`ROW_NUMBER()... LIMIT 3`	LIMITの前にORDER BYをつけなくても0,1,2を返す	LIMITの前にORDER BYをつけないと不定の連番を返す
31	REGEXP_REPLACE('(...)','$1')のように正規表現のグループ化へのアクセスに$が使用可能か	可能	不可。`REGEXP_REPLACE('(...)','\\1')`のように`\\`を使用する必要がある
32	12時間を表すフォーマット文字列	`HH`または`HH12`	`HH12`のみ
33	ミリ秒、マイクロ秒のフォーマット文字列	ミリ秒は`MS`、マイクロ秒は`US`	ミリ秒は`FF3`、マイクロ秒は`FF6`
34	時刻の小数点桁数	6桁	9桁
35	正規表現でPOSIX以外にPCREが使用可能か	可能	不可
36	特殊な正規表現として`SIMILAR TO`が使用可能か	可能	不可
37	PRIMARY KEY制約がSQLの結果に影響を与えるか	PRIMARY KEYカラムに同値が複数あるとSELECTで1つしか結果が返ってこないことがある	PRIMARY KEYカラムに同値が複数あってもSELECTで全ての結果が返ってくる
38	複数の配列を単一の配列にマージする`ARRAY_FLATTEN`関数が存在するか	存在する	存在しない
39	GROUP BY内でSUMを引数としたUDFが使用可能か。例：`SELECT f_myudf(SUM(col1)) … GROUP BY …`	可能	不可
40	テーブル定義DDLで右記が存在するか。DISTSTYLE、DISTKEY、SORTKEY、SORTKEY(col)、ENCODE、INTERLEAVED	存在する	存在しない
41	データをS3へデータ出力するSQL	`UNLOAD`	`COPY INTO <場所>`
42	S3からテーブルにロードするSQL	`COPY`	`COPY INTO <テーブル>`