Error parsing datatype when loading data from storage to bigquery

Question

I have a large bigquery table that has an schema like:

id:integer,age:integer,name:string,id2:integer,grade1:float,....,grade100:float

The table was loaded from google storage and now I'm trying to append another file that has the same number of columns and "same type" of columns. The thing is that both files were generated using the same script, but it seems (for reasons that I still don't know) that the columns that are supposed to be integers, in the second file are floats. So, the schema of the second csv file that I'm trying to append is:

**id:float**,**age:float**,name:string,**id2:float**,grade1:float,....,grade100:float

An example of this two files would be: File in BQ table:

id age name id2  grade1 ..... grade100
1  22  John  3    6.2   .....  6.1

And the data in the file I'm trying to append is like:

id    age   name  id2  grade1 ..... grade100
3.0   22.0  Peter 5.0    6.5   .....  6.3

How can I "force" or cast the second data type of the file in storage to match the data type of the table I have already in BQ, so I don't have those conflicts in the age,id,etc?

Note: this is not the actual schema. There is no age or grades, but its a simplier way to represent the problem I'm having.

EDIT: I do not have the second file in BigQuery, thats what I'm trying to do. The steps I'm following are:

1. Click on the dataset
2. Click on "create table"
3. Select the bucket/folder/file I'm interested in
4. Go to advanced options and select the "append to table" option.

Is this the correct way to do it?

Answer 1

You have it. Literally CAST().

For example, SELECT id, age, name, CAST(id2 as FLOAT64) ...

Specifics of casting conversions can be found in https://cloud.google.com/bigquery/docs/reference/standard-sql/conversion_rules