男子新体操とはエンジニアリングである

この記事は個人ブログと同じ内容です


はじめに

私はエンジニアであり、男子新体操というスポーツをやっており、プライベートの時間に練習し、大会に出場してたりします。 私は個人競技をやっており、飽き性なため、4種目の演技を毎年作り変えています。 ※一般的には1つの演技を作ったら2~3年、長い人は7年ほど同じ演技をします

エンジニアを仕事としてやっていくうちに、男子新体操での演技作りと似ている部分が多いと、かなり前から感じていたので、面白いと思ってこの記事を書いてみました。

男子新体操とは

団体

youtu.be

個人

youtu.be

ダンスとアクロバットを混ぜたようなスポーツで、団体は6人で行い、個人は1人で4種目(スティック、リング、ロープ、クラブ)を行うスポーツです。 女子の新体操とは全く異なり、女子は華麗なスポーツであるのに対して、男子新体操はダイナミックで迫力があり、カッコいいスポーツです。 日本発祥で日本のみで、行われているスポーツです。

男子新体操のルール

  • 20点満点の減点方式
  • クロバット3回や投げの回数など、最低限のルールはあるがその他は自由
  • トリッキングやバレエ、ダンスなどのほか競技の技を入れても良い

男子新体操だけではなく、クリエイティビティを必要とするスポーツ全般に言えることですが、 100点の演技は存在しないので、曲を変えたり動きを変えたりして、無限に遊ぶことができるスポーツです。

答えのない不確実なものを試行錯誤し、形にしていくという点では、スポーツもビジネスも考え方は同じだと思いました。

演技(プロダクト)作りのやり方

私は以下の流れで演技作りをやっています。

市場調査 → 全体案を考える → 小さく作っていく → 全体で合わせる → 改修

私の場合は、新しいものを作るのではなく、上手い人のを盗んで、それを組み合わせて演技を作っていきます。 新しいものではなく、既存のものを組み合わせたり、プラスしていって作る感じです。 これは新しいビジネスアイディアを考える時と全く同じです。

市場調査

男子新体操は毎年レベルアップしているので、昔の演技をやっても点数が出るわけではなく、その年によって求められるものが変わってきます。 なので、はじめに前年の人の演技を見たり、審判からの聞き込み調査をして、どんな傾向があるかを調査します。

全体案考える

市場調査をもとに、ざっくり演技全体を作っていきます。 曲をベースに演技を作るので、曲を聴き、どこにどの動きをハメたいか、全体の動きの流れなどをざっくり決めます。

小さく作っていく

全体案をもとに、演技を4分割して、それぞれのパートを作っていきます。 それらを組み合わせて、全体感を見て違ったら、減点項目になりそうなものや違和感のある部分を直します。 ある程度の演技ができたら一旦完成です。

ここで意識しているのは、 最初から完璧なものを作らないし、求めない ということです。 自分が良くても、他の人から見ると違和感があるなど、演技には不確実要素が多いので、 私の場合、「音に合う、ある程度の流れが良い」という 最低限の要件を満たしたMVP(Minimum Viable Product)を作っていきます。

最低限の演技ができたら、 人のアドバイスや、もっと良くなりそうなポイントを練習で探し、それを元に演技を無限に改善していきます。

エラーハンドリングを考える

投げを落とした時などのミスをした後に、演技に影響が出ないように、そのパターンも想定して演技を作っています。 事前に復旧しやすい可用性の高い演技設計を意識しています。

最後に

この記事で語ったことは、あくまで私の場合なので、他の人の作り方は知りませんし、違っている可能性があります。 全日本大会で上位のチームたちは、私と違い、常に新しいものを開発し、取り入れてます。 この記事を通して少しでも男子新体操というスポーツを理解してもらえたら嬉しいです。


現在 back check 開発チームでは一緒に働く仲間を募集中です。 https://herp.careers/v1/scouter/j25fiAusU1v1herp.careers

TROCCO と BigQuery で始める ETL 入門

この記事は個人ブログと同じ内容です

www.ritolab.com


データ分析基盤を構築する上で欠かせないのが ETL ツールです。外部データソースの取り込みを一元管理し、分析基盤のデータを最新に保ちます。

今回は、TROCCO と BigQuery を使って分析基盤を構築し、データ処理の流れを体験してみましょう。

対象

本記事は、TROCCO を使ってみたい。分析基盤を構築してみたい人が対象です。

TROCCO と GCP を利用します。個人利用のアカウントを使い、ハンズオン形式で進めていけます。

基本的には無料の範囲で実施しますが、扱うデータによって GCP 側で課金が発生する場合があります。無料範囲で実施したい場合は、本記事で提供するデータを使用してください。

(本記事で作成、有効化していく各リソースは、ハンズオン終了後、不要になったら自身の環境から削除、ないし無効化してください。)

TROCCO

TROCCO は、外部データソースの取り込みからデータマートの生成までをカバーする ETL ツールです。

TROCCO®は、ETL/データ転送・データマート生成・ジョブ管理・データガバナンスなどのデータエンジニアリング領域をカバーした、分析基盤構築・運用の支援SaaSです。
あらゆるデータの連携・整備・運用を自動化し、スピーディーにデータ活用環境を整備。インサイトを得やすい状況に導きます。
https://trocco.io/lp

DB やログなどのデータを分析基盤に取り込んだり、分析基盤のデータからデータマートを作成したりを一括管理できます。さらにこれらをワークフローとして定義することで、分析基盤上のデータを最新に保つなどの仕組みも備えています。

TROCCO フリープランアカウント作成

TROCCO では、無料で使えるフリープランがあります。

https://trocco.io/lp/plan.html

フリープランを使用するには、あらかじめ申し込みが必要です。アカウント発行には 1 営業日程度かかるので、前もって申し込んでおきます。

アカウントが作成されたら、ログイン画面からログインします。

https://trocco.io/users/sign_in

TROCCO と BigQuery で構築する分析基盤

今回は、BigQuery を分析基盤として、TROCCO を ETL ツールとして使い、Google スプレッドシートのデータを取り込み、そこからさらにデータマートを作成するまでを行ってみましょう。

まずは、GCP と TROCCO で事前準備の設定を行っていきます。その後で、外部データの取り込みやデータマート作成を行います。

GCP 設定

まずは GCP の設定を行います。GCP でプロジェクトが作成されている前提で進めますのでまだ作成していない場合はプロジェクトを作成してください。

https://cloud.google.com/resource-manager/docs/creating-managing-projects?hl=ja

TROCCO 接続用のサービスアカウント作成

TROCCO から BigQuery に接続できるように、サービスアカウントを作成します。

「IAMと管理」より、「サービスアカウント」を押下し、サービスアカウントを作成します。

サービスアカウント画面にアクセスしたら、「サービスアカウントを作成」を押下します。

「サービス アカウントの詳細」では、サービスアカウントの名前を入力します。入力したら、「作成して続行」ボタンを押下します。

次に、このサービスアカウントに権限を付与していきます。「BigQuery ジョブユーザー」と「BigQuery データオーナー」を付与します。

完了ボタンを押下し、サービスアカウントの作成は完了です。

なお、サービスアカウントへのロール付与について、業務で使用する場合は管理者に相談のもと、適切な権限を付与するようにしてください。
https://cloud.google.com/bigquery/docs/access-control?hl=ja

サービスアカウントが作成されました。続いて、キーを発行します。

「鍵を追加」より、「新しい鍵を作成」を押下します。

キーのタイプは「JSON」を選択し、作成ボタンを押下します。

これでキーが生成されました。生成されキーは JSON ファイルで自動的にダウンロードされます。JSON ファイルを開いてみると以下が収録されています。キーは後工程で使用します。

{
  "type": "xxx",
  "project_id": "xxx",
  "private_key_id": "xxx",
  "private_key": "xxx",
  "client_email": "xxx",
  "client_id": "xxx",
  "auth_uri": "xxx",
  "token_uri": "xxx",
  "auth_provider_x509_cert_url": "xxx",
  "client_x509_cert_url": "xxx",
  "universe_domain": "xxx"
}

Google Sheets API の有効化

今回は、外部データソースとして Google Sheets を使います。TROCCO はスプレッドシートへのアクセスに Sheets API と Drive API を用いますので、これを有効化しておきます。

