英文:
Dynamic file name generation in snowflake
问题
我想使用存储集成将数据上传到S3存储桶。我的查询看起来像这样：
COPY INTO @storage_int/subfolder/name_${date} FROM (SELECT * FROM table1);
我无法弄清楚如何声明一个变量，使表达式可以评估为storage_int/subfolder/name_2022_13_07，或者像Python中的字符串格式化那样使用f"name_{date}"。对于日期，我想使用CURRENT_DATE()函数。
英文:
I want to upload data to an S3 bucket using a storage integration. My query looks something like that:
COPY INTO @storage_int/subfolder/name_${date} FROM (SELECT * FROM table1);
What I can't figure out is how to declare a variable, such that the expression would evaluate into storage_int/subfolder/name_2022_13_07, or what you would achieve using string formatting in Python with f"name_{date}". For date I would want to use the CURRENT_DATE() function.
答案1
得分: 2
使用Snowflake脚本和动态SQL：
开始
LET sql := 'COPY INTO @storage_int/subfolder/name_<date>
FROM (SELECT * FROM table1);';
sql := REPLACE(sql, '<date>', TO_VARCHAR(CURRENT_DATE(), 'YYYY_MM_DD'));
EXECUTE IMMEDIATE (sql);
返回 sql;
结束;
英文:
Using Snowflake Scripting and dynamic SQL:
BEGIN
LET sql := 'COPY INTO @storage_int/subfolder/name_<date>
FROM (SELECT * FROM table1);';
sql := REPLACE(sql, '<date>', TO_VARCHAR(CURRENT_DATE(), 'YYYY_MM_DD'));
EXECUTE IMMEDIATE (sql);
RETURN sql;
END;
答案2
得分: 1
根据 @Lukasz 的建议，我使用了 EXECUTE IMMEDIATE 方法，但我使用了管道来构建查询。
DECLARE
sql VARCHAR(100)
BEGIN
sql := 'COPY INTO @storage_int/subfolder/name_' || TO_VARCHAR(CURRENT_DATE) ||
' FROM (SELECT * FROM table1);';
EXECUTE IMMEDIATE sql;
END;
这种方法让我能够构建任意字符串，而不必在想要放入字符串的每个变量上使用 REPLACE 函数。
英文:
Following @Lukasz advice I used the EXECUTE IMMEDIATE approach but I built the query using pipes.
DECLARE
sql VARCHAR(100)
BEGIN
sql := 'COPY INTO @storage_int/subfolder/name_' || TO_VARCHAR(CURRENT_DATE) ||
' FROM (SELECT * FROM table1);'
EXECUTE IMMEDIATE sql;
END;
This approach let me build an arbitrary string without having to use REPLACE function on each variable I wanted to put in the string.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论