【BQとSnowflakeのSQL】で格闘した話

概要

BigQueryとSnowflakeのSQLとで書き方や関数の仕様の違いによりSQLと格闘したので備忘録として。

UNNESTとFLATTEN（配列の展開方法）

BQの場合

SELECT
	user_id
	,unnest_column
FROM
	table
,UNNEST(nested_column) AS unnest_column

Snowflakeの場合
TABLE関数とFLATTEN関数を組み合わせて、名前を付けておき、「名前.value」で配列を展開することができる

SELECT
	user_id
	,tn.value AS unnest_column
FROM
	table
TABLE(FLATTEN(table.nested_column)) tn

関数定義（UDF）

BQの場合

create temporary function FUNC() as ('処理の中身');

Snowflakeの場合
型を定義し、「$$」で囲む必要がある

create or replace function FUNC() RETURNS varchar as $$ ('処理の中身') $$;

時刻の差分計算の違い

BQとSnowflakeの時刻の差分掲載は動作が異なるため要注意。個人的にSnowflake側の動作がクセあるなと思いました。

「DAY」単位での差分を計算することを例にしてBQ、Snowflakeの動作をそれぞれ示します。

BQ

BQの場合（23時間59分59秒のズレがある時）

SELECT
  DATE_DIFF('2023-05-09 T 23:39:19'
          , '2023-05-08 T 23:39:20'
          , DAY) AS time_diffence

アウトプットは、「time_diffence --> 0」

BQの場合（24時間0分0秒のズレがある時）

SELECT
  DATE_DIFF('2023-05-09 T 23:39:20'
          , '2023-05-08 T 23:39:20'
          , DAY) AS time_diffence

アウトプットは、「time_diffence --> 1」

BQの場合（24時間0分1秒のズレがある時）

SELECT
  DATE_DIFF('2023-05-09 T 23:39:21'
          , '2023-05-08 T 23:39:20'
          , DAY) AS time_diffence

アウトプットは、「time_diffence --> 1」

Snowflake

Snowflakeの場合（23時間59分59秒のズレがある時）

SELECT
  DATEDIFF(DAY
  		  ,'2023-05-08 T 23:39:20'
          , '2023-05-09 T 23:39:19'
          ) AS time_diffence

アウトプットは、「time_diffence --> 1」

1日経っていないが、time_differenceが1になってしまう。これがややこしい。

Snowflakeの場合（24時間0分0秒のズレがある時）

SELECT
  DATEDIFF(DAY
  		  ,'2023-05-08 T 23:39:20'
          , '2023-05-09 T 23:39:20'
          ) AS time_diffence

アウトプットは、「time_diffence --> 1」

Snowflakeの場合（24時間0分1秒のズレがある時）

SELECT
  DATEDIFF(DAY
  		  ,'2023-05-08 T 23:39:20'
          , '2023-05-09 T 23:39:21'
          ) AS time_diffence

アウトプットは、「time_diffence --> 1」

SnowflakeのDATEDIFF関数では、指定している単位（今回は「DAY」）の数値のズレを計算するため、BQとは異なる結果が出力される。

そのため、例えば、5月8日の23時39分20秒と日付が変わった瞬間の時刻の差分を計算すると、実際には20分程度しかたっていないにもかかわらず、

SELECT
  DATEDIFF(DAY
  		  ,'2023-05-08 T 23:39:20'
          , '2023-05-09 T 00:00:00'
          ) AS time_diffence

アウトプットは、「time_diffence --> 1」となってしまう。

ちなみに、次の場合はDAYが変わっていないので、time_differenceは0となる。

SELECT
  DATEDIFF(DAY
  		  ,'2023-05-08 T 23:39:20'
          , '2023-05-08 T 23:59:59'
          ) AS time_diffence

アウトプットは、「time_diffence --> 0」となる。

BQと同じような出力結果を出すのであれば、
①時間単位を秒単位(SECOND)に設定して何秒の時間差があるかを計算
②①で出した秒数を24×60×60（=24時間の秒数）で割り算する。
③割り算結果の整数部分（例えば2.3であれば、”2”）をtime_differenceに保持する
という処理をすることでBQと同じ結果を出すことができる。

ので、例えば、23時間59分59秒のズレがある時

SELECT
TRUNCATE (
  DIV0NULL (
      DATEDIFF( SECOND
              , '2023-05-08 T 23:39:20'
              , '2023-05-09 T 23:39:19'
              ), 24*60*60
            ), 0
         ) AS time_diffence

こうすれば、アウトプットは「time_diffence --> 0」となり、BQと同じ結果を得ることができる。

また、日付が変わった瞬間の時刻の差分でも

SELECT
TRUNCATE (
  DIV0NULL (
      DATEDIFF( SECOND
              , '2023-05-08 T 23:39:20'
          		'2023-05-09 T 00:00:00'
              ), 24*60*60
            ), 0
         ) AS time_diffence

こうすれば、アウトプットは「time_diffence --> 0」となり、BQと同じ結果を得ることができる。

BOOLEANの違い

WHERE句でBOOLEANのカラムで絞り込みをする場合、BQでは「IS TRUE」と書けるがSnowflakeでは「= TRUE」にしないといけない。

BQ

SELECT COUNT(*)
FROM table
WHERE column1 IS TRUE AND column2 IS NULL

Snowflake

SELECT COUNT(*)
FROM table
WHERE column1 = TRUE AND column2 IS NULL