千亿级数仓项目第06-07天讲义

课程目标

• 完成Kylin环境搭建（安装、整合mr引擎）
• 能够使用Kylin进行OLAP分析
• 能够基于Kylin开发Ads层
• 能够使用Superset完成数据可视化

Kylin简介

Kylin的诞生背景

• ebay-中国团队研发的，是第一个真正由中国人自己主导、从零开始、自主研发、并成为Apache顶级开源项目
• Hive的性能比较慢，支持SQL灵活查询，特别慢
• HBase的性能快，原生不支持SQL
• Kylin是将先将数据进行预处理，将预处理的结果放在HBase中。效率很高

Kylin的应用场景

Kylin 典型的应用场景如下：

• 用户数据存在于Hadoop HDFS中，利用Hive将HDFS文件数据以关系数据方式存取，数据量巨大，在500G以上
• 每天有数G甚至数十G的数据增量导入
• 有10个以内较为固定的分析维度

Kylin 的核心思想是利用空间换时间，在数据 ETL 导入 OLAP 引擎时提前计算各维度的聚合结果并持久化保存

使用kylin的公司

为什么要使用Kylin

• Kylin 是一个 Hadoop 生态圈下的 MOLAP 系统，是 ebay 大数据部门从2014 年开始研发的支持 TB 到 PB 级别数据量的分布式 Olap 分析引擎。其特点包括:
  • 可扩展的超快的 OLAP 引擎
  • 提供 ANSI-SQL 接口
  • 交互式查询能力
  • MOLAP Cube 的概念（立方体）
  • 与 BI 工具可无缝整合

Kylin的总体架构

• Kylin 依赖于 Hadoop、Hive、Zookeeper 和 Hbase

  kylin数据流程图

Kylin安装

依赖环境

软件	版本
Apache hbase-1.1.1-bin.tar.gz	1.1.1
spark-2.2.0-bin-2.6.0-cdh5.14.0.tgz	2.2.0-bin-cdh5.14.0
apache-kylin-2.6.3-bin-hbase1x.tar.gz	2.6.3

集群规划

主机名	IP	守护进程
node1	192.168.88.120	NameNode DataNode RunJar(Hive metastore) RunJar(Hive hiveserver2) QuorumPeerMain HMaster HRegionServer kylin NodeManager
node2	192.168.88.121	SecondaryNameNode JobHistoryServer DataNode HRegionServer QuorumPeerMain ResourceManager HistoryServer NodeManager
node3	192.168.88.122	HRegionServer NodeManager DataNode QuorumPeerMain

注意：

• kylin-2.6.3-bin-hbase1x所依赖的hbase为1.1.1版本

• 要求hbase的hbase.zookeeper.quorum值必须只能是host1,host2,…。不允许出现host:2181,…

安装kylin-2.6.3-bin-hbase1x

## 解压apache-kylin-2.6.3-bin-hbase1x.tar.gz
tar -zxf /export/softwares/apache-kylin-2.6.3-bin-hbase1x.tar.gz -C /export/servers/

增加kylin依赖组件的配置

/export/servers/apache-kylin-2.6.3-bin-hbase1x/conf
ln -s $HADOOP_HOME/etc/hadoop/hdfs-site.xml hdfs-site.xml
ln -s $HADOOP_HOME/etc/hadoop/core-site.xml core-site.xml
ln -s $HBASE_HOME/conf/hbase-site.xml hbase-site.xml
ln -s $HIVE_HOME/conf/hive-site.xml hive-site.xml
ln -s $SPARK_HOME/conf/spark-defaults.conf spark-defaults.conf

配置kylin.sh

/export/servers/apache-kylin-2.6.3-bin-hbase1x/bin
vim kylin.sh

kylin.sh文件添加如下内容：

export HADOOP_HOME=/export/servers/hadoop-2.6.0-cdh5.14.0
export HIVE_HOME=/export/servers/hive-1.1.0-cdh5.14.0
export HBASE_HOME=/export/servers/hbase-1.1.1
export SPARK_HOME=/export/servers/spark-2.2.0-bin-hadoop2.6

配置conf/kylin.properties

修改 资料\Kylin\kylin_配置文件\kylin.properties 中HDFS的路径，然后上传到 Linux的 Kylin/conf文件夹中

初始化kylin在hdfs上的数据路径

hadoop fs -mkdir -p /apps/kylin

启动集群

1、启动zookeeper

2、启动HDFS

3、启动YARN集群

4、启动HBase集群

5、启动 metastore

nohup hive --service metastore &

6、启动 hiverserver2

nohup hive --service hiveserver2 &

7、启动Yarn history server

mr-jobhistory-daemon.sh start historyserver

8、启动spark history server【可选】

sbin/start-history-server.sh 

7、启动kylin

./kylin.sh start

7、登录Kylin

http://node1:7070/kylin

url	http://IP:7070/kylin
默认用户名	ADMIN
默认密码	KYLIN

• 用户名和密码都必须是大写

入门案例

测试数据表结构介绍

1、（事实表）dw_sales

列名	列类型	说明
id	string	订单id
date1	string	订单日期
channelid	string	订单渠道（商场、京东、天猫）
productid	string	产品id
regionid	string	区域名称
amount	int	商品下单数量
price	double	商品金额

2、（维度表_渠道方式）dim_channel

列名	列类型	说明
channelid	string	渠道id
channelname	string	渠道名称

3、（维度表_产品名称）dim_product

列名	列类型	说明
productid	string	产品id
productname	string	产品名称

4、（维度表_区域）dim_region

列名	类类型	说明
regionid	string	区域id
regionname	string	区域名称

导入测试数据

为了方便后续学习Kylin的使用，需要准备一些测试表、测试数据。

1. Hive中创建表
2. 将数据从本地文件导入到Hive

操作步骤

1、使用 beeline 连接Hive

!connect jdbc:hive2://node1:10000

2、创建并切换到 itcast_dw 数据库

create database itcast_kylin_dw;
use itcast_kylin_dw;

3、找到资料中的hive.sql文件，执行sql、创建测试表

-- 查看表是否创建成功
show tables;

4、在home目录创建~/dat_file 文件夹，并将测试数据文件上传到该文件夹中

mkdir ~/dat_file

导入数据到表中

5、执行一条SQL语句，确认数据是否已经成功导入

select * from dw_sales;

按照日期统计订单总额/总数量（Hive方式）

操作步骤：

1、使用beeline连接Hive

2、切换到itcast_dw数据库

3、编写SQL语句

操作步骤：

1、使用beeline连接Hive

2、切换到itcast_dw数据库

use itcast_dw

3、在代码目录中创建sql文件，编写SQL语句

select date1, sum(price) as total_money, sum(amount) as total_amount from dw_sales group by date1;

按照日期统计订单总额/总数量（Kylin方式）

要使用Kylin进行OLAP分析，需要按照以下方式来进行。

1、创建项目（Project）

2、创建数据源（DataSource）

• 指定有哪些数据需要进行数据分析

3、创建模型（Model）

• 指定具体要对哪个事实表、那些维度进行数据分析

4、创建立方体（Cube）

