Question

How to solve intermittent runtime errors with FeatureWriter to Snowflake?

  • 10 March 2023
  • 7 replies
  • 26 views
F

For my client we run a lot of translations on their FME server that write to Snowflake. For like 99% of the time that runs without any failures. However, occasionally we are getting the following run time error:

 

"COM.safe.fme.jdbc.TranslationFailureException: net.snowflake.client.jdbc.SnowflakeSQLException: JDBC driver internal error: {0}."

 

Unfortunately this is a rather non-descript error (I wonder what {0} is). Also this error only occurred after our upgrade from FME Server 2020 to 2022.2. Rerunning the exact same translation the next day so far did not result into this error.

 

So, does anyone have an idea how to prevent this from happening?

 

Frank

7 replies

steveatsafe
Rank
Badge +11

Hi @frankcremer​,

Thanks for posting this question.

Hmmm, I'm very curious.

I am guessing the workspace was created in 2020 and was not upgraded in 2022.2. We would hope for that. However, there have been lots of tweaks to the Snowflake format during that time and probably even the FeatureWriter.

 

Is your client using the Snowflake format or the JDBC format in the FeatureWriter?

 

Could you tell me a bit about the client's architecture?

  • FME Server is on-premise?
  • Is it FME Server installed on Windows or Linux?
  • Is the client's Snowflake Database hosted on AWS or Azure (or GCM)?

 

Can you tell me a little about the workspace?

  • I'm assuming the client is using a named database user?
  • How many records are being written?
  • Is the FeatureWriter writing to one table or many?
  • How often does this workspace/job run and for how long typically?
  • Does it error at the beginning of the write or after it has been writing records for some time?
  • Has anyone looked at the DBA side, reviewing the Snowflake History for possible causes?

 

Nothing jumps out at me, but I will share the error with a developer for any possible clues to the cause. It could be as simple as a table lock or warehouse issue on the database side.

 

If you can share the workspace and job log, that would help. If necessary feel free to open a new support case to dive deeper. We can always come back here to report our findings from the support case.

 

 

SteveatSafe
steveatsafe
Rank
Badge +11

Hi @frankcremer​,

Thanks for posting this question.

Hmmm, I'm very curious.

I am guessing the workspace was created in 2020 and was not upgraded in 2022.2. We would hope for that. However, there have been lots of tweaks to the Snowflake format during that time and probably even the FeatureWriter.

 

Is your client using the Snowflake format or the JDBC format in the FeatureWriter?

 

Could you tell me a bit about the client's architecture?

  • FME Server is on-premise?
  • Is it FME Server installed on Windows or Linux?
  • Is the client's Snowflake Database hosted on AWS or Azure (or GCM)?

 

Can you tell me a little about the workspace?

  • I'm assuming the client is using a named database user?
  • How many records are being written?
  • Is the FeatureWriter writing to one table or many?
  • How often does this workspace/job run and for how long typically?
  • Does it error at the beginning of the write or after it has been writing records for some time?
  • Has anyone looked at the DBA side, reviewing the Snowflake History for possible causes?

 

Nothing jumps out at me, but I will share the error with a developer for any possible clues to the cause. It could be as simple as a table lock or warehouse issue on the database side.

 

If you can share the workspace and job log, that would help. If necessary feel free to open a new support case to dive deeper. We can always come back here to report our findings from the support case.

 

 

And another possible solution might be to try the latest Snowflake driver.

You can get a newer version here: https://docs.snowflake.com/en/user-guide/jdbc-download

2022.2 is on 3.13.23. Possibly this is a driver bug and maybe using a newer Snowflake driver in FME would resolve it.

SteveatSafe
F

Hi @frankcremer​,

Thanks for posting this question.

Hmmm, I'm very curious.

I am guessing the workspace was created in 2020 and was not upgraded in 2022.2. We would hope for that. However, there have been lots of tweaks to the Snowflake format during that time and probably even the FeatureWriter.

 

Is your client using the Snowflake format or the JDBC format in the FeatureWriter?

 

Could you tell me a bit about the client's architecture?

  • FME Server is on-premise?
  • Is it FME Server installed on Windows or Linux?
  • Is the client's Snowflake Database hosted on AWS or Azure (or GCM)?

 

Can you tell me a little about the workspace?

  • I'm assuming the client is using a named database user?
  • How many records are being written?
  • Is the FeatureWriter writing to one table or many?
  • How often does this workspace/job run and for how long typically?
  • Does it error at the beginning of the write or after it has been writing records for some time?
  • Has anyone looked at the DBA side, reviewing the Snowflake History for possible causes?

 

Nothing jumps out at me, but I will share the error with a developer for any possible clues to the cause. It could be as simple as a table lock or warehouse issue on the database side.

 

