Kylin_入门

目录

toc

摘要

本文主要内容分为一下几个方面:

  1. 关系数据库数据导入Hive
  2. Cube设计
  3. Cube构建
  4. Cube应用
  5. Cube优化

目的是提供通过Kylin进行多维分析的整套解决方案。

关系数据库数据导入Hive

步骤缘由: 因为Kylin只支持Hive数据, 因此将关系数据库数据导入Hive

基础框架: 本功能基于Sqoop实现

导入分类: 1. 全量导入, 对应星型模型中的维度表 2. 增量导入, 对应星型模型中的事实表

Sqoop简介

Sqoop是一款开源的工具,主要用于在Hadoop(Hive)与传统的数据库(mysql、postgresql...)间进行数据的传递,可以将一个关系型数据库(例如 : MySQL ,Oracle ,Postgres等)中的数据导进到Hadoop的HDFS中,也可以将HDFS的数据导进到关系型数据库中。

Sqoop安装

1) 下载地址:http://mirrors.hust.edu.cn/apache/sqoop/1.4.7/

2) 上传安装包sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz到虚拟机中

3) 解压sqoop安装包到指定目录,如:

$ tar -zxf sqoop-1.4.7.bin__hadoop-2.6.0.tar.gz -C /opt/module/

Sqoop配置

Sqoop的配置文件与大多数大数据框架类似,在sqoop根目录下的conf目录中。

1) 重命名配置文件

$ mv sqoop-env-template.sh sqoop-env.sh

2) 修改配置文件: sqoop-env.sh

export HADOOP_COMMON_HOME=/opt/module/hadoop-2.7.2
export HADOOP_MAPRED_HOME=/opt/module/hadoop-2.7.2
export HIVE_HOME=/opt/module/hive
export ZOOKEEPER_HOME=/opt/module/zookeeper-3.4.10
export ZOOCFGDIR=/opt/module/zookeeper-3.4.10
export HBASE_HOME=/opt/module/hbase

3) 拷贝JDBC到Sqoop

拷贝要连接的关系数据库的JDBC到Sqoop的lib目录中,例如

$ cp mysql-connector-java-5.1.27-bin.jar /opt/module/sqoop-1.4.7.bin__hadoop-2.6.0/lib/

测试连接情况

$ bin/sqoop list-databases --connect jdbc:mysql://hadoop102:3306/ --username root --password 000000

Sqoop全量导入

应用于维度表, 因为维度表一般数据量较小, 且修改频率低, 数据发生变化后直接重新全量覆盖

首先创建一个数据库配置文件,如下

ip=192.168.73.12   #注意等号两边不要有空格
username=testUser
passwd=123456
dbname=fdw
connectURL="jdbc:sqlserver://`echo $ip`;DatabaseName=`echo $dbname`"

维度表导入脚本

参数: 维度表表名

功能: 将关系数据库维度表读入Hive同名表

#!/bin/bash
function load_config()
{
filePath="/home/atguigu/bin/bank_project_im/`echo $1`" #这是我存放的配置文件路径
echo $filePath
if [ -f "$filePath" ]; then
  . $filePath
fi
}
load_config db_config  #传入配置文件名称

${SQOOP_HOME}/bin/sqoop import \
--connect $connectURL \
--username $username \
--password $passwd \
--table $1 \
--num-mappers 1 \
--hive-import \
--fields-terminated-by "\t" \
--hive-overwrite \
--hive-table $1

Sqoop import参数解释:

参数 说明
connect 关系数据库连接
username 关系数据库用户名
password 关系数据库密码
table 关系数据库表名
num-mappers 建立 n 个并发执行task import
hive-import 将数据导入到hive
fields-terminated-by 指定导入文件的分割符, 要与目标Hive表的相同
hive-overwrite 重写已有表, 如不指定, 在表格已存在的情况下会报错
hive-table 目标hive表

Sqoop增量导入

应用于导入事实表, 事实表数据量庞大, 并且增速快, 一般设定成定时增量导入

同时因为其需要长时间后台运行, 我们需要将结果保存在日志中

事实表导入脚本

参数: 日期, 表格名称

功能: 导入数据并记录日志

详细步骤:

  1. 记录导入任务开始日志
  2. 加载对应事实表数据库连接配置
  3. 使用Sqoop在HDFS创建以日期命名的分区文件, 并将数据导入
  4. 记录第三步执行日志, 如果成功, 在Hive中添加相应分区的元数据
  5. 日志插入Hive表
#!/bin/bash
#时间,表格名称两个参数

pcount=$#
if((pcount==1)); then
time=$(date "+%Y%m%d")
table=$1
else
time=$1
table=$2
fi
#写入日志
logfile=/home/atguigu/bin/bank_project_im/sqoop_log/$table.txt
echo $time  >> $logfile
d_date_format=`echo ${time} | awk '{print substr($d_date,1,4)"-"substr($0,5,2)"-"substr($0,7,2)}'`
echo ${d_date_format} >> $logfile
start_date=`date '+%Y-%m-%d %H:%M:%S'`
echo ${start_date} >> $logfile

