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.