Converting date-time format for large dataset

Question

I am trying to create a table into BigQuery by importing a file. In that file the date-time format is like this 4/12/2016 12:00:00 AM. BigQuery is not creating a table because of AM. To change that format I tried spreadsheet but it couldn't handle it because the dataset is large. What other options do I have so that I can import that data into BigQuery.

Answer 1

Expounding on Jiho Choi's answer, you can use the following sample code from this GCP Documentation; this loads a CSV file from Cloud Storage using an explicit schema, hence you can explicitly define your column as string:

from google.cloud import bigquery

# Construct a BigQuery client object.
client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create.
table_id = "your-project.your_dataset.your_table_name"

job_config = bigquery.LoadJobConfig(
    schema=[
        bigquery.SchemaField("id", "STRING"),
        bigquery.SchemaField("time", "STRING"),
    ],
    skip_leading_rows=1,
    # The source format defaults to CSV, so the line below is optional.
    source_format=bigquery.SourceFormat.CSV,
)
uri = "gs://my-bucket/20220602.csv"

load_job = client.load_table_from_uri(
    uri, table_id, job_config=job_config
)  # Make an API request.

load_job.result()  # Waits for the job to complete.

destination_table = client.get_table(table_id)  # Make an API request.
print("Loaded {} rows.".format(destination_table.num_rows))

20220602.csv:

Loaded in Bigquery:

Then apply the PARSE_DATETIME() function when querying as previously stated.

Answer 2

One possible way would be importing the column as STRING and doing date parsing using the function: PARSE_DATETIME(). %p would handle string of "am, AM, pm, PM".

WITH
dataset AS (
    SELECT "4/12/2016 12:00:00 AM" as datetime_string
)
SELECT
    datetime_string,
    PARSE_DATETIME('%m/%d/%Y %H:%M:%S %p', datetime_string) as parsed_datetime,
FROM dataset
;