BigQuery

ユーザーを笑顔にする BigQuery の使いやすい SQL 新機能

Google Cloud Japan Team

2020年10月22日

GCP を試す

Start building on Google Cloud with $300 in free credits and 20+ always free products.

※この投稿は米国時間 2020 年 10 月 14 日に、Google Cloud blog に投稿されたものの抄訳です。

10 月は、スマイリーフェイスの生みの親である Harvey Ball が、人々を笑顔にする日であると宣言したワールドスマイルデイを祝う月です。今月から一般提供が開始される、使いやすい SQL の新機能が、BigQuery ユーザーを笑顔にすることでしょう。

テーブルオペレーションの新しいコマンド

BigQuery ユーザーは、テーブルレベルのオペレーションを使用してスキーマを進化させ、新しいコマンドを使って新しいデータのテーブルを準備できるようになりました。

ADD COLUMN: BigQuery でテーブルに新しい列を追加する操作は、以前はコンソール UI、API、bq cli のみが対応していました。現在は、BigQuery の ALTER TABLE コマンドでサポートされるようになった ADD COLUMN 句でテーブルに新しい列を追加できます。ドキュメント

TRUNCATE TABLE: 以前は、BigQuery でテーブルのコンテンツ全体を削除するのに DELETE コマンドを実行する必要があり、DML コストが発生していました。新機能では、TRUNCATE コマンドを実行してテーブル内のすべてのレコードを削除できるようになりました。これは、BigQuery でサポートされている多くの無料のオペレーションの一つです。ドキュメント

Unicode テーブルの命名: BigQuery でのテーブルの命名は、ASCII 文字セット（文字、数字、アンダースコア）のサブセットに限定されていました。BigQuery で Unicode のテーブル名がサポートされるようになったことにより、BigQuery 内で CUSTOMER という名前のテーブルに加えて、ग्राहक、お客様、顾客、заказчик、الزبون などと命名されたテーブルに顧客データを格納できます。ドキュメント

  Language: SQL

  -- Create the initial table employees on which you can add columns
-- Replace the "dataset" below with your entries
CREATE TABLE dataset.employés 
(
	employee_id INT64,
	manager_id INT64
);

-- Add new columns to the tables using ADD COLUMN clause
ALTER TABLE dataset.employés
ADD COLUMN last_name STRING,
ADD COLUMN date_of_birth DATE,
ADD COLUMN work_location GEOGRAPHY
;

-- Empty out the contents of the employee table to reload it
-- with data for the new columns
TRUNCATE TABLE dataset.employés
;

外部ストレージの読み取りと書き込み

ユーザーは BigQuery を使って外部ストレージバケット内のデータを読み取ることができます。新しい SQL コマンドなら、BigQuery 内でこうしたバケットを指定するようにテーブルを構成し、クエリから取得したデータをストレージバケットにエクスポートすることも可能です。

CREATE OR REPLACE EXTERNAL TABLE: BigQuery を使用すると、Google Cloud Storage（GCS）バケットのデータを BigQuery 内の外部テーブルとして登録することでクエリを実行できます。CREATE EXTERNAL TABLE コマンドを実行して、BigQuery で SQL を使ってこうした外部データソースのためのテーブル定義を作成できるようになりました。外部データが Hive パーティショニングで分割されている場合、DDL の一部として任意の Hive パーティション URI プレフィックスを使ってパーティショニングスキームを指定できます。ドキュメント

  Language: SQL

  -- Create an external table automatically detecting table schema 
CREATE EXTERNAL TABLE IF NOT EXISTS dataset.whale_detection_data
OPTIONS (
uris=["gs://noaa-passive-bioacoustic/cornell_sperm_whale_detections/detection_data/GoMexSpermWhalePresence-2010*.csv"], 
  	format=CSV
);
-- Create an external table with explicit schema declaration. All the fields are set to data type STRING.
CREATE OR REPLACE EXTERNAL TABLE dataset.whale_detection_data (
  	Julian_Date STRING, 
  	Date STRING, 
  	Month STRING, 
  	Day STRING, 
  	Year STRING, 
  	Season STRING, 
  	Region STRING, 
  	Site_HF STRING, 
  	Hours_Presence STRING, 
  	DayHr STRING, 
  	Proportion_Presence STRING, 
  	CorrPres STRING, 
  	CorArcsin STRING
)
OPTIONS (
uris=["gs://noaa-passive-bioacoustic/cornell_sperm_whale_detections/detection_data/GoMexSpermWhalePresence-2010*.csv"], 
 	 format=CSV
);

DROP EXTERNAL TABLE: 外部テーブルのテーブル定義は、DROP EXTERNAL TABLE コマンドを実行して削除できます。削除されるのは BigQuery 内のテーブル定義のみで、ストレージバケットに格納されているデータは削除されないことにご注意ください。また、外部テーブルの削除には EXTERNAL プレフィックスが必要です。DROP TABLE table_name（EXTERNAL プレフィックスなし）が有効なのは通常の BigQuery テーブルに対してのみで、外部テーブルで実行しようとすると失敗します。ドキュメント

  Language: SQL

  -- Drop an external table on storage bucket if it exists
