从包含不带分隔符的json对象的文件中加载数据

Question

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

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

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

Answer 1

由于整个文件不是有效的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}}"}这样的对象。它通过观察以下内容来做到这一点：

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

Answer 2

Hans的方法可以工作，但它是一个相当不错的破解方法。

如果您的文件不是太大(最多几兆字节(，则有一种更简单的方法。

然后，您可以将整个文件读取为单个varchar值，并使用以下内容对其进行后处理。简而言之，我们

• 通过}{拆分字符串
• 如果需要，"修复"缺失的游标
• parse_json的结果：

这是代码：

create or replace table x(v varchar) as 
select * from values('{"a":1}{"b":2}{"c":{"cc":3}}');
select parse_json(
concat(
iff(startswith(spl.value,'{'), '', '{'),  -- add '{' if needed 
spl.value,
iff(endswith(spl.value, '}'), '', '}')  -- add '}' if needed
)
) rec 
from x, lateral split_to_table(v, '}{') spl;
-------------+
REC     |
-------------+
{           |
"a": 1    |
}           |
{           |
"b": 2    |
}           |
{           |
"c": {    |
"cc": 3 |
}         |
}           |
-------------+