网络
虚拟私有云 VPC
弹性公网IP EIP
弹性负载均衡 ELB
NAT网关 NAT
云专线 DC
虚拟专用网络 VPN
云连接 CC
VPC终端节点 VPCEP
企业路由器 ER
企业交换机 ESW
全球加速 GA
企业连接 EC
云原生应用网络 ANC
安全与合规
安全技术与应用
Web应用防火墙 WAF
企业主机安全 HSS
云防火墙 CFW
安全云脑 SecMaster
DDoS防护 AAD
数据加密服务 DEW
数据库安全服务 DBSS
云堡垒机 CBH
数据安全中心 DSC
云证书管理服务 CCM
威胁检测服务 MTD
态势感知 SA
认证测试中心 CTC
边缘安全 EdgeSec
应用中间件
微服务引擎 CSE
分布式消息服务Kafka版
分布式消息服务RabbitMQ版
分布式消息服务RocketMQ版
API网关 APIG
分布式缓存服务 DCS
多活高可用服务 MAS
事件网格 EG
管理与监管
统一身份认证服务 IAM
消息通知服务 SMN
云监控服务 CES
应用运维管理 AOM
应用性能管理 APM
云日志服务 LTS
云审计服务 CTS
标签管理服务 TMS
配置审计 Config
应用身份管理服务 OneAccess
资源访问管理 RAM
组织 Organizations
资源编排服务 RFS
优化顾问 OA
IAM 身份中心
云运维中心 COC
资源治理中心 RGC
解决方案
高性能计算 HPC
SAP
混合云灾备
开天工业工作台 MIW
Haydn解决方案工厂
数字化诊断治理专家服务
云生态
云商店
合作伙伴中心
华为云开发者学堂
华为云慧通差旅
开发与运维
软件开发生产线 CodeArts
需求管理 CodeArts Req
流水线 CodeArts Pipeline
代码检查 CodeArts Check
编译构建 CodeArts Build
部署 CodeArts Deploy
测试计划 CodeArts TestPlan
制品仓库 CodeArts Artifact
移动应用测试 MobileAPPTest
CodeArts IDE Online
开源镜像站 Mirrors
性能测试 CodeArts PerfTest
应用管理与运维平台 ServiceStage
云应用引擎 CAE
开源治理服务 CodeArts Governance
华为云Astro轻应用
CodeArts IDE
Astro工作流 AstroFlow
代码托管 CodeArts Repo
漏洞管理服务 CodeArts Inspector
联接 CodeArtsLink
软件建模 CodeArts Modeling
Astro企业应用 AstroPro
CodeArts 盘古助手
华为云Astro大屏应用
计算
弹性云服务器 ECS
Flexus云服务
裸金属服务器 BMS
云手机服务器 CPH
专属主机 DeH
弹性伸缩 AS
镜像服务 IMS
函数工作流 FunctionGraph
云耀云服务器(旧版)
VR云渲游平台 CVR
Huawei Cloud EulerOS
网络
虚拟私有云 VPC
弹性公网IP EIP
弹性负载均衡 ELB
NAT网关 NAT
云专线 DC
虚拟专用网络 VPN
云连接 CC
VPC终端节点 VPCEP
企业路由器 ER
企业交换机 ESW
全球加速 GA
企业连接 EC
云原生应用网络 ANC
CDN与智能边缘
内容分发网络 CDN
智能边缘云 IEC
智能边缘平台 IEF
CloudPond云服务
安全与合规
安全技术与应用
Web应用防火墙 WAF
企业主机安全 HSS
云防火墙 CFW
安全云脑 SecMaster
DDoS防护 AAD
数据加密服务 DEW
数据库安全服务 DBSS
云堡垒机 CBH
数据安全中心 DSC
云证书管理服务 CCM
威胁检测服务 MTD
态势感知 SA
认证测试中心 CTC
边缘安全 EdgeSec
大数据
MapReduce服务 MRS
数据湖探索 DLI
表格存储服务 CloudTable
可信智能计算服务 TICS
推荐系统 RES
云搜索服务 CSS
数据可视化 DLV
数据接入服务 DIS
数据仓库服务 GaussDB(DWS)
数据治理中心 DataArts Studio
湖仓构建 LakeFormation
智能数据洞察 DataArts Insight
应用中间件
微服务引擎 CSE
分布式消息服务Kafka版
分布式消息服务RabbitMQ版
分布式消息服务RocketMQ版
API网关 APIG
分布式缓存服务 DCS
多活高可用服务 MAS
事件网格 EG
开天aPaaS
应用平台 AppStage
开天企业工作台 MSSE
开天集成工作台 MSSI
API中心 API Hub
云消息服务 KooMessage
交换数据空间 EDS
云地图服务 KooMap
云手机服务 KooPhone
组织成员账号 OrgID
云空间服务 KooDrive
管理与监管
统一身份认证服务 IAM
消息通知服务 SMN
云监控服务 CES
应用运维管理 AOM
应用性能管理 APM
云日志服务 LTS
云审计服务 CTS
标签管理服务 TMS
配置审计 Config
应用身份管理服务 OneAccess
资源访问管理 RAM
组织 Organizations
资源编排服务 RFS
优化顾问 OA
IAM 身份中心
云运维中心 COC
资源治理中心 RGC
区块链
区块链服务 BCS
数字资产链 DAC
华为云区块链引擎服务 HBS
解决方案
高性能计算 HPC
SAP
混合云灾备
开天工业工作台 MIW
Haydn解决方案工厂
数字化诊断治理专家服务
价格
成本优化最佳实践
专属云商业逻辑
云生态
云商店
合作伙伴中心
华为云开发者学堂
华为云慧通差旅
其他
管理控制台
消息中心
产品价格详情
系统权限
客户关联华为云合作伙伴须知
公共问题
宽限期保留期
奖励推广计划
活动
云服务信任体系能力说明
开发与运维
软件开发生产线 CodeArts
需求管理 CodeArts Req
流水线 CodeArts Pipeline
代码检查 CodeArts Check
编译构建 CodeArts Build
部署 CodeArts Deploy
测试计划 CodeArts TestPlan
制品仓库 CodeArts Artifact
移动应用测试 MobileAPPTest
CodeArts IDE Online
开源镜像站 Mirrors
性能测试 CodeArts PerfTest
应用管理与运维平台 ServiceStage
云应用引擎 CAE
开源治理服务 CodeArts Governance
华为云Astro轻应用
CodeArts IDE
Astro工作流 AstroFlow
代码托管 CodeArts Repo
漏洞管理服务 CodeArts Inspector
联接 CodeArtsLink
软件建模 CodeArts Modeling
Astro企业应用 AstroPro
CodeArts 盘古助手
华为云Astro大屏应用
存储
对象存储服务 OBS
云硬盘 EVS
云备份 CBR
高性能弹性文件服务 SFS Turbo
弹性文件服务 SFS
存储容灾服务 SDRS
云硬盘备份 VBS
云服务器备份 CSBS
数据快递服务 DES
云存储网关 CSG
专属分布式存储服务 DSS
数据工坊 DWR
地图数据 MapDS
键值存储服务 KVS
容器
云容器引擎 CCE
云容器实例 CCI
容器镜像服务 SWR
云原生服务中心 OSC
应用服务网格 ASM
华为云UCS
数据库
云数据库 RDS
数据复制服务 DRS
文档数据库服务 DDS
分布式数据库中间件 DDM
云数据库 GaussDB
云数据库 GeminiDB
数据管理服务 DAS
数据库和应用迁移 UGO
云数据库 TaurusDB
人工智能
AI开发平台ModelArts
华为HiLens
图引擎服务 GES
图像识别 Image
文字识别 OCR
自然语言处理 NLP
内容审核 Moderation
图像搜索 ImageSearch
医疗智能体 EIHealth
企业级AI应用开发专业套件 ModelArts Pro
人脸识别服务 FRS
对话机器人服务 CBS
语音交互服务 SIS
人证核身服务 IVS
视频智能分析服务 VIAS
城市智能体
自动驾驶云服务 Octopus
盘古大模型 PanguLargeModels
IoT物联网
设备接入 IoTDA
全球SIM联接 GSL
IoT数据分析 IoTA
路网数字化服务 DRIS
IoT边缘 IoTEdge
设备发放 IoTDP
企业应用
域名注册服务 Domains
云解析服务 DNS
企业门户 EWP
ICP备案
商标注册
华为云WeLink
华为云会议 Meeting
隐私保护通话 PrivateNumber
语音通话 VoiceCall
消息&短信 MSGSMS
云管理网络
SD-WAN 云服务
边缘数据中心管理 EDCM
云桌面 Workspace
应用与数据集成平台 ROMA Connect
ROMA资产中心 ROMA Exchange
API全生命周期管理 ROMA API
政企自服务管理 ESM
视频
实时音视频 SparkRTC
视频直播 Live
视频点播 VOD
媒体处理 MPC
视频接入服务 VIS
数字内容生产线 MetaStudio
迁移
主机迁移服务 SMS
对象存储迁移服务 OMS
云数据迁移 CDM
迁移中心 MGC
专属云
专属计算集群 DCC
开发者工具
SDK开发指南
API签名指南
DevStar
华为云命令行工具服务 KooCLI
Huawei Cloud Toolkit
CodeArts API
云化转型
云架构中心
云采用框架
用户服务
账号中心
费用中心
成本中心
资源中心
企业管理
工单管理
客户运营能力
国际站常见问题
支持计划
专业服务
合作伙伴支持计划
我的凭证
华为云公共事业服务云平台
工业软件
工业数字模型驱动引擎
硬件开发工具链平台云服务
工业数据转换引擎云服务
更新时间:2024-08-01 GMT+08:00
分享

