Query a public dataset with the BigQuery Ruby client library

Open the Cloud Shell Editor

1. In Cloud Shell, create a new Ruby project and file:

   mkdir bigquery-ruby-quickstart \
       && touch \
       bigquery-ruby-quickstart/app.rb
   

   This command creates a Ruby project that's named bigquery-ruby-quickstart and a file that's named app.rb.

2. Open the Cloud Shell Editor:

   cloudshell workspace bigquery-ruby-quickstart
   

To learn how to prepare files for queries, click Next.

Prepare files for queries

1. To open a terminal in the Cloud Shell Editor, click Open Terminal.

2. Open your project directory:

   cd bigquery-ruby-quickstart
   

3. Install the BigQuery client library for Ruby:

   gem install google-cloud-bigquery
   

   The output is similar to the following. Several lines are omitted to simplify the output.

   23 gems installed
   

To learn how to query a public dataset in BigQuery, click Next.

Query a public dataset in BigQuery

1. Click Open Editor.

2. In the Explorer pane, locate your BIGQUERY-RUBY-QUICKSTART project.

3. Click the app.rb file to open it.

4. To create a query against the bigquery-public-data.stackoverflow dataset that returns the top 10 most viewed Stack Overflow pages and their view counts, copy the following code into the app.rb file:

   require "google/cloud/bigquery"
   
   # This uses Application Default Credentials to authenticate.
   # @see https://cloud.google.com/bigquery/docs/authentication/getting-started
   bigquery = Google::Cloud::Bigquery.new
   
   sql     = "SELECT " \
             "CONCAT('https://stackoverflow.com/questions/', CAST(id as STRING)) as url, view_count " \
             "FROM `bigquery-public-data.stackoverflow.posts_questions` " \
             "WHERE tags like '%google-bigquery%' " \
             "ORDER BY view_count DESC LIMIT 10"
   results = bigquery.query sql
   
   results.each do |row|
     puts "#{row[:url]}: #{row[:view_count]} views"
   end

5. Click Open Terminal.

6. In the terminal, run the app.rb script. If you are prompted to authorize Cloud Shell and agree to the terms, click Authorize.

   ruby app.rb
   

   The result is similar to the following:

   https://stackoverflow.com/questions/35159967: 170023 views
   https://stackoverflow.com/questions/22879669: 142581 views
   https://stackoverflow.com/questions/10604135: 132406 views
   https://stackoverflow.com/questions/44564887: 128781 views
   https://stackoverflow.com/questions/27060396: 127008 views
   https://stackoverflow.com/questions/12482637: 120766 views
   https://stackoverflow.com/questions/20673986: 115720 views
   https://stackoverflow.com/questions/39109817: 108368 views
   https://stackoverflow.com/questions/11057219: 105175 views
   https://stackoverflow.com/questions/43195143: 101878 views
   

You have successfully queried a public dataset with the BigQuery Ruby client library.

To avoid incurring charges to your account and learn about next steps, click Next.