2

There's not much info out there for perl dbi and snowflake so I'll give this a shot. I have a raw file, of which the headers are contained in line 1. This exact 'copy into' command works from the snowflake gui. I'm not sure if I can just take this exact command and put it into a perl prepare and execute.

COPY INTO DBTABLE.LND_LND_STANDARD_DATA FROM (
SELECT SPLIT_PART(METADATA$FILENAME,'/',4) as SEAT_ID,
 $1:auction_id_64 as AUCTION_ID_64,
 DATEADD(S,\$1:date_time,'1970-01-01') as DATE_TIME,
 $1:user_tz_offset as USER_TZ_OFFSET,
 $1:creative_width as CREATIVE_WIDTH,
 $1:creative_height as CREATIVE_HEIGHT,
 $1:media_type as MEDIA_TYPE,
 $1:fold_position as FOLD_POSITION,
 $1:event_type as EVENT_TYPE
FROM @DBTABLE.lnd.S3_STAGE_READY/pr/data/standard/data_dt=20200825/00/STANDARD_FILE.gz.parquet)
pattern = '.*.parquet'  file_format = (TYPE = 'PARQUET' SNAPPY_COMPRESSION = TRUE)
ON_ERROR = 'SKIP_FILE_10%'

my $SQL = "COPY INTO DBTABLE.LND_LND_STANDARD_DATA FROM (
SELECT SPLIT_PART(METADATA\$FILENAME,'/',4) as SEAT_ID,
 \$1:auction_id_64 as AUCTION_ID_64,
 DATEADD(S,\$1:date_time,'1970-01-01') as DATE_TIME,
 \$1:user_tz_offset as USER_TZ_OFFSET,
 \$1:creative_width as CREATIVE_WIDTH,
 \$1:creative_height as CREATIVE_HEIGHT,
 \$1:media_type as MEDIA_TYPE,
 \$1:fold_position as FOLD_POSITION,
 \$1:event_type as EVENT_TYPE
FROM \@DBTABLE.lnd.S3_STAGE_READY/pr/data/standard/data_dt=20200825/00/STANDARD_FILE.gz.parquet)
pattern = '.*.parquet'  file_format = (TYPE = 'PARQUET' SNAPPY_COMPRESSION = TRUE)
ON_ERROR = 'SKIP_FILE_10%'";
my $sth = $dbh->prepare($sql);
$sth->execute;

In looking at the output from snowflake I see this error

syntax error line 3 at position 4 unexpected '?'. syntax error line 4 at position 13 unexpected '?'.