CREATE FOREIGN TABLE (SQL on OBS or Hadoop)

功能描述

在当前数据库创建一个HDFS或OBS外表,用来访问存储在HDFS或者OBS分布式集群文件系统上的结构化数据。也可以导出ORC格式数据到HDFS或者OBS上。

数据存储在OBS:数据存储和计算分离,集群存储成本低,存储量不受限制,并且集群可以随时删除,但计算性能取决于OBS访问性能,相对HDFS有所下降,建议在数据计算不频繁场景下使用。

数据存储在HDFS:数据存储和计算不分离,集群成本较高,计算性能高,但存储量受磁盘空间限制,删除集群前需将数据导出保存,建议在数据计算频繁场景下使用。

实时数仓(单机部署)暂不支持OBS和HDFS外表导入导出功能。

注意事项

  • HDFS外表与OBS外表分为只读外表和只写外表,只读外表用于查询操作,只写外表可以将GaussDB(DWS)中的数据导出到分布式文件系统中。
  • 支持ORC、TEXT、CSV、CARBONDATA、PARQUET和JSON格式的导入查询,OBS外表支持ORC、CSV和TEXT格式的导出。HDFS外表仅支持ORC格式的导出。
  • 该方式需要用户手动创建外部服务器,具体请参见CREATE SERVER
  • 若手动创建Server时指定foreign data wrapper为HDFS_FDW或者DFS_FDW,创建只读外表时需DISTRIBUTE BY子句指定分布方式。

