Hive学习笔记

hive 1.2.1

你好，Hive

Hive 是基于 Hadoop 的一个数据仓库工具，可以讲结构化的数据文件映射为一张表，并提供类 SQL 查询功能。

本质是：将 HQL 转化成 MapReduce 程序。

  • Hive 处理的数据存储在 HDFS。
  • Hive 分析数据底层的默认实现是 MapReduce。
  • 执行程序运行在 Yarn 上。

Hive 优缺点

优点：

  1. 操作接口采用 SQL 语法，提供快速开发的能力（简单、容易上手）。
  2. 避免了去写 MapReduce，减少开发人员的学习成本。
  3. Hive 的执行延迟比较高，因此 Hive 常用于数据分析，对实时性要求不高的场合。
  4. Hive 的优势在于处理大数据，对于处理小数据没有优势，因为 Hive 的执行延迟比较高。
  5. Hive 支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

缺点：

  1. Hive 的 HQL 表达能力有限
    1. 迭代式算法无法表达
    2. 数据挖掘方面不擅长
  2. Hive 的效率比较低
    1. Hive 自动生成的 MapReduce 作业，通常情况下不够智能化。
    2. Hive 调优比较困难，粒度较粗。

Hive 的架构

圈出来的从左到右从上到下分别是：元数据、程序员的操作方式、Hadoop。

没有圈出来的那部分就是 Hive 的组成。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# hive 设置使用特定的任务队列
hive> set mapred.job.queue.name=root.xxx;



# insert 一条一条插入到表中的效率是很慢的，一条数据几十秒左右，所以有下面的从文件加载数据到表中。
# hive 从文件加载数据到 HDFS
hive> load data local inpath '/opt/module/data/stu.txt' into table student;
# 注意 stu.txt 中的数据分隔符要保持一致，这个在建表的时候设置
hive> create table student(id int, name string) row format delimited fields terminated by '\t';
# 其实 load 命令就是把 stu.txt 上传到了 hadoop 的数据库文件夹下，使用如下命令也可以起到相同效果
bash$ hadoop fs -put stu1.txt /user/hive/warehouse/student



bash$ export HADOOP_CLIENT_OPTS="-Xmx4g"
# 一般 HADOOP 集群都会配套 HIVE，hive直接用 sql 来查询数据比mapreduce简单很多。启动hive是直接用 hadoop jar 来启动的。相对于一个客户端程序。控制hive内存的就是 HADOOP_CLIENT_OPTS 环境变量中的 -Xmx。

hive 常用命令：

1
2
3
4
5
6
7
8
9
10
11
12
# -e 不进入 hive 的交互窗口执行 sql 语句
bash$ hive -e "select * from student;"

# -f SQL from files
bash$ hive -f ./hive.hql


# hive cli 中查看 hdfs 文件系统
hive> dfs -ls /;

# 查看本地文件系统
hive> ! ls /opt/module/datas;

HiveDDL

数据类型、类型转换

Hive 支持 Map String Array 等集合类型。

Hive 支持类型转换，当然有显示转换、隐式转换、强制 CAST 转换。

库-增删改查

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
show databases;

### 增 ###
create database test;  # 创建的这个数据库在 warehouse 下的 test.db，是默认数仓的位置
# create database if not exists test;  # if not exists
# create database test location '/test.db';  # location 指定存储位置，这里是放在根目录下

use test;
create table bb(id int);  # /warehouse/test.db/bb/


### 查 ###
show databases;

show databases like 'db_nam*';  # 模糊查询

查看数据库详情：显示 数据库名、描述、存储地址、拥有者名称之类的
desc database db_name;
查看扩展信息，和上面看到的其实一样
desc database extended db_name;


### 改 ###
数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。
但是可以给数据可附加信息，修改后使用 desc database 即可查看效果。
alter database db_name set dbproperties('createtime'='20170830');


### 删 ###
删除空数据库
drop database db_name;

如果删除的数据库不存在，最好采用 if exists 判断数据库是否存在
drop database db_name;
drop database if exists db_name;

如果数据库不为空，可以采用 cascade 命令，强制删除
drop database db_name cascade;

表-增删改查

创建表

1
2
3
4
5
6
7
8
9
10
11
12
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]  # 存储文件格式（JSON、ORC、TXT...）
[LOCATION hdfs_path]  # 存储位置


show create table table_name;

管理表（内部表）

默认创建的表都是管理表（内部表）。这种表，Hive 会（或多或少）控制者数据的生命周期。

Hive 默认情况下会讲这些表的数据存储在由配置项 hive.metastore.warehouse.dir 所定义的目录的子目录下。

