BigQuery CLI usage

Check: Get meaningful insights with BigQuey

BigQuery offers a number of sample tables that you can run queries against. This examples run queries against the shakespeare table, which contains an entry for every word in every play.

General help

# see a list of all bq commands  
bq help  

# show info for query command  
bq help query  

Search info

# examine the shakespeare table  
bq show bigquery-public-data:samples.shakespeare

# query to see how many times the substring "raisin" appears in Shakespeare's works.
bq query --use_legacy_sql=false \
    'SELECT word, SUM(word_count) AS count
     FROM `bigquery-public-data`.samples.shakespeare
     WHERE word LIKE "%raisin%"
     GROUP BY word'

 # see the top 5 most popular names  
 bq query "SELECT name,count
             FROM babynames.names2010
             WHERE gender = 'F'
             ORDER BY count DESC
             LIMIT 5"

# list any existing dataset in your project
bq ls

Create queries and upload a dataset

# create a new dataset named babynames
bq mk babynames

Before you can build a table, you need to add the dataset to your project. The custom data file you’ll use contains approximately 7 MB of data about popular baby names, provided by the US Social Security Administration.

The bq load command creates or updates a table and loads data in a single step.

# create the table
bq load babynames.names2010 yob2010.txt name:string,gender:string,count:integer

this is the equivalent to

datasetID: babynames  
tableID: names2010  
source: yob2010.txt  
schema: name:string,gender:string,count:integer  
# confirm the table appears
bq ls babynames

# see the table schema
bq show babynames.names2010

# remove table
bq rm -r babynames