在Snowflake in语句中使用逗号分隔的列值列表

Question

我正在为行访问策略编写一些逻辑，并且有一个部门列，我想做的是将其转换为逗号分隔的列表，我可以将其用作IN语句的输入，以从特定的过滤条件中选择部门。此刻，我只是想让IN语句部分工作，我可以创建列分隔列表，如果我将结果剪切并粘贴到IN语句中，它可以工作，但当我使用创建列表的SQL时，它返回无行。

有人能看出我做错了什么吗?

WITH 
dept_map AS (SELECT (CONCAT(''', (select listagg(dept, '','') from dept_mapping_table), ''')) AS dept_list)
(
SELECT * FROM dept WHERE dept_name IN (select dept_list from debt_map)
);

谢谢你的帮助

Answer 1

我不确定我是否理解您的用例，正如NickW提到的，对于行访问策略，您可以将查询修改为简单的IN语句。

但是，如果您必须执行某种类型的解析列表IN命令，这可能会有所帮助。

CREATE TEMPORARY TABLE abc (str varchar);
INSERT INTO abc VALUES ('a'),('b'),('c');
CREATE TEMPORARY TABLE abc_xyz (str varchar);
INSERT INTO abc_xyz VALUES ('a'),('b'),('c'),('x'),('y'),('z');
--test the listagg function, use ~ instead of escaping backslash
SELECT listagg(str, '~') FROM abc;
SELECT listagg(str, '~') FROM abc_xyz;
--test out your listagg
SELECT b.value::string as str_part
FROM (select listagg(str, '~') str_agg from abc_xyz) a,
lateral flatten(input=>split(a.str_agg, '~')) b;

--get records from table abc where str in abc_xyz
SELECT *
FROM   abc 
WHERE  str in (
SELECT b.value::string as str_part
FROM (select listagg(str, '~') str_agg from abc_xyz) a,
lateral flatten(input=>split(a.str_agg, '~')) b);
--results
STR
a
b
c

最后的想法:我对行访问策略的看法是，它们应该易于理解，而不是复杂的实现。它们在Snowflake中是一个非常强大的功能，但是如果创建得不好，它们可能导致数据丢失和/或其他安全问题，所以我希望你把你的设置设置得尽可能复杂，而不是更复杂。