打造次世代分析型数据库(八):高效数据导入导出方案

常见的场景优化方案:导出Excel大批量数据的优化过程 团队目前在做一个用户数据看板(下面简称看板),基本覆盖用户的所有行为数据,并生成分析报表,用户行为由多个数据来源组成(餐饮、生活日用、充值消费、交通出行、通讯物流、交通出行、医疗保健、住房物业、运动健康...),看看标红的代码,这个生成Excel的方式是对Excel中的每一个cell进行渲染,逐行的进行数据填充,效率太慢了,根据日志分析发现:基本时间都耗费在数据生成Excel上。目前遇到的主要问题是,随着行为能力逐渐的完善闭环,用户数据沉淀的也越来越多了,同时业务量的也在不断扩大。 阅读详情

bc849bf98af987bfb03f8a558bb5a4d6.gif

作者介绍

ceciliasu(苏翠翠),腾讯云数据库工程师,加入腾讯以来持续从事分布式数据库内核研发工作,曾负责TDSQL PG版、CDW PG快速扩容能力设计和研发。目前主要参与CDW PG数据库内核研发相关工作,负责外部数据快速导入工具的设计和研发。

原生数据导入导出方式以及存在的问题

使用原生COPY导入数据相当耗时,这是因为在CN上执行COPY导入数据是一个串行执行的过程,所有数据都需要经过CN处理分发给不同DN入库,所以CN是瓶颈,它只适合小数据量的导入。

1d39fda1fb5ee858c02519c18450d430.png

图表 1 COPY数据流向示意图

TDX提出了一种基于外表实现多DN并行导入数据的方式,将数据的处理和写入直接下推到DN执行,使DN直连数据源,充分利用分布式数据库的多节点优势,最大化数据库的计算能力。

a6d1cef718f5b61f567a245befad31f6.png

图表 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_formatdate类型数据在文件中数据格式
timestamp_formattimestamp类型数据在文件中数据格式
time_formattime类型数据在文件中数据格式
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') --外部数据源URL
FORMAT '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') --外部数据源URL
FORMAT '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 tdx
KILL -9 pid

【TDengine】2、数据导入与导出 TDengine提供多种方便的数据导入功能,一种按脚本文件导入,一种按数据文件导入,一种是taosdump工具导入本身导出的文件。 阅读详情

相关推荐

12-TDengine数据迁移:导入与导出

分享一些系统的面试题,大家可以拿去刷一刷,准备面试涨薪。

a1901149的博客 1295

TDSQL转载

TDSQL

hxsln11的博客 459

导入导出设计(导出篇)

下载/导出设计分享,一起学习

m0_43600013的博客 4114

探索TDX:一个创新的SQL文本处理工具

探索TDX:一个创新的SQL文本处理工具 项目简介 是一个用于处理和分析SQL文本的开源工具,旨在帮助开发者、数据分析师和数据库管理员更有效地管理和理解他们的SQL代码。它提供了诸如语法高亮、查询解析、结构化数据提取等一系列实用功能,极大地提升了SQL文档的可读性和工作效率。 技术分析 SQL解析器 TDX的核心是一个强大的SQL解析器,能够处理多种数据库系统的SQL语句,包括但不限于MySQL,...

gitblog_00049的博客 586

别再死记硬背了!用这5个Qt Design Studio小项目,彻底玩转核心组件

本文通过5个Qt Design Studio实战项目,帮助开发者深入理解核心组件的协作与应用。从动态登录界面到游戏化任务列表,每个项目都展示了组件属性与交互设计的巧妙结合,让你不再死记硬背,而是真正掌握Qt Design Studio的核心技能。

weixin_29202213的博客 272

机器学习——径向基函数(RBF)神经网络

目录 一、什么是基函数 二、什么是径向基函数 三、为什么RBF神经网络使用一个径向基函数作为隐含层的激活函数 四、参数训练 五、RBF网络vs BP网络和 SVM 一、什么是基函数 著名的傅里叶变换是指:n个有参(权值)的正、余弦三角函数基的和式可以等价的表示任意一个周期函数,这里的“基”和我们数学中说的坐标基或向量基含义相同,即坐标空间中任意一个向量都可以用基向量的线性和表示出来。 因此,我们引入函数空间的概念,函数空间中的每个连续函数可以表示为基函数的线性组合,就像向量空间中的每..

