Issue with Google Bigquery date type

Four Stars

Issue with Google Bigquery date type

Hi community,

 

With Talend Open Studio for Big Data (Version: 6.4.1), I succeed in loading a file from Google cloud storage  (or flat file) to a Bigquery table with tBigQueryOutput.

Nethertheless, the date fields are loaded in a Bigquery TIMESTAMP type or a STRING type.

 

I do not succeed to load a date field in a Bigquery DATE type.

I tried different ways with SQL instructions (parse_date...),  or with tJava, or with tMap to transform the fields in the correct format.

But at the end, Talend loading in a Bigquery timestamp type or string type, but not in Bigquery date type.

 

Have you ever encountered this problem ?

Do you have a solution, please ?

 

Thanks&regards,

Gilles

 

   

Moderator

Re: Issue with Google Bigquery date type

Hi,

Talend supports the same data types than Big Query: https://cloud.google.com/bigquery/preparing-data-for-bigquery#datatypes

Have you tried to insert a tMap into your flow and change the datatypes from the output flow to what BigQuery only allows? After that you have to convert the types with the assignment expressions or you could use the tConvertType component.

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: Issue with Google Bigquery date type

Hi Sabrina,

Thanks for your answer.

Yes, I tried to insert different components (tMap or tJava tConvertType, ...) and I succeed to change the date type as BigQuery allows.

But at the end of the flow, when Talend loads data, the date type is a timestamp in Bigquery.

In my opinion, it is a Talend issue which not distinguish Bigquery date type and timestamp type.

Best regards,

Gilles

Two Stars

Re: Issue with Google Bigquery date type

I am running into the same issue.  Is there any solution for this?

Moderator

Re: Issue with Google Bigquery date type

Hello,

Could you please have a look at this jira issue:https://jira.talendforge.org/browse/TBD-3208?

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: Issue with Google Bigquery date type

Hello,

 

The issue concerns the Bigquery data type DATE.

I don't see this data type in the list and it is not mentionned in the answer.

 

Best regards,

Gilles

 

  

Four Stars

Re: Issue with Google Bigquery date type

This issue still exists with version 7, Date datatype is converted into Timestamp

Five Stars

Re: Issue with Google Bigquery date type

Is there a fix for this yet?
Moderator

Re: Issue with Google Bigquery date type

Hello,

Are you concerning more about Bigquery data type DATE? Are you trying to create a JDBC connection in metadata for BigQuery or use it in bigdata spark job?

Best regards

Sabrina

 

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Five Stars

Re: Issue with Google Bigquery date type

I'd like to push DATE data to Bigquery as a DATE datatype and not a TIMESTAMP so Bigquery queries don't have to be customized to exclude the time.

Two Stars

Re: Issue with Google Bigquery date type

I have had a similar issues when filtering by date range - meta base sends a SQL cast query which big query struggles with. I have not fixed it but think the solution is to store dates as type DATE in big query to avoid the cast Student British essay writers UK.

Four Stars

Re: Issue with Google Bigquery date type

The goal is to store dates as type Date in BigQuery. This issue still exists, Date datatype is converted into Timestamp.

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

How OTTO Utilizes Big Data to Deliver Personalized Experiences

Read about OTTO's experiences with Big Data and Personalized Experiences

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

Talend Integration with Databricks

Take a look at this video about Talend Integration with Databricks

Watch Now