月: 2019年4月

　やっとクエリの準備ができました！
と、いうことで、どんな話をしようとしていたかというと、リソースに開始日と終了日を持つと、ある期間に有効だったデータを取得するのが難しくなるかどうか、という議論です。そんなデータの持ち方したら、複雑になってしまうのでダメだというご指摘を受けたことがあって、意外と簡単になりますよ～、という話です。
　先ほどのデータ、ビジュアル的に表すと以下のようになります。2001年1月1日から2004年12月31日の期間に販売されていた書籍を取り出すとどうなるでしょうか。

• 

このような有効期間のある問い合わせの場合、考えなければならないバターンを単純化すると、以下のように、６パターンになります。

• 

ここで、StartからEndの範囲内で有効だったリソースは、②、④、⑤、⑥の４種類となります。まじめにやると、

②（Start ≦ 開始日 AND 終了日 ≦ End）
　　　　　　　　OR
④（開始日 ≦ Start AND End ≦ 終了日）
　　　　　　　　OR
⑤（開始日 ≦ Start AND Start ≦ 終了日）
　　　　　　　　OR
⑥（開始日 ≦ End AND End ≦ 終了日）

と、いうことになります。確かにこれは手間だし、複雑になるのが嫌だという抵抗も、もっともかもしれません。これをSQLで表現すると、以下のようになります。

SELECT BookNo, BookName
  FROM BOOKS.BOOKS
 WHERE ('2001-01-01' <= StartDate AND EndDate <= '2004-12-31') 
    OR (StartDate <= '2001-01-01' AND '2004-12-31' <= EndDate)
    OR (StartDate <= '2001-01-01' AND '2001-01-01' <= EndDate)
    OR (StartDate <= '2004-12-31' AND '2004-12-31' <= EndDate)

BigQueryで実行してみると予想通り、ちゃんと6件取得できました。

• 

　それでは、お待ちかねの改案です。実は、この問い合わせ、上記の①と③以外を検索する、と読み替えられます。

NOT（　①（終了日　＜　Start）　OR　③（End　＜　開始日）　）

意外とシンプルですね。SQLで表現すると以下の通りです。結果も変わりませんね。

SELECT BookNo, BookName
  FROM BOOKS.BOOKS
 WHERE NOT (EndDate < '2001-01-01' OR '2004-12-31' < StartDate)

• 

ド・モルガンの法則で書き換えて整形すると以下のようになります。さらにシンプルになりました。結果も同じになりました。

SELECT BookNo, BookName
  FROM BOOKS.BOOKS
 WHERE '2001-01-01' <= EndDate AND StartDate <= '2004-12-31'

• 

　ある四角形の領域に重なる線分を探す、という問題を見つけましたが、今回のケースと同じ問題として考えられますので、興味のある方はチャレンジしてみてください。

BigQueryまずは使ってみましたが、実際の業務で使っていくためには、もう少し調査が必要ですね。

• CREATE TABLE文などのスクリプトでテーブル作成するやり方
• 利用可能なデータ型
• NO以外のカラム名として利用できない予約語
• オンプレミスのデータをBigQueryへ読み込むやり方

あと、上の画像のクエリ結果のところにある「データポータルで調べる」が気になります！ユーザーにも使えるようなものなのかどうか、後ほど調べてみます。
今回はここまでです。

　先日の続きです。
空のテーブルはできましたが、データの入れ方がわかりません。RDBならINSERT文ですよね、ということでお試しでINSERT文を書いてみました。

• 

何と、エラーが出ました。「VALUES」のリストかクエリが想定されているんだけど、違いますよ～、と、言われていますね。完全に当てずっぽうで書いてみたのですけど、行けましたねぇ。続きを書いてみます。文字列の表現はシングルクォーテーションでいいのか、日付はどうやって表現するのか分からないけど、とりあえずの形で「INSERT INTO BOOKS VALUES (1, ‘CASEツール ‘, 1989-11-01, 1999-11-01);」を書いてみたところ、やはりエラーが。。。「Table name “BOOKS” cannot be resolved: dataset name is missing.」ということで、データセットの名前が必要ということですね。。。いやいや、これはいつ終わるかわからないパターンですよ。
　と、いうことでまじめにやりましょう。こういう時はマニュアルを参照します。右上の方にアイコンがありましたよ！

