Encountering unknown data types during a bigquery import (bq load) results in a common error. To get around these errors, try these tips:
Return Null for unknown Data Types
The SAFE_CAST() function can be used to return a NULL value instead of an error when a cast is unsuccessful.
BigQuery Automatic Type Conversion - Use DataPrep
BigQuery does not have every function that you might find in other data warehouses or programming languages. Also, BigQuery's automatic type detection sometimes might coerce a data type with unwanted results.
Cloud Dataprep which is a server less service which can load the data from a file, transform it and insert into BigQuery. The ETL jobs developed using Cloud Dataprep can be scheduled to run automatically.
