株式会社 石川設計 システム開発をとことん極めます！

　前回のエントリーの続きです。やっとGoogle Cloud SDKがインストールできたところまで書きました。bq コマンドライン ツールの使用に関するクイックスタートに書いてありましたが、インストールした後にはBigQuery APIを有効にする必要があるそうです。

• 

　「APIを有効にする」ボタンをクリックしてみます。

• 

　上記のGCPのページへ遷移しました。プロジェクトを選ぶ必要があるようです。

• 

　BigQuery APIはプロジェクトごとに設定するようですね。アプリケーションごとに設定することもあるのでしょうか。こちらは不明ですねぇ。とりあえず今回のために作ったプロジェクト（BigQuery challenge 2019)を選択して、「続行」ボタンをクリックします。左下に「APIを有効にしています」というポップアップがでて、次のような表示にかわりました。

• 

　なるほど、APIは有効でしたか。次は、適切な認証情報ということで、「認証情報に進む」ボタンをクリックしてみます。

• 

　おお～、これはなんだか難しそうですねぇ。あ、でも、お手伝いしていただけるようです。使用するAPIは、「BigQuery API」がセットされているので、これで問題ないでしょうね。App EngineまたはCompute EngineでこのAPIを使用する予定、あるのかなぁ。。。将来的には、App Engineでアプリケーションを作って、BigQueryのデータを呼び出して使いたいから、「はい、1つまたは両方を使用しています」を選択すべきか、今回のテーマはあくまでローカルファイルをアップロードすることだから、「いいえ、使用していません」を選ぶべきか、悩むなぁ。お、よく読むと、App Engineでは必要ない、と、書いてあるような気がするなぁ。と、いうことで「いいえ、使用していません」を選ぼう。そして、「必要な認証譲歩」ボタンをクリックします。

• 

　画面が変わって、次は、「サービスアカウントを作成する」必要があるようです。サービスアカウント名は、自由でいいのかな？「？」をポイントすると「このサービスアカウントで行うことを説明します」と、ヒントがポップアップしました。役割は、おお、意外といっぱいありました！

• 

　今回は、テーブル定義が作られた状態からのデータを投入する、ということで考えているので、「BigQuery データー編集者」が適切なのかな。「BigQuery ユーザー」とはどう違うのでしょうか。。。わかりませんが、とりあえず選択して進めましょう。 では、サービスアカウント名を「サンプルデータをロードする」くらいにしてと。おお、サービスアカウントIDが勝手に入力されました。そして、選択すると、おお、内容説明がポップアップした上に、複数選択できるのですね！とりあえず進めるって、必要ですねぇ。悩みが減りました。クエリ実行するためには、BigQueryユーザーを選ばないといけないようです。BigQueryデータ閲覧者はデータセットを表示することができるけど、クエリは発行できない、ということですね。

• 

　「キーのタイプ」は、たぶんデフォルトの「JSON」のままでよいでしょうね。「次へ」ボタンをクリックします。

• 

　おお、自動でファイルがダウンロードされてしまいました。このJSONのファイルを利用するのでしょうか。「閉じる」をクリックします。

• 

　すると、認証情報のページが出てきて、どうやら「サービスアカウントキー」というものができたようです。む～、とりあえずこれでBigQuery APIが有効になった、ということでしょうか。
　そういえば、前回のエントリーでCloud SDKをインストールしたけど、動作確認してませんでしたね。クイックスタートにも書いてありましたが、確か、bqコマンドが使えるはずですね。試しに実行してみます。

bg show bigquery-challenge-2019:books.books

　失敗しました。。。「Not found: Dataset」ということですね。もしかしたら、大文字小文字を気にするのかもしれません、ということで、再度入力します。

bg show bigquery-challenge-2019:BOOKS.BOOKS

• 

　 おおっ、出ました！成功したよ～！！
大したことはしていないんだけれど、うれしいですねぇ。BigQueryのテーブル名は大文字小文字を気にする、ということがわかりました。今後、気を付けます。
　クイックスタートは続けてクエリを発行していますが、今回ぼくはクエリを実行する権限を与えていないつもりなので、失敗するのかも。ちょっとやってみます。

bq query "SELECT * FROM bigquery-challenge-2019:BOOKS.BOOKS"

• 

