BigQuery入門！無料データでSQLの基本検索関数をマスター

2024年9月15日2024年11月28日

記事の対象者と解消できるお悩み

【対象者】

初中級データアナリスト

【興味関心】

大量データからの情報抽出
複雑な条件でのデータ検索
テキストデータ分析
類似データ検索
SQLスキルアップ
業務効率化

【解消できるお悩み】

大量のデータから、必要な情報を見つけ出すのに時間がかかる…
複雑な条件でデータを検索したいけど、SQLの書き方がわからない…
商品名や顧客名など、テキストデータから特定のキーワードを含むデータを探したい
顧客の属性や購買履歴が似ている顧客を見つけたい
SQLスキルを向上させて、より高度な分析に挑戦したい
データ分析をもっと効率化して、時間を節約したい

記事作成者

【人物】株式会社志庵　代表取締役　佐藤　光　起業6年目

【経歴】株式会社光通信➜光通信同期と起業➜データアナリスト転職➜SaaSで2度目起業

【会社実績】

SaaS 資金調達無しARR2億円

SaaS GMV200億円

【内容】自社AaaSであるStruccle、webスクレイピング、BigQueryに関する記事を作成

各種用語

ARR(Annual Recurring Revenue)
- サブスクリプションサービスの年間売上※当社はSaaS売上
Struccle(ストラクル)
- 株式会社志庵が独自開発しているAaaS
AaaS(アース)
- Data Analytics as a Serviceの意
SaaS(サース)
- Software as a Serviceの意
GoogleCloudPlatform
- Googleが提供するクラウドプラットフォーム。この中に様々なサービスが存在する。
GCP(ジーシーピー)
- GoogleCloudPlatformの略
BigQuery(ビッグクエリ)
- 上記GCPの中に含まれるデータ分析基盤サービス
SQL(エスキューエル)
- データベースを操作するプログラミング言語
クエリ
- SQLで記述した命令文
- クエリ例）
  - SQL構文のクエリ⬇
    - SELECT item_name, price FROM sales_table where price >= 1000;
    - 上記クエリ(命令文)の意味としては「売上テーブル(sales_table)から価格(price)が1000円以上の商品名(item_name)と価格(price)を抽出する」
ペタバイト
- データの単位　ペタバイト＝1000テラバイト＝約100兆行のデータ

概要

大量のデータから特定の条件に合致する情報を見つけ出すことは、データ分析の基本です。Google CloudのBigQueryは、効率的なデータ検索を実現するための強力な検索関数を備えています。これらの関数を使いこなすことで、複雑な条件でのデータ抽出や、類似データの検索などをスムーズに行うことができます。

この記事では、BigQueryで利用できる基本的な検索関数を、分かりやすい例とともに紹介します。BigQueryの無料公開データセットを使って、実際に手を動かしながら検索関数をマスターしましょう！

BigQueryの検索関数とは？
BigQueryの基本検索関数一覧
1. SEARCH
2. VECTOR_SEARCH
BigQuery公開データで実践！検索関数を使った分析例
FAQ：BigQueryに関するよくある質問
まとめ：BigQueryの検索関数をマスターして、データ分析の幅を広げよう！

1. BigQueryの検索関数とは？

BigQueryの検索関数は、データセットの中から特定の条件に合致するデータを見つけ出すための関数です。従来のSQLでは、`WHERE`句を使って条件を指定してデータを抽出していましたが、検索関数は、より複雑な条件やあいまいな条件での検索を可能にします。例えば、複数のキーワードを含むテキストデータや、類似したベクトルデータなどを効率的に検索することができます。

2. BigQueryの基本検索関数一覧

BigQueryで利用できる基本的な検索関数を、例とともに紹介します。

2.1 SEARCH

テーブルやその他の検索データに、一連の検索語が含まれているかどうかを確認します。検索語句を含む行の配列を返します。

構文：

SEARCH(search_query, text_or_array_to_search[, language])

例： taxi_trips テーブルから、乗車場所 (pickup_location_id) に ‘Manhattan’ を含む乗車レコードを検索する

CREATE TEMP TABLE table2 (--一時テーブルを作成
  query_id STRING,
  embedding ARRAY<FLOAT64>
)
as
select 'dog' query_id, [1.0, 2.0] embedding
union all
select 'cat' query_id, [3.0, 5.2] embedding;


SELECT *
FROM table2
where SEARCH(query_id, "dog")

2.2 VECTOR_SEARCH

埋め込みに対してベクトル検索を実行し、意味的に類似したエンティティを見つけます。検索語句に類似したベクトルを持つ行の配列を返します。

