sqlite离线技术处理计算

前言

大数量处理技术应用:

  • [x] 电信运营商
  • 数码营销:房地产营销、运营商时代(会聚用户作为)
  • [x] 互联网用户作为分析
  • 多少驱动运营:漏斗模型、反作弊
  • [x] P2P风控系统
  • 民用征信(人民银行)、各大银行贷款记录

俺们正在做的:

离线:

sqlite 1

image

实时:

sqlite 2

image

一、离线数据处理简介

(1)分布式统计(mapreduce)

数据仓库工具hive(基于磁盘存储)构建在mapreduce之上,支持条件SQL的花样查询存储在HDFS上的多寡,原理就是解析HiveQL,经过编译后(SQL转换成MapReduce职分)生成执行安顿(三个Stage依赖)

sqlite 3

image

(2)分布式存储(hdfs)

  1. 是一种允许文件
    通过网络在多台主机(廉价商用机)上享受的文件系统,可让多机器上的多用户分享文件和仓储空间。
  2. 通透性。让实际是通过网络来拜会文件的动作,由程序与用户看来,就如访问当地的磁盘一般。
  3. 容错。即便系统中有好几节点脱机,全部来说系统依然可以持续运转而不会有多少损失。
  4. 适用于 一回写入、 数次查询的动静,不协理并发写意况,小文件不适用