　予想通りエラーになりましたが、エラー内容が予想と違いましたねぇ。予想外に”-“が登場したと言っています。そういえば、演算のマイナスとの区別ができないから、テーブルを指定するときには括弧「[]」でくくること、という記述がどこかにあったような気がしますね。（追記：オライリー・ジャパンの『Google BigQuery』P.219 7.2 BigQueryのクエリ言語 のところに記述がありました。ISBN978-4-87311-716-4）ちょっと試しにやってみます。今度は想定通りのエラーになるかな？

bq query "SELECT * FROM [bigquery-challenge-2019:BOOKS.BOOKS]"

• 

　あら、想定外にデータが取得できてしまいました。そういえば、さっきダウンロードしたJSONのファイル、何にも指定していないよね。なるほど、ということは、bqコマンドの承認は、前回のエントリーでSDKをインストールしたときにやったものが有効になっているのですね。（あれです、’gloud init’を実行して、ログインして承認したやつですね！）

　ええと、ということは、BigQueryのAPIを有効にしようと一生懸命やったこと、って、無意味だったのかな。。。？

　はい、気を取り直して、新しく書籍のデータを作って入れてみます。

C:\work\NEW_BOOKS.csv
10,やさしいみつのりさんのデータベース,2020-05-14,9999-12-31
11,きびしいみつのりさんのデータベース,2021-05-14,9999-12-31

　コマンドは、以下の通りです。「–noreplace」オプションを付けると、テーブルに追加してくれるそうです。上書きしたい場合は「–replace」で、何もつけなければ、テーブルが空の場合にのみ書き込みされるということですね。「–source_format」で指定できるフォーマットは、「CSV、AVRO、PARQUET、ORC または NEWLINE_DELIMITED_JSON です。」と、書いてありますね。CSVとNEWLINE_DELIMITED_JSONしか分かりません。（汗）ええと、いつか余裕が出てきたら、他のフォーマットも調べてみたいと思います。

bq load --noreplace --source_format=CSV BOOKS.BOOKS C:\work\NEW_BOOKS.csv

さて、実行してみます。

• 

　おお、なんか成功したっぽい！ほら、タイムスタンプが変わってるし！

• 

　データも、ほら、、、

• 

　ぐはっ！！文字化けしてる。。。(笑)
そういえば、確かUTF-8にしないとダメだって、どこかに書いてあった気がしますねぇ。やっと終了、って、思ったのに～♪

• 

　おお、確かにSJISになってます。サクラエディタの文字変換機能でSJISをUTF-8に変換して、再度コマンドを実行して確認してみます！

• 

　やっとこ、できました！
でも、なんだろう、この書名は、ねぇ。。。(笑)

　これで、実現方法は完了ですね。サーバーでCSVファイルをUTF-8で作成して、bqコマンドを実行するバッチファイルをスケジューラに登録すれば、自動でデータロードできるところまで確認できました。注意点は文字コードを気にすることと、’cloud init’の権限ですね。テストはこれで動いたということで問題ないですけど、本番環境のサーバーでは、どのユーザで権限を設定するのか、というのが課題になりそうです。

　BigQueryのAPI、有効にしたのが無意味だったか確認してみます。先ほど作成した認証情報を削除してみます。

• 

　「削除」ボタンをクリックします。

• 

　おお、脅されますねぇ。脅しには屈しませんよ！「削除」をクリックします。数秒で消えましたので、再度データをロードしてみます。問題なく動きましたので、BigQuery APIの利用は、データのロードには関係ない、ということがわかりました。やりたいことはできるようになったけど、プロの仕事としては、いまいちだなぁ。GCPをプロとして使っていくためには、権限、認証を理解することが課題ですね。またそのうち調査したいと思います。では、今回のエントリーはこのあたりで終了とします。

　先日のエントリーで実験的にやりましたが、WebのUIからINSERT文を記載することで複数件データを投入することができました。しかし、実業務で毎日WebのUIにINSERT文を書くわけにはいきません。Googleさまの担当者さまからそのやり方が記載されているというURLを二つ、教えていただきました。

• Oracle data (ETL) to Google BigQuery using Google Cloud Dataflow and Dataprep
• Cloud Storage からの CSV データの読み込み