GCP のコンソール画面から、「APIとサービス」にアクセスし、検索窓に「Google Sheets API」と入力すると出てくる「Google Sheets API」を押下します。

Google Sheets API を有効にするボタン押下します。

これで Google Sheets API の有効化は完了です。

Google Drive API の有効化

同じく、Drive API も有効化します。検索窓に「Google Drive API」と入力すると出てくる「Google Drive API」を押下します。

「有効にする」ボタンを押下します。

TROCCO 設定

続いて、TROCCO 側の設定を行っていきます。

接続情報の登録

TROCCO がスプレッドシートに接続するための接続情報と、BigQuery に接続するための接続情報 2 つを登録します。

左メニュー「接続一覧」を押下し接続情報一覧へアクセスしたら、画面右上にある「新規作成」ボタンを押下します。

Google Spreadsheets 接続情報

「ファイル・ストレージサービス」タブを押下すると、Google Spreadsheets が表示されますので、これを押下します。

「サービスアカウントで作成」ボタンを押下します。

接続情報の作成画面で、必要な情報を入力します。認証方式は JSON を選択し、JSON Key には、GCP で TROCCO 接続用のサービスアカウントを作成した際にダウンロードした JSON ファイルの中身を全てコピーし、ここにペーストします。

保存ボタンを押下し、接続情報を登録します。

BigQuery 接続情報

Google Spreadsheets 接続情報の登録と同じ要領で、BigQuery への接続情報も登録します。

DWH を押下すると、BigQuery が表示されますので、これを押下します。

こちらも先ほど同じく、認証方式は JSON を選択し、JSON Key には、GCP で TROCCO 接続用のサービスアカウントを作成した際にダウンロードした JSON ファイルの中身を全てコピーし、ここにペーストします。

入力できたら、「接続を確認」ボタンを押下してみましょう。接続結果に正しく接続が行えたか表示されます。

接続確認ができたら、保存ボタンを押下し、接続情報を登録します。

以上で接続情報の登録は完了です。

これで一通りの事前準備が完了しました。

サンプルデータ

今回は、外部データソースとして Google Spreadsheets を用いますが、そこに収録するデータとして、以下を利用します。

(これらのデータに関してや、BigQuery に関しては BigQuery やさしいはじめの一歩〜実際に触って理解するデータ操作ワークショップ〜 を参照してください)

自分のアカウント上にスプレッドシートを作成し、上記のデータをコピーしてください。そして、作成したスプレッドシートをサービスアカウントに共有します。

共有先ですが、ダウンロードしたサービスアカウントの JSON ファイルに client_email の記載がありますので、これを指定します。権限は閲覧者で問題ありません。

データ転送設定

それでは実際に分析基盤にデータの取り込みを行ってみましょう。TROCCO の「データ転送」画面から「新規転送設定作成」ボタンを押下します。

転送元は Google Spreadsheets, 転送先は BigQuery を選択して「この内容で作成」ボタンを押下します。

遷移後の転送設定の新規作成画面では、以下を入力していきます。

  1. 概要設定
    • 名前: 転送設定に任意の名前をつけます。ここでは「sample_ice_cream__ice_cream_sales」としています。
  2. 転送元 Google Spreadsheetsの設定
    • Google Spreadsheets接続情報: 作成した接続を指定します。
    • シートのURL: 自分のアカウント上に作成したサンプルデータのスプレッドシート URL を入力します。
    • シート名: 取り込みたいシート名を入力します。
    • カラム設定: 「カラム情報を抽出」ボタンを押下します。スプレッドシートに接続できれば、自動でカラム情報を取得してくれます。

  1. 転送先 Google BigQueryの設定
    • Google BigQuery接続情報: 作成した接続を指定します。
    • データセット: sample_ice_cream と入力します。
    • テーブル: ice_cream_sales と入力します。
    • データセットのロケーション: お住まいの地域に近いロケーションを指定します。東京が最寄りなら asia-northeast-1(東京)を指定します。
    • データセットの自動生成オプション: 「dataset を自動で生成する」を選択します。
    • 転送モード: 全件洗い替え(REPLACE)を選択します。
    • 接続確認: 「接続を確認」ボタンを押下して、接続が正常に行えるか確認しましょう。エラーが出なければ正常に接続できています。

全て入力できたら、画面最下部にある「次の STEP へ」 ボタンを押下します。

次の「データプレビュー・詳細設定」では、実際に BigQuery に転送されるデータのプレビューやカラム定義、フィルターや文字列変換、マスキング設定など細かい設定が行えます。

今回は特に変換処理は設定しませんので、プレビューが正常に表示されたら、「確認画面へ」ボタンを押下し先に進みます。

遷移先の「内容の確認」画面は、これまで設定した情報の確認です。問題なければ「保存して適用」ボタンを押下すると、データ転送設定が登録されます。

データ転送実行

では、登録した転送設定を使用して、スプレッドシートのデータを BigQuery に転送してみましょう。作成したデータ転送画面の右上にある「実行」ボタンを押下します。

「新規転送ジョブの実行」画面に遷移するので、そのまま「実行」ボタンを押下すれば、データ転送が開始されます。

ステータスが SUCCESS になれば、転送は成功です。

実際に BigQuery を確認してみましょう。データセット「sample_ice_cream」に、テーブル「ice_cream_sales」が作成されていることが確認できます。

おつかれさまでした。これで、外部データソースからのデータの取り込みができました。

次に進む前に

同じ要領で、「ポイントメンバー会員」データについても、データ転送設定を作成し、データ取り込みまで行ってください。この後のデータマート作成で、これら 2 つのテーブルを使用します。

  • 転送設定名: sample_ice_cream__members
  • データセット: sample_ice_cream
  • テーブル: members

データマート作成

前章で、外部のデータソースを分析基盤に取り込むことを行っていきました。

ここでは、分析基盤に既にあるテーブルから、さらに新しいテーブルを作成するための設定を行います。

現在、sample_ice_cream データセットには 2 つのテーブル「ice_cream_sales」と「sample_ice_cream」があります。この 2 つのテーブル使用して、「各商品ごとの男女購入比率」を算出した新しいテーブルを作成します。

テーブルのレコードとしては、以下になる想定です。

WITH t1 AS (
  SELECT 
    item_name,
    gender
  FROM `sample_ice_cream.ice_cream_sales` sales
  INNER JOIN `sample_ice_cream.members` members ON sales.member_id=members.id
  WHERE gender in ('', '')
)
, t2 AS (
  SELECT 
    item_name,
    SUM(CASE WHEN gender='' THEN 1 ELSE 0 END) as male,
    SUM(CASE WHEN gender='' THEN 1 ELSE 0 END) as female 
  FROM t1
  GROUP BY item_name
)
SELECT
  item_name,
  male + female as total_sales,
  ROUND(male / (male + female), 2) as male_ratio,
  ROUND(female / (male + female), 2) as female_ratio,
FROM t2
;

データマート定義

それでは TROCCO で設定を行っていきます。

「データマート定義」より、「新規データマート定義作成」ボタンを押下します。

データマート定義の新規作成画面に遷移するので、BigQuery を押下します。

データマート定義の新規作成フォームが表示されたら、以下を入力していきます。

  1. 概要設定
    • データマート定義名: データマート定義に任意の名前をつけます。ここでは「sample_ice_cream__item_gender_sales_ratio」としています。
  2. 基本設定
    • Google BigQuery接続情報: 作成した接続を指定します。
  3. クエリ設定
    • クエリ実行モード:「データ転送モード」を選択します。
    • クエリ: 前項のクエリをペーストします。
      • フォームの右上に「プレビュー実行」ボタンがあるので押下します。クエリが正常に実行できれば、プレビュー結果が表示されます。
    • 出力先データセット: sample_ice_cream と入力します。
    • 出力先テーブル: item_gender_sales_ratio と入力します。
    • 書き込みモード: 「全件洗い替え」を選択します。

入力が完了したら、確認画面へ進み、適用ボタンを押下します。これでデータマート定義が作成できました。

データマート作成実行

では、登録したデータマート定義を使用して、BigQuery にテーブルを作成してみましょう。右上にある「シンク」ボタンを押下します。

「新規シンクジョブの実行」画面に遷移するので、そのまま「シンクジョブを実行」ボタンを押下すれば、データマート作成が開始されます。

ステータスが SUCCESS になれば、作成は成功です。