语法格式

创建外表。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name 
( [ { column_name type_name 
    [ { [CONSTRAINT constraint_name] NULL |
    [CONSTRAINT constraint_name] NOT NULL |
      column_constraint [...]} ] |
      table_constraint [, ...]} [, ...] ] ) 
    SERVER server_name 
    OPTIONS ( { option_name ' value ' } [, ...] ) 
    [ {WRITE ONLY | READ ONLY}]
    DISTRIBUTE BY {ROUNDROBIN | REPLICATION}
   
    [ PARTITION BY ( column_name ) [ AUTOMAPPED ] ] ;
  • 其中column_constraint为:
    1
    2
    3
    [CONSTRAINT constraint_name]
    {PRIMARY KEY | UNIQUE}
    [NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED]
    
  • 其中table_constraint为:
    1
    2
    3
    [CONSTRAINT constraint_name]
    {PRIMARY KEY | UNIQUE} (column_name)
    [NOT ENFORCED [ENABLE QUERY OPTIMIZATION | DISABLE QUERY OPTIMIZATION] | ENFORCED]
    

参数说明

  • IF NOT EXISTS

    如果已经存在相同名称的表,不会抛出一个错误,而会发出一个通知,告知表关系已存在。

  • table_name

    外表的表名。

    取值范围:字符串,要符合标识符的命名规范。

  • column_name

    外表中的字段名。可以选择多个字段名,中间用“,”隔开。

    取值范围:字符串,要符合标识符的命名规范。

    JSON对象由嵌套或并列的name-value对组成,具有顺序无关性,当导入JSON格式数据时,需要通过字段名与name的自动对应来确定字段与value的对应关系。用户需要定义恰当的字段名,否则可能导致导入结果不符合预期。字段名与name的自动对应规则如下:

    • 无嵌套无数组的情况下,字段名应当与name一致,不区分大小写。
    • 字段名使用‘_’字符拼接两个name,标识嵌套关系。
    • 字段名使用‘#’字符加十进制非负整数‘n’标识数组的第n个元素(从0开始)。

    例如,要导入JSON对象{"A" : "simple", "B" : {"C" : "nesting"}, "D" : ["array", 2, {"E" : "complicated"}]}中的每个元素,外表字段名应当分别定义为a、b、b_c、d、d#0、d#1、d#2、d#2_e,字段的定义顺序不会影响导入结果的正确性。

  • type_name

    字段的数据类型。

  • constraint_name

    外表的表约束名。

  • { NULL | NOT NULL }

    标识此列是否允许NULL值。

    在创建表时,对于列的约束NULL/NOT NULL,并不能保证该表在HDFS系统中的数据为NULL或者NOT NULL,数据的一致性由用户保证。所以需要由用户判断该列是否一定不为空或者一定为空,在建表的时候选用NULL或NOT NULL。(优化器对列为NULL/NOT NULL做了优化处理,会产生更优的计划。)

  • SERVER server_name

    外表的server名字。允许用户自定义名字。

    取值范围:字符串,要符合标识符的命名规范,并且这个server必须存在。

  • OPTIONS ( { option_name ' value ' } [, ...] )
    用于指定外表数据的各类参数,参数类型如下所示。
    • header

      指定导出数据文件是否包含标题行,标题行一般用来描述表中每个字段的信息。header只能用于CSV格式的文件中。

      如果header选项为on,则数据文件第一行会被识别为标题行,导出时会忽略此行。如果header为off,而数据文件中第一行会被识别为数据。

      取值范围:true/on,false/off。缺省值为false/off。

    • quote

      CSV格式文件下的引号字符,缺省值为双引号。

      quote参数不能和分隔符、null参数相同。

      quote参数只能是单字节的字符。

      推荐不可见字符作为quote,例如0x07,0x08,0x1b等。

    • escape

      CSV格式下,用来指定逃逸字符,逃逸字符只能指定为单字节字符。

      缺省值为双引号。当与quote值相同时,会被替换为'\0'。

    • location

      OBS外表参数,指定存储在OBS上的文件路径,多个桶的数据源数据之间使用分隔符‘|’进行分割,例如:LOCATION 'obs://bucket1/folder/ | obs://bucket2/',数据库将会扫描指定路径文件夹下面的所有对象。

      当访问DLI多版本表时,无需指定location参数。

    • format:外表中数据源文件的格式。
      • HDFS外表READ ONLY外表支持ORC、TEXT、JSON、CSV、PARQUET文件格式,而WRITE ONLY外表只支持ORC文件格式。
      • OBS外表READ ONLY外表支持ORC、TEXT、JSON、CSV、CARBONDATA、PARQUET文件格式,而WRITE ONLY外表只支持ORC文件格式。

      对于JSON格式数据,仅支持JSON对象(object,最外层由{}构造)导入,不支持JSON数组(array,最外层由[]构造)导入,但支持JSON对象内部数组的导入。

    • foldername:外表中数据源文件目录,即表数据目录在HDFS文件系统和OBS上对应的文件目录。此选项对WRITE ONLY外表为必选项,对READ ONLY外表为可选项。

      当访问DLI多版本表时,无需指定foldername参数。

    • encoding:外表中数据源文件的编码格式名称,缺省为utf8。此选项为可选参数。
    • totalrows:可选参数,估计表的行数,仅OBS外表使用。由于OBS上文件可能很多,执行analyze可能会很慢,通过此参数让用户设置一个预估的值,使优化器能通过这个值做大小表的估计。一般预估值和实际值相近时,查询效率较高。
    • filenames:外表中数据源文件,以","间隔。
      • 推荐通过使用foldername参数指定数据源的位置,对于只读外表filenames参数与foldername参数两者必有其一,而只写外表只能通过foldername指定。
      • foldername为绝对目录时,前后必须有'/', 多个路径用', '分隔。
      • 查询分区表时,会先根据分区信息进行剪枝,然后查询满足条件的数据文件。由于剪枝操作会涉及多次扫描HDFS分区目录内容,不建议使用重复度非常小的列作为分区列,因为这可能导致分区目录非常的多,增加对HDFS的查询压力。
      • OBS只读外表不支持。
    • delimiter

      指定数据文件行数据的字段分隔符,不指定则使用默认分隔符,TEXT格式的默认分隔符是水平制表符(tab)。

      • 分隔符不能是\r和\n。
      • 分隔符不能和null参数相同。
      • 分隔符不能包含“\”、“.”、数字和字母。
      • 数据文件中单行数据长度需<1GB,如果分隔符较长且数据列较多的情况下,会影响导出有效数据的长度。
      • 分隔符推荐使用多字符(例如'$^&')和不可见字符(例如0x07、0x08、0x1b等)。
      • delimiter参数只在TEXT和CSV格式下有效。

      取值范围:

      支持多字符分隔符,但分隔符不能超过10个字节。

    • eol

      指定导入数据文件换行符样式。

      取值范围:支持多字符换行符,但换行符不能超过10个字节。常见的换行符,如\r、\n、\r\n(设成0x0D、0x0A、0x0D0A效果是相同的),其他字符或字符串,如$、#。

      • eol参数只能用于TEXT格式的导入。
      • eol参数不能和分隔符、null参数相同。
      • eol参数不能包含:数字,字母和符号“.”。
    • null
      用来指定数据文件中空值的表示。
      • null值不能是\r和\n,最大为100个字符。
      • null值不能是分隔符。
      • null参数只在TEXT和CSV格式下有效。

      取值范围:

      在TEXT格式下缺省值是\N。

    • noescaping

      TEXT格式下,不对'\'和后面的字符进行转义。

      noescaping参数只在TEXT格式下有效。

      取值范围:true/on,false/off。缺省值为false/off。

    • fill_missing_fields

      当数据加载时,若数据源文件中一行的最后一个字段缺失时的处理方式。

      取值范围:true/on,false/off。缺省值为false/off。

      • 参数为true/on,当数据加载时,若数据源文件中一行数据的最后一个字段缺失,则把最后一个字段的值设置为NULL,不报错。
      • 参数为false/off,如果最后一个字段缺失会显示如下错误信息。
        missing data for column "tt"
      • TEXT格式下执行SELECT COUNT(*) 不会去解析具体字段,因此不会对字段缺失情况报错。
      • fill_missing_fields参数只在TEXT和CSV格式下有效。
    • ignore_extra_data

      若数据源文件比外表定义列数多,是否会忽略多出的列。该参数只在数据导入过程中使用。

      取值范围:true/on,false/off。缺省值为false/off。

      • 参数为true/on,若数据源文件比外表定义列数多,则忽略行尾多出来的列。
      • 参数为false/off,若数据源文件比外表定义列数多,会显示如下错误信息。
        extra data after last expected column
      • 如果行尾换行符丢失,使两行变成一行时,设置此参数为true将导致后一行数据被忽略掉。
      • TEXT格式下执行SELECT COUNT(*) 不会去解析具体字段,因此不会对多余的情况报错。
      • ignore_extra_data参数只在TEXT和CSV格式下有效。
    • date_format

      导入对于DATE类型指定格式。此语法仅对READ ONLY的外表有效。

      取值范围:合法DATE格式。可参考时间、日期处理函数和操作符

      • 对于指定为ORACLE兼容类型的数据库,则DATE类型内建为TIMESTAMP类型。在导入的时候,若需指定格式,可以参考下面的timestamp_format参数。
      • date_format参数只在TEXT和CSV格式下有效。
    • time_format

      导入对于TIME类型指定格式。此语法仅对READ ONLY的外表有效。

      取值范围:合法TIME格式,不支持时区。可参考时间、日期处理函数和操作符

      time_format参数只在TEXT和CSV格式下有效。

    • timestamp_format

      导入对于TIMESTAMP类型指定格式。此语法仅对READ ONLY的外表有效。

      取值范围:合法TIMESTAMP格式,不支持时区。可参考时间、日期处理函数和操作符

      timestamp_format参数只在TEXT和CSV格式下有效。

    • smalldatetime_format

      导入对于SMALLDATETIME类型指定格式。此语法仅对READ ONLY的外表有效。

      取值范围:合法SMALLDATETIME格式。可参考时间、日期处理函数和操作符

      smalldatetime_format参数只在TEXT和CSV格式下有效。

    • dataencoding

      在数据库编码与数据表的数据编码不一致时,该参数用于指定导出数据表的数据编码。比如数据库编码为Latin-1,而导出的数据表中的数据为UTF-8编码。此选项为可选项,如果不指定该选项,默认采用数据库编码。此语法仅对HDFS的WRITE ONLY外表有效。

      取值范围:该数据库编码支持转换的数据编码。

      dataencoding参数只对ORC格式的WRITE ONLY的HDFS外表有效。

    • filesize

      指定WRITE ONLY外表的文件大小。此选项为可选项,不指定该选项默认分布式文件系统配置中文件大小的配置值。此语法仅对WRITE ONLY的外表有效。

      取值范围:[1, 1024]的整数。

      filesize参数只对ORC格式的WRITE ONLY的HDFS外表有效。

    • compression

      指定ORC格式文件的压缩方式,此选项为可选项。此语法仅对WRITE ONLY的外表有效。

      取值范围:zlib,snappy,lz4。缺省值为snappy。

    • version

      指定ORC格式的版本号,此选项为可选项。此语法仅对WRITE ONLY的外表有效。

      取值范围:目前仅支持0.12。缺省值为0.12。

    • dli_project_id

      DLI服务对应的项目编号,可在管理控制台上获取项目ID,该参数仅支持server类型为DLI时设置。该参数仅8.1.1及以上版本支持。

    • dli_database_name

      待访问的DLI多版本表所在的数据库名称,该参数仅支持server类型为DLI时设置。该参数仅8.1.1及以上版本支持。

    • dli_table_name

      待访问的DLI多版本表的名称,该参数仅支持server类型为DLI时设置。该参数仅8.1.1及以上版本支持。

    • checkencoding

      是否检查字符编码

      取值范围:low,high 。缺省值为low。

      TEXT格式下,导入非法字符容错规则如下:

      • 对于'\0',容错后转换为空格;
      • 对于其他非法字符,容错后转换为问号;
      • 若checkencoding为low标识,导入时对于非法字符进行容错处理,则若NULL、DELIMITER设置为空格或问号则会通过如"illegal chars conversion may confuse null 0x20"等报错信息提示用户修改可能引起混淆的参数以避免导入错误。

      ORC格式下,导入非法字符容错规则如下:

      • checkencoding为low标识,若导入时检查到某个字段中包含非法字符,则自动将当前列当前行的字段整体替换为同样长度的‘?’字符;
      • checkencoding为high标识,若导入时检查到某个字段中包含非法字符,则报错退出。
    • force_mapping

      JSON格式下,外表列无法匹配到正确的name-value键值对时的处理方式。

      取值范围:true,false。缺省值为true。

      • force_mapping为true,相应的列填null,该null与JSON定义中的null含义相同。
      • force_mapping为false,查询报错,提示不存在这样的列。

      由于对JSON对象没有限制,但外表字段定义需要符合GaussDB(DWS)的标识符规范(例如长度、字符等限制),因此这种导入方式可能导致异常:例如,字段无法正确标识JSON name、字段需重复定义等。建议使用容错性选项force_mapping或json操作符(可参考JSON/JSONB函数和操作符)来规避。

      JSON格式下执行SELECT COUNT(*) 不会去解析具体字段,因此不会对字段缺失、格式错误等情况报错。

    表1 text、csv、json、orc、carbondata、parquet格式对OBS外表的option支持说明

    参数名称

    OBS

    -

    TEXT

    CSV

    JSON

    ORC

    CARBONDATA

    PARQUET

    READ ONLY

    READ ONLY

    READ ONLY

    READ ONLY

    WRITE ONLY

    READ ONLY

    READ ONLY

    location

    ×

    format

    header

    ×

    ×

    ×

    ×

    ×

    ×

    delimiter

    ×

    ×

    ×

    ×

    ×

    quote

    ×

    ×

    ×

    ×

    ×

    ×

    escape

    ×

    ×

    ×

    ×

    ×

    ×

    null

    ×

    ×

    ×

    ×

    ×

    noescaping

    ×

    ×

    ×

    ×

    ×

    ×

    encoding

    fill_missing_fields

    ×

    ×

    ×

    ×

    ×

    ignore_extra_data

    ×

    ×

    ×

    ×

    ×

    date_format

    ×

    ×

    ×

    ×

    time_format

    ×

    ×

    ×

    ×

    timestamp_format

    ×

    ×

    ×

    ×

    smalldatetime_format

    ×

    ×

    ×

    ×

    chunksize

    ×

    ×

    ×

    ×

    filenames

    ×

    ×

    ×

    ×

    ×

    ×

    ×

    foldername

    dataencoding

    ×

    ×

    ×

    ×

    ×

    ×

    ×

    filesize

    ×

    ×

    ×

    ×

    ×

    ×

    ×

    compression

    ×

    ×

    ×

    ×

    ×

    ×

    version

    ×

    ×

    ×

    ×

    ×

    ×

    checkencoding

    ×

    totalrows

    ×

    ×

    ×

    force_mapping

    ×

    ×

    ×

    ×

    ×

    ×

    表2 text、csv、json、orc、parquet格式对HDFS外表的option支持说明

    参数名称

    HDFS

    -

    TEXT

    CSV

    JSON

    ORC

    PARQUET

    READ ONLY

    READ ONLY

    READ ONLY

    READ ONLY

    WRITE ONLY

    READ ONLY

    location

    ×

    ×

    ×

    ×

    ×

    ×

    format

    header

    ×

    ×

    ×

    ×

    ×

    delimiter

    ×

    ×

    ×

    ×

    quote

    ×

    ×

    ×

    ×

    ×

    escape

    ×

    ×

    ×

    ×

    ×

    null

    ×

    ×

    ×

    ×

    noescaping

    ×

    ×

    ×

    ×

    ×

    encoding

    fill_missing_fields

    ×

    ×

    ×

    ×

    ignore_extra_data

    ×

    ×

    ×

    ×

    date_format

    ×

    ×

    ×

    time_format

    ×

    ×

    ×

    timestamp_format

    ×

    ×

    ×

    smalldatetime_format

    ×

    ×

    ×

    chunksize

    ×

    ×

    ×

    filenames

    ×

    foldername

    dataencoding

    ×

    ×

    ×

    ×

    ×

    filesize

    ×

    ×

    ×

    ×

    ×

    compression

    ×

    ×

    ×

    ×

    ×

    version

    ×

    ×

    ×

    ×

    ×

    checkencoding

    totalrows

    ×

    ×

    ×

    ×

    ×

    ×

    force_mapping

    ×

    ×

    ×

    ×

    ×

  • WRITE ONLY | READ ONLY

    WRITE ONLY指定创建HDFS/OBS的只写外表。

    READ ONLY指定创建HDFS/OBS的只读外表。

    如果不指定创建的外表的类型,默认为只读外表。

  • DISTRIBUTE BY ROUNDROBIN

    指定HDFS/OBS外表为ROUNDROBIN分布方式。

  • DISTRIBUTE BY REPLICATION

    指定HDFS外表为REPLICATION分布方式。

  • PARTITION BY ( column_name ) AUTOMAPPED

    column_name指定分区列。对于分区表,AUTOMAPPED表示HDFS分区外表指定的分区列会和HDFS数据中的分区目录信息自动对应,前提是必须保证HDFS分区外表指定分区列的顺序和HDFS数据中分区目录定义的顺序一致,该功能只适用于只读外表,只写外表不支持。

    • HDFS的只读和只写外表都支持分区表,但是只写外表只支持一级分区,不支持多级分区。
    • OBS只读外表支持分区表,OBS只写外表不支持分区表。
    • 不支持浮点类型和布尔类型的列作为分区列。
    • 分区字段长度限制可通过guc参数 "dfs_partition_directory_length" 调整。
    • 分区目录名称由 "分区列名=分区列值" 组成。此名称内如果包含特殊字符还会经过转义,所以转义前推荐长度不要超过 (dfs_partition_directory_length + 1) / 3 ,以此保证其转义后总长度不会因为超过dfs_partition_directory_length而出现报错。
    • 不推荐包含过长中文字符的列作为分区列。因为一个中文字符和一个英文字符所占的空间大小并不一致,用户不易计算最终的分区目录名称长度,更容易触发超过dfs_partition_directory_length长度限制的报错。
  • CONSTRAINT constraint_name

    用于指定外表所建立的信息约束(Informational Constraint)的名字。

    取值范围:字符串,要符合标识符的命名规范。

  • PRIMARY KEY

    主键约束,表示表里的一个或者一些字段只能包含唯一(不重复)的非NULL值。一个表只能声明一个主键。

  • UNIQUE

    唯一约束,表示表里的一个或者多个字段的组合必须在全表范围内唯一。对于唯一约束,NULL被认为是互相不等的。

  • NOT ENFORCED

    指定所建立的约束为信息约束,该约束不由数据库来保证,而由用户来保证。

  • ENFORCED

    ENFORCED为默认值。预留参数,目前对于ENFORCED不支持。

  • PRIMARY KEY (column_name)

    指定所建立的信息约束位于column_name列上。

    取值范围:字符串,要符合标识符的命名规范,并且这个column_name必须存在。

  • ENABLE QUERY OPTIMIZATION

    利用信息约束对执行计划进行优化。

  • DISABLE QUERY OPTIMIZATION

    禁止利用信息约束对执行计划优化。

