Hive数仓基础

架构图：

 

 

组成：SQL语句到任务执行需要经过解释器，编译器，优化器，执行器

•  解释器：调用语法解释器和语义分析器将SQL语句转换成对应的可执行的java代码或业务代码
• ​ 编译器：将对应的java代码转换成字节码文件或jar包
• ​ 优化器：从SQL语句到java代码的解析转化过程中需要调用优化器，进行相关策略优化
• ​ 执行器：当业务代码转换完成之后上传到集群中执行

 

职责：元数据管理，sql解析

　　1，维护数据的元数据，记录数据位置，数据列，数据类型，分割格式等 相当二级索引功能

　　2，sql解析，对输入的sql进行语法解析，转换为mapReduce,spark计算程序

 

Hive参数操作：
    启动hive cli时，通过--hiveconf key=value的方式进行设置，
    注意：只在当前会话有效，退出会话之后参数失效
    
    在进入到cli之后，通过set命令设置
    例：set hive.cli.print.header=true;       

 

hive脚本运行方式：
    --hive直接执行sql命令，可以写一个sql语句，也可以使用;分割写多个sql语句
        hive -e "select..."
    --hive执行sql命令，将sql语句执行的结果重定向到某一个文件中
        hive -e "select.."  > aaa
    --hive静默输出模式，输出的结果中不包含ok，time token等关键字
        hive -S -e "select.."  > aaa
    --hive可以直接读取文件中的sql命令，进行执行
        hive -f file
    --hive可以从文件中读取命令，并且执行初始化操作
        hive -i /home/my/hive-init.sql
    --在hive的命令行中也可以执行外部文件中的命令
        hive> source file (在hive cli中运行)   

 

hive的动态分区：
    --hive设置hive动态分区开启
    set hive.exec.dynamic.partition=true;
    默认：true
    --hive的动态分区模式
    set hive.exec.dynamic.partition.mode=nostrict;
    默认：strict（至少有一个分区列是静态分区）
    --每一个执行mr节点上，允许创建的动态分区的最大数量(100)
    set hive.exec.max.dynamic.partitions.pernode;
    --所有执行mr节点上，允许创建的所有动态分区的最大数量(1000)    
    set hive.exec.max.dynamic.partitions;
    --所有的mr job允许创建的文件的最大数量(内存1g对应最多100000个fd)    
    set hive.exec.max.created.files;

 注：partition对应文件目录，减少计算的数据量

Hive分桶：
    1、Hive分桶表是对列值取hash值得方式，将不同数据放到不同文件中存储
    ​2、对于hive中每一个表、分区都可以进一步进行分桶
    ​3、由列的hash值除以桶的个数来决定每条数据划分在哪个桶中
    
    Hive分桶的配置
    set hive.enforce.bucketing=true;
    
    Hive分桶的抽样查询
    select * from bucket_table tablesample(bucket 1 out of 4 on columns)
        (BUCKET x OUT OF y)
        x：表示从哪个bucket开始抽取数据
        y：必须为该表总bucket数的倍数或因子，（抽取数据量为桶的个数/y）

注：分桶是对文件进行切割，bucket数量对应reduce数量，较少shuffer数据量

 

Hive视图:
    一组数据的逻辑表示，本质上就是一条SELECT语句的结果集
        1、不支持物化视图 ​
        2、只能查询，不能做加载数据操作 ​
        3、视图的创建，只是保存一份元数据，查询视图时才执行对应的子查询 ​
        4、view定义中若包含了ORDER BY/LIMIT语句，当查询视图时也进行ORDER BY/LIMIT语句操作，view当中 定义的优先级更高 ​
        5、view支持迭代视图

        
hive索引:        
    --创建索引：
    create index t1_index on table t1(name)
    as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred             rebuild in table t1_index_table;
    --as：指定索引器；
    --in table：指定索引表，若不指定默认生成在default__t1_t1_index__表中
    create index t1_index on table t1(name)
    as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler' with deferred             rebuild;
    --查询索引
    show index on t2;
    --重建索引（建立索引之后必须重建索引才能生效）
    ALTER INDEX t1_index ON t2 REBUILD;
    --删除索引
    DROP INDEX IF EXISTS t1_index ON t2;

 