　いずれもGoogle Cloud Storage(GCS)にあるCSVを読み込むことを推奨しているように見えますね。確か、いったんGCSに保存しておけば、そこからBigQueryへはGoogle内のネットワークで完結できるので速い、と、聞いたような記憶があります。確かに何度もGCSからBigQueryのテーブルを再作成し直すならそれは最適でしょうが、そこがおすすめの理由でしょうか。どちらの方法にどんなメリットがあるのでしょうかね。
　ぼくが想定しているのは、オンプレミスのオラクルデータベースからデータを抜き出して、BigQueryへ入れるという流れを構築することです。でも、上記の最初のリンクではサードパーティのETLツールを使えばとっても便利で時間の節約になる、ということと、DataPrep、DataFlowのGCPサービスがとっても便利なのでそれを使っているということが書いてありました。とっても便利なのかもしれないけれど、ツールとサービスの概念や使い方を新たに学ばなければならないというハードルの高さがあって、くじけそうです。ええと、DataPrepは、「分析や機械学習に使用するデータを視覚的に探索、クリーニング、準備するためのインテリジェント クラウド データ サービス」ということですので、データクリーニングが視覚的に実行できて時間が節約できる、ということですかね。そして、DataFlowは「信頼性と表現力を損なうことなく、ストリーム データ処理とバッチデータ処理を簡素化」ということなので、データを流すためのジョブを作れるサービスなのでしょうかね。
　と、いうことで、いったんは、CSVファイルを作るのはオラクル側で処理を作成してファイルを作るとして、そのファイルを直接BigQueryへ読み込む方法を探ってみます。上記の二つ目のリンクからたどっていった先に、ローカルのCSVファイルを読み込む方法もありました。

• データをローカル データソースから読み込む

　こちらをよく読んでみた結果、Webのユーザインターフェースを使った方法とコマンドラインから実行する方法が書かれてありました。ぼくは、自動的にアップロードされる仕組みを作りたい、と思っていますので、当然、コマンドラインからの実行方法を選択します。これを使うためには、「bqコマンドライン」というものが使えるようですが、当然ローカルマシンにはそんなコマンドが入っていないので、インストールする必要があるでしょう。bqコマンドラインが使えるようになるためのチュートリアルがありました。

• bq コマンドライン ツールを使用したクイックスタート

　なるほど、Cloud SDKをインストールして初期化する必要がある、ということですね。インストーラをダウンロードして、Cloud SDKシェルを起動、gloud initを実行する、と言う手順ですね。そして、Google Cloudライブラリをインストールすれば、開発言語から簡単に呼び出せるようになるようです。Windows ServerではInternet Explorerでセキュリティ強化の構成がセットアップされている場合は、注意が必要のようです。今回はお試しでこちらのWindows10のクライアントマシンからの実行なので気にしなくてよさそうですが、オンプレミスのWindows Serverにセットアップするときは、注意が必要そうですね。
　さて、手順に従って、やりましょうかねぇ。まずは、ダウンロードします。

• 

「GoogleCloudSDKInstaller.exe」というファイルがダウンロードされました。ウィルススキャンして脅威は見つかりませんでした(笑)ので、実行してみます。

• 

　Google Cloud SDK Setupの、ようこそ画面が表示されました。GCPのリソースを管理したり作成したりすることが簡単にできるようになるライブラリやツールが入っているそうです。使用した統計情報が自動的に送られますが、ここは使わせていただいているので協力させていただきましょう、チェックしたままにします。
「Next >」をクリックします。

• 

　契約条項に同意するために内容をよく読んでから、「I Agree」をクリックします。

• 

　次はインストールタイプの選択です。今回はローカルPCなのでどちらでもよいでしょう。ただ、Windows Serverでバッチを動かすときは、All usersにしておいた方がよいのかな、と、いうことでAll usersを選択してみます。管理者権限が必要になりました。

• 

　「Next >」をクリックします。すると、ユーザアカウント制御の画面が表示されます。

• 

　場合によっては管理者ユーザとパスワードが聞かれるかも知れませんね。「はい」をクリックして続行します。

• 

　インストール先を尋ねられます。特に変更する必要はありませんので、「Next >」をクリックします。ディスクスペースは、89MBytes必要ですね。

• 