• 指定对哪个数据模型执行数据预处理，生成不同维度的数据

5、执行构建、等待构建完成

6、再执行SQL查询，获取结果

• 从Cube中查询数据

操作步骤

创建项目（Project）

创建项目（Project）

创建数据源（DataSource）

将Hive中的表都导入到Kylin中 在添加表名中，指定数据库名itcast_kylin_dw.dim_channel,itcast_kylin_dw.dim_product,itcast_kylin_dw.dim_region,itcast_kylin_dw.dw_sales

创建模型（Model）

1、 指定模型名称
2、 指定事实表
3、 指定维度表
4、 指定待分析的指标
5、 指定分区和过滤条件

创建立方体（Cube）

1、选择数据模型
2、指定维度
3、指定度量
4、指定刷新设置
5、指定执行引擎

执行构建

执行SQL语句分析

执行sql

在Insight选项卡中，执行以下SQL语句

select
  date1,
  sum(price) as total_money,
  sum(amount) as total_amount
from
  dw_sales
group by date1;

按照订单渠道名称统计订单总额/总数量

1、创建Model

• 指定关联表、关联条件

  指定关联表、关联条件

2、创建Cube

3、执行构建、等待构建完成

4、执行SQL查询，获取结果

执行SQL查询，获取结果

select
	t2.channelid,
	t2.channelname,
	sum(t1.price) as total_money,
	sum(t1.amount) as total_amount
from 
	dw_sales t1
inner join dim_channel t2
on t1.channelid = t2.channelid
group by t2.channelid, t2.channelname

按照日期、区域、产品维度统计订单总额/总数量

1、创建Model

指定关联表、关联条件
指定维度字段

2、创建Cube

指定cube维度

3、执行构建、等待构建完成

4、执行SQL查询，获取结果

select
	t1.date1, 
	t2.regionid, 
	t2.regionname, 
	t3.productid, 
	t3.productname,
	sum(t1.price) as total_money,
	sum(t1.amount) as total_amount
from
	dw_sales t1
inner join dim_region t2
on t1.regionid = t2.regionid
inner join dim_product t3
on t1.productid = t3.productid
group by 
	t1.date1, 
	t2.regionid, 
	t2.regionname, 
	t3.productid, 
	t3.productname
order by 
	t1.date1,
	t2.regionname,
	t3.productname

Kylin的工作原理

Apache Kylin的工作原理本质上是 MOLAP（多维立方体分析）。

维度和度量

• 维度就是观察数据的角度，例如：
  • 电商的销售数据，可以从时间的维度来观察，也可以细化从时间和地区的维度来观察
  • 统计时，可以把维度值相同的记录聚合在一起，然后应用聚合函数做累加、平均、去重计数等聚合计算
• 度量就是被聚合的统计值，也是聚合运算的结果。

时间（维度）	销售额（度量）
2019 1Q	1.7M
2019 2Q	2.1M
2019 3Q	1.6M
2019 4Q	1.8M

时间（维度）	地区（维度）	销售额（度量）
2019 1Q	中国	1.0M
2019 1Q	北美	0.7M
2019 2Q	中国	1.5M
2019 2Q	北美	0.6M
2019 3Q	中国	0.9M
2019 3Q	北美	0.7M
2019 4Q	中国	0.9M
2019 4Q	北美	0.9M

Cube和Cuboid

• 一个数据表或数据模型上的字段就它们要么是维度，要么是度量（可以被聚合）

• 给定一个数据模型，可以对其上的所有维度进行组合。对于N个维度来说，组合的所有可能性共有2 的N 次方种

• 对于每一种维度的组合，将度量做聚合运算，然后将运算的结果保存为一个物化视图，称为Cuboid（立方形）

• 所有维度组合的Cuboid作为一个整体，被称为Cube（立方体）。一个Cube就是许多按维度聚合的物化视图的集合。

说明	维度1地区	维度2时间	维度3	维度4
取值	0或1	0或1	0或1	0或1

• 数据立方体
  • Cube 是所有 dimession 的组合
  • 每一种 dimession 的组合称之为cuboid（立方形）。某一有 n 个 dimession 的 cube 会有 2^n个 cuboid
  • 数据立方体只是多维模型的一个形象的说法

组合示意图

• 为什么叫立方体？
  • 立方体本身只有三维，但多维模型不仅限于三维模型，可以组合更多的维度
  • 为了与传统关系型数据库的二维表区别开来，才有了数据立方体的叫法

工作原理

Apache Kylin的工作原理是对数据模型做Cube预计算，并利用计算的结果加速查询。具体工作过程如下。

1. 指定数据模型，定义维度和度量
2. 预计算Cube，计算所有Cuboid并保存为物化视图（存储到hbase中）
3. 执行查询时，读取Cuboid，运算，产生查询结果

高效OLAP分析：

• Kylin的查询过程不会扫描原始记录，而是通过预计算预先完成表的关联、聚合等复杂运算
• 利用预计算的结果来执行查询，相比非预计算的查询技术，其速度一般要快一到两个数量级，在超大的数据集上优势更明显
• 数据集达到千亿乃至万亿级别时，Kylin的速度可以超越其他非预计算技术1000倍以上

技术架构

Apache Kylin系统可以分为在线查询和离线构建两部分。

kylin架构图

在线查询模式主要处于上半部分，离线构建处于下半部分。以下为Kylin技术架构的具体内容：

1. 数据源主要是Hadoop Hive，数据以关系表的形式输入，且必须符合星形模型，保存着待分析的用户数据。根据元数据的定义，构建引擎从数据源抽取数据，并构建Cube
2. Kylin可以使用MapReduce或者Spark作为构建引擎。构建后的Cube保存在右侧的存储引擎中，一般选用HBase作为存储
3. 完成了离线构建后，用户可以从上方查询系统发送SQL进行查询分析
4. Kylin提供了各种Rest API、JDBC/ODBC接口。无论从哪个接口进入，SQL最终都会来到Rest服务层，再转交给查询引擎进行处理
5. SQL语句是基于数据源的关系模型书写的，而不是Cube
   • Kylin在设计时，刻意对查询用户屏蔽了Cube的概念
   • 分析师只需要理解简单的关系模型就可以使用Kylin，没有额外的学习门槛，传统的SQL应用也很容易迁移
   • 查询引擎解析SQL，生成基于关系表的逻辑执行计划，然后将其转译为基于Cube的物理执行计划，最后查询预计算生成的Cube并产生结果，整个过程不会访问原始数据源

Kylin增量构建

应用场景

• Kylin在每次Cube的构建都会从Hive中批量读取数据，
• 对于大多数业务场景来说，Hive中的数据处于不断增长的状态。
• 为了支持Cube中的数据能够不断地得到更新，且无需重复地为已经处理过的历史数据构建Cube，因此对于
  Cube引入了增量构建的功能

理解Cube、Cuboid与Segment的关系

Kylin将Cube划分为多个Segment（对应就是HBase中的一个表），

• 每个Segment用起始时间和结束时间来标志

• Segment代表一段时间内源数据的预计算结果。

• 一个Segment的起始时间等于它之前那个Segment的结束时间，同理，它的结束时间等于它后面那个Segment的起始时间。