Hive优化：
    Hive的存储层依托于HDFS，Hive的计算层依托于MapReduce,Hive的执行效率主要取决于SQL语句的执行效率(主要是MapReduce，spark程序优化)

　1，本地模式
    开发阶段可以选择使用本地执行，提高SQL语句的执行效率，验证SQL语句是否正确
    //设置本地模式
    set hive.exec.mode.local.auto=true;    
    注：本地模式，加载数据文件大小不能超过128M,如果超过128M,也会按照集群模式运行。
    --设置读取数据量的大小限制
    set hive.exec.mode.local.auto.inputbytes.max=128M
    
    2，并行模式
    一个SQL语句中包含多个子查询语句，且多个子查询语句之间没有任何依赖关系，此时，可以Hive运行的并行度
    //设置Hive SQL的并行度
    set hive.exec.parallel=true;
    //设置一次SQL计算允许并行执行的job个数的最大值
    set hive.exec.parallel.thread.number
    
    3，排序处理
    ​  Order By - 对于查询结果做全排序，只允许有一个reduce处理 ​ （当数据量较大时，严格模式下，必须结合limit来使用）
    ​  Sort By - 对于单个reduce的数据进行排序 ​
      Distribute By - 分区排序，经常和Sort By结合使用 ​
      Cluster By - 相当于 Sort By + Distribute By ​ （Cluster By不能通过asc、desc的方式指定排序规则； ​ 可通过 distribute by column sort by column asc|desc 的方式）
   

　4，Join操作
         手动Map join
         SELECT  /*+ MAPJOIN(smallTable) */  smallTable.key,  bigTable.value
         FROM  smallTable  JOIN  bigTable  ON  smallTable.key  =  bigTable.key;
   
         //开启自动Map Join

　　 //该参数为true时，Hive自动对左边的表数据统计，如果是小表就加入内存，即对小表使用Map join
        set hive.auto.convert.join = true;
　　//小表判断的阈值，如果表的大小小于该值则会被加载到内存中运行
        hive.mapjoin.smalltable.filesize;  
        //默认值：true；是否忽略mapjoin hint 即mapjoin标记
        hive.ignore.mapjoin.hint；
  
        大表join大表
            空key过滤：有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据，我们需要在SQL语句中进行过滤。
            ​空key转换：有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上
    
    5，合并小文件
       操作的时候，如果文件数目小，容易在文件存储端造成压力，影响效率
        //是否合并map输出文件：
        set hive.merge.mapfiles=true
        //是否合并reduce输出文件：
        set hive.merge.mapredfiles=true;
        //合并文件的大小：
        set hive.merge.size.per.task=256*1000*1000
        
    6，Map-Side聚合
        ​ Hive的某些SQL操作可以实现map端的聚合，类似于MR的combine
        //设置以下参数开启在Map端的聚合：
        set hive.map.aggr=true;
        //map端group by执行聚合时处理的多少行数据（默认：100000）
        hive.groupby.mapaggr.checkinterval：
        //聚合的最小比例（预先对100000条数据做聚合，若聚合之后的数据量/100000的值大于该配置0.5，则不会聚合）
        hive.map.aggr.hash.min.reduction：
        //map端聚合使用最大内存
        hive.map.aggr.hash.percentmemory：
        //是否对GroupBy产生的数据倾斜做优化，默认为false
        hive.groupby.skewindata
    
    7，控制Map以及Reduce的数量
       //一个split的最大值，即每个map处理文件的最大值
        set mapred.max.split.size
        //一个节点上split的最小值
        set mapred.min.split.size.per.node
        //一个机架上split的最小值
        set mapred.min.split.size.per.rack

        //强制指定reduce任务的数量
        set mapred.reduce.tasks
        //每个reduce任务处理的数据量
        set hive.exec.reducers.bytes.per.reducer
        //每个任务最大的reduce数
        set hive.exec.reducers.max