DROP EXTERNAL TABLE IF EXISTS dataset.whale_detection_data;

EXPORT DATA: データのパイプラインオペレーションの一環として、分析クエリの出力結果を外部ストレージにエクスポートして、結果を他の関係者と共有し、データサイエンスやデータレイクなどの継続的なデータパイプラインにフィードしたいという BigQuery ユーザーからの要望があります。コンソール UI やスクリプトで EXPORT DATA コマンドを実行して、BigQuery がサポートしているデータ形式と圧縮形式でクエリの結果を Google Cloud Storage バケットに保存できるようになりました。ドキュメント

  Language: SQL

  -- Export result of a query execution to GCS bucket

EXPORT DATA OPTIONS (
uri="gs://mybucket/myfile/*", 
  	format=CSV
) AS 
SELECT corpus, sum(word_count) as word_count 
FROM `bigquery-public-data.samples.shakespeare` 
GROUP BY corpus 
ORDER BY 2 DESC;

スクリプティングと SQL の改善

SQL でサポートされているスクリプティングとストアドプロシージャに基づいて、BigQuery では動的 SQL、承認済みユーザー定義関数、SQL 言語の改善が導入されています。

EXECUTE IMMEDIATE: 動的 SQL は SQL のスクリプティングで使用できる強力な手法で、SQL を使って動的に SQL を生成して実行できます。EXECUTE IMMEDIATE は静的文字列と変数の組み合わせの式を受け取り、有効な SQL ステートメントをその場ですぐに生成して即座に実行します。セキュリティ上の理由から、1 回の EXECUTE IMMEDIATE の呼び出しごとに実行できる SQL ステートメントは 1 つだけです。ドキュメント

  Language: SQL

  -- Example of Dynamic SQL
DECLARE year INT64 DEFAULT 2015;
EXECUTE IMMEDIATE format("""
SELECT COUNT(*)
FROM `bigquery-public-data`.new_york.tlc_yellow_trips_%d
""", year);

承認済みユーザー定義関数（UDF）: 承認済み UDF は、特定のデータセットへのアクセスが有効になるよう IAM 権限を使用して承認されたユーザー定義関数です。UDF はデータセット内のテーブルに対して、UDF を呼び出しているユーザーにテーブルへのアクセス権がない場合でもクエリを実行できます。ドキュメント

クエリ結果内の重複する列名: 従来の BigQuery では、クエリ結果内の 2 つの列に同じ名前を使用できませんでした。そのため、アナリストは検索するクエリを手動で編集して、重複している列名を変更する必要がありました。現在は、BigQuery で同じ列名を使ってクエリを実行できるようになりました。BigQuery は自動的に数字のサフィックス（_n）を追加して、重複する列を区別します。ドキュメント

  Language: SQL

  -- Example of duplicate column name
SELECT duration, durationMinutes/60 duration
FROM bigquery-public-data.baseball.games_post_wide
LIMIT 10;

新しい DATE 関数

BigQuery に、ビジネスプロセスに必要とされる日付演算を行う新しい関数が導入されます。

DATE ± n（日数）: BigQuery で日数を加算または減算するといった DATE の算術演算は、従来サポートされていた DATE_ADD 関数と DATE_SUB 関数を使って特定の日付に対して日数を加減していました。現在はシンプルな算術演算（+ または -）を使って日数を加減できるようになりました（HIRE_DATE+7、DELIVERY_DATE–2 など）。ドキュメント

LAST_DAY: この関数は、指定された日付表現の時間単位における最後の日付を返します（その日付が属する月や年の最後の日など）。ドキュメント

  Language: SQL

  SELECT LAST_DAY(DATE '2008-11-25', MONTH)+1 AS first_day_next_month;

新しい文字列関数

BigQuery には、文字列データを抽出および操作して検索やテキストマッチングを有効化するための新しい関数も導入されます。

連結演算子（“||”）のサポート: 従来の BigQuery の連結演算子には、複数の文字列を連結するために CONCAT 関数を使用する必要がありました。現在は連結演算子 || を使って複数の文字列を連結できるようになりました。「ABC」という組み合わせは “A”||“B”||“C” だけで済みます。ドキュメント

  Language: SQL

  SELECT ‘a’ || ’b’ || ’c’  string_concatenation, [‘a’,’b’] || [‘c’] array_concatenation;

LEFT: 関数 LEFT(value, length) は、関数で指定された長さに対応する文字列値の左端の文字を返します。ドキュメント

RIGHT: 関数 RIGHT(value, length) は、関数で指定された長さに対応する文字列値の右端の文字を返します。ドキュメント

INSTR: 関数 INSTR(source_value, search_value[, position[, occurrence]]) は、文字列の特定の位置から開始して、文字列内における検索値の指定された出現場所をレポートします。ドキュメント

INITCAP: 関数 INITCAP(value[, delimiters]) は、文字列の各単語の最初の文字を大文字で表示し、その他の文字は小文字で表示します。文章内で使用されている標準の句読文字を上書きするよう任意の区切り文字を指定できます。ドキュメント

