Hive常用命令

Hive数据类型 1. 基本数据类型

2. 集合数据类型

3. 案例

(1) 假设某表有如下一行，我们用 JSON 格式来表示其数据结构。在 Hive 下访问的格式为：

{
 "name": "songsong",
 "friends": ["bingbing" , "lili"] , //列表 Array, 
 "children": { //键值 Map,
 "xiao song": 18 ,
 "xiaoxiao song": 19
 }
 "address": { //结构 Struct,
 "street": "hui long guan",
 "city": "beijing"
 }
}

可以看到，name是string类型，friends是array类型，children是map类型，address是struct类型。

(2) 我们的本地测试文件test.txt如下：

songsong,bingbing_lili,xiao song:18_xiaoxiao song:19,hui long guan_beijing
yangyang,caicai_susu,xiao yang:18_xiaoxiao yang:19,chao yang_beijing

可以看到，不同的数据被一些特定的字符隔开（如", " “:” "_"等），这些字符是可以在sql的创建表的命令中自定义的。建表之后，表内的数据就会按照表的分隔符号对数据做分隔。

(3) 为了更好理解，我贴出一个创建表的命令。

create table test(
name string,
friends array,
children map,
address struct
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by 'n';

创建表的命令和普通的sql语句没有什么大的不同，只是下面有几行format定义。

• row format delimited fields terminated by ‘,’ ------ 这表示列与列之间的分隔符是’,’
• collection items terminated by ‘_’ ------这表示集合中的分隔符为’_’
• map keys terminated by ‘:’ ------这表示map的key与value的映射用’:'表示
• lines terminated by ‘n’; ------这表示每行数据的分隔符为’n’

(4) 接下来，我们把test.txt导入到表test中。

load data local inpath '/opt/module/hive/datas/test.txt' into table test;

/opt/module/hive/datas/test.txt就是test.txt的文件路径。这条命令就是指将test.txt导入到test中，导入后数据会根据建表时的分隔规则解析数据，最终得到一个结构化的数据。

(5) 访问表里的数据

select friends[1],children['xiao song'],address.city from test where name="songsong";
//结果：
//OK
//_c0 _c1 city
//lili 18 beijing
//Time taken: 0.076 seconds, Fetched: 1 row(s)

4. 隐式转换规则

（1）任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成INT，INT 可以转换成 BIGINT。
（2）所有整数类型、FLOAT 和 STRING 类型都可以隐式地转换成 DOUBLE。
（3）TINYINT、SMALLINT、INT 都可以转换为 FLOAT。
（4）BOOLEAN 类型不可以转换为任何其它的类型。
（5）可以使用 CAST 操作显示进行数据类型转换。例如 CAST(‘1’ AS INT)将把字符串’1’ 转换成整数 1；如果强制类型转换失败，如执行CAST(‘X’ AS INT)，表达式返回空值 NULL。

常规命令 1. 数据库相关命令 创建数据库

CREATE DATAbase [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

• IF NOT EXISTS表示如果不存在才创建；
• COMMENT添加注释；
• LOCATION hdfs_path指定数据库在HDFS上的存放位置，默认存储路径是/user/hive/warehouse/*.db。
• WITH DBPROPERTIES设置数据库的属性

显示数据库

show database database_name;

切换数据库

use database_name;

修改数据库

alter database db_hive set dbproperties('createtime'='20170830');

删除数据库

drop database database_name;

显示数据库信息

desc database database_name
//OK
//db_hive hdfs://hadoop102:9820/user/hive/warehouse/db_hive.db	username	USER

2. 表相关命令 创建表

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 test(
name string,
friends array,
children map,
address struct
)
row format delimited fields terminated by ','
collection items terminated by '_'
map keys terminated by ':'
lines terminated by 'n';

1. 在创建表时，EXTERNAL关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION）。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。
2. COMMENT：为表和列添加注释。
3. PARTITIonED BY 创建分区表。
4. CLUSTERED BY 创建分桶表。
5. SORTED BY 不常用，对桶中的一个或多个列另外排序。
6. ROW FORMAT为表指定列。
7. STORED AS 指定存储文件类型。常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件）。
8. LOCATION ：指定表在 HDFS 上的存储位置，也就是实际数据的存储路径。这里要注意，LOCATION不能是一个文件，必须是一个路径，否则会抛异常。因为你这是在初始化表的数据存放位置。
9. AS：后跟查询语句，根据查询结果创建表。

修改表

ALTER TABLE table_name RENAME TO new_table_name

查看表结构

desc table_name;
//结果
//OK
//id                      int
//name                    string

修改列信息

更新列：

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

//例子
alter table dept change column deptdesc dept string;

增加和替换列：

ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT 
col_comment], ...) 

//例子 add
alter table dept add columns(deptdesc string);
//例子 replace
alter table dept replace columns(deptno string, dname string, loc string);

修改表数据的存储类型

Hive可以在创建的时候使用stored as设置数据的存储文件类型。hive文件存储格式包括以下几类：

1. TEXTFILE
2. SEQUENCEFILE
3. RCFILE
4. ORCFILE(0.11以后出现)

其中TEXTFILE为默认格式。

若在建表时未设置，但又想要修改该表的文件存储类型。可以使用以下方法设置。

alter table table_name set fileformat xxx;

//例如
alter table table_name set fileformat parquet;

修复表

Hive可以根据HDFS上路径的元数据修复自己记录的元数据。

msck repair table table_name;

修复后的表可以得到HDFS的分区元数据等。

3. 管理表与外部表 管理表（内部表）

Hive默认创建的表都是管理表，也被叫做内部表。Hive 默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(如，/user/hive/warehouse)所定义的目录的子目录下。
当我们删除一个管理表时，Hive 也会删除这个表中数据。管理表不适合和其他工具共享数据。

外部表

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

管理表和外部表的使用场景

每天将收集到的网站日志定期流入 HDFS 文本文件。
在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过 SELECT+INSERT 进入内部表。

查看表格式化数据（可以查看是管理表还是外部表）

//外部表
desc formatted table_name;
//Table Type: EXTERNAL_TABLE

//管理表
desc formatted table_name;
//Table Type: MANAGED_TABLE

删除表

drop table dept;

外部表删除表后数据仍然会保留，只删除元数据。

管理表与外部表的互相转换

修改管理表为外部表：

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

修改外部表为管理表：

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

注：区分大小写

4. 数据导入 load data

load data [local] inpath '数据的 path' [overwrite] into table table_name [partition (partcol1=val1,…)];

• load data:表示加载数据
• local:表示从本地加载数据到 hive 表；否则从 HDFS 加载数据到 hive 表
• inpath:表示加载数据的路径
• overwrite:表示覆盖表中已有数据，否则表示追加
• into table:表示加载到哪张表
• table_name :表示具体的表
• partition:表示上传到指定分区

这里注意，做数据导入的时候，数据文件会被移动到数据表指定的文件夹里，也就是表的LOCATION的路径中。

例子：

//加载本地文件到hive
load data local inpath '~/student.txt' into table db_name.table_name;

//加载HDFS文件到hive
load data inpath '/data/student.txt' into table db_name.table_name;

//加载HDFS文件覆盖已有数据
load data inpath '/data/student.txt' overwrite into table db_name.table_name;

使用查询语句插入表数据（insert后接select）

//从student_temp表中并追加到student表中
insert into table student [partition(month='2021-11')]
select id, name from student_temp where month='2021-11';

//从student_temp表中并覆盖到student表中
insert overwrite table student 
select id, name from student where month='2021-11';

使用查询语句结果创建表（as）

//在创建表的时候把数据加载进去
create table if not exists student3
as select id, name from student;

从HDFS上import数据到表中

import一般和export挂钩，将export的数据import到表中。

import table student form '/user/hive/warehouse/export/student'

5. 数据导出 从表上export数据到HDFS中

export一般和import挂钩，将表中的数据export到HDFS中。

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

insert导出

将查询的结果导出到本地

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

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

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

将查询的结果导出到 HDFS 上(没有 local)

 insert overwrite directory '/data'
 ROW FORMAT DELIMITED FIELDS TERMINATED BY 't' 
 select * from student;

hive shell 命令导出到本地

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

HQL命令

HQL命令和数据库中的SQL命令大体是吻合的，我只挑出一些Hive特有的命令细说。其中最经典的就是4个By。

在讲这之前，我们可以知道通过这种方式调整Hive对应的MR程序的reducer个数。

set mapreduce.job.reduces=n;

Order By

order by虽然在常规的sql命令中也有，代表着按照xxx排序的意思。
但是这里有一点需要注意，在hive使用order by的时候，代表着最后只有一个reducer，即使你手动修改reducer数量，最终结果reducer数量也只有1个。

Sort By

sort by同样是按照xxx排序的意思，但是sort by是实现分区内的排序。提前声明好reducer的数量，最后得到的就是各个分区排序好的数据，分区之间没有排序。

Distribute By

distribute by的意思是按照xxx分区。这跟MR中的自定义分区是相同的实现。
distribute by通常和sort by组合使用。

Cluster By

当sort by和distribute by对应的是同一种数据时，可以直接用cluster by代替，这两种写法是同一个意思。

select * from student sort by id distribute by id
//可以改为
select * from student cluster by id

参考：尚硅谷