Using bq command line - examples to run SQL, check job status, get destination table using Gcloud Bigquery SDK

Gcloud SDK comes with bq utility, its a command line tool (CLI) which can be used to interact with Google Bigquery. In this guide we will see how to run commands using the bq utility

How to run a SQL Query using the BQ CLI using bq query

bq query --use_legacy_sql=false \
'select * from `bigquery-public-data.samples.shakespeare` limit 10'

Waiting on bqjob_r35189e6fbf98cf71_0000016a16517f64_1 ... (0s) Current status: DONE
+-----------+------------+---------+-------------+
|   word    | word_count | corpus  | corpus_date |
+-----------+------------+---------+-------------+
| LVII      |          1 | sonnets |           0 |
| augurs    |          1 | sonnets |           0 |
| dimm'd    |          1 | sonnets |           0 |
| plagues   |          1 | sonnets |           0 |
| treason   |          1 | sonnets |           0 |
| surmise   |          1 | sonnets |           0 |
| heed      |          1 | sonnets |           0 |
| Unthrifty |          1 | sonnets |           0 |
| quality   |          1 | sonnets |           0 |
| wherever  |          1 | sonnets |           0 |
+-----------+------------+---------+-------------+

Monitoring job status using: bq show

bq --location=US show -j 'bqjob_r35189e6fbf98cf71_0000016a16517f64_1'

Job projectId:bqjob_r35189e6fbf98cf71_0000016a16517f64_1

  Job Type    State      Start Time      Duration         User Email         Bytes Processed   Bytes Billed   Billing Tier   Labels
 ---------- --------- ----------------- ---------- ------------------------ ----------------- -------------- -------------- --------
  query      SUCCESS   13 Apr 13:50:01   0:00:00    xyz@gmail.com   6432064           10485760 

To get Jobs Details use: --format=prettyjson

bq --location=US --format=prettyjson show -j 'bqjob_r35189e6fbf98cf71_0000016a16517f64_1'
    "destinationTable": {
            "datasetId": "_678da79776e587d77e4352f89f5345e7f20849ef",
            "projectId": "mydata",
            "tableId": "anon0dd6d6c2fda3d77bec552bbc189e4ba08a136b20"
          },

bq command line - checking results of long running query:

Though most queries run fast. If there is any long running query and if the session timed out, you can always use the tableId that you got from the previous step to check the results:

bq query --use_legacy_sql=false \ 
'select * from `projectId._678da79776e587d77e4352f89f5345e7f20849ef.
anon0dd6d6c2fda3d77bec552bbc189e4ba08a136b20` limit 10'

Result:

Waiting on bqjob_r44dc9a8fdbdf61f0_0000016a1662e2ac_1 ... (0s) Current status: DONE   
+-----------+------------+---------+-------------+
|   word    | word_count | corpus  | corpus_date |
+-----------+------------+---------+-------------+
| LVII      |          1 | sonnets |           0 |
| augurs    |          1 | sonnets |           0 |
| dimm'd    |          1 | sonnets |           0 |
| plagues   |          1 | sonnets |           0 |
| treason   |          1 | sonnets |           0 |
| surmise   |          1 | sonnets |           0 |
| heed      |          1 | sonnets |           0 |
| Unthrifty |          1 | sonnets |           0 |
| quality   |          1 | sonnets |           0 |
| wherever  |          1 | sonnets |           0 |
+-----------+------------+---------+-------------+

nVector

posted on 05 Sep 20