我有一個 copy into table 命令，其中包括 sql 中的多個美元符號，所有這些都被轉義。如果我從腳本中列印出實際命令并手動執行它，它會完美運行。但是當 perl 腳本執行它時，我得到一個語法錯誤。這就是我要執行的操作，命令的列印輸出，然后是 sql 錯誤，（我在腳本中分配了一個 $file 來插入資料，這樣美元符號就不會在下面被轉義）
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' 部分？
uj5u.com熱心網友回復：
看起來它將 : 解釋為系結變數值，而不是變體中的值。您是否嘗試過使用括號表示法？
https://docs.snowflake.com/en/user-guide/querying-semistructured.html#bracket-notation
我相信它看起來像
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%';
我不確定這是否有效，但如果沒有，我將洗掉答案。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/429488.html
上一篇：如何在不列印換行符的情況下使用Term::ReadLine讀取用戶輸入？
下一篇：將影像輪廓轉換為極坐標