• 同一个Cube下不同的Segment除了背后的源数据不同之外，其他如结构定义、构建过程、优化方法、存储方式等都完全相同。

  segment示意图

一个Cube，可以包含多个Cuboid，而Segment是指定时间范围的Cube，可以理解为Cube的分区。对应就是HBase中的一张表。该表中包含了所有的Cuboid。

例如：以下为针对某个Cube的Segment

Segment名称	分区时间	HBase表名
201910110000000-201910120000000	20191011	KYLIN_41Z8123
201910120000000-201910130000000	20191012	KYLIN_5AB2141
201910130000000-201910140000000	20191013	KYLIN_7C1151
201910140000000-201910150000000	20191014	KYLIN_811680
201910150000000-201910160000000	20191015	KYLIN_A11AD1

全量构建与增量构建

全量构建

在全量构建中，

• Cube中只存在唯一的一个Segment
• 该Segment没有分割时间的概念，也就没有起始时间和结束时间
• 对于全量构建来说，每当需要更新Cube数据的时候，它不会区分历史数据和新加入的数据，也就是说，在构建的时候会导入并处理所有的原始数据。

增量构建

增量构建

• 只会导入新Segment指定的时间区间内的原始数据，并只对这部分原始数据进行预计算。

全量构建和增量构建的对比

全量构建	增量构建
每次更新时都需要更新整个数据集	每次只对需要更新的时间范围进行更新，因此离线计算量相对较小
查询时不需要合并不同Segment的结果	查询时需要合并不同Segment的结果，因此查询性能会受影响
不需要后续的Segment合并	累计一定量的Segment之后，需要进行合并
适合小数据量或全表更新的Cube	适合大数据量的Cube

全量构建与增量构建的Cube查询方式对比：

• 全量构建Cube
  • 查询引擎只需向存储引擎访问单个Segment所对应的数据，无需进行Segment之间的聚合
  • 为了加强性能，单个Segment的数据也有可能被分片存储到引擎的多个分区上，查询引擎可能仍然需要对单个Segment不同分区的数据做进一步的聚合
• 增量构建Cube
  • 由于不同时间的数据分布在不同的Segment之中，查询引擎需要向存储引擎请求读取各个Segment的数据
  • 增量构建的Cube上的查询会比全量构建的做更多的运行时聚合，通常来说增量构建的Cube上的查询会比全量构建的Cube上的查询要慢一些

对于小数据量的Cube，或者经常需要全表更新的Cube，使用全量构建需要更少的运维精力，以少量的重复计算降低生产环境中的维护复杂度。而对于大数据量的Cube，例如，对于一个包含两年历史数据的Cube，如果需要每天更新，那么每天为了新数据而去重复计算过去两年的数据就会变得非常浪费，在这种情况下需要考虑使用增量构建

增量构建Cube过程

1、指定分割时间列

增量构建Cube的定义必须包含一个时间维度，用来分割不同的Segment，这样的维度称为分割时间列（Partition Date Column）。

2、增量构建过程

• 在进行增量构建时，将增量部分的起始时间和结束时间作为增量构建请求的一部分提交给Kylin的任务引擎
• 任务引擎会根据起始时间和结束时间从Hive中抽取相应时间的数据，并对这部分数据做预计算处理
• 将预计算的结果封装成为一个新的Segment，并将相应的信息保存到元数据和存储引擎中。一般来说，增量部分的起始时间等于Cube中最后一个Segment的结束时间。

增量Cube的创建

创建增量Cube的过程和创建普通Cube的过程基本类似，只是增量Cube会有一些额外的配置要求

1、配置Model

增量构建的Cube需要指定分割时间列。例如：将日期分区字段添加到维度列中

将日期分区字段添加到维度列中

在设置中，配置分区列，并指定日期格式

注意事项

• 注意构建Cube时，选择的分区时间为，起始时间（包含）、结束时间（不保存），对应了从Hive从获取数据源的条件

INSERT OVERWRITE TABLE `kylin_intermediate_cube_order_1582ee64_45f9_cf22_bef2_e0b455efc284` SELECT
`FACT_ORDER`.`DT` as `FACT_ORDER_DT`
,`FACT_ORDER`.`USER_ID` as `FACT_ORDER_USER_ID`
,`FACT_ORDER`.`PRICE` as `FACT_ORDER_PRICE`
 FROM `ITCAST_KYLIN_DW`.`FACT_ORDER` as `FACT_ORDER`
INNER JOIN `ITCAST_KYLIN_DW`.`DIM_USER` as `DIM_USER`
ON `FACT_ORDER`.`USER_ID` = `DIM_USER`.`ID`
WHERE 1=1 AND (`FACT_ORDER`.`DT` >= '20191011' AND `FACT_ORDER`.`DT` < '20191012');

2、查看Segment

查看segment

3、构建 20191012、20191013的Cube数据

构建 20191012、20191013的Cube数据

INSERT OVERWRITE TABLE `kylin_intermediate_cube_order_16b6b739_cf24_fa63_c9bb_f8932b8c5d15` SELECT
`FACT_ORDER`.`DT` as `FACT_ORDER_DT`
,`FACT_ORDER`.`USER_ID` as `FACT_ORDER_USER_ID`
,`FACT_ORDER`.`PRICE` as `FACT_ORDER_PRICE`
 FROM `ITCAST_KYLIN_DW`.`FACT_ORDER` as `FACT_ORDER`
INNER JOIN `ITCAST_KYLIN_DW`.`DIM_USER` as `DIM_USER`
ON `FACT_ORDER`.`USER_ID` = `DIM_USER`.`ID`
WHERE 1=1 AND (`FACT_ORDER`.`DT` >= '20191012' AND `FACT_ORDER`.`DT` < '20191013');

查看任务名称

4、查看增量构建Cube对应的Segment

查看增量构建Cube对应的Segment

REST API触发增量构建

在Web GUI上进行的所有操作，其背后调用的都是同一套Rest API。将来可以SHELL脚本调度REST API触发构建。

属性	值
URL	http://node1:7070/kylin/api/cubes/{Cube名称}/rebuild
请求方式	PUT
RequestBody（JSON字符串）	{“startTime”:时间戳, “endTime”: “时间戳”, “buildType”: “BUILD/MERGE/REFRESH”}

注意：

• Kylin中Cube构建的时间采用CST（北京时间），而REST提交的时间采用的是UTC（世界标准时间）
• CST = UTC + 8
• startTime、endTime提交到Kylin，应该 +8 个小时

参考JSON：

{
	"startTime": "1571011200000",
	"endTime": "1571097600000",
	"buildType": "BUILD"
}

时间戳转换工具：

http://tool.chinaz.com/Tools/unixtime.aspx

postman示意图

Cube碎片管理

增量构建的问题

日积月累，增量构建的Cube中的Segment越来越多，该Cube的查询性能也会越来越慢，因为需要在单点的查询引擎中完成越来越多的运行时聚合。为了保持查询性能：

• 需要定期地将某些Segment合并在一起
• 或者让Cube根据Segment保留策略自动地淘汰那些不会再被查询到的陈旧Segment

