学习总结——hive高级

冷不防 2024-04-18 13:20 59阅读 0赞

## json数据的解析 ##

### 小结 ###

基本json和字符串数组（重点）

*  json数据的解析
    
     *  内置函数
        
         *  get\_json\_object 一次可以解析出一个字段，通过$.字段名将值取出来
         *  json\_tuple 可以解析出多个字段，可以直接通过字段名取出该字段的值
         *  扩展：如果是json数组呢？
            
             *  思路：想法把json数组转换成为array 交给explode来炸开 结合 json\_tuple 来使用
            
                regexp_replace('json数组', '\\}\\,\\{','\\}\\;\\{')   --把json数组中的分隔符替换成为;
                regexp_replace('asdas','[|]','')   --  |表示or   把json中的[或者]替换成为空

### 如何在 Apache Hive 中解析 Json 数组 ###

Hive 内部提供了大量的内置函数用于处理各种类型的需求。从内置的 UDF 可以看到两个用于解析 Json 的函数：**get\_json\_object** 和 **json\_tuple**。用过这两个函数的同学肯定知道，其只能解析最普通的 Json 字符串，如下：

SELECT get_json_object('{"website":"www.itcast.cn","name":"allenwoon"}', '$.website');
    
    SELECT json_tuple('{"website":"www.itcast.cn","name":"allenwoon"}', 'website', 'name');
    
    --json_tuple 相对于 get_json_object 的优势就是一次可以解析多个 Json 字段。

但是如果我们有个 Json 数组，这两个函数都无法处理，get\_json\_object 处理 Json 数组的功能很有限

SELECT get_json_object('[{"website":"www.itcast.cn","name":"allenwoon"}, {"website":"cloud.itcast.com","name":"carbondata 中文文档"}]', '$.[1].website');

如果我们想将整个 Json 数组里面的 website 字段都解析出来，如果这么写将非常麻烦，因为我们**无法确定数组的长度**，而且即使确定了，这么写可维护性也很差，所以我们需要想别的办法。

--------------------

### 使用explode函数解析json数组 ###

explode() 接收一个 array 或 map 类型的数据作为输入，然后将 array 或 map 里面的元素按照每行的形式输出。其可以配合 LATERAL VIEW 一起使用。

select json_tuple(json, 'website', 'name') from (SELECT explode(split(regexp_replace(regexp_replace('[{"website":"www.itcast.cn","name":"allenwoon"},{"website":"cloud.itcast.com","name":"carbondata 中文文档"}]', '\\}\\,\\{','\\}\\;\\{'),'\\[|\\]',''),'\\;')) as json) itcast;

SELECT explode(split(regexp_replace(regexp_replace('[{"website":"www.itcast.cn","name":"allenwoon"},{"website":"cloud.itcast.com","name":"carbondata 中文文档"}]', '\\}\\,\\{','\\}\\;\\{'),'\\[|\\]',''),'\\;')) as json
    
    
    
    regexp_replace('json数组', '\\}\\,\\{','\\}\\;\\{')   --把json数组中的分隔符替换成为;
    regexp_replace('asdas','[|]','')   -- |表示or 把json中的[或者]替换成为空

sql语法解释：linux中，两个反斜杠表示转义，即\\

explode 函数只能接收数组或 map 类型的数据，而 split 函数生成的结果就是数组；
    
    第一个 regexp_replace 的作用是将 Json 数组元素之间的逗号换成分号，，
    regexp_replace('[{"website":"www.itcast.cn","name":"allenwoon"},{"website":"cloud.itcast.com","name":"carbondata 中文文档"}]', '\\}\\,\\{','\\}\\;\\{')
    
    
    第二个 regexp_replace 的作用是将 Json 数组两边的中括号去掉，所以使用完这个函数之后
    regexp_replace(regexp_replace('[{"website":"www.itcast.cn","name":"allenwoon"},{"website":"cloud.itcast.com","name":"carbondata 中文文档"}]', '\\}\\,\\{','\\}\\;\\{'),'\\[|\\]','')

## HIve窗口函数 ##

