作者介绍
ceciliasu(苏翠翠),腾讯云数据库工程师,加入腾讯以来持续从事分布式数据库内核研发工作,曾负责TDSQL PG版、CDW PG快速扩容能力设计和研发。目前主要参与CDW PG数据库内核研发相关工作,负责外部数据快速导入工具的设计和研发。
原生数据导入导出方式以及存在的问题
使用原生COPY导入数据相当耗时,这是因为在CN上执行COPY导入数据是一个串行执行的过程,所有数据都需要经过CN处理分发给不同DN入库,所以CN是瓶颈,它只适合小数据量的导入。
图表 1 COPY数据流向示意图
TDX提出了一种基于外表实现多DN并行导入数据的方式,将数据的处理和写入直接下推到DN执行,使DN直连数据源,充分利用分布式数据库的多节点优势,最大化数据库的计算能力。
图表 2 TDX数据流向示意图
从零开始 - TDX使用实践
并行文件分发组件TDX服务部署
支持CDW PG并行导入导出的第一步便是在数据服务器上部署CDW PG并行文件分发组件-TDX服务。数据服务器是指数据源文件所在的机器,数据服务器上是否分布有数据库结点无要求。
联系CDW PG团队人员获取TDX服务rpm包;
CDW PG_tdx-1.0-i.x86_64.rpm
基础依赖安装:
yum install apr-devel.x86_64 yum install libevent-devel.x86_64
软件包安装:
rpm -ivh CDW PG_tdx-1.0-i.x86_64.rpm
配置并启动TDX服务
将TDX二进制目录添加到$PATH:修改~/.bashrc,添加PATH路径:
export PATH=/usr/local/install/CDW PG_tdx/bin:$PATH
并使其生效:
source ~/.bashrc
建立TDX数据目录并启动TDX服务
当启动TDX服务时,需要指定服务监听的端口和工作目录$tdx_prefix(该目录内的数据都可以被访问)。
mkdir $tdx_prefix/tdx_data
可以通过-l参数来指定TDX服务的日志输出。
tdx -d $tdx_prefix/tdx_data -p 8999 -l tdx_prefix/tdx_data/log 2>&1 &
如果不需要TDX的输出,则可以这样启动TDX服务:
nohup tdx -d $tdx_prefix/tdx_data -p 8999 >/dev/null 2>&1 &
部署多个TDX服务
部署多个TDX服务可以提高数据导入效率,可以在不同机器上运行多个TDX,也可以在一台机器上运行多个TDX,充分利用机器IO和网络带宽。
TDX数量不能超过DN数量,一个DN只连接一个TDX服务。
外部表创建
在数据库正常启动并创建exttable_fdw插件之后,我们便可以创建外部表(External Table)了。
参数说明
WRITABLE/READABLE(缺省) -
外表分为可读外表和可写外表;创建外表时指定WRITABLE,则外表为可写外表;未指定时默认为可读外表。可读外表支持数据并行导入,可写外表支持数据并行导出。
LOCATION -
指定外部数据的URL,包括外部数据读写协议(tdx/tdxs)。
FORMAT -
指定外部数据格式(csv或text),CDW PG会根据这些指定的格式,实现外部数据和数据库内部元组的转换。
[ [LOG ERRORS] SEGMENT REJECT LIMIT count [ROWS | PERCENT] ] -
LOG ERRORS 是否记录有关具有格式错误的信息;
SEGMENT REJECT LIMIT count [ROWS | PERCENT] 拒绝限制计数可以指定为行数(默认值)或总行数百分比(1-100),如果错误行的数量达到limit,整个外部表操作会被中止并且不会有行被处理。
其他参数说明如下
参数 | 说明 |
---|---|
header | 导入文件第一行为列名(导出暂不支持) |
DELIMITER | 自定义列分隔符(允许多字节) |
EOL | 自定义行分隔符 |
QUOTE | 指定一个数据值被引用时使用的引用字符。默认是双引号。 |
ESCAPE | 指定应该出现在一个匹配QUOTE值的数据字符之前的字符;OFF代表关闭转义功能,把反斜杆当做普通字符处理; |
date_format | date类型数据在文件中数据格式 |
timestamp_format | timestamp类型数据在文件中数据格式 |
time_format | time类型数据在文件中数据格式 |
position | 字段取值在数据文件行中的位置 |
fill_missing_fields | 数据源文件中某行的最后一个(或以上)字段缺失时的处理方式 |
ignore_extra_data | 若数据源文件比外表定义列数多,是否会忽略多出的列 |
compatible_illegal_chars | 导入时遇到非法字符时的处理方式 |
外表的创建示例如下:
CREATE EXTERNAL TABLE EXT_TABLE(name TEXT, DATE DATE, amount FLOAT)LOCATION ('tdx:// 9.20.171.94: 8999 /file_name.csv') --外部数据源URLFORMAT 'csv' (header); -- 数据格式为csv,并包含列的名称
数据的导入导出
数据的导入导出通过类似的句式来实现。
INSERT INTO TABLE1 SELECT * FROM TABLE2;
数据导入
INSERT INTO LOCAL_TABLE SELECT * FROM EXT_TABLE;test=# EXPLAIN INSERT INTO LOCAL_TABLE SELECT * FROM EXT_TABLE; QUERY PLAN ---------------------------------------------------------------------------------------------- Remote Subquery Scan ON ALL (datanodes 1) (cost=100.00..100.00 ROWS=0 width=0) -> INSERT ON local_table (cost=100.00..100.00 ROWS=0 width=0) -> Remote Subquery Scan ON ALL (datanodes 1) (cost=100.00..100.00 ROWS=0 width=44) Distribute results BY S: name -> FOREIGN Scan ON ext_table (cost=0.00..0.00 ROWS=0 width=44)
(5 ROWS)
数据导入SQL的执行计划如上所示。
从查询计划可以看出DN的工作包括:
- 扫描可读外表,从TDX获取部分数据块,将它转化为元组;
- 根据需要导入的本地表的分布键,对元组进行重分布,发往对应DN;
- 对应DN将元组插入到本地表中。
数据导出
数据导出需要创建可写(writable)外部表:
CREATE WRITABLE EXTERNAL TABLE EXT_TABLE_w(name TEXT, DATE DATE, amount FLOAT)LOCATION ('tdx:// 9.20.171.94: 8999 /file_name.csv') --外部数据源URLFORMAT 'csv'; -- 数据格式为csv,并包含列的名称
数据导出需要执行如下SQL:
INSERT INTO EXT_TABLE_w SELECT * FROM LOCAL_TABLE;test=# EXPLAIN INSERT INTO EXT_TABLE_w SELECT * FROM LOCAL_TABLE; QUERY PLAN --------------------------------------------------------------------------------- Remote Subquery Scan ON ALL (datanodes 1) (cost=0.00..18.40 ROWS=840 width=44) -> INSERT ON ext_table_w (cost=0.00..18.40 ROWS=840 width=44) -> Seq Scan ON local_table (cost=0.00..18.40 ROWS=840 width=44)(3 ROWS)
从执行计划可以看出对本地表的扫描与外部表的写入(将数据发送给TDX执行写入文件操作)都被下推到了DN执行。
收尾工作
外部表的删除
DROP EXTERNAL TABLE EXT_TABLE_w;DROP EXTERNAL TABLE EXT_TABLE;
TDX的停止
在文件服务器找到TDX进程直接kill即可。
ps aux | grep tdxKILL -9 pid
推荐阅读
关注腾讯云大数据公众号
邀您探索数据的无限可能
点击“阅读原文”,了解相关产品最新动态
↓↓↓