尚硅谷大数据项目之电商数仓(即席查询)

尚硅谷大数据项目之电商数仓(即席查询)

第1章Presto

1.1Presto简介

1.1.1Presto概念

夕Presto概念@尚硅谷

Presto是一个开源的分布式SQL查询引擎，数据量支持GB到PB字节，主要用

来处理秒级查询的场景。

注意：虽然Presto可以解析SQL,但它不是一个标准的数据库。不是MySQL、

Oracle的代替品，也不能用来处理在线事务(OLTP)»

1.1.2Presto架构

&Presto架构。尚硅谷

Presto由一个Coordinalor和多个Workei•组成。

4)Calolog表示数据源。一个Calelog包含Schema和Connector

HiveMetastore

1)由客户端提交查询，

从Pre*。命令行CLI提交到

Coordinator；.

PrestoCLI>PrestoCoordinator

2)Coordinaior解析查询计

划，然用把任务分发给

Worker执行，

7)Coordinator是负方从Worker获

取结果并返回最终结果给Client

Connect^臂配器，6)Schema类似J；

用,Presio和数据吗My河中数据库,Table

徵赢就B类似「MySQL中表

1.1.3Presto优缺点

沙Presto优缺点。商硅谷

MapReducePresto1）优点

Allstagesarepipe-lined

（1）Preslo基于内存运算，减少了硬盘IO,计算更快。

reduce■reduce/Nowaittime

/Nofault-tolerance

Waitbetween（2）能够连接多个数据源，路数据源连表查，如从

fstages

Hive杳询大量网站访问记录，然后从Mysql中匹配出设备

memory-to-memofy信息。

datatransfer

reduce■reduce

✓Nodisk10

Writedata/Datochunkmint

todiskfitinmemory

2）缺点

Presto能够处理PB级别的海量数据分析，但Presio并不是

把PB级数据都放在内存中计算的。而是根据场景，如Couni,

AVG等聚合运算，是边读数据边计算，再济内存:，再读数据

再计算，这种耗的内存并不高。但是连表查，就可能产生.大

量的临时数据，因此速度会变慢，反而Hive此时会更擅长。

1.1.4Presto、Impala性能比较

s:/u012551524/article/details〃9124532

测试结论：Impala性能稍领先于Presto，但是Presto在数据源支持上非常丰富，包

括Hive、图数据库、传统关系型数据库、Redis等。

1.2Presto安装

1.2.1PrestoServer安装

o）官网地址

s:prestodb.github.io/

1）下载地址

s:/maven2/com/facebook/presto/presto-server/0.196/presto

-server-0.196.tar.gz

2）将presto-server-0.196.tar.gz导入hadoopl02的/opt/software目录下，并解压到

/opt/module目录

[atguiguhadoopl02software]$tar-zxvfpresto-server-0.196.tar.gz-C

/opt/module/

3）修改名称为presto

[atguiguhadoopl02module]$mvpresto-server-0.196/presto

4）进入到/opt/module/presto目录，并创建存储数据文件夹

[atguiguhadoopl02presto]$mkdirdata

5）进入到/opt/module/presto目录，并创建存储配置文件文件夹

[atguiguhadoopl02presto]$mkdiretc

6）配置在/opt/module/presto/etc目录下添加jvm.config配置文件

[atguiguhadoopl02etc]$vimjvm.config

添加如下内容

-server

-Xmxl6G

-XX:+UseGlGC

-XX:GlHeapRegionSize=32M

-XX:+UseGCOverheadLimit

-XX:+ExplicitGCInvokesConcurrent

-XX:+HeapDumpOnOutOfMemoryError

-XX:+ExitOnOutOfMemoryError

7）Presto可以支持多个数据源，在Presto里面叫catalog,这里我们配置支持Hive的数

据源，配置一个Hive的catalog

[atguiguhadoopl02etc]$mkdircatalog

[atguiguhadoopl02catalog]$vimperties

添加如下内容

=hive-hadoop2

hive.metastore.uri=thrift:hadoopl02:9083

8）将hadoopl02上的presto分发至UhadooplOS.hadoopl04

[atguiguhadoopl02module]$xsyncpresto

分发之后，分别进入三台主机的

9)hadoopl02xhadoopl03,hadoopl04

/opt/moduIe/presto/etc的路径。配置node属性，nodeid每个节点都不一样。

[atguiguhadoopl02etc]$vimperties

node.environment=production

node.id

node.data-dir=/opt/module/presto/data

[atguiguhadoopl03etc]$vimperties

node.environment=production

node.idtffffffff-ffff-ffff-ffff-fffffffffffe

node.data-dir=/opt/module/presto/data

[atguiguhadoopl04etc]$vimperties

node.environment=production

nodeJd

node.data-dir=/opt/module/presto/data

10)Presto是由一个coordinator节点和多个worker节点组成。在hadoopl02上配置

成在上配置为

coordinator,hadoopl03xhadoopl04workero

(1)hadoopl02上配置coordinator节点

[atguiguhadoopl02etc]$vimperties

添加内容如下

coordinator=true

node-scheduler.include-coordinator=false

-server..port=8881

query.max-memory=50GB

discovery-server.enabled=true

discovery.uri=:hadoopl02:8881

上配置节点

(2)hadoopl03xhadoopl04worker

[atguiguhadoopl03etc]$vimperties

添加内容如下

coordinator=false

-server..port=8881

query.max-memory=50GB

discovery.uri=:hadoopl02:8881

[atguiguhadoopl04etc]$vimperties

添加内容如下

coordinator=false

-server..port=8881

query.max-memory=50GB

discovery.uri=:hadoopl02:8881

11)在hadoopl02的/opt/module/hive目录下,启动HiveMetastore,用atguigu角

色

[atguiguhadoopl02hive]$

nohupbin/hive—servicemetastore>/dev/null2>&1&

分别在上启动

12)hadoopl02.hadoopl03xhadoopl04PrestoServer

(1)前台启动Presto,控制台显示日志

[atguiguhadoopl02presto]$bin/launcherrun

[atguiguhadoopl03presto]$bin/launcherrun

[atguiguhadoopl04presto]$bin/launcherrun

(2)后台启动Presto

[atguiguhadoopl02presto]$bin/launcherstart

[atguiguhadoopl03presto]$bin/launcherstart

[atguiguhadoopl04presto]$bin/launcherstart

13）日志查看路彳2/opt/module/presto/data/var/log

1.2.2Presto命令行Client安装

1）下载Presto的客户端

s:repol・m〉ven・org/maven2/com/facebook/presto/picsto・cli/0.196/presto・cli・

0.196-executable.ja「

2）将presto-cli-0.196-executable.jar上传至（Jhadoopl02的/opt/module/presto文件

夹下

3）修改文彳特称

[atguiguhadoopl02presto]$mvpresto-cli-0.196-executable.jarprestocli

4）增加执行权限

[atguiguhadoopl02presto]$chmod+xprestocli

5）启动prestocli

[atguiguhadoopl02presto]$./prestocli-serverhadoopl02:8881—catalog

hive-schemadefault

6）Presto命令行操作

Presto的命令行操作,相当于Hive命令行操作。每个表必须要加上schema。

例如：

select*fromschema.tablelimit100

1.2.3Presto可视化Client安装

1）将yanagishima-18.0.zip上传至!Jhadoopl02的/opt/module目录

2）解压缩yanagishima

[atguiguhadoopl02module]$unzipyanagishima-18.0.zip

cdyanagishima-18.0

3）进入至U/opt/module/yanagishima-18.0/conf文彳牛夹,编写perties

配置

[atguiguhadoopl02conf]$vimperties

添加如下内容

jetty,port=7080

presto.datasources=atguigu-presto

presto.coordinator.server.atguigu-presto=:hadoopl02:8881

catalog.atguigu-presto=hive

schema.atguigu-presto=default

sql.query.engines=presto

4）在/opt/module/yanagishima-18.0路径下启动yanagishima

[atguiguhadoopl02yanagishima-18.0]$

nohupbin/yanagishima-start.sh>y.log2>&1&

5）启动web页面

:hadoopl02:7080

看到界面，进行查询了。

6）查看表结构

tltM•fE

SchemaTatoleOColumn;ExtraComment

defauhdm.version.alocauonnewsjd

dw.weMherm«d.d»Hy.ne«.dt

infomwtion.KbeniamW_newj_hist<xy_di

mW_usef.hUtory_dt

MW

ocfe.ad.dt

toul.dtsptay

odi_tMdtg<ound.dt

toul.ctck

odt.basedau.dt

partitionkey

ods.dlsplay.dl

od$.loadlnt.dt

ods.newsdeutlpro.dt

fxpandColumnsMLECT,FROM...WHEUat--29190222■LIMITxeeSet»Run

这里有个TreeView,可以查看所有表的结构，包括Schema、表、字段等。

比如执行select*fromhive.dw_weather.tmp_news_clicklimit10这个句子里Hive

这个词可以删掉，是上面配置的Catalog

select,fromhive.dw_weather.mid_daily_news_dtlimit10

品Treeview

•B»euUe•view

Schema:)TableColumnType

defaultdm_version_allocationvarchar

dw.weathermld.daily_news.dtvarchar

lnformatlon_schemamid_news_hl$tory_dtvarchar

testmld_user_history_dt

varchar

ods.ad.dt

integer

ods_background_dt

Integer

ods.basedata.dt

varchar

ods.display.dt

ods_loadlng_dt

ods_newsdetailpro_dt

每个表后面都有个复制键，点一下会复制完整的表名，然后再上面框里面输入sql语句,

Ctrl+enter键执行显示结果

20190223_122817.00012_54w6c12019/02/2320:28:258,03278105'-Pl

newsidlangversion_nameareauserjd

1n4260en127MXu2868

2n0010en13.3MXu6515

n6759Pl1.0.7MXU9947

4n8048en125MXu5196

n3795en13.5MXU5296

6n3021en13.0MXU8066

7n7023en13.8MXU8958

8n7696en122MXU5696

9n9067Pt1.1.2MXU0179

10n6068en12.6MX□3771

13Presto优化之数据存储

13.1合理设置分区

与Hive类似，Presto会根据元数据信息读取分区数据,合理的分区能减少Presto数

据读取量，提升查询性能。

13.2使用列式存储

Presto对ORC文件读取做了特定优化，因此在Hive中创建Presto使用的表时，建议

采用ORC格式存储。相对于Parquet,Presto对ORC支持更好。

13.3使用压缩

数据压缩可以减少节点间数据传输对I。带宽压力，对于即席查询需要快速解压，建议

采用Snappy压缩。

1.4Presto优化之直询SQL

1.4.1只选择使用的字段

由于采用列式存储，选择需要的字段可加快字段的读取、减少数据量。避免采用*读取

所有字段。

[GOOD]:SELECTtime,user;hostFROMtbl

[BAD]:SELECT*FROMtbl

1.4.2过滤条件必须加上分区字段

对于有分区的表，where语句中优先使用分区字段进行过滤。acct_day是分区字段，

visit_time是具体访问时间。

[GOOD]:SELECTtime,user,hostFROMtblwhereacct_day=20171101

[BAD]:SELECT*FROMtblwherevisit_time=20171101

1.4.3GroupBy语句优化

合理安排Groupby语句中字段顺序对性能有一定提升。将GroupBy语句中字段按照

每个字段distinct数据多少进行降序排列。

[GOOD]:SELECTGROUPBYuid,gender

[BAD]:SELECTGROUPBYgender,uid

1.4.4Orderby时使用Limit

Orderby需要扫描数据到单个worker节点进行排序导致单个worker需要大量内存。

如果是查询TopN或者BottomN，使用limit可减少排序计算和内存压力。

[GOOD]:SELECT*FROMtblORDERBYtimeLIMIT100

[BAD]:SELECT*FROMtblORDERBYtime

1.4.5使用Join语句时将大表放在左边

Presto中join的默认算法是broadcastjoin，即将join左边的表分割到多个worker,

然后将join右边的表数据整个复制T分发送到每个worker进行计算。如果右边的表数据

量太大，则可能会报内存溢出错误。

[GOOD]SELECT...FROMlarge_tableIjoinsmall_tablesonLid=s.id

[BAD]SELECT...FROMsmall_tablesjoinlarge_tableIonLid=s.id

1.5注意事项

1.5.1字段名引用

避免和关键字冲突：MySQL对字段加反引号'、Presto对字段加双引号分割

当然，如果字段名称不是关键字，可以不加这个双引号。

1.5.2时间函数

对于Timestamp,需要进行比较的时候，需要添加Timestamp关键字，而MySQL

中对Timestamp可以直接进行比较。

/*MySQL的写法*/

SELECTtFROMaWHEREt>,2017-01-0100:00:00,;

/*Presto中的写法*/

SELECTtFROMaWHEREt>timestamp'2017-01-0100:00:00,;

1.5.3不支持INSERTOVERWRITE语法

中不支持语法，只能先然后

Prestoinsertoverwritedelete,insertintoe

1.5.4PARQUET格式

Presto目前支持Parquet格式，支持查询，但不支持insert,

第2章Druid

2.1Druid简介

2.1.1Druid概念

夕Druid简介◎商硅谷

Druid是一个快速的列式分布式的支持实时分析的数据存储系统。它在处理PB

级数据、毫秒级查询、数据实时处理方面，比传统的OLAP系统有了显著的性能

改进。

Druid的官方网站是http:〃druid.io。

注意：阿里巴巴也曾创建过一个开源项目叫作Druid（简称阿里Druid）,它是

一个数据库连接池的项目。阿里Druid和本文讨论的Druid没有任何关系，它们解

决完全不同的问题。

2.1.2Druid特点

§Druid特点◎尚硅谷

1）列式存储格式。Druid使用面向列的存储，它只需要加载特定查询所需要的列。查询速度迅速快。

2）可扩展的分布式系统。Druid通常部署在数十到数百台服务器的集群中，并且提供数百万条/秒的摄

取率，保留数百万条记录，以及亚秒级到几秒钟的查询延迟。

3）大规模的并行处理.Druid可以在整个集群中进行大规模的并行查询。

4）实时或批量摄取cDruid可以实时摄取数据（实时获取的数据可立即用于查询）或

批量处理数据。

5）自愈，自平衡，易操作。集群扩展和缩小，只需添加或删除服务器，集群将在后台自动重新平衡，

无需任何停机时间。

6）数据进行了有效的预聚合或预计算，查询速度快。

7）数据的结果应用了Bitmap压缩算法。

2.1.3Druid应用场景

〈夕Druid应用场景@商硅谷

1）适用于清洗好的记录实时录入，但不需要更新操作

2）适用于支持宽表，不用Join的方式（换句话说就是一张单表）

3）适用于可以总结出基础的统计指标，用一个字段表示

4）适用于实时性要求高的场景

5）适用于对数据质量的敏感度不高的场景

2.1.4Druid对比Impala/Presto/Spark

SQL/Kylin/Elasticsearch

夕Druid对比Impala/Presto/SparkSQL/Kylin/Elasticsearch◎尚硅苍

1Druid1KvlinIES4)SparkSQL：基于Spark平台上的一个OLAP框架，

YYNNNN

基本思路是增加机器来并行il•算，从而提高查询速

YYYYYY

度。

N(开发中)YYYYN

离线YYYYYY

实时YN(开发中)NNNY

NYYYYN5)ES：最大的特点是使用了倒排索引解决索引问题。

NYYYYN根据研究，ES在数据获取和聚集用的资源比在Druid