信息约束(Informational Constraint)

GaussDB(DWS)中,数据的约束完全由使用者保证,数据源数据能够严格遵守某种信息约束条件,能够加速对已经具有这种约束特征数据的查询。目前外表不支持索引,所以采取使用Informational Constraint信息优化Plan,提高查询性能。

建立外表信息约束的约束条件:

  • 只有用户保证表中的其中一列的非空值具有唯一性时才可以建立Informational Constraint,否则查询结果将与期望值不同。
  • GaussDB(DWS)的Informational Constraint只支持PRIMARY KEY和UNIQUE两种约束。
  • GaussDB(DWS)的Informational Constraint支持NOT ENFORCED属性,不支持ENFORCED属性。
  • 一个表上的多列可以分别建立UNIQUE类型的Informational Constraint,但是PRIMARY KEY一个表中只能建立一个。
  • 一个表的一列上可以建立多个Informational Constraint(由于一个列上有多个约束和一个的作用一致,所以不建议一个列上建立多个Informational Constraint),但是Primary Key类型只能建立一个。
  • 不支持COMMENT。
  • 不支持多列组合约束。
  • ORC格式只写外表不支持同一个集群不同CN向同一外表并发导出。
  • ORC格式只写外表的目录,只能用于GaussDB(DWS)的单个外表的导出目录,不能用于多个外表,并且其他组件不能向此目录写入其他文件。

