2022-01-17

BigQuery 2021/12月のupdate情報まとめ

GCP

新年一発目のブログとなります！皆様今年もよろしくお願いしますmm

それでは先月までに引き続き12月のまとめもいってみましょう！

nii-yan.hatenablog.com

BI EngineがSQLに対応

previewで出ていましたが、GAになりました！

WebUIからのクエリもBI Engineを使って高速化することが可能になりました。
BI Engineという名前からするとWebUIで使えるのは少し違和感ですね。
現状BI Engineは

DataStudio
Looker
Tableau
SQL

に対応しています。

実際に走ったクエリがBI Engineで動いたかどうかはBI Engineを有効にしてあるプロジェクトでしたらJob詳細の biEngineMode から確認出来ます。

行レベルアクセステーブルの過去データに対して管理者閲覧が可能に

テーブルに行レベルのアクセスポリシーがあるか、すでに設定されている場合は、テーブル管理者のみがテーブルの過去のデータにアクセスできるようになりました。
(リリース前は管理者でもアクセス出来なかったのか、誰でもアクセス出来たのか確認出来ていません)

12月のリリースはこちらの2件のみでした。Googleのエンジニアも12月はもう年末モードですかね？
1月のリリースはJSON型など待望の機能も出ているようなので、また来月まとめたいと思います！！

2021-12-17

子連れキャンプに行くようになって追加で買ったもの

育児キャンプ

キャンプAdventCalendar 17日目の記事となります。

adventar.org

イクジニア(育児 + エンジニア)といいながら育児関連のことを全然書いてなかったので久々に・・・

今年は子連れキャンプに数回行かせてもらい、課題が見えるたびに道具を買い続けたので買ってよかったものをいくつか紹介させていただければと思っています!!

ハンモック

最初から遊び道具！？って感じですがこちら買って本当に良かったです
4歳6歳の2人の子供を連れてキャンプ行ってるのですが、キャンプ場に遊具がないとタープ貼ったりテント立てたりしている間に子供が飽きてしまうとか、しっかりと見ていられないので気づいたらいなくなっているとかがあるかなと思います。
(うちのタープは大人2人がかりでないと建てられないタープだという事情もありますが・・・)

ハンモックがあるだけで、子供は喜んで遊んでくれますしその間に大人は設営作業や料理の準備に集中出来ます。
今回買ったハンモックはこちらです
ハンモックとして使い終わった後はハンガーになるので、ランタンなどを吊るしたりも出来ます

[商品価格に関しましては、リンクが作成された時点と現時点で情報が変更されている場合がございます。]

【ポイント5倍 19日20:00〜23:59】【送料無料】ハンモック自立式ゆらふわモックマルチタイプ　チェアー椅子部屋干し簡単設置専用バッグ付きアウトドアギアスタンドネットシエスタゆらふあハンモックキャンプ
価格：12800円（税込、送料別) (2021/12/17時点)

楽天で購入

ゴムハンマー

ハンモックで遊んでいてもらっても、大人がペグを打っていると子供がやりたがります。
その際に自分がペグを持ちながら、子供にペグハンマーで打ってもらったところ手を強打されましたToT いや〜めっちゃ痛かった・・・
だからといって子供にやらせないのもせっかくキャンプに来ているのでもったいないと思いゴムハンマーを購入しました。
こちらでもやらかい地面であればペグが打てますし、なにより手を打たれても(そこまでは)痛くありません！！
兄弟で喧嘩にならないように2つ購入しました。ダイソーやセリアなどの100均で購入出来ます。

黒ゴムパイプハンマー８オンスＫ４５jp.daisonet.com

焚き火台テーブル

キャンプの醍醐味はなんと行っても焚き火だと思っています。
焚き火楽しいですよね！！
ですが、好奇心旺盛な子供は焚き火に近づき過ぎてしまい、結構危ないことが多かったので子供のガードのためにもと焚き火台テーブルを購入しました。

結果夕飯時にはみんなで焚き火を囲んでその場で焚き火を使って調理したものを食べられるし、焚き火に近づく危険性も減ったので大変よかったです。

