13

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?

Alexis Wilke
  • 19,179
  • 10
  • 84
  • 156
Echo Li
  • 143
  • 1
  • 1
  • 6

6 Answers6

13

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
Thomas
  • 174,939
  • 50
  • 355
  • 478
10

There is another way.

Try this:

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

Ex:

bq query --flagfile=/home/user/abc.sql
Cà phê đen
  • 1,883
  • 2
  • 21
  • 20
Kuan Yao
  • 167
  • 2
  • 10
  • this is a massive tip – Seamus Abshere Sep 26 '17 at 13:20
  • Apparently this no longer works `FATAL Flags parsing error: Unknown command line flag 'flagfile'` – jjmerelo Nov 24 '17 at 08:41
  • 1
    https://stackoverflow.com/questions/12323326/using-a-sql-source-file-with-the-bigquery-cli addresses this issue. If you are on linux you can use bq query `cat my_query.sql` – Rotkiv Dec 10 '17 at 20:07
  • @Kuan Yao 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:03
  • This works really well, but I'm not sure it's intended to be used that way for BigQuery. Can you shed any light perhaps @Elliott Brossard i.e is it ok to use this param for passing SQL in a file to BigQuery? – Graham Polley Mar 25 '19 at 13:21
  • 2
    It works because `--flagfile` tries to extract whatever looks like a flag, and passes the rest to standard input to `bq`. I don't think this will change, but it's kind of a weird hack. Why not just pipe the input from the file instead? `bq query --use_legacy_sql=false < my_query.sql` – Elliott Brossard Mar 25 '19 at 13:40
  • @ElliottBrossard - thanks! We're trying to invoke it from a step in Cloud Build. Our first attempt was to pipe it like you suggest, but it wouldn't work. Also tried back ticks. Maybe this deserves its own question. Agree, using `--flagfile` is hacky. – Graham Polley Mar 25 '19 at 22:44
5

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.

Jordan Tigani
  • 26,089
  • 4
  • 60
  • 63
1

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;
user3858193
  • 1,320
  • 5
  • 18
  • 50
0

This thread offers good solution 

bq query `cat my_query.sql`
Rotkiv
  • 1,051
  • 2
  • 13
  • 33
  • 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:03
-1
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\`"
Cà phê đen
  • 1,883
  • 2
  • 21
  • 20
Tarun Aggarwal
  • 11
  • 2
  • 4
    While this code may answer the question, providing additional context regarding *why* and/or *how* this code answers the question improves its long-term value. – Sneftel Jul 11 '19 at 07:19