CODEWITHSUNDEEP
google-bigquerygoogle-cloud-storage
iron_man83
iron_man83

Reputation: 67

bigquery Data transfer service not following CSV column order

so I am using big query's data transfer service to basically schedule loading of CSV files from GCS to the big query table. And everything was working fine until the column order in my CSV file changed. Please Note that new files are being uploaded to the GCS at regular intervals

For e.g. My csv looks like this

A,B,C
1,2,3

data in the table after the transfer

a,b,c
1,2,3

Now suppose my csv changed, and it becomes ( B column get dropped)

A,C
4,5

Now my table will look like this

a,b,c
1,2,3
4,5,null

Note here that now 5 get inserted in the C column while technically it was B's. So we can say that the data transfer service is blindly copying the CSV without looking at the column name

any idea how to solve this????

Upvotes: 0

Views: 393

Answers (1)

guillaume blaquiere
guillaume blaquiere

Reputation: 75940

When the data is integrated, the column order is taken and the column name doesn't mater. In you case, you can use the direct import of the file.

The solution, is to import your new file in a temporary table and then to use the schema metadata to get the column name and to match the name of the correct column in the target table (with a insert select)

Upvotes: 1

Related Questions