管理Cube碎片

上述案例，每天都会生成一个Segment，对应就是HBase中的一张表。增量构建的Cube每天都可能会有新的增量。这样的Cube中最终可能包含上百个Segment，这将会导致Kylin性能受到严重的影响。

• 从执行引擎的角度来说，运行时的查询引擎需要聚合多个Segment的结果才能返回正确的查询结果
• 从存储引擎的角度来说，大量的Segment会带来大量的文件，给存储空间的多个模块带来巨大的压力，例如Zookeeper、HDFS Namenode等

因此，有必要采取措施控制Cube中Segment的数量。

segment数量过多

手动触发合并Segment

Kylin提供了一种简单的机制用于控制Cube中Segment的数量：合并Segments。在Web GUI中选中需要进行Segments合并的Cube，

操作步骤：

1、单击Action→Merge

单击Action→Merge

2、选中需要合并的Segment，可以同时合并多个Segment，但是这些Segment必须是连续的

单击提交后系统会提交一个类型为“MERGE”的构建任务，它以选中的Segment中的数据作为输入，将这些Segment的数据合并封装成为一个新的Segment。新的Segment的起始时间为选中的最早的Segment的起始时间，它的结束时间为选中的最晚的Segment的结束时间。

查看合并任务

注意事项

• 在MERGE类型的构建完成之前，系统将不允许提交这个Cube上任何类型的其他构建任务
• 在MERGE构建结束之前，所有选中用来合并的Segment仍然处于可用的状态
• 当MERGE构建结束的时候，系统将选中合并的Segment替换为新的Segment，而被替换下的Segment等待将被垃圾回收和清理，以节省系统资源

删除Segment

使用WebUI删除Cube的segment

1、disable Cube
2、点击 delete Segment，删除指定的segment

使用Postman发送请求删除Segment

属性	值
URL	http://node1:7070/kylin/api/cubes/{cubeName}/segs/{segmentName}
请求方式	DELETE

postman示意图

自动合并

手动维护Segment很繁琐，人工成本很高，Kylin中是可以支持自动合并Segment。

在Cube Designer的“Refresh Settings”的页面中有：

• Auto Merge Thresholds
• Retention Threshold

“Refresh Settings”的页面

两个设置项可以用来帮助管理Segment碎片。这两项设置搭配使用这两项设置可以大大减少对Segment进行管理的麻烦。

1、Auto Merge Thresholds

• 允许用户设置几个层级的时间阈值，层级越靠后，时间阈值就越大

• 每当Cube中有新的Segment状态变为 READY的时候，会自动触发一次系统自动合并

• 合并策略

  • 尝试最大一级的时间阈值，例如：针对（7天、28天）层级的日志，先检查能否将连续的若干个Segment合并成为一个超过28天的大Segment
    • 如果有个别的Segment的时间长度本身已经超过28天，系统会跳过Segment
    • 如果满足条件的连续Segment还不能够累积超过28天，那么系统会使用下一个层级的时间阈值重复寻找

示例1 - 理解Kylin自动合并策略

• 假设自动合并阈值设置为7天、28天
• 如果现在有A-H8个连续的Segment，它们的时间长度为28天（A）、7天（B）、1天（C）、1天（D）、1天（E）、1天（F）、1天（G）、1天（H）
• 此时，第9个Segment I加入，时间长度为1天。

自动合并策略为：

1、Kylin判断时候能将连续的Segment合并到28天这个阈值，由于Segment A已经超过28天，会被排除

2、剩下的连续Segment，所有时间加一起 B + C + D + E + F + G + H + I （7 + 1 + 1 + 1 + 1 + 1 + 1 + 1 = 14） < 28天，无法满足28天阈值，开始尝试7天阈值

3、跳过A（28）、B（7）均超过7天，排除

4、剩下的连续Segment，所有时间加一起 C + D + E + F + G + H + I（1 + 1 + 1 + 1 + 1 + 1 + 1 = 7）达到7天阈值，触发合并，提交Merge任务。并构建一个Segment X（7天）

5、合并后，Segment为：A（28天）、B（7天）、X（7天）

6、继续触发检查，A（28天）跳过，B + X（7 + 7 = 14）< 28天，不满足第一阈值，重新使用第二阈值触发

7、跳过B、X，尝试终止

2、示例：配置自动合并4天的Segment

操作步骤：

1、配置自动合并阈值为（4、28）
2、分别按照天构建分区Cube
3、自动触发合并Segment构建

配置保留Segment

自动合并是将多个Segment合并为一个Segment，以达到清理碎片的目的。保留Segment则是及时清理不再使用的Segment。

在很多场景中，只会对过去一段时间内的数据进行查询，例如：

• 对于某个只显示过去1年数据的报表
• 支撑它的Cube其实只需要保留过去一年类的Segment即可
• 由于数据在Hive中已经存在备份，则无需在Kylin中备份超过一年的历史数据

可以将Retention Threshold设置为365。每当有新的Segment状态变为READY的时候，系统会检查每一个Segment。如果它的结束时间距离最晚的一个Segment的结束时间已经大于等于“Retention Threshold”，那么这个Segment将被视为无需保留。系统会自动地从Cube中删除这个Segment。

保留策略示意图

需求：

• 配置保留Segment为2天，分别构建增量Segment，测试Segment保留情况

操作步骤：

1、在Cube中设置Retention Range为2

在Cube中设置Retention Range为2

2、重新构建Cube

3、测试超过指定保留时间的Segment，是否被自动移除

使用JDBC连接操作Kylin

• 要将数据以可视化方式展示出来，需要使用Kylin的JDBC方式连接执行SQL，获取Kylin的执行结果
• 使用Kylin的JDBC与JDBC操作MySQL一致
  • jdbc url
    • jdbc:kylin://node1:7070/itcast_dw
  • 用户名密码：ADMIN/KYLIN

需求

通过JDBC方式，查询按照日期、区域、产品维度统计订单总额/总数量结果

开发步骤

• 导入驱动依赖

<dependencies>
    <!-- Kylin -->
    <dependency>
        <groupId>org.apache.kylin</groupId>
        <artifactId>kylin-jdbc</artifactId>
        <version>2.6.3</version>
    </dependency>
    <dependency>
        <groupId>com.google.guava</groupId>
        <artifactId>guava</artifactId>
        <version>27.1-jre</version>
    </dependency>
</dependencies>

1、加载驱动

2、创建Connection连接对象

3、构建SQL语句

4、创建Statement对象，并执行executeQuery

5、打印结果

参考代码

