require_once 'Google/Client.php'; require_once 'Google/Service/Bigquery.php'; define("PROJECT_ID", 'YOUR PROJECT ID'); define("DATASET_ID", "YOUR DATASET ID"); define("TABLE_ID", "DESIRED TABLE ID"); define("KEY_FILE", 'KEY FILE NAME'); define("SERVICE_ACCOUNT_NAME", 'SERVICE ACCOUNT NAME'); define("CLIENT_ID", 'CLIENT ID'); $key = file_get_contents(KEY_FILE); $client = new Google_Client(); $client->setAssertionCredentials(new Google_Auth_AssertionCredentials(SERVICE_ACCOUNT_NAME, array('https://www.googleapis.com/auth/bigquery'), $key)); $client->setClientId(CLIENT_ID); // Instantiate a new BigQuery Client $service = new Google_Service_Bigquery($client); $fields = array(array('name' => 'user_id', 'type' => 'integer', 'mode' => 'required'), array('name' => 'order_id', 'type' => 'integer', 'mode' => 'required'), array('name' => 'status', 'type' => 'integer', 'mode' => 'nullable'), array('name' => 'timestamp', 'type' => 'timestamp', 'mode' => 'nullable')); $table_reference = new Google_Service_Bigquery_TableReference(); $table_reference->setProjectId(PROJECT_ID); $table_reference->setDatasetId(DATASET_ID); $table_reference->setTableId(TABLE_ID); $schema = new Google_Service_Bigquery_TableSchema(); $schema->setFields($fields); $table = new Google_Service_Bigquery_Table(); $table->setTableReference($table_reference); $table->setSchema($schema); try { $result = $service->tables->insert(PROJECT_ID, DATASET_ID, $table); print_r($result); } catch (Google_Service_Exception $e) { echo $e->getMessage(); } ?>
private function createDestinationTable($dataset, $table_name) { $destinationTable = new Google_Service_Bigquery_TableReference(); $destinationTable->setProjectId($this->project_id); $destinationTable->setDatasetId($dataset); $destinationTable->setTableId($table_name); return $destinationTable; }
/** * @param $project_id * @param $dataSet * @param $tableId * @param $csvPath * @param $skipLeadingRows * @return array|\Exception */ public function bigQueryLoadDataExistingTable($project_id, $dataSet, $tableId, $csvPath, $skipLeadingRows = 1) { // Information about the destination table $destination_table = new \Google_Service_Bigquery_TableReference(); $destination_table->setProjectId($project_id); $destination_table->setDatasetId($dataSet); $destination_table->setTableId($tableId); // Set the load configuration, including source file(s) and schema $load_configuration = new \Google_Service_Bigquery_JobConfigurationLoad(); //'gs://YOUR_GOOGLE_CLOUD_STORAGE_BUCKET/file.csv' $load_configuration->setSourceUris(array($csvPath)); $load_configuration->setDestinationTable($destination_table); $load_configuration->skipLeadingRows = $skipLeadingRows; $load_configuration->sourceFormat = 'CSV'; $load_configuration->setwriteDisposition('WRITE_APPEND'); $job_configuration = new \Google_Service_Bigquery_JobConfiguration(); $job_configuration->setLoad($load_configuration); $job = new \Google_Service_Bigquery_Job(); $job->setKind('load'); $job->setConfiguration($job_configuration); $jobs = $this->bigqueryService->jobs; $response = $jobs->insert($project_id, $job); $jobStatus = new \Google_Service_Bigquery_JobStatus(); $status = $response->getStatus(); if ($jobStatus->count() != 0) { $err_res = $jobStatus->getErrorResult(); return $err_res->getMessage(); } $jr = $response->getJobReference(); $jobId = $jr['jobId']; $state = $status['state']; return array('JOBID' => $jobId, 'STATUS' => $state); }
/** * @param $bqTableName * * @return \Google_Service_Bigquery_TableReference * @throws \Exception */ public function getTableReference($bqTableName) { $bqTableName = $this->_trimDatasetFromTableName($bqTableName); if (!isset($this->_tableRefs[$bqTableName])) { $tableReference = new \Google_Service_Bigquery_TableReference(); $tableReference->setDatasetId($this->getDataSet()); $tableReference->setProjectId($this->bigQueryProject()); $tableReference->setTableId($bqTableName); $this->_tableRefs[$bqTableName] = $tableReference; } return $this->_tableRefs[$bqTableName]; }