示例1

在HDFS通过HIVE导入TPC-H benchmark测试数据表part表及region表。part表的文件路径为/user/hive/warehouse/partition.db/part_4region表的文件路径为/user/hive/warehouse/gauss.db/region_orc11_64stripe/

  1. 创建HDFS_Server,对应的foreign data wrapper为HDFS_FDW或者DFS_FDW。
    1
    CREATE SERVER hdfs_server FOREIGN DATA WRAPPER HDFS_FDW OPTIONS (address '10.10.0.100:25000,10.10.0.101:25000',hdfscfgpath '/opt/hadoop_client/HDFS/hadoop/etc/hadoop',type'HDFS');
    
    • 在可选项options里面写入了HDFS集群对应的NameNode的IP地址及端口号。具体端口号请在MRS-HDFS服务配置中搜索参数“dfs.namenode.rpc.port”查看。本示例假设端口号为25000
    • ‘10.10.0.100:25000,10.10.0.101:25000’中列出了两组NameNode的地址及端口号,分别表示HDFS的主NameNode及备NameNode,这里推荐使用该种主备方式填写。两组参量中间使用“,”进行分割
  2. 创建HDFS外表。表关联的HDFS server为hdfs_server,表ft_region对应的HDFS服务器上的文件格式为‘orc’,在HDFS文件系统上对应的文件目录为'/user/hive/warehouse/gauss.db/region_orc11_64stripe/'。
    • 创建不包含分区列的HDFS外表:
       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
      11
      12
      13
      14
      15
      16
      17
      DROP FOREIGN TABLE IF EXISTS ft_region;
      CREATE FOREIGN TABLE ft_region
      (
          R_REGIONKEY INT4,
          R_NAME TEXT,
          R_COMMENT TEXT
      )
      SERVER
          hdfs_server
      OPTIONS
      (
          FORMAT 'orc',
          encoding 'utf8',
          FOLDERNAME '/user/hive/warehouse/gauss.db/region_orc11_64stripe/'
      )
      DISTRIBUTE BY 
           roundrobin;
      
    • 创建包含分区列的HDFS外表:
       1
       2
       3
       4
       5
       6
       7
       8
       9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      CREATE FOREIGN TABLE ft_part 
      (
           p_partkey int, 
           p_name text, 
           p_mfgr text, 
           p_brand text, 
           p_type text, 
           p_size int, 
           p_container text, 
           p_retailprice float8, 
           p_comment text
      )
      SERVER
           hdfs_server
      OPTIONS
      (
           FORMAT 'orc',
           encoding 'utf8',
           FOLDERNAME '/user/hive/warehouse/partition.db/part_4'
      )
      DISTRIBUTE BY 
           roundrobin
      PARTITION BY 
           (p_mfgr) AUTOMAPPED;
      

      GaussDB(DWS)支持2种文件指定方式:通过关键字filenames指定和通过foldername指定。推荐通过使用foldername进行指定。关键字distribute指定了表ft_region的存储分布方式。

  3. 查看创建的外表:
    1
    2
    SELECT * FROM pg_foreign_table WHERE ftrelid='ft_region'::regclass;
    SELECT * FROM pg_foreign_table WHERE ftrelid='ft_part'::regclass;
    

