大数据项目之电商数仓即席查询数据仓库

大数据项目之电商数仓（即席查询）

第1章Presto

1.1Presto简介

1.1.1Presto概念

夕Presto概念◎尚硅谷

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

来处理秒级查询的场景。

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

Oracle的代替品，也不能用来处理在线事务（OLTP）。

1.1.2Presto架构

夕Presto架构

Presto由一个Coordinator和多个Worker组成。

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

Catalog

Schema

HiveMetasiorePrestoWorkerHiveConnectorHive

Table

1）由客户端提交查询，।3）Worker负诳执行

从Prcslo命令行CLI提交到I任务和处理数据

Coordinator5

Catalog

Schema

PrestoCLI►PrestoCoordinatorPrestoWorkerKafkaConnectorKafka

Table

2)Coordinator解析查询计'

划，然后把任务分发给

Worker执彳:Catalog

Schema

।PrestoWorkerRedisConnectorRedis

Table

7）Coordinator^负贵从Worker获

取结果并返回最终结果给Cliem。

5）Conneclor是适配器，6）Schema类似于

用于Presto和数据源Mysql中数据库，Table

）

（如Hive、Rcdis的类似,MySQL中表

连接,类似于JDBC.

1.1.3Presto优缺点

沙Presto优缺点o商硅谷

MapReducePresto1）优点

Allstagesarepipe-lined

■■■■■■■■■✓Nowaittime（1）Presto基于内存运算，减少了硬盘10,计算更快。

/Nofault-tolerance

<<

Waitbetw^tn/•^*****^*~^（2）能够连接多个数据源，跨数据源连表查，如从

stages

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

memory-to-memory信息。

datatransfer

■■SBIKSfl/NodiskIO

Writedatai/Datachunkmust

todisklnmemory

2）缺点

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

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

AVG等聚合运算，是边读数据边il算，再清内存，再读数据

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

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

1.1.4Presto>Impala性能比较

hUDS：//blog.csdn.cet/uO12551524/article/details/79124532

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

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

1.2Presto安装

1.2.1PrestoServer安装

o）官网地址

https://prestodb.github.io/

1）下载地址

https://repoL/maven2/com/facebook/presto/presto-servei70.196/presto-server-

0.196.tar.gz

2），各presto-server-0.196.tar.gz导入hadoop102的/opt/software目录下，并解压至lj/opt/module

目录

[atguigu@hadoopl02software]$tar-zxvfpresto-server-

0.196.tar.gz-C/opt/module/

3）修改名称为presto

[atguigu@hadoopl02module]$mvpresto-server-0.196/presto

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

[atguigu@hadoopl02presto]$mkdirdata

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

[atguigu@hadoopl02presto]$mkdiretc

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

[atguigu@hadoopl02etc]$vimjvm.config

添加如下内容

-server

-Xmxl6G

-XX:+UseGlGC

-XX:GlHeapRegionSize=32M

-XX:+UseGCOverheadLimit

-XX:+ExplicitGCInvokesConcurrent

-XX:4-HeapDumpOnOutOfMemoryError

-XX:+ExitOnOutOfMemoryError

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

源，配置一个Hive的catalog

[atguigu@hadoopl02etc]$mkdircatalog

[atguigu@hadoopl02catalog]$vimperties

添加如下内容

=hive-hadoop2

hive.metastore.uri=thrift://hadoopl02:9083

8)将hadoopl02上的presto分发到hadoopl03、hadoop104

[atguigu@hadoopl02module]$xsyncpresto

9)分发之后，分别进入hadoopl02、hadoopl03^hadoopl04三台主机的/opt/module/presto/elc

的路径。配置node属性，nodeid每个节点都不一样。

[atguigu@hadoopl02etc]$vimperties

node.environment=production

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

[atguigu@hadoopl03etc]$vimperties

node.environment=production

node.

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

[atguigu@hadoopl04etc]$vimperties

node.environment=production

node.

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

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

coordinator,在hadoopl03、hadoop104上配置为worker。

(1)hadoop102上配置coordinator节点

[atguigu@hadoopl02etc]$vimperties

添加内容如下

