gpfdist外部表导入数据

本节通过一个简单的例子来说明如何通过gpfdist导入数据,关于NULL值处理,分割符,换行符,转义字符以及编码的处理可以参考格式化输入和输出文件章节。

使用gpfdist进行数据加载有四个步骤。

  • 启动gpfdist文件服务器
  • 把需要加载的数据文件放到gpfdist数据目录
  • 定义外部表
  • 加载数据

启动gpfdist文件服务器

在本例中,我们在一个节点上启动了两个gpfdist实例,分别使用/data1/load-files和/data2/load-files目录来存放源文件。两个gpfdist实例分别侦听在8081和8082端口。日志分别存放在/home/hawq/log1和/home/hawq/log2中。

gpfdist就像是一个文件服务器。其中-d选项指定的是目录是该文件服务器的根目录。

gpfdist -d /data1/load-files -p 8081 -l /data1/log1
gpfdist -d /data2/load-files -p 8082 -l /data2/log2

把需要加载的数据文件放到gpfdist数据目录

本例中我们创建两个文本文件expense1和expense2。在expense1中添加如下数据:

john|2017-01-20|100.00|travel|nothing
tom|2016-12-01|300|taxi|nothing

在expense2中添加如下数据:

marry|2017-01-21|1000.00|travel|nothing
kurt|2016-12-02|800|taxi|nothing

定义外部表

可以使用下面的命令创建外部表。其中需要把etlhost-1替换为gpfdist所在机器的主机名。本机的可以使用localhost。

1
2
3
4
CREATE EXTERNAL TABLE ext_expenses
        ( name text, date date, amount float4, category text, desc1 text )
LOCATION ('gpfdist://etlhost-1:8081/*', 'gpfdist://etlhost-1:8082/*')
FORMAT 'TEXT' (DELIMITER '|');

可以直接查询该外部表:

1
2
3
4
5
6
7
8
etl=# select * from ext_expenses;
 name  |    date    | amount | category |  desc1
-------+------------+--------+----------+---------
 john  | 2017-01-20 |    100 | travel   | nothing
 tom   | 2016-12-01 |    300 | taxi     | nothing
 marry | 2017-01-21 |   1000 | travel   | nothing
 kurt  | 2016-12-02 |    800 | taxi     | nothing
(4 rows)

有些时候,输入的text/csv文件有一些格式错误,默认情况下,出现错误时,整个加载会失败。如果数据量很大的话,修正错误再重新加载会浪费很多时间。如果错误可以接受的话,我们可以通过定义error table的方法隔离错误的行,把错误的行放到一个单独的error table中,而正常加载所有的正确行。下面是一个例子。

1
2
3
4
CREATE EXTERNAL TABLE ext_expenses ( name text, date date, amount float4, category text, desc1 text )
LOCATION ('gpfdist://etlhost-1:8081/*', 'gpfdist://etlhost-2:8082/*')
FORMAT 'TEXT' (DELIMITER '|')
LOG ERRORS INTO expense_errortable SEGMENT REJECT LIMIT 10 ROWS;

上面那条语句指定了error table为expense_errortable,系统会自动创建该表。SEGMENT REJECT LIMIT指的是如果有超过10行的错误,加载将报错退出。

如果一个CSV文件包含错误的格式,error table的rawdata列可能包含几个合并的错误行。例如,如果一个text列的值少了一个结束的引号,后面的行(包含换行符)将会被当作那个列的值处理。当这种情况发生时,并且该值超过64K大小时,OushuDB会把64K大小的值放入error table中做为一个单独的行,继续处理后面的行。如果这种情况发生多次,加载会失败,OushuDB会报“rejected N or more rows”错退出。

加载数据

下面这条语句可以很简单的加载ext_expenses表中的数据到expenses表中。

1
CREATE TABLE expenses AS SELECT * FROM ext_expenses;

用户也可以通过把建表语句以及加载数据分开:

1
2
CREATE TABLE expenses(name text, date date, amount float4, category text, desc1 text);
INSERT INTO expenses SELECT * FROM ext_expenses;