public static void main(String[] args) throws Exception {
    // 1、加载驱动
    Class.forName("org.apache.kylin.jdbc.Driver");
    // 2、创建Connection连接对象
    // 连接字符串：jdbc:kylin://ip地址:7070/项目名称（project）
    Connection connection = DriverManager.getConnection("jdbc:kylin://node1:7070/itcast_dw",
                                                        "ADMIN",
                                                        "KYLIN");

    // 3、创建Statement对象，并执行executeQuery，获取ResultSet
    Statement statement = connection.createStatement();

    // 构建SQL和语句
    String sql = "select\n" +
        "  t1.date1,\n" +
        "  t2.regionname,\n" +
        "  productname,\n" +
        "  sum(t1.price) as total_money,\n" +
        "  sum(t1.amount) as total_amount\n" +
        "from\n" +
        "  dw_sales t1\n" +
        "inner join dim_region t2\n" +
        "on t1.regionid = t2.regionid\n" +
        "inner join dim_product t3\n" +
        "on t1.productid = t3.productid\n" +
        "group by\n" +
        "  t1.date1,\n" +
        "  t2.regionid,\n" +
        "  t2.regionname,\n" +
        "  t3.productid,\n" +
        "  t3.productname";
    ResultSet resultSet = statement.executeQuery(sql);

    // 4、打印ResultSet
    while(resultSet.next()) {
        // 4.1 获取时间
        String date1 = resultSet.getString("date1");
        // 4.2 获取区域名称
        String regionname = resultSet.getString("regionname");
        // 4.3 获取产品名称
        String productname = resultSet.getString("productname");
        // 4.4 总金额
        String total_money = resultSet.getString("total_money");
        // 4.5 总数量
        String total_amount = resultSet.getString("total_amount");

        System.out.println(date1 + " " + regionname + " " + productname + " " + total_money + " " + total_amount);
    }

    connection.close();
}

Kylin Cube优化

Cuboid剪枝优化

为什么要进行Cuboid剪枝剪枝优化

将以减少Cuboid数量为目的的Cuboid优化统称为Cuboid剪枝。在没有采取任何优化措施的情况下，Kylin会对每一种维度的组合进行预计算，每种维度的组合的预计算结果被称为Cuboid。

• 如果有4个维度，可能最终会有2^4 =16个Cuboid需要计算。但在实际开发中，用户的维度数量一般远远大于4个。

• 如果有10个维度，那么没有经过任何优化的Cube就会存在2^10 =1024个Cuboid

• 如果有20个维度，那么Cube中总共会存在2^20 =104 8576个Cuboid

这样的Cuboid的数量就足以让人想象到这样的Cube对构建引擎、存储引擎压力非常巨大。因此，在构建维度数量较多的Cube时，尤其要注意Cube的剪枝优化。

Cube的剪枝优化是一种试图减少额外空间占用的方法，这种方法的前提是不会明显影响查询时间。在做剪枝优化的时候，

• 需要选择跳过那些“多余”的Cuboid
• 有的Cuboid因为查询样式的原因永远不会被查询到，因此显得多余
• 有的Cuboid的能力和其他Cuboid接近，因此显得多余

Kylin提供了一系列简单的工具来帮助他们完成Cube的剪枝优化

检查Cuboid数量

Apache Kylin提供了一个简单的工具，检查Cube中哪些Cuboid最终被预计算了，称这些Cuboid为被物化的Cuboid，该工具还能给出每个Cuboid所占空间的估计值。由于该工具需要在对数据进行一定阶段的处理之后才能估算Cuboid的大小，因此一般来说只能在Cube构建完毕之后再使用该工具。

使用如下的命令行工具去检查这个Cube中的Cuboid状态：

bin/kylin.sh org.apache.kylin.engine.mr.common.CubeStatsReader CUBE_NAME 
# CUBE_NAME 想要查看的Cube的名字

示例：

bin/kylin.sh org.apache.kylin.engine.mr.common.CubeStatsReader cube_order 

============================================================================
Statistics of cube_order[20191011000000_20191015000000]

Cube statistics hll precision: 14
Total cuboids: 3
Total estimated rows: 20
Total estimated size(MB): 1.02996826171875E-4
Sampling percentage:  100
Mapper overlap ratio: 0.0
Mapper number: 0
Length of dimension ITCAST_KYLIN_DW.FACT_ORDER.DT is 1
Length of dimension ITCAST_KYLIN_DW.FACT_ORDER.USER_ID is 1
|---- Cuboid 11, est row: 12, est MB: 0
    |---- Cuboid 01, est row: 4, est MB: 0, shrink: 33.33%
    |---- Cuboid 10, est row: 4, est MB: 0, shrink: 33.33%
----------------------------------------------------------------------------

输出结果分析：

Cube statistics hll precision: 14
Total cuboids: 3
Total estimated rows: 20
Total estimated size(MB): 1.02996826171875E-4
Sampling percentage:  100
Mapper overlap ratio: 0.0
Mapper number: 0

• 估计Cuboid大小的精度（Hll Precision）
• 总共的Cuboid数量
• Segment的总行数估计
• Segment的大小估计，Segment的大小决定mapper、reducer的数量、数据分片数量等

|---- Cuboid 11, est row: 12, est MB: 0
    |---- Cuboid 01, est row: 4, est MB: 0, shrink: 33.33%
    |---- Cuboid 10, est row: 4, est MB: 0, shrink: 33.33%

• 所有的Cuboid及它的分析结果都以树状的形式打印了出来
• 在这棵树中，每个节点代表一个Cuboid，每个Cuboid都由一连串1或0的数字组成
• 数字串的长度等于有效维度的数量，从左到右的每个数字依次代表Rowkeys设置中的各个维度。如果数字为0，则代表这个Cuboid中不存在相应的维度；如果数字为1，则代表这个Cuboid中存在相应的维度
• 除了最顶端的Cuboid之外，每个Cuboid都有一个父亲Cuboid，且都比父亲Cuboid少了一个“1”。其意义是这个Cuboid就是由它的父亲节点减少一个维度聚合而来的（上卷）
• 最顶端的Cuboid称为Base Cuboid，它直接由源数据计算而来。Base Cuboid中包含所有的维度，因此它的数字串中所有的数字均为1
• 每行Cuboid的输出中除了0和1的数字串以外，后面还有每个Cuboid的具体信息，包括该Cuboid行数的估计值、该Cuboid大小的估计值，以及这个Cuboid的行数与父亲节点的对比（Shrink值）
• 所有Cuboid行数的估计值之和应该等于Segment的行数估计值，所有Cuboid的大小估计值应该等于该Segment的大小估计值。每个Cuboid都是在它的父亲节点的基础上进一步聚合而成的

检查Cube大小

在Web GUI的Model页面选择一个READY状态的Cube，当我们把光标移到该Cube的Cube Size列时，Web GUI会提示Cube的源数据大小，以及当前Cube的大小除以源数据大小的比例，称为膨胀率（Expansion Rate）

查看cube大小

一般来说，Cube的膨胀率应该在0%~1000%之间，如果一个Cube的膨胀率超过1000%，那么应当开始挖掘其中的原因。通常，膨胀率高有以下几个方面的原因：

• Cube中的维度数量较多，且没有进行很好的Cuboid剪枝优化，导致Cuboid数量极多
• Cube中存在较高基数的维度，导致包含这类维度的每一个Cuboid占用的空间都很大，这些Cuboid累积造成整体Cube体积变大
• 存在比较占用空间的度量，例如Count Distinct，因此需要在Cuboid的每一行中都为其保存一个较大度量数据，最坏的情况将会导致Cuboid中每一行都有数十KB，从而造成整个Cube的体积变大。