BigQuery を確認してみましょう。データセット「sample_ice_cream」に、新たなテーブル「item_gender_sales_ratio」が作成されていることが確認できます。

おつかれさまでした。これで、データマートの作成が完了しました。

ワークフロー

ここまで、「外部データソースの取り込み」そして「データマートの作成」を行ってきました。

TROCCO のような ETL ツールの役割は、これらの一元管理はもちろんのこと、定期的にデータの最新化を行うことも重要な役割の一つです。

つまり、外部のデータソースから定期的に新しいデータを取得し、それを使ってデータマートのデータも最新化していきます。

これらを行うために、ワークフローを定義して、一連のデータの処理の流れを自動化します。

具体的には、TROCCO でこれまで作成してきた以下の 3 つのデータ処理を一連のフローとして定義し、定期的にこれらを自動で実行できるようにします。

  1. スプレッドシートを読み込み BigQuery に ice_cream_sales テーブルを作成する
  2. スプレッドシートを読み込み BigQuery に members テーブルを作成する
  3. BigQuery の ice_cream_sales テーブル、members テーブルから、item_gender_sales_ratio テーブルを作成する

ワークフロー定義

ワークフロー定義 より、画面右上にある「新規ワークフロー作成」ボタンを押下します。

ワークフロー定義の新規作成画面に遷移したら、以下を入力していきます。

  • 概要設定
    • ワークフロー名: ワークフローに任意の名前をつけます。ここでは「ice_cream_sales_workflow」としています。
  • ジョブ実行設定
    • タスク同時実行上限数: 2
    • タイムアウト設定: 有効, 3 分
    • リトライ回数: 0
    • ジョブの重複実行: 許可する

入力したら「保存」ボタンを押下します。

フロー編集

フロー編集画面に遷移したら、「TROCCO転送ジョブ」を押下します。

モーダルが表示されるので、「sample_ice_creamice_cream_sales」「sample_ice_creammembers」両方にチェックを入れ、「追加」ボタンを押下します。

「sample_ice_creamice_cream_sales」と「sample_ice_creammembers」を、それぞれ「START」から矢印をドラッグして並列につなぎます。それぞれのオブジェクトに点(ポイント)があるので、そこからドラッグすると以下のように START と転送設定を矢印で繋げられます。

次に、「TROCCOデータマートシンク」を押下します。

モーダルが表示されるので、「sample_ice_cream__item_gender_sales_ratio」にチェックを入れ、「追加」ボタンを押下します。

新しく追加された「sample_ice_creamitem_gender_sales_ratio」のオブジェクトを、それぞれ「sample_ice_creamice_cream_sales」「sample_ice_cream__members」から矢印でつなぎます。

こうすることで、データ転送の 2 つが完了したら、データマート作成を行う。という依存関係も定義できます。元のデータが両方最新化されていなければ、データマートを作成してもデータが不十分ですから、こうして依存関係を定義することで、データの不整合を防ぐことができます。

ここまで設定できたら、画面右上の「保存」ボタンを押下してワークフロー定義を保存します。

ワークフロー実行

ワークフローを実行してみましょう。画面右上の「実行」ボタンからワークフローを実行できます。

ステータスが SUCCESS になれば、ワークフローの実行は成功です。

ワークフローの実行によって、外部データソースからのデータ取り込み、そして既存テーブルを利用した新たなデータマートの作成までが一気通貫で行えるようになりました。

ワークフローのスケジュール設定

ワークフローを定期的に実行することで、外部データソースのデータが更新されても定期的に取り込めるようになり、分析基盤を最新の状態に保つことができます。

ワークフローのスケジュール設定は、概要設定から行えますので、興味があれば設定してみてください。

まとめ

本記事では、TROCCO と BigQuery を使って、外部データソースからのデータ取り込みから、データマートの作成、そしてワークフローによる一連の処理の自動化までを体験してきました。

ETL ツールを使うことで、データエンジニアリングの作業を効率化し、データ分析基盤を強化することができます。TROCCO は、直感的な操作性とワークフロー機能により、データの取り込みからデータマート作成までを一元管理できる便利なツールです。

今回は、サンプルデータを使った簡単な例でしたが、実際のビジネスにおいては、様々なデータソースから大量のデータを取り込み、複雑な変換処理を行うことが求められます。そのような場合でも、TROCCO を活用することで、データエンジニアリングのタスクを効率的に進められるでしょう。

データ活用が進むにつれ、データエンジニアリングの重要性はますます高まっています。TROCCO のような ETL ツールを上手に活用し、データ分析基盤を強化していくことが、ビジネスの意思決定の質を高め、競争力を向上させる鍵となるでしょう。

ぜひ、TROCCO を使ってみて、データエンジニアリングの世界に触れてみてください。データの力を活用し、ビジネスの可能性を広げていきましょう。

SQL クエリを効率よく書こう: CTE, Window 関数編

この記事は個人ブログと同じ内容です

www.ritolab.com


データベースを操作する SQL

SQL(Structured Query Language)は、関係データベース管理システムRDBMS)でデータを管理・操作するための標準化された言語です。データベースに格納された情報を効果的に取得、挿入、更新、削除するために使用されます。

SQLは、テーブルと呼ばれるデータの形式で情報を格納し、クエリを使用してデータベースとの対話を行います。初心者にとっても覚えやすく、構文も直感的であるため、多くのデータベース管理システムで広く利用されています。

SQL の歴史は古く、1970 年代初頭に IBM 社において、Edgar F. Codd(エドガー・F・コッド)博士によって革新的な関係モデルが提唱されたことから始まりました。

現代においては、MySQL, PostgreSQL, BigQuery などではこの SQL をベースとしたクエリを組み立て、発行(実行)していくものになっており、データベース製品が異なっても、ベースの文法は同じものです。

クエリを効率良く書こう

データを取得するクエリを組み立てる際のクエリの書き方は必ず 1 つというわけではありません。文法など、クエリとして成立していれば異なった書き方でも同じ結果を取得することができます。

例えば、本の売上を収録しているテーブルがあったとします。

> SELECT * FROM book_salses GROUP BY sale_date;

  |---------|-----------|
  | book_id | sale_date |
  |---------|-----------|
  | 1       | 2024-01-01|
  | 1       | 2024-01-01|
  | 1       | 2024-01-01|
  | 1       | 2024-01-01|
  .
  .
  .
  | 1       | 2024-01-05|
  |---------|-----------|

このテーブルを操作して、本の日次販売数と、累計販売数を出力します。

このとき、以下 2 つの SQL クエリは同じ結果を出力します。

-- クエリ 1
SELECT
    daily_salses.sale_date,
    daily_salses.book_id,
    daily_salses.sales_count,
    SUM(t1.sales_count) as cumulative_sales_volume
FROM (
     SELECT
         sale_date,
         book_id,
         COUNT(book_id) as sales_count,
     FROM book_sales
     GROUP BY sale_date, book_id
 ) as daily_salses
INNER JOIN (
    SELECT
        sale_date,
        book_id,
        COUNT(book_id) as sales_count,
    FROM book_sales
    GROUP BY sale_date, book_id
) t1 ON daily_salses.sale_date >= t1.sale_date
GROUP BY daily_salses.sale_date, daily_salses.book_id, daily_salses.sales_count
;

