ユーザーを笑顔にする BigQuery の使いやすい SQL 新機能
Google Cloud Japan Team
※この投稿は米国時間 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 という名前のテーブルに加えて、ग्राहक、お客様、顾客、заказчик、الزبون などと命名されたテーブルに顧客データを格納できます。ドキュメント
外部ストレージの読み取りと書き込み
ユーザーは BigQuery を使って外部ストレージ バケット内のデータを読み取ることができます。新しい SQL コマンドなら、BigQuery 内でこうしたバケットを指定するようにテーブルを構成し、クエリから取得したデータをストレージ バケットにエクスポートすることも可能です。
CREATE OR REPLACE EXTERNAL TABLE: BigQuery を使用すると、Google Cloud Storage（GCS）バケットのデータを BigQuery 内の外部テーブルとして登録することでクエリを実行できます。CREATE EXTERNAL TABLE コマンドを実行して、BigQuery で SQL を使ってこうした外部データソースのためのテーブル定義を作成できるようになりました。外部データが Hive パーティショニングで分割されている場合、DDL の一部として任意の Hive パーティション URI プレフィックスを使ってパーティショニング スキームを指定できます。ドキュメント
DROP EXTERNAL TABLE: 外部テーブルのテーブル定義は、DROP EXTERNAL TABLE コマンドを実行して削除できます。削除されるのは BigQuery 内のテーブル定義のみで、ストレージ バケットに格納されているデータは削除されないことにご注意ください。また、外部テーブルの削除には EXTERNAL プレフィックスが必要です。DROP TABLE table_name（EXTERNAL プレフィックスなし）が有効なのは通常の BigQuery テーブルに対してのみで、外部テーブルで実行しようとすると失敗します。ドキュメント
EXPORT DATA: データのパイプライン オペレーションの一環として、分析クエリの出力結果を外部ストレージにエクスポートして、結果を他の関係者と共有し、データ サイエンスやデータレイクなどの継続的なデータ パイプラインにフィードしたいという BigQuery ユーザーからの要望があります。コンソール UI やスクリプトで EXPORT DATA コマンドを実行して、BigQuery がサポートしているデータ形式と圧縮形式でクエリの結果を Google Cloud Storage バケットに保存できるようになりました。ドキュメント
スクリプティングと SQL の改善
SQL でサポートされているスクリプティングとストアド プロシージャに基づいて、BigQuery では動的 SQL、承認済みユーザー定義関数、SQL 言語の改善が導入されています。
EXECUTE IMMEDIATE: 動的 SQL は SQL のスクリプティングで使用できる強力な手法で、SQL を使って動的に SQL を生成して実行できます。EXECUTE IMMEDIATE は静的文字列と変数の組み合わせの式を受け取り、有効な SQL ステートメントをその場ですぐに生成して即座に実行します。セキュリティ上の理由から、1 回の EXECUTE IMMEDIATE の呼び出しごとに実行できる SQL ステートメントは 1 つだけです。ドキュメント
承認済みユーザー定義関数（UDF）: 承認済み UDF は、特定のデータセットへのアクセスが有効になるよう IAM 権限を使用して承認されたユーザー定義関数です。UDF はデータセット内のテーブルに対して、UDF を呼び出しているユーザーにテーブルへのアクセス権がない場合でもクエリを実行できます。ドキュメント
クエリ結果内の重複する列名: 従来の BigQuery では、クエリ結果内の 2 つの列に同じ名前を使用できませんでした。そのため、アナリストは検索するクエリを手動で編集して、重複している列名を変更する必要がありました。現在は、BigQuery で同じ列名を使ってクエリを実行できるようになりました。BigQuery は自動的に数字のサフィックス（_n）を追加して、重複する列を区別します。ドキュメント
新しい DATE 関数
BigQuery に、ビジネス プロセスに必要とされる日付演算を行う新しい関数が導入されます。
DATE ± n（日数）: BigQuery で日数を加算または減算するといった DATE の算術演算は、従来サポートされていた DATE_ADD 関数と DATE_SUB 関数を使って特定の日付に対して日数を加減していました。現在はシンプルな算術演算（+ または -）を使って日数を加減できるようになりました（HIRE_DATE+7、DELIVERY_DATE–2 など）。ドキュメント
LAST_DAY: この関数は、指定された日付表現の時間単位における最後の日付を返します（その日付が属する月や年の最後の日など）。ドキュメント
新しい文字列関数
BigQuery には、文字列データを抽出および操作して検索やテキスト マッチングを有効化するための新しい関数も導入されます。
連結演算子（“||”）のサポート: 従来の BigQuery の連結演算子には、複数の文字列を連結するために CONCAT 関数を使用する必要がありました。現在は連結演算子 || を使って複数の文字列を連結できるようになりました。「ABC」という組み合わせは “A”||“B”||“C” だけで済みます。ドキュメント
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 と同義で、文字列値の長さをバイト数で返します。ドキュメント
拡張された INFORMATION_SCHEMA ビュー
ANSI SQL 標準で指定されている INFORMATION_SCHEMA により、ユーザーは参照データを含むさまざまなオブジェクト（テーブル、ビュー、プログラムなど）やエンティティ（ストアド プロシージャやユーザー定義関数など）に関するメタデータをクエリできます。
テーブルの情報スキーマ: INFORMATION_SCHEMA の TABLES ビュー、TABLE_OPTIONS ビュー、COLUMNS ビュー、COLUMN_FIELD_PATHS ビューからテーブルのメタデータを検索できるようになりました。
ビューの情報スキーマ: INFORMATION_SCHEMA の VIEWS を使って、ビューのメタデータを検索できます。
ルーティンの情報スキーマ: ルーティンのメタデータは、INFORMATION_SCHEMA の ROUTINES ビュー、ROUTINE_OPTIONS ビュー、PARAMETERS ビューに含まれています。
データセットの情報スキーマ: データセット情報は、INFORMATION_SCHEMA の SCHEMATA ビューと SCHEMATA_OPTIONS ビューに含まれています。
これらの SQL の新機能によって、BigQuery ユーザーの皆様が笑顔になることを願っています。BigQuery とその他の Google データ分析プロダクトの使用を開始するには、無料トライアル（https://cloud.google.com/free）をご利用ください。また、Google Cloud のサンドボックスで BigQuery を無料でお試しいただけます。
-Google Cloud プロダクト マネージャー Jagan R. Athreya
