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