Hive学习笔记

第1章 Hive基本概念

• Hive是基于Hadoop 的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类SQL 查询功能
• Hive架构原理：解析器、编译器、优化器、执行器，HDFS存储，MR运算，元数据存在关系型数据库中
• Hive与关系型数据库的比较：
  • 查询语言
  • 数据存储位置：Hive是基于Hadoop的，数据存放在分布式存储系统HDFS上，关系型数据库存在本地文件系统中
  • 数据更新：Hive一般不对数据进行改写，比如update，而关系型数据库的数据通常是经常更新的
  • 索引：Hive是读模式，读取的时候才校验数据的合法性，数据库是写模式，写的时候会建立索引，对于小的数据量的访问，数据库速度快，效率高，数据量大的时候，Hive比较有优势，由于MR的引入，可以并行访问数据

第2章 Hive安装

Hive安装

• 解压，修改名为hive
• 修改conf目录下的hive-env.sh，添加HADOOP_HOME，HIVE_CONF_DIR：
  export HADOOP_HOME=/opt/module/hadoop 2.7.2
  export HIVE_CONF_DIR=/opt/module/hive/conf
• 配置mysql，存储元数据

MySQL安装

• 查看是否已安装：]rpm qa|grep mysql，若安装删除：rpm -e --nodeps 名称
• 解压mysql-libs：root用户下安装MySQL服务器：rpm -ivh mysql-server-xxx.rpm
• 查看随机生成的密码：cat /root/.my_secret，查看MySQL状态：service mysql status，启动：service mysql start
• 安装MySQL客户端：rpm -ivh mysql-client-xxx.rpm
• 连接MySQL：mysql -uroot -pxxxx（密码）
• 修改密码：set password=password('999999')

MySQL中主机设置

• 进入mysql：mysql -uroot -p999999
• 显示数据库：show databases; 使用mysql数据库：use mysql; 
• 将user表中的host localhost 修改为%，update user set host='%' where host='localhost';其他删除，这样其他用户都可以登录mysql

Hive元数据配置到MySQL

• 拷贝MySQL驱动mysql-connector-hava-5.1.27-bin.jar到hive/lib/下
• hive/conf下创建hive-site.xml，配置MySQL地址、用户、密码、连接驱动类

<property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://localhost102:3306/metastore?createDatabaseIfNotExist=true</value>
    <description>JDBC connect string for a JDBC metastore</description>
</property>

<property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
    <description>Driver class name for a JDBC metastore</description>
</property>

<property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>root</value>
    <description>username to use against metastore database</description>
</property>

<property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>999999</value>
    <description>password to use against metastore database</description>
</property>

HiveJDBC访问

• hiveserver2，hive向外提供服务（不使用bin/hive使用hive功能而是通过第三方或外部客户端的方式使用hive）
• beeline，hiveserver2的一个客户端

常用交互命令

• bin/hive -e "SQL语句;"，不进入交互窗口执行sql
• bin/hive -f file，-f执行脚本sql语句

Hive其他命令

• hive命令行下使用hdfs命令hive> dfs -命令，如：dfs -ls /
• hive命令行下使用linux命令hive> ！命令，如！ls ./
• 查看hive的历史命令：用户目录下有个隐藏文件.hivehistory

常见属性配置

• 仓库位置，默认default数据库没有新建目录，直接在warehouse下建表

<property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/user/hive/warehouse</value>
    <description>location of default database for the warehouse</description>
</property>

• 查询后信息显示配置，显示表列名，使用的哪个数据库

<property>
    <name>hive.cli.print.header</name>
    <value>true</value>
</property>

<property>
    <name>hive.cli.print.current.db</name>
    <value>true</value>
</property>

• Hive运行日志信息位置：修改conf/hive-log4j.properties.template文件名称为hive-log4j.properties，修改hive.log.dir=
  dir=/opt/module/hive/logs
• 参数配置：hive-site.xml < 命令行参数bin/hive -hiveconf < 参数声明

第3章 Hive数据类型

• 常用：int，bigint，double，string
• 集合数据类型：struct，map，array
• 类型转化
  • 隐式转换：任何类型都可以转换为string类型
    1>任何整数类型都可以隐式的转换为一个范围更广的类型
    2>所有整数、float、数值string都可以隐式的转换为double
    3>tinyint、smallint、int都可以转换为float（不包括bigint）
    4>boolean不可以转换为任何其他类型
  • 强制转换：cast(A as B)，转换失败返回null

第4章 DDL数据定义

创建数据库

• create database xxx [location ‘/xx’];，默认/user/hive/warehouse下创建xxx.db文件夹，否则创建/xx文件夹，可以与库名xxx不同；即库名可以和库所在的路径名不一样
• if not exists xxx

查询数据库