構文：

VECTOR_SEARCH(query_embedding, (SELECT (embedding, id) FROM table_with_embeddings), num_neighbors)

例： 次の例は、table1のmy_embedding列から、table2のembedding列の各行データにマッチする上位2つのembeddingを検索します：

CREATE TEMP TABLE table1 (--一時テーブルを作成
  id INT64,
  my_embedding ARRAY<FLOAT64>
)
as
select 1 id, [1.0, 2.0] my_embedding
union all
select 2 id, [2.0, 4.0] my_embedding
union all
select 3 id, [1.5, 7.0] my_embedding
union all
select 4 id, [1.0, 3.2] my_embedding
union all
select 5 id, [5.0, 5.4] my_embedding;

CREATE TEMP TABLE table2 (--一時テーブルを作成
  query_id STRING,
  embedding ARRAY<FLOAT64>
)
as
select 'dog' query_id, [1.0, 2.0] embedding
union all
select 'cat' query_id, [3.0, 5.2] embedding;


SELECT *
FROM
  VECTOR_SEARCH(
    TABLE table1,--table1を対象に
    'my_embedding',--my_embedding列から
    (SELECT query_id, embedding FROM table2),--table2の
    'embedding',--embedding列の各業データにマッチ
    top_k => 2--上位2つのembeddingを検索
  );

3. BigQuery公開データで実践！検索関数を使った分析例

ここでは、BigQueryの公開データセット「NYC Taxi Trips」を使って、検索関数を使った簡単なSQL分析例を紹介します。このデータセットには、ニューヨーク市のタクシー乗車に関する情報が格納されています。

例：乗車場所 (pickup_location_id) に ‘166’ を含む乗車レコードを検索し、乗車距離が上位5件のレコードを取得する

SELECT
    pickup_location_id,
    trip_distance
  FROM
    bigquery-public-data.new_york_taxi_trips.tlc_yellow_trips_2015
  WHERE SEARCH(pickup_location_id, '166')
QUALIFY ROW_NUMBER() OVER (ORDER BY trip_distance DESC) <= 5;

このクエリでは、`SEARCH`関数を使って、`pickup_location_id`カラムに ‘166’ を含むレコードを検索し、`ROW_NUMBER()`関数と`QUALIFY`句を使って乗車距離が上位5件のレコードを取得しています。

4. FAQ：BigQueryに関するよくある質問

Q1. BigQueryの無料枠を超えて利用すると、どのくらい費用がかかりますか？

A1. 無料枠を超えた場合、従量課金制で料金が発生します。料金は、処理したデータ量やストレージ容量などによって異なります。詳しくは、BigQueryの料金ページをご確認ください。

Q2. BigQueryで使えるSQLは、他のデータベースと同じですか？

A2. BigQueryは標準SQLをサポートしており、他のデータベースで学んだSQLの知識を活かすことができます。ただし、BigQuery独自の関数や構文も存在するため、BigQueryのドキュメントで詳細を確認することをおすすめします。

Q3. SQLの学習には、どのような教材がありますか？

A3. SQLの学習には、オンライン学習サイト、書籍、動画教材など、様々な教材があります。初心者向けの教材から、上級者向けの教材まで、レベルに合わせて選ぶことができます。Googleが提供するBigQueryのチュートリアルもSQLの学習に役立ちます。

5. まとめ：BigQueryの検索関数をマスターして、データ分析の幅を広げよう！

BigQueryの検索関数は、大量のデータから必要な情報を効率的に抽出するために非常に役立ちます。この記事で紹介した関数以外にも、様々な検索関数が用意されています。BigQueryの公式リファレンスなどを参考に、検索関数をマスターし、データ分析の幅を広げましょう！

BigQuery導入サポート、転職検討中の方はお問い合わせフォームからご相談くださいませ。

誠心誠意精一杯対応いたします。

【参考URL】

GoogleCloudPlatform：https://console.cloud.google.com/welcome/new

BigQuery：https://cloud.google.com/bigquery?hl=ja

BigQueryリリース情報：https://cloud.google.com/bigquery/docs/release-notes

BigQuery料金：https://cloud.google.com/bigquery/pricing

BigQuery料金無料枠：https://cloud.google.com/bigquery/pricing?hl=ja#free-tier

Google Cloud活用事例：https://cloud.google.com/customers/index.html?hl=ja#

Looker Studio：https://cloud.google.com/looker-studio?hl=ja

Looker Studioサンプル：https://cloud.google.com/bigquery/docs/visualize-looker-studio?hl=ja

よかったらシェアしてね！

URLをコピーしました！

URLをコピーしました！