首页 > 代码库 > 用HAWQ轻松取代传统数据仓库（十一） —— 数据管理

用HAWQ轻松取代传统数据仓库（十一） —— 数据管理

2024-09-12 23:13:02   218人阅读

一、基本操作
1. INSERT
        在常用的增删改查数据库操作中，HAWQ仅支持INSERT和SELECT两种，不支持UPDATE和DELETE，这主要是因为HDFS是一个只能追加数据而不能更新的文件系统。SELECT语句最熟悉不过，它应该是数据库中最常用的语句了，在下一篇“查询优化”时再进一步讨论。INSERT语句用于创建表行，该命令需要表名和表中每个列的值。在HAWQ中，该命令有四种用法，其中三种是SQL中的常规用法，另一种是对标准SQL的扩展。
（1）指定列名与列值
        可以按任何顺序指定列名，列值与列名一一对应。
insert into products (name, price, product_no) values (‘Cheese‘, 9.99, 1);
（2）仅指定列值
        如果不指定列名，数据值列表的个数与顺序必须与表中的列保持一致。
insert into products values (1, ‘Cheese‘, 9.99);
（3）使用SELECT语句
        通常数据值是字符常量，但是也可以使用标量表达式或查询语句。此时SELECT出的列表必须与插入表的列一致。
insert into products select * from tmp_products where price < 100;
insert into products (name, price, product_no) select * from tmp_products where price < 100;
        如果列有缺省值，可以在插入语句中省略该列名而使用缺省值。
insert into products (name, product_no) select name, product_no from tmp_products where price < 100;
（4）显式一次插入多行
        这个SQL扩展与MySQL类似，一条INSERT语句中可以显式指定多条需要插入的记录。
db1=# create table t (a int);
CREATE TABLE
db1=# insert into t values (1),(2),(3);
INSERT 0 3
db1=# select * from t;
 a 
---
 1
 2
 3
(3 rows)
        如果需要快速插入大量数据，最好使用后面介绍的外部表或COPY命令，这些数据装载机制比INSERT更有效。

2. 整理系统目录表
（1）VACUUM
        对于数据库中的对象，如表、视图、函数等总是在不断地执行新增、删除、修改等操作，相应地会引起HAWQ系统目录表的增删改。因此对系统目录良好的空间管理非常重要，这能够给性能带来大幅提高。当删除一个数据库对象时，并不立即释放该条目在系统目录表中所占用的空间，而是在该条目上设置一个过期标志。只有当执行VACUUM命令时，才会物理删除那些已经标识为过期的数据条目并释放空间。应该定期执行VACUUM命令移除过期行。VACUUM命令也可收集表级的统计信息，如行数和页数等。
db1=# -- 整理pg_class系统表
db1=# vacuum pg_class;
VACUUM
db1=# -- 整理并分析pg_class系统表
db1=# vacuum analyze pg_class;
VACUUM
db1=# -- 整理并分析pg_class系统表的指定列
db1=# vacuum analyze pg_class (relname,relam,relpages,reltuples,relkind,relnatts,relacl,reloptions);
VACUUM
（2）配置空余空间映射
        过期行被保存在名为free space map的结构中。free space map必须足够大，能保存数据库中的所有过期行。VACUUM命令不能回收超过free space map以外过期行占用的空间。注意，HAWQ中不推荐使用VACUUM FULL，因为对于大表，该操作可能造成不可接受的执行时间。
db1=# vacuum full pg_class;
NOTICE:  ‘VACUUM FULL‘ is not safe for large tables and has been known to yield unpredictable runtimes.
HINT:  Use ‘VACUUM‘ instead.
VACUUM
        free space map的大小由以下服务器配置参数所控制：
  • max_fsm_pages
  • max_fsm_relations
        max_fsm_pages（整数）设置free space map跟踪的最大页数。每个页槽位占用6字节的共享内存。至少要设置为16 * max_fsm_relations。在初始安装数据库时，系统依照可用内存的数量设置该参数的缺省值。设置该参数后需要重启HAWQ使其生效。
[gpadmin@hdp3 ~]$ hawq config -s max_fsm_pages
GUC		: max_fsm_pages
Value		: 200000
        max_fsm_relations（整数）设置free space map跟踪的最大表数。每个表槽位占用7字节左右的共享内存。缺省值为1000。设置该参数后需要重启HAWQ使其生效。
[gpadmin@hdp3 ~]$ hawq config -s max_fsm_relations
GUC		: max_fsm_relations
Value		: 1000
3. 其它操作
        与Oracle、MySQL等常用数据库系统一样，HAWQ支持MVCC并发访问控制和非锁定读。支持ACCESS SHARE、ROW EXCLUSIVE、SHARE UPDATE EXCLUSIVE、SHARE、SHARE ROW EXCLUSIVE、ACCESS EXCLUSIVE六种锁模式。并支持读非提交、读提交、可重复读、串行化四种标准的事务隔离级别。因为数据仓库应用中的ETL操作通常为一个独立的后台程序，几乎没有并发调用，而前台的分析类应用大都是只读操作，所以这里不展开讨论HAWQ的并发控制与事务处理。

二、数据的装载与卸载
        HAWQ既支持大数据量、多个文件的高性能并行数据装载卸载操作，又支持小数据量、单个文件、非并发的数据导入导出。HAWQ可读写多种外部数据源，包括本地文本文件、HDFS或Web服务器。
        在“用HAWQ轻松取代传统数据仓库（九） —— 外部数据”中详细说明了PXF外部表，这里将介绍使用另外一种协议——gpfdist的外部表。PXF外部表针对HDFS上的文件访问，而gpfdist用于对本地文件的并行访问。gpfdist应用是一个HAWQ的并行文件分布程序。它是一个操作外部表的HTTP服务器，使HAWQ的segment可以从多个文件系统的外部表并行装载数据。可以在多个不同的主机上运行gpfdist实例，并能够并行使用它们。
        外部Web表提供了对动态数据地访问。它支持使用HTTP协议从URL访问数据，或者通过运行在segment上的脚本输出数据。
        hawq load应用使用一个YAML格式的控制文件，自动完成数据装载任务。
        HAWQ中的COPY SQL命令可在master主机上的文本文件与HAWQ数据库表之间转移数据。

        所选择的数据装载方法依赖于数据源的特性，如位置、数据量、格式、需要的转换等。在最简单的情况下，一条COPY命令就可将HAWQ主实例上的文本文件装载到表中。对于少量数据，这种方式不需要更多步骤，并提供了良好的性能。COPY命令在HAWQ master主机上的单个文件与数据库表之间拷贝数据。这种方式拷贝的数据量受限于文件所在系统所允许的单一文件最大字节数。对于大数据集，更为有效的数据装载方式是利用HAWQ的MPP架构，利用多个HAWQ segments并行装载数据。该方式允许同时从多个文件系统装载数据，实现很高的数据传输速率。用gpfdist创建的外部表会使用所有HAWQ segment装载或卸载数据，并且完全是并行操作的。
        无论使用哪种方法，装载完数据都应运行ANALYZE。如果装载了大量表数据，运行ANALYZE或VACUUM ANALYZE（只对系统目录表）为查询优化器更新表的统计信息。使得当前统计信息保证优化器做出最好的查询计划，避免由于数据的增长或缺失的统计信息导致性能问题。

1. gpfdist
        gpfdist是HAWQ的提供的一种文件服务器，提供了良好的性能并且非常容易运行。gpfdist利用HAWQ系统中的所有segment读写外部表。
（1）并行性
        gp_external_max_segs服务器配置参数控制可被单一gpfdist实例同时使用的虚拟段的数量，缺省值为64。在master实例的hawq-site.xml文件中设置此参数。
[gpadmin@hdp3 ~]$ hawq config -s gp_external_max_segs
GUC		: gp_external_max_segs
Value		: 64
        用户可能需要设置虚拟段的数量，例如一些用于处理外部表数据文件，一些执行其它的数据库处理。hawq_rm_nvseg_perquery_perseg_limit和hawq_rm_nvseg_perquery_limit参数控制并行虚拟段的数量，它们限制集群中一个gpfdist外部表上执行查询时使用的最大虚拟段数。