coordinator=true

node-scheduler.include-coordinator=false

http-server.http.port=8881

query.max-memory=50GB

discovery-server.enabled=true

discovery.uri=http://hadoopl02:8881

(2)hadoopl03^hadoop104上配置worker节点

[atguigu@hadoopl03etc]$vimperties

添加内容如下

coordinator=false

http-server.http.port=8881

query.max-memory=50GB

discovery.uri=http://hadoopl02:8881

[atguigu@hadoopl04etc]$vimperties

添加内容如下

coordinator=false

http-server.http.port=8881

query.max-memory=50GB

discovery.uri=http://hadoopl02:8881

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

[atguigu@hadoopl02hive]$

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

12)分别在hadoopl02、hadoopl03>hadoop104上启动PrestoServer

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

[atguigu@hadoopl02presto]$bin/launcherrun

[atguigu@hadoopl03presto]$bin/launcherrun

[atguigu@hadoopl04presto]$bin/launcherrun

(2)后台启动Presto

[atguigu@hadoopl02presto]$bin/launcherstart

[atguigu@hadoopl03presto]$bin/launcherstart

[atguigu@hadoopl04presto]$bin/launcherstart

13)11志查看路径/opt/module/presto/data/var/log

1.2.2Presto命令行Client安装

1)下载Presto的客户端

https://repol.maven.ovg/maven2/com/faceboKk/p)esto/Dresto-cli/0.196/Dresto-cli-0.196・

execulablc.jar

2)将presto-cli-0.196-executable.jar上传至ijhadoop102W/opt/module/presto文件夹下

3)修改文件名称

[atguigu@hadoopl02presto]$mvpresto-cli-0.196-executable.jar

prestocli

4)增加执行权限

[atguigu@hadoopl02presto]$chmod+xprestocli

5)启动prestocli

[atguigu@hadoopl02presto]$./prestocli--serverhadoopl02:8881

--cataloghive——schemadefault

6）Presto命令行操作

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

例如：

select*fromschema.tablelimit100

1.2.3Presto可视化Client安装

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

2）解压缩yanagishima

[atguigu@hadoopl02module]$unzipyanagishima-18.0.zip

cdyanagishima-18.0

3）进入至lj/opt/module/yanagishima-18.0/conf文件夹，编写perties酉己置

[atguigu@hadoopl02conf]$vimyanagishima.properties

添加如下内容

jetty.port=7080

presto.datasources=atguigu-presto

presto,coordinator.server.atguigu-presto=http://hadoopl02:8881

catalog.atguigu-presto=hive

schema.atguigu-presto=default

sql.query.engines=presto

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

[atguigu@hadoopl02yanagishima-18.0]$

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

5）启动web页面

http://hadoop102:7080

看到界面，进行查询了。

6）查看表结构

Schema<：>TableOColumnrjExtraComment

defaultdm.versson.aBocationntws.ld

mid.daMy.nwis.dtlang

lnfornwtion.schemamid.news.histofy.divttsion.nMne

mid_U5«r_hljW»y.dt

area

ods.ad.dt

toul.dhpUy

ods_background_dt

toul.dkk

partitionkey

ods.dtsplay.dt

ods.newsdeuilpro.dt

（xpandColumnsMLICT•FROM...WHEUot-2eiW22-LIMITtoe

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

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

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

1MHl111111HH11111aM1Hli11MlM

I1select,fromhive.dwweather.middailynowsdtlimit10

CMBIC；hive，•Basetable•view

Schema□Table。ColumnTypeExtr

defaultdm.verslon.allocatlon二news.ldvarchar

dw.weathermld.dally.news.dtvarchar

lnformation_schemamld.news.hlstory.dtvarchar

testmid_user_history_dt

varchar

ods.ad.dt

Integer

ods.background.dt

integer

ods_basedata_dt

varcharpan

ods.display.dt

ods.loadlng.dt

ods.nesvsdetailpro.dt

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

ctrl+enter键执行显示结果

Vyanagishima18.0au«M<eatiguigu-prestob.presto

•.elect•fromhiv«.dw_w««ther.tmp_n«ws_c1ick|1im:t10