If you can share the workspace and job log, that would help. If necessary feel free to open a new support case to dive deeper. We can always come back here to report our findings from the support case.

 

 

Hi Steve,

Thanks for your replies. Regarding the latest Snowflake driver: where and how do I install that on FME server?

Regarding your other questions:

  • Architecture:
    • The FME server is an on premise installation.
    • It runs on Windows with 2 FME Engines
    • It is hosted on Azure
  • The FeatureWriter writes to a single table
  • The workspace hasn't been upgraded to 2022.2, so it still uses 2020.2 transformer versions
  • The Snowflake connection uses an advanced configuration with a named user, private key file and password.
  • At one of the runs that failed it was writing 92367 features (read from Oracle)
  • The last log entries before the error were (replaced schema/table because of privacy):
    • Snowflake Non-Spatial Writer: Committing feature 80000
    • Snowflake Non-Spatial Writer: Uploading bulk data to stage '"<SCHEMA>".%"<TABLE>"', using file name fme-d24109c1-dde2-4802-969a-3b73cfcbe2a3
    • Snowflake Non-Spatial Writer: Copying bulk data using SQL: COPY INTO "<SCHEMA>"."<TABLE>" from '@"<SCHEMA>".%"<TABLE>"' files = ('fme-d24109c1-dde2-4802-969a-3b73cfcbe2a3.gz') FILE_FORMAT = (TYPE=CSV COMPRESSION = GZIP FIELD_OPTIONALLY_ENCLOSED_BY = '"') purge = true
    • FeatureWriter (WriterFactory): Processed 80000 of 92367 features
    • Snowflake Non-Spatial Writer: Committing feature 90000
    • Snowflake Non-Spatial Writer: Uploading bulk data to stage '"<SCHEMA>".%"<TABLE>"', using file name fme-e36bedf0-7c70-47a2-9125-429a1db6b318
    • COM.safe.fme.jdbc.TranslationFailureException: net.snowflake.client.jdbc.SnowflakeSQLException: JDBC driver internal error: {0}.
  • This error occurred after 1 minute and 15 seconds and on average this workspace runs only for 37 seconds.
  • We run this particular workspace 18 times per day, but there are also other workspaces with Snowflake FeatureWriter that give the same intermittent failures.
  • Out of the pas 876 runs, this particular workspace failed 4 times (0.5%)
  • We haven't looked into the Snowflake side as these issues start to occur after upgrade of FME Server and as far as I know there hasn't been any changes on the Snowflake side.

 

Please let met know if you'd like me to file a support case to provide further (confidential) details.

 

 

 

 

F

And another possible solution might be to try the latest Snowflake driver.

You can get a newer version here: https://docs.snowflake.com/en/user-guide/jdbc-download

2022.2 is on 3.13.23. Possibly this is a driver bug and maybe using a newer Snowflake driver in FME would resolve it.

Hi Steve,

 

Actually there is another issue with the new Snowflake driver and that it's that since the upgrade we are unable to write TIMESTAMPTZ with the FeatureWriter. In the 2020 version that was possible even though the format was not recognised. Now in 2022 it gives a warning in the logfile and refuses to accept any (even valid) data.

 

As we are using TIMESTAMPTZ as our default timestamp storage in Snowflake, how do we write this using FME?

F

Hi @steveatsafe​ ,

I saw that I didn't directly included you in my replies. Do you have any time to look at them?

FYI see:

https://community.safe.com/s/question/0D5Dm00000EukOUKAZ/how-to-solve-intermittent-runtime-errors-with-featurewriter-to-snowflake

Regards,

Frank

steveatsafe
Rank
Badge +11

@frankcremer​  I see that I didn't see the notification from this. Sorry for the radio silence.

I'd recommend creating a case at www.safe.com/support (one on one). I think we have a few back and forth before we get this one resolved (and we can report back here when we get to the outcomes/solutions).

 

You seem to have a lot going on there.

There is a discrepancy here

  • The FME server is an on premise installation.
  • It is hosted on Azure

Both of these cannot be true in regards to FME Server (unless you have two instances). Or did you mean Snowflake is hosted on Azure?

 

For this one...

  • The workspace hasn't been upgraded to 2022.2, so it still uses 2020.2 transformer versions

I would consider making a copy of the workspace and updating the Snowflake Writer to see if that helps with the failures. I don't think the Transformers are the cause (until I see logs via the case).

 

For the the TIMESTAMPTZ it seems this may be an enhancement request. I'll do some digging and once you submit a ticket we can see where we are at with this one.

SteveatSafe
steveatsafe
Rank
Badge +11

@frankcremer​ If you can file the case, address me and include the link to this question, that would be great. Look forward to helping you.

SteveatSafe

Reply


Community Stats

28,731
Posts
109,530
Replies
36,565
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.