Kylin_入门

目录

目录
摘要
关系数据库数据导入Hive
Sqoop简介
Sqoop安装
Sqoop配置
Sqoop全量导入
Sqoop增量导入
快速入门
创建项目
导入Hive表定义
创建数据模型
设计Cube
构建Cube
全量构建
增量构建
历史数据刷新
合并
查询Cube
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)  编辑  收藏  举报