Dataprep: creating a column to converts to Big Query timestamp type

Question

I have been trying like crazy to create a column from an existing Datetime column type that would "publish" to a Big Query "timestamp" column. I have tried every permutations of the functions "unixtime" and "unixtimeformat" functions of Dataprep to no avail: I always get Dataprep to publish either to a "string" or "datetime" column type.

Update: My workflow is the following: GCS .csv files ==> Dataprep ==> BQ.

Please help !! Thanks !!!

SOLUTION: The folks at Trifacta (the ones being Cloud Dataprep) offered the solution. Create a BQ table with a column of "Timestamp" type (let's say named "dt") and use the "Append" publication feature of Dataprep along with a source column type "Datetime" (of the same name "dt").

To add to his solution, this is the link with a concrete example: https://community.trifacta.com/s/question/0D51L0000582y30SAA/how-do-i-export-a-column-to-a-bq-timestamp-or-date-type-usecase-leverage-bqs-time-based-partitioning-to-effectively-use-the-data-lifecycle-management-functions-of-bq-ie-manage-our-costs- — Massimo, Nov 20 '18 at 14:26

Xiaoxia Lin · Answer 1 · 2018-07-05T08:21:47.907

1

Check Cloud Dataprep Supported Data Types,"timestamp" Data Type is not supported as it is.

However if the column in Dataprep is of Datetime (timestamp) type, then when you write to BigQuery it will keep Timestamp type (see BigQuery Data Type Conversions)

Here is a dummy example:

Import dataset from BigQuery a table with the schema.

Add recipe and run the job:

Export results to GCS and load it back to BigQuery.
- "dataformatt_timestamp_col" is STRING type, but
- "datetime_col" can be "published" to a Big Query TIMESTAMP column.

edited Jul 05 '18 at 08:21

answered Jul 04 '18 at 11:11

Xiaoxia Lin

736
6
16

I know what the documentation says. Have you personally tried to make this work ? If yes, then please explain. – jldupont Jul 04 '18 at 22:49
I don't see how this answers my question. I need Dataprep to export one of my Datetime column to a BQ Timestamp one. – jldupont Jul 06 '18 at 12:23
Hi @jldupont, my point is that "datatime_col" that is DATETIME column in Dataprep is published to a TIMESTAMP column in BQ. Could you share one line of your GCS .csv files here to play around? – Xiaoxia Lin Jul 12 '18 at 14:54

score 0 · Answer 2 · answered Feb 10 '20 at 11:59

Not an ideal solution but helped my case:

Create BQ table (oldTable) with the value as datetime format (named event_time for example)
Create table (newTable) from selecting all data, and convert event_time to timestamp:

CREATE TABLE
  newTable AS (
  SELECT
    col1,
    col2,
    ...,
    timestamp(event_time) as event_time,
    ...,
    colN
  FROM
    oldTable
  );

Dataprep: creating a column to converts to Big Query timestamp type

2 Answers2