を作りました。
https://rubygems.org/gems/bq
https://github.com/takeru/bq/blob/master/lib/bq.rb
Rubyで文字列を組み立てて、ちょっと複雑なクエリを実行したいときに便利です。
データのインポートとかは標準のbqコマンドでやればいいと思います。
% time bundle exec ruby -rpp -e "require 'bq'; pp Bq::InstalledApp.new(:project_id=>'bq-takeru-test',:token_storage=>'.bq_secret_token.json').query('SELECT weight_pounds, state, year, gestation_weeks FROM publicdata:samples.natality ORDER BY weight_pounds DESC LIMIT 20;').to_hash"
{"kind"=>"bigquery#queryResponse",
"schema"=>
{"fields"=>
[{"name"=>"weight_pounds", "type"=>"FLOAT", "mode"=>"NULLABLE"},
{"name"=>"state", "type"=>"STRING", "mode"=>"NULLABLE"},
{"name"=>"year", "type"=>"INTEGER", "mode"=>"NULLABLE"},
{"name"=>"gestation_weeks", "type"=>"INTEGER", "mode"=>"NULLABLE"}]},
"jobReference"=>
{"projectId"=>"bq-takeru-test", "jobId"=>"job_R_ejXGskMARhRxExo_FAxVlaj1Q"},
"totalRows"=>"20",
"rows"=>
[{"f"=>[{"v"=>"18.0007436923"}, {"v"=>nil}, {"v"=>"2006"}, {"v"=>nil}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>nil}, {"v"=>"2007"}, {"v"=>"41"}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>"KY"}, {"v"=>"2004"}, {"v"=>"47"}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>"KY"}, {"v"=>"2004"}, {"v"=>"47"}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>"WY"}, {"v"=>"1979"}, {"v"=>"35"}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>nil}, {"v"=>"2007"}, {"v"=>"38"}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>"KY"}, {"v"=>"2004"}, {"v"=>"38"}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>nil}, {"v"=>"2006"}, {"v"=>"37"}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>"UT"}, {"v"=>"1982"}, {"v"=>"33"}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>nil}, {"v"=>"2006"}, {"v"=>"40"}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>"KY"}, {"v"=>"2004"}, {"v"=>"47"}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>"KY"}, {"v"=>"2004"}, {"v"=>"41"}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>nil}, {"v"=>"2005"}, {"v"=>"40"}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>nil}, {"v"=>"2007"}, {"v"=>"38"}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>nil}, {"v"=>"2005"}, {"v"=>"37"}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>nil}, {"v"=>"2005"}, {"v"=>"39"}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>nil}, {"v"=>"2005"}, {"v"=>nil}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>"KY"}, {"v"=>"2004"}, {"v"=>"43"}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>nil}, {"v"=>"2007"}, {"v"=>"39"}]},
{"f"=>[{"v"=>"18.0007436923"}, {"v"=>"OR"}, {"v"=>"1972"}, {"v"=>"40"}]}],
"totalBytesProcessed"=>"3751143548",
"jobComplete"=>true,
"cacheHit"=>false}
bundle exec ruby -rpp -e 0.83s user 0.13s system 30% cpu 3.223 total
"totalBytesProcessed"=>"3751143548"
3.7GBのgrep的フルスキャンなクエリ実行を数秒でできているということです。
(で、もっとでかいデータでもこんな具合なんですよね？)
これはまじはかい的テクノロジーなのではないか！
bq_example.rb
require "pp"
require "json"
require "bq"
require "terminal-table"
project_id = nil
project_id ||= ENV["bq_project_id"]
raise "please set your project_id" unless project_id
token_file = ".bq_secret_token.json"
bq = Bq::InstalledApp.new(:project_id=>project_id, :token_storage=>token_file)
unless bq.authorized?
unless bq.authorize
raise "failed to authorize."
end
end
q = DATA.read.strip
result = bq.query(q).to_hash
if result["error"] || result["rows"].nil?
pp result
exit
end
["kind","jobReference","totalRows","totalBytesProcessed","jobComplete","cacheHit"].each do |k|
puts "%20s : %s" % [k, result[k]]
end
# pp [result["rows"], result["schema"]]
rows = result["rows"]
fields = result["schema"]["fields"]
headings = [""] + fields.map{|f| f["name"] + "\n" + f["type"] + "\n" + f["mode"] }
table = Terminal::Table.new :headings=>headings do |t|
rows.each_with_index do |r,i|
t.add_row [i] + r['f'].map{|hash| hash['v'] }
end
end
table.align_column(0, :right)
fields.each_with_index do |s,i|
if %w(INTEGER FLOAT).include?(s["type"])
table.align_column(i+1, :right)
end
end
puts table
__END__
--- SELECT weight_pounds, state, year, gestation_weeks FROM publicdata:samples.natality ORDER BY weight_pounds DESC LIMIT 10;
--- SELECT word FROM publicdata:samples.shakespeare WHERE word="huzzah";
--- SELECT corpus FROM publicdata:samples.shakespeare GROUP BY corpus;
SELECT corpus, sum(word_count) AS wordcount FROM publicdata:samples.shakespeare GROUP BY corpus ORDER BY wordcount DESC;
bq% bq_project_id=bq-takeru-test bundle exec ruby examples/bq_example.rb
kind : bigquery#queryResponse
jobReference : {"projectId"=>"bq-takeru-test", "jobId"=>"job_49OOvoD3eaJ0NiMLybw6G-VMY4c"}
totalRows : 42
totalBytesProcessed : 3781873
jobComplete : true
cacheHit : false
+----+-----------------------+-----------+
| | corpus | wordcount |
| | STRING | INTEGER |
| | NULLABLE | NULLABLE |
+----+-----------------------+-----------+
| 0 | hamlet | 32446 |
| 1 | kingrichardiii | 31868 |
| 2 | coriolanus | 29535 |
| 3 | cymbeline | 29231 |
| 4 | 2kinghenryiv | 28241 |
| 5 | othello | 28076 |
| 6 | kinglear | 28001 |
| 7 | kinghenryv | 27894 |
| 8 | troilusandcressida | 27837 |
| 9 | antonyandcleopatra | 27310 |
| 10 | 2kinghenryvi | 27165 |
| 11 | 1kinghenryiv | 26401 |
| 12 | 3kinghenryvi | 26288 |
| 13 | kinghenryviii | 26265 |
| 14 | romeoandjuliet | 26192 |
| 15 | winterstale | 26181 |
| 16 | allswellthatendswell | 24622 |
| 17 | kingrichardii | 24150 |
| 18 | merrywivesofwindsor | 24033 |
| 19 | measureforemeasure | 23303 |
| 20 | 1kinghenryvi | 23272 |
| 21 | loveslabourslost | 23189 |
| 22 | asyoulikeit | 23087 |
| 23 | muchadoaboutnothing | 22760 |
| 24 | merchantofvenice | 22448 |
| 25 | tamingoftheshrew | 22358 |
| 26 | kingjohn | 21983 |
| 27 | titusandronicus | 21911 |
| 28 | twelfthnight | 21633 |
| 29 | juliuscaesar | 21052 |
| 30 | periclesprinceoftyre | 19846 |
| 31 | timonofathens | 19841 |
| 32 | twogentlemenofverona | 18493 |
| 33 | macbeth | 18439 |
| 34 | sonnets | 17805 |
| 35 | tempest | 17593 |
| 36 | midsummersnightsdream | 17348 |
| 37 | comedyoferrors | 16361 |
| 38 | rapeoflucrece | 15221 |
| 39 | venusandadonis | 10035 |
| 40 | various | 3545 |
| 41 | loverscomplaint | 2586 |
+----+-----------------------+-----------+
これらを参考にしました。
http://d.hatena.ne.jp/a-know/20140506/1399386097
https://github.com/abronte/BigQuery
https://github.com/a-know/ruby-bigquery-test
https://github.com/google/google-api-ruby-client
http://ja.asciicasts.com/episodes/245-new-gem-with-bundler