#与维度表相同,加载配置文件
function load_config()
{
filePath="/home/atguigu/bin/bank_project_im/`echo $1`" #这是我存放的配置文件路径
echo $filePath
if [ -f "$filePath" ]; then
  . $filePath
fi
}

load_config db_config  #传入配置文件名称
${SQOOP_HOME}/bin/sqoop import \
--connect $connectURL \
--username $username \
--password $passwd \
--query "select * from $table  where data_date=$time and \$CONDITIONS;"  \
--target-dir "/user/hive/warehouse/$table/data_date=$time"  \
--fields-terminated-by '\t' \
--num-mappers 1 \

#判断sqoop执行结果,etl_status为0表示失败,为1表示成功,由于这里无法获取到错误信息,只能以"errors"代替
if [ $? -ne 0 ];then
        etl_status=0
        etl_error="errors"
        echo "执行失败" >> $logfile
else
        etl_status=1
        etl_error="\\\\N"
        echo "执行成功" >> $logfile
        #数据导入成功,在Hive中添加对对应分区数据的支持
        ${HIVE_HOME}/bin/hive -e "alter table $table add partition (data_date=$time)"
fi

#日志写入到hive中,参数顺序对应说明

#table_name=$1
#etl_date=$2
#etl_status=$3
#start_time=$4
#etl_errors=$5
#write audit of etl's recored  into  hive's table

sh /home/atguigu/bin/bank_project_im/hive_etl_record.sh "$table" "$time" "$etl_status"  "$start_date" "$etl_errors"

事实表导入脚本中用到hive_etl_record.sh

参数: 5个日志表字段

功能: 实现向Hive中插入日志, 具体实现如下