CHWB2580的博客 6273

数据导出流程设计

导出流程设计

hp961218的博客 4605

基于ESP32的智能健康监测系统开发

本文介绍了如何基于ESP32开发智能健康监测系统,从硬件选型、系统架构到软件实现的每一个环节。通过ESP32的Wi-Fi功能、传感器模块和云平台的结合,我们实现了实时健康数据监测、远程查看、报警等功能。系统的低功耗特性使得其适用于长期健康监测,能够为用户提供更便捷的健康管理方案。随着技术的不断发展,未来智能健康监测系统将进一步发展。例如,增加更多的传感器,提升数据分析能力,甚至通过人工智能算法来预测用户的健康趋势,为用户提供个性化的健康建议。

2201_76125393的博客 1106

数据库导入导出详解

Sql格式:.sql格式的文件,可用文本编辑器查看,通用性比较好,效率不如第一种,适合小数据量导入导出。Pde格式:.pde格式的文件,.pde为PL/SQL Developer自有的文件格式,只能用PL/SQL Developer工具导入导出,不能用文本编辑器查看;优点:代码书写简单易,从本地即可直接导入,不用在服务器中操作,降低难度,减少服务器上的操作也就保证了服务器上数据文件的安全性。导出步骤:工具,导出表,选好表以后,下方有Oracle导出是对应的dmp文件,SQL插入是对应的SQL文件。

彷徨的博客 9881

《大数据最全面试题-Offer直通车》目录

做好这些不用担心试用期不通过:程序员入职新公司如何快速上手项目本人遇到大数据面试题和参考答案(超过1万字精华版)大数据面试英文自我介绍参考(万字长文)大数据运维应用场景面试题汇总及参考答案(持续更新)万字数据仓库面试题及参考答案数据仓库数据质量监控和处理方法最佳实践数据仓库建模方法万字详解Doris的3种数据模型详解和数据仓库每一层的模型选用大数据面试临阵磨枪不知看什么?看这份心理就有底了-大数据常用技术栈常见面试100道题最全Hive面试题2024年(2万字详解)Elasticsearch 面试题及参考答

大模型大数据攻城狮的专栏 2653

在 MATLAB/Simulink 中构建一个卫星间激光通信链路的完整仿真系统

摘要: 本文通过Simulink详细演示了卫星间激光通信系统的建模过程,重点分析其在10Gbps高速率、2000km距离下的性能。系统设计涵盖PAM-4调制、自由空间信道建模(路径损耗、指向误差、多普勒效应)、APD探测及链路预算计算,并给出典型场景下18.5dB SNR、1e-9误码率的仿真结果。针对激光通信的μrad级指向精度挑战,提出AI辅助跟踪、混合RF/激光等优化策略,为未来太空高速组网提供可扩展的仿真框架。所需工具箱包括Communications Toolbox和Aerospace Toolb

xiaoheshang_123的博客 384

Excel导入导出百万级数据

Excel导入导出百万数据方案

Jeck_wu的博客 2190

Weka学习笔记01:初探Weka世界

下载、安装和启动Weka、Weka数据格式

howard2005的专栏 1473

数据导入的三种方法

ml笔记

sz1125218970的博客 2138

数据中台:大数据平台,数据仓库(DM),数据中台之间的联系以及异同分析

大数据平台是一个技术平台。这个技术平台提供了对于大数据的分布式采集,存储,流处理和计算,实时分析等能力。在没有大数据平台前也有数据集成和管理的平台,这种平台可以实现对结构化数据本身的采集,集成和管理。 管理平台,是在原生的大数据生态的基础之上,为了更好的管理集群服务,管理集群的资源,提供灵活SRE能力和资源核算审计能力的一系列工具和合称整体架构数据中台包括数据仓库的全部内容,数据仓库为数据中台提供了数据对外提供服务的基础资源,数据中台将数据仓库建设的投入价值进行最大化,以加快数据赋能业务的速度大家都知道数据

GIS摆渡人 2136

TMS_WebGMaps_1.9.8.0_Full_Source

TMS_WebGMaps_1.9.8.0_Full_Source 全源码 delphi cbuilder

面试官:百万数据的导入导出解决方案,怎么设计?

前景在项目开发中往往需要使用到数据的导入和导出,导入就是从Excel中导入到DB中,而导出就是从DB中查询数据然后使用POI写到Excel上。写本文的背景是因为在工作中遇到了大数据的导入和导出,问题既然来了逃跑不如干掉它!!!只要这一次解决了,后期遇到同样的问题就好解决了。废话不多说,开始撸起来!!!1. 传统POI的版本优缺点其实想到数据的导入导出,理所当然的会想到apache的poi技术,以及...

Java精选 1442

lwip-2.0.3

lwip是瑞典计算机科学院(SICS)的Adam Dunkels 开发的一个小型开源的TCP/IP协议栈。实现的重点是在保持TCP协议主要功能的基础上减少对RAM 的占用。

浅谈GBase 8c优化器之Join查询下推

浅谈GBase 8c优化器之Join查询下推

futianxia061112的博客 468

IMSL CNL 7.0.0 (x86-64)Crack

IMSL CNL 7.0.0 (x86-64)Crack intel的算法库破解文件,这是C++版本的破解文件

最新的sdio 4.0 spec 协议标准 带书签 高清完整PDF版

Part-E1-SDIO-Specification-Ver4.00-Final-120220.pdf:最新的sdio 4.0 spec 协议标准 带书签 高清完整PDF版,包括SDIO 接口的外设,SDIO 接口官方协议等。

使用T-SQL实现数据导出/导入(SQLSERVER—>SQLSERVER)

尝试使用Transact-SQL进行数据的导出导入,收获颇丰。与使用DTS相比,效率要高很多! 一、打开OPENDATASOURCE功能开始 —> Microsoft SQL Server 2005  —> 配置工具  —> SQL Server外围应用配置器  —> 功能的外围应用配置器  —> 实例名  —> Database Engine  —

浮云天空 1539

数据库的导入导出命令

数据导出:  1 将数据库TEST完全导出,用户名system 密码manager 导出到D:\daochu.dmp中    exp system/manager@TEST file=d:\daochu.dmp full=y  2 将数据库中system用户与sys用户的表导出    exp system/manager@TEST file=d:\daochu.dmp owner=(sys

宋程铬的专栏 4392

数据库查询百万级数据导出excel功能优化

数据库查询百万级数据导出excel功能优化

weixin_44775375的博客 1812

GBase 8c优化器——Join查询下推

GBase 8c优化器——Join查询下推

futianxia061112的博客 340

SYBASE数据库数据迁移至TDSQL数据库辛酸经历

随着国产云计算的新兴发展,许多的企业将“云安全”定为高优先级的任务,企业需要云的安全能力,以便应对数据中心云化和业务应用云原生化的发展。我所在的项目组负责的“XXXX业务系统”已经在小型机上服务了20余年,业务系统使用的EJB2.0的架构,数据库使用了Sybase 15.7,部署在webshpere 8.0集群上,现如今进行新创,将此业务系统迁移至云服务器和云数据库,本文章主要讲述了作者本人在此次新创项目中将Sybase库的数据迁移至TDSQL库的辛酸史及经验分享给大家参考。

huangkaiwuhan的博客 1171

300万数据导入导出优化方案,从80s优化到8s(实测)

前景在项目开发中往往需要使用到数据的导入和导出,导入就是从Excel中导入到DB中,而导出就是从DB中查询数据然后使用POI写到Excel上。写本文的背景是因为在工作中遇到了大数据的导入和导出,问题既然来了逃跑不如干掉它!!!只要这一次解决了,后期遇到同样的问题就好解决了。废话不多说,开始撸起来!!!1 传统POI的...

程序员小明1024 3458

Python八种数据导入方法,你掌握了吗?赶紧收藏

大多数情况下,会使用NumPy或Pandas坚持到了这儿,恭喜你,表示你有做开发的潜力,其实我想说的上面的内容还是刚刚开始,刚开始大家不需要多么精通了解这些内容,除了Python方面的知识,每个部分掌握一点儿能进行基本开发就好,主要是不断练习,让自己跳出「舒适区」,进入「学习区」,但是又不进入「恐慌区」,不断给自己「喂招」。

AI大模型的博客 6876
上一篇: 最佳实践 | 基于腾讯云ES如何跨地域容灾?跨集群复制为您解忧!
下一篇: Flink JobManager内存管理机制介绍与调优总结
腾讯云大数据
博客等级 码龄6年 348粉丝 219原创
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付:10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值