PHP upload file Google Cloud Storage and import to Google big query

1. Download Google API Client Library for PHP
2. composer install to load vender
3. Enjoy my script (now the google-api-php-client version google/apiclient:^2.0.0@RC)

require_once __DIR__.'/../vendor/autoload.php';
// [START all]
// [START build_service
$client = new Google_Client();
$key_file_location = 'privatekey-bigquery.p12';
$key = file_get_contents($key_file_location);
$client->setAuthConfig("credential.json");
$client->setScopes(array(Google_Service_Storage::DEVSTORAGE_FULL_CONTROL,Google_Service_Bigquery::BIGQUERY));

$projectId = "yourProjectId";

$storage = new Google_Service_Storage($client);

/**
 * Google Cloud Storage API request to retrieve the list of buckets in your project.
 */

/*$buckets = $storage->buckets->listBuckets($projectId);

foreach ($buckets['items'] as $bucket) {
  printf("%s\n", $bucket->getName());
} */


$bucketName = "yourBucketName";

$sfilename = "yourfile.csv"; //filename here

$obj = new Google_Service_Storage_StorageObject();

$obj->setName($sfilename);
$obj->setBucket($bucketName); //bucket name here


$filen = "yourfilename.csv";



$chunkSizeBytes = 1 * 1024 * 1024;
$client->setDefer(true);
$status = false;

$filetoupload = array('name' => $sfilename, 'uploadType' => 'multipart');

$request = $storage->objects->insert($bucketName,$obj,$filetoupload);

$media = new Google_Http_MediaFileUpload($client, $request, "text/csv", null, true, $chunkSizeBytes);
$media->setFileSize(filesize($filen));
$handle = fopen($filen, "rb");

while (!$status && !feof($handle)) {
    $chunk = fread($handle, $chunkSizeBytes);
    $status = $media->nextChunk($chunk);
}

$result = false;
if($status != false) {
    $result = $status;
}

fclose($handle);
// Reset to the client to execute requests immediately in the future.
$client->setDefer(false);

echo "Upload success to Google Cloud.<br/>";

sleep(5);

// Instantiate a new BigQuery Client
$bigqueryService = new Google_Service_Bigquery($client);

/// Information about the destination table
$destination_table = new Google_Service_Bigquery_TableReference();
$destination_table->setProjectId($projectId);
$destination_table->setDatasetId('vsmtp');
$destination_table->setTableId('vsmtp_logs');

// Information about the schema for your new table
$schema_fields = array();
$schema_fields[0] = new Google_Service_Bigquery_TableFieldSchema();
$schema_fields[0]->setName('id');
$schema_fields[0]->setType('INTEGER');

$schema_fields[1] = new Google_Service_Bigquery_TableFieldSchema();
$schema_fields[1]->setName('u_id');
$schema_fields[1]->setType('INTEGER');        

$schema_fields[2] = new Google_Service_Bigquery_TableFieldSchema();
$schema_fields[2]->setName('u_app_id');
$schema_fields[2]->setType('STRING');  

$schema_fields[3] = new Google_Service_Bigquery_TableFieldSchema();
$schema_fields[3]->setName('u_secret_key');
$schema_fields[3]->setType('STRING');  

$schema_fields[4] = new Google_Service_Bigquery_TableFieldSchema();
$schema_fields[4]->setName('m_title');
$schema_fields[4]->setType('STRING');  

$schema_fields[5] = new Google_Service_Bigquery_TableFieldSchema();
$schema_fields[5]->setName('m_desc');
$schema_fields[5]->setType('STRING');  

$schema_fields[6] = new Google_Service_Bigquery_TableFieldSchema();
$schema_fields[6]->setName('m_file');
$schema_fields[6]->setType('STRING');  

$schema_fields[7] = new Google_Service_Bigquery_TableFieldSchema();
$schema_fields[7]->setName('m_sender');
$schema_fields[7]->setType('STRING');  

$schema_fields[8] = new Google_Service_Bigquery_TableFieldSchema();
$schema_fields[8]->setName('m_mailto');
$schema_fields[8]->setType('STRING');

$schema_fields[9] = new Google_Service_Bigquery_TableFieldSchema();
$schema_fields[9]->setName('ac_id');
$schema_fields[9]->setType('STRING');

$schema_fields[10] = new Google_Service_Bigquery_TableFieldSchema();
$schema_fields[10]->setName('aca_id');
$schema_fields[10]->setType('STRING');

$schema_fields[11] = new Google_Service_Bigquery_TableFieldSchema();
$schema_fields[11]->setName('status');
$schema_fields[11]->setType('STRING');

$schema_fields[12] = new Google_Service_Bigquery_TableFieldSchema();
$schema_fields[12]->setName('desc');
$schema_fields[12]->setType('STRING');

$schema_fields[13] = new Google_Service_Bigquery_TableFieldSchema();
$schema_fields[13]->setName('created');
$schema_fields[13]->setType('TIMESTAMP');

$schema_fields[14] = new Google_Service_Bigquery_TableFieldSchema();
$schema_fields[14]->setName('updated');
$schema_fields[14]->setType('STRING');

$destination_table_schema = new Google_Service_Bigquery_TableSchema();
$destination_table_schema->setFields($schema_fields);

// Set the load configuration, including source file(s) and schema
$load_configuration = new Google_Service_Bigquery_JobConfigurationLoad();
$load_configuration->setSourceUris(array('gs://'.$bucketName.'/'.$filen));
$load_configuration->setDestinationTable($destination_table);
$load_configuration->setSchema($destination_table_schema);


$job_configuration = new Google_Service_Bigquery_JobConfiguration();
$job_configuration->setLoad($load_configuration);

$load_job = new Google_Service_Bigquery_Job();
$load_job->setKind('load');        
$load_job->setConfiguration($job_configuration);

$jobs = $bigqueryService->jobs;
$response = $jobs->insert($projectId, $load_job);


 $status = new Google_Service_Bigquery_JobStatus();
    $status = $response->getStatus();
    if ($status->count() != 0) {
        $err_res = $status->getErrorResult();
        die($err_res->getMessage());
    }

echo "<pre>";
print_r($response);
echo '</pre>';

note : ถ้าไลบราลี่มีการอัพเดทเวอร์ชั่นโค้ดด้านบน อาจใช้ไม่ได้

Related posts:

This entry was posted in BigQuery, Google API Oauth2.0. Bookmark the permalink.