📑

BigQueryのサンドボックス環境をつかってみる

2022/10/27に公開

サンドボックスとは

  • クレジットカードを登録せずに制限ありで無料でBigQueryを使用することができる環境
  • サンドボックスの制限は
     - 毎月 10 GBのアクティブストレージと1TBのクエリデータ処理

他、以下機能が未サポート
 ・データのストリーミング
 ・データ操作言語(DML)のステートメント
 ・BigQuery Data Transfer Service

サンドボックスの利用方法

詳細は以下参照
 https://cloud.google.com/bigquery/docs/sandbox

以下HPに遷移
https://cloud.google.com/bigquery/?hl=en

右の画面で、右上の「Console」をクリックしてください

右の画面の左側部分をスクロールして「BigQuery」を選んでください
(クレカ登録してなければサンドボックスの 画面になるはず)

以下画面のように、左上に「サンドボックス」の表示があればOK

つかってみる

command + enter で実行できる

SELECT 1 + 2
SELECT 3.0 * 2.5
SELECT 8/2
SELECT CAST(8/2 as INTEGER)
SELECT 5%3
SELECT (2 + 3) = (4 + 1)
SELECT (2 + 3) != (4 + 1)
SELECT 5 BETWEEN 5 and 10
SELECT 'c' BETWEEN  'a' and  'd' 

legacySQLとstandardSQL

・BigQueryにはlegacySQL(旧)とstandardSQL(新)がある
・legacySQLをつかうときは“#legacySQL”,
standardSQLをつかうときは“#standardSQL”とかく

#legacySQL
SELECT 'ABC' + 'DEF'

#legacySQL→#standardSQLにすると?

なお、デフォルトはlegacySQLである
https://cloud.google.com/bigquery/docs/reference/standard-sql/enabling-standard-sql?hl=ja

legacySQLとstandardSQLで使用可能な機能が大きく異なる→https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql?hl=ja

サンプルテーブル

そろそろテーブルからデータを読み出してみたい!
→サンプルテーブルからデータが読める
https://cloud.google.com/bigquery/sample-tables

さっそくサンプルテーブルから読みだしてみたい!
 テーブルの総行数は?

#standardSQL
SELECT count(*)
FROM `bigquery-public-data.samples.gsod`

→次は、テーブルの中身をみたい!
 (右クエリは16GBスキャンして
  いい人以外実行しないこと)

→右真ん中にクエリ実行後のデータ数が
  見えるがとても大きな数になっている
  (クエリ検証ツールと呼ぶ)

BigQueryのスキャンについて

・大きなデータのスキャンはWHERE, LIMITとかで絞ればいい?
 →BigQueryは常に列をフルスキャンする
  ・WHERE, LIMITの類でレコードを絞ろうとしても意味がない
   ・実験してみよう(実行はせず、LIMIT 1000をつけてみるとか)
    ・クエリ検証ツールの結果は?

・(参考)スキャンデータ量(課金額)を絞るためには以下の方法などがある
 ・抽出するカラムを絞る(SELECT * は基本的に使わないように)
 ・テーブルを分割or縮小する(日付ごとにする、とか正規形にする、とか)
 ・パーティションを使用する
 ・このあたりの詳細はGoogle公式ドキュメントを参照のこと
https://cloud.google.com/bigquery/docs/best-practices-costs
https://cloud.google.com/bigquery/pricing#on_demand_pricing

テーブルのプレビューをみる

・結局テーブルの中身をちらっと見たい場合はどうするのか?
 →プレビュー機能がある(プレビューに課金はされない)
  https://cloud.google.com/bigquery/docs/best-practices-costs#preview-data
→サンプルテーブルの場合、
   「データを追加」→「一般公開データセットを調べる」
   からgsodなどで検索→「データセットの表示」で、
   クエリエディタのリソースにbigquery-public-dataが追加されるので、
   好きなサンプルテーブルの名前でフィルタリングし、
   テーブル名をクリックするとプレビューが表示される
(文字にすると長いのだが、辿っていけばわかると思う)
  以下リンク先は旧版のプレビュー表示画面
  https://bigquery.cloud.google.com/dataset/bigquery-public-data:samples?hl=ja&refresh=1pli%3D1&pli=1
(Schemaからカラムの意味も分かる、Detailsからテーブルサイズが16.1GBだったということもわかる)

クエリリファレンスを読んでみる

・クエリリファレンスにいろいろな関数とクエリ例がある
https://cloud.google.com/bigquery/query-reference?hl=ja
・ただしlegacySQLなのでstandardSQLで実行するには変更が必要
 違いはこちら
https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql?hl=ja

標準クエリリファレンス

・standardSQLのリファレンスもある
 https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax?authuser=1&hl=ja#limit-clause-and-offset-clause
・ただし、クエリ例はコピペでは動かない

自分でテーブルをつくる

・カラム絞ってもバイト数がそこそこ大きくなるし、、、
・お試し用で自分の都合のいいデータを簡単につくりだしたい!
 →自分でテーブルを作る方法もある(限界は1度に送れるクエリの文字数?)
https://qiita.com/yancya/items/a1ebe6dbc5d635839cc8

#standardSQL  
WITH t AS (SELECT * FROM UNNEST(ARRAY<STRUCT<id INT64, name STRING>>
  [(1, 'taro'), (2, 'jiro')]))
SELECT id , name FROM t

まとめ

・BigQueryサンドボックスを利用して、任意のクエリをいつでも試すことができるようになった
 ・サンドボックスでクエリが実行できた
   ・サンドボックスで任意のサンプルテーブルが読み出せた
   ・クエリリファレンスがどこにあるかわかった

Discussion