• 显示数据库：show databases [like 'xxx']；支持模糊查询
• 查看数据库详情：desc database [extended] xxx；extended查询更多信息
• 切换数据库：use xxx；

修改数据库

• 可以使用alter database修改dbproperties键值对属性值，其他元数据不可更改，包括库名和位置
• alter database xxx set dbproperties('xx'='xx');

删除数据库

• drop database [if exists] xxx [cascade]; 库不空不可删除，cascade强制删除

创建表

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name,
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]

• 使用like创建
• 内部表和外部表区别：删除时，内部表的元数据和实际数据都会删除，外部表只删除元数据，不删除实际数据。
• 相互转换：alter table xxx set tblproperties('EXTERNAL'='TRUE')，严格区分大小写，设置FALSE为内部表。

注意：

• location仅仅只是表的路径，创建表时使用location则不会创建目录，使用location指定的路径为表目录。
• 查看表信息：desc formatted xxx;
• 查看建表语句：show create table xxx;

内部表与外部表的区别：

• 删除时：删除内部表时，会把元数据和原始数据全部删除；删除外部表时，只删除元数据，原始数据保留
• 什么场景创建内部表？外部表
  • 多人使用的表创建外部表
  • 自己用的临时表创建内部表

分区表

• 分区表实际上对应HDFS文件系统上的独立文件夹，分区表可以有多级
• 创建分区表：create ... partitioned by (xxx xx)
• 加载数据到分区表：load ... partition(xxx='xx')，会自动添加分区元数据，put进去的不会添加元数据，可以添加分区后查询
• 添加分区：alter table xxx add partition(xxx='xx')
• 删除分区：alter table xxx drop partition(xxx='xx')，添加多个分区用空格，删除多个分区用逗号
• 查看有多少分区：show partitons xxx；查看分区表结构：desc formatted xxx partition；
• 分区表和数据关联的三种方式：
  1>上传数据后修复msck repair table xxx；
  2>hadoop上传数据后添加分区；
  3>load进去的可以直接查询，

修改表

• 重命名：alter table xxx rename to xx;
• 更改某一列：alter table xxx change [column] old_name new_name new_type [comment xx] [first | after col_name];
• 增加和替换所有列：alter table xxx add|replace columns (col_name col_type); 替换是替换所有列
• 删除表：drop table xxx;

第5章 DML操作

数据导入

• load：在hdfs上移动数据，在本地上上传数据，可以使用overwrite into覆盖原表中的数据
• insert：插入分区表需要加partition再加values；后可以加select子查询
• 创建时使用as select，从一张表复制数据并创建表
• 使用location指定数据路径
• import table xx from '/xxx'，只能导入export导出的数据，其中包含元数据和实际数据，xx必须不存在或者存在没有数据且列必须与导出的列全部匹配

数据导出

• insert overwrite [local] directory '/xxx'  select xxx，导出到HDFS或本地上，（结果为MR的输出，要指定输出分隔符方便后续使用）
• export  table xxx to '/xxx'，导出到HDFS上，（结果为拷贝表的所有实际数据和元数据）

清除数据

• truncate table xxx; 只能用于内部表

第6章 查询

基本查询select ... from

• 列别名
• 算术运算符
• 常用函数：count、max、min、sum、avg
• limit
• where：比较运算符：between/in/is null/<=>、like/rlike、逻辑运算符：and/or/not

分组

• group by
• having（where只针对列发挥作用，查询数据；having针对查询结果中的列发挥作用，筛选数据；where后不能写聚合函数，而having后可以使用聚合函数；having只用于group by分组统计语句）

join语句

• Hive只支持等值join
• 表的别名：简化查询、提高执行效率
• 内连接：join，左外连接：left join，右外连接：right join，满外连接：full join
• 多表连接：多个join
• 笛卡尔积：省略连接条件、连接条件无效、所有表中的所有行互相连接

排序

• order by，全局排序，可以别名排序、多个列排序
• sort by，每个reducer内部有序，需要设置reducer个数为多个，一般和distribute by连用，按照某个列分区排序；没有distribute by时随机均匀分，避免数据倾斜
• distribute by设置分区列，结合sort by使用
• cluster by，当distribute by和sort by后面的列相同时，可以使用cluster by代替，即按同一个列进行分区和排序，不能指定asc desc只能升序排列

分桶及抽样查询

• 创建分桶表：create table xxx clustered by(id) into xx buckets;
• 按照某个字段进行分桶，将数据分成几个桶，每个桶是一个文件，使用这个字段的hash值对桶的个数进行取余，余数相同的为一个桶
• load data进去的不会分成多个桶的文件，因为load是hadoop的上传上去的，使用子查询方式insert into table xxx select ...（需要设置属性set hive.enforce.bucketing=true; set mapreduce.job.reduces=-1，hive决定reducer个数）
• 分桶抽样查询：select * from xxx tablesample(bucket x out of y on id);是将id列的hash值对y取余，x为取第几组。本质上是将数据分为y份，按照hash值取余分，x从第几组取。