　やっとインストールできそうですね。「Cloud SDK Core Libraries and Tools」はチェック必須ですね。「Bundled Python」は、Python2.7がインストールされるようです。先日Anacondaをインストールして、Python3.7.3をゲットしたばかりなので、正直2.7のPythonは必要ないのですけど、これが入っていないとSDKが使えないそうなので、仕方ありません、このままにしておきます。「Cloud Tools for PowerShell」はWindowsのPowerShellからGCPのリソースを管理するのに使えるようです。今のところPowerShellから利用する予定はありませんが、初期設定を外す理由もないのでそのままにしておきます。「Beta Commands」はベータ機能を使いたい人向けですね。新しい物好きなぼくには、とっても気になるチェックボックスですが、こうやっていつも目的を見失うので今回はチェックを外したままとします。いつもなら、とりあえず全部チェックしちゃえよ～、ってなるのですけど。(笑)
　さあ、「Install」ボタンをクリックします。

• 

　いろいろインストールされて、、、終了。1分もかかりませんでしたね。

• 

　「Next >」をクリックします。

• 

　インストール完了しました。あとは、チェックボックスに従った内容を実行してくれるわけですね。上の二つはスタートメニューとデスクトップにショートカットを作ってくれる、という一般的なものなのですね。そして、Google Cloud SDKシェルをスタートして、’gcloud init’を実行して設定してくるそうです。そういえば、インストールの手引きにこのチェックを受け入れてね、と、書いてありましたね。このままで「Finish」をクリックします。そうすると、想定通り、コマンドプロンプトが起動されました。

• 

　ログイン必須ということで、「Y」を入力してEnterキーを押しますと、ブラウザが開いて「Googleにログイン」画面が表示されました。

• 

　一覧から、GCPのアカウントのアドレスをクリックするとアカウントへのアクセスをリクエストする画面に切り替わりました。

• 

• 

　許可しないと使えるようになりませんので、「許可」ボタンをクリックします。

• 

　おお、認証が完了しました！で、終わったのでしょうか。。。
コマンドプロンプトを見てみると、続きがありました！

• 

ええと、使用するクラウドプロジェクトを選べ、ということで、先日作った「bigquery-challenge-2019」プロジェクトを選択します。「1」を入力して、Enterキーを押します。なるほど、’gcloud init’で、プロジェクトを選択しておくことができる、ということでしょうね。変更したいときは切り替えコマンドがあるのか、新たに設定を追加するのかはわかりませんが、気にしておく必要がありますね。

• 

　すると、いろいろとメッセージが出てきて、終了しました。
今度こそ、完了したようです。まだ、やりたいことは終わっていませんが、長くなってきたので、いったん終了して次のエントリーへ続きます。

　先日の投稿の最後に、「データポータルで調べる」というボタンが気になるということを言い残していたのですけど、さっそくクリックして見てみました。クリック後に表示されたのは以下の画面です。

• 

　初めて使う人には、こんなダイアログが表示されるようです。もちろん、使ってみますよ。そのためにクリックしているのですから。ということで「使ってみる」をクリック。

• 

　思い切ってクリックしたのに、また何やら聞かれました。こういうとき、ぼくはすぐにくじけそうになります。初めての、よく知らないものに出会ったときに、ここであきらめることが多いのですけど、今日はがんばりますよ～。「承認」をクリックします。

• 

　また何か聞かれました。もうそろそろ限界ですよ。データへのアクセスは先ほど承認したはずなのですけど、、、む～、今度はアカウントへのアクセス、どこが違うのでしょうか。

• 

おお、下の方にボタンがありました。「許可」をクリックします。

• 

　これで先ほど検索したデータを使えるようになったようです。右側にグラフのアイコンがあって、これを切り替えることで表示が変わるようですが、、、いかんせん分析できるようなデータではなかったので、どれを選んでもうまく表示されませんね。レコード件数しか分析できる情報がないですからねぇ。

　ちなみに、グラフ＞表のアイコンは、左上から「表」「棒付きデータ表」「ヒートマップ付きデータ表」「スコアカード」「数字が短縮表示されたスコアカード」「時系列グラフ」「スパークライングラフ」「平滑時系列グラフ」「縦棒グラフ」「積み上げ縦棒グラフ」「100%積み上げ棒グラフ」「棒グラフ」「積み上げ横棒グラフ」「100%積み上げ横棒グラフ」「円グラフ」「ドーナッツグラフ」「地図」「複合グラフ」「積み上げ複合グラフ」「折れ線グラフ」「平滑線グラフ」「積み上げ面グラフ」「100%積み上げ面グラフ」「面グラフ」「散布図」「バブルチャート」「ピボットテーブル」「棒付きピボットテーブル」「ヒートマップ付きピボットテーブル」「ブレットグラフ」と、いろいろなグラフが選べます。