对于Cube膨胀率居高不下的情况，管理员需要结合实际数据进行分析，优化。

使用衍生维度

示例：

• 有两张表 用户维度表（dim_user）、订单事实表（fact_order），要根据各个维度建立MOLAP立方体

用户维度表（dim_user）

ID	姓名	出生年份	政治面貌	职业	性别	民族	省份	市	区
1	张三	1999	团员	程序员	男	汉族	北京	北京市	东城区
2	李四	1989	党员	学生	女	回族	北京	北京市	昌平区
3	王五	1990	党员	程序员	男	汉族	北京	北京市	昌平区
4	赵六	1997	党员	快递员	男	傣族	上海	上海市	闵行区

订单事实表（fact_order）

订单id	用户ID	价格
O0001	1	1000
O0002	1	2000
O0003	3	3000
O0004	4	4000
O0005	5	4000
O0006	6	3000

问题：

• 生成Cube时，如果指定维度表中的：姓名、出生年份、政治面貌、职业、性别、民族、省份、市、区等维度生成Cube，这些维度相互组合，会造成较大的Cube膨胀率

使用衍生维度用于在有效维度内将维度表上的非主键维度排除掉，并使用维度表的主键（其实是事实表上相应的外键）来替代它们。Kylin会在底层记录维度表主键与维度表其他维度之间的映射关系，以便在查询时能够动态地将维度表的主键“翻译”成这些非主键维度，并进行实时聚合。

衍生维度
姓名
出生年份
政治面貌
职业
性别
民族
省份
市
区

选择衍生维度

创建Cube的时候，这些维度如果指定为衍生维度，Kylin将会排除这些维度，而是使用维度表的主键来代替它们创建Cuboid。后续查询的时候，再基于主键的聚合结果，再进行一次聚合。

优化效果：维度表的N个维度组合成的cuboid个数会从2的N次方降为2。

不适用的场景：

• 如果从维度表主键到某个维度表维度所需要的聚合工作量非常大，此时作为一个普通的维度聚合更合适，否则会影响Kylin的查询性能

聚合组

• 聚合组（Aggregation Group）是一种更强大的剪枝工具

• 聚合组假设一个Cube的所有维度均可以根据业务需求划分成若干组

• 同一个组内的维度更可能同时被同一个查询用到，每个分组的维度集合均是Cube所有维度的一个子集

• 不同的分组各自拥有一套维度集合，它们可能与其他分组有相同的维度，也可能没有相同的维度

• 每个分组各自独立地根据自身的规则贡献出一批需要被物化的Cuboid，所有分组贡献的Cuboid的并集就成为了当前Cube中所有需要物化的Cuboid的集合

• 不同的分组有可能会贡献出相同的Cuboid，构建引擎会察觉到这点，并且保证每一个Cuboid无论在多少个分组中出现，它都只会被物化一次

  聚合组介绍

对于每个分组内部的维度，用户可以使用如下三种可选的方式定义它们之间的关系，具体如下：

• 强制维度（Mandatory）

  • 如果一个维度被定义为强制维度，那么这个分组产生的所有Cuboid中每一个Cuboid都会包含该维度。所有cuboid必须包含的维度，不会计算不包含强制维度的cuboi
  • 每个分组中都可以有0个、1个或多个强制维度
  • 如果根据这个分组的业务逻辑，则相关的查询一定会在过滤条件或分组条件中，因此可以在该分组中把该维度设置为强制维度
  • 适用场景
    • 可以将确定在查询时一定会使用的维度设为强制维度。例如，时间维度。
  • 优化效果
    • 将一个维度设为强制维度，则cuboid个数直接减半

• 层级维度（Hierarchy）

  • 每个层级包含两个或更多个维度
  • 假设一个层级中包含D1，D2…Dn这n个维度，那么在该分组产生的任何Cuboid中，这n个维度只会以（），（D1），（D1，D2）…（D1，D2…Dn）这n+1种形式中的一种出现
  • 每个分组中可以有0个、1个或多个层级，不同的层级之间不应当有共享的维度
  • 如果根据这个分组的业务逻辑，则多个维度直接存在层级关系，因此可以在该分组中把这些维度设置为层级维度
  • 使用场景
    • 年，月，日；国家，省份，城市这类具有层次关系的维度
  • 优化效果
    • 将N个维度设置为层次维度，则这N个维度组合成的cuboid个数会从2的N次方减少到N+1

• 联合维度（Joint）

  • 每个联合中包含两个或更多个维度，如果某些列形成一个联合，那么在该分组产生的任何Cuboid中，这些联合维度要么一起出现，要么都不出现

  • 每个分组中可以有0个或多个联合，但是不同的联合之间不应当有共享的维度（否则它们可以合并成一个联合）。如果根据这个分组的业务逻辑，多个维度在查询中总是同时出现，则可以在该分组中把这些维度设置为联合维度

  • 适用场景

    • 可以将确定在查询时一定会同时使用的几个维度设为一个联合维度

  • 优化效果

    • 将N个维度设置为联合维度，则这N个维度组合成的cuboid个数会从2的N次方减少到1

      聚合组设置

需求：

• 对Cube膨胀率进行调优

准备：

• 执行Kylin\kylin_Cube调优中的SQL脚本
• 导入Kylin中的数据

具体步骤：

优化前：创建model

创建为优化cube:

优化cube:

对比效果：

离线数仓开发

基于Kylin开发Ads层

项目需求介绍

因为业务需要，公司运营部门，希望随时能够自己编写SQL语句，快速获取到不同维度数据的指标，故基于Kylin OLAP分析平台，搭建快速OLAP分析平台。

业务开发

开发步骤：

1、创建 itcast_shop 项目

2、导入dw层宽表数据

3、创建数据模型

4、创建Cube立方体

5、构建立方体

6、执行查询

导入数据：

创建model:

创建model:

创建cube:

创建cube:

查询sql样例

获取全国、一级商品分类维度的不分交易类型数据

select
t2.cat_2d_id,
t2.cat_2d_name,
t2.cat_3d_id,
t2.cat_3d_name,
   count(distinct t1.order_id) as total_count,
    sum(t1.goods_num) as total_goods_num,
    sum(t1.pay_money) as total_money
from 
itcast_dw.fact_order_goods_wide as t1
inner join itcast_dw.dim_goods_cat as t2
on t1.goods_cat_3d_id =t2.cat_3d_id and t1.dt=t2.dt
where t1.dt='20190909'
group by t2.cat_2d_id,t2.cat_2d_name,t2.cat_3d_id,t2.cat_3d_name;

模块开发-数据可视化Superset

BI VS 报表工具

• 报表工具是数据展示工具，而BI（商业智能）是数据分析工具。报表工具可以制作各类数据报表、图形报表的工具，甚至还可以制作电子发票联、流程单、收据等。

• BI可以将数据进行模型构建，制作成Dashboard，相比于报表，侧重点在于分析，操作简单、数据处理量大。常常基于企业搭建的数据平台，连接数据仓库进行分析。

简介

Superset是一款开源的现代化企业级BI。它是目前开源的数据分析和可视化工具中比较好用的，功能简单但可以满足我们对数据的基本需求，支持多种数据源，图表类型多，易维护，易进行二次开发。