其他常用查询函数

• 空字段赋值：nvl(A, B)，如果A为null用B替换，AB都为null返回null
• 时间类：
  • 格式化时间：date_format('xx-xx-xx xx:xx:xx.xx','yy-MM-dd HH:mm:ss')，参数只接受-形式
  • 时间天数相加：date_add(A, B)
  • 日期相减：datediff(A, B)
  • year(), month(), day(), hour(), minute(), second()分别取对应的参数，date()识别日期格式，
• case when 表达式 then 值1 else 值2 end，当某一列的值满足表达式时返回值1，不满足返回值2
• 行转列：多行合成一列，
  • concat(A. B)：合并A和B，若有一个为NULL则返回NULL；
  • concat_ws(separator, str1, str2), （separator为null返回null，其他参数必须是字符串），与group by连用，将分组后的多行数据合成一个数据。
  • collect_set(col)，只接受基本数据类型，作用是将字段的值进行去重汇总，产生array字段
  • collect_list(col)，不去重
• 列转行：一列转成多行，
  • explode(col)，将hive中一列复杂的array或map类型拆分成多行；如果有其他字段，必须使用lateral view
  • lateral view udtf(expression) tableAias AS columnAlias，用于和split和explode等udtf一起使用，他将一列出具拆分成多行数据，在此基础上对拆分后的数据进行聚合
• 窗口函数：over()，指定分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。用在聚合函数之后，没有参数则对每一条数据开一个窗口
  • 用在over()函数里面的，限制窗口范围，用法：在over([partition by xx order by xx] rows between xxx and xxx)
  • current row：当前行
  • n preceding：往前n行数据
  • n following：往后n行数据
  • unbouned ：起点
  • unbouned preceding：从前面的起点
  • unbounded following：到后面的起点
  • 必须用在over()之前的，窗口函数的分析函数
  • lag(col, n)：往前第n行数据
  • lead(col, n)：往后第n行数据
  • ntile(n)：把有序分区中的行分发到指定数据的组中，各个组有编号，从1开始，对于每一行，ntile返回此行所属组的编号，n必须为int（对数据分n组，每组内的编号都是相同的，从1开始）
  • 注意：over()里面不可以用group by，可以用distribute by sort by或者partition by order by
• rank：必须用在over()之前，窗口函数的分析函数
  • rank()，排序时会重复，总数不会变，1134
  • dense_rank()，排序时会重复，总数会减少，1123
  • row_number()，根据顺序计算，1234
• 其他函数
  • regexp_replace(a, b,c)，a中的b替换成c
  • substring(a, b, c)，a中截取，从b开始，截取c个
  • split(str, regex)，按照匹配regex的分割，返回数组

第7章 函数

系统内置函数

• show functions;
• desc function xxx;
• desc function extended xxx;

自定义函数

• UDF：用户自定义函数，一进一出
• UDAF：用户自定义聚合函数，多进一处
• UDTF：用户自定义Table-Generating函数，一进多出
• 自定义UDF步骤：
  • 继承org.apache.hadoop.hive.ql.UDF
  • 实现evaluate()方法，支持重载，必须有返回类型，可以返回null，但是不能为void
• 自定义UDTF步骤：
  • 继承org.apache.hadoop.hive.ql.udf.generic.GenericUDTF
  • 实现initialize()方法
  • 重写process()和close()方法
• 在hive的命令行窗口创建函数：
  • 添加jar：add jar path；
  • 创建函数create [temporary] function [dbname.] function_name as 'class_name';（一般要加库名，因为调用一般是hive -e 'xxx'或hive -f xxx，就会在默认库中创建）
  • 删除函数：drop [temporary] function [if exists] [dbname.]function_name;

第8章 压缩和存储

• Hadoop源码编译支持Snappy压缩
• Hadoop压缩配置
• 开启Map输出阶段压缩
• 开启最终输出压缩
• 文件存储格式：行存储、列存储（实际上是行列都使用的）；textfile和sequencefile是行存储的，orc和parquet是列存储的
  压缩压缩比orc > parquet > textfile，查询速度相近
• 压缩和存储相结合：如snappy压缩的orc。修改hadoop具有snappy格式压缩，需要将编译后的hadoop/lib下文件拷贝。
  使用snappy压缩的orc，没有orc默认的压缩的小，orc默认采用ZLIB压缩
  在实际的项目开发过程中，hive表的存储格式一般选择orc或parquet，压缩方式一般选择lzo或snappy

第9章 企业级调优

