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 –
