每日分享 – 向Greenplum中导入json数据

说明

本文描述问题及解决方法同样适用于 腾讯云 云数据仓库 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)

小结

这里介绍了如何向Greenplum中导入JSON数据,由于Greenplum5.0以上就增加了原生的JSON格式支持,因此可以直接对外部的JSON文件进行复杂的解析操作,一步到位的完成数据的转换和加载。

正文完