Hive基本操作

第一章 数据定义

1.1 数据库操作

1.1.1 创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]    // 库的注释说明
[LOCATION hdfs_path]    // 库在hdfs中的路径
[WITH DBPROPERTIES (property_name=property_value, ...)];    // 库的属性

• if not exists：防止数据库已经存在
• 示例：创建exampledb数据库，放在hdfs上的/example目录下，注释为this is a example，设置属性owner=tom，empid=1001

create database if not exists exampledb
comment 'this is a example'
location '/example'
dbproperties('owner'='tom','empid'='1001');

1.1.2 删除数据库

• 若不加cascade只能删除空库，加上可以删除非空库，if exists用于判断数据库是否存在，若不加上，若数据库不存在会报错

drop database [if exists] 库名 [cascade];

• 示例：判断example库是否存在，并删除库中所有内容

drop database if exists example cascade;

1.1.3 修改数据库

• 用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的，包括数据库名和数据库所在的目录位置。修改数据库的属性值，同名的属性值会覆盖，之前没有的属性值会新增。

alter database 数据库名 set dbproperties(property_name=property_value, ...);

• 示例：将example库的所有者改为jack，编号1002，新增创建时间属性

alter database example dbproperties('owner'='tom','empid'='1002','createtime'='20210130');

1.1.4 查询数据库

• 切换数据库

use 数据库名;

• 示例：使用default库

use default;

• 查看当前所有数据库

show databases;

• 查看数据库中所有表

show tables in 库名;

• 示例：查看example中所有表

show tables in example;

• 查看数据库的描述信息，加上extended，可以显示库的详细描述信息

desc database [extended] 库名;

• 示例：查看example库的详细描述信息

desc database extended example;

第二章 表的操作

2.1 创建表的基本语法

• 在建表语句执行的时候，hive会在hdfs上生成表的路径，并向MySQL的metastore库中国呢插入表的元数据信息。

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]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement];

• 各个字段含义
  • create table：创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。
  • external：EXTERNAL关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
  • comment：为表和列添加注释
  • partitioned by：创建分区表
  • clustered by：创建分桶表
  • sorted by：不常用，对桶中的一个或多个列另外排序
  • row format：表中数据每行的格式，定义数据字段的分隔符，集合元素的分隔符等
  • stored as：指定存储文件类型。常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）。如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。
  • location：指定表在HDFS上的存储位置。
  • as：后跟查询语句，根据查询结果创建表。
• 示例：创建一个普通表

create table if not exists student(id int, name string)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student';

• 示例：根据查询结果创建表

create table if not exists student1 as select id,name from student;

2.2 外部表和管理表

2.2.1 理论

• 默认创建的表都是所谓的管理表，有时也被称为内部表。因为这种表，Hive会（或多或少地）控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/user/hive/warehouse)所定义的目录的子目录下。   当我们删除一个管理表时，Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。

• 内部表(管理表)在执行删除操作时，会将表的元数据(schema)和表位置的数据一起删除，外部表在执行删除表操作时，只删除表的元数据(schema)

2.2.2 外部表

• 因为表是外部表，所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。
• 示例：创建一个外部表

create external table stu_external(
id int,
name string)
row format delimited fields terminated by '\t'
location '/student'

2.2.3 管理表与外部表之间转换

• 修改内部表为外部表

alter table student2 set tblproperties('EXTERNAL'='TRUE');

• 修改外部表为内部表

alter table student2 set tblproperties('EXTERNAL'='FALSE');

2.3 分区表

• 分区表实际上就是对应一个HDFS文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

• 分区目的就是为了将数据分散到多个子目录中，在执行查询时，可以只选择查询某些子目录中的数据，加快查询效率。

• 分区目录由两部分组成：分区列列名=分区列列值，将输入导入到指定的分区之后，数据会附加上分区列的信息，分区的最终目的是在查询时，使用分区列进行过滤

2.3.1 分区表的基本操作

• 创建分区表

create external table if not exists default.deptpart1(
deptno int,
dname string,
loc int)
PARTITIONED BY(area string)
row format delimited fields terminated by '\t';

