Assigning the output of a BQ query to variable

Question

I am trying to query the total count of a partition in BigQuery and store the result in a mysql table. I am doing this for monitoring purpose.

#!/bin/sh
query1="bq query --format=csv 'SELECT COUNT(1) as Count FROM [dataset.tablename] WHERE _PARTITIONTIME = TIMESTAMP(\"$date\")'"
eval result=\$\($query1\)
echo "$result"

bq_insertquery="insert into <<table>>(<<column>>) values(${result})"
echo $bq_insertquery | mysql -u root -p <<dbname>>

Am getting error while insertion in mysql table. This is probably because the variable $result includes both the header and the value, i.e.

Variable $result holds: value with the header

Looks like myquery will be able to insert data, if i get only the value. How should i assign only value to a shell variable, and use it thereafter ?

Edit: Any sql output contains column_name and values. The variable i assigned to store the value from BigQuery also contains both, i.e. column_name and value. I am looking for something which would be helpfull in extracting only value.

Possible duplicate of [How to set a variable to the output from a command in Bash?](https://stackoverflow.com/questions/4651437/how-to-set-a-variable-to-the-output-from-a-command-in-bash) — l0b0, Jul 27 '17 at 13:12
It's still a duplicate. Have a look at the linked question. Extracting only the value is a completely separate issue solved [here](https://stackoverflow.com/questions/9558867/how-to-fetch-field-from-mysql-query-result-in-bash) — l0b0, Jul 27 '17 at 13:18
I checked the approaches given, including command substitution, none of it helps in extracting the value only. This is the main issue i am facing "Extracting only the value is a completely separate issue". — Logical, Jul 27 '17 at 13:35

score 4 · Accepted Answer · answered Jul 27 '17 at 23:33

Simply add the --quiet flag (ignore status updates while jobs are running), and pipe it to awk:

query1="bq query --quiet --format=csv 'SELECT COUNT(1) as Count FROM [dataset.tablename] WHERE _PARTITIONTIME = TIMESTAMP(\"$date\")' | awk '{if(NR>1)print}'"

score 0 · Answer 2 · answered Apr 15 '21 at 10:07

I would like to use jq command to parse the json output from the query. Before that you need to install jq command first. Here is the way to put count into a

result=$(echo -e "select 1 as col" | bq query --nouse_legacy_sql --format=json)
echo $result

it shows the output [{"col":"1"}]. Now it's time to use jq command to get final output.

count=$(echo $count | jq '.[0]' | jq '.col')
echo $count

In One line:

count=$(echo $(echo -e "select 1 as col" | bq query --nouse_legacy_sql --format=json) | jq '.[0]' | jq '.col')

Assigning the output of a BQ query to variable

2 Answers2

Linked