ScalaでBigQuery叩いてた時の備忘録です。
使ったレポジトリはここ
この後の話の流れ
- 準備 (認証)
- クエリを叩く
- ハマったこと
1. 準備
BigQueryを外から叩くためにとりあえず認証が必要です。
今回はサービスアカウントを利用して叩いてみました。
認証周りはGoogleのコンソールから準備します。
プロジェクトは事前に作ってる前提で、ここではMySampleBQProjectというプロジェクトを作って遊んでます
下記ページの右上にConsoleとあると思うので、そこから入れます。
BigQuery | Google Cloud Platform — Google Cloud Platform
入ってメニューを開くとAPI Managerという欄があるので、そこで認証用の設定が出来ます。
方法は下のほうの画像を参考に。
「作成」ボタンを押すと、 {プロジェクト名}-{証明書フィンガープリント}.p12 がダウンロードされます。
その後「認証情報」のページに戻るとサービスアカウントとして {なんかの番号}-{長い文字列}@developer.gserviceaccount.comができていると思います。
このダウンロードしたp12ファイルとサービスアカウントを利用します。
2. クエリを叩く
SELECT * FROM [publicdata:samples.wikipedia] limit 10
というふっつうのクエリをScalaから叩いてみました。
利用させていただいたライブラリはこちら
- build.sbt
libraryDependencies += "com.github.seratch" %% "bigquery4s" % "0.3"
- BigQueryDemo.scala
package vu import bigquery4s.BigQuery object BigQueryDemo { val bq = BigQuery.fromServiceAccount( "{なんかの番号}-{長い文字列}@developer.gserviceaccount.com", // サービスアカウント "/path/to/{プロジェクト名}-{証明書フィンガープリント}.p12" // 鍵ファイルへのPATH ) val projectId = "mysamplebqproject" // プロジェクト名 def main(args: Array[String]) { val sql = "SELECT * FROM [publicdata:samples.wikipedia] limit 10" val jobId = bq.startQuery(projectId, sql) val result = bq.await(jobId) bq.getRows(result).foreach(println) } }
これを叩くとこんな感じになります。
> run [warn] Multiple main classes detected. Run 'show discoveredMainClasses' to see the list Multiple main classes detected, select one to run: [1] vu.BigQueryDemo [2] vu.BigQueryDemo2 Enter number: 1 [info] Running vu.BigQueryDemo 11 09, 2015 10:42:12 午後 com.google.api.client.googleapis.services.AbstractGoogleClient <init> 警告: Application name is not set. Call Builder#setApplicationName. WrappedTableRow({"f":[{"v":"User talk:Sonicrazy"},{"v":"1303993"},{"v":""},{"v":"3"},{"v":null},{"v":"59222308"},{"v":null},{"v":"1063944"},{"v":"DJ BatWave"},{"v":"1150604342"},{"v":null},{"v":null},{"v":null},{"v":"hey"},{"v":"4442"}]}) WrappedTableRow({"f":[{"v":"Sit Down, Shut Up (U.S. TV series)"},{"v":"17446266"},{"v":""},{"v":"0"},{"v":null},{"v":"278080927"},{"v":null},{"v":"1862829"},{"v":"Magioladitis"},{"v":"1237376427"},{"v":"true"},{"v":null},{"v":null},{"v":"new television template"},{"v":"7042"}]}) WrappedTableRow({"f":[{"v":"Talk:Homestead, Florida"},{"v":"2387164"},{"v":""},{"v":"1"},{"v":null},{"v":"20382857"},{"v":null},{"v":"28438"},{"v":"Altenmann"},{"v":"1123295494"},{"v":null},{"v":null},{"v":null},{"v":"/* Page protection */"},{"v":"10354"}]}) WrappedTableRow({"f":[{"v":"Big Brother 9 (U.S.)"},{"v":"14549699"},{"v":""},{"v":"0"},{"v":null},{"v":"193263045"},{"v":"71.225.199.168"},{"v":null},{"v":null},{"v":"1203685970"},{"v":null},{"v":null},{"v":null},{"v":"[[WP:AES|←]]Replaced page with 'Yauman CAhan'"},{"v":"12"}]}) WrappedTableRow({"f":[{"v":"Talk:Pashtun people"},{"v":"273006"},{"v":""},{"v":"1"},{"v":null},{"v":"9212968"},{"v":null},{"v":"28107"},{"v":"Jmabel"},{"v":"1105225046"},{"v":null},{"v":null},{"v":null},{"v":"/* jambel please give reference to your text */ Lost Tribes of Israel"},{"v":"57553"}]}) WrappedTableRow({"f":[{"v":"Sprint Nextel"},{"v":"2380393"},{"v":""},{"v":"0"},{"v":null},{"v":"20820941"},{"v":"69.134.50.153"},{"v":null},{"v":null},{"v":"1123811750"},{"v":null},{"v":null},{"v":null},{"v":null},{"v":"5696"}]}) WrappedTableRow({"f":[{"v":"User:TheBigJagielka/Samuel Galindo"},{"v":"25455636"},{"v":""},{"v":"2"},{"v":null},{"v":"337561555"},{"v":"203.112.84.138"},{"v":null},{"v":null},{"v":"1263372806"},{"v":null},{"v":null},{"v":null},{"v":null},{"v":"2291"}]}) WrappedTableRow({"f":[{"v":"User talk:129.12.200.49"},{"v":"1211489"},{"v":""},{"v":"3"},{"v":null},{"v":"111561147"},{"v":null},{"v":"3315117"},{"v":"Qxz"},{"v":"1172665180"},{"v":"true"},{"v":null},{"v":null},{"v":"Level 1 warning re. [[Apollo]]"},{"v":"19031"}]}) WrappedTableRow({"f":[{"v":"Bhutan"},{"v":"2421391"},{"v":""},{"v":"0"},{"v":null},{"v":"212949166"},{"v":null},{"v":"96159"},{"v":"Riyehn"},{"v":"1210984268"},{"v":null},{"v":null},{"v":null},{"v":"fixed per capita income in economy"},{"v":"49647"}]}) WrappedTableRow({"f":[{"v":"Messiah"},{"v":"19616"},{"v":""},{"v":"0"},{"v":null},{"v":"188055849"},{"v":null},{"v":"1796696"},{"v":"Noobeditor"},{"v":"1201743987"},{"v":"true"},{"v":null},{"v":null},{"v":"Reverted edits by [[Special:Contributions/162.83.159.167|162.83.159.167]] to last version by Steven J. Anderson"},{"v":"26802"}]}) [success] Total time: 4 s, completed 2015/11/09 22:42:14
やったねたえちゃんプログラムから動かせたよ。
たかがSELECT *なのになんか楽しいですね！そんな気持ちを忘れちゃいけないんだと思います。
3.ハマったこと
強くハマったわけではないんですけど、TIMESTAMP型を扱うときにうーんってなったのでメモ。
joda.time.DateTime型に変換しようとしたらちょっとめんどかったという話です。
これ書こうと思ってpublicdata見たらTIMESTAMP型なかったので generatedata.com で用意しました。
テーブルfooとか雑過ぎですね。
スキーマはこんな感じ。
- build.sbt
libraryDependencies += "com.github.seratch" %% "bigquery4s" % "0.3" libraryDependencies += "joda-time" % "joda-time" % "2.9"
- BigQueryDemo2.scala
package vu import bigquery4s.BigQuery import org.joda.time.DateTime object BigQueryDemo2 { val bq = BigQuery.fromServiceAccount( //略 ) val projectId = "mysamplebqproject" def main(args: Array[String]) { val sql = "SELECT created FROM [dummy.foo] LIMIT 10" val jobId = bq.startQuery(projectId, sql) val result = bq.await(jobId) bq.getRows(result).foreach(r => println(r.cells.head.value.getOrElse(""))) } }
これを実行すると
> run [1] vu.BigQueryDemo [2] vu.BigQueryDemo2 Enter number: 2 [info] Running vu.BigQueryDemo2 11 09, 2015 11:02:12 午後 com.google.api.client.googleapis.services.AbstractGoogleClient <init> 警告: Application name is not set. Call Builder#setApplicationName. 1.436453925E9 1.433881786E9 1.448222214E9 1.463913534E9 1.419150889E9 1.430968225E9 1.436388839E9 1.465884312E9 1.450501949E9 1.440506521E9
となり、指数表記になり、数字として扱うならDouble型で受け取って、toFloatとかしてDateTimeに変換とかまどろっこしいことをしなければいけません。
それ以外無いのかなぁって思って探したらBQに関数があるのでそれを利用出来るみたいです。
Query Reference - BigQuery — Google Cloud Platform
FORMAT_UTC_USEC とか TIMESTAMP_TO_MSECとか使えば良さそうな感じが出てます。
package vu import bigquery4s.BigQuery import org.joda.time.DateTime object BigQueryDemo3 { val bq = BigQuery.fromServiceAccount( // 略 ) val projectId = "mysamplebqproject" def main(args: Array[String]) { val sql = "SELECT FORMAT_UTC_USEC(created) as a, TIMESTAMP_TO_MSEC(created) as b FROM [dummy.foo] LIMIT 10" val jobId = bq.startQuery(projectId, sql) val result = bq.await(jobId) bq.getRows(result).foreach(r => println(r.cells.head.value.getOrElse(""))) bq.getRows(result).foreach(r => println(r.cells(1).value.getOrElse("").toString.toLong)) } }
これを実行すると
> run [1] vu.BigQueryDemo [2] vu.BigQueryDemo2 [3] vu.BigQueryDemo3 Enter number: 3 [info] Running vu.BigQueryDemo3 11 09, 2015 11:13:53 午後 com.google.api.client.googleapis.services.AbstractGoogleClient <init> 警告: Application name is not set. Call Builder#setApplicationName. 2015-07-09 14:58:45.000000 2015-06-09 20:29:46.000000 2015-11-22 19:56:54.000000 2016-05-22 10:38:54.000000 2014-12-21 08:34:49.000000 2015-05-07 03:10:25.000000 2015-07-08 20:53:59.000000 2016-06-14 06:05:12.000000 2015-12-19 05:12:29.000000 2015-08-25 12:42:01.000000 1436453925000 1433881786000 1448222214000 1463913534000 1419150889000 1430968225000 1436388839000 1465884312000 1450501949000 1440506521000
見慣れた感じになりました。
もっといい方法ありそうだけど、今日は眠いのでとりあえずここまで。
また色々遊ぼうと思います。あと３日で無料枠切れるけど。
