如何快速将含任意分隔符的1TB压缩JSON文本加载至PostgreSQL

针对1TB压缩JSON文件的加载需求，以下是几种规避分隔符冲突的可行方案，核心均依托每行一个独立JSON对象（JSON Lines格式）的结构，以换行符作为记录分隔：

1. 预处理压缩文件，统一为JSON Lines格式 #

如果原始文件不是每行一个JSON对象的结构，先通过命令行工具批量转换：

# 遍历所有gz压缩文件，将格式化JSON转为单行结构后重新压缩
for file in *.json.gz; do
  zcat "$file" | jq -c '.' | gzip > "${file%.gz}_processed.gz"
done

jq -c '.'的作用是将多行缩进的JSON压缩为单行，确保每个JSON对象独占一行。

2. 用PostgreSQL原生COPY命令直接加载 #

COPY支持直接读取压缩文件，且指定FORMAT json后会自动识别每行的JSON对象，完全无需依赖字段分隔符：

• 先创建目标表（示例表存储JSONB类型数据）：

CREATE TABLE target_table (data jsonb);

• 加载单个压缩文件：

COPY target_table (data) FROM '/path/to/your/file_processed.json.gz'
WITH (FORMAT json, COMPRESSION 'gzip');

• 批量加载多个文件可通过SQL循环实现：

DO $$
DECLARE
  file_record record;
BEGIN
  FOR file_record IN SELECT pg_ls_dir('/path/to/json_files/') AS filename
  WHERE filename LIKE '%.json.gz' LOOP
    EXECUTE format(
      'COPY target_table (data) FROM %L WITH (FORMAT json, COMPRESSION ''gzip'')',
      '/path/to/json_files/' || file_record.filename
    );
  END LOOP;
END $$;

3. 结合pg_bulkload加载JSON Lines格式文件 #

若使用pg_bulkload，只需指定FORMAT = json即可基于换行符识别记录：

• 编写配置文件bulkload.conf：

INPUT = /path/to/file_processed.json.gz
OUTPUT = target_table(data)
FORMAT = json
COMPRESSION = gzip

• 执行加载命令：

pg_bulkload -c bulkload.conf

4. 大文件分批加载优化 #

针对1TB级数据，建议分批处理避免内存过载，示例shell脚本每次处理10个文件：

ls *.json.gz | xargs -n 10 -I {} sh -c 'zcat {} | jq -c "." | psql -c "COPY target_table(data) FROM STDIN WITH (FORMAT json)"'

内容的提问来源于stack exchange，提问作者Mark Muchane