当删除一个内部表时，Hive 也会删除这个表中的数据。

内部表不适合和其他工具共享数据。

外部表

……

内部表月外部表的互相转换

1
2
3
4
5
6
7
8
9
3desc formatted stu2;
Table Type: EXTERNAL_TABLE  #  外部表
Table Type: MANAGED_TABLE  # 内部表

# 内部表 -> 外部表
alter table stu2 set tblproperties('EXTERNAL'='TRUE');  # 这里需要大写！

# 外部表 -> 内部表
alter table stu2 set tblproperties('EXTERNAL'='FALSE');  # 大写！

分区表

分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。

Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。

在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。（谓词下推，过滤，缩减查询数据量）

分区表基本操作

  1. 引入分区表（需要根据日期对日志进行管理）

    1
    2
    3
    /user/hive/warehouse/log_partition/20190901/20190901.log
    /user/hive/warehouse/log_partition/20190902/20190902.log
    /user/hive/warehouse/log_partition/20190903/20190903.log

  2. 创建分区表语法

    1
    2
    3
    4
    5
    6
    7
    hive> create table dept_partition(
      deptno int,
      dname string,
      loc string
    )
    partitioned by (month string)  # 按月来分区，这个也表的是一个字段（列）
    row format delimited fields terminated by '\t';

  3. 加载数据到表中

    1
    2
    3
    4
    5
    hive> load data local inpath '/opt/module/datas/dept.txt' into table dept_partition partition(month='201709')

    hive> load data local inpath '/opt/module/datas/dept.txt' into table dept_partition partition(month='201708')

    hive> load data local inpath '/opt/module/datas/dept.txt' into table dept_partition partition(month='201707')

  4. 查询分区表中数据

    直接 where month=’201709’ 就可以了

  5. 增加分区

    创建单个分区

    1
    alter table dept_partition add partition(month='201706');

    同时创建多个分区，空格隔开

    1
    2
    3
    alter table dept_partition
    add partition(month='201706')
    add partition(month='201707');

  6. 删除分区

    删除单个分区

    1
    2
    alter table dept_partition
    drop partition(month='201706');

    删除多个分区，逗号隔开

    1
    2
    3
    alter table dept_partition
    drop partition(month='201706')
    , drop partition(month='201707');

  7. 查看分区表有多少分区

    1
    show partitions dept_partition;

  8. 查看分区表结构

    1
    2
    3
    4
    5
    6
    desc formatted dept_partition;

    # Partition Information
    # col_name data_type comment
      month    string
      ......
分区表注意事项

  1. 创建二级分区表

    1
    2
    3
    4
    5
    6
    7
    create table tab_name (
      id int,
      name string,
      loc string
    )
    partitioned by (month string, day string)
    row format delimited fields terminated by '\t';

  2. 正常的加载数据

    加载数据到二级分区表中

    1
    2
    3
    load data local inpath './dept.txt'
    into table tab_name
    partition(month='201709', day='13');

    查询分区数据

    1
    select * from tab_name where month='201709' and day='13';

  3. 把数据上传到分区目录上，让分区表和数据产生关联的三种方式

    • 上传数据后修复

      上传数据

      1
      2
      3
      4
      5
      6
      7
      8
      9
      hive> dfs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=12;

      hive> dfs -put /opt/module/datas/dept.txt /user/hive/warehouse/dept_partition2/month=201709/day=12;


      # 上面两句等同于下面这两句
      bash$ hadoop fs -mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=12;

      bash$ hadoop fs -put dept.txt /user/hive/warehouse/dept_partition2/month=201709/day=12;

      查询数据（查询不到刚上传的数据，因为没有操作数据库中的元数据，没有元数据，所以差不到）

      1
      hive> select * from dept_partition2 where month='201709' and day='12';

      执行修复命令

      1
      hive> msck repair table dept_partition2;

      再次查询数据

      1
      hive> select * from dept_partition2 where month='201709' and day='12';

    • 上传数据后添加分区

      1
      hive> alter table dept_partition2 add partition(month='2019-12');

    • 创建文件夹后 load 数据到分区

      创建目录

      1
      hive> dfs mkdir -p /user/hive/warehouse/dept_partition2/month=201709/day=12;

      上传数据

      1
      2
      3
      hive> load data local
      inpath '/opt/module/datas/dept.txt'
      into table dept_partition2 partition(month='201909', day='30');

      查询数据即可……

