将BQ查询的输出分配给变量 [英] Assigning the output of a BQ query to variable

问题描述

我试图查询BigQuery中分区的总数，并将结果存储在mysql表中。我正在为此进行监控。

 ＃！/ 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>>（<<<>）values（$ {result}）
 echo $ bq_insertquery | mysql -u root -p<< dbname>> 
  

在mysql表格中插入时出现错误。这可能是因为变量$ result包含头部和值，即

变量$结果保留：包含头部的值

看起来像myquery将能够插入数据，如果我只有值。
我应该如何赋值给一个shell变量，然后使用它？

编辑：任何sql输出都包含column_name和values。我分配用于存储来自BigQuery的值的变量也包含两个值，即column_name和value。我正在寻找一些对提取唯一值有帮助的东西。 作业正在运行），并将其管道到 awk ：

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

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.

解决方案

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}'"

这篇关于将BQ查询的输出分配给变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！