• 

ヘルプをクリックしたら、「人気の記事」一覧が表示されましたので、その中の「BigQuery」を選んでみました。すると、いろいろ書いてありますが、、、「よく参照されるトピック」の中に、「BigQueryへのデータの読み込み」がありました。ポチリと押してみましたら、書いてありましたねぇ。データは次の方法で読み込むことができます、、、

• Cloud Storage から
• Google アド マネージャーや Google 広告などの他の Google サービスから
• 読み取り可能なデータソース（ローカルマシンなど）から
• ストリーミング挿入を使用してレコードを個別に挿入する
• DML ステートメントを使用して一括挿入を行う
• Cloud Dataflow パイプラインの BigQuery I/O 変換を使用して BigQuery にデータを書き込む

ということで、今回は5番目のDMLステートメントでチャレンジしている、ということですね。で、マニュアルをよく読んでみると、INSERT文はカラムを指定する必要があるということで、気を取り直して実行してみますが、、、今度は、「Syntax error: Unexpected keyword NO at [1:26]」というエラーメッセージ。どうやら、カラム名に「No」を使っていたのがいけないのでしょうか。イライラしますねぇ♪
　はい、気を取り直して、No→BookNoに変更してテーブルを作り直します。今度は前回の経験をもとに、スキーマをテキストとして編集で作成してみました。カンマ区切りか、JSON形式で作成できるようです。

• 

さあ、気を取り直してINSERT文作成の続きです。日付の指定方法が分かりませんでしたが、文字列としておけば問題なさそうです。なんとか成功しそうです！どやっ！？

• 

成功しました！

• 

「テーブルに移動」して「プレビュー」を選択すると、データが入っていました。数行でも並列で投入されるのでしょうか、順番はバラバラになっていました。

• 

　それにしても、項目に予約語は使えないなら、作るときに教えてくれればいいのにねぇ。日本語の項目が作れないのも不親切な教え方でしたけど、テーブル作成上の注意点はこのあたりでしょうか。データ作成時の注意点は項目の指定は省略できないという点でした。で、やっと最初に話したかった話題の準備ができましたが、ちょっと長くなってきたので、次のエントリーとします。

　BigQueryを、こんな風に使うことってできるのかなぁ、という状態からなかなか抜け出せず、心を入れ替えることにしました。これまではとにかく勉強すればいいかと思っていたのですけど、ぼくが間違っていました。いったん内容を想定して、まずは使ってみることにします。

　と、いうことで先日勉強会で話題になった、リソースの利用開始日、終了日について、かつてこんなことを話題にしたなぁ、という内容を説明するために以下のテーブルを作ってみます。データは、佐藤正美先生の著作一覧です。発売開始日は奥付の第１刷の発行日、終了日は、本が売られていないものは10年後の日付、売られているものはいったん9999年12月31日とします。

No	商品名	発売開始日	終了日
１	CASEツール	1989-11-01	1999-11-01
2	リポジトリ入門解説	1991-10-01	2001-10-01
3	クライアント/サーバ データベース設計テクニック	1993-11-01	2003-11-01
4	RADによるデータベース構築技法	1995-11-25	2005-11-25
5	T字形ER データベース設計技法（黒本）	1998-10-25	2008-10-25
6	論理データベース論考	2000-03-25	9999-12-31
7	ITコンサルタントのスキル	2003-04-15	9999-12-31
8	データベース設計論-T字形ER（赤本）	2005-09-25	2015-09-25
9	SEのためのモデルへのいざない	2009-02-11	9999-12-31

まずは、今回のお試し用にプロジェクト「BigQuery Challenge 2019」を作成。
そして、Navigationメニューからビッグデータの「BigQuery」を選択します。

• 