示例2

通过HDFS只写外表,将TPC-H benchmark测试数据表region中的数据导出至HDFS文件系统的/user/hive/warehouse/gauss.db/regin_orc/目录下。

  1. 创建HDFS外表,对应的foreign data wrapper为HDFS_FDW或者DFS_FDW,同示例一。
  2. 创建HDFS只写外表。
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    CREATE FOREIGN TABLE ft_wo_region
    (
        R_REGIONKEY INT4,
        R_NAME TEXT,
        R_COMMENT TEXT
    )
    SERVER
        hdfs_server
    OPTIONS
    (
        FORMAT 'orc',
        encoding 'utf8',
        FOLDERNAME '/user/hive/warehouse/gauss.db/regin_orc/'
    )
    WRITE ONLY;
    
  3. 通过只写外表向HDFS文件系统写入数据。
    1
    INSERT INTO ft_wo_region SELECT * FROM region;
    

示例3

关于包含信息约束(Informational Constraint)HDFS外表的相关操作。

  • 创建含有信息约束(Informational Constraint)的HDFS外表。
     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    CREATE FOREIGN TABLE ft_region  (
     R_REGIONKEY  int,
     R_NAME TEXT,
     R_COMMENT TEXT
      , primary key (R_REGIONKEY) not enforced)
    SERVER hdfs_server
    OPTIONS(format 'orc',
        encoding 'utf8',
     foldername '/user/hive/warehouse/gauss.db/region_orc11_64stripe')
    DISTRIBUTE BY roundrobin;
    
  • 查看region表是否有信息约束索引:
    1
    SELECT relname,relhasindex FROM pg_class WHERE oid='ft_region'::regclass;
    
    图1 查看relname
    1
    SELECT conname, contype, consoft, conopt, conindid, conkey FROM pg_constraint WHERE conname ='ft_region_pkey';
    
    图2 查看信息约束索引
  • 删除信息约束:
    1
    2
    ALTER FOREIGN TABLE ft_region DROP CONSTRAINT ft_region_pkey RESTRICT;
    SELECT conname, contype, consoft, conindid, conkey FROM pg_constraint WHERE conname ='ft_region_pkey';
    
    图3 删除信息约束
  • 添加一个唯一信息约束:
    1
    ALTER FOREIGN TABLE ft_region ADD CONSTRAINT constr_unique UNIQUE(R_REGIONKEY) NOT ENFORCED;
    
    删除唯一信息约束:
    1
    2
    ALTER FOREIGN TABLE ft_region DROP CONSTRAINT constr_unique RESTRICT;
    SELECT conname, contype, consoft, conindid, conkey FROM pg_constraint WHERE conname ='constr_unique';
    
  • 添加一个唯一信息约束:
    1
    2
    ALTER FOREIGN TABLE ft_region ADD CONSTRAINT constr_unique UNIQUE(R_REGIONKEY) NOT ENFORCED disable query optimization;
    SELECT relname,relhasindex FROM pg_class WHERE oid='ft_region'::regclass;
    
    删除唯一信息约束:
    1
    ALTER FOREIGN TABLE ft_region DROP CONSTRAINT constr_unique CASCADE;
    

