topic Re: Unable to load time value into Snowflake table in Talend Studio

Unable to load time value into Snowflake table

JSD03 — Fri, 15 Nov 2024 23:15:45 GMT

Hello,

I'm unable to load a time value into a Snowflake table (field defined wth the type "time"). Im' using Talend ESB 8.0.1.

Is there a way to achieve that?

I've tried many things:

java Date type with Date schema (and HH:mm:ss pattern)
Java Time type with Object schema
java String type with String schema (after having transformed the value with TalendDate.formatDate function)

But I always get the same error message : SEVERE: State: COPY_INTO_TABLE, COPY INTO "MYTABLE_20220128_113324_058_2" FROM '@~/MY_TABLE/INSERT/20220128_113324_058_BCUCOG_2' on_error='ABORT_STATEMENT' file_format=( field_optionally_enclosed_by='"' empty_field_as_null=true), Time '1970-01-01 15:45:00.000+01:00' is not recognized

It seems that Talend send anyway a Date.

Thanks for your help

Re: Unable to load time value into Snowflake table

Anonymous — Fri, 28 Jan 2022 12:23:03 GMT

Can you show how your job is configured regarding this section? It would be useful to see the output schema (from the previous component) and the expected schema (from the Snowflake db component). I have just tried this out and it worked without issue. FYI the Date object in Java will always hold a complete date+time. It is essentially an object that holds a number.

Re: Unable to load time value into Snowflake table

JSD03 — Fri, 28 Jan 2022 13:48:02 GMT

Hello,

See attached screenshots :

Snowflake schemaJob design :

Schema (input/output of the tMap) :

Re: Unable to load time value into Snowflake table

Anonymous — Fri, 28 Jan 2022 14:24:20 GMT

I cannot reproduce this at all I am afraid. I will send you a private a message. If you can send me your Snowflake table schema DDL to me via email (so that you are not sharing it here and ONLY if you are happy doing it), I can recreate your table and try to emulate what you are doing as closely as possible. Just so that you are aware, I have tried various things to try and cause this to fail, but it works every time.

Re: Unable to load time value into Snowflake table

JSD03 — Fri, 28 Jan 2022 14:41:17 GMT

Okay... I have sent all the material I have by email. Hop it will help you to reproduce.

Re: Unable to load time value into Snowflake table

Anonymous — Fri, 28 Jan 2022 14:58:04 GMT

Hi @Jean-Sébastien DARGES,

I've not received anything as yet. It's not stuck in your outbox is it?

Regards

Richard

Re: Unable to load time value into Snowflake table

JSD03 — Fri, 28 Jan 2022 15:03:19 GMT

Strange: the email has been sent. Maybe the content type of the attachments has been blocked by your email server. Anyway I have sent to you a new email 😉

Re: Unable to load time value into Snowflake table

Anonymous — Fri, 28 Jan 2022 15:55:43 GMT

I still have no issue loading data. Can you show me the complete date format you have in your OUT__client.DATE_VENTE column date format?

Re: Unable to load time value into Snowflake table

JSD03 — Fri, 28 Jan 2022 16:01:36 GMT

The exact same that I have sent to you in the tMap :

input "yyyy-MM-dd'T'HH:mm:ss" (same issue if I use "HH:mm:ss")
output "HH:mm:ss"

Do you test that with the exact same version of Talend I'm using? (TOS ESB 8.0.1)

Can we schedule a web call in order to show you?

Re: Unable to load time value into Snowflake table

Anonymous — Fri, 28 Jan 2022 16:21:55 GMT

I am using the exact same version (ESB is my favourite of the TOS platforms). I've also tried it on the Enterprise Edition. I am using a Mac, but that shouldn't make any difference regarding this.

I have tried out the exact same date formats and it just won't fail for me.

What version of Java are you using?

Oh, another thought. Where is your date coming from? Is it from the source table (at the beginning of the job) or is it the first lookup table? Wherever it is coming from, can you show me an example of the dates that are returned and the format they are returned in?

Re: Unable to load time value into Snowflake table

JSD03 — Fri, 28 Jan 2022 16:35:57 GMT

My java version is 11.0.13 (on windows).

In the job I have sent to you I use a simple tFixedFlowInput to generate a fake row and it fails also so the source is not the root cause (in the real job the source is also a snowFlake table)

Re: Unable to load time value into Snowflake table

Anonymous — Fri, 28 Jan 2022 16:58:25 GMT

You sent me a job with 3 custom routines that don't appear to be being used in the job. However, they are in the "import" section and one was missing (CustomString). Once I sorted that missing routine reference, the job you sent me just worked. Incidentally, what are the routines used for and why were they included?

Re: Unable to load time value into Snowflake table

JSD03 — Mon, 31 Jan 2022 08:43:12 GMT

The routines are not needed (I don't know why they were exported)

Re: Unable to load time value into Snowflake table

Anonymous — Tue, 01 Feb 2022 15:35:04 GMT

Sorry about the delay in getting back to you. As I said, I have not been able to recreate this with the job you sent me. The only thing I can think of to suggest is to reinstall the Studio and try this from another installation. I know this is not ideal, but I cannot recreate this issue in the Enterprise Edition or the TOS ESB version that you are using.

Re: Unable to load time value into Snowflake table

JSD03 — Fri, 04 Feb 2022 08:16:24 GMT

Hello,

After a fresh installion of Talend it's not better: still the exact same error. The only difference between you and me is the operating system. Do you think you could try on Windows?

Re: Unable to load time value into Snowflake table

Anonymous — Fri, 04 Feb 2022 15:05:44 GMT

I'm afraid I do not have access to a Windows machine right now. I wouldn't have expected the OS to make a significant difference here though. All of the libraries used to communicate with Snowflake will be the same.

Would it be possible for you to compile the test job you sent me and try it on another machine to see what happens? I'll see if I can do the same with a colleague of mine using a Windows machine.

Re: Unable to load time value into Snowflake table

JSD03 — Fri, 04 Feb 2022 15:30:29 GMT

I have generated the job (you just have to complet the context value in "Default.properties" file).

Re: Unable to load time value into Snowflake table

Anonymous — Fri, 04 Feb 2022 16:05:49 GMT

OK, I have just got a colleague to test this and he can't recreate your issue either. He is on Windows. Were you able to get it tested on another machine where you are? If (on another machine) you get the same issues, the only difference we have is our Snowflake instances.

Re: Unable to load time value into Snowflake table

Anonymous — Fri, 04 Feb 2022 16:27:01 GMT

Sorry, I missed this. I cannot test that I am afraid. I am not permitted to run precompiled code against our internal systems. Sorry. Can you get a colleague to run it for you to test?