• 创建多级分区表

create external table if not exists default.deptpart2(
deptno int,
dname string,
loc int)
PARTITIONED BY(area string,province string)
row format delimited fields terminated by '\t';

• 分区的查询

show partitions 表名;

• 创建分区方法一
  • 会进行以下操作：
    • 在hdfs上生成分区路径
    • 在mysql中metastore.partitions表中生成分区的元数据

 alter table 表名 add partition(分区字段名=分区字段值);

• 创建分区方法二
  • ​​​​​​​直接使用load命令想分区加载数据，如果分区不存在，load时自动帮我们生成分区

load data [local] inpath '数据存放路径' into table 分区表明 partition(分区列名=分区列值，多级分区用逗号隔开);

2.3.2 分区表注意事项

• 如果表是个分区表，在导入数据时，必须指定向哪个分区目录导入数据
• 如果表是多级分区表，在导入数据时，数据必须位于最后一级分区的目录

2.4 分通表

• 上面分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。
• 分桶是将数据集分解成更容易管理的若干部分的另一个技术
• 分区针对的是数据的存储路径，分桶针对的是数据文件
• 分桶的意义是也是为了分散数据，可以结合hive提供的抽样查询，只查询指定桶的数据。
• 在分桶时，也可以指定将每个桶的数据根据一定的规则来排序，如果需要排序，那么可以在CLUSTERED BY后根SORTED BY。

2.4.1 创建分桶表

• 创建一个分桶表，将数据分为4桶，按照id分桶，并按照id降序排序

create table stu_buck(id int, name string)
clustered by(id) 
SORTED BY (id desc)
into 4 buckets
row format delimited fields terminated by '\t';

2.4.2 分桶表导入数据

• 向分桶表中导入数据必须执行MapReduce程序，才能实现分桶操作，故不能使用load方式向分桶中导入数据，load方式只是执行put操作无法满足分桶导入数据。
• 通常我们可以先建立一个临时表，先用load向临时表中导入数据，再用insert向分桶表中导入数据。
• 在导入数据之前需要先打开强制分桶开关

set hive.enforce.bucketing=true;

• 若要进行排序，需要打开强制排序开关

set hive.enforce.sorting=true;

• 使用insert into导入数据

insert into table 表名 values(),(),();
或
insert into table 表名 select 语句;

• 示例

insert into table stu_buck select * from stu_buck_tmp;

2.4.3 抽样查询

• 格式

select * from 分桶表 tablesample(bucket x out of y on 分桶表分桶字段);

• 要求
  • 抽样查询的表必须是分桶表
  • bucket x out of y on 分桶表分桶字段
    • x：从当前表的第几桶开始抽样，0<x<=y
    • y：z/y 代表一共抽多少桶，要求y必须是z的因子或倍数，z为总桶数
    • 从第x桶开始抽样，每间隔y桶抽一桶，直到抽满 z/y桶
• 示例：从第1桶(0号桶)开始抽，抽第x+y*(n-1)，一共抽2桶   ： 0号桶,2号桶

select * from stu_buck tablesample(bucket 1 out of 2 on id);

2.5 表的其他操作

2.5.1 删除表

drop table 表名;

2.5.2 查看表

• 查看表的描述

desc 表名;

• 查看表的详细描述

desc formatted 表名;

第三章 DML数据操作

3.1 数据导入

3.1.1 向表中装载数据

• 语法

load data [local] inpath '路径' [overwrite] into table 表名 [partition (partcol1=val1,…)];

• 说明
  • load data：表示加载数据
  • local：表示从本地加载数据到hive表；否则从HDFS加载数据到hive表
  • inpath：表示加载数据的路径
  • overwrite：表示覆盖表中已有数据，否则表示追加
  • into table：表示加载到哪张表
  • partition：表示上传到指定分区
• 示例：加载本地文件到student表中

load data local inpath '/opt/module/datas/student.txt' into table default.student;

• 示例：加载hdfs中的文件到student表中，并覆盖原有的数据

load data inpath '/user/atguigu/hive/student.txt' overwrite into table default.student;

