HIVE——常用sql命令总结

文章目录

• hive常用交互命令

• `-e`执行sql
• `-f`执行脚本中sql语句
• hive cli命令行窗口操作hdfs
• 查看hive中输入的所有历史命令

• 库

• 创建库
• 查看库
• 使用库
• 修改库
• 删除库

• 表

• 查看表
• 创建表

• 语法
• 管理表和外部表
• 示例

• 修改表
• 删除表
• 分区表

• 概念
• 操作
• 分区表和数据产生关联

• 方式一：上传数据后修复
• 方式二：上传数据后添加分区
• 方式三：创建文件夹后load数据到分区

• DML数据操作

• 数据导入

• 向表中导入数据(load data)

• 语法

• 查询插入数据(insert... select)
• 查询创建表并加载数据（as select）
• 创建表时location指定加载数据路径(location)
• import数据到指定hive表中

• 数据导出

• insert导出
• hadoop -get命令导出本地
• hive -e命令导出到本地
• hive export导出到hdfs

• 查询

• 列别名
• 运算符
• 常用函数
• where语句

• 示例
• 比较运算符
• like和rlike
• 逻辑运算符

• 分组

• group by语句
• having语句
• join语句

• 排序

• 全局排序（order by）
• 按照别名排序
• 多列排序
• 区内排序（sort by ）
• 分区排序（distribute by）
• cluster by

hive常用交互命令

-e执行sql

[linux01@test hive$] bin/hive -e "select * from tab_01;"

-f执行脚本中sql语句

1. 编写sql脚本

[linux01@test hive$] touch /data/test/hive-f-test.sql

在脚本中编写sql。
2. 执行sql脚本

[linux01@test hive$] bin/hive -f /data/test/hive-f-test.sql

3. 执行sql脚本并将结果写入另一个文件

[linux01@test hive$] bin/hive -f /data/test/hive-f-test.sql > /data/test/data/hive-f-test-result.txt

hive cli命令行窗口操作hdfs

hive> dfs -ls /user/hive/warehouse/;

查看hive中输入的所有历史命令

[linux01@test $] cat ~/.hivehistory

库

创建库

hive> create database if not exists db01;

查看库

1. 查看所有库

hive> show databases;

2. 查看数据库信息

hive> desc database db01;

3. 查看数据库详细信息

hive> desc database extended db01;

使用库

hive> use db01;

修改库

  可以使用alter database命令为某个数据库的DBPROPERTIES设置键-值对属性值，用于描述数据库的属性信息。数据库的其他元数据信息无法更改，包括数据库名和数据库所在目录位置location。

hive> alter database hive set dbproperties('createtime'='20220101');

删除库

1. 删除空数据库

hive> drop database db01;

2. 删除不存在数据库

hive> drop database if exists db01;

3. 删除不为空的数据库

hive> drop database db01 cascade;

表

查看表

1. 查看所有表

hive> show tables;

2. 查看表结构

hive> show create table tab_01;

3. 查看表类型

hive> desc formatted tab_01;

创建表

语法

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]

常用简化：

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[ROW FORMAT row_format]
[LOCATION hdfs_path]

字段说明：

