CODEWITHSUNDEEP
google-bigquery
Echo Li
Echo Li

Reputation: 173

Google Bigquery BQ command line execute query from a file

I use the bq command line tool to run queries, e.g:

bq query "select * from table"

What if I store the query in a file and run the query from that file? is there a way to do that?

Upvotes: 16

Views: 28970

Answers (7)

Yogabalakrishnan Kannan
Yogabalakrishnan Kannan

Reputation: 1

You can run

c:\AppData\Local\Google\Cloud SDK>bq query --use_legacy_sql=false < ddl.sql

ddl.sql file content in multiline,

Execute immediate """CREATE OR REPLACE PROCEDURE `BQ_PROC.child5_procedure`() 
BEGIN 
CALL `BQ_UTIL.sp_insert_batch_status_log`(0, 0, 'Yoga Test', 'Child 3 called', 'Child 3', 0); 
END;"""

Upvotes: 0

Kuan Yao
Kuan Yao

Reputation: 165

There is another way.

Try this:

bq query --flagfile=[your file with absolute path]

Ex:

bq query --flagfile=/home/user/abc.sql

Upvotes: 9

Tarun Aggarwal
Tarun Aggarwal

Reputation: 11

bq query --replace --use_legacy_sql=false --destination_table=syw-analytics:store_ranking.SHC_ENGAGEMENT_RANKING_TEST
"SELECT RED,
        DEC,
        REDEM
from `\syw.abc.xyz\`"

Upvotes: -1

user3858193
user3858193

Reputation: 1518

If you are using standard sql (Not Legacy Sql).

**Steps:**

1. Create .sql file (you can you any extension).
2. Put your query in that. Make sure (;) at the end of the query.
3. Go to command line ad execute below commands.
4. If you want add parameter then you have to specify sequentially.

Example:

bq query --use_legacy_sql=False  "$(cat /home/airflow/projects/bql/query/test.sql)"

for parameter
bq query --use_legacy_sql=False  --parameter=country::USA  "$(cat /home/airflow/projects/bql/query/test.sql)"

cat >/home/airflow/projects/bql/query/test.sql
select * from l1_gcb_trxn.account where country=@country;

Upvotes: 1

Thomas
Thomas

Reputation: 181715

The other answers seem to be either outdated or needlessly brittle. As of 2019, bq query reads from stdin, so you can just redirect your file into it:

bq query < myfile.sql

Query parameters are passed like this:

bq query --parameter name:type:value < myfile.sql

Upvotes: 18

Rotkiv
Rotkiv

Reputation: 1143

This thread offers good solution 

bq query `cat my_query.sql`

Upvotes: 0

Jordan Tigani
Jordan Tigani

Reputation: 26617

You can run a query from a text file with a little bit of shell magic:

$ echo "SELECT 17" > qq.txt
$ bq query "$(cat qq.txt)"

Waiting on bqjob_r603d91b7e0435a0f_00000150c56689c6_1 ... (0s) Current status: DONE   
+-----+
| f0_ |
+-----+
|  17 |
+-----+

Note this works on any unix variant (including mac). If you're using a windows, this should work under powershell but not the default cmd prompt.

Upvotes: 5

Related Questions