Google BigQuery - Import CSV - How to match columns?

0
votes

I have regular imports going to BigQuery via CSV which work fine.

The CSV file format is:

  • [1st line] - header = column names which match exactly the column names in the BigQuery table I am importing to

  • [rest of the lines] = the data

However, the order of the columns in my CSV has recently changed and when importing to BigQuery - the column names in CSV are not matched to column names in BigQuery table. They get basically imported in the order of the CSV columns which is wrong.

Is there a way to tell BigQuery which column from my CSV goes to which column in BigQuery table?

I am using the official PHP library.

Example: https://github.com/GoogleCloudPlatform/php-docs-samples/blob/master/bigquery/api/src/functions/import_from_file.php

2
csvgoogle-bigquery

2 Answers

2
votes

CSV import won't match the name of the columns (assuming you are using the first row to name the columns).

The best you could do is import into a different table which matches the column order of the new files, and then run a SELECT that will output the re-ordered columns into the existing table.

0
votes

If you have control over how the CSV is created, you can also use the BigQuery client to get the current order of columns in the table and then generate the file according to that.