将带JSON列的Pandas表导入Redshift时遇JSON类型不存在错误

错误原因 #

Redshift不支持PostgreSQL的JSON原生数据类型，你遇到的type "json" does not exist错误，是因为SQLAlchemy生成了包含JSON类型的建表语句，而Redshift无法识别该类型。

解决方案 #

有两种可行的处理方式，可根据需求选择：

方式1：使用Redshift兼容的JSONB类型

Redshift支持PostgreSQL的JSONB类型，只需将SQLAlchemy指定的JSON类型替换为JSONB即可：

from sqlalchemy import create_engine
from sqlalchemy.dialects.postgresql import JSONB
import pandas as pd
import json  # 补充原代码遗漏的json模块导入

RS_creds = {}
RS_creds['host'] = %env RS_DATA_HOST
RS_creds['user'] = %env RS_DATA_USER
RS_creds['pass'] = %env RS_DATA_PASS
RS_creds['port'] = %env RS_DATA_PORT
RS_creds['db'] = %env RS_DATA_DB

test = pd.DataFrame([[1, json.dumps({"a": "A1", "b": "B1"})], [2, json.dumps({"a": "A2", "b": "B2"})]])
test.columns = ['x', 'y']

url = f"postgresql://{RS_creds['user']}:{RS_creds['pass']}@{RS_creds['host']}:5439/{RS_creds['db']}"
conn = create_engine(url)

# 替换为JSONB类型指定y列
test.to_sql('test', conn, schema='test', index=False, dtype={'y': JSONB})

方式2：将JSON字符串存储为TEXT类型

由于你已经通过json.dumps()将JSON对象转为字符串，直接将该列指定为TEXT类型存储即可，后续可通过Redshift的JSON_PARSE等函数解析使用：

from sqlalchemy import create_engine, TEXT
import pandas as pd
import json

RS_creds = {}
RS_creds['host'] = %env RS_DATA_HOST
RS_creds['user'] = %env RS_DATA_USER
RS_creds['pass'] = %env RS_DATA_PASS
RS_creds['port'] = %env RS_DATA_PORT
RS_creds['db'] = %env RS_DATA_DB

test = pd.DataFrame([[1, json.dumps({"a": "A1", "b": "B1"})], [2, json.dumps({"a": "A2", "b": "B2"})]])
test.columns = ['x', 'y']

url = f"postgresql://{RS_creds['user']}:{RS_creds['pass']}@{RS_creds['host']}:5439/{RS_creds['db']}"
conn = create_engine(url)

# 指定y列为TEXT类型
test.to_sql('test', conn, schema='test', index=False, dtype={'y': TEXT})

额外提示 #

原代码遗漏了import json模块，执行时会触发NameError，必须补充导入。

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