05 Sep 20 · npack · #Bigquery ·   Bookmark   ×

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 |
+-----------+------------+---------+-------------+

npack

posted on 05 Sep 20

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds