臺 健太郎（Dai Kentaro） 株式会社Relic テクノロジープラットフォームグループ BigQuery・Glue・S3・QuickSightの連 携をしたお話

色々なやり方があると思います。 ご意見等あれば、発表後にご教示いただけると幸いです。 また、マサカリ大歓迎です。

自己紹介 ● 臺 健太郎（だい けんたろう） ● 出身: 大阪府 ● 株式会社Relic テクノロジープラットフォームグループ/バックエンドエンジニア ● 入社約2年2ヶ月 = エンジニア歴 ● 初めてコードを書いたのは2019/9 ● 業務でよく触る分野 ○ Rails,Nuxt.js,AWS,GCP,Docker,CircleCI,GithubAcitons etc ● twitter: kenkentaro@kenkentarouu ● facebook: https://www.facebook.com/kentaro.di/

● 閲覧数などの情報をGAで管理 ● 自分のプロフィールや投稿が閲覧された回数など を記録して、それをQuickSight上で表示したい。 （もちろんユーザーにも） ● BigQueryのデータをわざわざQuickSightで見ようと する理由は、BigQuery上のデータとRDSのDBデー タをガッチャンこして見たいという要件があったから。

完成形（簡単にざっくりやりたいこと）
 ● QuickSight上で以下のような表示を目指したい。


● 赤枠以外のところがRDSに保存されているDB データ ● 赤枠のところがBigQueryに保存されているデー タ ● それら２つを結合してQuickSight上で一覧表示 したい ● ソートしたりフィルターかけたりCSV出力したりし て分析したい

● アプリ側のコードにGoogleAnalyticsのタグを埋め込み ● ユーザーがイベントを踏むと発火しデータをGoogleAnalyticsへ送信 ● GoogleAnalyticsで取得しているデータをBigQueryに定期的にエクスポートして BigQuery上に永続化しておく ● BigQuery上で1日前のデータが入ったテーブルを作成して日々データを追加して いくクエリの定期実行をスケジューリングする(22:30に実行) ● AWS上でGlueを使いコネクター経由でBigQuery上のテーブルデータをその実行 時点までの全件取得して取り込んでCSV形式に変換してS3へ吐き出す定期実行 のジョブを作成し一度実行 ● AWS上でGlueを使いコネクター経由で1日前に追加されたBigQuery上のテーブ ルデータを取り込んでCSV形式に変換してS3へ吐き出す定期実行のジョブを作成 し1日1回実行するようにスケジューリング(23:00に実行) ● S3へ置いたCSVデータとQuickSightを連携させる ● QuickSight上で連携したCSVデータをとRDSのDBデータとを結合させ一覧表示

文字が多すぎてわからない.............

アプリサーバー FirebaseAnalytics event送信 - pageviewなどの規定イ ベント - カスタムイベント BigQueryのSQLパラメータを 付加してAPIリクエスト アプリサーバーを介して BigQueryの実行結果を受け 取る FirebaseAnalytics BigQuery Google Cloud Platform 連携 ・ネイティブ側はアプリサーバーを介して BigQueryの結果を受け取る ・FAとBigQueryをあらかじめ紐づけておく ネイティブから受け取った BigQueryのSQLパラメータ を使ってBigQueryAPIでリ クエスト １日一回定期的に FAの データをBigQueryにエクス ポートしてBigQuery上で永 続化

FirebaseAnalytics Google Cloud Platform １日一回定期的にFAのデータを BigQueryにエクスポートして BigQuery上で永続化 テーブル BigQuery クエリ定期実行 AWS Glue S3 テーブル生成 （データを日々追加していく） CSV形式へ 変換 ジョブの定期実行の中で S3 へデータを吐き出す CSVデータ を保存 QuickSight データを閲覧 コネクター経 由 Glueのジョブを 定期実行してテーブル データを取り込む RDS 接続 接続

● アプリ側のコードにGoogleAnalyticsのタグを埋め込み ● ユーザーがイベントを踏むと発火しデータをGoogleAnalyticsへ送信 ● GoogleAnalyticsで取得しているデータをBigQueryに定期的にエクスポートして BigQuery上に永続化しておく ● BigQuery上で1日前のデータが入ったテーブルを作成して日々データを追加して いくクエリの定期実行をスケジューリングする(22:30に実行) ● AWS上でGlueを使いコネクター経由でBigQuery上のテーブルデータをその実行 時点までの全件取得して取り込んでCSV形式に変換してS3へ吐き出す定期実行 のジョブを作成し一度実行 ● AWS上でGlueを使いコネクター経由で1日前に追加されたBigQuery上のテーブ ルデータを取り込んでCSV形式に変換してS3へ吐き出す定期実行のジョブを作成 し1日1回実行するようにスケジューリング(23:00に実行) ● S3へ置いたCSVデータとQuickSightを連携させる ● QuickSight上で連携したCSVデータをとRDSのDBデータとを結合させ一覧表示

BigQuery概念
 下に行くにつれて粒度が細かくなります。
 ● プロジェクト
 ● データセット
 ○ データセットとは、BigQueryで扱うテーブルの集合（デー タベースのようなもの）
 ● テーブル
 ○ データセットの中のテーブル


https://cloud.google.com/bigquery/docs/scheduling-queries?hl=ja クエリの定期実行について 定期実行用のクエリを作成し日々のテーブルを生成してい く

● 基本構文 ○ SELECT句にカラムをそのまま指定して、FROMにはプロ ジェクト名.データセット名.テーブル名 BigQueryで使うSQLの基本 SQLを実行するとその結果を元にテーブルを作成することがで きる

● フルマネージドサービス ● 「Glue」というのは「接着剤」 ● AWS上のデータ間のやり取りを管理する「仲介者」 ● 今回はコネクターというものを使いBigQueryとAWSを仲 介 ● ジョブの実行をすることで定期的にBigQueryからデータ を取り込んでS3に出力するために使用 「AWS Glue」とは

こういうジョブのスクリプトを書けます

QuickSightから見る
 ● QuickSightの単位 ○ 分析 ■ ダッシュボード ○ データセット ■ データソース(RDSのデータやS3のCSVデータなど) を元にして作成される概念 ○ データソース ■ データソースはRDBのデータであったりGlueでS3に 吐き出したデータなど

S3をデータソースとして作る場合 ● マニフェストファイルという設定ファイルをアップロードすると 指定のバケットのデータにアクセス可能となる ● Glueで取り込んだS3上のCSVデータが格納されているS3 バケットを指定する

データの結合
 ● S3から取り込んだデータセットと、RDSのデータソースを結 合して閲覧

https://dev.classmethod.jp/articles/20220423-amazon-athena-connect-to-bigquery/ ご参考

ご清聴ありがとうございました！！！