-- クエリ 2
SELECT
    sale_date,
    book_id,
    COUNT(book_id) as sales_count,
    SUM(COUNT(book_id)) OVER (PARTITION BY book_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_sales_volume
FROM book_sales
GROUP BY sale_date, book_id
;

出力される結果は 2 つのクエリとも同じ以下が出力されます。

sale_date book_id sales_count cumulative_sales_volume
2024-01-01 1 12 12
2024-01-02 1 12 24
2024-01-03 1 12 36
2024-01-04 1 12 48
2024-01-05 1 12 60

2 つのクエリを見比べてみてどう感じるでしょう。少なくとも、ぱっと見て記述量が明らかに違うことはわかると思います。記述量が多いということは、それだけクエリを組み立てるのにも時間がかかりそうですね。

「メラ!」で唱えられる魔法と、「汝の身は我が下に、我が命運は汝の剣に ブツブツブツブツ......」みたいな詠唱して唱える魔法では魔法を発動するまでにかかる時間が違うのと似たようなものです。(仕事は早く済ませたい)

このように、どんなクエリだとしても欲しい結果が得られば何でも良いのかというと必ずしもそうとは言えません。クエリによってパフォーマンスに違いがあったり、可読性にも違いがあります。

今回は、最近では当たり前になりつつある記法を使って、クエリを効率よく組み立てていく方法を見ていきましょう。

CTE

CTE(Common Table Expressions)は、SQLで一時的な結果セットを定義するための構文です。主に複雑なクエリをより読みやすく、管理しやすくするために使用されます。CTEを使用すると、サブクエリやビューと同様に、クエリ内で一時的なテーブルを作成し、それを後続の処理で参照することができます。特に再帰的なクエリや複数の階層的なクエリを処理する際に、CTEは非常に有用です。

最初に見た、1 つめのクエリを見てみましょう。

-- クエリ 1
SELECT
    daily_salses.sale_date,
    daily_salses.book_id,
    daily_salses.sales_count,
    SUM(t1.sales_count) as cumulative_sales_volume
FROM (
     SELECT
         sale_date,
         book_id,
         COUNT(book_id) as sales_count,
     FROM book_sales
     GROUP BY sale_date, book_id
 ) as daily_salses
INNER JOIN (
    SELECT
        sale_date,
        book_id,
        COUNT(book_id) as sales_count,
    FROM book_sales
    GROUP BY sale_date, book_id
) t1 ON daily_salses.sale_date >= t1.sale_date
GROUP BY daily_salses.sale_date, daily_salses.book_id, daily_salses.sales_count
;

このクエリでは、全く同じクエリを記述している箇所があります。FROM 句の中と INNER JOIN 句の中で行っている SELECT 文です。

こういった同じクエリを記述している箇所を、CTE を使えば共通化することができます。CTE は WITH 句を使って定義します。

WITH <cte_name> AS (<query>)

WITH daily_salses AS (
    SELECT
        sale_date,
        book_id,
        COUNT(book_id) as sales_count,
    FROM book_sales
    GROUP BY sale_date, book_id
)
SELECT
    daily_salses.sale_date,
    daily_salses.book_id,
    daily_salses.sales_count,
    SUM(t1.sales_count) as cumulative_sales_volume
FROM daily_salses
INNER JOIN daily_salses t1 ON daily_salses.sale_date >= t1.sale_date
GROUP BY daily_salses.sale_date, daily_salses.book_id, daily_salses.sales_count
;

通化したいクエリを CTE として定義し、実際の SELECT 文で利用することによって、クエリ全体をすっきりさせることができます。

CTE はサブクエリに似ていますが、再利用可能で名前を付けられるので、長いクエリをモジュール化して見通しを良くできるというメリットがあります。特に複数の派生テーブルを作成する必要がある場合に、CTE は非常に便利です。

Window 関数

Window 関数は、SQLでデータセット内の特定の行に対して計算を行うための機能です。通常の集約関数(例:SUM、AVG、COUNT)とは異なり、Window 関数は各行に対して個別に計算を実行し、その結果を結果セットの各行に返します。

前項で CTE に触れたので、それを適用させた先ほどのクエリを見てみましょう。

WITH daily_salses AS (
    SELECT
        sale_date,
        book_id,
        COUNT(book_id) as sales_count,
    FROM book_sales
    GROUP BY sale_date, book_id
)
SELECT
    daily_salses.sale_date,
    daily_salses.book_id,
    daily_salses.sales_count,
    SUM(t1.sales_count) as cumulative_sales_volume
FROM daily_salses
INNER JOIN daily_salses t1 ON daily_salses.sale_date >= t1.sale_date
GROUP BY daily_salses.sale_date, daily_salses.book_id, daily_salses.sales_count
;

累計を出すために同じ daily_salses を join させ、再度 GROUP BY を実行し累計を SUM() 関数で集計しています。少し冗長な気もしますね。でも累計を出したいのだから仕方ない。

ところが、Window 関数を使えば、上記のような記述をしなくても累計を算出できます。以下は、Window 関数として SUM() 関数を用いた方法です。

WITH daily_salses AS (
    SELECT
        sale_date,
        book_id,
        COUNT(book_id) as sales_count,
    FROM book_sales
    GROUP BY sale_date, book_id
)
SELECT
    sale_date,
    book_id,
    sales_count,
    SUM(sales_count) OVER (PARTITION BY book_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_sales_volume
FROM daily_salses
;

SELECT 句の最後で Window 関数として SUM() 関数を使用しています。再度の JOIN も GROUP BY も不要になりました。

これらはもちろん、同じ結果を出力します。

sale_date book_id sales_count cumulative_sales_volume
2024-01-01 1 12 12
2024-01-02 1 12 24
2024-01-03 1 12 36
2024-01-04 1 12 48
2024-01-05 1 12 60

Window 関数の基本書式

Window 関数の基本的な構文は以下のようになります。

<ウィンドウ関数>([引数]) OVER (
  [PARTITION BY1, 列2, ...]
  [ORDER BY1 [ASC|DESC], 列2 [ASC|DESC], ...]
  [ROWS | RANGE 制約]  
)
  • <ウィンドウ関数> - 使用する Window 関数 (SUM、AVG、COUNT、RANK、DENSE_RANK、ROW_NUMBER など)
  • [引数] - Window 関数に渡す引数 (カラム名など)
  • PARTITION BY - データをグループ化する列を指定 (オプション)
  • ORDER BY - Window 内の並び順を指定 (一部の関数で必須)
  • ROWS | RANGE - Window のレンジを行ベースまたは値ベースで指定 (オプション)

Window 関数は OVER 句の中で設定されます。PARTITION BY で指定した列でデータを分割し、ORDER BY で指定した順序でウィンドウが作成されます。さらに ROWS または RANGE を使ってウィンドウのレンジを細かく制御できます。

今回の例に置き換えると、以下のようなことになります。

SUM(sales_count) OVER (PARTITION BY book_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as cumulative_sales_volume

「book_id ごとに計算します。それぞれは sale_date 昇順で並べます。計算するウィンドウフレームの開始位置は最初の行から、現在の行までの全ての行です。」

SUM(sales_count) -- 加法集計します
    OVER (
        -- book_id ごとに集計します
        PARTITION BY book_id
        -- sale_date を昇順に並び替えた上で集計します
        ORDER BY sale_date
        /*
         * Window の範囲指定は、
         * ROWS BETWEEN
         *      UNBOUNDED PRECEDING(最初の行)
         * AND(から)
         *      CURRENT ROW(現在行)
         * までです
         */
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) as cumulative_sales_volume

こんな感じで、Window 関数は、加法集計以外にも、データの順序付け、ランキングなどのさまざまな用途に使用できます。例えば、各顧客の売上を日付ごとにランク付けする、直近の日付ごとに合計売上を計算する、あるいは移動平均などの時系列解析を行う際に、Window関数が役立ちます。

どんな関数があるのかについては、以下を参照してください。

WINDOW 関数を一通り試してみよう

パフォーマンスアップにも寄与

Window 関数を使えば無駄な JOIN や GROUP BY を行わなくて済む例を見ましたが、これによって、内部的な処理量も減り、パフォーマンスにも寄与します。

例えば以下は、BigQuery で 2 つのクエリを実行した際のパフォーマンスです。

クエリ 1

クエリ 2

Window 関数を用いたクエリ 2 の方が、JOIN が無くなった分処理量も減っており、パフォーマンスが良くなっています。

BigQuery に限らず、読み込むデータの量や結合数がもっと大きな分析を行う際は、こうしたところで SQL クエリのパフォーマンスが大きく変わってきますので、地味に大切なポイントです。

まとめ

SQL クエリを効率よく書くために、CTE と Window 関数を見ていきました。

CTE で共通化すれば、再利用できて便利ですし、それによって冗長な記述を排除すれば、可読性も上がります。自分ひとりでクエリを組み立て使用している分には良いですが、チームでクエリを管理したり、レビューしたりする時には、クエリの可読性はアプリケーションのプログラミングと同じくらい重要です。

Window 関数もまた、記述量を減らせる上、パフォーマンスもアップさせることができます。分析時には多くのクエリを定義することも多いため、一つ一つのクエリにおいて最大のパフォーマンスで実行できるように定義していけると、最終的なパフォーマンスにも無駄なく実行できます。

まさにクエリのパフォーマンスは「塵も積もれば山となる」です。

CTE も Window 関数も、SQL が 1970 年に生まれたことを考えれば比較的新しい機能ですが、現代においてはそこそこお馴染みになっているものです。

代表的なデータベース製品である MySQL は 8.0 から、PostgreSQL は 8.4 から Window 関数が利用できます。

資料やサンプルも沢山あると思いますので、ぜひ活用してみてください。


現在 back check 開発チームでは一緒に働く仲間を募集中です。 herp.careers

外部ライブラリのfinalクラスをモックする

この記事は個人ブログと同じ内容です


はじめに

外部のライブラリを使用して実装し、テストを書いていたらfinalクラスがモックできずに詰まったので、その解決方法を解説していきます。 また、解決にあたりサービスコンテナ周りで得た知見の共有をしていきます。

問題

以下のようにLaravelのAuth0 SDKを使用して実装し、テストを書いていたら、 Auth0 SDKで実装されているクラスがfinalなため、直でモック化することができませんでした...

https://github.com/auth0/laravel-auth0

//Auth0 SDKの初期化
$auth0 = new \Auth0\SDK\Auth0([
    'domain' => $_ENV['AUTH0_DOMAIN'],
    'clientId' => $_ENV['AUTH0_CLIENT_ID'],
    'clientSecret' => $_ENV['AUTH0_CLIENT_SECRET'],
    'cookieSecret' => $_ENV['AUTH0_COOKIE_SECRET']
]);

$auth0->getIdToken()
//Auth0のテスト
app()->bind(Auth0::class, function () {
    $mockedAuth0 = Mockery::mock(Auth0::class);
    $mockedAuth0->shouldReceive('getIdToken')->once()
                ->andReturn('test');
    return $mockedAuth0;
});

エラー文

The class Auth0\SDK\Auth0 is marked final and its methods cannot be replaced. Classes marked final can be passed in to \Mockery::mock() as instantiated objects to create a partial mock, but only if the mock is not subject to type hinting checks.

解決方法

Auth0 SDKは外部のライブラリなので、finalを消すことができないです... ですが、以下のようにコードを見てみると Auth0クラスはAuth0Interfaceクラスをimplementsしているので、 Auth0Interfaceクラスを使用して実装してあげればできそう!

// SDKのコード
final class Auth0 implements Auth0Interface
{
        ・
        ・
}

サービスプロバイダーを使用する

サービスプロバイダーを作成し、Auth0Interface::classにAuth0::classを注入します。 そうすると、app()->make(Auth0Interface::class)で作成したインスタンスで、 Auth0Interface::classで実装しているAuth0::classのすべてのメソッドが使用できるようになります。

class Auth0ServiceProvider extends ServiceProvider
{
    public array $bindings = [
        // ここでもいい
        Auth0Interface::class => Auth0::class,
    ];
    public function register()
    {
        // こっちでもいい
        app()->bind(Auth0Interface::class, Auth0::class);
    }
}

サービスプロバイダについてはこの記事がわかりやすかったです https://note.com/sasshiii/n/n554298c93077

サービスプロバイダー使用後の実装側のコード

app()->make()を使用し、Auth0Interfaceクラスを呼び出すこと、 サービスプロバイダーに登録した、Auth0クラスが注入されたAuth0Interfaceクラスが作成されます。 なので、Auth0Interfaceクラスのメソッドが呼び出されると、Auth0で実装されたメソッドが実行されるので、 Auth0クラスのメソッドを使用した時と変わらないです。

// サービスプロバイダに登録したので、Auth0Interfaceを呼ぶと、Auth0が作られる
$auth0 = app()->make(Auth0Interface::class,[
            'domain' => $_ENV['AUTH0_DOMAIN'],
            'clientId' => $_ENV['AUTH0_CLIENT_ID'],
            'clientSecret' => $_ENV['AUTH0_CLIENT_SECRET'],
            'cookieSecret' => $_ENV['AUTH0_COOKIE_SECRET']
]);
// Auth0::classのgetIdToken()が呼ばれる
$auth0->getIdToken()

サービスプロバイダー使用後のテストのコード

先ほどはAuth0クラスをモックしていましたが、実装側ではAuth0Interfaceクラスが使用されているので、 Auth0Interfaceクラスをモックします。

//Auth0のテスト
app()->bind(Auth0Interface::class, function () {
    // 実装したコードで呼ばれるのはAuth0Interfaceなので、そっちをモックする
    $mockedAuth0 = Mockery::mock(Auth0Interface::class);
    $mockedAuth0->shouldReceive('getIdToken')->once()
                ->andReturn('test');
    return $mockedAuth0;
});

番外編

app()->instance()を使用するケース

既存のインスタンスを注入したい場合に使用します。

例えば、以下のコードのように$hogeService->test()が外部APIを叩くメソッドで、APIを叩くたびに課金が発生するならば、本番では呼ばれていいが、テストでは呼ばれるべきではないと思います。 SampleServiceのhello()のテストを書くときに、$hogeService->test()が実際に呼ばれないようにする時に使用します。

実装側のコード

class SampleService
{
    public function __construct(
        private readonly HogeService $hogeService
    ) {}

    public function hello()
    {
        $hogeService->test();
                ・
                ・
    }
}

テストコード

テストで、app()->instance()を使用し、$hogeServiceをMockに置き換え、実際に呼ばれないようにします。 今回は例が単調すぎますが、実際にはLaravelのコントローラーのテストでよく使います。

public function testHello(): void
{
    // HogeServiceのモックを作成
    $mockedHogeService = Mockery::mock(HogeService::class);
    // HogeServiceのtest()が1回呼ばれ、helloを返すように設定
    $mockedHogeService->shouldReceive('test')->once()
                     ->andReturn('hello')
    // HogeServiceが呼ばれるときは、mockedHogeServiceが呼ばれる
    app()->instance(HogeService::class, $mockedHogeService);

    // こっちでもいい
    $sampleService = new SampleService(mockedHogeService);
}

bindとsingletonの違い

singletonは呼び出された時の値が変わらないです。 例: ランダムな値を返す関数を持つクラスを注入したら、 bindは呼び出されるごとに値が変わるが、singletonは変わらないです。

最後に

今までサービスコンテナについてはなんとなくしか理解していなかったのですが、この問題を通じてかなり理解を深めることができました。 サービスコンテナとかサービスプロバイダーは、あるクラスを別のクラスなどに置き換えて、クラスが登録されている箱の中から、呼び出すだけなのに、 インスタンスやらコンテナなど横文字を使用し、Dockerと似た表現をするから頭がゴチャゴチャしてわかりにくかったんだなと思いました。(かと言って他のいい表現は思いつかない、、、)

他にもっと簡単な方法などありましたら気軽にコメントいただけると幸いです。

参考

https://note.com/sasshiii/n/n554298c93077


現在 back check 開発チームでは一緒に働く仲間を募集中です。 herp.careers

はじめまして。AWS Step Functions のフローを理解する

この記事は個人ブログと同じ内容です

www.ritolab.com


AWS Step Functions の「フロー」を触って挙動を確認します。

AWS Step Functions

様々なアプリケーションのワークフローを一連の処理として定義し実行できるサービス。Lambda invokeSNS Publish, ECS Run task など、AWS 上に構築した処理を一つのシナリオにまとめて、順番に実行したり、条件によってそれらの実行を決定したり、並列実行や反復実行なども行える。

フロー

Step Functions における「フロー」は、ステートマシンを定義する上で、処理実行の流れや有無、どう実行するかを決める根幹の部分。

大きく以下 3 つフローを用いて処理の流れを定義する。

  • Choice
  • Parallel
  • Map

最もシンプルなフロー

最もシンプルなフローは、上から順番に実行していくフロー。パラータや状態の評価による処理の分岐がない場合は、シンプルに実行順に各処理を並べていけばよく、上記の Choice, Parallel, Map も使用しない。

Choice

Choice は、パラメータや状態によって処理を分岐できるフロー。if 文のような挙動をする。

// Choice のイメージ

if (...) {

} elseif (...) {

} elseif (...) {

} else {

}

Choice には複数の Rule が指定できるが、上から定義した Rule が評価され、合致した時点でその処理に遷移し Choice フローは終了する。つまり、必ずどれか 1 つだけの処理に移行する。(条件に合う全てのフローが実行されるような動作はしない)

ASL (Amazon States Language)

{
  "Comment": "A Hello World example demonstrating various state types of the Amazon States Language. It is composed of flow control states only, so it does not need resources to run.",
  "StartAt": "Pass",
  "States": {
    "Pass": {
      "Comment": "A Pass state passes its input to its output, without performing work. They can also generate static JSON output, or transform JSON input using filters and pass the transformed data to the next state. Pass states are useful when constructing and debugging state machines.",
      "Type": "Pass",
      "Result": {
        "selectA": true,
        "selectB": true,
        "IsHelloWorldExample": true
      },
      "Next": "Hello World example?"
    },
    "Hello World example?": {
      "Comment": "A Choice state adds branching logic to a state machine. Choice rules can implement many different comparison operators, and rules can be combined using And, Or, and Not",
      "Type": "Choice",
      "Choices": [
        {
          "Variable": "$.IsHelloWorldExample",
          "BooleanEquals": true,
          "Next": "Selected A"
        },
        {
          "Variable": "$.IsHelloWorldExample",
          "BooleanEquals": false,
          "Next": "No"
        },
        {
          "Variable": "$.IsHelloWorldExample",
          "BooleanEquals": true,
          "Next": "Wait"
        }
      ],
      "Default": "Selected A"
    },
    "Wait": {
      "Type": "Wait",
      "Seconds": 5,
      "Next": "Yes"
    },
    "Yes": {
      "Type": "Pass",
      "Next": "Success (1)"
    },
    "Success (1)": {
      "Type": "Succeed"
    },
    "No": {
      "Type": "Fail",
      "Cause": "Not Hello World"
    },
    "Wait 3 sec": {
      "Comment": "A Wait state delays the state machine from continuing for a specified time.",
      "Type": "Wait",
      "Seconds": 3,
      "Next": "Parallel State"
    },
    "Parallel State": {
      "Comment": "A Parallel state can be used to create parallel branches of execution in your state machine.",
      "Type": "Parallel",
      "Next": "Hello World",
      "Branches": [
        {
          "StartAt": "Hello",
          "States": {
            "Hello": {
              "Type": "Pass",
              "End": true
            }
          }
        },
        {
          "StartAt": "World",
          "States": {
            "World": {
              "Type": "Pass",
              "End": true
            }
          }
        }
      ]
    },
    "Hello World": {
      "Type": "Pass",
      "Next": "Success"
    },
    "Success": {
      "Type": "Succeed"
    },
    "Selected A": {
      "Type": "Pass",
      "Next": "Wait 3 sec"
    }
  }
}

実際の実行結果

Parallel

並行処理を実施するフロー。複数の処理を並行で実施できる。

ASL (Amazon States Language)

{
  "Comment": "A description of my state machine",
  "StartAt": "Pass",
  "States": {
    "Pass": {
      "Type": "Pass",
      "Next": "Parallel"
    },
    "Parallel": {
      "Type": "Parallel",
      "End": true,
      "Branches": [
        {
          "StartAt": "Choice run A",
          "States": {
            "Choice run A": {
              "Type": "Choice",
              "Choices": [
                {
                  "Variable": "$.runA",
                  "BooleanEquals": true,
                  "Next": "run A"
                }
              ],
              "Default": "not run A"
            },
            "run A": {
              "Type": "Pass",
              "Next": "Success run A"
            },
            "Success run A": {
              "Type": "Succeed"
            },
            "not run A": {
              "Type": "Pass",
              "End": true
            }
          }
        },
        {
          "StartAt": "Choice run B",
          "States": {
            "Choice run B": {
              "Type": "Choice",
              "Choices": [
                {
                  "Variable": "$.runB",
                  "BooleanEquals": true,
                  "Next": "run B"
                }
              ],
              "Default": "not run B"
            },
            "run B": {
              "Type": "Pass",
              "Next": "Success run B"
            },
            "Success run B": {
              "Type": "Succeed"
            },
            "not run B": {
              "Type": "Pass",
              "End": true
            }
          }
        }
      ]
    }
  }
}

実際の実行結果

Map

反復処理を実施するフロー。配列で受け取ったパラメータに対して、item 文、処理をループ実行できる。

ASL (Amazon States Language)

{
  "Comment": "A description of my state machine",
  "StartAt": "Pass",
  "States": {
    "Pass": {
      "Type": "Pass",
      "Next": "Map",
      "Result": {
        "foo": "bar",
        "colors": [
          "red",
          "green",
          "blue",
          "yellow",
          "white"
        ]
      }
    },
    "Map": {
      "Type": "Map",
      "ItemProcessor": {
        "ProcessorConfig": {
          "Mode": "INLINE"
        },
        "StartAt": "exec 01",
        "States": {
          "exec 01": {
            "Type": "Pass",
            "Next": "exec 02"
          },
          "exec 02": {
            "Type": "Pass",
            "End": true
          }
        }
      },
      "End": true,
      "ItemsPath": "$.colors",
      "ItemSelector": {
        "uuid.$": "States.UUID()"
      },
      "ResultPath": "$.uuid"
    }
  }
}

実際の実行結果

{
  "output": {
    "foo": "bar",
    "colors": [
      "red",
      "green",
      "blue",
      "yellow",
      "white"
    ],
    "uuid": [
      {
        "uuid": "8272efe6-80e5-4530-aa7d-fad0fb8d9eb8"
      },
      {
        "uuid": "067ccf0c-85aa-4086-9dc4-7537f6729c7c"
      },
      {
        "uuid": "51e0f5d2-25a2-4f39-a3c7-4bc101e353ef"
      },
      {
        "uuid": "18c85a40-15da-405d-ae02-8c7c3bc5c8e2"
      },
      {
        "uuid": "bd902850-51f1-4857-9232-bc0348c29a22"
      }
    ]
  },
  "outputDetails": {
    "truncated": false
  }
}

使ってみればシンプルで強力なサービス

Choice, Parallel, Map と、名前から挙動を想像しやすく分かりやすかったですが、Choice の挙動が択一であることは留意しておきたい点。

パラメータ $isRunA = true, $isRunB = true で Choice の Rule から A と B の実行判断は行えないため、こういった判断は Parallel → Choice で実現する必要がある。

基本のフローが理解できたので今回はここまで。


現在 back check 開発チームでは一緒に働く仲間を募集中です。 herp.careers

【AWS】SAA [SAA-C03] 合格体験記 2023/01

この記事は個人ブログと同じ内容です


はじめに

2024年1月30日にAWS実務未経験で3ヶ月でAWS SAAに合格することができましたので、 その合格体験記を書きたいと思います。

私の背景

私のエンジニア歴は3年程度で、普段はWeb開発のフロントエンドとバックエンドをやっています。 ですので、AWSの実務経験はありません。 AWSは昔に、チュートリアルなどをみながらEC2やECSを使用してサーバーを建てた経験があるくらいです。

試験の傾向

オンプレからAWS移行するサービスの組み合わせや、コストを最適化するための問題が多かった印象です。 各サービスやタイプの特徴を理解して、使い分けできるような理解が必要と思います。

例えば、ストレージの種類(EBS,EFS,S3)、、Storage Gatewayのタイプ、EC2のインスタンスの種類などなど

勉強方法

1ヶ月目

Kindleで以下の 「AWS認定資格 ソリューションアーキテクトアソシエイトの教科書: 合格へ導く虎の巻」 を読んでインプットの学習をしました。こちらはなんと無料で読むことが可能です。

アウトプット中心の学習をした方がいいので、あまり時間をかけ過ぎない方がいいです。 さらに、情報が古いので重要なところだけをなんとなく理解する程度でいいと思います。

https://www.amazon.co.jp/AWS%E8%AA%8D%E5%AE%9A%E8%B3%87%E6%A0%BC-%E3%82%BD%E3%83%AA%E3%83%A5%E3%83%BC%E3%82%B7%E3%83%A7%E3%83%B3%E3%82%A2%E3%83%BC%E3%82%AD%E3%83%86%E3%82%AF%E3%83%88%E3%82%A2%E3%82%BD%E3%82%B7%E3%82%A8%E3%82%A4%E3%83%88%E3%81%AE%E6%95%99%E7%A7%91%E6%9B%B8-%E5%90%88%E6%A0%BC%E3%81%B8%E5%B0%8E%E3%81%8F%E8%99%8E%E3%81%AE%E5%B7%BB-CloudTech%E6%9B%B8%E7%B1%8D%E4%BD%9C%E6%88%90%E5%A7%94%E5%93%A1%E4%BC%9A-ebook/dp/B0BCPNZ9GJ

2ヶ月目

Ping-tという、無料のSAAの問題集を使用し、基礎的な問題を解いていきました。 Ping-tは、問題が充実しており、かつ復習などがやりやすいサービスになっているのでとても使用しやすかったです。

私自身は全ての問題を解くとこまでは終えることができたのですが、復習は時間がなくてできませんでした。 Ping-tも同様に情報が古いので、Ping-tでのサービス名と最新のAWSのサービス名が違うところが一部ありました。 ですので、最新の情報が載っているUdemyと併用して学習するのをお勧めします。

https://mondai.ping-t.com/g

3ヶ月目

Udemyの 「【SAA-C03版】AWS 認定ソリューションアーキテクト アソシエイト模擬試験問題集(6回分390問)」 を使用し学習しました。 本番と同様程度のレベルで、よく試験に出る問題を実際のテストと同じように受けられる教材になります。

私は時間がなく6回分あるうちの、4回分しかできませんでした。 どのテストも1回目は5割程度しか正解していませんでしたが、復習して2回目解く時には、4回分全てが720点を超えていました。 ほぼこのUdemyしか頭に入っていない状態で本番のテストに向かいました。

通常で買うと7600円とかなり高いので、セールで2000円程度で買うことをお勧めします。

https://www.udemy.com/course/aws-knan/

試験当日

試験自体は、リモート受験をしました。 30分前に入室したのですが、MACの操作権限やらのトラブルがあり、机のチェックなどがやり直しになり、試験時間が少し過ぎてからのスタートになってしまいました。 私が受けたのが平日だったので、試験監督の待ち時間がほぼありませんでしたが、土日にリモートで受験する方は注意してください。 また、試験監督はフィリピン人なので日本語が通じない時があります。 トラブルはあったが問題なく試験は終えられました。

結果

735点でギリギリの合格でした。 試験時間が足りず、見直しがほぼできておらず、自信はなくて確実に試験に落ちたと思っていたので、とても嬉しかったです。

反省・やっておくべきだったこと

復習に力を入れる

AWS SAAの学習は基本アウトプットを中心の学習にした方がいいのですが、やって終わりだと身につかないので、復讐をしましょう。 私は、Ping-tは問題を全て解きましたが復習はできなかったので、Udemyを解いた時に知識が身についていないと感じました。 試験本番はUdemyで解いたような問題も出ましたが、Ping-tで解いたような問題も出たのでPing-tはやっておいて損がないです。

ダラダラやらない

1ヶ月目と2ヶ月目は、ほぼ毎日学習はしていたものの、ダラけていて進捗がかなり遅かったので、目標を立ててしっかり逆算して計画を立てた方がいいです。 私は、試験の前の週だけ異常にに頑張ったので、皆さんは焦らないように常に一定の高い生産性を出せるようにした方がいいです。

本番と同じ環境でテストする

試験時間を測って本番と同じ環境でテストをした方がいいです。 試験本番の時に、時間をあまり気にせずに解いていたら、見直す時間がほぼなくて全くできませんでした。 Udemyのテストの時はいつもかなり時間が余っていたのですが、本番だと問題が違い、普段より時間がかかるので気をつけた方がいいです。

感想

何はともあれ1月中にAWS SAAに合格することができてとても嬉しかった。 このブログ記事が、AWS SAAを目指す方々の参考になれば幸いです。


現在 back check 開発チームでは一緒に働く仲間を募集中です。 herp.careers

瞬時に、柔軟に、そして簡単に。Amazon Athena で行うログ分析

この記事は個人ブログと同じ内容です

www.ritolab.com


ログの分析をどのような手段で実施するか

ログの多くはテキストファイルであることが多く、DB に格納されていないことがほとんどです。

しかも、ログファイルは大抵、複数の環境から吐き出されたり、量や時間で分割して出力されたりするため、複数のファイルにまたがっていることがほとんど。

そんな環境下でも、ログ分析の環境を簡単に構築し、柔軟に解析していけるのが、Amazon Athena です。

AWS Athena

AWS Athena は、Amazon S3 に保存されたデータを標準的な SQL を使って直接分析できるインタラクティブなクエリサービスです。サーバーの管理や設定は不要で、数秒でクエリを実行し、結果を取得できます。

aws.amazon.com

使いやすい上に瞬時にログ分析の環境を用意できる。

最初に結論からお伝えしておくと、ログ分析に Athena を用いるのはとてもオススメです。

特に調査や障害など、急にスポットでログ分析が発生した時などは瞬時に分析環境を構築できるため、初動が非常に早くなります。

前提とデータの用意

今回は、ログの分析を行ってみます。

以下のフォーマットを持つ、nginx ライクなログを 1000 行、擬似的に作成しました。APIアクセスログを模しています。

192.168.0.247 - - [2024-02-18 14:56:55] "POST /api/v1/tasks/24361 HTTP/1.1" 201 7101 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.9876.54321 Safari/537.36"
192.168.0.142 - - [2024-02-18 14:56:56] "GET /api/v1/messages/20050 HTTP/1.1" 200 2942 "-" "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/97.0.4692.99 Safari/537.36"
.
.
.

この access.log を、 S3 のバケットに配置してあります。

データベース作成

まずは、Athena でデータベースを作成します。初期は default になっています。データベースを作成せずに default のまま使うこともできます。

エディターにデータベース作成のクエリを入力し、実行ボタンを押下します。

CREATE DATABASE log_analysis;

実行が成功すると、左メニューのデータベースのセレクトボックスから、log_analysis を選択できるようになります。

テーブル作成

作成したデータベース log_analysis にテーブルを作成していきます。

作成テーブルは、最初に用意して S3 に配置したログのテーブルです。

左メニュー「テーブルとビュー」に「作成」というセレクトボックスがあります。そこから、「S3バケットデータ」を選択します。

「S3 バケットデータからテーブルを作成」という画面に遷移します。ここで必要な入力を行い、テーブルを作成します。

テーブルの詳細セクションでは、テーブル名とその説明を入力します。データベース選択セクションでは、先程作成した log_analysis を選択します。

データセットセクションでは、ログファイルが設定されている S3 のバケットディレクトリを指定します。

データ形式セクションでは、ファイル(今回でいうとログファイル)のパースに必要な情報を指定します。

今回、ログは正規表現でパースするため、このような指定になっています。

  • テーブルタイプ
  • ファイル形式
  • SerDe ライブラリ
  • SerDe プロパティ - オプション
    • 名前: input.regex
    • 値: ^(\\S+) - - \\[([^\\[]+)\\] "(\\S+) (\\S+) (\\S+)" (\\S+) (\\S+) "(\\S+)" "(.*)"

列の詳細セクションでは、作成するテーブルのカラムを定義します。

  • ip: string,
  • time: string,
  • method: string,
  • uri: string,
  • protocol: string,
  • status: int,
  • bytes_sent: int,
  • referer: string,
  • user_agent: string

注意点は、データ形式セクションでログのパースを正規表現で記載しましたが、正規表現によってキャプチャ(抽出)する数と、ここで指定するカラムの数は同じである必要があります。

このセクション以降はオプションなので今回は設定しません。最下部にある「テーブルを作成」ボタンを押下すると、テーブルが作成されます。

-- DDL
CREATE EXTERNAL TABLE IF NOT EXISTS `mydatabase`.`nginx_logs` (
  `ip` string, 
  `time` string,
  `method` string,
  `uri` string,
  `protocol` string,
  `status` int,
  `bytes_sent` string,
  `referer` string,
  `user_agent` string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
  'input.regex' = '^(\\S+) - - \\[([^\\[]+)\\] "(\\S+) (\\S+) (\\S+)" (\\S+) (\\S+) "(\\S+)" "(.*)"'
)
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://sample-logs/logs/'
;

クエリを実行しログデータを取得する

テーブルが作成されたので、実際にクエリを実行してログのデータを取得してみます。

SELECT 
*
FROM nginx_logs
;

取得できました。SQL を記述してログのデータが取得できるのはとても利便性が高いですね。

ip time method uri protocol status bytes_sent referer user_agent
192.168.0.247 2024-02-18 14:56:55 POST /api/v1/tasks/24361 HTTP/1.1 201 7101 - Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.9876.54321 Safari/537.36
192.168.0.142 2024-02-18 14:56:56 GET /api/v1/messages/20050 HTTP/1.1 200 2942 - Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/97.0.4692.99 Safari/537.36
192.168.0.17 2024-02-18 14:56:57 GET /api/v1/users/29621 HTTP/1.1 401 6058 - Mozilla/5.0 (Macintosh; Intel Mac OS X 1101) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.1234.5678 Safari/537.36
192.168.0.243 2024-02-18 14:56:58 PUT /api/v1/orders/49212 HTTP/1.1 204 6035 - Mozilla/5.0 (Macintosh; Intel Mac OS X 1152) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.9876.54321 Safari/537.36
192.168.0.176 2024-02-18 14:56:59 POST /api/v1/products/15518 HTTP/1.1 201 8669 - Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.1234.5678 Safari/537.36
192.168.0.144 2024-02-18 14:57:00 GET /api/v1/users/26990 HTTP/1.1 200 9565 - Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.9876.54321 Safari/537.36
192.168.0.70 2024-02-18 14:57:01 POST /api/v1/users/30991 HTTP/1.1 201 7781 - Mozilla/5.0 (Macintosh; Intel Mac OS X 1152) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.9876.54321 Safari/537.36
192.168.0.201 2024-02-18 14:57:02 PUT /api/v1/tasks/7064 HTTP/1.1 204 7816 - Mozilla/5.0 (Macintosh; Intel Mac OS X 1101) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/98.0.1234.5678 Safari/537.36
192.168.0.166 2024-02-18 14:57:03 GET /api/v1/users/12541 HTTP/1.1 200 6082 - Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.9876.54321 Safari/537.36
192.168.0.233 2024-02-18 14:57:04 DELETE /api/v1/products/1588 HTTP/1.1 204 5112 - Mozilla/5.0 (Macintosh; Intel Mac OS X 1152) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.9876.54321 Safari/537.36
192.168.0.47 2024-02-18 14:57:05 PUT /api/v1/orders/8586 HTTP/1.1 204 7961 - Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/97.0.4692.99 Safari/537.36

ログデータの分析

SQL でログデータを分析していけるということで、簡単な分析を行ってみます。

エンドポイントの成功率・エラー率

各エンドポイントの成功率やエラー率を算出してみます。

WITH log_with_endpoint_added AS (
    SELECT 
        method,
        regexp_extract(uri, '^/api/v1/\w+') as endpoint,
        status
    FROM nginx_logs
)
, log_with_call_count_added AS (
    SELECT
        method, 
        endpoint, 
        status,
        status / 100 as status_class,
        COUNT(endpoint) as call_count
    FROM log_with_endpoint_added
    GROUP BY method, endpoint, status
)
, number_results_per_endpoint AS (
    SELECT
        endpoint, 
        method, 
        SUM(call_count) as all_count,
        SUM(CASE WHEN status_class = 2 THEN call_count ELSE 0 END) as succeeded_count,
        SUM(CASE WHEN status_class = 4 THEN call_count ELSE 0 END) as client_error_count,
        SUM(CASE WHEN status_class = 5 THEN call_count ELSE 0 END) as server_error_count
    FROM log_with_call_count_added
    GROUP BY endpoint, method
)
SELECT 
    endpoint,
    method,
    all_count as count,
    CONCAT(CAST(ROUND((CAST(succeeded_count as DOUBLE) / all_count) * 100, 2) as VARCHAR), '%') as succeeded_rate,
    CONCAT(CAST(ROUND((CAST(client_error_count as DOUBLE) / all_count) * 100, 2) as VARCHAR), '%') as client_error_rate,
    CONCAT(CAST(ROUND((CAST(server_error_count as DOUBLE) / all_count) * 100, 2) as VARCHAR), '%') as server_error_rate
FROM number_results_per_endpoint
ORDER BY server_error_rate desc
;

endpoint method count succeeded_rate client_error_rate server_error_rate
/api/v1/users DELETE 15 73.33% 20.00% 6.67%
/api/v1/products DELETE 22 81.82% 13.64% 4.55%
/api/v1/tasks GET 96 84.38% 11.46% 4.17%
/api/v1/orders GET 88 85.23% 11.36% 3.41%
/api/v1/products POST 59 74.58% 22.03% 3.39%
/api/v1/users POST 59 83.05% 13.56% 3.39%
/api/v1/messages GET 95 88.42% 8.42% 3.16%
/api/v1/users PUT 32 87.50% 9.38% 3.13%
/api/v1/products PUT 40 80.00% 17.50% 2.50%
/api/v1/products GET 88 79.55% 18.18% 2.27%
/api/v1/users GET 91 78.02% 19.78% 2.20%
/api/v1/messages POST 49 83.67% 14.29% 2.04%
/api/v1/tasks POST 59 88.14% 10.17% 1.69%
/api/v1/messages PUT 29 82.76% 17.24% 0.00%
/api/v1/messages DELETE 17 100.00% 0.00% 0.00%
/api/v1/orders DELETE 18 94.44% 5.56% 0.00%
/api/v1/tasks DELETE 8 87.50% 12.50% 0.00%
/api/v1/tasks PUT 39 92.31% 7.69% 0.00%
/api/v1/orders PUT 40 82.50% 17.50% 0.00%
/api/v1/orders POST 56 89.29% 10.71% 0.00%

SQL でクエリを書けるので、集計などもこうして柔軟に分析を行っていけます。

複数リソースを用いた分析

Athena を使えば、ログファイルを柔軟に分析できることがわかりました。次に、ログファイルだけではなく、別のリソースを掛け合わせて使ってみます。

ログファイル × CSV ファイル

先ほどのログファイルのデータを使って、このアプリケーション API において使われていないエンドポイントを抽出してみます。

新たに、エンドポイントの一覧を収録した CSV ファイルを用意しました。

endpoints.csv

/api/v1/products
/api/v1/orders
/api/v1/users
/api/v1/messages
/api/v1/tasks
/api/v1/bookmarks
/api/v1/friends

表というよりは、エンドポイントの一覧が書き連ねてあるだけの CSV ファイルです。

これを S3 に配置します。

この CSV に対応するテーブルを Athena に作成します。(nginx_logs テーブルを作成したのと同じ要領で、CSV をパースする設定にてテーブルを作成します。)

-- DDL
CREATE EXTERNAL TABLE IF NOT EXISTS `mydatabase`.`endpoints` (`endpoint` string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES ('field.delim' = ',')
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION 's3://sample-logs/documents/'
TBLPROPERTIES ('classification' = 'csv');

作成した endpoints テーブルを SELECT してみます。

あとは、先程の nginx_logs テーブルのデータにこの endpoints テーブルのデータをぶつければ、使用されていないエンドポイントが抽出できます。

WITH log_with_endpoint_added AS (
    SELECT 
        method,
        regexp_extract(uri, '^/api/v1/\w+') as endpoint,
        status
    FROM nginx_logs
)
, endpoint_used AS (
    SELECT 
        DISTINCT endpoint
    FROM log_with_endpoint_added
)

SELECT 
*
FROM endpoints
WHERE not exists(
    SELECT * FROM endpoint_used WHERE endpoints.endpoint=endpoint_used.endpoint
)
;

こんな風に、様々な形式のデータを取り込み、分析できる柔軟性を Athena は持ち合わせています。

瞬時に、柔軟に、そして簡単に。

AWS Athena は、標準的な SQL を使用してデータにアクセスできるため、SQL のスキルを持つ多くのユーザーにとって親しみやすい環境です。今回は取り上げませんでしたが、notebook の実行も可能なため、SQL の範囲を超えるような分析も行える点も良いです。

また、データのフォーマットに対する柔軟性も高く、JSONCSV、Parquet、ORC などのさまざまな形式のデータに対応しています。これにより、さまざまなデータソースからのデータを統合して分析することができます。

個人的には、急なログ分析の必要が生じた際に瞬時に分析環境を構築できる点は、非常に心強く、利便性が高いと感じています。

ログなどの分析を行う際は、Amazon Athena を試してみてください。


現在 back check 開発チームでは一緒に働く仲間を募集中です。 herp.careers