(b_b) is Query Builder for Google BigQuery
Ruby Shell
Latest commit 6b2ead0 Jul 16, 2016 @yhirano55 committed on GitHub Merge pull request #6 from yhirano55/v0.1.3
V0.1.3
Permalink
Failed to load latest commit information.
bin init Jun 29, 2016
lib v0.1.3 Jul 16, 2016
spec Modify BB to delegation with method_missing Jul 16, 2016
.coveralls.yml init Jun 29, 2016
.gitignore init Jun 29, 2016
.hound.yml init Jun 29, 2016
.rspec init Jun 29, 2016
.rubocop.yml init Jun 29, 2016
.ruby-style.yml init Jun 29, 2016
.travis.yml init Jun 29, 2016
Gemfile v0.1.0 Jul 14, 2016
LICENSE.txt init Jun 29, 2016
README.md Modify README to add current version Jul 16, 2016
Rakefile init Jun 29, 2016
b_b.gemspec Fix gemspec Jul 16, 2016

README.md

BB

(b_b) is SQL Query Builder for Google BigQuery

Gem Version Dependency Status Build Status Coverage Status codebeat badge

Install

Add the following line to Gemfile:

gem 'b_b'

and run bundle from your shell.

To install the gem manually from your shell, run:

gem install b_b

Basic usage

(b_b) can build only SQL SELECT Statement.

BB.select("word", "corpus", "COUNT(word)").
   from("publicdata:samples.shakespeare").
   where(word_cont: "th").
   group(:word, :corpus).
   to_sql

# => "SELECT word, corpus, COUNT(word) FROM publicdata:samples.shakespeare WHERE (word CONTAINS 'th') GROUP BY word, corpus"

Query Reference of BigQuery's query syntax and functions is here.

Examples

L(b_b)z ==============33

SELECT clause

BB.select(:id, :name, :state).to_sql
# => "SELECT id, name, state"

BB.select("id", "name", "COUNT(*)").to_sql
# => "SELECT id, name, COUNT(*)"

FROM clause

BB.from("publicdata:samples.shakespeare").to_sql
# => "SELECT * FROM publicdata:samples.shakespeare"

BB.from("[applogs.events_20120501]", "[applogs.events_20120502]", "[applogs.events_20120503]").to_sql
# => "SELECT * FROM [applogs.events_20120501], [applogs.events_20120502], [applogs.events_20120503]"

BB.from("applogs.events_", on: Date.new(2012, 5, 1)).to_sql
# => "SELECT * FROM applogs.events_20120501"

BB.from("mydata.people", from: Date.new(2014, 3, 25), to: Date.new(2014, 3, 27)).to_sql
# => "SELECT * FROM TABLE_DATE_RANGE(mydata.people, TIMESTAMP('2014-03-25'), TIMESTAMP('2014-03-27'))"

BB.from(BB.from("publicdata:samples.shakespeare"), as: shakespeare).to_sql
# => "SELECT * FROM (SELECT * FROM publicdata:samples.shakespeare) AS shakespeare"

JOIN clause

BB.from(:customers, as: :t1).inner_join(:orders, as: :t2).on("t1.customer_id = t2.customer_id").to_sql
# => "SELECT * FROM customers AS t1 INNER JOIN orders AS t2 ON t1.customer_id = t2.customer_id"

BB.from(:customers, as: :t1).join_each(BB.select(:id, :name).from(:orders), as: :t2).on("t1.customer_id = t2.customer_id").to_sql
# => "SELECT * FROM customers AS t1 JOIN EACH (SELECT id, name FROM orders) AS t2 ON t1.customer_id = t2.customer_id"

WHERE clause

BB.where(id: 1..10, name: "donald", flag: false).to_sql
# => "WHERE (id BETWEEN 1 AND 10 AND name = 'donald' AND flag IS false)"

BB.where("id = ? OR name CONTAINS ?", 123, "john").to_sql
# => "WHERE (id = 123 OR name CONTAINS 'john')"

