向Greenplum中导入json数据

岳涛 • 2023-01-02 • 云技术社区 • 281 阅读

说明

本文描述问题及解决方法同样适用于 腾讯云云数据仓库 PostgreSQL（CDWPG），其中使用到的外部对象存储为 腾讯云对象存储（Cloud Object Storage，COS）。

JSON概述

JSON是一种文本方式展示结构化数据的方式，从产生的时候开始就由于其简单好用、跨平台，特别适合HTTP下数据的传输（例如现在很流行的REST）而被广泛使用。Greenplum 5.0开始正式支持了JSON格式的数据类型，可以在SQL语句中方便的检索和使用JSON结构中的各个关键字。

导入json数据

Greenplum原生支持了JSON类型，因此有了便捷的方式导入JSON文件，例子如下：

创建外部表导入json数据

dy_test=# CREATE EXTERNAL TABLE json_demo (data json) 
dy_test-# LOCATION('cos://dy-cdwpg-1301087413.cos.ap-guangzhou.myqcloud.com/json_demo/ 
dy_test'#           secretId=<secretId>
dy_test'#           secretKey=<secretKey>')
dy_test-# FORMAT 'text';
CREATE EXTERNAL TABLE
dy_test=# 
dy_test=# SELECT * FROM json_demo;
              data               
---------------------------------
 {"name":"dy","city":"shanghai"}
(1 row)

这里对象存储COS的路径同样可以替换成系统路径，区别只是存储介质不一样。

创建内部表插入json数据

利用内置的JSON操作符，通过如下命令即可完成JSON的插入

dy_test=# CREATE TABLE json_data (name text, city text);
CREATE TABLE
dy_test=# 
dy_test=# INSERT INTO json_data
dy_test-# SELECT data->'name',
dy_test-#        data->'city'
dy_test-# FROM json_demo;
INSERT 0 1
dy_test=# 
dy_test=# SELECT * FROM json_data;
 name |    city    
------+------------
 "dy" | "shanghai"
(1 row)

知识扩展

重要的列分隔符

需要注意的是外部表在进行列切割时，会检查指定的列分隔符，因此尽量选择一个不会出现的字符当作列分隔符。

这里建议指定ASCII编码值'\\001'来作为分隔符。'\\001'本身是一种不可见字符，键盘是打不出来的。在linux终端里看到是^A，在notePad打开时看到的SOH。所以使用'\\001'它作为分隔符再合适不过，可以参考下面的建表语句。

dy_test=# CREATE EXTERNAL TABLE json_demo (data json) 
dy_test-# LOCATION('cos://dy-cdwpg-1301087413.cos.ap-guangzhou.myqcloud.com/json_demo/ 
dy_test'#           secretId=<secretId>
dy_test'#           secretKey=<secretKey>')
dy_test-# FORMAT 'TEXT' (DELIMITER E'\\001');
CREATE EXTERNAL TABLE
dy_test=# 
dy_test=# SELECT * FROM json_demo;
              data               
---------------------------------
 {"name":"dy","city":"shanghai"}
(1 row)