How to dynamically change the table schema while reading csv data in BQ?

Question

I have data in txt format with several files, each file has 3 columns and few files have 4 columns, how do create the table which will only read 1st 3 columns?

i have been using BQ UI for creating tables, this is causing error when reading data with 4 columns,

EX : file1.txt

1234|sample|test
1254|sample2|test2
12324|sample3|test3

File2.txt

1234|var1|test
1254|var2|test2
12324|var3|test3

file3.txt

1234|var1|test|123242
1254|var2|test2|1321412
12324|var3|test3|1312123

Schema i have now is

field1 Int
field2 String
field3 String
filed4 Int

i have these file in gcs bucket and the external table is pointed to this location,

Error while reading table: testing-dataset-project.testing_tables.test_data, error message: CSV table references column position 3, but line starting at position:0 contains only 3 columns.

Answer 1

You can use the option Allow jagged rows as described here.