1

My pipe is executing a COPY INTO command every time a parquet file is loaded into a STAGED location in AWS S3, that's working just fine (the execution).

This is my copy query: (summarized)

copy into table_name
from (
        TRY_TO_DATE(
            $1:int_field::varchar,
            'YYYYMMDD'
        ) as date_field
        from @"stage-location"/path/path2/ (FILE_FORMAT = > c000)
    ) ON_ERROR = "SKIP_FILE_1%" PATTERN = ".*part.*"

So, I convert $1:int_field (type:int) to VARCHAR (::varchar) and then parse this varchar to DATE in 'YYYYMMDD' format. That works fine for int_field that conform to this format, but when the field is 0, the load fails (only when is executed by the pipe)

When the pipe executed the COPY COMMAND by it self I checked the COPY_HISTORY and got the following error:

Can't parse '0' as date with format 'YYYYMMDD'

And of course the load fails... FAILED LOAD

Here is when the thing gets interesting: when I execute this SAME copy command by myself in the Worksheets, load goes smoothly: OK LOAD

I tried:

  • VALIDATE, VALIDATION_MODE, VALIDATE_PIPE_LOAD, but This function does not support COPY INTO statements that transform data during a load, like mine.
  • FILE_FORMAT= (FORMAT_NAME=c000 DATE_FORMAT='YYYYMMDD') ON_ERROR = "SKIP_FILE_1%" >>> SAME ISSUE, the file's only loaded when I execute the COPY COMMAND with my own hand.
  • I thought the problem was the "ON_ERROR" option, but I can't erase it (I think), I need to filter the REAL errors :(

Maybe is some SESSION problem or so, I read smthg about DATE_INPUT_FORMAT, but I can't detect the exact problem to solve this.

Can someone help me? Thanks!

Emilio Perea
  • 31
  • 1
  • Do you have a sample file to see if I can reproduce? – Felipe Hoffa Nov 16 '20 at 17:23
  • What do you want to happen when the input value is zero? As TRY_TO_DATE returns null if it can't convert the value, why not wrap this statement in an IFNULL statement? – NickW Nov 16 '20 at 21:02
  • @FelipeHoffa No, I'm sorry but it's confidential from the company I work for ... If I have some time, I'm going to replicate smth similar – Emilio Perea Nov 16 '20 at 21:16
  • @NickW when the input value is 0 I want the file to load anyway into the table, with that field like NULL, it works, but only when I execute the COPY INTO query, if PIPE does it, it doesn't work :( – Emilio Perea Nov 16 '20 at 21:19
  • 1
    Hi - please can you add the complete CREATE PIPE statement, including the COPY statement? You can mask anything that might be confidential, such as AWS_SNS_TOPIC. Also, when you successfully run just the copy statement what gets loaded into the target when the source has a zero value? – NickW Nov 16 '20 at 22:12
  • @NickW yes, I'm going to do that later so you can help me out. When I run the copy statement and the source is 0 the target loads with NULL as expected. – Emilio Perea Nov 16 '20 at 23:11

1 Answers1

1

On my tests, I see that it fails all the time (even the stand-alone COPY does not work). On the other hand, querying from the stage file works as expected.

select TRY_TO_DATE(
      $1::varchar,
      'YYYYMMDD'
) as date_field
from @my_stage; -- works

copy into testing
from (
select 
   TRY_TO_DATE(
      $1::varchar,
      'YYYYMMDD'
)
  from @my_stage
) ON_ERROR = "SKIP_FILE_1%"; -- fails with "Date '0' is not recognized"

It seems there is an issue with TRY_TO_DATE when running as part of a COPY transformation. By the way, I tested TRY_TO_NUMBER, and it works.

You should submit a case to the Snowflake support, so the development team can investigate the issue.

Gokhan Atil
  • 9,278
  • 2
  • 11
  • 24
  • Thanks for testing the issue, I'm gonna contact the Snowflake support team and post their answer here so I can help others! – Emilio Perea Nov 17 '20 at 15:25