#!/bin/bash
if [ $# != 5 ];then
  echo 'must be provide 5 paras'
  echo $#
  exit 2
fi
echo "传入的参数值为以下"
for arg in "$@"
do
    echo $arg
done
#参数赋值
table_name=$1
etl_date=$2
etl_status=$3
start_time=$4
etl_errors=$5
${HIVE_HOME}/bin/hive -e "
insert into table view_etl_audit
select  '$table_name'as  etl_name
                ,'$table_name'as etl_table
                ,'$etl_date' as  etl_date
                ,$etl_status as etl_status
                ,'$start_time' as start_time
                ,from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss')as end_time
from    dual;"

用于日志存储的Hive表

create table view_etl_audit (
,table_name  string comment '表名,如果MyTestTabel_20150511则MyTestTable'
,etl_date  string comment '取日期,如果MyTestTabel_20150511则20150511'
,etl_status int
,start_time string comment 'sqoop脚本的开始时间'
,end_time string comment 'sqoop脚本的结束时间'
)
comment 'table for keeping etl audit record'
row  format delimited
fields terminated by '\t'
lines terminated by '\n'
stored as textfile;

快速入门

快速实现一个Cube的构建

创建项目

点击页面左上角加号, 创建项目

image-20200618093907003

填写项目名和项目描述

image-20200618094007759

导入Hive表定义

单击Web界面的Model→Data source下的“Load Hive Table”图标,然后输入表的名称(可以一次导入多张表,以逗号分隔表名),单击按钮“Sync”,Kylin就会使用Hive的API从Hive中获取表的属性信息。

加载Hive表定义

image-20200617173018791

一次可导入多个表

image-20200617173122340

导入成功后, 表的结构信息会以树状的形式显示在页面的左侧

image-20200618094253097

同时, kylin会在后台触发一个MapReduce任务, 计算此表每一列的基数

image-20200618094425038

注意, 此处采用近似算法精确值有误差, 但作为参考值已经足够了。

创建数据模型

有了表信息之后,就可以开始创建数据模型(Data Model)了。数据模型是Cube的基础,它主要用于描述一个星形模型。有了数据模型以后,定义Cube的时候就可以直接从此模型定义的表和列中进行选择了,省去重复指定连接(join)条件的步骤。基于一个数据模型还可以创建多个Cube,以方便减少用户的重复性工作。

在Kylin界面的“Models”页面中,单击“New”→“New Model”,开始创建数据模型。给模型输入名称之后,选择一个事实表(必需的),然后添加维度表(可选),如图所示。

添加事实表

image-20200618094827939

添加维度表的时候,需要选择连接的类型:是Inner还是Left,然后选择连接的主键和外键,这里也支持多主键,如图所示。

添加维度表

image-20200618094956673

接下来选择会用作维度和度量的列。这里只是选择一个范围,不代表这些列将来一定要用作Cube的维度或度量,你可以把所有可能会用到的列都选进来,后续创建Cube的时候,将只能从这些列中进行选择。

选择维度列

image-20200618095350988

选择度量列

image-20200618095450363

最后一步,是为模型补充分割时间列信息和过滤条件。如果此模型中的事实表记录是按时间增长的,那么可以指定一个日期/时间列作为模型的分割时间列,从而可以让Cube按此列做增量构建。

image-20200618100354930

过滤(Filter)条件是指,如果想把一些记录忽略掉,那么这里可以设置一个过滤条件。Kylin在向Hive请求源数据的时候,会带上此过滤条件。在图2-8所示的示例中,会直接排除掉金额小于等于0的记录。

image-20200618100517576

最后,单击“Save”保存此数据模型,随后它将出现在“Models”的列表中。

设计Cube

本章快速介绍Cube各种配置选项,但不会对Cube的配置和优化进行深入介绍。读者可以在Cube优化一章找到更详细的介绍。

第一页,选择要使用的数据模型,并为此Cube输入一个唯一的名称(必需的)和描述(可选的)(如图2-9所示);这里还可以输入一个邮件通知列表,用于在构建完成或出错时收到通知。

image-20200619102655147

第二页,选择Cube的维度。通过“Add Dimension”按钮完成。

“Add Dimension”:逐个添加维度,可以是普通维度也可以是衍生(Derived)维度。

image-20200619103118907

第三页,创建度量。Kylin默认会创建一个Count(1)的度量。可以单击“+Measure”按钮来添加新的度量。Kylin支持的度量有:SUM、MIN、MAX、COUNT、COUNT DISTINCT、TOP_N、RAW等。请选择需要的度量类型,然后再选择适当的参数(通常为列名)。

image-20200619103531708

第四页,是关于Cube数据刷新的设置。在这里可以设置自动合并的阈值、数据保留的最短时间,以及第一个Segment的起点时间(如果Cube有分割时间列的话),详细内容查看Cube增量构建

image-20200619103834795

数据刷新

第五页,高级设置。在此页面上可以设置聚合组和Rowkey,目的是优化Cube构建和查询效率。详细内容查看看Cube配置与优化

image-20200619105325892

聚合组

image-20200619104201875

Rowkeys

第六页,为Cube配置参数。和其他Hadoop工具一样,Kylin使用了很多配置参数以提高灵活性,用户可以根据具体的环境、场景等配置不同的参数进行调优。Kylin全局的参数值可在conf/kylin.properties文件中进行配置;如果Cube需要覆盖全局设置的话,则需要在此页面中指定。单击“+Property”按钮,然后输入参数名和参数值。

image-20200619110406833

然后单击Next跳转到最后一个确认页面,如有修改,则单击“Prev”按钮返回以修改,最后再单击“Save”按钮进行保存,一个Cube就创建完成了。

创建好的Cube会显示在“Cubes”列表中,如要对Cube的定义进行修改,只需单击“Edit”按钮就可以进行修改。也可以展开此Cube行以查看更多的信息,如JSON格式的元数据、访问权限、通知列表等。

构建Cube

全量构建

对数据模型中没有指定分割时间列信息的Cube,Kylin会采用全量构建,即每次从Hive中读取全部的数据来开始构建。通常它适用于以下两种情形。

  • 事实表的数据不是按时间增长的。
  • 事实表的数据比较小或更新频率很低,全量构建不会造成太大的开销。

增量构建

增量构建的时候,Kylin每次都会从Hive中读取一个时间范围内的数据,然后进行计算,并以一个Segment的形式进行保存。下次再构建的时候,会自动以上次结束的时间为起点时间,再选择新的终止时间进行构建。经过多次构建,Cube中将会有多个Segment依次按时间顺序进行排列,如Seg-1,Seg-2,…,Seg-N。查询的时候,Kylin会查询一个或多个Segment然后再做聚合计算,以便返回正确的结果给请求者。

使用增量构建的好处是,每次只需要对新增数据进行计算,从而避免了对历史数据进行重复计算。对于数据量很大的Cube,使用增量构建是非常有必要的。下图为构建一个Segment的Cube时的输入框。

image-20200619111527236

注意:增量构建抽取数据的范围,采用了前包后闭的原则,即包含了开始时间,但不包含结束时间,从而保证上一个Segment的结束时间与下一个Segment的起始时间相同,但数据不会重复。

历史数据刷新

Cube构建完成以后,如果某些历史数据发生了改动,那么需要针对相应的Segment进行重新计算,这种构建称为刷新。刷新通常只针对增量构建的Cube而言,因为全量构建的Cube只要重新全部构建就可以得到更新;而增量更新的Cube因为有多个Segment,因此需要先选择要刷新的Segment,然后再进行刷新。

下图是提交刷新的请求页面,用户需要在下拉列表中选择一个时间区间。

image-20200619112146643

合并

随着时间的迁移,Cube中可能会存在较多数量的Segment,使得查询性能下降,并且会给HBase集群管理带来压力。对此,需要适时地将一些Segment进行合并,将若干个小Segment合并成较大的Segment。

现在触发一个合并,单击Actions→Merge;选择要合并的起始Segment和结束Segment,生成一个合并的任务,如下图所示。

image-20200619112642827

查询Cube

Kylin提供一个SQL查询界面,点击insight进入

image-20200619112736961

尝试查询

image-20200619113048843

posted @ 2020-06-23 10:33  永遇乐yjt  阅读(456)  评论(0编辑  收藏  举报