hive常用知识点汇总

hive常用知识点汇总

1.了解啥是hive
问题 : 啥是hive?

Hive是一个基于Hadoop的开源 数据仓库工具，用于 存储 和 处理 海量结构化数据。
Hive 使用 HDFS 作为数据存储介质 使用MapReduce 作为数据计算引擎
Hive 是 Facebook 2008年8月开源的一个数据仓库框架，提供了类似于SQL语法的 HQL（hiveSQL）语句作为数据访问接口。

问题 : hive能做什么呢?
利用其灵活的SQL语法可以帮我我们进行复杂统计分析之类的活。

2.hive的优缺点
优点：

1）Hive 使用类SQL 查询语法, 最大限度的实现了和SQL标准的兼容，大大降低了传统数据分析人员处理大数据的难度
2）使用JDBC 接口/ODBC接口，开发人员更易开发应用；
3）以MR 作为计算引擎、HDFS 作为存储系统，为超大数据集设计的计算/ 扩展能力；
4）统一的元数据管理（Derby、MySql等）表存在哪 数据库在哪 和SQL相关的对象都保存在哪 保存在哪 谁创建的 啥时候修改，并可与Pig 、spark等共享；

缺点 ：
1）Hive 的 HQL 表达的能力有限，比如不支持UPDATe、非等值连接、DELETE、INSERT单条等；
2）由于Hive自动生成MapReduce 作业， HQL 调优困难；
3）粒度较粗，可控性差，是因为数据是读的时候进行类型的转换，mysql关系型数据是在写入的时候就检查了数据的类型。
4）hive生成MapReduce(shuffle失误)作业，高延迟，不适合实时查询。hive 最害怕的就是数据倾斜问题 --> OOM

3.一般Hive的元数据是保存在MySQL中的

4.一般hive client和metastore 不是在一台机器上的，配置完成后，要启动metastore服务
nohup hive --service metastore > /dev/null 2>&1 &

5.分区Partitions就是分目录。的目的是查询时不扫描全表，只扫描指定分区目录的文件，可有效提高查询效率。

6.桶Buckets，就是分文件。它是按照某几列hash进行划分，是更细粒度的数据范围。分几个桶就有几个文件。
桶表抽样的时候，查询不扫描全表，只抽指定桶的文件的数据。

7.分区分桶的区别：分区只能按字段的value分区，而分桶则可以按照字段的value的HashPartitioner分区。
共同的作用就是：可以减少mr作业的时间。
分桶更利于抽样，因为它已经分好文件了，随机抽几个桶就可以了。

8.HIVE的数据类型
STRUCT类似于java的自定义类型使用，定义的时候有两种方式，一种是只有字段值，还有一种就是带有字段名和字段值，这种可以构成key val结构，推荐使用这种。

1. struct(val1, val2, val3, …) ，只有字段值；
2. named_struct(name1, val1, name2, val2, …)，带有字段名和字段值； <-- 推荐使用 可以构成 key val 结构

9.RCFile采用游程编码，相同的数据不会重复存储，节省空间。ORC格式是对RCFile的优化，ORC有索引机制，查询速度比RCFile要快很多

10.hive常用的表有内部表（CREATE）和外部表(CREATE EXTERNAL)

--创建 inner_test 表
CREATE TABLE inner_test(word string, num int); 

--创建外部表的语法格式
CREATE EXTERNAL TABLE ext_test(
col_name data_type ...)
 COMMENT 'This is ext_testtable'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY '01' 
 STORED AS TEXTFILE
 LOCATION '';
 
--创建外部表ext_test
--使用字符‘01’作为列分隔符
--文件数据是纯文本
--数据存放的hdfs地址：/user/panniu/hive/ext_test'
create external table ext_test(
word string,num int
)
COMMENT '创建一张测试外部表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '01'
STORED AS TEXTFILE
LOCATION 'hdfs://ns1/hive/warehouse/hn19_db1.db/ext_test';

11.创建分区表

