I have issue with CAST or SAFE_CAST a table in BigQuery from string into INT64 or FLOAT64.
I have a csv file which contains timestamp %m/%d/%Y %I:%M:%S %p such as 10/25/2023 03:42:00 PM So that file cannot be uploaded normally to BigQuery.
Then I tried to upload the file manually with all column with string schema.
The file would look like this (example of 3 columns):
Id Date Fat 1234 10/25/2023 03:42:00 PM 25 1234 09/15/2023 05:15:00 PM null
The Fat column only contains interter data as 25 and 22 and null value.
So after upload this file, I tried to create new table based on that file by using conversion functions on BigQuery to covert each column's schema.
Id was CAST from string to INT64 (Interger) Date was change from string to DATETIME by PARSE_DATETIME and then Fat column by CAST OR SAFE_CAST function with expect it would change from string to INT64 or FLOAT64.
Everything seems work well as I saw Id was converted to INT64 and Date become DATETIME data. However all rows corresponding to the null data in Fat column were **disappeared ** in the new table
Does anyone know what happened and how can I deal with this issue to create a new table which contains all data as the original csv file?