hive
简介
hive是基于Hadoop的一个数据仓库工具，可以将结构化的数据映射为一张表，它提供了一系列的工具，可以用来进行数据提取、转化和加载。hive定义了简单的类SQL查询语言，称为HiveSQL。hive在执行过程中会将HQL转换为MapReduce执行，所以它本质上还是一种离线的大数据分析工具，由于hadoop通常会在作业提交和调度时有很大的开销，有较高延迟，因此hive也不能再大规模数据集上实现低延迟的快速查询。hive的最佳适用场景是大数据集的批处理作业，例如网络日志的分析。
数据仓库的特征：
- 由多种异构数据组成；
- 一般是历史数据，用来读和分析，所以是弱事物；
- 数据库是为捕获数据，数据仓库是为分析数据的；
- 数据是时变的，即数据包含时间元素；
数据库属于OLTP，即联机事物处理系统，一般用来各种具体的业务处理；数据仓库属于OLAP系统，即联机分析处理系统，一般用来分析数据。
数据仓库与数据库的对比：
| 数据仓库 | 数据库 |
|---|---|
| 为离线分析存储历史数据 | 为线上提供实时数据 |
| 只支持一写多读，不支持行级改 | 具有完善的crud功能 |
| 不强调事物 | 具有完整的事物能力 |
| 制造冗余，提高查询效率 | 尽量避免冗余 |
| 数据来源多样 | 数据来源单一 |
| OLTP | OLAP |
安装：需要jdk环境、hadoop环境、解压即可使用；
运行：运行hive之前需要启动HDFS和MapReduce，启动命令：./bin hive；
注意：hive会将使用中产生的元数据会放在derby数据库，而derby是文件式数据库，而且是在哪里启动的hive，derby文件就在那个目录，更换目录元数据就丢失，而且同时只能连接一个客户端，这很不方便，所以需要借助mysql来保存hive的元数据。
基本语句
hive常用命令如下：
show databases：显示数据库；create database test01：创建数据库；（其实是一个目录）use test01：切换到test01数据库；create table stu(id int, name string) row format delimited fields terminated by ‘ ’：创建stu表，包含id、name两个字段，且分隔符为空格；（其实就是一个目录）desc stu：查看表结构；insert into stu values(1, "张三")：插入数据（一般不用）；select * from stu (where id=1)：查询，可以跟条件；load data local inpath '/data/1.txt' into table stu：加载本地文件保存到数据库（HDFS）；create table stu2 like stu：创建一个结构和stu一样的表；insert overwrite table stu2 select * from stu：将查询结果覆插入到stu2中；from stu insert overwrite table stu2 select * insert overwrite table stu2 select *：将stu的查询结果覆盖插入到stu2和stu3中insert overwrite local directory ‘/data/1.txt' row format delimited fields terminated by ' ' select * from stu：将查询结果保存到本地文件（去掉local关键字则是保存到HDFS中）；alter table stu2 rename to stu3：将stu2表重命名为stu3；drop table stu3：删除stu3表；- hql中连表有：内连接（inner join，不能简写）、左连接（left join）、右连接（right join）、全连接（full join）；
表概念
内部表
在上面的基本指令中，默认创建的表就是内部表，由hive先建一张表，然后向这个表里插入数据，这种表不常用；
外部表
外部表是在HDFS中已经有了固定格式的数据，然后在hive中创建外部表处理这部分数据，外部表可以管理指定目录下的所有文件，要求是格式统一。删除外部表不会删除真实的文件。创建命令：create external table stu1(id int, name string, score int) row format delimited fields terminated by ' ' location '/hdfs/dir'。
分区表
即对数据进行分区，每个分区一个目录，这样可以避免整表查询从而提高查询效率，外部表和内部表都可以是分区表。分区表在导入数据时需要在hql语句末尾添加**partition(type=‘xx’)**指定分区；实际效果在HDFS中就是一个分区对应一个目录，分区字段就对应目录名，为字段名=字段值（type=xx），相关命令如下。
- 创建内部分区表：
create table stu(id int, name string) partitioned by (type string) row format delimited fields terminated by ' '; - 创建外部分区表：
create external table stu(id int, name string) partitioned by (type string) row format delimited fields terminated by ' ' location '/hdfs/dir'； - 添加分区：
alter table stu add partition(type='xx1') location '/hdfs/type=xx1';或者msck repair table book; - 查看分区：
show partitions stu; - 删除分区：
alter table stu drop partition(type='xx1'); - 修改分区名：
alter table stu partition(type='xx1') rename to partition(type='xx2');
分桶表
分桶表概念：
- 分桶表是一种更细粒度的数据分配方式；
- 一个表可以分区也可以分桶；
- 分桶的主要作用是用于实现数据抽样；
- 分桶通过hash算法将数据分布在不同的桶中；
- 默认不开启，需要手动开启；
- 分桶表只能从另一张表导入数据（经测试，并不是）；
- 涉及到Join操作时，可以在桶与桶间关联即可，大大减小Join的数据量，提高执行效率。
- 物理上每个桶就是目录里的一个文件，一个作业产生的桶（输出文件）数量和reduce任务个数相同。
使用分桶表：
- 开启分桶机制：set hive.enforce.bucketing=true；
- 创建分桶表：
create table student(name string, age int) clustered by (name) into 3 buckets row format delimited fields terminated by ' '; - 进行抽样：
select * from student tablesample(bucket 1 out of 6 on name)；（1表示从第1个桶开始抽样；6表示抽样步长和比例，即抽取桶数/6个桶的数据，每隔6个桶抽取一次，这个值只能是桶数的因数或倍数）；
事务表
hive0.14之后开始支持事务和行级更新，但缺省不支持，需要额外的属性配置，还需要是分桶表且为orc格式；使用步骤如下：
- hive-site.xml中进行如下配置：
<property>
<name>hive.support.concurrency</name>
<value>true</value>
</property>
<property>
<name>hive.exec.dynamic.partition.mode</name>
<value>nonstrict</value>
</property>
<property>
<name>hive.txn.manager</name>
<value>org.apache.hadoop.hive.ql.lockmgr.DbTxnManager</value>
</property>
<property>
<name>hive.compactor.initiator.on</name>
<value>true</value>
</property>
<property>
<name>hive.compactor.worker.threads</name>
<value>1</value>
</property>
<property>
<name>hive.enforce.bucketing</name>
<value>true</value>
</property>
- 创建事务表：
create table t1(id int,name string) clustered by (id) into 3 buckets row format delimited fields terminated by ',' stored as orc；
事务表一般不用
数据类型
array
# 数组类型可以通过size(info)获取数组长度
create external table t1(info array<int>) row format delimited fields terminated by ' ' collection items terminated by ',' location '/hdfs/1.txt'
map
# 列分隔符只能是\t
create external table t1(info map<string,int>) row format delimited fields terminated by '\t' map keys terminated by ',' location '/hdfs/1.txt'
# 一个map的查询示例
select distinct(info['k']) from t1 where info['k'] is not null;
struct
类似于Javabean
# 没有列分割符了
create external table t1(info struct<id:int,name:string,age:int>) row format delimited collection items terminated by ' ' location '/hdfs/1.txt'
# 使用struct示例
select info.age from t1 where info.naem='xx'
常用操作函数
- length(str)：返回字符串长度；
- reverse(str)：反转字符串；
- concat(str1, str2, ···)、concat_ws(ws, str, str, ···)：拼接字符串、带拼接符的字符串拼接；
- substr(str, start, end)：截取字符串，位置可以为负数，表示倒数；
- upper(str)、lower(str)：转大/小写；
- trim(str)、ltrim(str)、rtrim(str)：去掉空格，去掉左边空格，去掉右边空格；
- regexp_replace(str, pattern, str)：正则替换；
- regexp_extract(str, pattern, int)：正则搜索；
- repeat(str, int)：重复字符串n此；
- split(str, str)：切分为数组，参数为：字符串、切割符；
- explode(array)：将数组切分为多行；
UDF
UDF即用户自定义函数，使用方法如下：
- 将hive的lib中的jar包加到工程中；
- 创建类继承UDF类， 编写**evaluate()**方法；
- 编写完成后打成jar包，拷贝到hive的lib目录下；
- 执行hive命令：add jar /data/hive/xxx.jar；
- 执行hive命令：create temporary function myfuc as ‘com.xxx’;
调优
group by的数据倾斜问题：
- set hive.map.aggr=true：相当于添加Combiner；
- set hive.groupby.skewindata=true；该参数本质是生成两个MRJob查询计划。第一次MR会在key上拼接随机字符串，从而使相同的key可能输出到不同的reduce中，第二次才会真正的实现将相同的key输出到相同的reduce中，从而达到最终的分区效果；
join的数据倾斜问题：
- 大小表的join：使用map join代替join，本质是将小表缓存到map端，并在map端就完成join；
- 在0.11之前是显示在select后面跟
/*+mapjoin(t1)*/ - 在0.11之后由两个参数来控制：①hive.auto.convert.join=true(默认开启)；②hive.mapjoin.smalltable.filesize=25000000(默认25M)。
- 0.11之后也可以通过指定两个参数后使用第一种方式进行mapjoin：①hive.auto.convert.join=false；②hive.ignore.mapjoin.hint=false；
- 在0.11之前是显示在select后面跟
- 大表join大表：将造成数据倾斜的字段单独处理，然后在union其他结果；
连表查询的优化，在hive中，如果连表查询有查询条件，最好改为子查询（原因是count计数操作在hive中只会用一个reduce来实现，所以要先过滤出数据再计数），例如：
# 优化前：
select * from t1 a join t2 b on a.cid=b.id where a.dt='2019-10' and b.price>100;
# 优化后：
select * from (select * from t1 where dt='2019-10') a join (select * from t2 where price>100) b on a.cid=b.id;
参数调优
- 调整切片大小：set mapred.max.split.size=134217728（默认128MB）；
- JVM重用：set mapred.job.reuse.jvm.num.tasks=1(默认1个)；
- 关闭推测执行机制：set mapred.map.tasks.speculative.execution=false和set mapred.reduce.tasks.speculative.execution=false；
- 启用严格模式：set hive.mapred.mode=strict，启用严格模式后在以下情况会报错：
- 查询分区表没有指定分区字段；
- 使用了order by但是没有使用limit；
- 产生了笛卡尔积；
- 切换执行引擎：set hive.execution.engine=spark；（默认为mr）
- 设置reduce数量：set mapreduce.job.reduces=2；
- hive.exec.reducers.bytes.per.reducer=1000000000 ；(hive默认每1G文件分配一个reduce)
- 不要使用count (distinct cloumn) ，使用子查询
sqoop
sqoop：用于HDFS和关系型数据库的数据的相互导入，还可以将数据从关系数据库导出到HBASE，安装sqoop需要jdk和hadoop（导出到Hbase还需要hbase的）的环境变量，并且需要将相关的驱动包（例如mysql驱动包）拷贝到sqoop的lib下，最后在./conf/sqoop-env.sh文件中修改相关的环境变量即可；
基础指令：
- 查看mysql数据库：
./sqoop list-databases --connect jdbc:mysql://hdp01:3306/ -username root -password 1234； - 查看表：
./sqoop list-tables --connect jdbc:mysql://hdp01:3306/db1 -username root -password 1234； - mysql->hdfs：
./sqoop import --connect jdbc:mysql://hdp01:3306/db1 --username root --password 1234 --table t1 --target-dir '/data/t1' --fields-terminated-by '|' -m 1；（还可以通过–query参数指定SQL查询，如：--query 'select * from db1.t1 where $CONDITIONS and id > 10'） - hdfs->mysql：
- 先在mysql建表，注意字段类型要一致；
./sqoop export --connect jdbc:mysql://hdp01:3306/db1 --username root --password root --export-dir '/data/t1/' --table t1 -m 1 --fields-terminated-by '|'；
- mysql->hbase：
./sqoop import connect jdbc:mysql://hdp01:3306/db1 --username root --password 1234 --table t1 --hbase-table t1 --column-family info --hbase-row-key sid --hbase-create-table； - 查看帮助：./sqoop help，也可以：./sqoop import help
处理json
- 拷贝jar包hive-hcatalog-core-1.2.0.jar到lib目录下；
- 在hive客户端中执行命令：add jar /…/lib/xxx.jar；
- 创建与json格式对应的表：
create table if not exists t1(id int,name String) row format serde 'org.apache.hive.hcatalog.data.JsonSerDe' stored as textfile； - 加载数据：
load data local inpath '/data/xxx.json' into table t1；
1224
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
