Loading CSV files from Google Drive to BigQuery

1
votes

I would like to have my CSV data from Google Drive completely downloaded to Bigquery, like a native table in it. However, the Table type option is not available when I want to upload my data, I have no choice but to use the "external table" option.

This is really unconvenient, as the amount of data I will get in the future will become too important for a Drive Storage.

My question is: is it possible to load a CSV file from Drive to BigQuery as a native table, or do I have to use Google Storage between the two, making the process even more complicated than it already is?

2
csvgoogle-drive-apigoogle-bigquerygoogle-cloud-storage

2 Answers

1
votes
  1. Create an external table of the files on Drive.
  2. Run a query of the form CREATE TABLE dataset.NewTable AS SELECT * FROM dataset.ExternalTable. Now you have the file contents in a BigQuery-managed table without having to copy to Cloud Storage.
0
votes

You can do it using Big Query UI only. It is done using 2 tables:

  1. Create a table which will be linked to your source (CSV or GSheets on Google Drive, CSV better for me). For this click "new table" in BigQuery, choose "Drive" source and add link to your CSV. Then finish all other setting and click "create table".

  2. Create query for this first table, click on the gear icon and in query settings in destination choose "Set a destination table for query results". Add the name for the new table, choose "Write if empty" in destination table preferences, finish all other settings and you are done. Next time if you want to add something to this table, you can update your CSV from the first table and choose "Append" or "Rewrite" options.

If you need to understand all other settings, you can read BigQuery documentation.