如何在AWS Redshift创建JSONB列及实现PostgreSQL POINT数据CSV导入

Redshift没有原生的JSONB数据类型，官方推荐使用SUPER类型作为功能对等的替代方案，支持存储任意半结构化数据（JSON对象、数组、嵌套结构），可直接执行JSON嵌套字段查询、修改等操作，性能与PostgreSQL的JSONB一致。
建表示例如下：

CREATE TABLE airports_data (
    airport_code character(3) NOT NULL,
    airport_name text NOT NULL,
    city text NOT NULL,
    coordinates super NOT NULL, -- 等效于PostgreSQL的JSONB类型
    timezone text NOT NULL
);

分两种场景处理：

场景1：尚未导出CSV，建议导出时直接转换为标准JSON格式（操作最简便） #

• 第一步：PostgreSQL端执行COPY命令导出，将POINT类型坐标转换为JSON结构字符串

COPY (
    SELECT 
        airport_code,
        airport_name,
        city,
        json_build_object('x', ST_X(coordinates), 'y', ST_Y(coordinates))::text AS coordinates,
        timezone
    FROM airports_data
) TO '/本地路径/airports_export.csv' 
WITH (FORMAT csv, HEADER false, ENCODING 'utf8', QUOTE '"');

• 第二步：将导出的CSV文件上传到与Redshift同区域的S3存储桶
• 第三步：Redshift端执行COPY命令直接导入，合法JSON字符串会自动转换为SUPER类型

COPY airports_data (airport_code, airport_name, city, coordinates, timezone)
FROM 's3://你的存储桶名称/文件路径/airports_export.csv'
IAM_ROLE 'arn:aws:iam::你的AWS账号ID:role/你的Redshift访问S3的角色名'
FORMAT CSV
QUOTE '"'
REGION 'S3桶所在区域编码';

场景2：已经导出了坐标格式为(x,y)的CSV，无需重新导出，导入时转换格式即可 #

• 第一步：创建临时表存储原始CSV数据，坐标字段先以VARCHAR类型存储

CREATE TEMP TABLE temp_airports_import (
    airport_code character(3) NOT NULL,
    airport_name text NOT NULL,
    city text NOT NULL,
    coordinate_str VARCHAR NOT NULL,
    timezone text NOT NULL
);

• 第二步：将CSV数据导入临时表

COPY temp_airports_import
FROM 's3://你的存储桶名称/文件路径/airports_export.csv'
IAM_ROLE 'arn:aws:iam::你的AWS账号ID:role/你的Redshift访问S3的角色名'
FORMAT CSV
QUOTE '"'
REGION 'S3桶所在区域编码';

• 第三步：拆分坐标字符串构造标准JSON，插入到正式表

INSERT INTO airports_data (airport_code, airport_name, city, coordinates, timezone)
SELECT 
    airport_code,
    airport_name,
    city,
    JSON_PARSE(
        '{"x":' || split_part(trim(both '()' from coordinate_str), ',', 1) 
        || ',"y":' || split_part(trim(both '()' from coordinate_str), ',', 2) 
        || '}'
    ) AS coordinates,
    timezone
FROM temp_airports_import;

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