时间戳

首页 > 解决方案 > 从包含没有分隔符的 json 对象的文件中加载数据

snowflake-cloud-data-platform - 从包含没有分隔符的 json 对象的文件中加载数据

问题描述

我正在尝试将 s3 中的文件中的数据加载到雪花中。由于我无法控制的原因，这些文件的内容是没有分隔符的 JSON 对象，例如，单个文件将是这样的： {"key1":"valueA","key2":"valueB"}{"key1":"valueC","key2":"valueD"}

所以我无法创建带有任何分隔符的文件格式。我有一个想法，使用'}{'缺少的括号将两个记录（JSON 对象）包装起来，但是没有这样的选项，并且记录分隔符接受单个字符。另一种方法可能是使用正则表达式来捕获单个记录，但我在文档中看不到任何内容。

有没有更好的方法来处理这个问题？

标签： snowflake-cloud-data-platform

解决方案

由于整个文件不是有效的 JSON，因此您不能将其作为半结构化数据读取。

}如果对象在内部字符处拆分，您可以将文件读取为 CSV 并稍微巧妙地重新组合：

CREATE OR REPLACE TABLE T (
  LINE_NO NUMBER IDENTITY,
  JSON TEXT
);

COPY INTO T(JSON) FROM (SELECT $1||'}' JSON FROM @my_stage/json.csv)
FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = NONE RECORD_DELIMITER='}');

SELECT REC_NO, PARSE_JSON(LISTAGG(JSON) WITHIN GROUP (ORDER BY LINE_NO)) JSON FROM (
  SELECT
    SUM(CASE WHEN NEW_OBJ AND MOD(QUOTE_QTY_AGG - QUOTE_QTY, 2) = 0 THEN 1 ELSE 0 END)
        OVER (ORDER BY LINE_NO) REC_NO, LINE_NO, JSON
  FROM (
    SELECT
      REGEXP_COUNT(JSON, '"') - REGEXP_COUNT(JSON, '\\\\"') QUOTE_QTY,
      SUM(REGEXP_COUNT(JSON, '"') - REGEXP_COUNT(JSON, '\\\\"'))
          OVER (ORDER BY LINE_NO) QUOTE_QTY_AGG,
      REGEXP_LIKE(JSON, '\\s*\\{\\s*".*') NEW_OBJ,
      LINE_NO, JSON
    FROM T
  )
)
GROUP BY REC_NO
ORDER BY REC_NO;

上面的查询将（在这里伸出我的头）解析任何“有效”（模多个对象）JSON，甚至像{"{a}": "{{b}}"}. 它通过观察以下内容来做到这一点：

  • 新对象以 开头{"，但如果{位于字符串内则不是
  • 如果前几行中未转义的双引号字符数为偶数，则当前行的开头不在字符串内

推荐阅读