Hive基础（一）

目录

数据库管理

创建数据库

查询数据库

修改数据库

删除数据库

表管理

创建表

管理表

外部表

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

分区表

分区表注意事项

修改表

删除表

---

数据库管理

创建数据库

1、创建一个数据库，数据库在HDFS上的默认存储路径是
/user/hive/warehouse/*.db

2、避免要创建的数据库已经存在错误，增加 if not exists判断。
（标准写法）   （/*这里以school为例*/）

hive (default)> create database if not exists school;

查询数据库

1．查看数据库

show databases;

2．过滤显示查询的数据库

show databases like 'db_hive*';

OK
db_hive
db_hive_1 

3. 数据库详情

1)显示数据库信息

desc database school;

2)显示数据库详细信息，extended

desc database extended school;

4.切换当前数据库

use school;

修改数据库

用户可以使用ALTER DATABASE命令为某个数据库的 DBPROPERTIES设置键-值对属性值，来描述这个数据库的属性 信息。数据库的很多元数据信息是不可更改的，包括数据库名 和数据库所在的目录位置。

alter database school set dbproperties('createtime'='20200407');

删除数据库

1．删除空数据库

drop database db_hive2;

2．如果删除的数据库不存在，最好采用if exists判断数据库是否存在

3．如果数据库不为空，可以采用cascade命令，强制删除

drop database school cascade;

表管理

创建表

1．建表语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_namedata_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_bucketsBUCKETS]
[ROW FORMAT row_format]
[STORED AS file_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 SERDEPROPERTIES 
(property_name=property_value, property_name=property_value, ...)]
用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定
ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建
表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自
定义的SerDe，Hive通过SerDe确定表的具体的列的数据。
SerDe是Serialize/Deserilize的简称，目的是用于序列化和反序列化。
（8）STORED AS指定存储文件类型
常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、
TEXTFILE（文本）、RCFILE（列式存储格式文件）
如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据
需要压缩，使用STORED AS SEQUENCEFILE。
（9）LOCATION ：指定表在HDFS上的存储位置。
*/

管理表

1.普通创建表

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

2.根据查询结果创建表（查询的结果会添加到新创建的表中）

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

3.根据已经存在的表结构创建表

create table if not exists student4 like student2;

4.查询表的类型

desc formatted student2;

外部表

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

创建外部表

create external table if not exists ext_stu(
id String,
name String,
age int
)
row format delimited fields terminated by ','
location '/usr/test_data/';

 查看表格式化数据

desc formatted default.stu1;

删除外部表

drop table ext_stu;

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

（1）查询表的类型

desc formatted ext_stu;

Table Type: EXTERNAL_TABLE 

（2）修改内部表student2为内部表

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

（3）查询表的类型

desc formatted ext_stu;

Table Type: MANAGED_TABLE 

（4）修改外部表student2为外部表

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

（5）查询表的类型

desc formatted ext_stu;

Table Type: EXTERNAL_TABLE 

注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！

分区表

1.引入分区表
/user/hive/warehouse/school.db/stu_partition/class=dsj-1
/user/hive/warehouse/school.db/stu_partition/class=dsj-2

2.创建分区表语法

create table stu_partition(
name String, Phone_NO String
)
partitioned by (class String)
row format delimited fields terminated by '\t';

 3.加载数据到分区表中（方法之一）

load data local inpath '/usr/test_data/dsj-1.txt' into table school.stu_partition partition(class='dsj-1');
load data local inpath '/usr/test_data/dsj-2.txt' into table school.stu_partition partition(class='dsj-2');

4.查询分区表中数据

单分区查询

select * from stu_partition where class='dsj-1';

多分区联合查询

select * from stu_partition where class='dsj-1'
union
select * from stu_partition where class='dsj-2';

全表查询 

select * from stu_partition;

5.增加分区

创建单个分区

alter table stu_partition add partition(class='dsj-1');

 同时创建多个分区

alter table stu_partition add partition(class='dsj-2') partition(class='dsj-3');

查看分区：

show partitions stu_partition;

 6．删除分区

alter table stu_partition drop partition(class='dsj-1');

alter table stu_partition drop partition(class='dsj-2'),partition(class='dsj-3');

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

show partitions stu_partition;

8.查看分区表结构

desc formatted stu_partition;

分区表注意事项

1．创建二级分区表

create table stu_partition2(
name String, Phone_NO String
)
partitioned by (class String,year int)
row format delimited fields terminated by '\t';

2.二级分区加载数据示例

/*（1）加载数据到二级分区表中 */
load data local inpath '/usr/test_data/dsj-1.txt' into table 
school.stu_partition2 partition(class='dsj-1',year='2018'); 
/*（2）查询分区数据 */
select * from school.stu_partition2 where class='dsj-1' 
and year='2018';

修改表

1 重命名表

ALTER TABLE table_name RENAME TO new_table_name

实操案例

alter table student2 rename to stu2;

3 修改列

增加列：alter table stu2 add columns (pid int) ;
修改列：alter table stu2 change pid tab_id int ;
替换列：alter table stu2 replace columns (id String, pname 
String,tab_id String); --会替换所有列，慎用!

删除表

drop table stu2;