読者です 読者をやめる 読者になる 読者になる

日頃の行い

個人的な日頃の行いをつらつら書いてます\\\\ ٩( 'ω' )و ////

ScalaからBigQueryのAPI叩いてみた時の備忘録

Scala GoogleBigQuery Java リレーブログ

ScalaでBigQuery叩いてた時の備忘録です。
使ったレポジトリはここ

github.com


この後の話の流れ

  1. 準備 (認証)
  2. クエリを叩く
  3. ハマったこと

1. 準備

BigQueryを外から叩くためにとりあえず認証が必要です。
今回はサービスアカウントを利用して叩いてみました。
認証周りはGoogleのコンソールから準備します。
プロジェクトは事前に作ってる前提で、ここではMySampleBQProjectというプロジェクトを作って遊んでます
下記ページの右上にConsoleとあると思うので、そこから入れます。
BigQuery | Google Cloud Platform — Google Cloud Platform

入ってメニューを開くとAPI Managerという欄があるので、そこで認証用の設定が出来ます。
方法は下のほうの画像を参考に。


f:id:arata3da4:20151109222519p:plain

f:id:arata3da4:20151109222524p:plain

「作成」ボタンを押すと、 {プロジェクト名}-{証明書フィンガープリント}.p12 がダウンロードされます。
その後「認証情報」のページに戻るとサービスアカウントとして {なんかの番号}-{長い文字列}@developer.gserviceaccount.comができていると思います。
このダウンロードしたp12ファイルとサービスアカウントを利用します。

2. クエリを叩く

SELECT *  FROM [publicdata:samples.wikipedia] limit 10

というふっつうのクエリをScalaから叩いてみました。
利用させていただいたライブラリはこちら

github.com

  • build.sbt
libraryDependencies += "com.github.seratch" %% "bigquery4s" % "0.3"
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とか雑過ぎですね。

スキーマはこんな感じ。

f:id:arata3da4:20151109224443p:plain

  • build.sbt
libraryDependencies += "com.github.seratch" %% "bigquery4s" % "0.3"

libraryDependencies += "joda-time" % "joda-time" % "2.9"
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

見慣れた感じになりました。
もっといい方法ありそうだけど、今日は眠いのでとりあえずここまで。
また色々遊ぼうと思います。あと3日で無料枠切れるけど。