品Treeview三QueryListSHistory★BookmarkSResultG>Timeline

20190223.122817.00012_54w6c■2019/02/2320:28:258.03278105

linenewsidlangversion_nameareauserjd

1n4260en127MXu2868

2n0010en133MXu6515

♦6759P«1.0.7MX□9947

4n8048en1.2.5MXU5196

n3795en13.5MXU5296

6n3021en13.0MXU8066

7n7023en1.3.8MXU8958

8(17696enMXu5696

9n9O67Pt1.12MXU0179

10n6068en12.6MX□3771

1.3Presto优化之数据存储

1.3.1合理设置分区

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

读取量，提升查询性能。

1.3.2使用列式存储

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

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

1.3.3使用压缩

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

采用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右边的表数据整个复制一份发送到每个worker进行计算。如果右边的表数据量太大，

则可能会报内存溢出错误。

[GOOD]SELECT・・・FROMlargetable1joinsmalltableson1.id

=s.id

[BAD]SELECT.・,FROMsmall_tablesjoinlargetable1on1.id

=s.id

1.5注意事项

1.5.1字段名引用

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

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

1.5.2时间函数

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

Timestamp可.以直接进行比较。

//MySQL的写法*/

SELECTtFROMaWHEREt>f2017-01-0100:00:00';

/*Presto中的写法*/

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

1.5.3不支持INSERTOVERWRITE语法

Presto中不支持insertoverwrite语法，只能先delete,然后insertintOo

1.5.4PARQUET格式

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

第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）实时或批量摄取.Druid可以实时摄取数据（实时获取的数据可立即用于查询）或

批量处理数据。

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

无需任何停机时间。

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

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

2.1.3Druid应用场景

S>Druid应用场景◎尚硅谷

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

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

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

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

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

2.1.4Druid对比Impala/Presto/SparkSQL/Kylin/EIasticsearch

Druid对比Impala/Presto/SparkSQL/Kylin/EIasticsearch。商硅谷

飙・ES画■闻组3国・国4）SparkSQL：基于Spark平台上的一个OLAP框架，

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

YYYYYY

（开发中）丫丫度。

SQL支持NYYN

鹿线YYYYYY

实时YN（开发中）NNNY

）最大的特点是使用了倒排索引解决索引问题。

精确去里NYYYYN5ES：

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

JDBCforBlNYYYYN立

MJ。

1）Druid：i出一个实时处理时序数据的OLAP数据库，因为它的索引首先按照6）框架选型：

时间分片，：在询的时候也是按照时间线去路由索引。（1）从超大数据的查询效率来看：

Druid>Kylin>Presto>SparkSQL

2）Kylin：出定心是Cube,Cube是•种预计算技术，基本思路是预先对数据作”

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

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

Presto>SparkSQL>Kylin>Druid

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

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

4）Impala：基于内存运算，速度快.支持的数据源没仃Presto?

2.2Druid框架原理

沙Druid框架原理O商硅谷

4）拙调。点：主5）统治老：进程监Dn«dnodM

要负责历史N点的数据视MiddleManager进程，并ExternalDcpendanc***

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

则（Rule）管理数据的CoordinatorOverlord器,他们负费将提取任务

Nodes

生命周期.Ncxies分配给MiddleManagers并

协冏Segement发布，

3）森谕磔点：接收客户端

位询请求，并将这些杳询转发给

Historicals^lMiddleManagers.

StrMwnmoDaU

Broket

MetadataStorageZookeeper♦・dQuertet

Nodes当Brokers从这些子杳询中

BattftDato

收到结果时,它们会合并这些结

1）中间管理节点，及时8>Zookeeper：为Q

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

掇入实时数据.已生成7）元数现年.存筒Druid集科的:Druid集群提供以执行协调

元数树信息,比如的相关信息.■查询节点果刖了线存技术

Segment数据文件.Segment服务.如内部服务的监控.

一般用MySQl.协调和领导者选举.

Historical

DeepStorage

Nodes.

2）历史。点:加栽已生成好的数据文件,以供数据有

）数阳文件存储浑:存放生成的数据文件,并