Amazon QuickSightのSPICEに入れるデータを加工する際に注意すること

はじめに

こんにちは、開発本部のデータ＆AIチームの24新卒の蜜澤です。

現在取り組んでいる業務で、Amazon QuickSight(以下quicksight)を使用しているので、quicksightでSPICEに入れるデータを加工する際に注意することについてまとめたいと思います。
SPICEというのはインメモリエンジンで、SPICEにデータを取り込むことで、クエリ速度の向上とクエリを叩くコストの節約をすることができます。

作成したいデータセット

今回SPICEに入れたいデータは以下のようになっています。
レシピ動画サービスの検索傾向を可視化するために、ユーザーの検索ログを集計したデータという想定です。

• date：日付（2024/07/01~2024/07/07）
• search_word：検索されたワード（ハンバーグ・生姜焼き）
• gender：検索した人の性別（男性・女性・無回答）
• search_count：search_wordが1日に検索された回数
• daily_search_sum：全てのワードが1日に検索された合計回数
• index：検索1000回あたりにつきsearch_wordが何回検索されたかを表す指標

dateとindexに関してはquicksightの計算フィールドにおいて以下のように定義しています。

granularityは文字列のパラメータであり、event_dateは元のデータの日付です。

計算フィールドやパラメータについてはこのブログで解説しているので、気になる方はご覧ください。

目的

日付の集計粒度（日・週）、性別、検索ワードなどのパラメータをインタラクティブに指定できる、indexの集計をすることを目的とします。

例えば、集計粒度が「日」、性別が「男性」、検索ワードが「生姜焼き」と指定すると以下のようになります。

集計粒度を「週」に変えてみます。

search_countとdaily_search_sumの7日分の値が足されて1週間分の値が表示されました。

今回は7日分の模擬データしか用意していませんが、実際の業務となると何年分ものデータがあり、集計粒度は「月」、「年」なども指定できるようになります。　　

このように集計粒度を変更できるようなデータセットを作成するために注意することがあります。

注意点

例を見ただけでは、気をつけることはなさそうに感じますが、実は先ほどの例の中に工夫されている点があります。

それは、、、

search_countが「0」のレコードを残していることです！

え、それだけ？と思う方もいるかもしれませんが、かなり大切なことです。

search_countが「0」のレコードをなくすと以下のようになります。

search_countが「0」のレコードをなくした方のindexは大きくなってしまいます。

集計粒度を「週」にしている場合は、indexは「1週間の検索ワードの検索回数/1週間の全ワードの検索回数*1000」となっていることが期待されますが、search_countが「0」のレコードがないと分母が1週間分にならないため問題となってしまいます。

具体的には、各検索ワードごとのindexを比較する場合や、性別ごとにindexを比較する場合に意味合いが違う指標を比較することになってしまいます。
また、search_countが「0」となるべきレコードが存在せず、かつ、daily_search_sumが小さい場合にindexが極端に大きくなってしまうなどの問題が起きます。

発生原因

search_countが「0」のレコードは意識してデータを加工しないと、データに入らないと思います。
その要因としては、以下の２点があります。

• SPICEを使用するため、毎回生ログデータに対してクエリを叩かないから
• 検索されていないデータはそもそも生ログデータに存在しないから

SPICEを使用する場合は、パラメータを変更するたびにクエリを叩くのではなく、変更されたパラメータに応じてSPICEに入っているデータから条件に合うデータを抽出します。

仮に毎回クエリを叩くのであれば、生ログデータなどからwhere句で条件指定して、必要なdaily_search_sumを毎回集計すれば良いので、模擬データのような日ごとにsearch_countやdaily_search_sumを集計したデータセットを準備する必要はありません。

もしそうすれば、search_countが「0」のレコードを用意せずとも、集計粒度を変えながら正確なindexを算出することができます。

それなら毎回クエリを叩けば良いのではないかと思いますが、毎回クエリを叩くとなるとコストがかかってしまうため、追加コストなしで再利用できるSPICEを使用したいです。

検索回数のデータは、生ログデータなどを加工して作成すると思いますが、count_searchが「0」となるようなデータは生ログデータには含まれていません（検索されていないのだからあたりまえ）。
そのため、search_countが「0」となるレコードは明確な意思を持って、データに入れなければいけません。

対処法

search_countが「0」となるレコードを作るために私がやった方法を紹介します（大したことではないですが）。

以下のような生ログデータからSPICEに入れるデータを作成します。

まずはevent_date,search_word,genderからユニークな要素を抽出し、それらの総当たりをしたテーブルを作成します。
今回の場合だと、event_dateが7通り、search_wordが2通り、genderが2通りなので、28行のテーブルになります。

次に、event_date,search_word,genderでgroup byして、countすることでsearch_countを求め、event_date,genderでgroup byして、countすることでdaily_search_sumを求めます。

最後に総当たりのテーブルに、search_countとdaily_search_sumをjoinして、nullを0に置き換えることで、search_countが「0」のレコードを含むデータセットを作成できます。

sqlで書くと以下のような感じになると思います。

with a as (
    select
        event_date,
        search_word,
        gender
    from
        log_data
    where
        event_date between '2024-07-01' and '20240-07-07'
        and (search_word = 'ハンバーグ' or search_word =　'生姜焼き')
        and (gender = '男性' or gender='女性')
),        
b as (
    select
        event_date,
        search_word,
        gender,
        count(1) as search_count
    from
        a
    group by
        1,
        2,
        3
),        
c as (
    select
        event_date,
        gender
        count(1) as daily_search_sum
    from
        log_data
    group by
        1,
        2        
)
select
    a.event_date,
    a.search_word,
    a.gender,
    coalesce(b.search_count, 0) as search_count
    c.daily_search_sum 
from
    a
    left join
    b
    on a.event_date = b.event_date and a.search_word = b.search_word and a.gender = b.gender
    left join
    c 
    on a.event_date = c.event_date and a.gender = c.gender                               

このようにsearch_countが「0」のレコードを作成したことで、日付の集計粒度を変更しても想定通りの指標で集計できるようになります！

さいごに

今回はSPICEに入れるデータを作成する際の注意点についてまとめました。
この記事がいつか誰かの役に立てば嬉しいです！

最後まで読んでいただきありがとございました。