今回購入した焚き火台テーブルは尾上製作所のものを購入しました。
焚き火台はLOGOSの the ピラミッドTAKIBI L を使っていますが、大きさ的にも丁度いい大きさでした。

ガイドロープランプ

キャンプ中に(特に夜間)子供がガイドロープにひっかかって転ぶということが何回かありました。
まあ子供は転ぶものですし、よいのですが運悪く転んだ先にペグなどがあったばあいは大怪我に繋がりかねません。
とくにうちのテントの場合は出口のところにガイドロープがあるタイプなので、夜のトイレなどで大人も転びそうになりました。

そのような危険性を回避するためにガイドロープにつけられるランプを購入しました。
最近はダイソーやセリアでも１つ100円で売ってるので、ためしにいくつか買ってみるのも良いかなと思います。

ガイドロープで転ぶ危険性もなくなりますし、サイトもおしゃれになりますし一石二鳥なアイテムです!!!

マット

最初はそれぞれ個別のマットを買ったのですが、子供1人に対して大人用マット1枚だと大きいし、2枚連結すると間が気になるし・・・ということで、ダブルサイズのマットを購入しました！！

今回購入したのは「コールマン(Coleman) エアーマットキャンパーインフレーターマットハイピークダブル」です
こちらですと厚さも10cmほどあり、ほぼベッドと同じような感覚で快適に寝ることが出来ます。また子供2人と大人1人でダブルサイズのシェラフにくるまってちょうど良いサイズでもあります。

(妻が子供と寝るので私は過去に買ったシングルマットを使っています)
マットに直接寝ると汚れが気になったので丁度良い大きさのカバーをつけて使っています。

他にも最近は調理器具なども購入しているのですが、こちらはまた次回紹介させていただけたらと思います。

2021-12-14

BigQuery 2021/11月のupdate情報まとめ

GCP

一気に寒くなって来ましたね。

歳のせいか、寒さに弱くなってきており、去年までよく毎日会社に通えていたな？と思っています。
リモートありがたい。

それでは先月までに引き続き11月のまとめもいってみましょう！

nii-yan.hatenablog.com

パラメータ化されたデータ型

すでにpreviewで出ていた機能がGAになりました

STRING/BYTES/NUMERIC/BIGNUMERIC型を宣言する際に桁数を指定出来るようになりました。

クレジットカードなど桁数が決まっている場合や、他のDBへのマイグレーション時には桁数を指定することで便利に使えそうです。

INFORMATION_SCHEMAでDDLが確認可能になりました

以下のDDLがINFORMATION_SCHEMAで確認可能になりました

INFORMATION_SCHEMA.ROUTINES
UDFとプロシージャのDDL
INFORMATION_SCHEMA.SCHEMATA
DatasetのDDL
INFORMATION_SCHEMA.TABLES
TableやviewなどのDDL

複数のスクリプト ステートメントが追加されました

CASE：条件を満たすステートメントを実行
CASE search_expression：search_expressionで指定した値に対応するステートメントを実行

f:id:nii_yan:20211214132518p:plain

LABELS：labelで指定した場所に戻ることが出来る
BEGIN句/WHILE句/LOOP句/FOR句/REPEAT句で使用可能
REPEAT：UNTILで指定した条件を満たすまで繰り返す
FOR...IN：テーブルの各行について処理を実行する

以下の例の場合、select word...の結果で得た５行に対してLoopを行い各行に対してselectを行っている
結果として5行変えるのではなく、5回selectが行われる f:id:nii_yan:20211214132903p:plain

承認済みDataset

承認済みviewのdataset版
承認済みdataset配下に作られたviewに対しては承認済みviewと同じように元テーブルに対するアクセス権がなくても参照可能に

11月のアップデートは以上となります。

BigQueryの話題ではないですがレビュワーとして少しだけ関わらせていただいた本が出版されました！
基盤自体の作り方から、データの整備の仕方、組織論まで網羅された本になりますので、よかったら読んでみてください

また来月！！

2021-12-07

The Self-Service Data Roadmap について

この記事は、datatech-jp Advent Calendar 2021の7日目の記事となります。