功能

• 丰富的数据可视化集
• 易于使用的界面，用于浏览和可视化数据
• 创建和共享仪表板
• 与主要身份验证提供程序（数据库，OpenID，LDAP，OAuth和REMOTE_USER通过Flask AppBuilder集成）集成的企业就绪身份验证
• 可扩展的高粒度安全性/权限模型，允许有关谁可以访问单个要素和数据集的复杂规则
• 一个简单的语义层，允许用户通过定义哪些字段应显示在哪些下拉列表中以及哪些聚合和功能度量可供用户使用来控制如何在UI中显示数据源
• 通过SQLAlchemy与大多数说SQL的RDBMS集成
• 与Druid.io的深度集成

支持的数据库

superset现在支持的所有数据库或分析引擎：

• Amazon Athena
• Amazon Redshift
• Apache Drill
• Apache Druid
• Apache Hive
• Apache Impala
• Apache Kylin
• Apache Pinot
• Apache Spark SQL
• BigQuery
• ClickHouse
• Google Sheets
• Greenplum
• IBM Db2
• MySQL
• Oracle
• PostgreSQL
• Presto
• Snowflake
• SQLite
• SQL Server
• Teradata
• Vertica

界面

superset示例页面

安装

安装python3

首先升级python版本，我们使用Anaconda来安装Python3版本的python。

1、首先去Anaconda官网下载安装脚本

资料\superset\Anaconda3-2019.07-Linux-x86_64.sh

2、上传Anaconda3-2019.07-Linux-x86_64.sh

使用 FileZilla 上传到node3 /export/softwares

3、运行Anaconda3-2019.07-Linux-x86_64.sh脚本

sh Anaconda3-2019.07-Linux-x86_64.sh

安装过程输入：回车、yes、

Anaconda安装目录设置为：/export/servers/anaconda

4、配置环境变量

vim /etc/profile
#Anaconda 
export PATH=$PATH:/root/anaconda3/bin
source /etc/profile

5、验证是否安装python3成功

python3

提示出现python3.x版本即安装成功！！

退出使用quit();

注意：对于重新打开的终端连接会出现base字样，消除方法：

若在终端中输入conda deactivate，也可消除base字样，但是一次性的，再次打开终端依然存在base字样。在.bashrc文件（home目录下）添加命令：conda deactivate可以永久消除base字样。

至此python3已经安装成功。

安装superset

1、安装依赖

yum upgrade python-setuptools
yum install gcc gcc-c++ libffi-devel python-devel python-pip python-wheel openssl-devel libsasl2-devel openldap-devel

1、pip安装superset

cd /export/servers/anaconda3/
pip install superset==0.30.0

需要联网下载文件等待一段时间

安装进度图

2、创建管理员用户名和密码

fabmanager create-admin --app superset

记住以下信息，登录使用：

Username [admin]: admin
User first name [admin]: admin
User last name [user]: admin
Email [admin@fab.org]: 
Password: 123456
Repeat for confirmation: 123456
Recognized Database Authentications.
Admin User admin created.

3、初始化superset

superset db upgrade

4、装载初始化数据

superset load_examples

5、创建默认角色和权限

superset init

6、启动superset

superset run -h node3 -p 8080 --with-threads --reload --debugger

7、登录superset

http://node3:8080/superset/welcome

用户名： admin

密码：123456

切换到中文

8、Superset 初体验

superset入门案例

需求：

• 使用Superset展示不同性别的用户人数

• 效果图

准备环境

yum install python-devel -y
yum install mysql-devel -y
yum install gcc -y
pip install mysqlclient

实现步骤：

1、导入MySQL数据源

导入资料中的 superset\数据源\superset_demo.sql

2、添加新的数据库

mysql的url地址

mysql://root:123456@node1/superset_demo?charset=utf8

3、点击 SQLLab > SQL Editor编写以下SQL语句

选择 数据库

选择表，查看表的列

参考SQL语句：

select
	case when gender = 0 then '男'
		when gender = 1 then '女'
		else '保密'
    end as gender,
	count(id) as total_cnt
from 
	t_user
group by gender

4、保存查询

5、点击 saved queries

保存查询

• 运行查询，点击 Explore 浏览数据

  浏览数据

6、配置图表类型为 Bar Chart 条形图

选择bar chart

7、指定统计指标 sum(total_cnt)

8、指定序列为 gender（性别）

修改分组字段

Superset功能介绍

• 用户权限
• Sources
• Manage
• Charts
• Dashboards
• SQL Lab

Superset实战 - MySQL订单分析案例

Superset Charts图表展示实战

1、根据日期统计，每日订单总额（趋势图）

每日订单总额（趋势图）

select 
	str_to_date(date1,'%Y-%m-%d') date1,
	sum(price) total_price
from
	dm_sales
group by date1;

2、根据日期、渠道统计订单总额（Sunburst Chart）

select
	date1,
	channelname,
	sum(price) total_price
from
	dm_sales
group by 
	date1,
	channelname

双环图

3、根据日期、区域统计订单总额（数据透视表）

select
	str_to_date(date1,'%Y-%m-%d') date1,
	regionname,
	sum(amount) as total_amount,
	sum(price) as total_price
from
	dm_sales
group by
	date1,
	regionname