（2）启动与停止
        可以选择在HAWQ master以外的其它机器上运行gpfdist，例如一个专门用于ETL处理的主机。使用gpfdist命令启动gpfdist。该命令位于HAWQ master主机和每个segment主机的$GPHOME/bin目录中。可以在当前目录位置或者指定任意目录启动gpfdist，缺省的端口是8080。下面是一些启动gpfdist的例子。
  • 处理当前目录中的文件，使用缺省8080端口。
    [gpadmin@hdp4 ~]$ gpfdist &
  • /home/gpadmin/load_data/是要处理的文件目录，8081是HTTP端口号，/home/gpadmin/log是消息与错误日志文件，进程在后台运行。
    [gpadmin@hdp4 ~]$ gpfdist -d /home/gpadmin/load_data/ -p 8081 -l /home/gpadmin/log &
  • 在同一个ETL主机上运行多个gpfdist实例，每个实例使用不同的目录和端口。
    [gpadmin@hdp4 ~]$ gpfdist -d /home/gpadmin/load_data1/ -p 8081 -l /home/gpadmin/log1 &
[gpadmin@hdp4 ~]$ gpfdist -d /home/gpadmin/load_data2/ -p 8082 -l /home/gpadmin/log2 &
        如果在启动时报出类似没有libapr-1.so.0或libyaml-0.so.2文件的错误，则需要安装相应的包。
yum install apr
yum install libyaml
        HAWQ没有提供停止gpfdist的特殊命令，直接使用kill停止gpfdist进程。
[gpadmin@hdp4 ~]$ ps -ef |grep gpfdist |grep -v grep | awk ‘{print $2}‘|xargs kill -9

（3）排错
  • 虚拟段在运行时访问gpfdist，因此需要保证HAWQ segment主机能访问gpfdist实例。gpfdist实际上是一个Web服务器，可以在HAWQ的每个主机（master和segment）上执行下面的命令测试连通性：
    [gpadmin@hdp3 ~]$ wget http://gpfdist_hostname:port/filename
  • CREATE EXTERNAL TABLE定义必须为gpfdist提供正确的主机名、端口号和文件名。
2. gpfdist外部表
（1）gpfdist协议
        在URI中使用gpfdist://协议引用一个运行的gpfdist实例。在外部数据文件所在的主机上运行gpfdist命令。gpfdist自动解压缩gzip（.gz）和bzip2（.bz2）文件。可以使用通配符（*）或其它C语言风格的模式匹配多个需要读取的文件。指定的文件应该位于启动gpfdist实例时指定的目录下。
        所有虚拟段并行访问外部文件，虚拟段的数量受gp_external_max_segments参数、gpfdist的位置列表长度，以及hawq_rm_nvseg_perquery_limit和hawq_rm_nvseg_perquery_perseg_limit参数影响。在CREATE EXTERNAL TABLE语句中使用多个gpfdist数据源可扩展外部表扫描性能。

（2）创建gpfdist外部表

        为了创建一个gpfdist外部表，需要指定输入文件的格式和外部数据源的位置。使用以下协议之一访问外部表数据源。一条CREATE EXTERNAL TABLE语句中使用的协议必须唯一，不能混用多个协议。
  • gpfdist:// —— 指定主机上的一个目录，用于存储外部数据文件。HAWQ的所有segment可并行访问该目录下的文件。
  • gpfdists:// —— gpfdist的安全版本。
        使用gpfdist外部表的步骤如下：
  1. 定义外部表。
  2. 启动gpfdist文件服务器。
  3. 将数据文件放置于外部表定义中指定的位置。
  4. 使用SQL命令查询外部表。
        与PXF外部表一样，HAWQ提供可读与可写两种gpfdist外部表，但一个外部表不能既可读又可写。

（3）gpfdist外部表示例
        例1：单gpfdist实例外部表
        启动gpfdist。
[gpadmin@hdp4 ~]$ gpfdist -p 8081 -d /home/gpadmin/staging -l /home/gpadmin/log &
        使用gpfdist协议创建只读外部表example1，文件以管道符（|）作为列分隔符。
db1=# create external table example1
db1=#         ( name text, date date, amount float4, category text, desc1 text )
db1=#     location (‘gpfdist://hdp4:8081/*‘)
db1=#     format ‘text‘ (delimiter ‘|‘);
CREATE EXTERNAL TABLE
        准备文本文件数据。
[gpadmin@hdp4 unload_data1]$ cd /home/gpadmin/staging
[gpadmin@hdp4 staging]$ more a.txt 
aaa|2017-01-01|100.1|aaa|aaa
bbb|2017-01-02|100.2|bbb|bbb
[gpadmin@hdp4 staging]$ more b.txt 
aaa|2017-03-01|200.1|aaa|aaa
bbb|2017-03-02|200.2|bbb|bbb
        查询外部表。
db1=# select * from example1;
 name |    date    | amount | category | desc1 
------+------------+--------+----------+-------
 aaa  | 2017-01-01 |  100.1 | aaa      | aaa
 bbb  | 2017-01-02 |  100.2 | bbb      | bbb
 aaa  | 2017-03-01 |  200.1 | aaa      | aaa
 bbb  | 2017-03-02 |  200.2 | bbb      | bbb
(4 rows)

        例2：多gpfdist实例外部表

        在hdp3和hdp4上分别启动一个gpfdist实例。
[gpadmin@hdp3 ~]$ gpfdist -p 8081 -d /home/gpadmin/staging -l /home/gpadmin/log &
[gpadmin@hdp4 ~]$ gpfdist -p 8081 -d /home/gpadmin/staging -l /home/gpadmin/log &
        使用gpfdist协议创建只读外部表example2，文件以管道符（|）作为列分隔符，‘ ‘作为NULL。
db1=# create external table example2
db1-#         ( name text, date date, amount float4, category text, desc1 text )
db1-#     location (‘gpfdist://hdp3:8081/*.txt‘, ‘gpfdist://hdp4:8081/*.txt‘)
db1-#     format ‘text‘ ( delimiter ‘|‘ null ‘ ‘) ;
CREATE EXTERNAL TABLE
        查询外部表，因为gpfdist://hdp3:8081/*.txt不存在而报错。