*  窗口函数（OLAP函数/分析函数）
    
    既显示聚集前的数据,又要显示聚集后的数据，窗口函数兼具分组和排序的功能
    
    最重要的关键字是partition by 和order by
 *  格式：带有over语句的函数
    
        over (partition by xxx order by asc|desc)
        
        partition by 用于指定根据表中哪个字段进行分，相同的分在一起
        order by 用于指定相同分区内根据谁进行排序。默认升序
 *  如果不指定rows between,默认为从起点到当前行;
    
     *  如果不指定order by，则将分组内所有值累加;
     *  关键是理解rows between含义,也叫做window子句：
        
         *  preceding：往前
         *  following：往后
         *  current row：当前行
         *  unbounded：起点
         *  unbounded preceding 表示从前面的起点
         *  unbounded following：表示到后面的终点
 *  数据准备
    
        建表语句:
        create table itcast_t1(
        cookieid string,
        createtime string,   --day 
        pv int
        ) row format delimited 
        fields terminated by ',';
        
        加载数据：
        load data local inpath '/root/hivedata/itcast_t1.dat' into table itcast_t1;
        
        cookie1,2018-04-10,1
        cookie1,2018-04-11,5
        cookie1,2018-04-12,7
        cookie1,2018-04-13,3
        cookie1,2018-04-14,2
        cookie1,2018-04-15,4
        cookie1,2018-04-16,4
        
        开启智能本地模式
        SET hive.exec.mode.local.auto=true;

--------------------

