GCP – run bigquery sql from command line

Use bq cli tool to run bigquery sql statements

bq tool, which is installed as part of the gcloud sdk toolset is used to interact with Google BigQuery API.

As prerequisite you have to install and properly configure the Google Cloud SDK (gcloud) and make sure you have either a service account or setup the application default credentials for authentication.

To install bq –

$ gcloud components install bq

To execute a BQ sql statement –

$ 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'
Waiting on bqjob_r54eb78210073870d_0000016e7bcf1b58_1 ... (0s) Current status: DONE   
+---------------+-------+
|     word      | count |
+---------------+-------+
| praising      |     8 |
| Praising      |     4 |
| raising       |     5 |
| dispraising   |     2 |
| dispraisingly |     1 |
| raisins       |     1 |
+---------------+-------+

Rather than typing or copy pasting all that text , you can save it in a file and execute BQ client from the sql statement file –

$ cat /tmp/word_count.sql
SELECT
   word,
   SUM(word_count) AS count
 FROM
   `bigquery-public-data`.samples.shakespeare
 WHERE
   word LIKE "%raisin%"
 GROUP BY
   word


$ bq query --use_legacy_sql=false "$(cat /tmp/word_count.sql)"

Waiting on bqjob_r1ddd1741c19ced77_0000016e7bd821a8_1 ... (0s) Current status: DONE   
+---------------+-------+
|     word      | count |
+---------------+-------+
| praising      |     8 |
| Praising      |     4 |
| raising       |     5 |
| dispraising   |     2 |
| dispraisingly |     1 |
| raisins       |     1 |
+---------------+-------+

BQ uses the parameters set in ~/.bigqueryrc such as your default project ID and path to credentials file.

References –

https://cloud.google.com/bigquery/docs/bq-command-line-tool

https://cloud.google.com/sdk/docs/