<?php
$instruction = "bq query --replace --allow_large_results
--destination_table=ShowByNumbers.SBN '"SELECT 'Chicago Fire' as Show,
'NBC' as Network,'2016-10-15' as Start_Date, '2016-10-10' as End_Date,
COUNT(UNIQUE( TV_ID )) as Audience_Size FROM Partition_Analysis.BaseTable
WHERE _PARTITIONTIME BETWEEN TIMESTAMP('2016-10-15') AND
TIMESTAMP('2016-10-10') AND CAST(content_recognition_end_timestamp AS float)
- CAST(content_recognition_start_timestamp AS float) > 15000000 AND
network_callsign != 'INFOMERCIAL' AND Content_title='Chicago Fire' AND
Network_CallSign IN (SELECT Network_CallSign FROM
Partition_Analysis.network_mapping WHERE Network= 'NBC')'"";
echo exec($instruction);
?>
当我运行这个PHP命令时，我没有得到任何输出。
当我去google大查询时，在我的查询历史中，它显示正在执行的实际命令是:
SELECT 'Chicago Fire' as Show, 'NBC' as Network,'2016-10-15' as Start_Date, '2016-10-10' as End_Date,
COUNT(UNIQUE( TV_ID )) as Audience_Size FROM Partition_Analysis.BaseTable
WHERE _PARTITIONTIME BETWEEN TIMESTAMP('2016-10-15') AND TIMESTAMP('2016-10-10')
AND CAST(content_recognition_end_timestamp AS float) - CAST(content_recognition_start_timestamp AS float)
AND network_callsign != 'INFOMERCIAL' AND Content_title='Chicago Fire'
AND Network_CallSign IN (SELECT Network_CallSign
FROM Partition_Analysis.network_mapping WHERE Network= 'NBC')
如果你注意到，这里缺少>15000000。
为什么exec命令在查询中没有读取>15000000 ?
我想你是从Web UI执行的，而不是从api控制台。
列出最近的工作并进行比较，使用:
bq ls -j -a --max_results=15
然后你可以为每个作业id一个bq show -j <job_id>运行，为了有更多的细节，你将选择使用json响应:
bq show --format=prettyjson -j job_joQEqPwOiOoBlOhDBEgKxQAlKJQ
这将返回以下格式，其中包含您的查询，您的用户和字节处理等…
{
"configuration": {
"dryRun": false,
"query": {
"createDisposition": "CREATE_IF_NEEDED",
"destinationTable": {
"datasetId": "",
"projectId": "",
"tableId": ""
},
"query": "",
"writeDisposition": "WRITE_TRUNCATE"
}
},
"etag": "",
"id": "",
"jobReference": {
"jobId": "",
"projectId": ""
},
"kind": "bigquery#job",
"selfLink": "",
"statistics": {
"creationTime": "1435006022346",
"endTime": "1435006144730",
"query": {
"cacheHit": false,
"totalBytesProcessed": "105922683030"
},
"startTime": "1435006023171",
"totalBytesProcessed": "105922683030"
},
"status": {
"state": "DONE"
},
"user_email": ""
}
使用API，您需要传递allUsers属性来列出所有用户的作业https://cloud.google.com/bigquery/docs/reference/v2/jobs/list#allUsers