6

Is it possible to use an input file with the bigquery CLI? 

bq query < my_query.sql
SemperFly
  • 1,563
  • 3
  • 17
  • 31

3 Answers3

14

If you're using unix (or have cygwin installed on windows), you can use xargs:

xargs -a my_query.sql -0 bq query

Alternately you can use back-ticks:

bq query `cat my_query.sql`

Note that bq can only process one command at a time -- if your .sql script has several queries, you'll need to split the file on ;

Jordan Tigani
  • 26,089
  • 4
  • 60
  • 63
  • @jordan What if I have several queries to pass on? Does splitting the file mean I need to split and pass seperate files( one file per query) to BQ CLI? – Balajee Venkatesh Mar 15 '18 at 03:15
  • @Jordan Tigani is this documented in the public documentation? I can't seem to find references to executing a .sql file using the `bq query` command – Ryan Chase Mar 28 '18 at 23:02
3

I wasn't able to get the other solutions to work with very long and complex queries, particularly those with any kind of quote marks in them. I've had more luck piping the file into the bq tool

cat test.sql | bq query
Holt Skinner
  • 1,692
  • 1
  • 8
  • 21
0

On windows I am using this method. Prerequisite is to have each command listed in a single row. This will process each command in row one by one using bq.

C:\temp>for /F "tokens=*" %A in (batch_query.sql) do bq query %A

C:\temp>bq query select count+1 from cmdwh_bq_prod.newtable ;
Waiting on bqjob_r63bf8c82_00000163004c7fd0_1 ... (0s) Current status: DONE
+-------+
|  f0_  |
+-------+
| 20136 |
+-------+

C:\temp>bq query select count+2 from cmdwh_bq_prod.newtable ;
Waiting on bqjob_r7ffd9b2a_00000163004c9348_1 ... (0s) Current status: DONE
+-------+
|  f0_  |
+-------+
| 20137 |
+-------+

C:\temp>bq query select count+3 from cmdwh_bq_prod.newtable ;
Waiting on bqjob_r223c57a3_00000163004ca682_1 ... (0s) Current status: DONE
+-------+
|  f0_  |
+-------+
| 20138 |
+-------+

C:\temp>
C:\temp>type batch_query.sql
select count+1 from cmdwh_bq_prod.newtable ;
select count+2 from cmdwh_bq_prod.newtable ;
select count+3 from cmdwh_bq_prod.newtable ;

C:\temp>bq query select * from cmdwh_bq_prod.newtable
Waiting on bqjob_r3a363e1b_00000163004f4dba_1 ... (0s) Current status: DONE
+-------+
| count |
+-------+
| 20135 |
+-------+

C:\temp>
Ratnesh Sharma
  • 497
  • 6
  • 7
  • Just to add to the solution, I've faced the same problem and was able to use `type file.sql | bq query` without any problem even for a file with queries spread out in several lines. – chengvt Jul 31 '18 at 04:57