数据仓库

数据仓库部分

前言

（hive优化，hive数据倾斜的原因及优化，hive内置函数的使用，hive开窗函数，hive基本练习题，
sqoop导入hive，hbase，mysql，及分段导入，全表导入，sql导入，导入原理，ETL概念，维度概念，建模概念，    雪花型和星型区别，数据仓库架构，hbase rowkey设计原则，hbase如何使用）

hive优化

1.表连接优化

 (1)大表放在后面
 
 (2)使用相同的连接健(join连接时,使用的on字句,连接健如果一样,只会产生一个mrjob)
 
 (3)尽量尽早的过滤掉数据
 
 (4)尽量原子化操作(中间表来完成复杂的逻辑)

2.用insert into 替换 union all
3.order by & sort by

  order by:对查询结果进行全局排序,消耗时间长,需要set hive.mapred.mode=nostrict
  
  sort by:局部排序,并非全局有序,提高效率

4.limit语句快速出结果

  hive.limit.optimize.enable=true --- 开启对数据源进行采样的功能
  
  hive.limit.row.max.size --- 设置最小的采样容量
  
  hive.limit.optimize.limit.file --- 设置最大的采样样本数
  
  缺点：有可能部分数据永远不会被处理到	

5.本地模式

  设置属性hive.exec.mode.local.auto的值为true

6.并行执行

  hive会将一个查询转化为一个或多个阶段，包括：MapReduce阶段、抽样阶段、合并阶段、limit阶段等。默认情况下，一次只执行一个阶段。 不过，如果某些阶段不是互相依赖，是可以并行执行的。
  
  set hive.exec.parallel=true,可以开启并发执行。
  
  set hive.exec.parallel.thread.number=16; //同一个sql允许最大并行度，默认为8。
  会比较耗系统资源。

7.调整mapper和reducer的个数

  (1)减少map数
  
  (2)reducer阶段优化
	set mapred.reduce.tasks=?
	set hive.exec.reducers.bytes.per.reducer = ?

8.严格模式

  set hive.marped.mode=strict -----防止用户执行那些意想不到的不好的影响的查询
  
  --分区表,必须选定分区范围
  
  --对于使用order by的查询,要求必须使用limit语句,因为order by为了执行排序过程会将所有
  的结果数据分发到同一个reducer中进行处理
  
  --限制笛卡尔积查询:两张表join必须有on语句

9.数据倾斜:

  (1)key分布不均匀
  (2)业务数据本身的特性
  (3)建表时考虑不周
  (4)某些SQL语句本身就有数据倾斜
  
  关键词					情形											后果
	join			其中一个表较小，但是key集中						分发到某一个或几个Reduce上的数据远高于平均值
	join			大表与大表，但是分桶的判断字段0值或空值过多		这些空值都由一个reduce处理，灰常慢
	group by		group by 维度过小，某值的数量过多				处理某值的reduce灰常耗时
	count distinct	某特殊值过多									处理此特殊值reduce耗时
hive.map.aggr=true

hive数据倾斜的原因及优化:

原因:

(1)key分布不均匀
(2)业务数据本身的特性
(3)建表时考虑不周
(4)某些SQL语句本身就有数据倾斜

优化:

(1)参数调节:

      hive.map.aggr = true
	  Map端部分聚合,相当于Combiner
	  hive.groupby.skewindata=true
	  保证相同的Group by key被分布到同一个reduce

(2)SQL语句调节

如何join

		关于驱动表的取,用join key分布到最均匀的表作为驱动表
		做好列裁剪和filter操作,以达到两表做join的时候,数据量相对变小的结果

大小表join:

		使用mapjoin让小的维度表(1000条以下的记录条数)先进行内存,在map端完成reduce
		但是如果一个RDD是比较小的，则可以采用广播小RDD全量数据+map算子来实现与join同样的效果，
		也就是map join，

大表join大表:

		其中某一个RDD/Hive表中的少数key的数据量过大,而另一个RDD/Hive表中的所有Key都分布比较均匀
		(1)倾斜的key的那个RDD,通过sample算子采样出一份样本,统计出每个key的数量,计算出数据量最大的是哪几个key
		(2)然后将这几个key对应的数据从原来的RDD中拆分出来，形成一个单独的RDD，并给每个key都打上n以内的随机数作为前缀，而
		   不会导致倾斜的大部分key形成另外一个RDD
		(3)接着将需要join的另一个RDD，也过滤出来那几个倾斜key对应的数据并形成一个单独的RDD，将每条数据膨胀成n条数据，这n
		   条数据都按顺序附加一个0~n的前缀，不会导致倾斜的大部分key也形成另外一个RDD。
		(4)再将附加了随机前缀的独立RDD与另一个膨胀n倍的独立RDD进行join，此时就可以将原先相同的key打散成n份，分散到多个
		   task中去进行join
		(5)而另外两个普通的RDD就照常join即可。
		(6)最后将两次join的结果使用union算子合并起来即可，就是最终的join结果