修改表

  1. 重命名表

    1
    hive> alter table table_name rename to new_table_name;

    重命名表后物理存储的文件夹的名字也改变了。（改的是元数据）

  2. 增加/删除/修改 表分区

    在上面 分区表 就有操作。

  3. 增加/修改/替换 列信息

    更新列

    1
    2
    3
    4
    hive> alter table table_name 
    change [column] col_old_name col_new_name column_type
    [comment col_comment]
    [first|after column_name]

    增加列和替换列

    1
    2
    3
    4
    hive> alter table table_name
    add|replace columns (col_name data_type [comment col_comment],
    ...
    )

    注意：replace 是整列全部替换的，如果用了 replace 后面只写了一个列，那后面这张表就只有一个列，如果指向替换表中的一个列，用 change 就够了。

删除表

1
hive> drop table table_name;

HiveDML

数据导入

数据从 HDFS 导入到 Hive 表中、从本地文件系统导入 Hive 表中。

向表中装载数据（load）

语法

1
hive> load data [local] inpath '...' [overwrite] into table student [partition(xxx=xxx, ...)];

local：表示从本地加载数据到 Hive 表，否则从 HDFS 加载数据到 Hive 表。

insert into 和 insert overwrite 的区别

overwrite 上述的数据，先现将原始表的数据 remove，再插入新数据。

insert into 只是简单的插入，不考虑原始表的数据，直接追加到表中。

通过查询语句向表中插入数据（insert）

  1. 创建一张分区表

    1
    2
    3
    4
    5
    6
    hive> 
    create table student(
      id int, name string
    )
    partitioned by (month string)
    row format delimited fields terminated by '\t';

  2. 基本插入数据

    1
    2
    hive>
    insert into table student partition(month='201709') values(1, 'ahojcn');

  3. 基本模式插入（根据单张表查询结果）

    1
    2
    3
    hive>
    insert overwrite table student partition(month='201708')
    	select id, name from student where month='201709';

  4. 多插入模式（根据多张表查询结果）

    1
    2
    3
    4
    5
    6
    hive>
    from student
    	insert overwrite table student partition(month='201707') select id, name
    where month='201709'
    	insert overwrite table student partition(month='201706') select id, name
    where month='201709'

####查询语句中创建表并加载数据（as Select）

根据查询结果创建表（查询的结果会添加到新创建的表中），这个也可以用来导入数据。

1
2
3
hive>
create table if not exists student
as select id, name from student;

创建表时通过 location 指定加载数据路径

  1. 创建表，并指定在 HDFS 上的位置

    1
    2
    3
    4
    5
    6
    hive>
    create table if not exists student (
      id int, name string
    )
    row format delimited fields terminated by '\t'
    location '/user/hive/warehouse/student'

  2. 上传数据到 HDFS 上

    1
    2
    hive>
    dfs -put /opt/module/datas/student.txt /user/hive/warehouse/student;

  3. 查询数据

    1
    2
    hive>
    select * from student;

import 数据到指定 Hive 表中

注意先用 export 导出后，再将数据导入。

1
2
3
hive>
import table student partition(month='201709')
	from '/user/hive/warehouse/export/stu';

数据导出

insert 导出

  1. 将查询的结果导出到本地

    这样导出的数据是没有分隔符的

    1
    2
    3
    hive>
    insert overwrite local directory '/opt/module/datas/export/student'
      select * from student;

  2. 将查询的结果格式化导出到本地

    1
    2
    3
    4
    hive>
    insert overwrite directory '/opt/module/datas/export/student1'
    	row format delimited fields terminated by '\t'
    	select * from student;

  3. 将查询的结果导出到 HDFS 上（没有 local）

    1
    2
    3
    4
    hive>
    insert overwrite directory '/user/atguidu/student2'
    	row format delimited fields terminated by '\t'
    	select * from student;

Hadoop 命令导出到本地

1
2
hive>
dfs -get /user/hive/warehouse/student/month=201709/000000_0 /opt/module/datas/export/student3.txt;

Hive Shell 命令导出

基本语法：hive -f/-e 执行语句或者脚本 > file，重定向！

1
bash$ hive -e 'select * from db_name.tab_name' > /opt/module/datas/export/student4.txt;

export 导出到 HDFS 上

1
2
hive> 
export table db_name.tab_name to '/user/hive/warehouse/export/student';

sqoop 导出

SQL to Hadoop，后面学习……

清除表中数据（Truncate）

注意：Truncate 只能删除管理表，不能删除外部表中数据。

1
2
hive>
truncate table student;

查询

常用函数

  1. count，求总行数

    1
    2
    hive>
    select count(*) cnt from tab_name;

  2. max，求最大值

    1
    2
    hive>
    select max(salary) max_salary from tab_name;

  3. min

  4. sum，求和

    1
    select sum(sqlary) sum_salary from tab_name;

  5. avg，求平均值