*  SUM（结果和ORDER BY相关,默认为升序）
    
        select cookieid,createtime,pv,
        sum(pv) over(partition by cookieid order by createtime) as pv1 
        from itcast_t1;
        
        select cookieid,createtime,pv,
        sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
        from itcast_t1;
        
        select cookieid,createtime,pv,
        sum(pv) over(partition by cookieid) as pv3
        from itcast_t1;
        
        select cookieid,createtime,pv,
        sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4
        from itcast_t1;
        
        select cookieid,createtime,pv,
        sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5
        from itcast_t1;
        
        select cookieid,createtime,pv,
        sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6
        from itcast_t1;
        
        
        pv1: 分组内从起点到当前行的pv累积，如，11号的pv1=10号的pv+11号的pv, 12号=10号+11号+12号
        pv2: 同pv1
        pv3: 分组内(cookie1)所有的pv累加
        pv4: 分组内当前行+往前3行，如，11号=10号+11号， 12号=10号+11号+12号，
        	                       13号=10号+11号+12号+13号， 14号=11号+12号+13号+14号
        pv5: 分组内当前行+往前3行+往后1行，如，14号=11号+12号+13号+14号+15号=5+7+3+2+4=21
        pv6: 分组内当前行+往后所有行，如，13号=13号+14号+15号+16号=3+2+4+4=13，
        							 14号=14号+15号+16号=2+4+4=10
 *  AVG，MIN，MAX，和SUM用法一样
    
        select cookieid,createtime,pv,
        avg(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
        from itcast_t1;
        
        select cookieid,createtime,pv,
        max(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
        from itcast_t1;
        
        select cookieid,createtime,pv,
        min(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv2
        from itcast_t1;

### NTILE,ROW\_NUMBER,RANK,DENSE\_RANK ###

*  数据准备
    
        cookie1,2018-04-10,1
        cookie1,2018-04-11,5
        cookie1,2018-04-12,7
        cookie1,2018-04-13,3
        cookie1,2018-04-14,2
        cookie1,2018-04-15,4
        cookie1,2018-04-16,4
        cookie2,2018-04-10,2
        cookie2,2018-04-11,3
        cookie2,2018-04-12,5
        cookie2,2018-04-13,6
        cookie2,2018-04-14,3
        cookie2,2018-04-15,9
        cookie2,2018-04-16,7
         
        CREATE TABLE itcast_t2 (
        cookieid string,
        createtime string,   --day 
        pv INT
        ) ROW FORMAT DELIMITED 
        FIELDS TERMINATED BY ',' 
        stored as textfile;
          
        加载数据：
        load data local inpath '/root/hivedata/itcast_t2.dat' into table itcast_t2;

--------------------

*  NTILE
    
    背景：
    
        有时会有这样的需求:如果数据排序后分为三部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢?NTILE函数即可以满足。
    
        ntile可以看成是：把有序的数据集合平均分配到指定的数量（num）个桶中, 将桶号分配给每一行。
        如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1。
        语法是：ntile (num)  over ([partition_clause]  order_by_clause)  as xxx
        然后可以根据桶号，选取前或后 n分之几的数据。
        数据会完整展示出来，只是给相应的数据打标签；具体要取几分之几的数据，需要再嵌套一层根据标签取出。
        NTILE不支持ROWS BETWEEN，比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)
    
        SELECT 
        cookieid,
        createtime,
        pv,
        NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn1,
        NTILE(3) OVER(PARTITION BY cookieid ORDER BY createtime) AS rn2,
        NTILE(4) OVER(ORDER BY createtime) AS rn3
        FROM itcast_t2 
        ORDER BY cookieid,createtime;
    
    比如，统计一个cookie，pv数最多的前1/3的天
    
        SELECT 
        cookieid,
        createtime,
        pv,
        NTILE(3) OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn 
        FROM itcast_t2;
         
        其中rn = 1 的记录，就是我们想要的结果

--------------------

*  ROW\_NUMBER
    
    ROW\_NUMBER() 从1开始，按照顺序，生成分组内记录的序列
    
        SELECT 
        cookieid,
        createtime,
        pv,
        ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn 
        FROM itcast_t2;
 *  RANK 和 DENSE\_RANK
    
    RANK() 生成数据项在分组中的排名，排名相等会在名次中留下空位  
    DENSE\_RANK() 生成数据项在分组中的排名，排名相等会在名次中不会留下空位—正常思维

RANK 考虑数据的重复性，重复的数据会挤占后续的标号

DENSE\_RANK。。。

SELECT 
    cookieid,
    createtime,
    pv,
    RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn1,
    DENSE_RANK() OVER(PARTITION BY cookieid ORDER BY pv desc) AS rn2,
    ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY pv DESC) AS rn3 
    FROM itcast_t2 
    WHERE cookieid = 'cookie1';
    
    select cookieid,
    createtime,
    pv,
    rank() over(partition by cookieid order by pv desc) as rn2, //1 2 3 3 5
    row_number() over(partition by cookieid order by pv desc) as rn3,
    dense_rank() over(partition by cookieid order by pv desc) as rn4 from itcast_t2;//1 2 3 3 4 5

### LAG,LEAD,FIRST\_VALUE,LAST\_VALUE ###

**注意： 这几个函数不支持WINDOW子句**

*  准备数据
    
        cookie1,2018-04-10 10:00:02,url2
        cookie1,2018-04-10 10:00:00,url1
        cookie1,2018-04-10 10:03:04,1url3
        cookie1,2018-04-10 10:50:05,url6
        cookie1,2018-04-10 11:00:00,url7
        cookie1,2018-04-10 10:10:00,url4
        cookie1,2018-04-10 10:50:01,url5
        cookie2,2018-04-10 10:00:02,url22
        cookie2,2018-04-10 10:00:00,url11
        cookie2,2018-04-10 10:03:04,1url33
        cookie2,2018-04-10 10:50:05,url66
        cookie2,2018-04-10 11:00:00,url77
        cookie2,2018-04-10 10:10:00,url44
        cookie2,2018-04-10 10:50:01,url55
         
        CREATE TABLE itcast_t4 (
        cookieid string,
        createtime string,  --页面访问时间
        url STRING       --被访问页面
        ) ROW FORMAT DELIMITED 
        FIELDS TERMINATED BY ',' 
        stored as textfile;
        
        加载数据：
        load data local inpath '/root/hivedata/itcast_t4.dat' into table itcast_t4;

--------------------

*  LAG
    
    **LAG(col,n,DEFAULT) 用于统计窗口内往上第n行值**  
    第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
    
        SELECT cookieid,
        createtime,
        url,
        ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
        LAG(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS last_1_time,
        LAG(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS last_2_time 
        FROM itcast_t4;
        
        
        last_1_time: 指定了往上第1行的值，default为'1970-01-01 00:00:00'  
                     			 cookie1第一行，往上1行为NULL,因此取默认值 1970-01-01 00:00:00
                     			 cookie1第三行，往上1行值为第二行值，2015-04-10 10:00:02
                     			 cookie1第六行，往上1行值为第五行值，2015-04-10 10:50:01
        last_2_time: 指定了往上第2行的值，为指定默认值
        						 cookie1第一行，往上2行为NULL
        						 cookie1第二行，往上2行为NULL
        						 cookie1第四行，往上2行为第二行值，2015-04-10 10:00:02
        						 cookie1第七行，往上2行为第五行值，2015-04-10 10:50:01
 *  LEAD
    
    与LAG相反  
    **LEAD(col,n,DEFAULT) 用于统计窗口内往下第n行值**  
    第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
    
        SELECT cookieid,
        createtime,
        url,
        ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
        LEAD(createtime,1,'1970-01-01 00:00:00') OVER(PARTITION BY cookieid ORDER BY createtime) AS next_1_time,
        LEAD(createtime,2) OVER(PARTITION BY cookieid ORDER BY createtime) AS next_2_time 
        FROM itcast_t4;
 *  FIRST\_VALUE
    
    取分组内排序后，截止到当前行，第一个值
    
        SELECT cookieid,
        createtime,
        url,
        ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
        FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS first1 
        FROM itcast_t4;
 *  LAST\_VALUE
    
    取分组内排序后，截止到当前行，最后一个值
    
        SELECT cookieid,
        createtime,
        url,
        ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
        LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1 
        FROM itcast_t4;
    
    如果想要取分组内排序后最后一个值，则需要变通一下：
    
        SELECT cookieid,
        createtime,
        url,
        ROW_NUMBER() OVER(PARTITION BY cookieid ORDER BY createtime) AS rn,
        LAST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime) AS last1,
        FIRST_VALUE(url) OVER(PARTITION BY cookieid ORDER BY createtime DESC) AS last2 
        FROM itcast_t4 
        ORDER BY cookieid,createtime;
    
    **特别注意order by**
    
    如果不指定ORDER BY，则进行排序混乱，会出现错误的结果

SELECT cookieid,
      createtime,
      url,
      FIRST_VALUE(url) OVER(PARTITION BY cookieid) AS first2  
      FROM itcast_t4;

一二四重要

w3c练习sql 高级教程 -----sql很重要

## hive的数据压缩 ##

*  压缩的优缺点
    
     *  优点：减少带宽 减少磁盘空间
     *  缺点：浪费时间 增加cpu处理压力
 *  压缩方式
    
     *  hive中间结果压缩（map输出的压缩）
     *  hive的最终结果压缩（reduce输出的压缩）
 *  压缩算法选择
    
     *  推荐：snappy
 *  例子：
    
        --没有开启压缩
        create table student_no_compress(Sno int,Sname string,Sex string,Sage int,Sdept string) row format delimited fields terminated by ',';
        
        insert into table student_no_compress select * from student;
        
        
        --开启压缩
        create table student_compress(Sno int,Sname string,Sex string,Sage int,Sdept string) row format delimited fields terminated by ',';
        
        --首先开启map压缩功能
        0: jdbc:hive2://node-1:10000> set hive.exec.compress.intermediate=true;
        No rows affected (0.004 seconds)
        0: jdbc:hive2://node-1:10000> set mapreduce.map.output.compress=true;
        No rows affected (0.004 seconds)
        0: jdbc:hive2://node-1:10000> set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
        
        --然后开启reduce压缩功能
        0: jdbc:hive2://node-1:10000> set hive.exec.compress.output=true;
        No rows affected (0.006 seconds)
        0: jdbc:hive2://node-1:10000> set mapreduce.output.fileoutputformat.compress=true;
        No rows affected (0.005 seconds)
        0: jdbc:hive2://node-1:10000> set mapreduce.output.fileoutputformat.compress.codec =org.apache.hadoop.io.compress.SnappyCodec;
        No rows affected (0.003 seconds)
        0: jdbc:hive2://node-1:10000> set mapreduce.output.fileoutputformat.compress.type=BLOCK;
        
        insert into table student_compress select * from student;
 *  注意：snappy压缩的文件 属于二进制格式数据 不可以直接观察 hive在操作的时候可以自解析。

--------------------

###  ###

## hive数据存储格式 ##

### 行存储和列存储 ###

#### hive存储格式 ####

##### 行式存储 #####

一行一行保存数据，有利于进行插入和更新操作

##### 列式存储 #####

一列一列存储数据，查询时，只有涉及到的列才会被查询，有利于查询操作，不利于插入和更新操作，不利于扫描小量的数据

##### hive支持的存储格式 #####

textfile(行式存储)，orc(列 式存储)，parquet(列式存储)

*  textfile
    
    默认存储格式，数据不做压缩，hive不会对数据进行切分
 *  **orc(常用)**
    
    它并不是一个单纯的列式存储格式，仍然是首先根据行组分割整个表，在每一个行组内进行按列存储。
    
    > 优点如下：
    > 
    > ORC是列式存储，有多种文件压缩方式，并且有着很高的压缩比。
    > 
    > 文件是可切分（Split）的。因此，在Hive中使用ORC作为表的文件存储格式，不仅节省HDFS存储资源，查询任务的输入数据量减少，使用的MapTask也就减少了。
    > 
    > ORC可以支持复杂的数据结构（比如Map等）。
    
    ORC文件也是以二进制方式存储的，所以是不可以直接读取，ORC文件也是自解析的。
    
        orc格式是自解析生成的，要想把文件变成orc等二进制格式存储，首先存储成普通文件，然后通过insert+select得到

> orc存储文件默认采用ZLIB压缩。比snappy压缩的小

*  ###### parquet ######
    
    Parquet是面向分析型业务的列式存储格式。Parquet文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此Parquet格式文件是自解析的
    
    通常情况下，在存储Parquet数据的时候会按照Block大小设置行组的大小，由于一般情况下每一个Mapper任务处理数据的最小单位是一个Block，这样可以把每一个行组由一个Mapper任务处理，增大任务执行并行度。

##### 语法 #####

create table...
    
    stored as file-formay

*  说明
    
    如果不指定语法，默认使用hive使用textfile格式存储 即普通文本文件 行存储

##### 文件格式存储对比 #####

> 存储文件的压缩比总结：
> 
> ORC > Parquet > textFile

#### 压缩格式和压缩的整合 ####

压缩并且orc存储的数据看不 到

可以直接查询插入一个表，然后在客户端 select查看

> 实际项目开发中，hive表的数据存储格式一般选择orc或parquet,压缩方式一般选择snappy

**把所有的东西放进去 文件夹名/**\*

### hive调优 ###

#### Fetch抓取机制 ####

hive中对某些情况的查询可以不必使用mapreduce进行计算

设置：
    
    set hive.fetch.task。conversion=more
    该属性修改为more以后，在全局查找、字段查找、limit查找等都不走mapreduce。
    
    如果把hive.fetch.task.conversion设置成none，然后执行查询语句，都会执行mapreduce程序。

hive中的每一个调优，底层都是mr的调优

*  使用mr程序处理数据的，尽量不使用。
    
     *  默认实现：more模式
        
            hive.fetch.task.conversion=more
            以下三种情况不走mr程序
            
            全表扫描查询
            字段查询
            limit查询

#### mapreduce本地模式 ####

mapreduce程序除了可以提交到yarn执行之外，还可以使用本地模拟环境运行，此时就不是分布式执行的程序，但是针对小文件小数据处理特别有效果。

设置：
     set hive.exec.mode.local.auto=true;
     //通过设置为true,可以让hive在适当的时候自动启动这个优化

*  建议在开发环境中开启
    
        正常情况下：
        hive sql--->mapreduce--->yarn（分布式执行）--->hdfs结构化数据--->处理结果
        
        本地执行模式：
        hive sql--->mapreduce--->local mode(本地模式 单机执行)--->hdfs结构化数据--->处理结果
        
         hive> SET mapreduce.framework.name=local; 该参数决定了hive中最终的mr程序是什么执行模式
         如果是local 所有的mr程序都走本地 如果yarn 都走yarn.
         
         0.7版本之后：
         SET hive.exec.mode.local.auto=false; 该参数可以自动决定是否切换本地模式。默认不开启
         如果设置为true,会根据下述三个标准进行自动切换
         
         切换标准：
         总共的数据量小于128M
         maptask个数小于4个
         reducetask个数要么为0  要么为1

#### join查询的优化 ####

多个表关联时，最好拆分成小段的sql分段执行，避免一个大的sql，因为无法控制中间的job

##### map side join #####

如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会将Join操作转换成Common Join，即：在Reduce阶段完成join。

容易发生数据倾斜。可以用MapJoin把小表全部加载到内存在map端进行join，避免reducer处理。

执行流程：
    首先是Task A，它是一个Local Task（在客户端本地执行的Task），负责扫描小表b的数据，将其转换成一个HashTable的数据结构，并写入本地的文件中，之后将该文件加载到DistributeCache中。
    接下来是Task B，该任务是一个没有Reduce的MR，启动MapTasks扫描大表a,在Map阶段，根据a的每一条记录去和DistributeCache中b表对应的HashTable关联，并直接输出结果。
    由于MapJoin没有Reduce，所以由Map直接输出结果文件，有多少个Map Task，就有多少个结果文件。

map端join的参数设置：
    开启mapjoin参数设置：
    （1）设置自动选择mapjoin
    set hive.auto.convert.join = true; 默认为true
    （2）大表小表的阈值设置：
    set hive.mapjoin.smalltable.filesize= 25000000;
    小表的输入文件大小的阈值（以字节为单位）;如果文件大小小于此阈值，它将尝试将common join转换为map join。
    因此在实际使用中，只要根据业务把握住小表的阈值标准即可，hive会自动帮我们完成mapjoin，提高执行的效率。

##### 大表join大表 #####

###### 空key过滤 ######

有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够。
    此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据，我们需要在SQL语句中进行过滤。例如key对应的字段为空，操作如下：
    map端join的参数设置：
    
    准备环境：
    create table ori(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';
    
    create table nullidtable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';
    
    create table jointable(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';
    
    load data local inpath '/root/hivedata/hive_big_table/*' into table ori; 
    load data local inpath '/root/hivedata/hive_have_null_id/*' into table nullidtable;
    
    不过滤查询：
    INSERT OVERWRITE TABLE jointable
    SELECT a.* FROM nullidtable a JOIN ori b ON a.id = b.id;
    结果：
    No rows affected (152.135 seconds)
    过滤查询：
    INSERT OVERWRITE TABLE jointable
    SELECT a.* FROM (SELECT * FROM nullidtable WHERE id IS NOT NULL ) a JOIN ori b ON a.id = b.id;
    结果：
    No rows affected (141.585 seconds)

###### 空key转化 ######

有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上。
    不随机分布：
    set hive.exec.reducers.bytes.per.reducer=32123456;
    set mapreduce.job.reduces=7;
    
    INSERT OVERWRITE TABLE jointable
    SELECT a.*
    FROM nullidtable a
    LEFT JOIN ori b ON CASE WHEN a.id IS NULL THEN 'hive' ELSE a.id END = b.id;
    No rows affected (41.668 seconds)
    结果：这样的后果就是所有为null值的id全部都变成了相同的字符串，及其容易造成数据的倾斜（所有的key相同，相同key的数据会到同一个reduce当中去）。
    为了解决这种情况，我们可以通过hive的rand函数，随记的给每一个为空的id赋上一个随机值，这样就不会造成数据倾斜。
    随机分布：
    set hive.exec.reducers.bytes.per.reducer=32123456;
    set mapreduce.job.reduces=7;
    
    INSERT OVERWRITE TABLE jointable
    SELECT a.*
    FROM nullidtable a
    LEFT JOIN ori b ON CASE WHEN a.id IS NULL THEN concat('hive', rand()) ELSE a.id END = b.id;

###### 大小表、小大表 ######

面试：数据处理需要多久

数据量本身，数据的结构化，是否压缩，业务逻辑，服务器的性能资源

##### group by 优化–map端聚合 #####

##### 数据倾斜问题 #####

##### 执行计划 #####

##### 并行执行机制 #####

##### 严格模式 #####

##### jvm重用机制 #####

##### 推测执行机制 #####

（有待拷贝从当天预习资料以及老师上课讲义）

*  join中的map side join
    
     *  通常认为进行数据的join 应该把相关联的数据发送到同一个reduce 在reduce中完成关联的判断 这种称之为common join(reduce join)
     *  当下hive版本中 **默认开启了map端的join** 如果可以转化满足map端join的条件 hive将会自动转化
        
            何谓map 端join？
             首先启动第一个mr程序把小表的文件读取 变成hashtable 做分布式缓存发送
             
             然后启动一个只有mapper阶段的mr程序 通过maptask读取一条记录和上一步的缓存做关联  在map端就完成了join的操作
     *  在实际使用中，通过下面这个参数设置小表阈值。
        
            set hive.mapjoin.smalltable.filesize= 25000000;  设置多大以下为小表
 *  大表join大表
    
     *  如果有空值 空值过滤可以优化查询时间
     *  空值转换
        
         *  把空值转换成某一个非空字符串
            
                CASE WHEN a.id IS NULL THEN 'hive' ELSE a.id END = b.id
                
                弊端如果这样的空值过多  会造成reduce数据倾斜
         *  把空值转换成若干个随机的非空字符串
            
                CASE WHEN a.id IS NULL THEN concat('hive', rand()) ELSE a.id END
     *  大小表之间的join
        
        不管是大join小 还是小join大 当下的hive版本已经自动处理好了 不需要额外的特别设置。
        
            hive老的版本中  把小表写在前面 提高join的速度
 *  group by优化
    
     *  如果数据都发送到reduce进行分组后的操作 容易产生数据倾斜
     *  记住如果数据本身不平衡 可能会导致数据倾斜 请开启下面的选项
        
            有数据倾斜的时候进行负载均衡（默认是 false）
            set hive.groupby.skewindata = true;
 *  maptask个数调整
    
     *  小文件场景（想法进行合并）
        
         *  上传hdfs之前进行本地合并 Java IO—>block size
         *  上传中间进行合并 appendToFile
         *  上传之后使用hadoop archive小文件归档
     *  大文件场景（想法进行拆分）
        
         *  在设置hdfs存储block size的时候进行改变
         *  使用distribute by把原来表的数据随机分散成若干个部分 set mapreduce.job.reduces =10;
            
            然后在针对数据进行处理 就可以增加maptask个数
            
                create table a_1 as
                select * from a
                distribute by rand(123);
 *  reducetask个数调整
    
     *  set mapreduce.job.reduces = -1 默认hive自己会根据任务情况进行决定
     *  当然用户可以手动指定个数 不一定生效 hive会保证某些逻辑能够正常执行
        
            order by  全局排序 意味着只能有一个reducetask   hive为了满足能够正常执行 自己决定
 *  hive的并行执行机制
    
     *  hive中sql会最终编译成若干个不同的阶段stage执行 如果当中某些阶段没有依赖 可以尝试开始并行执行
     *  默认是没有开启的 需要开启该机制 并且指定最大可以通过并行执行的个数
        
            set hive.exec.parallel=true; //打开任务并行执行
            set hive.exec.parallel.thread.number=16; //同一个 sql 允许最
            大并行度，默认为 8
     *  注意：并行执行意味着短时间内资源的大量消耗 通常在集群空闲时期开启
 *  严格模式
    
     *  默认情况下 hive执行的是非严格模式
     *  可以通过下面的参数开启严格模式
        
            set hive.mapred.mode = strict;
     *  如果是严格模式，以下三种操作被禁止
        
         *  分区表查询必须指定where分区条件 禁止全表扫描
         *  order by查询必须跟limit语法 限制返回的条数
         *  禁止笛卡尔积查询
 *  jvm重用机制
    
     *  默认情况下在mr中，不管是maptask还是reducetask 本质都是jvm进程
     *  默认一个jvm运行一个task 运行完毕 释放资源 销毁jvm
     *  如果开启重用机制 就可以在本次job中多次重复使用该同一个jvm 避免资源频繁的申请销毁
     *  对其他job来说 就会形成一直霸占资源的场景
 *  mr推测执行机制
    
     *  默认maptask reducetask都是开启的
     *  找出拖后腿的task 为其启动备份task 两个同时处理同一份数据。谁先执行完，谁的结果最为最终结果。