BigQuery のユーザー フレンドリーな SQL: 分析、データ品質、セキュリティの向上
Google Cloud Japan Team
※この投稿は米国時間 2023 年 9 月 15 日に、Google Cloud blog に投稿されたものの抄訳です。
SQL は、世界中で約 700 万人がデータを管理し分析するために日常的に使用しています。データ エンジニアであれ、アナリストであれ、ビジネス主導の分析情報を提供するためにデータをどのように管理し、効率的に使用するかが、これまで以上に重要になっています。
BigQuery は業界をリードするフルマネージドのクラウド データ ウェアハウスであり、エンドツーエンドの分析エクスペリエンスを簡素化できます。データの取り込みから、準備、分析、そして SQL を使用した ML のトレーニングや推論に至ります。このたび、データ品質、セキュリティ、柔軟性のサポートを拡大する新しい SQL 機能を BigQuery に追加しました。新機能は次のとおりです。
- データ品質向上のためのスキーマ オペレーション: 列の説明を指定してビューを作成 / 変更、柔軟な列名、LOAD DATA SQL ステートメント
- 安全な方法でのデータの共有と管理: 承認済みのストアド プロシージャ
- 柔軟なデータ分析: LIKE ANY/SOME/ALL、ANY_VALUE (HAVING)、配列と構造体のインデックスのサポート
データ品質向上のためのスキーマのサポート拡大
ここでは、BigQuery のスキーマのサポートをどのように拡大してデータを簡単に扱えるようにしたのかについて概説します。
列の説明を指定してビューを作成 / 変更（プレビュー）
お客様から、ビューを使用して他の人にデータのアクセス権を付与することが多いという声や、列に何が含まれているのかについての詳細な情報を提供する機能があれば非常に助かるという声が寄せられています。テーブルの列の説明と同様に、同じ機能をビューにも拡張しました。Terraform を利用してビューを事前に作成し、列の詳細を入力するのではなく、列の説明を指定したビューの CREATE / ALTER ステートメントを使用して、ビューで列の説明を直接作成、変更できるようになりました。
柔軟な列名（プレビュー）
データ アクセシビリティとユーザビリティを向上させるために、BigQuery では、列名に好みの国際言語を使用したり、アンパサンド（&）やパーセント記号（%）のような特殊文字を使用したりといった柔軟性がサポートされるようになりました。移行ニーズや国際的なビジネスデータがあるお客様にとって、これは特に重要です。サポートされる特殊文字の一部を以下に示します。
- あらゆる言語のあらゆる文字
- あらゆる言語のあらゆる数字
- あらゆる連結用句読記号文字
- あらゆる種類のハイフンまたはダッシュ
- 別の文字と組み合わせることを意図したあらゆる文字
- 列名の例:
- `0col1`
- `姓名`
- `int-col`
サポートされている文字の詳細な一覧については、こちらをご覧ください。
LOAD DATA SQL ステートメント（一般提供）
- 「以前は主に読み込み API を使用して BigQuery にデータを読み込んでいましたが、API について学び、構成を行うには、エンジニアの専門知識が必要でした。LOAD DATA がリリースされてからは SQL ステートメントのみでデータを読み込めるようになり、シンプルさとコンパクトさが増し、便利になりました。」- Northbeam データ エンジニアリング担当ディレクター Steven Yampolsky 氏
BigQuery ユーザーは、読み込み API や CLI を使用するよりも、SQL データ パイプラインの一部としてデータを読み込むための SQL インターフェースの互換性と利便性を好みます。BigQuery へのデータの読み込みをさらに簡単にするために、いくつかの新しいユースケースのサポートを拡大しました。
柔軟な列名でデータを読み込む（プレビュー）
- 列の名前を変更したテーブル、または短時間で列を削除して追加したテーブルに読み込む
- 取り込み時間でパーティション分割したテーブルにデータを読み込む
- 選択したパーティションにデータを読み込むか上書きする
安全な方法でのデータの共有と管理
承認済みのストアド プロシージャ（プレビュー）
ストアド プロシージャは、他のクエリから呼び出すことができるステートメントの集合です。ストアド プロシージャからのクエリ結果を、基となるテーブルの読み取りアクセス権を付与せずに特定のユーザーと共有する必要がある場合、新しく導入された承認済みのストアド プロシージャで便利かつ安全にデータアクセスを共有できます。
仕組み
- データ エンジニアが特定のクエリを作成し、特定のアナリスト グループに対して承認済みのストアド プロシージャに関する権限を付与することで、基となるテーブルの読み取り権限なしでクエリを実行して結果を確認できる。
- アナリストが承認済みのストアド プロシージャを使用して、クエリ エンティティ（テーブル、ビュー、UDF など）の作成、プロシージャの呼び出し、DML オペレーションを行える。
柔軟にデータを分析するためのサポートの拡大
LIKE ANY / SOME / ALL（プレビュー）
アナリストは、文字列の列に格納されたビジネス情報（顧客名、レビュー、インベントリ名など）を検索する必要が頻繁に生じます。LIKE ANY / LIKE ALL を使用して、1 つのステートメントで複数のパターンをチェックできるようになりました。LIKE 演算子と WHERE 句を組み合わせて複数のクエリを使用する必要はありません。
新しく導入された LIKE 修飾子 ANY / SOME / ALL を使用すると、指定したパターンのいずれかまたはすべてに一致するフィールドの行をフィルタできます。そのため、アナリストが効率的にデータをフィルタし、検索条件に基づいて分析情報を生成できます。
LIKE ANY（LIKE SOME と同義）: 指定した 1 つまたは複数のパターンのいずれかに一致するフィールドの行をフィルタできます。
LIKE ALL: 指定したパターンすべてに一致するフィールドの行をフィルタできます
ANY_VALUE (HAVING MAX | MIN)（一般提供）
お客様が同じ行にある別の列の最大値や最小値に関連する値をクエリすることはよくあります（ベストセラー商品の SKU の検索など）。以前は、array_agg() と order by () を組み合わせるか、ウィンドウ関数で last_value() を使用して結果を取得する必要があったため、特にレコードが重複している場合は複雑で非効率でした。
ANY_VALUE(x HAVING MAX/MIN y) と、同義語の MAX_BY、MIN_BY を使用すると、わかりやすく読みやすい SQL ステートメントで他の列の最大値や最小値に関連する列を簡単にクエリできます。
例: 各顧客の最新の契約額を調べる
配列と構造体のインデックスのサポート（一般提供）
配列は、同じデータ型の値の順序付きリストです。現在、配列の要素にアクセスするには、0 ベースのインデックス（0 から数える）には OFFSET(index) を使用し、1 ベースのインデックス（1 から数える）には ORDINAL(index) を使用しています。さらに簡潔にするために、BigQuery では a[OFFSET(n)] の同義語として a[n] がサポートされるようになりました。そのため、こうした配列のインデックスのアクセス規則にすでに慣れているユーザーにとっては簡単になります。
構造体は、さまざまなデータ型のフィールドの順序付きタプルを表すデータ型です。現在、匿名のフィールドがある場合、またはフィールド名が重複している場合、フィールド値にアクセスすることは困難です。配列と同様に、0 ベースのインデックス（0 から数える）の OFFSET(index) と 1 ベースのインデックス（1 から数える）のORDINAL(index) を導入しました。このインデックスのサポートにより、構造体の選択位置にあるフィールドの値を簡単に取得できます。
一般提供を開始したその他の BigQuery 機能
最後に、最近複数の BigQuery 機能がプレビューから一般提供になり、Google Cloud で完全にサポートされるようになりました。たとえば次のようなものです。
列の削除 / 列名の変更
列を削除する場合や列名を変更する場合はすでに、費用のかからないメタデータのみのコマンドである DROP COLUMN または RENAME COLUMN を実行できていました。
一般提供になり、テーブルのコピーとコピージョブのサポートがさらに拡大されました。以前に列名を変更したか列を削除したテーブルがある場合、CREATE TABLE COPY ステートメントを使用してそのテーブルのコピーを作成するか、最新のスキーマ情報を使用してコピージョブを実行することができるようになりました。
大文字と小文字を区別しない文字列の照合順序
現在、「und:ci」を指定することで大文字と小文字の区別に関係なく文字列を比較したり並べ替えたりできています。つまり、文字列値のオペレーションにおいて、[A,a] は同等の文字として扱われ、[B. b] に先行します。一般提供になり、このサポートが集計関数（MIN、MAX、COUNT DISTINCT）、ビューの作成、マテリアライズド ビュー、BI エンジン、その他多数に拡大されました。詳しくはこちらをご覧ください。
次のステップ
Google は引き続き、BigQuery でのデータの読み込み、分析、管理を最も効率よく行える、ユーザー フレンドリーな SQL 機能の構築に注力していきます。こうした機能を普段の業務でどのように使用する予定か、お聞かせください。使用したい具体的な SQL 機能があれば、こちらから機能リクエストを提出してください。まずは無料で BigQuery をお試しください。
ー BigQuery プロダクト マネージャー Candice Chen
ー BigQuery エンジニアリング マネージャー Fang Liu