　データ部分には、「データソース」「期間のディメンジョン」「ディメンジョン」「指標」「ページ当たりの行数」「集計行」「並べ替え」「サブの並べ替え」「デフォルトの日付範囲」「Interactions」と項目名だけではなんとなくしか分かりませんが、選べるようです。今回のこのデータではいろいろと試すことができなかったので、また別のデータを作って動作確認のため、再チャレンジしてみたいと思います。どんなデータがいいかなぁ。。。

　いろいろとデータポータルを触って満足したので、BigQueryにちょっと戻ってみてみました。先ほどまではあまり気にならなかったのですけど、「クエリ履歴」が見られるのですね。

• 

　こちら、ぼくが先日実行したクエリ以外にも、先ほどデータポータルから検索したときに発行されたクエリもこちらに出てくるようです。この履歴をクリックすると詳細が表示されました。

• 

　各クエリはこんな風に自動で保存されて、再利用が簡単にできるようになっているのですね。ちなみに、あて先テーブルは「期限切れの一時テーブル」となっていますが、一時テーブルは、グーグルさまによって、だいたい24時間ほどは再利用できるようになっているそうです。そういえば、クエリ実行時にあて先にテーブル名を指定できるようになっていましたね。なので、再利用が必須の時は名前を付けて、今回限りのときは名前を付けずにクエリを発行する、という感じになるでしょう。

　そういえば前回、後で再利用できるかも、と、こっそり作業していたのですけど、クエリにも名前を付けて保存することができるのですよね。上の画像だと「ThirdSelectBooks」と名前を付けて保存してあります。「保存したクエリ」から確認できます。

• 

　この中の一つ「ThirdSelectBooks」を選択してみます。内容が表示されて「クエリをエディタで開く」をクリックすることで再利用することができました。

• 

　よく見ると「個人用（本人のみが編集可能）」を選べるようになっていますね。ほかの選択肢は、一つだけですね。

• 

　試しに、「プロジェクト（プロジェクトメンバーが編集可能）」を選択してみました。

• 

　おお、プロジェクトのすべてのメンバーと共有できるようです。ただし、元には戻せないと脅されました。今回は一人プロジェクトなので、キャンセルです。きっと、保存したクエリの下にある「プロジェクトクエリ」を選択することで見られるようになるのでしょう。あと、リンク共有をオンに変更すると「クエリURLの共有：」という欄が出現します。おそらくこのURLを知っている人にだけ共有できる、という機能なのでしょうね。組織で利用するときには、必要な機能になってきますね。

　あと、残ったメニューの「ジョブ履歴」は、文字通りジョブの履歴ですね。今回の作業中にも発生してました。BOOKSテーブルがうまくなくて再作成したときにバックアップとしてテーブルをコピーしたときにジョブが発生していました。ほかにもジョブとして扱われるものもあるかも知れませんが、現時点ではとりあえず不明です。

• 

　そして、「転送」と「スケジュールされたクエリ」を選択したところ、両方ともBigQuery Data Transfer APIのページが表示されました。おそらく、このAPIを有効にして、API経由で実行するのでしょうね。あと、「BI Engine」は、同名のベータ版の機能のページが表示されました。ええと「BigQuery 内の複雑なデータセットをインタラクティブに探索できる高速なインメモリ分析サービスです」ということが書いてありました。む～、違いがよくわからないサービスが登場してきましたなぁ。インメモリで処理できるということで、速さが必要なときに使えばいいのかな。余力があれば、また調べてみよう。今回はこれくらいで。

　やっとクエリの準備ができました！
と、いうことで、どんな話をしようとしていたかというと、リソースに開始日と終了日を持つと、ある期間に有効だったデータを取得するのが難しくなるかどうか、という議論です。そんなデータの持ち方したら、複雑になってしまうのでダメだというご指摘を受けたことがあって、意外と簡単になりますよ～、という話です。
　先ほどのデータ、ビジュアル的に表すと以下のようになります。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を作成しました。」というポップアップがでていましたが、、、確かにできていました！

• 

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