在复制到命令时使用perl-dbi时出现语法错误

Question

我有一个复制到表中的命令，该命令在sql中包含多个美元符号，所有这些符号都是转义的。如果我从脚本中打印出实际的命令并手动执行，它就可以完美地工作。但是当perl脚本执行此操作时，我会出现语法错误。这就是我试图执行的，命令的打印输出，然后是sql错误，(我在脚本中分配了一个$文件，它正在插入数据，这样美元符号就不会在下面转义(

my $sql = "COPY INTO metaproc.control_table 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
FROM @DBNAME.lnd.S3_PROD_ADIP/$file)
pattern = '.*.parquet'  file_format = (TYPE = 'PARQUET' SNAPPY_COMPRESSION = TRUE)
ON_ERROR = 'SKIP_FILE_10%';";
my $sth = $dbh->prepare($sql);
$sth->execute;

COPY INTO metaproc.control_table 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
FROM @DBNAME.lnd.S3_PROD_ADIP/pr/appnexus/data_dt=20220217/19/STANDARD_20220218012146.gz.parquet)
pattern = '.*.parquet'  file_format = (TYPE = 'PARQUET' SNAPPY_COMPRESSION = TRUE)
ON_ERROR = 'SKIP_FILE_10%';

SQL编译错误：位置4处的语法错误第3行出现意外的"？"。位置13处的第4行出现语法错误，意外为"？"。位置13处的第4行出现语法错误，意外为"？"。

COPY INTO DWH_AIR.LND_APN.LND_STANDARD_IMP_EVENT 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

第3行第4位是第3行"$1"后面的问号。我不明白，为什么它要删除字符串的"：auction_id_64"部分？

Answer 1

它似乎将：解释为绑定变量值，而不是变量中的值。你试过用括号表示法吗？

https://docs.snowflake.com/en/user-guide/querying-semistructured.html#bracket-记法

我相信它看起来会有点像

COPY INTO metaproc.control_table 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
FROM @DBNAME.lnd.S3_PROD_ADIP/pr/appnexus/data_dt=20220217/19/STANDARD_20220218012146.gz.parquet)
pattern = '.*.parquet'  file_format = (TYPE = 'PARQUET' SNAPPY_COMPRESSION = TRUE)
ON_ERROR = 'SKIP_FILE_10%';

我不确定这是否有效，但如果无效，我会删除答案。