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