Dates which are not in ISO format... about bigquery-schema-generator HOT 8 CLOSED

Hi, Can you give examples of the formats that you would like to support? And does bq load support them?

from bigquery-schema-generator.

For example, if date is in the format of MM/DD/YYYY or MM-DD-YYYY or MM-DD-YY. Currently bq load only supports ISO format. But I thought if we can handle that in this code and then send the ISO formatted one to bq, it will be a very useful feature. I have a file with date format as
1/27/2015 4:00

from bigquery-schema-generator.

when I say ISO formatted one to bq, it is not only the converated date but also the derived datatype that we will be sending to bq

from bigquery-schema-generator.

Identifying "MM/DD/YYYY" and other variations as a TIMESTAMP is probably straightforward (I'd have to think about any potential side-effects to be sure). But if bq load is given "4/13/2020" and the schema says that it's a TIMESTAMP, will bq load accept that as a properly formatted TIMESTAMP? If not, then it's not clear to me that making this change to bigquery-schema-generator would help much.

But, if you already have a script that converts your "MM/DD/YYYY" data to ISO 8601, then can't you feed that converted data into bigquery-schema-generator?

Just trying to understand the scope of the problem that you are trying to solve.

from bigquery-schema-generator.

If a data scientist have a csv file with date format other than iso, i wanted the application to deduce the datatype and also convert the date to ISO before sending to load to BQ since BQ handles non ISO date as String. I did not want user to take the pain to convert the date datatype. I thought we can use bigquery-schema-generator to identify any date formats and convert to ISO date. I am not sure if I am able to explain the scenario

from bigquery-schema-generator.

Even if bigquery-schema-generator is able to understand that the MM/DD/YYYY format is a TIMESTAMP, if you feed that CSV file into bq load, the documentation says that it will reject it. (See https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#timestamp_type, although I have not actually verified that the documentation is correct, so I recommend that you actually try it with one of your sample CSV files to be sure.) If bq load does not accept that format, then you are going to have to create a script to convert that format into ISO format anyway.

If you are requesting that bigquery-schema-generator should perform that data conversion, I would decline because I don't think this script should be in the business of doing data transformations. There are simply too many variations of how data can be represented in real life, and how it should be cleaned up before being imported into BigQuery. To me, doing that work is part of the job of the data scientist. For example, if the CSV data actually does contain just "MM/DD/YYYY", then the data scientist needs to figure out the timezone of that date value, and what the time component should be (e.g. "00:00" or "12:00"). bigquery-schema-generator cannot do that.

from bigquery-schema-generator.

Thanks Brian, I agree with your point. I will take care of this in my program before calling bigquery-schema-generator. I will close this.

from bigquery-schema-generator.

Ok, good luck with your work!

from bigquery-schema-generator.