CODEWITHSUNDEEP
phpinsertgoogle-bigquery
juvi
juvi

Reputation: 11

How do use insert statement in the google-api for BigQuery in PHP

I'm trying to create a register page where the username and password are to be stored in users table in BigQuery. I'm using PHP and tried different functions on the bigquery api for php to do this but every attempt has failed so far. Would be great if someone could assist with a code snippet for syntax or other steps that may be useful for me.

I've tried all the google reference documents available and nothing has sufficient details for insert statements. Since these are small and single inserts, I won't be need streaming inserts.

$request = new Google_Service_Bigquery_QueryRequest();

        $request->setQuery("INSERT INTO `Testing.users` (user,password,role) VALUE ('$userid','$password','$role')");

        $response = $bigquery->jobs->query($projectId, $request);

Upvotes: 1

Views: 973

Answers (1)

Pentium10
Pentium10

Reputation: 207830

It is strongly recommended to use streaming insert. It's much more faster, around max of 300ms, compared to a sync query of 2-3 seconds. 

composer require google/cloud-bigquery

However if you want an INSERT job

you can use:

use Google\Cloud\BigQuery\BigQueryClient;

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);

$sql="INSERT INTO `test_table` (`name`, `title`) VALUES ('Brent Shaffer', 'PHP Developer')";

$queryConfig = $bigQuery->query($sql)->defaultDataset($dataset);
$bigQuery->runQuery($queryConfig);

for streaming insert the example is here

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$table = $dataset->table($tableId);
$insertResponse = $table->insertRows([
    ['col1' => $val1,'col2' => $val2],// <-- this was a row
    // additional rows can go here
]);
if ($insertResponse->isSuccessful()) {
    print('Data streamed into BigQuery successfully' . PHP_EOL);
} else {
    foreach ($insertResponse->failedRows() as $row) {
        foreach ($row['errors'] as $error) {
            printf('%s: %s' . PHP_EOL, $error['reason'], $error['message']);
        }
    }
}

Upvotes: 2

Related Questions