The Self-Service Data Roadmapという本がとても評判良さそうで、読んでみたいけど英語版しかないので1人で読むのは厳しいなぁ・・・って思っていたところ、Twitterから発展してあれよあれよと輪読会が開催されることになりました。 amzn.to

輪読会は現在はdatatech-jpというコミュニティに発展をしています。

詳細は@syou6162さんがブログに書いてくださっているのでこちらを参考にしてください。

www.yasuhisay.info

The Self-Service Data Roadmapの輪読会で私が担当した章は最初のIntroだったので、この本の全体感をここでは説明させてもらいます。
それぞの章の細かい内容は別の日のAdventCalendarでも触れてくれる方がいらっしゃる予定です。

The Self-Service Data Roadmapで述べられている内容は、タイトルの通り、Selfでデータを価値に変換出来るような基盤を作っていくにはどのようにしたら良いかが書かれています。

また、出てくるアーキテクチャの内容としては著者の関係もあるのか、Hadoopエコシステムに少し偏っていますが抽象化して考えればHadoop以外にも適用出来るものが多いです。

まず最初に3つの失敗するパターンが書かれています。

データエンジニアとデータユーザの食い違い
新しい技術に飛びつく
全てに取り組む

データエンジニアの認識とデータユーザの認識齟齬とかどこの組織でもあるあるなのかなと思います。

このような失敗パターンがある上で、ではどのようにSelf-Service基盤を作っていけば良いかというと

まず全体を4つの工程に分けます

f:id:nii_yan:20211206204636p:plain

その上でさらに工程毎に細かく分けていきます

f:id:nii_yan:20211206204720p:plain

細かく分けることが出来たらそれぞれどれぐらい最終的にインサイトを出すために時間を掛かってるか見積もりします

f:id:nii_yan:20211206204834p:plain

もっとも時間のかかっている2~3メトリクスを特定/分析し、自動化までのロードマップを作成しましょう！！！

という流れです。

それぞれの time to XXX というメトリクスの詳細と改善方法はそれぞれ章毎に案内がされています。
なので全体を読まなくても例えばコンプライアンスに関して知りたい！でしたら、 time to comply の章だけを読むという読み方でも良さそうでした。

中には こんな夢のような基盤本当に世の中に実現するのか！？ というような基盤の説明もありましたが、どこの章でも共通して書いてあったのは

銀の弾丸はない

でした。これを解決すればよい！というのはやはりなく、複合的に少しずつ解決していくのが遠回りなようでSelf-serviceへの近道なのかもしれません。

私が作成した資料はこちらですので興味ある方は是非とも読んでみてくださいmm

Advent Calendar 明日は@nana7dataさんです。よろしくお願いします！

2021-11-15

BigQuery 2021/10月のupdate情報まとめ

GCP

Google Cloud Nextでわくわくする機能が発表されましたね！！！

発表内容は以下の動画にまとまっています

www.youtube.com

まだリリースされていない主な発表としては以下のようなものなどがあります。

BQからCloudFunctionsを呼べるように
SlotのAutoScaling
テキスト検索インデックス

発表された機能も含めて、それでは先月までに引き続き10月のまとめもいってみましょう！

nii-yan.hatenablog.com

地理関数が追加

先月に引き続き地理関数の追加です

関数名	内容
ST_BUFFER	Returns a GEOGRAPHY that represents the buffer around the input GEOGRAPHY.
ST_BUFFERWITHTOLERANCE	Returns a GEOGRAPHY that represents the buffer around the input GEOGRAPHY.

BigQueryStorageAPIの料金体系が変更になりました

networkのegress料金など、一部料金が変更になりました。詳しい価格はこちらから確認をお願いします。

他のDWHからのマイグレーションが可能に

BigQuery Migration Serviceとして、他のDWHからのMigrationがサポートされました！(現在対応のDWHはTeradataのみ)
データの移動はDataTransferServiceを使って行い、SQLの書き換えも行ってもらえるように！
SQLの書き換えはバッチモードとInteractiveモードがあり、InteractiveモードではBQコンソール上でSQLの変換が可能となりました。

f:id:nii_yan:20211115202847p:plain f:id:nii_yan:20211115202911p:plain

StorageAPIがWriteにも対応

