本文介绍了HAWQ数据库的基本操作，包括INSERT和SELECT语句的使用方法，以及整理系统目录表的操作。此外，还详细讲解了数据的装载与卸载过程，包括使用gpfdist和基于Web的外部表进行数据装载的方法。最后，本文探讨了数据库统计的重要性及如何通过ANALYZE命令更新统计信息。
目录
（2）pg_statistic 系统表与 pg_stats 视图
一、基本操作
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.
VACUUMfree 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 : 200000max_fsm_relations（整数）设置 free space map 跟踪的最大表数，每个表槽位占用 7 字节左右的共享内存，缺省值为 1000。设置该参数后需要重启 HAWQ 使其生效。
[gpadmin@hdp3 ~]$ hawq config -s max_fsm_relations
GUC : max_fsm_relations
Value : 10003. 其他操作
与 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 libyamlHAWQ 没有提供停止 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 命令。可以使用通配符（*）或其他 C 语言风格的模式匹配多个需要读取的文件，指定的文件应该位于启动 gpfdist 实例时指定的目录下。gpfdist 自动解压缩 gzip（.gz）和 bzip2（.bz2）文件。
所有虚拟段并行访问外部文件，虚拟段的数量受 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 外部表的步骤如下：
- 定义外部表。
- 启动 gpfdist 文件服务器。
- 将数据文件放置于外部表定义中指定的位置。
- 使用 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 TABLEHAWQ 集群中每台主机的相同位置上都必须有同一个可执行的脚本，否则查询会报错，如 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 TABLEmyprogram1.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 在一行数据达到 64KB 时停止解析，并将此 64KB 数据作为单行写入错误日志表，然后重置引号标记，继续读取数据。如果这种情况在处理装载时发生三次，载入文件被认为是无效的，整个装载失败，错误信息为“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缺省时，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;
ANALYZE3. 统计配置
（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_STATSon_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 : 2147483647on_change 模式可能触发不希望的大的分析操作，严重的会使系统中断，因此不推荐在全局修改该参数，但可以在会话级设置，例如装载数据后自动分析。
为了禁用函数外部的自动统计收集，设置 gp_autostats_mode 参数为 none。例如：
$ hawq configure -c gp_autostats_mode -v none如果想记录自动统计收集操作的日志，可以设置 log_autostats 系统配置蚕食参数为 on。
扫一扫
746
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