COPY INTO DBTABLE.LND_LND_STANDARD_DATA FROM (
SELECT SPLIT_PART(METADATA$FILENAME,'/',4) as SEAT_ID,
$1? as AUCTION_ID_64,
DATEADD(S,$1?,'1970-01-01') as DATE_TIME,
$1? as USER_TZ_OFFSET,
$1? as CREATIVE_WIDTH,
$1? as CREATIVE_HEIGHT,
$1? as MEDIA_TYPE

Do I need to create bind variables for each of the columns? I usually pull in the data from the file and put them into variables but this is different as I can't read the raw file first, it has to come directly from the copy into command.

Any help would be appreciated.

James Finnegan
  • 169
  • 6
  • `$1` is a reserved variable (first parantheseed regex match). `@DBTABLE` is also a variable. You are trying to interpolate both of these into the string, which looks like it was accidental. This leads me to believe you are not using `use strict; use warnings;` or your question would be "Why am I getting these warnings?" Let us know if this is correct or not. – TLP Aug 28 '20 at 14:33
  • 1
    Try enclosing the SQL query with single quotes rather than double quotes to prevent interpolation. – GMB Aug 28 '20 at 14:34
  • Or use the `q()` operator with an unused character as delimiter, for example `q# ... #`. – TLP Aug 28 '20 at 14:40
  • Agree with @GMB. The Snowflake syntax with $ looks to the Perl compiler like variables that need to be replaced. Try single quoting the string. – Greg Pavlik Aug 28 '20 at 14:50
  • I should have mentioned that I am escaping the '$' and the '@' when assigning the $sql variable. – James Finnegan Aug 28 '20 at 15:14
  • @JamesFinnegan The code you are showing does not escape them. Maybe you are using some other code than the one you have presented. You should never, ever do that, it is not very constructive, and it is wasting all our time. – TLP Aug 28 '20 at 15:21
  • I tried replacing the double quotes with single quotes, escaping the single quotes within the select command but I'm getting the same error. Snowflake shows me for each of the header assignments where I put the name of the column in the file to map it to the table => '$1:user_tz_offset as USER_TZ_OFFSET' it returns the error ' $1? as USER_TZ_OFFSET,' instead. – James Finnegan Aug 28 '20 at 15:24
  • Sorry TLP, your right, I should have included the escape characters, my apologies. I edited the original post. – James Finnegan Aug 28 '20 at 15:26
  • You still have a variable in there called `$FILENAME`. And my bet is that you are still not using `use strict; use warnings`, which is a [very bad idea.](https://stackoverflow.com/q/8023959/725418) Please add those to your program, fix the related errors, and then come back and ask for help with the things that are still not resolved. – TLP Aug 28 '20 at 15:34
  • I am using use strict and use warnings. The $FILENAME is escaped. every $ in the sql variable is escaped along with the '@'. – James Finnegan Aug 28 '20 at 15:37
  • I do not believe you are using `use strict; use warnings`. If you had, your sql string interpolation would have produced a fatal error `Global symbol $FILENAME requires explicit package name`. Your `$1` variable would have produced a bunch of warnings `Use of uninitialized value $1 in concatenation`. Etc. Did you fail to mention this when you wrote the question the first time? If so, that was a very bad idea. Programming requires exact information, or it does not work. You should include all relevant code to produce your error, and you should list errors exactly as they appear. – TLP Aug 28 '20 at 16:09
  • As for `DBI`, it is customary to use `?` as a placeholder for arguments in the query string, and then run the `execute` command with a list of the argument values. That way, DBI handles the quotes and interpolation issues. But before we can address the DBI issues, we have to rule out simple perl issues that stem from not using strict and warnings, or not paying attention to warnings and fatal errors. – TLP Aug 28 '20 at 16:22
  • Sir, I AM using use strict and use warnings. As I stated above I was escaping all the '$'s and the '@' in the script. I used the print statement from the output to use in this posting. I should have used what I had in my script, again I apologize for that. I tried to change the string to use single quotes as well, but I get the same error. It has nothing to do with string interpolation. As far as the ? as a placeholder I agree. I have used it that way in the past, but in this situation I can't because I don't know what the headers are, they are contained in the 1st line of the raw file. – James Finnegan Aug 28 '20 at 16:46
  • The placeholder issue is what I'm asking about when it comes to reading directly from a raw file. I'm not sure the exact syntax as I usually read the file, put the data into variables names and then use the ? placeholder to load it. In this case the 1st line contains the header names and they map to a real header name in a table. So they get assigned that way and then the load occurs, but I've not done it that way before and this is the error that I'm posting. – James Finnegan Aug 28 '20 at 16:52

1 Answers1

0

It was interpreting the : as a bind variable value, rather than a value in a variant. I used the bracket notation, instead like the following:

my $SQL = "COPY INTO DBTABLE.LND_LND_STANDARD_DATA FROM (
SELECT SPLIT_PART(METADATA\$FILENAME,'/',4) as SEAT_ID,
 \$1['auction_id_64'] as AUCTION_ID_64,
 DATEADD(S,\$1['date_time,'1970-01-01') as DATE_TIME,
 \$1['user_tz_offset'] as USER_TZ_OFFSET,
 \$1:creative_width'] as CREATIVE_WIDTH,
etc...

That worked

JamesGerard
  • 13
  • 1