0
Help us understand the problem. What are the problem?
Organization

日時にてデータを抽出する際にハマったこと(Snowflake)

はじめに

Snowflakeのデータを日時にて抽出する際に想定した出力が得られないことがあったので,解決策と共に書きたいと思います.

準備

まず初めにデータ抽出元のテーブルを作成します.(データベース・スキーマは作成済みとする)
ここでは単純なid・created_at・titleの3つで構成されているテーブルを作成しています.

create or replace table test_table(
    id number autoincrement,
    created_at TIMESTAMP_TZ,
    title string
);

中身を確認してみます.何もデータを挿入していないため空です.

select 
    * 
from 
    test_table;

次にランダムなデータを作成し,そのデータを挿入してみます.
なお日時に関してはUTCタイムゾーンにて挿入します.

insert into test_table(created_at, title)
select 
    dateadd(
        hour,
        row_number() over (order by true) - 1,
        '2020-01-01T00:00:00Z'::timestamp_tz
    ),
    randstr(10, random())
from table(generator(rowcount => 100000));

ランダムなデータを挿入するSQLとして下記URLに記載されているSQLを少し書き換えて利用しています.
Snowflake でダミーデータを生成する

抽出してみる

日時で抽出してみます.

select 
    * 
from
    test_table
where 
    created_at between '2020-01-01 00:00:00' and '2021-01-01 00:00:00';

結果を降順にソートした結果が以下の通りになります.なぜか範囲外のデータが含まれています.
qiita_snowflake_2.png

原因

データのタイムゾーンとクライアントのタイムゾーンがずれていることが原因です.
日時データはUTCで入れています.しかしクライアントのタイムゾーンを下記のSQLを用いてみてみると…

show parameters like '%timezone%';

qiita_snowflake_3.png

何故かアメリカ(ロス)のタイムゾーンになっており,データのタイムゾーンとクライアント側のタイムゾーンがずれています.

解決策

一時的な解決策

セッションにおいてタイムゾーンをUTCに変更することでそのセッションにおいては解決することが出来ます.

alter session set timezone = 'UTC';

恒久的な解決策

全ての日時データがUTCで入っていて,全てUTCにて見たい!という人はアカウントに設定されているタイムゾーンをUTCに変更することで,恒久的に解決することが出来ます.

alter account set timezone = 'UTC';

確認

先程と同じように日時で抽出してみます.

select 
    * 
from
    test_table
where 
    created_at between '2020-01-01 00:00:00' and '2021-01-01 00:00:00';

結果を降順にソートした結果が以下の通りになります.範囲外のデータがきちんと除外されています.

qiita_snowflake_4.png

おまけ

タイムゾーンをデフォルトに戻す方法

alter session unset timezone;
alter account unset timezone;

まとめ

  • きちんとデータのタイムゾーンとクライアントのタイムゾーンを確認しておこう
  • Snowflakeに限らずデータの時刻とクライアントの時刻がずれているということはあるのできちんと確認しておこう

参考文献

Register as a new user and use Qiita more conveniently

  1. You can follow users and tags
  2. you can stock useful information
  3. You can make editorial suggestions for articles
What you can do with signing up
0
Help us understand the problem. What are the problem?