数据透视表
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-i91Gb5Zy-1651199472599)(http://img.sucaiwa.cn/5c311ab7c76411eca94164fb8169d209.png)]

4、根据日期、区域、渠道、产品统计订单数、订单总额（层级环图）

select
	date1,
	regionname,
	channelname,
	productname,
	sum(price) as total_price
from
	dm_sales
group by
	date1,
	regionname,
	channelname,
	productname

层级环图

Superset Dashboards看板展示实战

将之前设计好的图标整合到看板中

操作步骤：

1、点击 Dashboards > 添加看板

2、拖动之前开发好的 Charts 到看板中

拖动开发好的 Charts 到看板中

Superset权限控制

Superset初始化权限之后，创建5个角色，分别为Admin，Alpha，Gamma，sql_lab以及Public。Admin，Alpha和Gamma角色，分配了很多的菜单/视图权限，如果手工去修改，改错的可能性很大，加之Superset并没有说明每一项权限的完整文档，所以不建议去修改这些角色的定义。灵活使用预置的角色，可以快速满足业务上安全控制需求。

角色权限介绍

• Admin：拥有所有权限

• Alpha：能访问所有数据源，增加或者更改数据源，但不能给更改其他用户权限。

• Gamma：必须结合其他能访问数据源的角色才能访问数据。这个角色所能访问的切片和看板，也是基于能访问数据源所创建的切片和看板。

• sql_lab：能访问SQL Lab菜单。

• Public：默认没有任何权限

匿名访问

所有用户都能访问某一个看板，需要进行如下设置 ：

1、更改config.py文件，设置如下部分，PUBLIC_ROLE_LIKE_GAMMA = True

vim /export/servers/anaconda3/lib/python3.7/site-packages/superset/config.py

2、需要运行superset init命令，这个命令会给“Public”角色设置与“Gamma”一样的权限

superset init

3、将匿名用户所需要访问的数据库和数据源分配给“Public”角色。例如，基于superset_demo数据库的grade_test创建了看板，如果匿名用户需要查看这个看板，那将如下权限分配给“Public”。

• all database access on all_database_access
• all datasource access on all_datasource_access

分配权限

• 删除一些菜单权限

  删除一些菜单权限

分享页面或者嵌入html

html页面：

<html>
<head>
<title>dashboard</title>
</head>
<body>
    <div class="dashboard">
       <!-- <iframe src="http://node3:8080/superset/dashboard/7/"  style="height=100%; width=100%" ></iframe > -->
		
		<iframe name="myframe" src="http://node3:8080/r/10" frameborder="0" scrolling="auto" width="100%" height="100%" onload="document.all['myframe'].style.height=myframe.document.body.scrollHeight" ></iframe>
    </div>
	</body>
</html>

角色介绍

实际业务中，不同的职能部门访问的数据不一样，例如财务部需要访问成本，应收，应付等数据，供应链需要访问库存数量，发货数据等，怎样简洁的设置，快速满足这种业务需求？

如前文所述，“Gamma”拥有大部分基础的权限，但是必须结合其他能访问数据源的角色才能访问数据。所以，可以给用户分配“Gamma”角色和针对部门分别创建的数据源角色来进行控制。

例如，针对财务用户，创建角色“Finance”，将成本，应收，应付的数据表权限赋予这个角色，财务用户就分配“Gamma”和“Finance”。

针对供应链用户，创建角色“SCM”，将库存和发货数据表权限赋予这个角色，供应链用户就配“Gamma”和“SCM”。

如果是公司的霸道总裁，需要看所有的看板，就可以给霸道总裁赋予“Gamma”和“Finance”，“SCM”角色。

角色介绍

我们创建2个角色，分别是main角色可以查看访问main的数据，

examples角色可以查看和访问 examples 数据源。

1、创建 main 角色

• database access on [main] 拥有访问 main 数据库的权限
• datasource access on [main] 拥有访问main 数据源的权限
• can dashboard on Superset 拥有访问 main 数据源创建的 dashboard的权限

main分配权限

2、创建examples角色

• database access on [examples] 拥有访问 examples数据库的权限
• datasource access on [examples] 拥有访问examples数据源的权限
• can dashboard on Superset 拥有访问 examples数据源创建的 dashboard的权限

example分配权限

3、创建用户

• main_user: 关联gamma、sqllab与main角色；

  main_user: 关联gamma、sqllab与main角色；

• examples_user: 关联gamma、sqllab与examples角色；

  examples_user: 关联gamma、sqllab与examples角色

• 用不同的用户登录查看每个用户具有的table，以及能查看到的dashboard!!

使用Supset进行业务开发

Superset对接MySQL展示离线指标数据

准备：

• 导入 资料\superset\数据源\ads_dim_table.sql

1、添加之前离线阶段开发好的 itcast_ads_shop 数据库数据源

• jdbc连接：mysql://root:123456@node1/itcast_ads_shop?charset=utf8

2、统计指定日期 按照大区获取订单总额

select
	t2.orgname as regionname,
	allprice
from
	ads_trade_order t1
	left join itcast_org t2
	on t1.regionid = t2.orgid
where dt = '20190905' and regionid != '9999'

日期 、大区订单总额

2、统计指定日期 按照大区、商品一级分类ID、订单总额

select
	t4.orgname,
	t2.catname,
	allprice
from
	ads_trade_order t1
    left join itcast_goods_cats t2 
	on t1.firstcatid  = t2.catId 
	left join itcast_goods_cats  t3
	on t1.secondcatid  = t3.catId 
	left join itcast_org t4
	on t1.regionid  = t4.orgId 
	left join itcast_org t5
	on t1.cityid  = t5.orgId 
where dt = '20190905' and regionid != '9999' and firstcatid != '9999'

More Actions日期 、大区、商品一级分类ID订单总额

Superset对接Kylin展示离线指标数据

环境准备：

• 添加 kylin 支持

 pip install kylinpy

• 启动Kylin
  • 启动HDFS
  • 启动HBASE
  • 启动Hive metastore、hiveserver2

添加 kylin JDBC数据库

• kylin的JDBC url 为

kylin://ADMIN:KYLIN@node1:7070/itcast_shop

1、统计大区、店铺分类、支付方式订单总额

select 
    REGIONID ,
    SHOPID ,
    PAYTYPE ,
    SUM (GOODSPRICE ) total_money
FROM TMP_ORDER_GOODS_CAT_ORG 
group by REGIONID, SHOPID ,PAYTYPE 

kylin指标结果图

2、统计大区、一级分类、支付方式订单笔数、订单总额

select 
    REGIONID ,
    FIRSTCAT ,
    PAYTYPE ,
    count(DISTINCT ORDERID ) total_amount,
    SUM (GOODSPRICE ) total_money
FROM TMP_ORDER_GOODS_CAT_ORG 
group by REGIONID, FIRSTCAT , PAYTYPE  

kylin指标层级环图

创建看板

kylin指标看板

rset=utf8

2、统计指定日期 按照大区获取订单总额

select
	t2.orgname as regionname,
	allprice
from
	ads_trade_order t1
	left join itcast_org t2
	on t1.regionid = t2.orgid
where dt = '20190905' and regionid != '9999'

日期 、大区订单总额

2、统计指定日期 按照大区、商品一级分类ID、订单总额

select
	t4.orgname,
	t2.catname,
	allprice
from
	ads_trade_order t1
    left join itcast_goods_cats t2 
	on t1.firstcatid  = t2.catId 
	left join itcast_goods_cats  t3
	on t1.secondcatid  = t3.catId 
	left join itcast_org t4
	on t1.regionid  = t4.orgId 
	left join itcast_org t5
	on t1.cityid  = t5.orgId 
where dt = '20190905' and regionid != '9999' and firstcatid != '9999'

More Actions日期 、大区、商品一级分类ID订单总额

Superset对接Kylin展示离线指标数据

环境准备：

• 添加 kylin 支持

 pip install kylinpy

• 启动Kylin
  • 启动HDFS
  • 启动HBASE
  • 启动Hive metastore、hiveserver2

添加 kylin JDBC数据库

• kylin的JDBC url 为

kylin://ADMIN:KYLIN@node1:7070/itcast_shop

1、统计大区、店铺分类、支付方式订单总额

select 
    REGIONID ,
    SHOPID ,
    PAYTYPE ,
    SUM (GOODSPRICE ) total_money
FROM TMP_ORDER_GOODS_CAT_ORG 
group by REGIONID, SHOPID ,PAYTYPE 

kylin指标结果图

2、统计大区、一级分类、支付方式订单笔数、订单总额

select 
    REGIONID ,
    FIRSTCAT ,
    PAYTYPE ,
    count(DISTINCT ORDERID ) total_amount,
    SUM (GOODSPRICE ) total_money
FROM TMP_ORDER_GOODS_CAT_ORG 
group by REGIONID, FIRSTCAT , PAYTYPE  

kylin指标层级环图

创建看板

kylin指标看板