Hive - 文件存储、文件压缩、视图、日志、运行方式、JDBC、优化

悠悠 2024-04-19 16:24 32阅读 0赞

## 一、文件存储格式 ##

[File Formats and Compression][]: [RCFile][], [Avro][], [ORC][], [Parquet][]; [Compression][], [LZO][]

### 1.1 textfile ###

1、textfile是hive默认的数据文件存储格式
    2、textfile是普通的文件文本存储
    3、不压缩
    4、可以配合压缩配置属性进行压缩

CREATE TABLE `u4`(
      `id` int,
      `name` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    stored as textfile;
    
    set mapreduce.output.fileoutputformat.compress=true;
    set hive.exec.compress.output=true;
    insert into table u4
    select * from u2;

### 1.2 sequencefile ###

1、sequencefile是hive为用户提供的二进制存储
    2、sequencefile不能使用load方式直接加载数据
    3、本身压缩

CREATE TABLE `u4`(
      `id` int,
      `name` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    stored as sequencefile;

### 1.3 rcfile ###

1、rcfile是hive为用户提供的行列混合存储
    2、rcfile格式下，将会尽量把附近的行和列的块尽量存储到一起
    3、本身压缩，且查询效率较高

CREATE TABLE `u5`(
      `id` int,
      `name` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    stored as rcfile;

### 1.4 orc ###

1、orc是优化后的rcfile

CREATE TABLE `u6`(
      `id` int,
      `name` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    stored as orc;

### 1.5 parquet ###

1、parquet是典型列式存储，自带压缩，查询较快（按列查询）

CREATE TABLE `u7`(
      `id` int,
      `name` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    stored as PARQUET;
    insert into table u7
    select * from u2;

### 1.6 自定义存储格式 ###

数据：
    seq_yd元数据文件：
    aGVsbG8gemhhbmdoYW8=
    aGVsbG8gZmVpZmVpLGdvb2QgZ29vZCBzdHVkeSxkYXkgZGF5IHVw
    seq_yd文件为base64编码后的内容，decode后数据为：
    
    hello zhanghao
    hello feifei,good good study,day day up
    
    create table cus(str STRING)  
    stored as  
    inputformat 'org.apache.hadoop.hive.contrib.fileformat.base64.Base64TextInputFormat'  
    outputformat 'org.apache.hadoop.hive.contrib.fileformat.base64.Base64TextOutputFormat';  
    
    LOAD DATA LOCAL INPATH '/home/hivedata/cus' INTO TABLE cus;

### 配置文件相关内容 ###

hive-default.xml.template

<property>
        <name>hive.default.fileformat</name>
        <value>TextFile</value>
        <description>
          Expects one of [textfile, sequencefile, rcfile, orc].
          Default file format for CREATE TABLE statement. Users can explicitly override it by CREATE TABLE ... STORED AS [FORMAT]
        </description>
      </property>

## 二、文件压缩 ##

### 2.1 map输出压缩 ###

mapreduce.map.output.compress=false
    mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.DefaultCodec

### 2.2 reduce输出压缩（reduce压缩） ###

snappy、bzip2、gzip、DefaultCompress
    mapreduce.output.fileoutputformat.compress=false
    mapreduce.output.fileoutputformat.compress.type=NONE/RECORD/BLOCK(默认RECORD)
    mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.DefaultCodec

### 2.3 压缩配置 ###

set hive.exec.compress.output=false;
    set hive.exec.compress.intermediate=false;
    set hive.intermediate.compression.codec=
    set hive.intermediate.compression.type=
    
    
    CREATE TABLE `u4`(
      `id` int,
      `name` string)
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
    stored as textfile;
    
    set mapreduce.output.fileoutputformat.compress=true;
    set hive.exec.compress.output=true;
    insert into table u4
    select * from u2;

### 2.4 压缩算法 ###

snappy、bzip2、gzip、DefaultCompress

### 2.5 lzo压缩（未完成） ###

### 2.6 snaapy压缩（未完成） ###

## 三、Hive视图 ##

### 3.1 视图简介 ###

hive的视图简单理解为逻辑上的表
    hive现目前只支持逻辑视图，不支持物化视图。
    
    hive的视图意义：
    1、对数据进行局部暴露(涉及隐私数据不暴露)。
    2、简化复杂查询。

### 3.2 创建视图 ###

create view if not exists tab_v1 
    as 
    select id from u2;

### 3.3 查看视图 ###

show tables;
    show create table tab_v1;
    desc tab_v1;

### 3.4 删除视图 ###

drop view if exists tab_v2;
    drop table if exists tab_v1;   (drop是删除表，不能用于删除视图)

### 3.5 注意事项 ###

1、不建议先删除视图对应的表后再查询视图。
    2、视图是不能用insert into 或者load 方式来加载数据。
    3、视图是只读，不能修改其结构、表相关属性。

## 四、Hive日志 ##

### 4.1 Hive的系统日志 ###

默认目录：/tmp/{user.name}
    hive.log.dir={java.io.tmpdir}/{user.name}
    hive.log.file=hive.log

### 4.2 Hive的查询日志 ###

// hive-default.xml.template
    <property>
        <name>hive.querylog.location</name>
        <value>${system:java.io.tmpdir}/${system:user.name}</value>
        <description>Location of Hive run time structured log file</description></property>

## 五、Hive运行方式 ##

beeline的配置

### 5.1 Hive的属性设置 ###

1、hive-site.xml
    2、hive通过命令行参数设置
    3、hive通过cli端set设置

### 5.2 三种设置方式的区别 ###

1、属性设置优先级从上往下依次升高
    2、hive-site.xml是全局和永久的，其它两个是临时和局部的
    3、hive-site.cml适合所有属性配置，而后两个对于系统级别的属性不能配置，比如启动所需的元数据库url、log的配置等。

### 5.3 Hive的四类属性 ###

hiveconf：可读可写
    hivevar：自定义临时变量，可读可写
    system：可读可写
    env：可读不可写
    
    --hiveconf <property=value>   Use value for given property
    --hivevar <key=value>         Variable subsitution to apply to hive
                                      commands. e.g. --hivevar A=B
    hive -e
    hive -f
    hive -S		静音模式
    hive -i
    hive --database 
    
    hive>set -v;  ##查看hive相关的环境变量
    hive -e "set" | grep current   ##单个查找

### 5.4 Hive的三种运行方式 ###

在hive的cli端运行：(开发测试，以及临时跑作业)
    通过命令行 hive -e 'sql query';
    通过命令行 hive -f /hql文件   (生产线)
    
    hive --database qf1701 -e 'select * from text1';
    hive --database qf1701 --hivevar ls=2 --hiveconf tn=text1 -e 'select * from ${hiveconf:tn} limit ${hivevar:ls}';
    
    hive -S --hivevar mapoutputdir=/home/hivedata/out/05 --hivevar textoutdir=/home/hivedata/out/06 --hivevar limit=1 -f ./hql
    
    注意：
    1、一个--hivevar 或者 --hiveconf 只能带一个参数
    2、--hiveconf 或者 --hivevar 可以混合使用
    3、--hiveconf 或 --hivevar 定义参数不能取消

## 六、Hive的远程模式使用 ##

hive也可以启动为一个服务器，来对外提供
    
    启动方式，（假如是在hadoop01上）：
    启动为前台：bin/hiveserver2
    启动为后台：nohup bin/hiveserver2 1>/var/log/hiveserver.log 2>/var/log/hiveserver.err &
    
    启动成功后，可以在别的节点上用beeline去连接
    方式（1）
    hive/bin/beeline  回车，进入beeline的命令界面
    输入命令连接hiveserver2
    beeline> !connect jdbc:hive2://hdp01:10000
    （hdp01是hiveserver2所启动的那台主机名，端口默认是10000）
    方式（2）
    或者启动就连接：
    bin/beeline -u jdbc:hive2://hdp01:10000 -n hadoop
    
    接下来就可以做正常sql查询了

## 七、Hive的JDBC ##

### 7.1 实例 ###

pom.xml

<dependency>
    	<groupId>org.apache.hive</groupId>
    	<artifactId>hive-jdbc</artifactId>
    	<version>1.2.1</version>
    </dependency>

HiveJdbc.java

package HiveJdbc;
    
    import java.sql.Connection;
    import java.sql.PreparedStatement;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    
    import util.HiveJdbcUtil;
    
    /**
     * hive的连接
     */
    public class HiveJDBC {
        private static Connection conn = null;
        public static void main(String[] args) {
            selectDemo();
        }
    
        //查询某天某公司的员工数量
        public static void selectDemo(){
            conn = HiveJdbcUtil.getConn();
            PreparedStatement ps = null;
            ResultSet rs = null;
            try {
                //获取ps
                ps = conn.prepareStatement("select \n" +
                        "*\n" +
                        "from t1 \n");
                //为占位符赋值
    //            ps.setInt(1,1);
                //执行查询
                rs = ps.executeQuery();
                //遍历结果集
                while (rs.next()){
                    System.out.println(rs.getString(1) + "\t"
                            + rs.getString(2) + "\t" + rs.getString(3));
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } finally {
                HiveJdbcUtil.closeConn(conn,ps,rs);
    
            }
        }
    
    }

HiveJdbcUtil.java

package util;
    
    import java.sql.*;
    
    /**
     * 连接工具类
     */
    public class HiveJdbcUtil {
        private static String driverName = "org.apache.hive.jdbc.HiveDriver";
        private static String url = "jdbc:hive2://hadoop01:10000/leo"; //指定数据库为dw_hivedata
        private static String userName = "root"; //hiveserver2的用户名和密码
        private static String password = "123456";
        public static void main(String[] args) {
            System.out.println(getConn());
        }
    
        /**
         *获取hive的驱动连接
         * @return
         */
        public static Connection getConn(){
            Connection conn = null;
            try {
                Class.forName(driverName); //加载驱动
                //获取conn
                conn = DriverManager.getConnection(url, userName, password);
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
                System.exit(1);
            } catch (SQLException e){
                e.printStackTrace();
            }
            return conn;
        }
    
        /**
         * 关闭连接
         * @param conn
         */
        public static void closeConn(Connection conn, PreparedStatement ps , ResultSet rs){
            if(rs != null){
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
    
            if(ps != null){
                try {
                    ps.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
    
            if(conn != null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

### 7.2 注意 ###

1、conn、ps\rs的关闭顺序需要时rs\ps\conn,否则报错sasl
    2、连接的用户名和密码需要 填写，如果没有配置可以使用root、root,否则会报错没有权限。

### 7.3 优化 ###

kylin：加速hive的查询（将查询预执行，并将结果保存在Hbase中）

## 八、Hive的优化 ##

1、考虑环境（硬件服务器、配置）
    2、业务	（统计指标的实现思路）
    3、代码或者配置属性

### 8.1、explain 和 explain extended ###

explain select * from text1;
    explain extended select * from text1;
    explain extended
    select
    d.deptno as deptno,
    d.dname as dname
    from dept d
    union all
    select
    d.dname as dname,
    d.deptno as deptno
    from dept d
    ;
    explain ： 只有对hql语句的解释。
    explain extended：对hql语句的解释，以及抽象表达式树的生成。
    
    stage 相当于一个job，一个stage可以是limit、也可以是一个子查询、也可以是group by等。
    hive默认一次只执行一个stage，但是如果stage之间没有相互依赖，将可以并行执行。
    任务越复杂，hql代码越复杂，stage越多，运行的时间一般越长。

### 8.2、join ###

hive的查询永远是小表（结果集）驱动大表（结果集）
    hive中的on的条件只能是等值连接
    注意hive是否配置普通join转换成map端join、以及mapjoin小表文件大小的阀值
    注意hive的倾斜join：

### 8.3、limit的优化 ###

hive.limit.row.max.size=100000
    hive.limit.optimize.limit.file=10
    hive.limit.optimize.enable=false  (如果limit较多时建议开启)
    hive.limit.optimize.fetch.max=50000

### 8.4、本地模式 ###

hive.exec.mode.local.auto=false (建议打开)
    hive.exec.mode.local.auto.inputbytes.max=134217728（128M）
    hive.exec.mode.local.auto.input.files.max=4

### 8.5、并行执行 ###

hive.exec.parallel=false   (建议开启)
    hive.exec.parallel.thread.number=8

### 8.6、严格模式 ###

hive.mapred.mode=nonstrict
    
    Cartesian Product.
    No partition being picked up for a query.
    Orderby without limit.
    Comparing bigints and strings.
    Comparing bigints and doubles.
    
    select
    e.*
    from dept d 
    join emp e
    ;
    
    select
    e.*
    from emp e
    order by e.empno desc
    ;
    
    create table text9 (
    uid bigint, 
    uid1 double
    )
    ROW FORMAT DELIMITED FIELDS TERMINATED BY ' '
    ;
    
    LOAD DATA LOCAL INPATH '/home/hivedata/text8' INTO TABLE text9;
    
    select
    
    - from text9 t
      where t.uid = t.uid1
      ;

### 8.7、mapper和reducer的个数 ###

不是mapper和redcuer个数越多越好，也不是越少越好,适合即可。
    
    将小文件合并处理(将输入类设置为：CombineTextInputFormat)
    通过配置将小文件合并：
    mapred.max.split.size=256000000   
    mapred.min.split.size.per.node=1
    mapred.min.split.size.per.rack=1
    hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat
    
    手动设置：
    set mapred.map.tasks=2;
    
    reducer的个数(自动决定和手动设置)：
    mapred.reduce.tasks=-1
    hive.exec.reducers.max=1009

### 8.8、配置jvm重用 ###

mapreduce.job.jvm.numtasks=1   ###
    mapred.job.reuse.jvm.num.tasks=1

### 8.9、数据倾斜 ###

由于key分布不均匀造成的数据向一个方向偏离的现象
    join语句、count(distinct col)、group by都认识容易造成数据倾斜的
    
    倾斜现象：
    卡在某一个reduce任务
    
    解决方法：
    1、找到造成数据倾斜的key，然后再通过hql语句避免（查看日志是那个task失败-->找到该task关联字段、group by\count(distrinct col)-->抽样字段个数-->判断是否是倾斜的key）。单独拿出来处理，然后再和正常的结果进行union all
    
    2、造成倾斜的key加随机数（加的随机不能造成二次倾斜、保证加随机不能影响原有的业务）
    
    3、设置相关倾斜的属性
    hive.map.aggr=true;
    hive.groupby.skewwindata=false;（建议开启）
    hive.optimize.skewjoin=false;
    
    4、如上都不行，则需要重新查看业务，优化语句流程

### 8.10、索引 ###

索引是一种hive的优化

### 8.11、分区 ###

分区本身就是hive的一种优化

### 8.12、job的数量 ###

一般是一个查询产生一个job，然后通常情况一个job、可以是一个子查询、一个join、一个group by 、一个limit等一些操作。
    
    1个job：
    select
    a.*
    from t1 a
    left join t1 b
    on a.order_id = b.order_id
    ;

### 8.13 analyze ###

参考官网:https://cwiki.apache.org/confluence/display/Hive/StatsDev
    
    Analyze，分析表（也称为计算统计信息）是一种内置的Hive操作，可以执行该操作来收集表上的元数据信息。这可以极大的改善表上的查询时间，因为它收集构成表中数据的行计数，文件计数和文件大小（字节），并在执行之前将其提供给查询计划程序。
    
    已经存在表的Analyze语法：
    ANALYZE TABLE [db_name.]tablename [PARTITION(partcol1[=val1], partcol2[=val2], ...)]  -- (Note: Fully support qualified table name since Hive 1.2.0, see HIVE-10007.)
      COMPUTE STATISTICS 
      [FOR COLUMNS]          -- (Note: Hive 0.10.0 and later.)
      [CACHE METADATA]       -- (Note: Hive 2.1.0 and later.)
      [NOSCAN];
    
    例1(指定分区)、
    ANALYZE table dw_employee_hive partition(bdp_day=20190701) COMPUTE STATISTICS;
    收集表的bdp_day=20190701的这个分区下的所有列列相关信息。它是一个细粒度的分析语句。它收集指定的分区上的元数据，并将该信息存储在Hive Metastore中已进行查询优化。该信息包括每列，不同值的数量，NULL值的数量，列的平均大小，平均值或列中所有值的总和（如果类型为数字）和值的百分数。
    
    例2(指定所有列)、
    ANALYZE table dw_employee_hive partition(bdp_day=20190701) COMPUTE STATISTICS FOR COLUMNS;
    收集表的bdp_day=20190701的这个分区下的所有列相关信息。
    
    例3(指定某列)、
    ANALYZE table dw_employee_hive partition(bdp_day=20190701) COMPUTE STATISTICS FOR COLUMNS snum,dept;
    
    例4、
    ANALYZE TABLE dw_employee_hive partition(bdp_day=20190701) COMPUTE STATISTICS NOSCAN;
    收集指定分区相关信息，然后不进行扫描。
    
    测试分析后的结果。
    例1、
    DESCRIBE EXTENDED dw_employee_hive partition(bdp_day=20190701);
    
    描述结果:
    ...parameters:{totalSize=10202043, numRows=33102, rawDataSize=430326, ...
    
    例2、
    desc formatted dw_employee_hive partition(bdp_day=20190701) name;
    
    结果如下：
    
    # col_name  data_type   min max num_nulls   distinct_count  avg_col_len max_col_len num_trues   num_falses  comment
    
    name string 0 37199 4.0 4 from deserializer
    
    注意:
    对分区表的分析，一般都要指定分区，如对全表分析，则可以这样使用partition(bdp_day).
    优化后查询结果可以参考:https://www.cnblogs.com/lunatic-cto/p/10988342.html

[File Formats and Compression]: https://cwiki.apache.org/confluence/display/Hive/FileFormats
[RCFile]: https://cwiki.apache.org/confluence/display/Hive/RCFile
[Avro]: https://cwiki.apache.org/confluence/display/Hive/AvroSerDe
[ORC]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+ORC
[Parquet]: https://cwiki.apache.org/confluence/display/Hive/Parquet
[Compression]: https://cwiki.apache.org/confluence/display/Hive/CompressedStorage
[LZO]: https://cwiki.apache.org/confluence/display/Hive/LanguageManual+LZO