db1=# select * from example2;
ERROR:  http response code 404 from gpfdist (gpfdist://hdp3:8081/*.txt): HTTP/1.0 404 file not found (url.c:306)  (seg0 hdp4:40000 pid=53784) (dispatcher.c:1801)
db1=#
        将外部文件复制到hdp3的相关目录下。
[gpadmin@hdp4 staging]$ scp *.txt hdp3://home/gpadmin/staging/
        再次查询外部表，可以正确读取全部外部文件数据。
db1=# select * from example2;
 name |    date    | amount | category | desc1 
------+------------+--------+----------+-------
 aaa  | 2017-01-01 |  100.1 | aaa      | aaa
 bbb  | 2017-01-02 |  100.2 | bbb      | bbb
 aaa  | 2017-03-01 |  200.1 | aaa      | aaa
 bbb  | 2017-03-02 |  200.2 | bbb      | bbb
 aaa  | 2017-01-01 |  100.1 | aaa      | aaa
 bbb  | 2017-01-02 |  100.2 | bbb      | bbb
 aaa  | 2017-03-01 |  200.1 | aaa      | aaa
 bbb  | 2017-03-02 |  200.2 | bbb      | bbb
(8 rows)

        例3：带有错误日志的单gpfdist实例外部表

        缺省在访问外部表时只要遇到一行格式错误的数据，就会立即返回错误，并导致查询失败。下面的语句设置了SEGMENT REJECT LIMIT的值，只有当一个segment上的错误数大于等于5时，整个外部表操作才会失败，并且不处理任何行。而当错误数小于5时，会将被拒绝的行写入一个错误表errs，其它数据行还可以正常返回。
db1=# create external table example3
db1-#           ( name text, date date, amount float4, category text, desc1 text )
db1-#      location (‘gpfdist://hdp3:8081/*.txt‘, ‘gpfdist://hdp4:8081/*.txt‘)
db1-#      format ‘text‘ ( delimiter ‘|‘ null ‘ ‘)
db1-#      log errors into errs segment reject limit 5;
NOTICE:  Error table "errs" does not exist. Auto generating an error table with the same name
CREATE EXTERNAL TABLE
db1=# \d errs
         Append-Only Table "public.errs"
  Column  |           Type           | Modifiers 
----------+--------------------------+-----------
 cmdtime  | timestamp with time zone | 
 relname  | text                     | 
 filename | text                     | 
 linenum  | integer                  | 
 bytenum  | integer                  | 
 errmsg   | text                     | 
 rawdata  | text                     | 
 rawbytes | bytea                    | 
Compression Type: None
Compression Level: 0
Block Size: 32768
Checksum: f
Distributed randomly

db1=#
        准备一条格式错误的数据。
[gpadmin@hdp4 staging]$ more a.txt
aaa|2017-01-01|100.1|aaa|aaa
bbb|2017-01-02|100.2|bbb|bbb
bbb,2017-01-02,100.2,bbb,bbb
        查询外部表，返回8条数据，错误数据进入了errs表。
db1=# select * from example3;
NOTICE:  Found 1 data formatting errors (1 or more input rows). Rejected related input data.
 name |    date    | amount | category | desc1 
------+------------+--------+----------+-------
 aaa  | 2017-01-01 |  100.1 | aaa      | aaa
 bbb  | 2017-01-02 |  100.2 | bbb      | bbb
 aaa  | 2017-03-01 |  200.1 | aaa      | aaa
 bbb  | 2017-03-02 |  200.2 | bbb      | bbb
 aaa  | 2017-01-01 |  100.1 | aaa      | aaa
 bbb  | 2017-01-02 |  100.2 | bbb      | bbb
 aaa  | 2017-03-01 |  200.1 | aaa      | aaa
 bbb  | 2017-03-02 |  200.2 | bbb      | bbb
(8 rows)

db1=# \x
Expanded display is on.
db1=# select * from errs;
-[ RECORD 1 ]-----------------------------------------------------
cmdtime  | 2017-04-05 15:23:19.579421+08
relname  | example3
filename | gpfdist://hdp4:8081/*.txt [/home/gpadmin/staging/a.txt]
linenum  | 3
bytenum  | 
errmsg   | missing data for column "date"
rawdata  | bbb,2017-01-02,100.2,bbb,bbb
rawbytes | 

db1=#
        准备5条错误数据。
[gpadmin@hdp4 staging]$ more a.txt
aaa|2017-01-01|100.1|aaa|aaa
bbb|2017-01-02|100.2|bbb|bbb
b1,2017-01-02,100.2,bbb,bbb
b2,2017-01-02,100.2,bbb,bbb
b3,2017-01-02,100.2,bbb,bbb
b4,2017-01-02,100.2,bbb,bbb
b5,2017-01-02,100.2,bbb,bbb
        再次查询外部表，因为达到了错误上限，整条语句失败，没有数据被返回。
db1=# select * from example3;
ERROR:  Segment reject limit reached. Aborting operation. Last error was: missing data for column "date"  (seg16 hdp3:40000 pid=350431)
DETAIL:  External table example3, line 7 of gpfdist://hdp4:8081/*.txt: "b5,2017-01-02,100.2,bbb,bbb"
db1=#

        例4：gpfdist可写外部表

        建立可写外部表，并插入一条数据。
db1=# create writable external table example4 (name text, date date, amount float4, category text, desc1 text)
db1-#     location (‘gpfdist://hdp4:8081/sales.out‘, ‘gpfdist://hdp3:8081/sales.out‘)
db1-#     format ‘text‘ ( delimiter ‘|‘ null ‘ ‘)
db1-#     distributed by (name);
CREATE EXTERNAL TABLE
db1=# insert into example4 values (‘aaa‘,‘2017-01-01‘,100.1,‘aaa‘,‘aaa‘);
INSERT 0 1
        结果只在hdp4上建立了文件/home/gpadmin/staging/sales.out，而hdp3并没有建立输出文件。
[gpadmin@hdp4 staging]$ more sales.out 
aaa|2017-01-01|100.1|aaa|aaa
        再次建立可写外部表，将gpfdist位置调换，把hdp3放前面，并插入一条数据。
db1=# drop external table example4;
DROP EXTERNAL TABLE
db1=# create writable external table example4 (name text, date date, amount float4, category text, desc1 text)
db1-#     location (‘gpfdist://hdp3:8081/sales.out‘, ‘gpfdist://hdp4:8081/sales.out‘)
db1-#     format ‘text‘ ( delimiter ‘|‘ null ‘ ‘)
db1-#     distributed by (name);
CREATE EXTERNAL TABLE
db1=# insert into example4 values (‘aaa‘,‘2017-01-01‘,100.1,‘aaa‘,‘aaa‘);
INSERT 0 1
        这次只在hdp3上建立了文件/home/gpadmin/staging/sales.out。
[gpadmin@hdp3 staging]$ more sales.out 
aaa|2017-01-01|100.1|aaa|aaa
        在LOCATION子句中指定同一主机上的多个gpfdist实例，结果也是一样的。可见，在可写外部表上执行INSERT操作时，只在第一个gpfdist实例的位置上生成本地文件数据。

3. 基于web的外部表
        外部表可以是基于文件的或基于web的。
  • 基于文件的外部表访问静态平面文件。在查询运行时数据是静态的，数据可重复读。
  • 基于web的外部表通过web服务器的http协议或通过执行操作系统命令或脚本，访问动态数据源。数据不可重复读，因为在查询运行时数据可能改变。
        CREATE EXTERNAL WEB TABLE语句创建一个web外部表。web外部表允许HAWQ将动态数据源视作一个常规的数据库表。因为web表数据可能在查询运行时改变，所以数据是不可重复读的。可以定义基于命令或基于URL的web外部表，但不能在一条建表命令中混用两种定义。

（1）基于命令的web外部表

        用一个shell命令或脚本的输出定义基于命令的web表数据。在CREATE EXTERNAL WEB TABLE语句的EXECUTE子句指定需要执行的命令。外部表中的数据是命令运行时的数据。EXECUTE子句运行特定master或虚拟段上的shell命令或脚本。脚本必须是gpadmin用户可执行的，并且位于所有master和segment主机的相同位置上，虚拟段并行运行命令。
        外部表定义中指定的命令从数据库执行，数据库不能从.bashrc或.profile获取环境变量，因此需要在EXECUTE子句中设置环境变量。例如，下面的外部表运行一个HAWQ master主机上的密令：
CREATE EXTERNAL WEB TABLE output (output text)
EXECUTE ‘PATH=/home/gpadmin/programs; export PATH; myprogram.sh‘
    ON MASTER 
FORMAT ‘TEXT‘;
        下面的命令定义一个web表，在五个虚拟段上运行一个名为get_log_data.sh脚本文件。
CREATE EXTERNAL WEB TABLE log_output (linenum int, message text) 
EXECUTE ‘/home/gpadmin/get_log_data.sh‘ ON 5 
FORMAT ‘TEXT‘ (DELIMITER ‘|‘);
        资源管理器在运行时选取虚拟段。

（2）基于URL的Web外部表

        基于URL的web表使用HTTP协议从web服务器访问数据，web表数据是动态的。在LOCATION子句中使用http://指定文件在web服务器上的位置。web数据文件必须在所有segment主机能够访问的web服务器上。URL的数量对应访问该web表时并行的最少虚拟段数量。下面的例子定义了一个从多个URL获取数据的web表。
CREATE EXTERNAL WEB TABLE ext_expenses (
    name text, date date, amount float4, category text, description text) 
LOCATION (‘http://hdp1/sales/file.csv‘,
          ‘http://hdp1/exec/file.csv‘,
          ‘http://hdp1/finance/file.csv‘,
          ‘http://hdp1/ops/file.csv‘,
          ‘http://hdp1/marketing/file.csv‘,
          ‘http://hdp1/eng/file.csv‘ 
      )
FORMAT ‘CSV‘;

（3）基于Web的外部表示例

        例5：执行脚本的可读Web外部表
        建立外部表。
db1=# create external web table example5 (linenum int, message text) 
db1-# execute ‘/home/gpadmin/get_log_data.sh‘ on 5 
db1-# format ‘text‘ (delimiter ‘|‘);
CREATE EXTERNAL TABLE
        HAWQ集群中每台主机的相同位置上都必须有同一个可执行的脚本，否则查询会报错，如hdp1上没有/home/gpadmin/get_log_data.sh文件。
db1=# select * from example5;
ERROR:  external table example5 command ended with error. sh: /home/gpadmin/get_log_data.sh: No such file or directory  (seg0 hdp1:40000 pid=360600)
DETAIL:  Command: execute:/home/gpadmin/get_log_data.sh
        对该外部表的查询会返回每个虚拟段输出的并集，例如，get_log_data.sh脚本内容如下：
#!/bin/bash
echo "1|aaa"
echo "2|bbb"
        则该表将返回10条（每个虚拟段两条，五个虚拟段）数据：
db1=# select * from example5;
 linenum | message 
---------+---------
       1 | aaa
       2 | bbb
       1 | aaa
       2 | bbb
       1 | aaa
       2 | bbb
       1 | aaa
       2 | bbb
       1 | aaa
       2 | bbb
(10 rows)
        执行查询时，资源管理器最少分配5个虚拟段。如果建表时指定的虚拟段数超过了允许的最大值，表仍然可以建立，但查询时会报错。
db1=# drop external web table example5;
DROP EXTERNAL TABLE
db1=# create external web table example5 (linenum int, message text) 
db1-# execute ‘/home/gpadmin/get_log_data.sh‘ on 100 
db1-# format ‘text‘ (delimiter ‘|‘);
CREATE EXTERNAL TABLE
db1=# select * from example5;
ERROR:  failed to acquire resource from resource manager, minimum expected number of virtual segment 100 is more than maximum possible number 64 in queue pg_default (pquery.c:804)
        例6：执行脚本的可写web外部表
        创建外部表。
db1=# create writable external web table example6
db1-#         (name text, date date, amount float4, category text, desc1 text)
db1-#         execute ‘PATH=/home/gpadmin/programs; export PATH; myprogram1.sh‘ on 6
db1-#         format ‘text‘ (delimiter ‘|‘)
db1-#         distributed randomly;
CREATE EXTERNAL TABLE
        myprogram1.sh的内容如下：
#!/bin/bash
while read line
do
    echo "File:${line}" >> /home/gpadmin/programs/a.txt
done
        向外部表中插入数据。
db1=# insert into example6 values (‘aaa‘,‘2017-01-01‘,100.1,‘aaa‘,‘aaa‘);
INSERT 0 1
db1=# insert into example6 values (‘bbb‘,‘2017-02-01‘,200.1,‘bbb‘,‘‘);
INSERT 0 1
        插入的数据通过管道输出给myprogram1.sh并执行，可以看到插入的数据被写入了a.txt文件。与可读表不同，该文件只在一个HAWQ主机上生成，并且每次插入数据只生成一行。
[gpadmin@hdp4 programs]$ more  /home/gpadmin/programs/a.txt
File:aaa|2017-01-01|100.1|aaa|aaa
File:bbb|2017-02-01|200.1|bbb|
5. 使用外部表装载数据
        使用INSERT INTO target_table SELECT ... FROM source_external_table命令装载数据。例如：
CREATE TABLE expenses_travel (LIKE ext_expenses);
INSERT INTO expenses_travel 
SELECT * FROM ext_expenses WHERE category=‘travel‘;
        也可以在创建一个新表的同时装载数据：
CREATE TABLE expenses AS SELECT * FROM ext_expenses;

6. 外部表错误处理

        可读外部表通常被用于选择数据装载到普通的HAWQ数据库表中。使用CREATE TABLE AS SELECT或INSERT INTO命令查询外部表数据。缺省时，如果数据包含错误，则整条命令失败，没有数据装载到目标数据库表中。
        SEGMENT REJECT LIMIT子句允许隔离外部表中格式错误的数据，并继续装载格式正确的行。使用SEGMENT REJECT LIMIT设置一个错误阈值，指定拒绝的数据行数（缺省）或一个占总行数的百分比（1-100）。
        如果错误行数达到了SEGMENT REJECT LIMIT的值，整个外部表操作失败，没有数据行被处理。限制的错误行数是相对于一个虚拟段的，不是整个操作的。如果错误行数没有达到SEGMENT REJECT LIMIT值，操作处理所有正确的行，丢弃错误行，或者可选地将格式错误的行写入日志表。LOG ERRORS子句允许保存错误行以备后续检查。
        设置SEGMENT REJECT LIMIT会使HAWQ以单行错误隔离模式扫描外部数据。当外部数据行出现多余属性、缺少属性、数据类型错误、无效的客户端编码序列等格式错误时，单行错误隔离模式将错误行丢弃或写入日志表。HAWQ不检查约束错误，但可以在查询外部表时过滤约束错误。例如，消除重复键值错误：
INSERT INTO table_with_pkeys 
SELECT DISTINCT * FROM external_table;

（1）使用单行错误隔离定义外部表

        下面的例子在HAWQ表中记录错误记录，并设置错误行阈值为10。
db1=# create external table ext_expenses ( name text, date date, amount float4, category text, desc1 text )
db1-#    location (‘gpfdist://hdp3:8081/*‘, ‘gpfdist://hdp4:8081/*‘)
db1-#    format ‘text‘ (delimiter ‘|‘)
db1-#    log errors into errs segment reject limit 10 rows;
CREATE EXTERNAL TABLE

（2）标识无效的CSV文件数据

        如果一个CSV文件包含无效格式，错误日志表的rawdata字段可能包含多行。例如，某字段少了一个闭合的引号，后面所有的换行符都被认为是数据中内嵌的换行符。当这种情况发生时，HAWQ在一行数据达到64K时停止解析，并将此64K数据作为单行写入错误日志表，然后重置引号标记，继续读取数据。如果这种情况在处理装载时发生三次，载入文件被认为是无效的，整个装载失败，错误信息为“rejected N or more rows”。

（3）表间迁移数据

        可以使用CREATE TABLE AS或INSERT...SELECT语句将外部表或web外部表的数据装载到其它非外部表中，数据将根据外部表或web外部表的定义并行装载。如果一个外部表或web外部表数据源有错误，依赖于使用的错误隔离模式，有以下两种处理方式：
  • 表没有设置错误隔离模式：读取该表的任何操作都会失败。没有设置错误隔离模式的外部表或web外部表上的操作将整体成功或失败。
  • 表设置了错误隔离模式：除了发生错误的行，其它数据将被装载（依赖于REJECT_LIMIT的配置）。
7. 使用hawq load装载数据
        HAWQ的hawq load应用程序使用可读外部表和HAWQ并行文件系统（gpfdist或gpfdists）装载数据。它并行处理基于文件创建的外部表，允许用户在单一配置文件中配置数据格式、外部表定义，以及gpfdist或gpfdists的设置。

（1）确认建立了运行hawq load的环境。

        它需要依赖某些HAWQ安装中的文件，如gpfdist和Python，还需要通过网络访问所有HAWQ segment主机。

（2）创建控制文件。

        hawq load的控制文件是一个YAML（Yet Another Markup Language）格式的文件，在其中指定HAWQ连接信息，gpfdist配置信息，外部表选项、数据格式等。例如下面是一个名为my_load.yml的控制文件内容：

---
VERSION: 1.0.0.1
DATABASE: db1
USER: gpadmin
HOST: hdp3
PORT: 5432
GPLOAD:
   INPUT:
    - SOURCE:
         LOCAL_HOSTNAME:
           - hdp4
         PORT: 8081
         FILE: 
           - /home/gpadmin/staging/*.txt
    - COLUMNS:
           - name: text
           - date: date
           - amount: float4
           - category: text
           - desc1: text           
    - FORMAT: text
    - DELIMITER: ‘|‘
    - ERROR_LIMIT: 25
    - ERROR_TABLE: errlog
   OUTPUT:
    - TABLE: t1
    - MODE: INSERT
   SQL:
    - BEFORE: "INSERT INTO audit VALUES(‘start‘, current_timestamp)"
    - AFTER: "INSERT INTO audit VALUES(‘end‘, current_timestamp)"
    注意：
  • hawq load控制文件使用YAML 1.1文档格式，为了定义HAWQ数据装载的各种步骤，它定义了自己的schema。控制文件必须是一个有效的YAML文档。hawq load程序按顺序处理控制文件文档，并使用空格识别文档中各段之间的层次关系，因此空格的使用是非常重要的。不要使用TAB符代替空格，YAML文档中不要出现TAB符。
  • LOCAL_HOSTNAME指定运行hawq load的本地主机名或IP地址。如果机器配置了多块网卡，可以为每块网卡指定一个主机名，允许同时使用多块网卡传输数据。比如hdp4上配置了两块网卡，可以如下配置LOCAL_HOSTNAME：
    LOCAL_HOSTNAME:
 - hdp4-1
 - hdp4-2
（3）hawq load示例
        准备本地文件数据。
[gpadmin@hdp4 staging]$ more a.txt 
aaa|2017-01-01|100.1|aaa|aaa
bbb|2017-01-02|100.2|bbb|bbb
[gpadmin@hdp4 staging]$ more b.txt 
aaa|2017-03-01|200.1|aaa|aaa
bbb|2017-03-02|200.2|bbb|bbb
        建立目标表和audit表。
db1=# create table t1 ( name text, date date, amount float4, category text, desc1 text );
CREATE TABLE
db1=# create table audit(flag varchar(10),st timestamp); 
CREATE TABLE
        执行hawq load。
[gpadmin@hdp4 ~]$ hawq load -f my_load.yml
2017-04-05 16:41:44|INFO|gpload session started 2017-04-05 16:41:44
2017-04-05 16:41:44|INFO|setting schema ‘public‘ for table ‘t1‘
2017-04-05 16:41:44|INFO|started gpfdist -p 8081 -P 8082 -f "/home/gpadmin/staging/*.txt" -t 30
2017-04-05 16:41:49|INFO|running time: 5.63 seconds
2017-04-05 16:41:49|INFO|rows Inserted          = 4
2017-04-05 16:41:49|INFO|rows Updated           = 0
2017-04-05 16:41:49|INFO|data formatting errors = 0
2017-04-05 16:41:49|INFO|gpload succeeded
[gpadmin@hdp4 ~]$
        查询目标表和audit表。
db1=# select * from t1;
 name |    date    | amount | category | desc1 
------+------------+--------+----------+-------
 aaa  | 2017-01-01 |  100.1 | aaa      | aaa
 bbb  | 2017-01-02 |  100.2 | bbb      | bbb
 aaa  | 2017-03-01 |  200.1 | aaa      | aaa
 bbb  | 2017-03-02 |  200.2 | bbb      | bbb
(4 rows)

db1=# select * from audit;
 flag  |             st             
-------+----------------------------
 start | 2017-04-05 16:41:44.736296
 end   | 2017-04-05 16:41:49.60153
(2 rows)

8. 使用COPY装载、卸载数据
        COPY是HAWQ的SQL命令，它在标准输入和HAWQ表之间互拷数据。COPY FROM命令将本地文件追加到数据表中，而COPY TO命令将数据表中的数据覆盖写入本地文件。COPY命令是非并行的，数据在HAWQ master实例上以单进程处理，因此只推荐对非常小的数据文件使用COPY命令。本地文件必须在master主机上，缺省的文件格式是逗号分隔的CSV文本文件。HAWQ使用客户端与master服务器之间的连接，从STDIN或STDOUT拷贝数据。
[gpadmin@hdp4 ~]$ psql -h hdp3 -d db1
psql (8.2.15)
Type "help" for help.

db1=# create table t2 (like t1);
NOTICE:  Table doesn‘t have ‘distributed by‘ clause, defaulting to distribution columns from LIKE table
CREATE TABLE
db1=# copy t2 from ‘/home/gpadmin/staging/a.txt‘ with delimiter ‘|‘;
COPY 2
db1=# select * from t2;
 name |    date    | amount | category | desc1 
------+------------+--------+----------+-------
 aaa  | 2017-01-01 |  100.1 | aaa      | aaa
 bbb  | 2017-01-02 |  100.2 | bbb      | bbb
(2 rows)
        将表数据卸载到master的本地文件中，如果文件不存在则建立文件，否则会用卸载数据覆盖文件原来的内容。
db1=# copy (select * from t2) to ‘/home/gpadmin/staging/c.txt‘ with delimiter ‘|‘;
COPY 2

[gpadmin@hdp3 staging]$ more /home/gpadmin/staging/c.txt 
bbb|2017-01-02|100.2|bbb|bbb
aaa|2017-01-01|100.1|aaa|aaa

（1）以单行错误隔离模式运行COPY。

        缺省时，COPY在遇到第一个错误时就停止运行。如果数据含有错误，操作失败，没有数据被装载。如果以单行错误隔离模式运行COPY，HAWQ跳过含有错误格式的行，装载具有正确格式的行。如果数据违反了NOT NULL或CHECK等约束条件，操作仍然是‘all-or-nothing’输入模式，整个操作失败，没有数据被装载。
[gpadmin@hdp3 staging]$ more a.txt
aaa|2017-01-01|100.1|aaa|aaa
bbb|2017-01-02|100.2|bbb|bbb
        向表拷贝本地文件数据，错误日志表中新增一条数据。
db1=# create table t3 ( name text not null, date date, amount float4, category text, desc1 text );
CREATE TABLE
db1=# copy t3 from ‘/home/gpadmin/staging/a.txt‘
db1-#    with delimiter ‘|‘ log errors into errtable
db1-#    segment reject limit 5 rows;
NOTICE:  Error table "errtable" does not exist. Auto generating an error table with the same name
WARNING:  The error table was created in the same transaction as this operation. It will get dropped if transaction rolls back even if bad rows are present
HINT:  To avoid this create the error table ahead of time using: CREATE TABLE <name> (cmdtime timestamp with time zone, relname text, filename text, linenum integer, bytenum integer, errmsg text, rawdata text, rawbytes bytea)
NOTICE:  Dropping the auto-generated unused error table
HINT:  Use KEEP in LOG INTO clause to force keeping the error table alive
COPY 2
db1=# select * from t3;
 name |    date    | amount | category | desc1 
------+------------+--------+----------+-------
 bbb  | 2017-01-02 |  100.2 | bbb      | bbb
 aaa  | 2017-01-01 |  100.1 | aaa      | aaa
(2 rows)
        修改文件，制造一行格式错误的数据。   
[gpadmin@hdp3 staging]$ more a.txt
aaa,2017-01-01,100.1,aaa,aaa
bbb|2017-01-02|100.2|bbb|bbb
        再次拷贝数据。与卸载不同，装载会向表中追加数据。
db1=# copy t3 from ‘/home/gpadmin/staging/a.txt‘
db1-#    with delimiter ‘|‘ log errors into errtable
db1-#    segment reject limit 5 rows;
NOTICE:  Error table "errtable" does not exist. Auto generating an error table with the same name
WARNING:  The error table was created in the same transaction as this operation. It will get dropped if transaction rolls back even if bad rows are present
HINT:  To avoid this create the error table ahead of time using: CREATE TABLE <name> (cmdtime timestamp with time zone, relname text, filename text, linenum integer, bytenum integer, errmsg text, rawdata text, rawbytes bytea)
NOTICE:  Found 1 data formatting errors (1 or more input rows). Errors logged into error table "errtable"
COPY 1
db1=# select * from t3;
 name |    date    | amount | category | desc1 
------+------------+--------+----------+-------
 bbb  | 2017-01-02 |  100.2 | bbb      | bbb
 bbb  | 2017-01-02 |  100.2 | bbb      | bbb
 aaa  | 2017-01-01 |  100.1 | aaa      | aaa
(3 rows)

db1=# \x
Expanded display is on.
db1=# select * from errtable;
-[ RECORD 1 ]----------------------------
cmdtime  | 2017-04-05 16:56:02.402161+08
relname  | t3
filename | /home/gpadmin/staging/a.txt
linenum  | 1
bytenum  | 
errmsg   | missing data for column "date"
rawdata  | aaa,2017-01-01,100.1,aaa,aaa
rawbytes | 

db1=#
        再次修改文件，将name字段对应的数据置空，因为该字段定义为NOT NULL，所以违反约束，没有数据被拷贝。   
[gpadmin@hdp3 staging]$ more a.txt
|2017-01-01|100.1|aaa|aaa
bbb|2017-01-02|100.2|bbb|bbb
  
db1=# truncate table t3;
TRUNCATE TABLE
db1=# copy t3 from ‘/home/gpadmin/staging/a.txt‘
   with delimiter ‘|‘ null as  ‘‘ log errors into errtable
   segment reject limit 5 rows;
ERROR:  null value in column "name" violates not-null constraint  (seg5 hdp1:40000 pid=370883)
CONTEXT:  COPY t3, line 1: "|2017-01-01|100.1|aaa|aaa"
db1=# select * from t3;
 name | date | amount | category | desc1 
------+------+--------+----------+-------
(0 rows)

9. 卸载数据
        一个可写外部表允许用户从其它数据库表选择数据行并输出到文件、命名管道、应用或MapReduce。如前面的例4和例6所示，可以定义基于gpfdist或基于web的可写外部表。
        对于使用gpfdist协议的外部表，HAWQ segment将它们的数据发送给gpfdist，gpfdist将数据写入命名文件中。gpfdist必须运行在HAWQ segment能够在网络上访问的主机上。gpfdist指向一个输出主机上的文件位置，将从HAWQ segment接收到的数据写入文件。
        一个可写web外部表的数据作为数据流发送给应用。例如，从HAWQ卸载数据并发送给一个连接其它数据库的应用或向别处装载数据的ETL工具。可写web外部表使用EXECUTE子句指定一个运行在segment主机上的shell命令、脚本或应用，接收输入数据流。
        可以选择为可写外部表声明分布策略。缺省时，可写外部表使用随机分布策略。如果要导出的源表是哈希分布的，为外部表定义相同的分布键列会提升数据卸载性能，因为这消除了数据行在内部互联网络上的移动。如果卸载一个特定表的数据，可以使用LIKE子句拷贝源表的列定义与分布策略。
        下面是一个gpfdist外部表的例子：
db1=# create writable external table unload_expenses
db1-# ( like t1 )
db1-# location (‘gpfdist://hdp3:8081/expenses1.out‘,
db1(# ‘gpfdist://hdp4:8081/expenses2.out‘)
db1-# format ‘text‘ (delimiter ‘,‘);
NOTICE:  Table doesn‘t have ‘distributed by‘ clause, defaulting to distribution columns from LIKE table
CREATE EXTERNAL TABLE
        可写外部表只允许INSERT操作。如果执行卸载的用户不是外部表的属主或超级用户，必须授予对外部表的INSERT权限。例如：
GRANT INSERT ON unload_expenses TO admin;
        与例4不同，INSERT INTO 外部表 SELECT ... 语句中，外部表的输出文件只能在一个主机上，否则会报错。
db1=# insert into unload_expenses select * from t1;
ERROR:  External table has more URLs then available primary segments that can write into them  (seg0 hdp1:40000 pid=387379)
db1=# drop external table unload_expenses;
DROP EXTERNAL TABLE
db1=# create writable external table unload_expenses
db1-# ( like t1 )
db1-# location (‘gpfdist://hdp3:8081/expenses1.out‘)
db1-# format ‘text‘ (delimiter ‘,‘);
NOTICE:  Table doesn‘t have ‘distributed by‘ clause, defaulting to distribution columns from LIKE table
CREATE EXTERNAL TABLE
db1=# insert into unload_expenses select * from t1;
INSERT 0 4
        查看导出的数据。
[gpadmin@hdp3 staging]$ more expenses1.out 
aaa,2017-01-01,100.1,aaa,aaa
bbb,2017-01-02,100.2,bbb,bbb
aaa,2017-03-01,200.1,aaa,aaa
bbb,2017-03-02,200.2,bbb,bbb
[gpadmin@hdp3 staging]$
        如上面的例6所示，也可以定义一个可写的外部web表，发送数据行到脚本或应用。脚本文件必须接收输入流，而且必须存在于所有HAWQ segment的主机的相同位置上，并可以被gpadmin用户执行。HAWQ系统中的所有segment都执行脚本，无论segment是否有需要处理的输出行。
        允许外部表执行操作系统命令或脚本会带来相应的安全风险。为了在可写外部web表定义中禁用EXECUTE，可在HAWQ master的hawq-site.xml文件中设置gp_external_enable_exec服务器配置参数为off。
gp_external_enable_exec = off
        正如前面说明COPY命令时所看到的，COPY TO命令也可以用来卸载数据。它使用HAWQ master主机上的单一进程，从表中数据拷贝到HAWQ master主机上的一个文件（或标准输入）中。COPY TO命令重写整个文件，而不是追加记录。

10. hawq register
        该命令将HDFS上的Parquet表数据装载并注册到对应的HAWQ表中。hawq register的使用场景好像很有限，因为它只能注册HAWQ或Hive已经生成的Parquet表文件。关于该命令的使用可参考“Registering Files into HAWQ Internal Tables”。

11. 格式化数据文件
        当使用HAWQ工具装载卸载数据时，必须指定数据的格式。CREATE EXTERNAL TABLE、hawq load和COPY都包含指定数据格式的子句。数据可以是固定分隔符的文本或逗号分隔值（CSV）格式。外部数据必须是HAWQ可以正确读取的格式。

（1）行格式

        HAWQ需要数据行以换行符（LF，Line feed，ASCII值0x0A）、回车符（CR，Carriage return，ASCII值0x0D）或回车换行符（CR+LF，0x0D 0x0A）作为行分隔符。LF是类UNIX操作系统中标准的换行符。而Windows或Mac OS X使用CR或CR+LF。所有这些表示一个新行的特殊符号都被HAWQ作为行分隔符所支持。

（2）列格式

        文本文件和CSV文件缺省的列分隔符是分别是TAB（ASCII值为0x09）和逗号（ASCII值为0x2C）。在定义数据格式时，可以在CREATE EXTERNAL TABLE或COPY命令的DELIMITER子句，或者hawq load的控制文件中，声明一个单字符作为列分隔符。分隔符必须出现在字段值之间，不要在一行的开头或结尾放置分隔符。例如，下面使用管道符（|）作为列分隔符：
data value 1|data value 2|data value 3
        下面的建表命令显示以管道符作为列分隔符：
=# CREATE EXTERNAL TABLE ext_table (name text, date date)
LOCATION (‘gpfdist://host:port/filename.txt)
FORMAT ‘TEXT‘ (DELIMITER ‘|‘);

（3）表示空值

        空值（NULL）表示一列中的未知数据。可以指定数据文件中的一个字符串表示空值。文本文件中表示空值的缺省字符串为\N，CSV文件中表示空值的缺省字符串为不带引号的空串（两个连续的逗号）。定义数据格式时，可以在CREATE EXTERNAL TABLE、COPY命令的NULL子句，或者hawq load的控制文件中，声明其它字符串表示空值。例如，如果不想区分空值与空串，就可以指定空串表示NULL。在使用HAWQ装载工具时，任何与声明的代表NULL的字符串相匹配的数据项都被认为是空值。

（4）转义

        列分隔符与行分隔符在数据文件中具有特殊含义。如果实际数据中也含有这个符号，必须对这些符号进行转义，以使HAWQ将它们作为普通数据而不是列或行的分隔符。文本文件缺省的转义符为一个反斜杠（\），CSV文件缺省的转义符为一个双引号（"）。
        文本文件转义
        可以在CREATE EXTERNAL TABLE、COPY的ESCAPE子句，或者hawq load的控制文件中指定转义符。例如，假设有以下三个字段的数据：
  • backslash = \
  • vertical bar = |
  • exclamation point = !
        假设指定管道符（|）为列分隔符，反斜杠（\）为转义符。则对应的数据行格式如下：
backslash = \\ | vertical bar = \| | exclamation point = !
        可以对八进制或十六进制序列应用转义符。在装载进HAWQ时，转义后的值就是八进制或十六进制的ASCII码所表示的字符。例如，取址符（&）可以使用十六进制的（\0x26）或八进制的（\046）表示。
        如果要在CREATE EXTERNAL TABLE、COPY命令的ESCAPE子句，或者hawq load的控制文件中禁用转义，可如下设置：
ESCAPE ‘OFF‘
        该设置常用于输入数据中包含很多反斜杠（如web日志数据）的情况。

        CSV文件转义
        可以在CREATE EXTERNAL TABLE、COPY的ESCAPE子句，或者hawq load的控制文件中指定转义符。例如，假设有以下三个字段的数据：
  • Free trip to A,B
  • 5.89
  • Special rate "1.79"
        假设指定逗号（,）为列分隔符，一个双引号（"）为转义符。则数据行格式如下：
"Free trip to A,B","5.89","Special rate ""1.79"""
        将字段值置于双引号中能保留字符串中头尾的空格。

（5）字符编码

        在将一个Windows操作系统上生成的数据文件装载到HAWQ前，先使用dos2unix系统命令去除只有Windows使用的字符，如删除文件中的CR（‘\x0d‘）。

（6）导入导出固定宽度数据

        HAWQ的函数fixedwith_in和fixedwidth_out支持固定宽度的数据格式。这些函数的定义保存在$GPHOME/share/postgresql/cdb_external_extensions.sql文件中。下面的例子声明了一个自定义格式，然后调用fixedwidth_in函数指定为固定宽度的数据格式。
db1=# create readable external table students (
db1(#   name varchar(5), address varchar(10), age int)
db1-# location (‘gpfdist://hdp4:8081/students.txt‘)
db1-# format ‘custom‘ (formatter=fixedwidth_in, name=‘5‘, address=‘10‘, age=‘4‘);
CREATE EXTERNAL TABLE
db1=# select * from students;
 name  |  address   | age 
-------+------------+-----
 abcde | 1234567890 |  40
(1 row)
        students.txt文件内容如下：
[gpadmin@hdp4 unload_data1]$ more students.txt 
abcde12345678900040
        文件中一行记录的字节必须与建表语句中字段字节数的和一致，如上例中一行必须严格为19字节，否则读取文件时会报错。再看一个含有中文的例子。
[gpadmin@hdp4 unload_data1]$ echo $LANG
zh_CN.UTF-8
[gpadmin@hdp4 unload_data1]$ more students.txt 
中文中文中0040
        操作系统和数据库的字符集都是UTF8，一个中文占用三个字节，记录一共19字节，满足读取条件。
db1=# select * from students;
 name | address | age 
------+---------+-----
 中   | 中文中  |  40
(1 row)
        name字段5字节，address字段10字节，理论上这两个字段都应该含有不完整的字符，但从查询结果看到，HAWQ在这里做了一些处理，name字段读取了一个完整的中文，address字段读取了三个完整的字符。而中间按字节分裂的中文字符被不可见字符所取代。
db1=# select char_length(name),octet_length(name),substr(name,1,1),substr(name,2,1) from students;
 char_length | octet_length | substr | substr 
-------------+--------------+--------+--------
           2 |            5 | 中     | 
(1 row)

db1=# select char_length(address),octet_length(address),substr(address,1,1),substr(address,2,1) from students;
 char_length | octet_length | substr | substr 
-------------+--------------+--------+--------
           4 |           10 |        | 中
(1 row)
        以下选项指定如何读取固定宽度数据文件。
  • 读取全部数据。装载固定宽度数据一行中的所有字段，并按它们的物理顺序进行装载。必须指定字段长度，不能指定起始于终止位置。固定宽度参数中字段名的顺序必须与CREATE TABLE命令中的顺序相匹配。
  • 设置空格与NULL特性。缺省时尾部空格被截取。为了保留尾部空格，使用preserve_blanks=on选项。使用null=‘null_string_value‘选项指定代表NULL的字符串。
  • 如果指定了preserve_blanks=on，也必须定义代表NULL值的字符串。否则会报ERROR:  A null_value was not defined. When preserve_blanks is on, a null_value
  • 如果指定了preserve_blanks=off，没有定义NULL，并且一个字段只包含空格，HAWQ向表中写一个null。如果定义了NULL，HAWQ向表中写一个空串。
  • 使用line_delim=‘line_ending‘参数指定行尾字符。下面的例子覆盖大多数情况。‘E’表示转义，就是说如果记录正文中含有line_delim，需要进行转义。
    line_delim=E‘\n‘
line_delim=E‘\r‘
line_delim=E‘\r\n‘
line_delim=‘abc‘
三、数据库统计
1. 概述
        统计信息指的是数据库中所存储数据的元信息描述。查询优化器需要依据最新的统计信息，为查询生成最佳的执行计划。例如，如果查询连接了两个表，一个表必须被广播到所有段，那么优化器会选择广播其中的小表，使网络流量最小化。
        ANALYZE命令计算优化器所需的统计信息，并将结果保存到系统目录中。有三种方式启动分析操作：
  • 直接运行ANALYZE命令。
  • 在数据库外运行analyzedb命令行应用程序。
  • 在执行DML操作的表上没有统计信息，或者DML操作影响的行数超过了指定的阈值时，自动执行分析操作。
        计算统计信息会消耗时间和资源，因此HAWQ会在大表上进行采样，通过计算部分数据，产生统计信息的估算。在大多数情况下，缺省设置能够提供生成正确查询执行计划的信息。如果产生的统计不能生成优化的查询执行计划，管理员可以调整配置参数，通过增加样本数据量，产生更加精确的统计信息。统计信息越精确，所消耗的CPU和内存资源越多，因此可能由于资源的限制，无法生成更好的计划。此时就需要查看执行计划并测试查询性能，目标是要通过增加的统计成本达到更好的查询性能。

2. 系统统计
（1）表大小
        查询优化器使用查询必须处理的数据行数和必须访问的磁盘页数等统计信息，寻找查询所需的最小的磁盘I/O和网络流量的执行计划。用于估算行数和页数的数据分别保存在pg_class系统表的reltuples和relpages列中，其中的值是最后运行VACUUM或ANALYZE命令时生成的数据。对于缺省的AO（Append Only）表，系统目录中的tuples数是最近的值，因此reltuples统计是精确值而不是估算值，但relpages值是AO数据块的估算值。如果reltuples列的值与SELECT COUNT(*)的返回值差很多，应该执行分析更新统计信息。

（2）pg_statistic系统表与pg_stats视图

        pg_statistic系统表保存每个数据库表上最后执行ANALYZE操作的结果。每个表列有一行记录，它具有以下字段：
  • starelid：列所属的表的对象ID。
  • staatnum：所描述列在表中的编号，从1开始。
  • stanullfrac；列中空值占比。
  • stawidth：非空数据项的平均宽度，单位是字节。
  • stadistinct：列中不同非空数据值的个数。
  • stakindN：用于表示后面number、values所表示的数据用途，被用于生成pg_stats。如1则表示是MCV（Most Common Values）的值；2表示直方图（histogram）的值；3表示相关性（correlation）的值等。kind的取值范围：1~99，內核占用；100~199，PostGIS占用；200~299，ESRI ST_Geometry几何系统占用；300~9999，公共占用。
  • staopN：用于表示该统计值支持的操作，如’=’或’<’等。
  • stanumbersN：如果是MCV类型（即kind=1），那么这里即是下面对应的stavaluesN出现的概率值，即MCF。
  • stavaluesN：anyarray类型的数据，內核特殊类型，不可更改。是统计信息的值部分，与kind对应。如kind=2的时候，则这里的值表示直方图。
        pg_statistic表将不同的统计类型分为四类，分别用四个字段表示。而pg_stats视图以一种更友好的方式表示pg_statistic的内容，其定义如下：
 SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct, 
        CASE 1
            WHEN s.stakind1 THEN s.stavalues1
            WHEN s.stakind2 THEN s.stavalues2
            WHEN s.stakind3 THEN s.stavalues3
            WHEN s.stakind4 THEN s.stavalues4
            ELSE NULL::anyarray
        END AS most_common_vals, 
        CASE 1
            WHEN s.stakind1 THEN s.stanumbers1
            WHEN s.stakind2 THEN s.stanumbers2
            WHEN s.stakind3 THEN s.stanumbers3
            WHEN s.stakind4 THEN s.stanumbers4
            ELSE NULL::real[]
        END AS most_common_freqs, 
        CASE 2
            WHEN s.stakind1 THEN s.stavalues1
            WHEN s.stakind2 THEN s.stavalues2
            WHEN s.stakind3 THEN s.stavalues3
            WHEN s.stakind4 THEN s.stavalues4
            ELSE NULL::anyarray
        END AS histogram_bounds, 
        CASE 3
            WHEN s.stakind1 THEN s.stanumbers1[1]
            WHEN s.stakind2 THEN s.stanumbers2[1]
            WHEN s.stakind3 THEN s.stanumbers3[1]
            WHEN s.stakind4 THEN s.stanumbers4[1]
            ELSE NULL::real
        END AS correlation
   FROM pg_statistic s
   JOIN pg_class c ON c.oid = s.starelid
   JOIN pg_attribute a ON c.oid = a.attrelid AND a.attnum = s.staattnum
   LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
  WHERE has_table_privilege(c.oid, ‘select‘::text);
        新建的表没有统计信息。

（3）采样

        在为大表计算统计信息时，HAWQ通过对基表采样数据的方式建立一个小表。如果基表是分区表，从全部分区中采样。样本表中的行数取决于由gp_analyze_relative_error系统配置参数指定的最大可接受错误数。该参数的缺省值是0.25（25%）。通常该值已经足够生成正确的查询计划。如果ANALYZE不能产生好的表列估算，可以通过调低该参数值，增加采样的数据量。注意，降低该值可能导致大量的采样数据，并明显增加分析时间。
[gpadmin@hdp3 ~]$ hawq config -s gp_analyze_relative_error
GUC		: gp_analyze_relative_error
Value		: 0.25

（4）统计更新

        不带参数运行ANALYZE会更新当前数据库中所有表的统计信息，这可能需要执行很长时间。所以最好分析单个表，在一个表中的数据大量修改后分析该表。也可以选择分析一个表列的子集，例如只分析连接、where子句、sort子句、group by子句、having子句中用到的列。
db1=# analyze t1 (name,category);
ANALYZE

（5）分析分区和AO表

        在分区表上运行ANALYZE命令时，它逐个分析每个叶级别的子分区。也可以只在新增或修改的分区文件上运行ANALYZE，避免分析没有变化的分区。
        analyzedb命令行应用自动跳过无变化的分区。并且它是多会话并行的，可以同时分析几个分区。缺省运行五个会话，会话数可以通过命令行的-p选项设置，值域为1-10。每次运行analyzedb，它都会将AO表和分区的状态信息保存在master节点中数据目录中的db_analyze目录下，如/data/hawq/master/db_analyze/。下次运行时，analyzedb比较每个表的当前状态与上次保存的状态，不分析没有变化的表或分区。堆表（系统表）总是进行分析。
        Pivotal查询优化器需要分区表根级别的统计信息，而老的优化器不使用该统计。通过设置optimizer和optimizer_analyze_root_partition系统配置参数启用Pivotal查询优化器，缺省是启用的。
[gpadmin@hdp3 ~]$ hawq config -s optimizer
GUC		: optimizer
Value		: on
[gpadmin@hdp3 ~]$ hawq config -s optimizer_analyze_root_partition
GUC		: optimizer_analyze_root_partition
Value		: on
        每次运行ANALYZE或ANALYZE ROOTPARTITION时，根级别的统计信息被更新。analyzedb应用缺省更新根分区统计。当在父表上使用ANALYZE收集统计信息时，既会收集每个叶子分区的统计信息，又会收集分区表的全局统计信息。生成分区表的查询计划时两个统计信息都需要。如果所有子分区的统计信息都已经更新，ROOTPARTITION选项可用于只收集分区表的全局状态信息，这可以节省分析每个叶子分区所需要的时间。
        如果在一个非根分区或非分区表上使用ROOTPARTITION选项，ANALYZE命令将跳过该选项并发出一个警告信息。
db1=# analyze rootpartition t1;
WARNING:  skipping "t1" --- cannot analyze a non-root partition using ANALYZE ROOTPARTITION
ANALYZE
db1=# analyze rootpartition sales;
ANALYZE

3. 统计配置
（1）统计目标
        统计目标指的是一个列的most_common_vals、most_common_freqs和histogram_bounds数组的大小。这些数组的含义可以从上面pg_stats视图的定义得到。缺省目标值为25。可以通过设置服务器配置参数修改全局目标值，也可以使用ALTER TABLE命令设置任何表列的目标值。目标值越大，ANALYZE需要的时间越长，但可以提高优化器的评估质量。
        default_statistics_target服务器配置参数设置系统缺省的统计目标。缺省值25通常已经足够，只有经过测试确定要定义一个新目标时，才考虑更改此参数的值。
        可以通过Ambari Web UI和命令行两种方法修改配置参数值。下面的例子使用hawq config命令行应用将统计目标从25改为50。
  • 以HAWQ管理员（缺省为gpadmin）登录HAWQ的master主机并设置环境。
    $ source /usr/local/hawq/greenplum_path.sh
  • 使用hawq config应用设置default_statistics_target。
    $ hawq config -c default_statistics_target -v 50
  • 重载使配置生效。
    $ hawq stop cluster -u
        单个列的统计目标可以用ALTER TABLE命令设置。例如，某些查询可以通过为特定列，尤其是分布不规则的列增加目标值提高性能。如果将一列的目标值设置为0，ANALYZE忽略该列。下面的命令将desc1列的统计目标设置为0，因为该列对于查询优化没有任何作用。
db1=# alter table t1 alter column desc1 set statistics 0;
ALTER TABLE
        统计目标可以设置为0到1000之间的值，或者设置成-1，此时恢复使用系统缺省的统计目标值。
        父分区表上设置的统计目标影响子分区。如果父表上某列的目标设置为0，所有子分区上的该列统计目标也为0。但是，如果以后增加或者交换了其它子分区，新增的子分区将使用缺省目标值，交换的子分区使用以前的统计目标。因此如果增加或交换了子分区，应该在新的子分区上设置统计目标。

（2）自动收集统计信息

        如果一个表没有统计信息，或者在表上执行的特定操作改变了大量的数据时，HAWQ可以在表上自动运行ANALYZE。对于分区表，自动统计收集仅当直接操作叶表时被触发，它仅分析叶表。
        自动收集统计信息有三种模式：
  • none：禁用自动收集。
  • on_no_stats：在一个没有统计信息的表上执行CREATE TABLE AS SELECT、INSERT、COPY命令时触发分析操作。
  • on_change：在表上执行CREATE TABLE AS SELECT、INSERT、COPY命令，并且影响的行数超过了gp_autostats_on_change_threshold配置参数设定的阈值时触发分析操作。
        依据CREATE TABLE AS SELECT、INSERT、COPY这些命令是单独执行，还是在函数中执行，自动收集统计信息模式的设置方法也不一样。如果是在函数外单独执行，gp_autostats_mode配置参数控制统计模式，缺省值为on_no_stats。
[gpadmin@hdp3 ~]$ hawq config -s gp_autostats_mode
GUC		: gp_autostats_mode
Value		: ON_NO_STATS
        on_change模式仅当影响的行数超过gp_autostats_on_change_threshold配置参数设置的阈值时触发ANALYZE，该参数的缺省值为2147483647。
[gpadmin@hdp3 ~]$ hawq config -s gp_autostats_on_change_threshold
GUC		: gp_autostats_on_change_threshold
Value		: 2147483647
        on_change模式可能触发不希望的大的分析操作，严重的会使系统中断，因此不推荐在全局修改该参数，但可以在会话级设置，例如装载数据后自动分析。
        为了禁用函数外部的自动统计收集，设置gp_autostats_mode参数为none。例如：
$ hawq configure -c gp_autostats_mode -v none
        如果想记录自动统计收集操作的日志，可以设置log_autostats系统配置蚕食参数为on。

用HAWQ轻松取代传统数据仓库（十一） —— 数据管理


联系
我们