shubhamKackar shubhamKackar - 1 month ago 11
PHP Question

PHP Exec command doesn't pass > 1500000 to google big query

<?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);
?>


When I run this PHP command I don't get any output.

When I go over to google big query, in my query history, it shows that the actual command that was being executed was:

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')


If you notice, the
>15000000
is missing over there.

Why isn't the exec command reading
>15000000
in the query?

Answer

I think you executed that from the Web UI and not from the api console.

to list recent jobs and compare there use:

bq  ls -j -a --max_results=15

Then you can run for each job id a bq show -j <job_id> and in order to have more details you will choose to use the json response:

bq show --format=prettyjson -j job_joQEqPwOiOoBlOhDBEgKxQAlKJQ

this returns the following format which have your query, your user and bytesprocessed etc...

{
  "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": ""
}

Using the API you need to pass allUsers property to list jobs from all users https://cloud.google.com/bigquery/docs/reference/v2/jobs/list#allUsers