![](https://github.com/guofei1219/RiskControl/blob/master/src/main/resources/jpg/hadoop_hdfs.png?raw=true)
image

(3)分布式资源调度(yarm)

通用资源管理序列,可为上层应用提供联合的资源管理和调度,它的引入为集群在利用率、资源统一管理和数量共享等方面带来了光辉利益

sqlite 4

image

二、数据导入(sqoop)

(1)数据导入格局相比较

  1. mysql表导出为文件方式,再使用hive load加载命令将数据导入Hadoop平台
    可取:格局不难
    缺点:数据可靠性得不到有限支撑,增添性低
  2. 开源工具Sqoop,基于MapReuce
    可取:扩张性高

(2)介绍

Sqoop是一个用来将Hadoop和关系型数据库中的数据交互转换的工具,可以将一个关系型数据库(例如
: MySQL ,Oracle
,Postgres等)中的数据导进到Hadoop的HDFS中,也足以将HDFS的数目导进到关系型数据库中。
对于某些NoSQL数据库它也提供了连接器。Sqoop,类似于其余ETL工具,使用元数据模型来判定数据类型并在数码从数据源转移到Hadoop时有限扶助项目安全的数码处理。Sqoop专为大数量批量传输设计,可以分割数据集并成立Hadoop任务来拍卖每个区块。

(3)个贷数据迁入Hadoop平台

  • [x] 基于个贷表元数据变化查询SQL,替换Hive敏感字符并统一数据类型
  • 换行符、制表符、#等等统一替换成空格符
  • 多种年华格式(timestamp、date、datetime)转换为Hive扶助的联合时间格式
  • [x] 开发迁移脚本
  • 集合时间库
  • 数据校验
  • 动态链接个贷表
扭转查询SQL脚本
SELECT 
    CASE 
        WHEN data_type  IN('varchar','text','char') then
            CONCAT('REPLACE(REPLACE(REPLACE(REPLACE(', column_name,',CHAR(13),'' ''),CHAR(10),'' ''),CHAR(9),'' ''),CHAR(44),'' ''),')
        when data_type in('timestamp','date','datetime') then
            CONCAT('DATE_FORMAT(', column_name, ',''%Y-%m-%d %h:%i:%s''),')
        else
            CONCAT(column_name,',') 
    END
FROM COLUMNS WHERE table_name='crm_repay_dk'
查询SQL样例
select
id,
contract_id,
REPLACE(REPLACE(REPLACE(REPLACE(account_number,CHAR(13),' '),CHAR(10),' '),CHAR(9),' '),CHAR(44),' '),
repay_method,
year,
DATE_FORMAT(first_repay,'%Y-%m-%d %h:%i:%s'),
status,
repay_status,
REPLACE(REPLACE(REPLACE(REPLACE(accntnm,CHAR(13),' '),CHAR(10),' '),CHAR(9),' '),CHAR(44),' '),
REPLACE(REPLACE(REPLACE(REPLACE(accntno,CHAR(13),' '),CHAR(10),' '),CHAR(9),' '),CHAR(44),' '),
REPLACE(REPLACE(REPLACE(REPLACE(bank,CHAR(13),' '),CHAR(10),' '),CHAR(9),' '),CHAR(44),' '),
REPLACE(REPLACE(REPLACE(REPLACE(branchnm,CHAR(13),' '),CHAR(10),' '),CHAR(9),' '),CHAR(44),' '),
loan_type,
from #tablename  where $CONDITIONS
mysql导入hdfs脚本
sqoop import \
--connect jdbc:mysql://$server:$port/$mysql_database?tinyInt1isBit=false \
--username $username \
--password $password  \
--split-by id \
--query "$excute_sql" \
--target-dir $tmp_partition  

echo "load data inpath '$tmp_partition/$table_suff/*' into table $hdb.pl_$table partition(dt='"$partition"')"
#exit
if [ $? -eq 0 ];then
    echo "数据mysql导入hive分区$year$month成功"
    echo "$tmp_partition/$table/*"
    hive -hiveconf hive.exec.parallel=true -e "use $hdb;alter table pl_$table add if not EXISTS partition(dt='"$partition"');
        load data inpath '$tmp_partition/*' into table $hdb.pl_$table partition(dt='"$partition"');"
else
    echo "$tmp_partition 分区下数据为空"
fi

(4)遭逢的问题

  • mysql数据导入hive有两种艺术,第一种是基于全表,第两种是依据查询条件

//基于全表导入
/*
优点:方法简单
缺点:1.导入过程中不能做额外逻辑操作
      2.导入到hdfs中目录结构为 /table/文件,导致hive外部表指定的location失效,解决方法可以使用中间临时存储区过度
      3.关系型数据控中如果有特殊字符可能会导致hive表错列,因为hive建表时会执行行分隔符和列分隔符,当关系型数据库表字段内容含有已指定分隔符就会导致hive表错列
*/
sqoop import \
--connect jdbc:mysql://$server:$port/$mysql_database?tinyInt1isBit=false \
--username sqoop \
--password sqoop 
--table ${table_suff} \
--warehouse-dir $tmp_partition

//基于查询SQL导入

优点:可以进行数据过滤转换等操作
缺点:暂时没发现啥缺点

sqoop import \
--connect jdbc:mysql://$server:$port/$mysql_database?tinyInt1isBit=false \
--username $username \
--password $password  \
--split-by id \
--query "$excute_sql" \
--target-dir $tmp_partition
  • 开局对数码探测不做到,导致对个贷部分表的有些字段内容涵盖的特殊字符明白不成功,比如换行符/制表符/#/逗号,导致大家导入到Hive中会错列
  • mysql 数据类型tinyint转换至hive中为null
    解决方法:tinyInt1isBit=false
  • 原生sqoop只辅助数据导入单分区,要辅助多分区可以先把多少拉到hdfs多分区目录下(/database/table/year=2016/month=10/day=27/),再采纳load命令加载数据

三、数据处理(hive/impala)

(1)数据仓库工具Hive

Hive建表

--放款合同信息表
CREATE EXTERNAL TABLE `pl_crm_account_mes_dk` (
  `id` int,
  `intopieces_id` int,
  `by_name` string,
  `is_by_name` string,
  `by_id_card` string,
  `addressee` string,
  `mail_province` int,
  `mail_city` int,
  `mail_address` string,
  `account_time` int,
  `account_staff_id` int,)
PARTITIONED BY ( 
  `dt` string COMMENT 'by date')
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ',' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  'hdfs://hadb/rmdb/pl_crm_account_mes_dk';

数据加载

加overwrite 数据覆盖对应分区,不加overwrite 会追加数据到对应分区
--load data [local] inpath 'hdfs路径/本地文件路径' [overwrite] into 库名.表名 partition(分区名='对应分区')
注:这个操作是移动数据,而不是复制数据
留神问题:
  1. 多少存放的路子层次要和表的分区一致;
  2. 一经分区表没有新增分区,尽管目的路径下己经有数量了,但照旧查不到数据。
相隔符问题:
  1. 相隔符默许只有单个字符。如若有五个字符,默认取第三个字符作为分隔符。
数据类型对应问题:
  1. Load数据数据,字段类型不能相互转化时,查询结果回到NULL。而事实上的数目如故存在。
  2. Select查询插入,字段类型不可能互相转化时,插入数据为NULL。而实质上的数量也为NULL。
其他:
  1. Select查询插入数据,字段值顺序要与表中字段顺序一致,名称可分歧。
  2. Hive在数据加载时不做检查,查询时检查。
  3. 表面分区表必要加上分区才能看出数据。

hive 中 Order by, Sort by ,Dristribute by,Cluster By 的作用和对照

order by

坚守某些字段排序
样例

select col1,other...
from table
where conditio
order by col1,col2 [asc|desc]

注意
order by后边可以有多列举办排序,默许按字典排序
order by为大局排序
order
by需求reduce操作,且唯有一个reduce,与配置非亲非故。数据量很大时,慎用。

Sort排序

sort by col – 根据col列把多少排序

select col1,col2 from M
distribute by col1
sort by col1 asc,col2 desc

两者结合出现,确保每个reduce的输出都是萧规曹随的。

distribute by与group by对比

都是按key值划分数据
都使用reduce操作
唯一不相同的是distribute by只是一味的粗放数据,而group
by把相同key的数码聚集到联合,后续必须是会晤操作。

order by与sort by 对比

order by是全局排序
sort
by只是确保每个reduce上面输出的数码有序。倘使唯有一个reduce时,和order
by功效一样。

cluster by

把有相同值的数码聚集到一道,并排序。
职能等价于distribute by col sort by col
cluster by col <==> distribute by col sort by col

hive自定义函数

自定义函数介绍

当hive提供的函数无法满意大家的急需时,须求开发者自己支付自定义函数,自定义函数分为以下三类:

  • [x] UDF(user defined function)
  • 用户自定义函数针对单条记录,解决输入一行输出一行需要。
  • 案例:按照用户访问日志得到客户端设备档次

public class UADeviceType {
    public Text evaluate(Text url_text) {
        if(null==url_text){
            return new Text();
        }
        DeviceType dt = null;
        try {
            UserAgent userAgent = UserAgent.parseUserAgentString(url_text.toString());
            dt = userAgent.getOperatingSystem().getDeviceType();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return new Text(dt.getName());
    }
    public static void main(String[] args) {
        UserAgent userAgent = UserAgent.parseUserAgentString("Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.1 (KHTML, like Gecko) Chrome/21.0.1180.89 Safari/537.1");
        System.out.println(userAgent.getOperatingSystem().getDeviceType().getName());
    }
}
  • [x] UDTF(User-Defined Table-Generating Functions)
  • 用来化解 输入一行输出多行(On-to-many maping) 的需要。
  • 案例:从日志表抽取降价券券码生成让利券明细表

hive --hiveconf mapreduce.job.queuename=root.offline.hdp_daojia.normal -v -e "use hdp_daojia_defaultdb;
   insert overwrite table dj58_f_coupon  partition(dt=$1)
    select coupon_code,cookieid,urlfields['hmsr'],order_id,operation_status,mobile,urlfields['znsr'] 
    from dj58_o_web lateral view explode(split(urlfields['code'],',')) adtable as coupon_code 
    where dt=$1;"
  • [x] UDAF(user defined aggregation function)
  • 用户自定义聚合函数针对记录集合,输入多行输出一行的须要
自定义函数开发流程(UDTF和UDAF)
  1. 自定义一个Java类
  2. 继承UDF类
  3. 重写evaluate方法
  4. 打成jar包
  5. 在hive执行add jar方法
  6. 在hive执行创立模板函数
  7. hql中使用
    案例:

hive --hiveconf mapreduce.job.queuename=root.offline.hdp_daojia.normal -hiveconf hive.exec.parallel=true -v -e "
add jar hdfs://hdp-58-cluster/home/hdp_58dp/udf/udf-utils.jar;
create temporary function result_update as 'com.dj58.data.hive.udf.ResultsUpdate';
create temporary function spend_time as 'com.dj58.data.hive.udaf.SpendTime';
use hdp_daojia_defaultdb;
--$1 优惠券活动下单明细统计
select result_update('app_coupon/a_app_coupon_order.properties',
    $1,hmsr,znsr,cateid,order_users,orders,finished_order_users,finished_orders,finished_order_price_sum,finished_corder_actual_price_sum)
from(
    select (case when t2.hmsr!='' then t2.hmsr else 'none' end) hmsr,--渠道
            (case when t2.znsr!='' then t2.znsr else 'none' end) znsr,--站内渠道
            t1.cateid,--业务类型
            count(distinct(case when t1.createtime='$2' and t2.operation_status = 'ordersuccess' then t1.uid end)) order_users,--渠道下单用户数
            count(distinct(case when t1.createtime='$2' and t2.operation_status = 'ordersuccess' then t2.order_id end)) orders,--渠道下单数
            count(distinct(case when t1.servicetime='$2' and t1.bistate in(3,8) then t1.uid end)) finished_order_users,--渠道完成订单用户数
            count(distinct(case when t1.servicetime='$2' and t1.bistate in(3,8) then t1.orderid end)) finished_orders,--渠道完成订单数
            sum((case when t1.bistate in(3,8) and t1.price is not null then t1.price else 0 end)) finished_order_price_sum, --渠道带来的完成订单的实收金额总和
            sum((case when t1.bistate in(3,8) and t1.actual_price is not null then t1.actual_price else 0 end))finished_corder_actual_price_sum--渠道带来的完成订单实际金额加和    
    from(
        select uid,orderid,bistate,price,actual_price,to_date(createtime) createtime,to_date(servicetime) servicetime,cateid
        from hdp_daojia_defaultdb.dj58_f_mysql_order
        where uid!='' and orderid!='' and to_date(createtime)='$2' or to_date(servicetime)='$2'
    )t1
    left outer join(
        select hmsr,znsr,order_id,operation_status,mobile
        from hdp_daojia_defaultdb.dj58_f_web_orderchannel
        where order_id !=''
    )t2
    on t1.orderid = t2.order_id
    group by t2.hmsr,t2.znsr,t1.cateid
    order by order_users desc
)t5;
"

(2)开源的Apache Hadoop UI系统Hue

HUE的拔取 B/S 代替C/S架构,不用反复登陆 hive/hbase等客户端
Hue是一个可火速支付和调试Hadoop生态系统种种应用的一个按照浏览器的图形化用户接口。官网给出的特征,通过翻译原文简单精晓一下Hue所协理的法力特色集合:

  1. 默许基于轻量级sqlite数据库管理会话数据,用户认证和授权,可以自定义为MySQL、Postgresql,以及Oracle
  2. 依照文件浏览器(File Browser)访问HDFS
  3. 据悉Hive编辑器来开发和周转Hive查询
  4. 支持基于Solr举行查找的利用,并提供可视化的数目视图,以及仪表板(Dashboard)
  5. 支持基于Impala的行使进行交互式查询

image

image

三、数据导出(sqoop)

hive导入mysql脚本

#下载HDFS文件到本地目录
hadoop fs -get $basedir/$hive_table/dt=$year-$month-$day/ $resultDir
#进入本地文件目录
cd $resultDir/dt=$year-$month-$day
pwd
#合并文件
cat ./* > data.txt
#exit
#加载数据至mysql数据库
mysql -u root -p123456 -h$server -e "use '$mysql_database';
delete from $mysql_table where data_dt='$year-$month-$day';
load data local infile '$resultDir/dt=$year-$month-$day/data.txt' into table $mysql_table FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';"

四、商业化hadoop平台CDH应用

hadoop是一个开源项目,所以重重铺面在那一个基础进行商业化,Cloudera对hadoop做了相应的改观

sqlite 5

image

五、作业调度系统

按照 林语堂ux 系统级其余 Crontab。

在 Job 数量庞大的意况下,Crontab
脚本的编辑,变得卓殊复杂。其调度的经过也不可以透明化,让管理变得困苦。

Java 应用级其余 Quartz。

Quartz 固然不要编写脚本,落成对应的调度 API
即可,然其调度进程不透明,不涵盖 Job 运行详情。需自行开发其效率。

其三方调度平台比较

Oozie

Oozie近来是托管在Apache基金会的,开源。配置的进程略显繁琐和错综复杂,配置相关的调度职责相比较费心,然其可视化界面也不是那么的直观,其它,对UI界面必要较高的同学,此调度种类臆度会让您失望。

Zeus

它是一个阿里巴巴(阿里巴巴(Alibaba))开源Hadoop的功课平台,从Hadoop职务的调试运行到生育任务的周期调度,它支持义务的全方位生命周期。从其效劳来看,它支持以下职责:

  1. Hadoop的MapReduce义务调度运行
  2. Hive职务的调度运行
  3. Shell职务的运转
  4. Hive元数据的可视化彰显查询及数码预览
  5. Hadoop职务的自动调度

Azkaban

Azkaban提供了一个简单使用的用户界面来保安和跟踪你的干活流程。其它,Github上进献的Azkaban调度种类的源码量不大,做二次开发难度不大。其成效点涉及以下内容:

  1. 易用的Web UI
  2. 简单的说的Web和Http工作流的上传
  3. 序列工作区
  4. 工作流调度
  5. 模块化和插件化
  6. 阐明和授权
  7. 用户作为跟踪
  8. 邮件告警失利和成功
  9. SLA告警
  10. 重启退步的乔布斯(Jobs)
Azkaban使用
  • [x] 主页面

https://localhost:8443
专注是https,拔取的是jetty
ssl链接。输入账号密码azkaban/azkanban(若是你以前从未改动的话)

image

首页有多少个菜单

  • projects:最根本的一部分,创造一个工程,所有flows将在工程中运行。
  • scheduling:显示定时职责
  • executing:突显当前运行的天职
  • history:彰显历史运行职分

主要介绍projects部分
先是创制一个工程,填写名称和讲述,比如o2olog。

image

Flows:工作流程,有五个job组成
Permissions:权限管理
Project Logs:工程日志

  1. 制造工程:

创立以前大家先精晓下里边的关联,一个工程包涵一个或三个flows,一个flow包罗多少个job。job是你想在azkaban中运行的一个进度,可以是概括的linux命令,可是java程序,也得以是错综复杂的shell脚本,当然,借使您安装相关插件,也可以运行插件。一个job可以信赖于另一个job,那种三个job和它们的依靠组成的图片叫做flow。
job创建

创制job很粗略,只要创制一个以.job结尾的文书文件就行了,例如我们创造一个办事,用来将日志数据导入hive中(关于大数量方面的事物,不在重复,可以知道为,将日志所需数据导入的mysql中),大家创立o2o_2_hive.job

type=command
command=echo "data 2 hive"

一个概括的job就创立好了,解释下,type的command,告诉azkaban用unix原生命令去运转,比如原生命令或者shell脚本,当然也有其它门类,前边说。

一个工程不容许唯有一个job,大家现在开立四个倚重job,那也是拔取azkaban的首要性目标。

2、Flows创建

大家说过五个jobs和它们的依靠组成flow。怎么开创看重,只要指定dependencies参数就行了。比如导入hive前,须要展开数据清洗,数据清洗前需要上传,上传以前需求从ftp获取日志。
定义5个job:

  • o2o_2_hive.job:将保洁完的数目入hive库
  • o2o_clean_data.job:调用mr清洗hdfs数据
  • o2o_up_2_hdfs.job:将文件上传至hdfs
  • o2o_get_file_ftp1.job:从ftp1获取日志
  • o2o_get_file_fip2.job:从ftp2获取日志
    借助于关系:
    3看重4和5,2看重3,1看重2,4和5平昔不器重关系。

o2o_2_hive.job

type=command
# 执行sh脚本,建议这样做,后期只需维护脚本就行了,azkaban定义工作流程
command=sh /job/o2o_2_hive.sh
dependencies=o2o_clean_data

o2o_clean_data.job

type=command
# 执行sh脚本,建议这样做,后期只需维护脚本就行了,azkaban定义工作流程
command=sh /job/o2o_clean_data.sh
dependencies=o2o_up_2_hdfs

o2o_up_2_hdfs.job

type=command
#需要配置好hadoop命令,建议编写到shell中,可以后期维护
command=hadoop fs -put /data/*
#多个依赖用逗号隔开
dependencies=o2o_get_file_ftp1,o2o_get_file_ftp2

o2o_get_file_ftp1.job

type=command
command=wget "ftp://file1" -O /data/file1

o2o_get_file_ftp2.job

type=command
command=wget "ftp:file2" -O /data/file2
  1. 将上述job打成zip包。上传:

image

image

点击o2o_2_hive进入流程,azkaban流程名称以最终一个从未依赖的job定义的。

image

右上方是布局执行当前流程如故举办定时流程。

image

Flow view:流程视图。可以禁用,启用某些job
Notification:定义义务成功如故战败是或不是发送邮件
Failure Options:定义一个job败北,剩下的job怎么执行
Concurrent:并行职责执行设置
Flow Parametters:参数设置。
1.实施一遍
安装好上述参数,点击execute。

image

肉色代表成功,紫色是运行,青色是败退。可以查阅job运行时刻,看重和日志,点击details可以查阅各种job运行景况。

image

2.定时进行

image

五、报表平台

sqlite 6

image

sqlite 7

image

sqlite 8

image

参考作品:

  1. HDFS长远浅析:http://www.cnblogs.com/linuxprobe/p/5594431.html

  2. Hive
    SQL编译进度:http://tech.meituan.com/hive-sql-to-mapreduce.html

  3. Hadoop 新 MapReduce 框架 Yarn
    详解:http://www.ibm.com/developerworks/cn/opensource/os-cn-hadoop-yarn/

  4. Sqoop安装配置及示范:http://www.micmiu.com/bigdata/sqoop/sqoop-setup-and-demo/

  5. Hue安装配置实践:http://shiyanjun.cn/archives/1002.html

  6. Hadoop Hive
    sql语法详解:http://blog.csdn.net/hguisu/article/details/7256833

  7. Azkaban-开源职分调度程序(安装篇):http://www.jianshu.com/p/cc680380ca34

  8. Azkaban-开源职务调度程序(使用篇):http://www.jianshu.com/p/484564beda1d

  9. Hive高级查询(group by、 order by、 join等)
    http://blog.csdn.net/scgaliguodong123\_/article/details/46944519

网站地图xml地图