Load file from Google Drive into Bigquery

Question

I have an API which takes a loaded file on my website and uploads it with mimeType text/csv to Google Drive via the API.

This creates and works wonders, however, I now need to load that file into Google Bigquery as a permanent table.

I have two ways:

Preferred: Take the uploaded file and ammend the mimeType to Google Sheets - i thought drive would be clever enough to do this with a csv but apparently not - maybe there is a way.
Load the file from text/csv straight into Bigquery - i am receiving invalid URI's

I have my PHP CURL command below:

$curl = curl_init();

curl_setopt_array($curl, array(
  CURLOPT_URL => "https://bigquery.googleapis.com/bigquery/v2/projects/xxxxxxx/datasets/xxxxx/tables",
  CURLOPT_RETURNTRANSFER => true,
  CURLOPT_ENCODING => "",
  CURLOPT_MAXREDIRS => 10,
  CURLOPT_TIMEOUT => 30,
  CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
  CURLOPT_CUSTOMREQUEST => "POST",
  CURLOPT_POSTFIELDS => "{\n  \"externalDataConfiguration\": {\n    \"sourceUris\": [\n      \"https://drive.google.com/file/d/{$id}/\"\n    ],\n    \"sourceFormat\": \"CSV\",\n    \"autodetect\": true\n  },\n  \"tableReference\": {\n    \"tableId\": \"{$user}\"\n  }\n}",
  CURLOPT_HTTPHEADER => array(
    "Accept: */*",
    "Accept-Encoding: gzip, deflate",
    "Authorization: Bearer",
    "Cache-Control: no-cache",
    "Connection: keep-alive",
    //"Content-Length: 319",
    "Content-Type: application/json",
    "Host: bigquery.googleapis.com",
    "User-Agent: PostmanRuntime/7.17.1",
    "cache-control: no-cache"
  ),
));

$response = curl_exec($curl);
$err = curl_error($curl);

curl_close($curl);

if ($err) {
  echo "cURL Error #:" . $err;
} else {
  echo $response;
}

Getting back the following - using method two - however, first prize would be to getting step 1 working, changing the file type to Google Sheets - I already have an update function setting the Title so if i need to add on to that I can

"Invalid source URI: https://drive.google.com/file/d/1Zt6OMQqE3rj1PJe4HlokQ4LPNfOPBHfB/

Yun Zhang Yun Zhang · Accepted Answer · 2019-10-18T17:06:25

From documentation:

Loading data into BigQuery from Google Drive is not currently supported, but you can query data in Google Drive by using an external table.

Follow Querying Google Drive data and once you can query it, you may persist it to BigQuery using query (or your don't have to if external table works for you already):

CREATE TABLE my_dataset.persisted_table_name
AS SELECT * FROM my_external_table_pointing_to_google_drive;

Load file from Google Drive into Bigquery

1 Answers