示例4

通过外表读取OBS上的json数据。

  1. OBS上有如下json文件,json对象中存在嵌套、数组,部分对象的某些字段缺失,部分对象name重复。
    {"A" : "simple1", "B" : {"C" : "nesting1"}, "D" : ["array", 2, {"E" : "complicated"}]}
    {"A" : "simple2", "D" : ["array", 2, {"E" : "complicated"}]}
    {"A" : "simple3", "B" : {"C" : "nesting3"}, "D" : ["array", 2, {"E" : "complicated3"}]}
    {"B" : {"C" : "nesting4"},"A" : "simple4",  "D" : ["array", 2, {"E" : "complicated4"}]}
    {"A" : "simple5", "B" : {"C" : "nesting5"}, "D" : ["array", 2, {"E" : "complicated5"}]}
  2. 创建obs_server,对应的foreign data wrapper为DFS_FDW。
    1
    2
    3
    4
    5
    6
    CREATE SERVER obs_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS ( 
      ADDRESS 'obs.example.com', 
      ACCESS_KEY 'xxxxxxxxx', 
      SECRET_ACCESS_KEY 'yyyyyyyyyyyyy', 
      TYPE 'OBS'
    );
    
    • ADDRESS是OBS的终端节点(Endpoint),请根据实际替换。也是使用region参数,通过指定regionCode在region_map文件中查找对应的域名。
    • ACCESS_KEY和SECRET_ACCESS_KEY是云账号体系访问密钥。请根据实际替换。
    • 认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险,建议在配置文件或者环境变量中密文存放,使用时解密,确保安全。
    • TYPE表示创建的Server为OBS Server。请保持OBS取值不变。
  3. 创建OBS外表json_f ,定义字段名,以d#2_e为例,从命名可以看出该字段是数组d的第二个元素里嵌套的e对象。表关联的OBS服务器为obs_server。foldername为外表中数据源文件目录,即表数据目录在OBS上对应的文件目录。

    认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险,建议在配置文件或者环境变量中密文存放,使用时解密,确保安全。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    CREATE FOREIGN TABLE json_f (
      a VARCHAR(10),
      b_c TEXT,
      d#1 INTEGER,
      d#2_e VARCHAR(30)
    )SERVER obs_server OPTIONS (
        foldername '/xxx/xxx/',
        format 'json',
        encoding 'utf8',
        force_mapping 'true'
    )distribute by roundrobin;
    
  4. 查询外表json_f。由于容错性参数force_mapping默认打开,json对象缺失的字段会填NULL;json对象name重复的以最后一次出现的name为准。
    1
    SELECT * FROM json_f;
    
    图4 查看外表json_f结果