3.1.2 通过查询语句向表中插入数据(insert)

• insert方式运行MR程序，通过程序将数据输出到表目录。
• 在某些场景，必须使用insert方式来导入数据
  • 向分桶表插入数据
  • 果指定表中的数据，不是以纯文本形式存储，需要使用insert方式导入
• 基本插入模式语法

insert into|overwrite table 表名 select xxx|values(),(),();

• 说明
  • insert into：向表中追加新的数据
  • insert overwrite： 先清空表中所有的数据，再向表中添加新的数据 
• 示例：向分区表student指定分区中插入数据

insert into table  student partition(month='201709') values(1,'wangwu');

• 多插入模式语法

from 源表
insert xxxx  目标表  select xxx
insert xxxx  目标表  select xxx
insert xxxx  目标表  select xxx;

• 示例

from deptpart2
insert into table deptpart1 partition(area='huaxi') select deptno,dname,loc
insert into table deptpart1 partition(area='huaxinan') select deptno,dname,loc;

3.1.3 查询语句中创建表并加载数据(as select)

• 根据查询结果创建表（查询的结果会添加到新创建的表中）
• 示例

create table if not exists student3 as select id, name from student;

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

• 示例

create table if not exists student5(
id int, name string)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student5';

3.1.5 import数据到指定hive表中

• 注意：先用export导出后，再将数据导入。
• 示例

import table student2 partition(month='201709') from '/user/hive/warehouse/export/student';

3.2 数据导出

3.2.1 Insert导出

• 示例：将查询的结果导出到本地

insert overwrite local directory '/opt/module/datas/export/student' select * from student;

• 示例：将查询结果格式化导出到本地

insert overwrite local directory '/opt/module/datas/export/student1'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'             
select * from student;

• 示例：将查询的结果导出到HDFS上(无local关键字)

insert overwrite directory '/user/atguigu/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
select * from student;

3.2.2 Hadoop命令导出到本地

• 示例

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

3.2.3 Hive Shell命令导出

• 示例

bin/hive -e 'select * from default.student;' > /opt/module/datas/export/student4.txt;

3.2.4 Export导出到HDFS上

• 语法

export table 表名 to '路径';

• 说明
  • export导出会将表的元数据一起导出 
• 示例

export table default.student to '/user/hive/warehouse/export/student';

3.3 清除表中的数据

• 语法

truncate table 表名;

• 说明：Truncate只能删除管理表，不能删除外部表中数据

第四章 查询

• 大部分都与SQL语法一样
• 语法

[WITH CommonTableExpression (, CommonTableExpression)*] 
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
  FROM table_reference
  [WHERE where_condition]
  [GROUP BY col_list]
  [ORDER BY col_list]
  [CLUSTER BY col_list
    | [DISTRIBUTE BY col_list] [SORT BY col_list]
  ]
 [LIMIT number]

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

​​​​​​​​​​​​​​第五章 排序

5.1 全局排序(order by)​​​​​​​

• order by：全局排序，一个MapReduce
• 使用order排序时
  • asc：升序
  • desc：降序
• ORDER BY 子句在SELECT语句的结尾
• 用法基本和普通SQL中一致
• 示例：使用薪水升序排序

select * from emp order by sal asc;

5.2 每个MapReduce内部排序

• Sort By：每个Reducer内部进行排序，对全局结果集来说不是排序。
• 使用sort by之前需要先设置reduce个数

set mapreduce.job.reduces=个数

• 示例：根据部门编号查看员工信息​​​​​​​

select * from emp sort by empno desc;

5.3 分区排序

• Distribute By：类似MR中partition，进行分区，结合sort by使用
• 注意：Hive要求DISTRIBUTE BY语句要写在SORT BY语句之前。
• 也要设置reduce个数，否则无法看到效果
• 示例：

select * from emp DISTRIBUTE BY deptno sort by deptno desc ;

5.4 Cluster By

• 当distribute by和sorts by字段相同时，可以使用cluster by方式。

• cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，不能指定排序规则为ASC或者DESC。

• 上面的示例还可以如下表示

select * from emp cluster by deptno;