StorageAPIでWriteも出来るようになりました。

Readと同じように、より高スループットでの書き込みを行うことが出来ます。

Writeのモードは３つあります

Committed mode

書き込み後すぐに読み込みが可能。streaming処理向け

Pending mode

stream上にデータを貯めておき、commitのタイミングで書き込みが可能。comittされたタイミングですべてのデータが書き込みされる。 commitするまでデータは書き込まれないので、エラー時の再実行も可能。バッチ処理向け

Buffered mode

stream上のデータを任意のタイミングでflush出来る。
基本的にCommitted modeか Pending modeを使用すれば良いと思われるが、データの細かい制御をしたい場合向けだと思われる。

それぞれのサンプルコードはこちらを参照

料金もストリーミングinsertの50%ほどとお安くなっています。

AWSやAzureのデータをBigQueryで読み込み可能に

ついにBigQuery Omniがリリースされました！(しかもGA)

こちらは別途検証をしたいと思いますが、概要としてはS3のデータを指定してBigQueryからクエリが出来ます。
BigQueryのクラスタはAWSまたはAzure上に構築されますが、ユーザは管理を必要としません。

必要な権限をあたえ、コンソールからデータソースにS3を選べばS3のデータに対してBQからクエリをすることが出来ます！

f:id:nii_yan:20211115204007p:plain

検索結果をS3にexportすることも可能です

AzureのBlobStorageに対しても使用可能とdocにはなっているのですが、私の環境ではまだコンソールにAzureの選択肢は出てきませんでした。

現状、S3のリージョンや、データのjoinなど制限事項も色々あるようですので別途検証をしたいと思っています！

カラムレベルでの暗号化が可能に

CloudKMSを使用してカラムレベルでの暗号化が可能となりました。
暗号化したカラムを読み込む際はクエリ内で、複合関数の使用が必要です。
以前からカラムレベルの権限制御は出来ましたが、暗号化も可能となったことでセキュリティのオプションが増えたと思われます。

テーブルのスナップショット

6月のupdate情報まとめで紹介したテーブルスナップショットがGAとなりました！！

10月のアップデートは以上となります。
また来月！！

2021-10-13

BigQuery 2021/9月のupdate情報まとめ

GCP

Google Cloud Nextが始まって続々とBQの新機能が発表されてますね！ Nextで發表された内容のまとめは来月書きます

それでは先月までに引き続き9月のまとめもいってみましょう！

nii-yan.hatenablog.com

JOB履歴の削除が可能に