--内部表，带分区
CREATE TABLE student_par(
name STRING
)
PARTITIonED BY (age int,sex STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY 't';

--外部表，带分区，要指定位置
CREATE EXTERNAL TABLE student_par(
name STRING
)
PARTITIonED BY (age int,sex STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY 't'
LOCATION '/user/suniu/hive/student_par';

add partitions的时候，如果不指定location路径就给你创建一个
我的经验 : 一定要指定我们的分区的location 如果不指定的话会破坏分区格式。

--添加partition
alter table student_par add IF NOT EXISTS partition(age=11,sex='girl');

举个例子，感受更深

--只扫描分区表，不扫描全表
select count(*) from student_par where age=10 and sex='girl';

--扫描全表
select count(*) from student_par ;

12.AVRO表
它的主要特点有：支持二进制序列化方式，可以便捷，快速地处理大量数据；动态语言友好，Avro提供的机制使动态语言可以方便地处理Avro数据。
Avro所提供的属性：

1.丰富的数据结构
2.使用快速的压缩二进制数据格式
3.提供容器文件用于持久化数据
4.远程过程调用RPC
5.简单的动态语言结合功能，Avro 和动态语言结合后，读写数据文件和使用 RPC 协议都不需要生成代码，而代码生成作为一种可选的优化只值得在静态类型语言中实现。

AVRO : 带有schema文件格式的(主要用JSON的格式), 用于保存结构化数据,内部用一个map存储数据,优点是可以动态扩展数据,缺点是因为格式限定必须是key:val 所以avro文件都比较大,所以我们都是把公司所有技术组用的数据保存到avro表中(使用mapreduce清洗完存入其中) 然后各分部门如果想使用数据的话执行特定的HQL与将数据保存在ORC表里,然后针对orc表进行 *** 作
总结：
如果新增字段，需要给新增字段设置默认值，否则查询会报错。
优点：后续数据的字段扩展不影响以前表的使用，或者后续表的修改不影响读取以前的数据。
缺点：做在数据里面存在冗余的数据，会使数据的文件变的很大。
应用场景：最原始的etl数据使用，因为最原始的数据经常变动结果。使用这种数据格式不受影响。

13.orc表，查询的时候不需要执行mapreduce，说明orc表存在索引

14.Bucket（桶）表的使用
总结：桶表就是对一次进入表的数据进行文件级别的划分。
公式：whichBucket = hash(columnValue) % numberOfBuckets
公式：要往哪个桶存 = hash(列值) % 桶的数量
hive桶表最大限度的保证了每个桶中的文件中的数据量大致相同，不会造成数据倾斜。

--创建外部表桶表,桶的数量是42
CREATE EXTERNAL TABLE user_install_status_buckets(
`aid` string, 
`pkgname` string, 
`uptime` bigint, 
`type` int, 
`country` string, 
`gpcategory` string)
COMMENT 'This is the buckets_table table'
PARTITIonED BY (`dt` string)
CLUSTERED BY (country) SORTED BY(uptime desc) INTO 42 BUCKETS
LOCATION 'hdfs://ns1/user/suniu/hive/user_install_status_buckets';

15.桶表抽样
当数据量特别大时，对全体数据进行处理存在困难时，抽样就显得尤其重要了。
举例：

#建表语句
create table () clustered by (user_id) into 32 buckets
#查询语句
select * from .. TABLESAMPLE(BUCKET 3 OUT OF 8 ON user_id) 
#建表语句有 clustered by,查询时sample字段与create的cluster by字段一致，可以直接返回对应桶的数据 
#取32/8=4个桶, 这四个分别是：3+8*0  3号桶  3+8*1 11号桶  3+8*2 19号桶 3+8*3 27号桶。

16.hive -e “sql” --> 推荐 小规模SQL

-- 导出的数据导出到out1
-- 错误的信息，导出到err.log
nohup hive -e "use db1;select r_key, r_val from inner_task where taskname='wordcount';" 1>./out1 2>./err.log &

# 显示结果输出重定向 hive有关

hive -f sqlfile 大规模SQL

hive -f ./test.sql 1>./out2 2>>./err.log

18.各种排序

hive排序：distribute by 、sort by 、cluster by 、order by 区别
1). order by 只有一个reduce负责对所有的数据进行排序，若大数据量，则需要较长的时间。建议在小的数据集中使用order by 进行排序。
2). order by 可以通过设置hive.mapred.mode参数控制执行方式，若选择strict，则order by 则需要指定limit（若有分区还有指定哪个分区） ；若为nostrict，则与关系型数据库差不多。
3). sort by 基本上不受hive.mapred.mode影响，可以通过mapred.reduce.task 指定reduce个数，查询后的数据被分发到相关的reduce中。
4). sort by 的数据在进入reduce前就完成排序，如果要使用sort by 是行排序，并且设置map.reduce.tasks>1,则sort by 才能保证每个reducer输出有序，不能保证全局数据有序。
5). distribute by 采集hash算法，在map端将查询的结果中hash值相同的结果分发到对应的reduce文件中。
6). distribute by 可以使用length方法会根据string类型的长度划分到不同的reduce中，最终输出到不同的文件中。 length 是内建函数，也可以指定其他的函数或者使用自定义函数。
7). cluster by 除了distribute by 的功能外，还会对该字段进行排序，所以cluster by = distribute by +sort by 。

18.hive性能调优
①大小表的时候开启mapjoin，系统会自动识别小表
②group by产生数据倾斜的时候开启负载均衡，系统回用两个mr来解决问题