示例5

通过外表读取DLI多版本外表。DLI多版本外表示例仅8.1.1及以上版本支持。

  1. 创建dli_server,对应的foreign data wrapper为DFS_FDW。
    1
    2
    3
    4
    5
    6
    7
    8
    9
    CREATE SERVER dli_server FOREIGN DATA WRAPPER DFS_FDW OPTIONS ( 
      ADDRESS 'obs.example.com', 
      ACCESS_KEY 'xxxxxxxxx', 
      SECRET_ACCESS_KEY 'yyyyyyyyyyyyy', 
      TYPE 'DLI',
      DLI_ADDRESS 'dli.example.com',
      DLI_ACCESS_KEY 'xxxxxxxxx',
      DLI_SECRET_ACCESS_KEY 'yyyyyyyyyyyyy'
    );
    
    • ADDRESS是OBS的终端节点(Endpoint)。DLI_ADDRESS是DLI的终端节点(Endpoint),请根据实际替换。
    • ACCESS_KEY和SECRET_ACCESS_KEY是云账号体系访问OBS服务的密钥。请根据实际替换。
    • DLI_ACCESS_KEY和DLI_SECRET_ACCESS_KEY是云账号体系访问DLI服务的密钥。请根据实际替换。
    • 认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险,建议在配置文件或者环境变量中密文存放,使用时解密,确保安全。
    • TYPE表示创建的Server为DLI Server。请保持DLI取值不变。
  2. 创建访问DLI多版本的OBS外表customer_address,不包含分区列,表关联的DLI服务器为dli_server。其中project_id为xxxxxxxxxxxxxxx,dli上的database_name为database123,需要访问的table_name为table456,根据实际替换。

    认证用的AK和SK硬编码到代码中或者明文存储都有很大的安全风险,建议在配置文件或者环境变量中密文存放,使用时解密,确保安全。

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    CREATE FOREIGN TABLE customer_address
    (
        ca_address_sk             integer               not null,
        ca_address_id             char(16)              not null,
        ca_street_number          char(10)                      ,   
        ca_street_name            varchar(60)                   ,   
        ca_street_type            char(15)                      ,   
        ca_suite_number           char(10)                      ,   
        ca_city                   varchar(60)                   ,   
        ca_county                 varchar(30)                   ,   
        ca_state                  char(2)                       ,   
        ca_zip                    char(10)                      ,   
        ca_country                varchar(20)                   ,   
        ca_gmt_offset             decimal(36,33)                  ,   
        ca_location_type          char(20)    
    ) 
    SERVER dli_server OPTIONS (
        FORMAT 'ORC',
        ENCODING 'utf8',
        DLI_PROJECT_ID 'xxxxxxxxxxxxxxx',
        DLI_DATABASE_NAME 'database123'
        DLI_TABLE_NAME 'table456'
    )
    DISTRIBUTE BY roundrobin;
    
  3. 通过外表查询DLI多版本表的数据。
    1
    SELECT COUNT(*) FROM customer_address;
    
    图5 查询结果

相关文档