将BQ查询的输出分配给变量

将BQ查询的输出分配给变量

本文介绍了将BQ查询的输出分配给变量的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我试图查询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查询的输出分配给变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-23 19:39