使用GP外部表及copy命令进行数据的导入

在某个项目任务中，需要将一堆的小文件（文件存放在ftp服务器上）中的内容，导入到数据库中。小文件中的内容是一个json格式的字符串。

 

考虑到使用外部表来进行数据的导入，而外部表，需要在数据库服务器本地，所以还需要将文件传到数据库服务器。

 

使用Xmanager Enterprise连上数据库服务器，开启通道，gpfdist -p 9876 -d /home/gpadmin/ -m 268435456 & 用于创建外部表。其中-p  后接端口，-d 后接目录 -m 后接可上传最大文件大小 ，&表示后台执行。

 

windows的命令也相同：gpfdist -p 8084 -d E:\temp\ftpUpload\ &

 

可以使用命令：wget http://192.168.27.7:8084/test.txt  将windows的文件传送到数据库服务器上。

 

创建外部表：CREATE EXTERNAL TABLE file_content(content text) LOCATION('gpfdist://pmd:8765/*.txt') FORMAT 'TEXT' (DELIMITER '\1' NULL ' ');

根据指定路径的所有名字中带有.txt的文件创建外部表，不同字段的分隔符为‘\1’，不过现在创建的外部表就一个字段，这个没啥意义。不过需要注意的是，如果字段的内容中含有分隔符，那么你插入值的时候，会自己添加一个‘\’。当时在做的时候，被它坑大了。

 

之后提取json中需要的部分：

create table json_content as select regexp_matches(content,'({\"数组数据\":\".*?})','g') as content,regexp_matches(content,'(\"单个的数据\":\".*?\")') as area_code from file_content

 

创建一个解析json的函数，解析直接在gp做：

函数是用第三方语言python写的，在创建时需要确认python是否有安装，安装之后运行create language plpythonu; 就可以用python写函数了。

 

CREATE or REPLACE FUNCTION public.json_parse(data text)    RETURNS text
AS $$
    import json
    try:
        mydata=json.loads(data)
    except:
        return 'parse json error'
    try:
        return  mydata['jsonkey']+"\1"+ ...  -- 自己补齐
    except:
        return 'get data error'
$$ LANGUAGE plpythonu;

创建可写的外部表：

CREATE Writable EXTERNAL TABLE hdfstmp (
    exportdata text
    )  
LOCATION (  
    'gpfdist://pmd:8765/hdfstmp'  
) FORMAT 'text' 

 

插入数据到外部表中：

insert into hdfstmp 
select nextval('seq') || '\1' || split_part(单个jsonkey，需要截取[1],'"',4) || '\1' || json_parse(content[1])  --json数组 
from json_content where 'get data error' not in (json_parse(content[1]))

 

使用copy命令到指定表中：

copy 表名(
    字段列表
    )   from '/home/gpadmin/hdfstmp' with delimiter '\1'

需要注意的是，每次导入的时候，hdfstmp文件需要先删除，否则数据一直叠加，出现重复数据。

.txt的文件处理后，也需要删除，免得下次又继续处理了。