如何在Snowflake中批量查询CSV内百万产品ID的JSON嵌套数据？

一、批量查询百万产品ID的方法 #

1. 导入产品ID到Snowflake临时表 #

首先将存储产品ID的CSV文件导入Snowflake临时表，为后续关联查询做准备：

-- 创建临时表存储产品ID
CREATE OR REPLACE TEMPORARY TABLE PRODUCT_IDS_TEMP (PRODUCT_ID VARCHAR);

-- 从外部存储（如S3/GCS）导入CSV，若使用Web UI上传可跳过此COPY语句
COPY INTO PRODUCT_IDS_TEMP
FROM '@your_stage_path/product_ids.csv'
FILE_FORMAT = (TYPE = CSV FIELD_OPTIONALLY_ENCLOSED_BY = '"' SKIP_HEADER = 1);

2. 关联查询匹配记录 #

针对PRODUCTCODES的JSON数组结构，提供两种高效关联方式：

方式一：展开数组后关联（适合需确认匹配的具体产品ID场景）

SELECT DISTINCT pt.ROWID, pt.PRODUCTCODES, pt.DESCRIPTORS
FROM PRODUCTTABLE pt,
LATERAL FLATTEN(input => pt.PRODUCTCODES) pc
JOIN PRODUCT_IDS_TEMP pit 
  ON pc.value:value::VARCHAR = pit.PRODUCT_ID
  AND pc.value:type::VARCHAR = 'product_id'; -- 精准限定type，避免误匹配其他类型的ID

使用DISTINCT避免同一ROWID因包含多个目标产品ID而重复返回。

方式二：用ARRAY_CONTAINS精准匹配（代码更简洁）

SELECT pt.ROWID, pt.PRODUCTCODES, pt.DESCRIPTORS
FROM PRODUCTTABLE pt
WHERE EXISTS (
    SELECT 1
    FROM PRODUCT_IDS_TEMP pit
    WHERE ARRAY_CONTAINS(
        OBJECT_CONSTRUCT('type', 'product_id', 'value', pit.PRODUCT_ID)::VARIANT,
        pt.PRODUCTCODES
    )
);

此方法直接匹配数组中的完整JSON对象，彻底避免模糊匹配的误判风险。

二、现有查询语句的优化方案 #

1. 替换模糊匹配为JSON精准查询 #

原LIKE '%BRB580900062%'属于全表扫描的模糊匹配，效率极低且可能误匹配无关内容。改为以下两种精准查询方式：

-- 方式1：用ARRAY_CONTAINS精准匹配单个产品ID
SELECT ROWID, PRODUCTCODES, DESCRIPTORS
FROM PRODUCTTABLE
WHERE ARRAY_CONTAINS(
    OBJECT_CONSTRUCT('type', 'product_id', 'value', 'BRB580900062')::VARIANT,
    PRODUCTCODES
);

-- 方式2：展开数组后过滤
SELECT ROWID, PRODUCTCODES, DESCRIPTORS
FROM PRODUCTTABLE,
LATERAL FLATTEN(input => PRODUCTCODES) pc
WHERE pc.value:type::VARCHAR = 'product_id'
  AND pc.value:value::VARCHAR = 'BRB580900062';

2. 添加搜索优化索引 #

针对PRODUCTCODES这种VARIANT类型字段，可开启Snowflake的搜索优化服务，加速半结构化数据的查询：

ALTER TABLE PRODUCTTABLE ADD SEARCH OPTIMIZATION ON PRODUCTCODES;

注意：此功能会产生额外存储与计算成本，适合高频查询该字段的场景。

3. 创建物化视图（高频查询场景） #

若需频繁查询产品ID对应的记录，可预先创建物化视图，将数组展开并存储关联关系：

CREATE OR REPLACE MATERIALIZED VIEW PRODUCT_PRODUCTID_MV
AS
SELECT 
  pt.ROWID, 
  pt.PRODUCTCODES, 
  pt.DESCRIPTORS, 
  pc.value:value::VARCHAR AS PRODUCT_ID
FROM PRODUCTTABLE pt,
LATERAL FLATTEN(input => pt.PRODUCTCODES) pc
WHERE pc.value:type::VARCHAR = 'product_id';

后续查询单个或批量产品ID时，直接从物化视图读取数据，性能会大幅提升。

内容的提问来源于stack exchange，提问作者Tyler Moore