CODEWITHSUNDEEP
phpinsertgoogle-bigquerygoogle-api-php-client
Jose Caro
Jose Caro

Reputation: 83

Bigquery PHP populate table with insert select job

I'm trying to populate a previously created table from an insert select job in PHP. Google documentation has a short paragraph that say the next:

"Run an asynchronous query, pass in the name of your existing table, and set writeDisposition=WRITE_APPEND."

Now I'm getting rows from bigquery using next sentences :

      // query request service
      $query = new Google_Service_Bigquery_QueryRequest();
      //armar query
      $selectQuery = "select source_year, year, month, day from publicdata:samples.natality LIMIT 100";
      if($whereQuery!=null && strlen($whereQuery)>0)$selectQuery = $selectQuery . " WHERE " . $whereQuery;
      //print_r(var_dump($selectQuery));
      $query->setQuery($selectQuery);
      $response = $service->jobs->query($project_id, $query);
      $jsonStr = "";
      if($response)if($response->getRows())$jsonStr = $response->getRows();
      print_r($jsonStr);

But I need to populate another table from the query result. Thank you for your advices. Bye

Thank you Pentium10 but I'm receiving the error message "Call to undefined method Google_Service_Bigquery_QueryRequest::setDestinationTable() " because I'm not using a job to execute the query.

I working with the next sentences to get the query result but Its didn't work:

  //query request service
  $query = new Google_Service_Bigquery_QueryRequest();

  //destination table
  $destinationTable = new Google_Service_Bigquery_TableReference();
  $destinationTable->setDatasetId($dataset);
  $destinationTable->setProjectId($project_id);
  $destinationTable->setTableId('ventastest');

  $query->setDestinationTable($destinationTable);
  $query->setWriteDisposition('WRITE_APPEND');

  //build query
  $selectQuery = "SELECT " . $fieldsQuery . " FROM " . $dataset . "." . $tableQuery;
  if($whereQuery!=null && strlen($whereQuery)>0)$selectQuery = $selectQuery . " WHERE " . $whereQuery;

  $query->setQuery($selectQuery);
  $response = $service->jobs->query($project_id, $query);
  $jsonStr = "";
  if($response)if($response->getRows())$jsonStr = $response->getRows();
  print_r($jsonStr);

Even if I attach to the service->job I receive the error message:

  $destinationTable = new Google_Service_Bigquery_TableReference();
  $destinationTable->setDatasetId($dataset);
  $destinationTable->setProjectId($project_id);
  $destinationTable->setTableId('ventastest');

  $service->jobs->setDestinationTable($destinationTable);
  $service->jobs->setWriteDisposition('WRITE_APPEND');

Thanks and bye!

Upvotes: 2

Views: 842

Answers (1)

Pentium10
Pentium10

Reputation: 208042

$destinationTable = new Google_Service_Bigquery_TableReference();
$destinationTable->setDatasetId(DATASET_ID);
$destinationTable->setProjectId(PROJECT_ID);
$destinationTable->setTableId('name_of_the_table');

$queryConfig->setDestinationTable($destinationTable);
$queryConfig->setWriteDisposition('WRITE_APPEND');

Upvotes: 2

Related Questions