TRANSLATE: 関数 TRANSLATE(expression, source_characters, target_characters) は、文字列のソース文字をターゲット言語にマッピングして、変換された値を返します。ドキュメント

ASCII: 関数 ASCII(value) は、指定された文字列値の最初の文字の ASCII コードを返します。ドキュメント

UNICODE: 関数 UNICODE(value) は、指定された文字列値の最初の文字の Unicode コードを返します。ドキュメント

CHR: 関数 CHR(value) は、値の指定された Unicode コードポイントに対応する Unicode 文字を返します。ドキュメント

SOUNDEX: 関数 SOUNDEX(value) は、文字列の発音を表す Soundex コードを返します。Soundex コードは 1 つの文字とそれに続く 3 桁で構成され、2 つの文字列の英語発音が類似しているかどうかを判断するのに使われます。ドキュメント

REGEXP_SUBSTR: 関数 REGEXP_SUBSTR(value, regexp[, position[, occurrence]]) は関数 REGEXP_EXTRACT と同義で、正規表現 regexp と一致する value 内の部分文字列を返します。ドキュメント

REGEXP_EXTRACT: 既存の関数 REGEXP_EXTRACT(value, regexp[, position[, occurrence]]) が拡張され、位置と出現がサポートされるようになりました。これにより、正規表現と一致すべき開始位置と、オプションの追加引数として検索する指定された出現を特定できます。ドキュメント

REGEXP_INSTR: 関数 REGEXP_INSTR(source_value, regexp [, position[, occurrence, [occurrence_position]]]) は、source_value 内の正規表現の特定のインスタンスの位置を返します。ドキュメント

OCTET_LENGTH: 関数 OCTET_LENGTH(value) は関数 BYTE_LENGTH と同義で、文字列値の長さをバイト数で返します。ドキュメント

  Language: SQL

  -- Examples of String functions
SELECT 
 REGEXP_INSTR('Hello Helloo and Hellooo', 'H?ello+', 1, 2),      
 INSTR('abc@google.com', '@google.com'),        
 REGEXP_EXTRACT('Hello Helloo and Hellooo', 'H?ello+', 1, 2),      
 REGEXP_SUBSTR('Hello Helloo and Hellooo', 'H?ello+', 1, 2),   
 SOUNDEX("terry"), SOUNDEX("teri"),            
 INITCAP("hello, this is a demo"),              
 TRANSLATE("this is a cookie", "co", "ku"),    
 ASCII("abc"),                             
 CHR(97),                                   
 UNICODE("学生"),                              
 LEFT("206-999-9999", 3),                      
 RIGHT("2000-08-24", 2),                      
 OCTET_LENGTH(b"абвгд");

拡張された INFORMATION_SCHEMA ビュー

ANSI SQL 標準で指定されている INFORMATION_SCHEMA により、ユーザーは参照データを含むさまざまなオブジェクト（テーブル、ビュー、プログラムなど）やエンティティ（ストアドプロシージャやユーザー定義関数など）に関するメタデータをクエリできます。

テーブルの情報スキーマ: INFORMATION_SCHEMA の TABLES ビュー、TABLE_OPTIONS ビュー、COLUMNS ビュー、COLUMN_FIELD_PATHS ビューからテーブルのメタデータを検索できるようになりました。

  Language: SQL

  # Example where we get the latest schema for a wildcard query.
SELECT * FROM my_dataset.INFORMATION_SCHEMA.COLUMNS
WHERE table_name =
  (SELECT table_name
   FROM my_dataset.INFORMATION_SCHEMA.TABLES
   WHERE STARTS_WITH(table_name, 'my_prefix')
   ORDER BY creation_time DESC
   LIMIT 1)

ビューの情報スキーマ: INFORMATION_SCHEMA の VIEWS を使って、ビューのメタデータを検索できます。

ルーティンの情報スキーマ: ルーティンのメタデータは、INFORMATION_SCHEMA の ROUTINES ビュー、ROUTINE_OPTIONS ビュー、PARAMETERS ビューに含まれています。

データセットの情報スキーマ: データセット情報は、INFORMATION_SCHEMA の SCHEMATA ビューと SCHEMATA_OPTIONS ビューに含まれています。

  Language: SQL

  # Example combining INFORMATION_SCHEMA with dynamic SQL to query common
# schemas across a region.
DECLARE unions DEFAULT
  (SELECT STRING_AGG(
     "SELECT my_col FROM " || table_schema || "." || table_name,
     " UNION ALL ")
   FROM `region-us`.INFORMATION_SCHEMA.COLUMNS
   WHERE column_name = "my_col"
     AND data_type = "STRING");
EXECUTE IMMEDIATE "SELECT my_col FROM (" || unions || ")";

これらの SQL の新機能によって、BigQuery ユーザーの皆様が笑顔になることを願っています。BigQuery とその他の Google データ分析プロダクトの使用を開始するには、無料トライアル（https://cloud.google.com/free）をご利用ください。また、Google Cloud のサンドボックスで BigQuery を無料でお試しいただけます。

-Google Cloud プロダクトマネージャー Jagan R. Athreya

Posted in: