人大金仓分析型数据库外部表（一）

定义外部表

file://协议

gpfdist://协议

gpfdists:// 协议

s3:// 协议

外部表是数据库可以支持存储在数据库之外的数据的表。您可以创建一个可读的外部表来从外部数据源读取数据，也可以创建一个可写的外部表以将数据写入外部数据源。您可以像在常规数据库表中一样在SQL命令中使用外部表。例如，您可以使用SELECT（可读外部表），INSERT（可写外部表）以及将外部表与其他表连接。外部表通常用于加载和卸载数据库数据。

定义外部表

要创建一个外部表定义，用户需要指定输入文件的格式以及外部数据源的位置。使用下列协议之一来访问外部表数据源。用户不能在CREATE EXTERNAL TABLE语句中混用协议：

file:// 访问 Segment 主机上数据超级用户能访问的外部数据文件
gpfdist:// 指向一个位于文件主机上的目录并且向数据库所有的实例并行提供外部数据文件
gpfdists:// 是 gpfdist的一个安全版本
s3://访问 Amazon S3中的文件
pxf://协议使用平台扩展框架（ PXF ）访问外部 HDFS 文件和 HBase和Hive表

外部表从数据库内访问外部文件，就好像它们是常规的数据库表一样。用gpfdist/gpfdists, pxf, 和 s3协议定义的外部表通过使用所有数据库的实例资源装载或卸载数据来利用数据库的并行机制。 pxf协议利用Hadoop分布式文件系统的并行体系结构来访问该系统上的文件。 s3协议利用了Amazon Web服务（AWS）的功能。用户可以使用SELECT、JOIN或SORT EXTERNAL TABLE DATA等SQL命令直接并行地查询外部表数据，并且用户可以为外部表创建视图。

file://协议

file://协议被用在一个指定操作系统文件的URI中。该URI包括主机名、端口和该文件的路径。每个文件都必须位于一个实例主机上由超级用户可访问的位置。该URI中使用的主机名必须匹配gp_segment_configuration系统目录表中注册的一个实例主机名。如例子中所示，LOCATION子句可以有多个URI：

CREATE EXTERNAL TABLE ext_expenses (
name text, date date, amount float4, category text, desc1 text )
LOCATION ('file://host1:5432/data/expense/*.csv',
'file://host2:5432/data/expense/*.csv',
'file://host3:5432/data/expense/*.csv')
FORMAT 'CSV' (HEADER);

用户在LOCATION子句中指定的URI的数量就是将并行工作访问外部表的实例的数量。对于每一个URI，数据库为该文件指派一个指定主机上的主实例。为了装载数据时的最大并行化，可将数据与主实例数量相同的大小均等的文件。这会保证所有的实例都参与到装载中。每个实例主机上的外部文件不能超过该主机上主实例的数量。基于file://协议的表只能是可读表。系统视图pg_max_external_files显示每个外部表允许多少个外部表文件。这个视图列出了使用file://协议协议时每个实例主机上可用的文件槽。该视图只适用于file://协议。例如：

SELECT * FROM pg_max_external_files;

gpfdist://协议

gpfdist工具从一个文件主机上的目录中把外部数据文件并行提供给数据库的所有实例。在外部数据文件所在的主机上运行gpfdist。 gpfdist 会自动解压 gzip (.gz) 和 bzip2 (.bz2)。用户可以使用通配符（*）或者其他C风格的模式匹配来表示要读取的多个文件。指定的文件都被假定是相对于启动 gpfdist实例时指定的目录。所有的主实例并行地访问外部文件，实例的数量服是gp_external_max_segments服务器配置参数中设置的数量。在CREATE EXTERNAL TABLE语句中使用多个gpfdist数据源可以提高外部表的扫描性能。

gpfdists:// 协议

gpfdists:// 协议是gpfdist:// 协议的一个安全版本。要使用它，用户需要以--ssl选项运行gpfdist工具。当在一个URI中指定gpfdists://协议时，它启用文件服务器和数据库的加密通信以及安全鉴定以保护其不受窃听和中间人等攻击。服务器配置参数verify_gpfdists_cert 控制数据库与gpfdist实用程序通信以从外部数据源读取数据或将数据写入外部数据源时是否启用SSL证书认证。在测试数据库外部表与提供外部数据的gpfdist实用程序之间的通信时，可以将参数值设置为false，以禁用身份验证。如果值为false，这些SSL异常将被忽略：

gpfdist使用的自签名SSL证书不被数据库信任
SSL证书中包含的主机名与运行 gpfdist的主机名不匹配

s3:// 协议

用户可以定义只读外部表使用S3桶中现有的数据文件作为表数据，或者创建可写外部表将来自于INSERT操作的数据存储到S3桶中的文件。数据库使用协议URL中指定的S3 URL以及前缀来为一个只读表选择一个或更多文件，还可以在为 INSERT操作上传S3文件到可写表中时定义文件位置和文件名格式。对于s3协议，用户需要在CREATE EXTERNAL TABLE命令的LOCATION子句中指定文件的位置以及一个可选的配置文件位置。下面是语法：

's3://S3_endpoint[:port]/bucket_name/[S3_prefix] [region=S3_region] [config=config_file_location

对于可写的S3表， s3 协议的 URL 指定数据库为该表上传数据文件的端点。对于上传文件的S3 用户 ID ，该 S3 桶的权限必须是 Upload/Delete 。对于每一个由于向表中插入数据形成的新的上传文件，都会使用这个S3文件前缀。