# NG
select
name,
comment,
from SAMPLE.tablewheredatebetweendate('2021-04-27') anddate('2021-05-05') and
name = 'name1'orcomment = 'comment1'
# OK
select
name,
comment,
from SAMPLE.tablewheredatebetweendate('2021-04-27') anddate('2021-05-05')
and name = 'name1'andcomment = 'comment1'
条件が複雑で長くなっている際に and や or が行末についていると見づらいというのがありました。
先頭に書いておけば、条件のかたまりの切れ目が分かりやすく良いのではという議論がありました。
サブクエリではなく、 with 句を使う
# NG
select
name,
sum_price
from (
select
name,
sum(price) sum_price,
from SAMPLE.tablewheredatebetweendate('2021-04-27') anddate('2021-05-05')
groupby name
)
where
sum_price >= 10000
# OK
with
t_base as (
select
name,
sum(price) sum_price,
from SAMPLE.tablewheredatebetweendate('2021-04-27') anddate('2021-05-05')
groupby name
)
select
name,
sum_price
from t_base
where sum_price >= 10000
主な理由としては
一つのクエリが大きくなりすぎない
ネストが深くなりすぎない
同じクエリを何度も再利用できる
の3点が挙がりました。可読性だけでなく、再利用性の観点からも with 句を使うようにしています。
ビフォーアフター
以下、全ての規約を適用したときのビフォーアフターの一例です。
# 適用前
SELECTdate,
target_name,
SUM(price) sum_price,
AVG(price) avg_price,
FROM (
SELECTdate,
CASEWHEN regecp_contains(name, r'regexp1') THEN'name1'WHEN regecp_contains(name, r'regexp2') THEN'name1'ELSEnullEND target_name,
price,
FROM DataBase.table1
WHEREdateBETWEENDATE('2021-01-01') ANDDATE('2021-01-31'))
WHERE target_name ISNOTNULLGROUPBYdate, target_name
GROUPBYdate, target_name
# 適用後
with
t_base as (
selectdate,
casewhen regecp_contains(name, r'regexp1') then'name1'when regecp_contains(name, r'regexp2') then'name1'elsenullend target_name,
price,
from DataBase.table1
wheredatebetweendate('2021-01-01') anddate('2021-01-31')
)
selectdate,
target_name,
sum(price) sum_price,
avg(price) avg_price,
from t_base
where target_name isnotnullgroupbydate, target_name
orderbydate, target_name
with
const as (
selectdate('2021-04-01') st, date('2021-04-30') en
),
date_array as (
selectdate(sequence) item,
from unnest(generate_date_array('2021-01-01', '2021-05-01')) sequence
)
select
date_array.*
from date_array, const
where
item between const.st and const.en
このように定義した const というサブクエリを CROSS JOIN することで利用します。
都度 from に書く必要があって少し面倒です。
create temp function const()
as (
struct(date('2021-04-01') as st, date('2021-04-30') as en)
);
with
date_array as (
selectdate(sequence) item,
from unnest(generate_date_array('2021-01-01', '2021-05-01')) sequence
)
select
date_array.*
from date_array
where
item between const().st and const().en
上記の場合では、構造体を返すようにすることで複数の定数を一つの関数で定義できるようにしています。 with 句を利用する方法と違って CROSS JOIN する必要がありません。
デメリットとしては、 JOIN する際の条件のなかで利用できないという点があります。
例えば以下のようなケースでは Subquery in join predicate should depend on one of join sides. のようなエラーがでます。
create temp function const()
as (
struct(date('2021-04-01') as st, date('2021-04-30') as en)
);
with
date_array as (
selectdate(sequence) item,
from unnest(['2021-01-01', '2021-02-01', '2021-04-01','2021-05-01']) sequence
),
date_array2 as (
selectdate(sequence) item,
from unnest(['2021-01-01', '2021-02-01', '2021-04-01','2021-05-01']) sequence
)
select
date_array.*
from date_array
join date_array2 on date_array.item = date_array2.item and date_array2.item = const().st
where date_array.item between const().st and const().en
declare st, en date;
set st = date('2021-04-01');
set en = date('2021-04-30');
with
date_array as (
selectdate(sequence) item,
from unnest(generate_date_array('2021-01-01', '2021-05-01')) sequence
)
select
date_array.*
from date_array
where item between st and en
with 句や UDF を利用する方法と違い CROSS JOIN する必要もなく、JOIN の条件の中でも利用できます。
また他の方法よりも可読性も良く、現時点では一番良い方法だと思います。
init:credential-file: /.gcp/credential.json
service: bigquery
in:query: |
select
id
from Dataset.users
out:project: project
database: Destination
table: users
mode: merge
merge:order:- column: modified
desc:Truekeys:- id
パーティション
下記の例のように、宛先テーブルのパーティションも定義することができます。
init:credential-file: /.gcp/credential.json
service: bigquery
in:query: |
select
id
from Dataset.users
out:project: project
database: Destination
table: users
mode: append
partition:mode: time
field: created
type: DAY
利用例
日毎に新規ユーザー数を集計して保存しておきたい
宛先テーブルの最新の created 以降に作成された未集計の users を集計する
init:credential-file: /.gcp/credential.json
service: bigquery
in:query: |
select
date(created) created,
count(distinct id) user_count,
from Sample.users
where
date(created) > date('${last_created}')
and date(created) <= date_sub(current_date, interval 1 day)
vars:- name: last_created
database: Destination
table: new_users
mode: max
field: created
out:project: project
database: Destination
table: new_users
mode: append
partition:mode: time
field: created
type: MONTH
特定のイベントのみ整形して保存しておきたい
宛先テーブルの最終更新日から昨日までのデータを append するように定義する
init:credential-file: /.gcp/credential.json
service: bigquery
in:query: |
select
timestamp_micros(event_timestamp) event_time,
user_id,
user_pseudo_id,
(select value.string_value from unnest(event_params) event_param where key = 'screen_class') screen_class,
device.category device_category,
device.operating_system os,
device.operating_system_version os_version,
from Sample.events
where
event_name = 'screen_view'
and date(timestamp_micros(event_timestamp)) between date('${last_modified}') and date_sub(current_date, interval 1 day)
vars:- name: last_modified
database: Destination
table: screen_views
mode: meta
field: last_modified_time
out:project: project
database: Destination
table: screen_views
mode: append
partition:mode: time
field: event_time
type: DAY
Da Vinci Studio インフラ基盤部データ分析チームの濱口です。
Da Vinci Studio のデータ分析チームでは、グループ内に溜まっている膨大なデータを日々分析・集計しています。そうして得られた学びについては、公開可能なものに限り Redash のダッシュボードとしてグループ内で確認できるようにしています。
ただ公開して終わりではなく、ダッシュボードがどれくらい見られているのかも計測して、グループ内のデータへの関心の把握やダッシュボードの改善などに繋げたいですよね。
というわけで今回は Redash 内のクエリやダッシュボードの閲覧状況を可視化した話についてまとめておこうと思います。
Da Vinci Studio インフラ基盤部データ分析チームの濱口です。
今回は Da Vinci Studio のデータ分析チームについて紹介したいと思います。
Da Vinci Studio の「データ分析チーム」
Da Vinci Studio のデータ分析チームは現在は 3 名体制で、くふうグループ横断のデータ分析を担当しています。
まだ最近できたばかりの比較的新しいチームで、データ分析を通してユーザーや社会により良い価値を届けるために日々活動しています。
まずはそんなデータ分析チームのミッション・ビジョン・バリュー(MVV)について紹介したいと思います。
データ分析チームにおけるMVV
紹介の前に、チームなのにMVV？と思われた方もいらっしゃるかもしれません。その疑問の通りで Da Vinci Studio 全体としてミッション・ビジョンは掲げられているので、全てのチームがミッションを定めているわけではありません。
しかしデータ分析チームは新しくできたばかりで、実績を積み重ねるために日々多くの業務をこなしながら、手探りで進んでいる状態でした。メンバーそれぞれのやっていることがチームの目指したい理想像や目的から逸れていないかを振り返ることができるようにMVVを決めるに至りました。
ミッション
まずはミッションですが、Da Vinci Studio ではテクノロジー(エンジニアリング/デザイン)の力を引き出すというミッションを掲げています。それを踏まえた上でデータ分析チームでは