1. CREATE TABLE： 创建一个指定名称的表，若相同名字的表已经存在，则抛出异常，用户可使用IF NOT EXISTS选项忽略这个异常。
2. EXTERNAL：关键字创建一个外部表，在建表的同时指定一个指向实际数据的路径LOCATION，hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不会对数据的位置做任何改变。在删除表时，内部表的元数据和数据会被一起删除；而外部表只删除元数据，不删除数据。
3. COMMENT：为表和列添加注释。
4. PARTITIONED BY：创建分区表。
5. CLUSTERED BY：创建分桶表。
6. SORTED BY：桶内排序。
7. ROW FORMAT：DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] | SERDE serde_name [WITH SERDEROPERTIES (property_name=property_value, property_name=property_value, ...] 用户在建表的时候可以自定义SerDe（Serialize/Deserialize，序列化和反序列化）或者使用自带的SerDe，若没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe，在建表的时候，用户还需要为表指定列，用户在指定表的列同时也会指定自定义的SerDe。hive通过SERDE确定表的具体的列的数据。
8. STORED AS：指定存储文件的类型，常见类型有：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）.若文本数据是纯文本，可以使用STORED AS TEXTFILE，若需要压缩存储，可以使用STORED AS SEQUENCEFILE。
9. LOCATION：指定表在HDFS上的存储位置。
   10.LIKE：允许用户复制现有的表结构，但是不复制数据。

管理表和外部表

  默认创建的表都是管理表，也称为内部表，hive会控制数据的声明周期，不适合和其他工具共享数据。默认情况下会将这些表的数据存储在hive.metastore.warehouse.dir配置项定义的目录的子目录下，如/user/hive/warehouse，当删除一个管理表时，hive也会删除这个表中的数据，即hdfs location的数据也会一并删除。
  外部表使用EXTERNAL关键字进行创建。若创建外部表，仅记录数据所在的路径，不会对数据的位置做任何改变。在删除表时，内部表的元数据和数据会被一起删除；而外部表只删除元数据，不删除数据。

示例

1. 创建分区表

hive> create table if not exists tab_01 (colume01 string)
partitioned by (colume02 string)
row format delimited
fields terminated by '\t';

2. 创建外部表

hive> create external table if not exists db01.tab_01(
id int, name string
)
row format delimited fields terminated by '\t';

装载数据进表中

hive> load data local inpath '/xxx/xxx/xxx.txt' into table db01.tab_01;

删除数据表

hive> drop table db01.tab_01;

此时location中的数据还是存在，即删除外部表后，元数据删除，但数据不删除。

修改表

1. 修改内部表为外部表

hive> alter table tab_01 set tblpropertites('EXTERNAL'='TRUE');

2. 修改外部表为内部表

hive> alter table tab_01 set tblpropertites('EXTERNAL'='FALSE');

3. 重命名表

hive> alter table tab_01 rename to tab_01_new;

4. 增加列信息

hive> alter table tab_01 add columns(column02 string);

5. 更新列

hive> alter table tab_01 change column column02 column02_new string;
hive> alter table tab_01 change column column02_new column02_new_new int;

6. 替换列
   不会修改存储在hdfs中的数据，只是改元数据的列而已。若hdfs中存储的是string类型，若列replace列为int后，则查不了对应的数据。

hive> alter table tab_01 replace columns (column03 int);

删除表

1. 删除表

hive> drop table tab_01;

2. 清空表
   只能清空管理表，不清楚外部表。

hive> truncate table tab_01;

分区表

概念

  分区表实际上是对应一个HDFS文件系统上的独立文件夹，该文件夹是该分区所有的数据文件。hive中的分区就是分目录，把一个大的数据集切割成多个小的数据集，在查询时可以通过WHERE选定指定的分区查询对应的数据。

操作

1. 创建分区表

hive> create table if not exists tab_01 (column01 string)
partitioned by (partition_column01 string)
row format delimited fields terminated by '\t';

2. 查看分区信息

hive> show partitions tab_01;

3. 查看分区表结构

hive> desc formatted tab_01;

4. 插入分区数据

hive> insert into table tab_01 partition(partition_column01='xxxx') values ('yyyy');

5. load数据到分区

hive> load data local inpath '/data/xxx/xx/yy.txt' into table tab_01 partition(column02='xxxxxx');

6. 查看分区数据

hive> select * from tab_01 where partition_column01='xxxx';

7. 增加单个分区

hive> alter table tab_01 add partition(partition_column01='yyyy');

8. 增加多个分区

hive> alter table tab_01 add partition(partition_column01='xxx') partition(partition_column01='yyy');

9. 删除单个分区

hive> alter table tab_01 drop partition(partition_column01='yyyy');

10. 删除多个分区

hive> alter table tab_01 drop partition(partition_column01='xxx'), partition(partition_column01='yyy');

11. 创建二级分区

hive> create table tab_01(column01 string, column02 int
)
partitioned by (partition_column01 string, partition_column02 string)
row format delimited fields terminated by '\t';

12. 加载数据到二级分区表

hive> load data local inpath '/data/xxx/yy.txt' into table tab_01 partition(partition_column01='xxxx', partition_column02='yyyy');

13. 查询二级分区表数据

hive> select * from tab_01 where partition_column01='xxxx' and partition_column02='yyyy';

分区表和数据产生关联

方式一：上传数据后修复

1. 上传数据

[linux01@test $] hdfs dfs -mkdir -p /user/hive/warehouse/tab_01/month=202205/day=7;
[linux01@test $] hdfs dfs -put /data/t.txt /user/hive/warehouse/tab_01/month=202205/day=7;

2. 查询数据

hive> select * from tab_01 where month='202205' and day='7';

查询不到数据，因为该表只是有实际数据，但是无元数据。
3. 执行修复命令

hive> msck repair table tab_01;

4. 再次查询数据

hive> select * from tab_01 where month='202205' and day='7';

查询到数据。

方式二：上传数据后添加分区

1. 上传数据

[linux01@test $] hdfs dfs -mkdir -p /user/hive/warehouse/tab_01/month=202205/day=8;
[linux01@test $] hdfs dfs -put /data/t.txt /user/hive/warehouse/tab_01/month=202205/day=8;

2. 增加分区

hive> alter table tab_01 add partition(month=202205,day=8);

3. 查询数据

hive> select * from tab_01 where month='202205' and day='8';

方式三：创建文件夹后load数据到分区

1. 创建目录

[linux01@test $] hdfs dfs -mkdir -p /user/hive/warehouse/tab_01/month=202205/day=9;

2. 上传数据

hive> load data local inpath '/data/t.txt' into table tab_01 partition(month='202205',day='9');

3. 查询数据

hive> select * from tab_01 where month='202205' and day='9';

DML数据操作

数据导入

向表中导入数据(load data)

语法

load data [local] inpath '/xxx/xxx/xx.txt' [overwrite] into table tab_01 [partition(part_col1=val1,...)];

其中:

1. load data: 表示加载数据。
2. local：表示从本地加载数据到hive表，否则从hdfs加载数据到hive表中。
3. inpath：表示加载数据的路径。
4. overwrite：表示覆盖原表中的数据，若不加该关键字，则表示追加数据。
5. into table：表示加载到目标表。
6. tab_01：表示加载到的目标表。
7. partition：表示加载到表中的哪个分区。

查询插入数据(insert… select)

1. 创建分区表

hive> create table tab_01(id int, name string)
    > partitioned by (month string)
    > row format delimited fields terminated by '\t';

2. 基本插入数据

hive> insert into table tab_01 partition (month='202205') values (1, 'xiaoming');

3. 基本插入模式（单张表查询插入）

hive> insert overwrite table tab_01 partition(month='202206')
    > select id, name from tab_01 where month='202205';

4. 多插入模式（多张表查询插入）

hive> from tab_01
    > insert overwrite table tab_01 partition(month='202207')
    > select id, name from tab_01 where month='202205'
    > insert overwrite table tab_01 partition(month='202208')
    > select id, name from tab_01 where month='202205';

查询创建表并加载数据（as select）

hive> create table if not exists tab_02
    > as select id from tab_01;

创建表时location指定加载数据路径(location)

1. 创建表并指定hdfs路径

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

2. 上传数据到hdfs路径中

[linux01@test $] hdfs dfs -put /xxx/xxx/xx.txt /user/hive/warehouse/tab_02

3. 查询数据

hive> select * from tab_02;

import数据到指定hive表中

需要先将数据export后才能import进hive表中。

hive> import table tab_01 partition(month='202205')
    > from
    > '/user/hive/warehouse/export/tab_01';

数据导出

insert导出

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

hive> insert overwrite local directory 
    > '/data/hive/export/tab_01'
    > select * from tab_01;

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

hive> insert overwrite local directory
    > '/data/hive/export/tab_01'
    > row format delimited fields terminated by '\t'
    > select * from tab_01;

3. 将查询的结果导出到hdfs上

hive> insert overwrite directory
    > '/user/user01/export/tab_01'
    > row format delimited fields terminated by '\t'
    > select * from tab_01;

hadoop -get命令导出本地

[linux01@test $] hdfs dfs -get /user/hive/warehouse/tab_01/month=202205/day=8 /data/test/export/t.txt

hive -e命令导出到本地

[linux01@test hive$] bin/hive -e 'select * from db01.tab_01;' > /data/test/export/t.txt

hive export导出到hdfs

hive> export table tab_01 to '/data/test/export/tab_01'

查询

列别名

hive> select name cn_name from tab_01;
hive> select name as cn_name from tab_01;

运算符

运算符	说明
A+B	A加B
A-B	A减B
A*B	A乘以B
A/B	A除以B
A%B	A对B取余
A&B	A和B按位取与
A|B	A和B按位取或
A^B	A和B按位取异或，相同即为0，不同即为1
~A	A按位取反

hive> select num + 100 from tab_01;

常用函数

1. 计数（count）

hive> select count(*) cnt from tab_01;

2. 最大值（max）

hive> select max(num) max_num from tab_01;

3. 最小值（min）

hive> select min(num) min_num from tab_01;

4. 总和（sum）

hive> select sum(num) sum_num from tab_01;

5. 平均值（avg）

hive> select avg(num) avg_num from tab_01;

6. limit语句

hive> select * from tab_01 limit 10;

where语句

示例

hive> select * from tab_01 where id < 10;

比较运算符

between/in/is null

运算符	支持的数据类型	说明
A=B	基本数据类型	若A等于B，返回TRUE，否则返回FALSE
A<=>B	基本数据类型	若A和B都为NULL，返回TRUE; 其他的比较等同于“=”的结果； 若任一方位NULL，则结果为NULL
A<>B, A!=B	基本数据类型	A或B为NULL，返回NULL; 若A不等于B，返回TRUE，否则返回FALSE
A<B	基本数据类型	A或B为NULL，返回NULL; 若A小于B，则返回TRUE，否则返回FALSE
A<=B	基本数据类型	A或B为NULL，返回NULL; 若A小于等于B，则返回TRUE，否则返回FALSE
A>B	基本数据类型	或B为NULL，返回NULL; 若A大于B，则返回TRUE，否则返回FALSE
A>=B	基本数据类型	A或B为NULL，返回NULL; 若A大于等于B，则返回TRUE，否则返回FALSE
A [NOT] BETWEEN B AND C	基本数据类型	若A,B或C任一个为NULL，则结果为NULL; 若A的值大于等于B且小于等于C，则结果为TRUE，否则为FALSE;若使用NOT关键字，则上述结果为相反的。
A IS NULL	所有数据类型	若A为NULL，则返回TRUE，否则返回FALSE
A IS NOT NULL	所有数据类型	若A不为NULL，则返回TRUE，否则返回FALSE
IN(num1, num2)	所有数据类型	使用IN判断是否在显示的列表中num1和num2这两个值
A [NOT] LIKE B	STRING类型	B是一个sql正则表达式，若A匹配，则返回TRUE，否则返回FALSE。 'x%‘表示A必须以’x’开头； ‘%x’表示A必须以’x’结尾。 ’%x%‘表示A包含字母’x’，可以任何位置。 若加入关键字NOT，则上述结果都为相反的。
A RLIKE B, A REGEXP B	STRING类型	B的一个正则表达式，若A匹配，则返回TRUE，否则返回FALSE;

like和rlike

1. 使用like运算选择类似的值；

hive> select * from tab_01 where col_01 like '%y';

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

hive> select * from tab_01 where col_01 like '_y%';

3. rlike子句是hive扩展功能，通过java正则表达式指定匹配条件。

hive> select * from tab_01 where col_01 rlike '[y]';
-- 等价于
hive> select * from tab_01 where col_01 like '%y%';

逻辑运算符

and/or/not

运算符	说明
AND	逻辑并
OR	逻辑或
NOT	逻辑否

1. 逻辑并and

-- 查出名字为xiaoming且年龄小于10岁的记录
hive> select * from tab_01 where name = 'xiaoming' and age < 10;

2. 逻辑或or

-- 查出名字为小明或者年龄小于10岁的记录
hive> select * from tab_01 where name = 'xiaoming' or age < 10;

3. 逻辑否

-- 查出年龄不是6岁和10岁的记录
hive> select * from tab_01 where age not in(10, 6);

分组

group by语句

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

1. 计算student表中每个班级classno的平均年龄

hive> select s.classno, avg(s.age) avg_age 
    > from student s 
    > group by s.classno;

2. 计算student表中每个班级每个学生的考试的最高分

hive> select s.classno, s.person, max(s.score) max_score 
    > from student s
    > group by s.classno, s.person;

having语句

  having和where语句不同：

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

-- 求每个班级的平均年龄大于12岁的班级
--1）求每个班级的平均年龄
hive> select classno, avg(age) avg_age from student
    > group by classno;
--2)求平均年龄大于12岁的班级
hive> select classno, avg(age) avg_age from student
    > group by classno
    > having avg_age > 12;