BB.where("id = :id AND name <> :name", id: 123, name: "trump").to_sql
# => "WHERE (id = 123 AND name <> 'trump')"

BB.where(id_gteq: 123, name_not_cont: "melania").to_sql
# => "WHERE (id >= 123 AND NOT name CONTAINS 'melania')"

BB.where(id: 123).or.where(id: 456).to_sql
# => "WHERE (id = 123) OR (id = 456)"

BB.not.where(id: 123).or.not.where(id: 456).to_sql
# => "WHERE (id <> 123) OR (id <> 456)"

BB.where(id: 123, name: "trump", reduce: :or).to_sql
# => "WHERE (id = 123 OR name = 'trump')"

OMIT RECORD IF clause

BB.omit_record_if("COUNT(payload.pages.page_name) <= ?", 80).to_sql
# => "OMIT RECORD IF (COUNT(payload.pages.page_name) <= 80)"

GROUP BY clause

BB.group(:age, :gender).to_sql
# => "GROUP BY age, gender"

BB.group("ROLLUP(year, is_male)").to_sql
# => "GROUP BY ROLLUP(year, is_male)"

BB.group_each(:age, :gender).to_sql
# => "GROUP EACH BY age, gender"

HAVING clause

BB.having(first_cont: "a", ngram_count_lt: 10000).to_sql
# => "HAVING (first CONTAINS 'a' AND ngram_count < 10000)"

BB.having("first CONTAINS ? AND negram_count < ?", "a", 10000).to_sql
# => "HAVING (first CONTAINS 'a' AND ngram_count < 10000)"

BB.having("first CONTAINS :first AND negram_count < :negram_count", first: "a", negram_count: 10000).to_sql
# => "HAVING (first CONTAINS 'a' AND ngram_count < 10000)"

BB.having(first: "a").or.not.having(first: "b").to_sql
# => "HAVING (first = 'a') OR (first <> 'b')"

ORDER BY clause

BB.order(:age, :gender).to_sql
# => "ORDER BY age, gender"

BB.order(age: :desc, gender: :asc).to_sql
# => "ORDER BY age DESC, gender ASC"

LIMIT clause

BB.limit(1000).to_sql
# => "LIMIT 1000"

BB.limit(1000).offset(500).to_sql
# => "LIMIT 1000 OFFSET 500"

Support

JOINS

Support methods:

  • cross_join
  • full_outer_join_each
  • inner_join
  • inner_join_each
  • join
  • join_each
  • left_join
  • left_join_each
  • left_outer_join
  • left_outer_join_each
  • right_join
  • right_join_each
  • right_outer_join
  • right_outer_join_each

Suffix of hash keys

For omit_record_if, where, having:

suffix means alias opposite example
cont contains contains, like not_cont, not_contains, not_like BB.where(name_cont: "banana")
eq equals eql, equals not_eq, not_eql, not_equals BB.where(id_not_eq: 123)
gt greater than undefined not_gt BB.where(id_not_gt: 123)
gteq greater than or equals to undefined not_gteq BB.where(id_not_gteq: 123)
lt less than undefined not_lt BB.where(id_not_lt: 123)
lteq less than or equals to undefined not_lteq BB.where(id_not_lteq: 123)

Contributing

Here's a quick guide:

  1. Fork the repo.
  2. Create a thoughtfully-named branch for your changes (git checkout -b my-new-feature).
  3. Install the development dependencies by running bundle install.
  4. Begin by running the tests.

    $ bundle exec rspec
    
  5. Implement something.

  6. Add tests for your changes.
  7. Make the tests pass.
  8. Commit your changes (git commit -am 'Add feature/Fix bug/improve something')
  9. Push the branch up to your fork on GitHub (git push origin my-new-feature) and from GitHub submit a pull request to b_b's master branch.

License

The gem is available as open source under the terms of the MIT License.