hive内置函数的使用:

(1)rand(), rand(int seed)	它返回一个随机数，从行改变到行。
(2)concat(string A, string B,...)	它返回从A后串联B产生的字符串
(3)substr(string A, int start, int length)	返回从给定长度的起始start位置开始的字符串
(4)upper(string A)	它返回从转换的所有字符为大写产生的字符串。
(5)ucase(string A)	和上面的一样
(6)lower(string A)	它返回转换B的所有字符为小写产生的字符串。
(7)lcase(string A)	和上面的一样
(8)trim(string A)	去掉两边空格
(9)ltrim(string A)	去掉左边空格
(10)rtrim(string A)	去掉右边空格
(11)from_unixtime(int unixtime)   转换为时间戳
(12)to_date(string timestamp)	返回一个字符串时间戳的日期部分：to_date("1970-01-01 00:00:00") = "1970-01-01"
(13)year(string date)	返回年份部分的日期或时间戳字符串：year("1970-01-01 00:00:00") = 1970, year("1970-01-01") = 1970
(14)month(string date)	返回日期或时间戳记字符串月份部分：month("1970-11-01 00:00:00") = 11, month("1970-11-01") = 11
(15)day(string date)	返回日期或时间戳记字符串当天部分：day("1970-11-01 00:00:00") = 1, day("1970-11-01") = 1
(16)get_json_object(string path,string json_string)	提取从基于指定的JSON路径的JSON字符串JSON对象，并返回提取的JSON字符串的JSON对象。如果输入的JSON字符串无效，返回NULL
(17)json_tuple函数 
	当使用json_tuple对象时，可以显著提高效率，一次获取多个对象并且可以被组合使用，写法如下：
	select a.* from test lateral view json_tuple(‘${hivevar:msg}’,’server’,’host’) a as f1,f2;
(18)列转行:
	select student, '语文' COURSE , chinese as  SCORE  from stu
	union select student, '数学' COURSE, maths as SCORE from stu
	union select student, '英语' COURSE, english as SCORE from stu
(19)行转列:
	SELECT user_name ,
	MAX(CASE course WHEN '数学' THEN score ELSE 0 END ) 数学,
	MAX(CASE course WHEN '语文' THEN score ELSE 0 END ) 语文,
	MAX(CASE course WHEN '英语' THEN score ELSE 0 END ) 英语
	ROM TEST_TB_GRADE
	GROUP BY USER_NAME;
	order by student,COURSE;

聚合函数:

count(*), count(expr),	count(*) - 返回检索行的总数。

sum(col), sum(DISTINCT col)	返回该组或该组中的列的不同值的分组和所有元素的总和。

avg(col), avg(DISTINCT col)	返回上述组或该组中的列的不同值的元素的平均值。

min(col)	返回该组中的列的最小值。

max(col)	返回该组中的列的最大值。

hive开窗函数:

1.基础结构:分析函数(如sum(),max(0,row_number()....) + 窗口字句(over函数)
2.over函数写法:
	over(partition by cookieid order by createtime),先根据cookied字段分区,相同的cookied分为一区,
	每个分区内根据 createtime字段排序(默认升序)
	不加 partition by 的话则把整个数据集当作一个分区，不加 order by的话会对某些函数统计结果产生影响，如    sum()
(1)SUM()函数:
      select cookieid,createtime,pv,
      sum(pv) over(PARTITION BY cookieid ORDER BY createtime) as pv1 
      FROM test1
(2)NTILE函数
      NTILE(n)，用于将分组数据按照顺序切分成n片，返回当前切片值
      NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS ntile1, --分组内将数据分成2片
      NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS ntile2,  --分组内将数据分成3片
      NTILE(4) OVER(PARTITION BY cookieid ORDER BY createtime) AS ntile3   --将所有数据分成4片
用法举例： 
      统计一个cookie，pv数最多的前1/3的天：
      SELECT cookieid,createtime,pv,
      NTILE(3) OVER(PARTITION BY cookieid ORDER BY pv DESC) AS ntile 
      FROM test1;
      取ntile = 1 的记录，就是我们想要的结果！
(3)ROW_NUMBER函数
	  ROW_NUMBER() 从1开始，按照顺序，生成分组内记录的序列
	  ROW_NUMBER() 的应用场景非常多，比如获取分组内排序第一的记录、获取一个session中的第一条refer等。
	  SELECT cookieid,createtime,pv,
	  ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn  
	  FROM test1;
(4)RANK和DENSE_RANK函数
      RANK()生成数据项在分组中的排名,排名相等会在名次中留下空位,3,3,5
      DENSE_RANK()生成数据项在分组中的排名,排名相等会在名次中不会留下空位3,3,4
      SELECT cookieid,createtime,pv,
      RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rank1,
      DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS d_rank2,
      ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 
      FROM test1 
(5)CUME_DIST函数
	  cume_dist 返回小于等于当前值的行数/分组内总行数

sqoop导入hive，hbase，mysql:

1.从hive导入mysql

sqoop export --connect jdbc:mysql://hadoop01:3306/result \
	--username root --password root \
	--table stats_event --export-dir /hive/stats_event/* \
	--input-fields-terminated-by "\\01" --update-mode allowinsert \
	--update-key date_dimension_id,
	--columns 列名

2.Sqoop 提供了将三步合为一步

Sqoop import --connect jdbc:mysql://master:3306/hive 
	--tabel DBS 
	--username root
	-m 1
	--hive-import

3.导入hbase

	sqoop import --connect jdbc:mysql://192.168.220.20:3306/test 
	--username root 
	--password 123456 
	--table smq_to_hbase 
	--hbase-table smq_hbase 
	--column-family info --hbase-row-key id

4.部分导入

sqoop import --connect jdbc:mysql://hadoop01:3306/userdb  \
	--username root --password mysql \
	--table 表名 \
	--splite-by id \
	--where 'id>1203' \
	--target-dir hdfs://hadoop01:9000/sqoopdata/00;

5.导入的数据只需要部分字段：

sqoop import --connect jdbc:mysql://hadoop01:3306/userdb  \
	--username root --password mysql \
	--table 表名 \
	--splite-by id \
	--columns id,name,deg \
	--where 'id>1203' \
	--target-dir hdfs://hadoop01:9000/sqoopdata/00;

6.导入数据由用户的查询语句指定

sqoop import --connect jdbc:mysql://hadoop01:3306/userdb  \
	--username root --password mysql \
	--splite-by id \
	--query 'select id,name,dept from 表名 where id < 1203'and $CONDITIONS' \
	--target-dir hdfs://hadoop01:9000/sqoopdata/00;


Sqoop eval --connect jdbc:mysql://master:3306/hive --username root --query “select * from DBS”
Sqoop eval --connect jdbc:mysql://master:3306/hive --username root --password root -e “select * from DBS”

ETL概念，维度概念，建模概念:

ETL，Extraction-Transformation-Loading的缩写，即数据抽取（Extract）、转换（Transform）、
装载（Load）的过程	

雪花型和星型区别:

概念:

星型架构是一种非正规化的结构，多维数据集的每一个维度都直接与事实表相连接，不存在渐变维度，
所以数据有一定的冗余，当有一个或多个维表没有直接连接到事实表上，而是通过其他维表连接到事实表上时
，其图解就像多个雪花连接在一起，故称雪花模型

雪花模型使用的是规范化数据，也就是说数据在数据库内部是组织好的，以便消除冗余，因此它能够有效地减少数据量。

通过引用完整性，其业务层级和维度都将存储在数据模型之中。

雪花模型加载数据集市，因此ETL操作在设计上更加复杂，而且由于附属模型的限制，不能并行化。

星形模型加载维度表，不需要再维度之间添加附属模型，因此ETL就相对简单，而且可以实现高度的并行化。

总结:

雪花模型使得维度分析更加容易，比如“针对特定的广告主，有哪些客户或者公司是在线的?”
星形模型用来做指标分析更适合，比如“给定的一个客户他们的收入是多少?”

数据仓库架构:

特点:

面向主题,集成的,随时间变化的,信息本身相对稳定

分层:

数据应用层 : 报表   接口    数据可视化   即席查询
	数据共享层 : RDBMS  Redis     kylin HBase
	数据存储与分析层 : 
			离线分析                                 实时计算
			 Hive SparkSQL  SparkMLlib               SparkStreaming   SparkMLlib
			  HDFS                                      Kafka
				flume                       sqoop
				
	数据采集层 :   日志  RDBMS  HTTP/FTP   其他

数据采集:

数据采集层的任务就是把数据从各种数据源中采集和存储到数据存储上，期间有可能会做一些ETL操作

数据存储与分析:

HDFS是大数据环境下数据仓库/数据平台最完美的数据存储解决方案

离线数据分析与计算，也就是对实时性要求不高的部分，Hive是不错的选择

使用Hadoop框架自然而然也提供了MapReduce接口，如果真的很乐意开发Java，或者对SQL不熟，那么也可以使用MapReduce来做分析与计算

Spark性能比MapReduce好很多，同时使用SparkSQL操作Hive

数据共享:

指的是前面数据分析与计算后的结果存放的地方，其实就是关系型数据库和NOSQL数据库	

数据应用:

报表：报表所使用的数据，一般也是已经统计汇总好的，存放于数据共享层。

接口：接口的数据都是直接查询数据共享层即可得到。

即席查询：即席查询通常是现有的报表和数据共享层的数据并不能满足需求，需要从数据存储层直接查询。一般都是通过直接操作SQL得到

数据仓库设计步骤:

  (1)确定主题
  (2)确定量度
  (3)确定数据粒度
  (4)确定维度
  (5)创建事实表

hbase rowkey设计原则，hbase如何使用:

rowkey设计原则:

(1)长度原则

(2)	散列原则:如果Rowkey是按时间戳的方式递增，不要将时间放在二进制码的前面，
    建议将Rowkey的高位作为散列字段，由程序循环生成，低位放时间字段，这样将提高数据均衡分布在每个   Regionserver实现负载均衡的几率。
    如果没有散列字段，首字段直接是时间信息将产生所有新数据都在一个 RegionServer上堆积的热点现象 这样在做数据检索的时候负载将会集中在个别RegionServer，降低查询效率。
    
(3)唯一原则:必须在设计上保证其唯一性。

HBase中rowkey可以唯一标识一行记录，在HBase查询的时候，有两种方式：

1、通过get方式，指定rowkey获取唯一一条记录 

2、通过scan方式，设置startRow和stopRow参数进行范围匹配 

3、全表扫描，即直接扫描整张表中所有行记录

什么是热点：

HBase中的行是按照rowkey的字典顺序排序的，这种设计优化了scan操作，可以将相关的行以及会被一起读取的行存取在临近位置，

便于scan。然而糟糕的rowkey设计是热点的源头。热点发生在大量的client直接访问集群的一个或极少数个节点（访问可能是读，

写或者其他操作）大量访问会使热点region所在的单个机器超出自身承受能力，引起性能下降甚至region不可用，

这也会影响同一个RegionServer上的其他region，由于主机无法服务其他region的请求。设计良好的数据访问模式以     使集群被充分，均衡的利用。

为了避免写热点，设计rowkey使得不同行在同一个region，但是在更多数据情况下，数据应该被写入集群的多个region，而不是一个

避免热点的方法以及它们的优缺点：
加盐:

在rowkey的前面增加随机数，具体就是给rowkey分配一个随机前缀以使得它和之前的rowkey的开头不同。

分配的前缀种类数量应该和你想使用数据分散到不同的region的数量一致。加盐之后的rowkey就会根据随机生

成的前缀分散到各个region上，以避免热点

哈希:

哈希会使同一行永远用一个前缀加盐。哈希也可以使负载分散到整个集群，但是读却是可以预测的。

使用确定的哈希可以让客户端重构完整的rowkey，

可以使用get操作准确获取某一个行数据

反转:

第三种防止热点的方法时反转固定长度或者数字格式的rowkey。这样可以使得rowkey中经常改变的部分（最没有意义的部分）放在前面。	

这样可以有效的随机rowkey，但是牺牲了rowkey的有序性。

反转rowkey的例子 

以手机号为rowkey，可以将手机号反转后的字符串作为rowkey，这样的就避免了以手机号那样比较固定开头导致热点问题

时间戳反转:

这个我看不懂??????????????????????????????????????

hive存储格式:

1.textfile

textfile为默认格式

存储方式：行存储

磁盘开销大 数据解析开销大

压缩的text文件 hive无法进行合并和拆分

2.sequencefile

二进制文件,以<key,value>的形式序列化到文件中

存储方式：行存储

可分割 压缩

一般选择block压缩

优势是文件和Hadoop api中的mapfile是相互兼容的。

3.rcfile

存储方式：数据按行分块 每块按照列存储

压缩快 快速列存取

读记录尽量涉及到的block最少

读取需要的列只需要读取每个row group 的头部定义。

读取全量数据的操作 性能可能比sequencefile没有明显的优势

oozie

Oozie Workflow：该组件用于定义和执行一个特定顺序的mapreduce，hive和pig作业。

Oozie Coordinator：该组件用于支持基于事件，系统资源存在性等条件的workflow的自动化执行。

Oozie Bundle：该引擎可以定义和执行“一束”应用，从而提供一个批量化的方法。将一组Coordinator应用程序一起进行管理。