join语句

  hive只支持等值连接，不支持非等值连接。支持内连接、左连接、右连接、满连接、多表连接和笛卡尔积。大多数情况下，hive会对每个join连接对象启动一个mapreduce任务。

-- 内连接：查询学生student表中和班级class表中班级编号classno相等，查询学生编号sno、学生姓名name和班级名称name
hive> select s.sno, s.name, c.name 
    > from student s 
    > join class c on s.classno = c.classno;
-- 笛卡尔积
hive> select s.name, c.name from student s, class c;

排序

全局排序（order by）

1. 使用order by子句排序

关键字	说明
ASC	ascend，升序（默认）
DESC	descend，降序

2. order by子句一般都在select语句的结尾。

-- age年龄升序
hive> select * from student order by age;
-- age年龄降序
hive> select * from student order by age desc;

按照别名排序

-- 按照学生3倍的分数排序
hive> select name, score*3 threescore 
    > from student 
    > order by threescore;

多列排序

-- 按照id和age排序
hive> select * from student order by id, age;

区内排序（sort by ）

1. 设置reduce个数

hive> set mapreduce.job.reduces=3;

2. 查看reduce个数

hive> set mapreduce.job.reduces;

3. 根据班级编号降序查看学生信息

hive> select * from student sort by classno desc;

4. 将查询结果导入文件

hive> insert overwrite local directory
	> '/data/test/student-sortby-result'
	> select * from student sort by classno desc;

若是全局排序，reduce只会有1个。

分区排序（distribute by）

  若需要进行分区排序，则使用distribute by结合sort by使用。

-- 根据学生编号sno进行分区排序，班级编号进行mapreduce排序
hive> insert overwrite local directory
	> '/data/test/student-distributeby-result'
	> select * from student distribute by sno sort by classno desc;

cluster by

  只有当distribute by和sort by的字段相同时，才可以使用cluster by。只能是升序，不能指定排序规则为ASC或者DESC。

-- 根据班级编号进行分区排序，同样以班级编号进行mapreduce排序
hive> select * from student distribute by classno sort by classno;
-- 等价于
hive> select * from student cluster by classno;