中央の右寄りにある「データセットを作成」をクリックします。すると、以下のような「データセットを作成」のダイアログ（？）が現れますので、データセットIDを「BOOKS」として「データセットを作成」をクリックしてみます。ロケーションと、デフォルトのテーブルの有効期限が設定できるのですね。

• 

キャプチャは取れませんでしたが「BOOKSが作成されました。」とポップアップして消滅しました。確かに、今までなかった三角形が左側に表示されるようになりました。

• 

そして、開いてみたところ、、、どうやらぼくはテーブルを作ったと思っていたのですけど、できたのは「データセット」というものらしい。ああ、確かに「データセットを作成」でしたね。気を取り直して「テーブルを作成」してみましょう。

• 

そして、今度こそテーブルの作成ですが、、、ソースとして「テーブルの作成元」を選択できるようですね。今は「空のテーブル」となっていますけど、選択肢は他に、「Google Cloud Storage」「アップロード」「ドライブ」「Google Cloud Bigtable」ですね。確か、CSVを指定すれば、100件でデータ型を自動判別してフィールドもつくってくれる、と、Google OnAirで、聞いたような気がします。

• 

• 

今回は、デフォルトに従って、「空のテーブル」で進めてみます。テーブル名を今度こそ「BOOKS」にして、おお、「スキーマ」の定義ですね。「テキストとして編集」もできるし、「＋フィールドを追加」をクリックして、項目を追加していくこともできるのですね。テキストの編集方法がわからないので、ここは「＋フィールドを追加」で地道に増やしていきます。今回は「No」「書籍名」「開始日」「終了日」にしてみますので、一気に4つ追加してみます。なるほどねぇ、こんな風になるのですね。

• 

では、「名前」を入れて、型を選択します。型は「STRING」「BYTES」「INTEGER」「FLOAT」「NUMERIC」「BOOLEAN」「TIMESTAMP」「DATE」「TIME」「DATETIME」「GEOGRAPHY」「RECORD」がありました。前半は一般的なデータベースにも登場してきますので問題ありませんね。でも、GEOGRAPHYは地図情報でしょうか。あと、RECORDは不思議なワードですね。ネストして行データを格納できるのでしょうか。
モードは基本的にNOT NULLにするのがお好みなのですけど、選択肢は「NULLABLE」「REQUIRED」「REPEATED」でした。REPEATED、謎です。(笑)
順番に入れていこうとしたところ、いきなりエラーです。日本語、使えないのですね。「フィールド名の先頭は文字またはアンダースコアにしてください。含めることができるのは、文字、数字、アンダースコアのみです。」だって。むー、マジか～、今時マルチバイトが使えないとは、、、ざんねん。英語の苦手なユーザーに提供できなさそうですねぇ。あとで別名を付けたり、って、できないかなぁ。。。
　【追記：2019/05/04】
　最後の画像にある、「説明」項目は、「スキーマを編集」ボタンから編集可能で、そちらは日本語を記載することができました。

• 

と、いうことでまたしても心を入れ替えて、英数字とアンダースコアのみで作成することにします。

• 

次は、パーティションとクラスタの設定ですが、今回は少量データでちょっと作ってみるだけなので、パーティションは「パーティショニングなし」のままです。ほかの選択肢は「取り込み時間により分割」「Partition by field」「StartDate」「EndDate」ですね。なるほど、日付項目を作るとパーティションの候補に出てくるのですね。クラスタリングはパーティショニングを利用しないと選べないようです。いずれにせよ、パフォーマンス目的で必要になってくる設定ですので、今回はとりあえず放置、パフォーマンスが必要な時に検討する余地がある、ということですね。詳細オプションは暗号化の話でしたので、そのままにしておきます。さあ、「テーブルを作成」をポチリとしてみます。

またしてもキャプチャは取れませんでした。。。「BOOKSを作成しました。」というポップアップがでていましたが、、、確かにできていました！

• 

ええと、テーブルができましたが、データはどうやって入れればいいのでしょうか。。。それらしいボタンやメニューはありません。クエリーを使うのかな？
長くなったので、いったんこれにて終了します。続きはまた次のエントリーとします。