• Fetch抓取：在某些情况下的查询可以不必使用MR计算，hive.fetc h.task.conversion默认为more
• 本地模式：【使用本地模式】对于数据集小的情况下，为查询出发执行任务的时间可能比执行处理所有任务的时间要多，可以通过本地模式在单台节点上处理所有任务。对于小数据集，执行时间可以明显缩短。必须满足条件才会使用：数据量小于设置的localMR数据量最大值（性能好的话可以调大，默认128M），输入的文件个数小于设置的localMR文件个数最大值
• 表的优化：
  • 小表join大表（新版hive已经对小表join大表做了优化，已经没有明显区别）。
    对于大表join大表，使用空key过滤，提前将join不到的异常数据进行过滤。如果空key很多，但是数据不是异常数据，可以将key为空的字段赋一个随机值，使数据均匀的分到不同的reducer上，避免数据倾斜。
  • MapJoin：【使用MapJoin，避免使用ReduceJoin】如果不指定MapJoin或者不符合MapJoin条件，hive会在Reduce端完成join，容易发生数据倾斜，可以使用MapJoin把小表加载到内存中，在map端进行join，避免reduce处理。（默认开启，大表小表阈值是25M）
  • Group by，如果某一个key的数量很大时容易数据倾斜，可以使用负载均衡，开启两个MR任务，第一个MR任务中相同的key有可能被分到不同的Reducer中，然后再执行一个MR任务，根据第一个MR任务预处理的结果进行groupby。
    - hive.map.aggr = true：是否在 Map 端进行聚合，默认为 True
    - hive.groupby .mapaggr.checkinterval = 100000 在Map端进行聚合操作的条目数目
    - hive.groupby.skewindata = true 有数据倾斜的时候进行负载均 衡（默认是false）
  • Count(distinct)：数据量小的时候无所谓，数据量大的时候要用一个reducer去处理，导致job很难完成，可以先用group by再用count，使用两个MR执行，减少最后count的时候reducer处理的数据量
    - set mapreduce.jobreduces = 5; 设置 5 个reduce个数
  • 笛卡尔积：尽量避免笛卡尔积（join不加on条件，或条件无效，hive只能使用1个reducer完成笛卡尔积，默认关闭的）
  • 动态分区：根据表的字段自动创建分区
    - hive.exec.dynamic.partition=true 开启动态分区功能（默认 true ，开启）
    - hive.exec.dynamic.par tition.mode=nonstrict 设置为非严格模式（动态分区的模式，默认 strict ，表示必须指定至少一 个分区为静态分区， nonstrict 模式表示允许所有的分区字段都可以 使 用动态分 区。）
    - hive.exec.max.dyn amic.partitions=1000  在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。
    - hive.exec.max.dynamic.par t itio ns.pernode=100 在每个执行 MR 的节点上，最大可以创建多少个动态分区。 该参数需要根据实际 的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有 365 个值，那 么该参数就需要设置成大 于 365 如果使用默认值 100 ，则会报错。
    - hive.exec.max.created.files=100000  整个 MRJob 中，最大可以创建多少个 HDFS 文件。
    - hive.erro r.o n.empty.partition=false 当有空分区生成时，是否抛出 异常。一般不需要设置。
  • 分桶
  • 分区
• MR优化
  • 合理设置Map数
  • 合理设置Reduce数：调整方法一：
    - hive.exec.reducers.bytes.per.reducer =256000000为每个reducer处理的最大数据量
    - hive.exec.reducers.ma x =1009每个任务最大的reducer数，那么个数N=min(参数1，总输入数据量/参数2)
    方法二：
    - set mapreduce. job.reduces = 15;
  • 小文件合并：在Map执行前合并小文件，减少map数，默认的
    - set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
  • 复杂文件增加Map数：根据computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksi ze)))=block size= 128M 公式，调整maxSize小于blockSize就可以增加Map个数
    - set mapreduce.inp ut.fileinputformat.split.maxsize=100; 调整切片大小改变Map数量
• 并行执行
  - set hive.exec.parallel=true; 打开任务并行执行
  - set hive.exec.parallel.thread.number= 16; 同一个sql 允许最大并行度，默认为 8 。
• 严格模式
  - hive.mapred.mode默认nonstrict
  开启严格模式可以禁止一些危险的查询，1>分区表查询where语句中必须加分区条件限制范围，否则不允许查询。2>使用order by必须使用limit。3>限制笛卡尔积，（join连接条件无效）
• JVM重用：对于小文件场景或task特别多的场景，默认是1
  - mapreduce.job.jvm.numtasks，通常在10-20之间，具体多少需要根据业务场景测试
• 推测执行，在mapred-site.xml配置，如果因为数据量大需要执行很长时间的话开启推测执行造成的浪费很大
  - mapreduce.map.speculative为true
  - mapreduce.reduce.speculative为true
  Hive本身也提供了配置项来控制reduce-site的推测执行
  - hive.mapred.reduce.tasks.speculative.execution，（默认为true）
• 压缩
• 执行计划（explain）：explain [extended | dependency | authorization] query