where 语句

where 子句紧随 from 子句

1
select * from emp where sal>1000;

名词：调优手段，谓词下推。

比较运算符 between/in/is null

<>，!=这两个是一个意思。

<=>，如果 A 和 B 都为 NULL，则返回 True，其他的和等号(=)操作结果一直，如果任一为 NULL 则返回 NULL。

其他的比较符都一样。

1
2
3
select 'b' between 'a' and 'c';  # true，注意左右都是闭区间
select 'a' in ('a', 'b', 'c');  # true
select 'a' is NULL;

like 和 rlike

使用 like 运算选择类似的值。

选择条件可以包含字符或数字：
% 代表零个或多个字符（任意个字符）
_ 代表一个字符。

rlike 子句是 Hive 中的一个扩展，其可以通过 Java 的正则表达式配合进行筛选。

逻辑运算符 and/or/not

……

分组

group by 子句

group by 语句通常会和聚合函数一起使用，按照一个或者多个队列结果进行分组，然后对每个组执行聚合操作。

栗子🌰：

  1. 计算 emp 中每个部门的平均工资：

    1
    2
    3
    hive>
    select t.deptno, avg(t.sal) avg_sal from emp t
    	group by t.deptno;

  2. 计算 emp 每个部门中每个岗位的最高薪水：

    1
    2
    3
    hive>
    select t.deptno, t.job, max(t.sal) as max_sal from emp as t
    	group by t.deptno, t.job;

having 子句

having 和 where 不同点

  1. where 针对表中的列发挥作用，查询数据；having 针对查询结果中的列发挥作用，筛选数据。
  2. where 后面不能写分组函数，而 having 后面可以使用分组函数。
  3. having 只用于 group by 分组统计语句。

栗子，求每个部门的平均薪水大于 2000 的部门：

  1. 求没个部门的平均工资

    1
    2
    hive>
    select deptno, avg(sal) from emp group by deptno;

  2. 求每个部门的平均薪水大于 2000 的部门

    1
    2
    3
    hive>
    select deptno, avg(sal) as avg_sal from emp group by deptno
    	having avg_sal>2000;

join 子句

Hive 支持通常的 SQL JOIN 语句，但是只支持等值连接，不支持非等值连接。

join 默认是 inner join。

等值 join

1
2
3
4
5
6
7
8
9
10
hive>
select e.deptno, e.name from emp as e
	join dept as d
	on e.deptno = deptno;
	
# 但是不支持下面的
hive>
select e.deptno, e.name from emp as e
	join dept as d
	on e.deptno != deptno;  # 或者 > < 之类的操作

表的别名

好处：

  1. 使用别名可以简化查询。
  2. 使用表名前缀可以提高执行效率。

内连接 join

只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。

左连接 left join

join 操作符左边表中符合 where 子句的所有记录将会被返回。

1
2
3
4
hive>
select e.empno, e.name, d.deptno from emp as e
	left join dept as d
	on e.deptno = d.deptno;

右连接 right join

join 操作符右边表中符合 where 子句的所有记录将会被返回。

满外连接 full join

将会返回所有表中符合 where 语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用 NULL 替代。

MySQL 中没有满外连接，MySQL 中可以使用 or 来自己实现满外连接。

多表连接

连接 n 个表，至少需要 n-1 个链接条件。

例如：A join B on xxx=xxx join C on xxx=xxx;

笛卡尔积

笛卡尔集会在以下条件中产生

  1. 省略连接条件
  2. 连接条件无效
  3. 所有表中的所有行互相连接

例如：select empno, dname from emp, dept;

连接谓词中不支持 or

支持 and。

栗子：

1
2
3
hive>  # 错误栗子
select e.empno, e.ename, d.deptno
	from emp as e join dept as d on e.deptno=d.deptno or e.ename=d.dname;

如果有这样的需求的话，用子查询的方式来处理。

赏

谢谢你请我吃糖果

支付宝
微信

扫一扫，分享到微信

微信分享二维码

tag:

    缺失模块。
    1、请确保node版本大于6.2
    2、在博客根目录（注意不是yilia根目录）执行以下命令：
    npm i hexo-generator-json-content --save

    3、在根目录_config.yml里添加配置： 

      jsonContent:
    meta: false
    pages: false
    posts:
      title: true
      date: true
      path: true
      text: false
      raw: false
      content: false
      slug: false
      updated: false
      comments: false
      link: false
      permalink: false
      excerpt: false
      categories: false
      tags: true