📑

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

2022/10/27に公開

Google Cloud

BigQuery

tech

サンドボックスとは

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

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

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

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

以下HPに遷移

右の画面で、右上の「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である

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

サンプルテーブル

そろそろテーブルからデータを読み出してみたい！
→サンプルテーブルからデータが読める

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

#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#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だったということもわかる）

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

・クエリリファレンスにいろいろな関数とクエリ例がある

・ただしlegacySQLなのでstandardSQLで実行するには変更が必要
　違いはこちら

標準クエリリファレンス

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

自分でテーブルをつくる

・カラム絞ってもバイト数がそこそこ大きくなるし、、、
・お試し用で自分の都合のいいデータを簡単につくりだしたい！
　→自分でテーブルを作る方法もある（限界は１度に送れるクエリの文字数？）

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

まとめ

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