BQでは過去6ヶ月分のJOB履歴を持ちますが、JOB履歴の削除が可能になりました。(悪用しちゃだめですよ

bq rm -j ‘プロジェクト名:ロケーション.job_id’

where句に個人情報を入れてしまった場合などセンシティブデータを消したい場合に有効
メタデータ(INFORMATION_SCHEMAでの参照データなど)は消えますが、ログからは消えません

セッションが利用可能に

WebUIからセッションが使えるようになり、トランザクション(begin/commit/rollback)が利用可能に
本番作業を安心に出来るようになりました

変数や一時テーブルをセッション内で使いまわすことも可能
セッションを使うかはQuerySettingsから指定が出来ます f:id:nii_yan:20211013212409p:plain

ブラウザのタブ毎に別のセッションを貼ることが可能
2つのセッションでDeadlockがかかるような処理を行った場合はupdateをしようとした時点でエラーが出ます
セッション中にエラーが起こってもrollbackやcommitはされません f:id:nii_yan:20211013212724p:plain

トランザクションレベルはSnapshot Isolationとなっており、トランザクションを開始した時点のスナップショットデータが読み込まれます。(ファジーリード/ファントムリードは起きない)

また、セッションは手動で終わらせることは出来ません。
24時間操作しないか、7日立つと自動的に終了し、commitしてない場合はrollbackされます。

多くの地理関数が利用可能に

以下の地理関数が利用可能になりました

関数名	内容
ST_EXTERIORRING	Returns a linestring geography that corresponds to the outermost ring of a polygon geography.
ST_INTERIORRINGS	Returns an array of linestring geographies that corresponds to the interior rings of a polygon geography.
ST_ANGLE	Returns the angle between two intersecting lines.
ST_AZIMUTH	Returns the azimuth of a line segment formed by two points.
ST_NUMGEOMETRIES	Returns the number of geometries in a geography.
ST_GEOMETRYTYPE	Returns the Open Geospatial Consortium (OGC) geometry type that describes a geography as a string.
ST_BOUNDINGBOX	Returns a STRUCT that represents the bounding box for a geography.
ST_EXTENT	Returns a STRUCT that represents the bounding box for a set of geographies.
S2_COVERINGCELLIDS	Returns an array of S2 cell IDs that cover a geography.
S2_CELLIDFROMPOINT	Returns the S2 cell ID covering a point geography.

クエリ結果のダウンロード上限が変更

クエリの結果をローカルにダウンロードする際の上限が10MBになりました。
(今までは16,000行 or 10MBだったと思う)

加えてnestedカラムや配列カラムもCSVにダウンロード可能になりました。
(ダウンロードするとカラムの中身はJSONでJSONのCSVとなってます)

承認済みTable関数

承認済みviewのTable関数版です
関数内で参照しているテーブルに対しての閲覧権限がなくても関数の権限があればデータの参照が可能

9月のアップデートは以上となります。
GoogleCloudNext前だからか、少なかったですね。また来月！！

2021-09-09

BigQuery 2021/8月のupdate情報まとめ

GCP

先月までに引き続き8月のまとめもいってみましょう！

nii-yan.hatenablog.com

ODBC/JDBC driverのバージョンアップ

エンハンス、bugfixなどが含まれています。

CREATE TABLE LIKE

既存のテーブルからスキーマ情報をコピーしてテーブルを作成します
(データのコピーはされない)
カラムのdescriptionなど、メタデータのコピーもされます

{CREATE TABLE | CREATE TABLE IF NOT EXISTS | CREATE OR REPLACE TABLE}
[[project_name.]dataset_name.]table_name
LIKE [[project_name.]dataset_name.]source_table_name
[PARTITION BY partition_expression]
[CLUSTER BY clustering_column_list]
[OPTIONS(table_option_list)]
[AS query_statement]

CREATE TABLE COPY

既存のテーブルと同じスキーマ、データを持つテーブルを作成します
メタデータのコピーもされます

{CREATE TABLE | CREATE TABLE IF NOT EXISTS | CREATE OR REPLACE TABLE}
[[project_name.]dataset_name.]table_name
COPY [[project_name.]dataset_name.]source_table_name
[OPTIONS(table_option_list)]

カラムの型変更が可能に

ALTER COLUMN SET DATA TYPEを使って、カラムの型変更が可能になりました

どの型にでも変更出来るわけではなく、INT64からBIGNUMERICなどより制約のゆるい型への変更のみ可能です

INT64からSTRINGの型変換も出来ませんでした

注意書きとして、型を変更するテーブルがマテリアライズドビューから参照されている場合は、マテリアライズドビューに対するクエリがエラーとなってしまうと書かれています
この問題を回避するには一度マテリアライズドビューを削除し作り直す必要があります

ALTER TABLE [IF EXISTS] [[project_name.]dataset_name.]table_name
ALTER COLUMN [IF EXISTS] column_name SET DATA TYPE data_type

Spannerへの外部クエリが可能になりました

EXTERNAL_QUERY関数を使用して、BQからSpannerへのクエリが可能になりました
BQに対応していない型がある場合はクエリが失敗します
BQが単一リージョンだった場合は同じリージョンにあるSpannerにのみクエリが可能
また、spannerへのクエリは読み取り専用です

BQから直接クエリ出来るサービスとしては他に

GCS
CloudSQL
スプレッドシートなどがあります

SELECT c.customer_id, c.name, rq.first_order_date
FROM mydataset.customers AS c
LEFT OUTER JOIN EXTERNAL_QUERY(
  'my-project.us.example-db',
  '''SELECT customer_id, MIN(order_date) AS first_order_date
  FROM orders
  GROUP BY customer_id''') AS rq ON rq.customer_id = c.customer_id
GROUP BY c.customer_id, c.name, rq.first_order_date;