IJDBCforBlNYYYYN

1)Druid：是•个实时处理时序数据的OLAP数据库，因为它的索引首先按照6)框架选型:

时间分片,查询的时候也是按照时间线上路由索引。＜1)从超大数据的查询效率来看:

Druid>Kylin>Presto>SparkSQL

2)Kylin：核心是Cube,Cube是--种预计算技术，基本思路是预先对数据作

（2）从支持的数据源种类来讲：

多维索引，查询时只扫描索引而不访问原始数据从而提速。

Presto>SparkSQL>Kylin>Druid

3)Presto：它没有使用MapReduce.大部分场景下比Hive快一个数量级，其

中的关键是所有的处理都在内存中完成

4)Impala：雄J•内存运算.速度快，＜：Presto^；

2.2Druid框架原理

沙Druid框架原理。商硅谷

：i5)统治并：进程监Dnadnod**

要负资历史节点的数据视MiddleManager进程，井ExternalDvpendenciM

负我均衡.以及通过规且是数据摄入Druid的控制

.OtMriM

»＞J(Rule)管理数据的CoordinatorOverlord器,他们负贲将提取任务

Nodes...........*Mctedate

Nodes分配给并

生命周期.MiddleManagers・••*0ata/S«9m«atB

梆调Segement发布。

3＞杳询打点：接收客户端

表闻请求，并将这些15询转发给

▼Historicals和MiddleManagers.

StrvarrwxiOaU-------►

Broker

MetadataStorageZookeeper♦，CltantOuviM

Nodes当Brokers从这些子杳询中

BMcfiDaU------

D中同管歼W点：及时।收到结果时，它们会合并这些结

8)Zdokeepert为

果并将它们诋回给调用者.

摄入实时数据,已生成7＞兀数据内,存储Druid集群的;Druid集群提供以执行仍谢

Segment数据文件,元数据信息,比如Segment的相关倍思.，服务，如内法服务的监控.皆询打点采用了缓存技术

一般用MySQL龄悔调和领导者选举.

Historical

Ncdcq

2)，历史节点：加靓已生成好的数据文件，以供数据台

6)数据文件疗铭班:存放生成的Segment数据文件,并Bifii,H«toncal节点是整个集种自询性能的核心所在，因为

供历史服务器卜找，对「单节点集肝可以是本地班盘，而对Historic排会承担绝大部分的segmentf£询.

丁分布式集群般是HDFS,

2.3Druid数据结构

与Druid架构相辅相成的是其基于DataSource与Segment的数据结构，它们共同成

就了Druid的高性能优势。

夕Druid数据结构(DataSource+Segment)@商硅谷

是•个逻辑概念,却是数损的实际物

Druid的DaiaSource相当于,关系里:数据库中的表fTable）..DataSource的结构包括:DataSourceSegment

评存储格式，Druid将不同时间范围内的数据存储在不同的

1）时间列：表明每行数据的时间值，默认使用UTC时间格式凡精确到富秒级别。

Segment数据块中.这便是所谓的数据横网切机按照时间横

2）谕度列：维度来自于OLAP的概念,用来标识数据行的各个类别信息。

向切割数据，避免了全表查询.极大的提高了效率.

3）指标列:是用于聚合和计兑的列.通常是一些数字，计电操作通常包括Count、Sum等“

（ESegment中，也采用面向列进行数据压缩存储（BitmapEE

tlmestaap::publisheradvertisergenderclickprice缩技术）,这便是所谓的数据纵向切割.

2011-ei-01Tei：ei:35Z；：bieberfever.co«google.con

2eil-ei-eiT01：e3:63Z*；bieberfever.coagoogle,CM

2eil-«l-«lT01：e4:51Z：：bieberfever.co«google.coa

2011-ei-eiTei：ee：0ez：：uUratri«fast.co<iigoogle.coaFemale

2011-61-eiT02:00:00Z：*ultratrlafast.coMgoogle.coaFemale

2011-ei-eiTe2：e0：eez：