【Hive_01】hive关于数据库和表的语法

1、Hive常见使用技巧

1.1 交互式与非交互式

hive分为交互式以及非交互式。交互式就是有来有回的，这种对于探索性来说是很便捷的。

如果是每天执行定时任务，则使用非交互式模式的hive会更合适。


但是写这样的语句比较麻烦，考虑放到一个文件当中：

1.2 hive参数配置方式

1）查看当前所有的配置信息

hive>set;

2）参数的配置三种方式

• 配置文件方式
  – 默认配置文件: hive-default.xml
  – 用户自定义配置文件: hive-site.xml
  – 注意：用户自定义配置会覆盖默认配置。另外，Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive 的配置会覆盖Hadoop 的配置。配置文件的设定对本机启动的所有Hive进程都有效。

查看hive默认日志路径：

接下来查看日志文件

 tail -500 /tmp/root/hive.log

• 命令行参数方式
  ①启动Hive时，可以在命令行添加-hiveconf param=value来设定参数。但是这种设置方法只对本次启动的进程有效。

• 参数声明方式
  可以在HQL中使用SET关键字设定参数。但仅对本次Hive启动有效。

上述三种设定方式的优先级依次递增。即配置文件<命令行参数<参数声明。注意某些系统级的参数，例如 log4j相关的设定，必须用前两种方式设定，因为那些参数的读取在会话建立以前已经完成了。

2、DDL数据定义

2.1 创建数据库

（1）创建数据库

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

• CREATE DATABASE：表示创建一个数据库的操作。
• [IF NOT EXISTS]：表示如果数据库不存在则创建，如果数据库已经存在则忽略。
• database_name：表示要创建的数据库的名称。
• [COMMENT database_comment]：可选项，表示对数据库的注释或描述。
• [LOCATION hdfs_path]：可选项，表示数据库在HDFS上的存储路径。
• [WITH DBPROPERTIES (property_name=property_value, …)]：可选项，表示数据库的属性，可以设置多个属性，以逗号分隔。

注：方括号里面的内容都是可选操作

（1）创建一个数据库，不指定路径

hive (default)> create database db_hive1;

注：若不指定路径，其默认路径为${hive.metastore.warehouse.dir}/database_name.db

（2）创建一个数据库，指定路径

hive (default)> create database db_hive2 location '/db_hive2';

（2）创建一个数据库，带有dbproperties

hive (default)> create database db_hive3 with dbproperties('create_date'='2022-11-18');

with dbproperties('create_date'='2023-12-18'): 这部分是为创建的数据库指定属性。在这里，通过 dbproperties 关键字，为数据库设置了一个属性，属性名为 create_date，属性值为 2023-12-18。这样的属性可以用来存储关于数据库的元信息或其他自定义信息。在这个例子中，create_date 属性表示数据库创建的日期。

（2）查询数据库

1）展示所有数据库

（1）语法

SHOW DATABASES [LIKE 'identifier_with_wildcards'];

注：like通配表达式说明：*表示任意个任意字符，|表示或的关系。【起到模糊匹配的作用】

2）查看数据库信息

（1）语法

DESCRIBE DATABASE [EXTENDED] db_name;

（2）案例

• 查看基本信息

hive> desc database db_hive3;

• 查看更多信息

hive> desc database extended db_hive3;

（3）修改数据库

用户可以使用alter database命令修改数据库某些信息，其中能够修改的信息包括dbproperties、location、owner user。需要注意的是：修改数据库location，不会改变当前已有表的路径信息，而只是改变后续创建的新表的默认的父目录。

1）语法

--修改dbproperties
ALTER DATABASE database_name SET DBPROPERTIES (property_name=property_value, ...);

--修改location
ALTER DATABASE database_name SET LOCATION hdfs_path;

--修改owner user
ALTER DATABASE database_name SET OWNER USER user_name;

（4）删除数据库

1）语法

DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE];

注：

• RESTRICT：严格模式，若数据库不为空，则会删除失败，默认为该模式。
• CASCADE：级联模式，若数据库不为空，则会将库中的表一并删除。

（1）删除空数据库

hive> drop database db_hive2;

（2）删除非空数据库

hive> drop database db_hive3 cascade;

（5）切换当前数据库

1）语法

USE database_name;

2.2 表

（1）创建表

1）普通建表

（1）完整语法

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]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, ...)]

（2）关键字说明：

• TEMPORARY
  临时表，该表只在当前会话可见，会话结束，表会被删除。【做某些临时测试的时候用到】

• EXTERNAL（重点）
  外部表，与之相对应的是内部表（管理表）。管理表意味着Hive会完全接管该表，包括元数据和HDFS中的数据。而外部表则意味着Hive只接管元数据，而不完全接管HDFS中的数据。
  如果是内部表的话，当用户删除数据的时候，会将元数据和HDFS的数据都删除；而如果是外部表的话，只会删除元数据。

• data_type（重点）
  Hive中的字段类型可分为基本数据类型和复杂数据类型。【具体看第四小节的数据类型及其转换】

• PARTITIONED BY（重点）
  创建分区表，一般分区是按照日期来进行分区的。

• CLUSTERED BY … SORTED BY…INTO … BUCKETS（重点）
  创建分桶表，分桶表会将Hive当中一张表的数据，存储在多个文件上面。 CLUSTERED BY 后面跟的是一个或者多个字段。而一个BUCKETS代表的就是一个文件；SORTED BY是会在每一个分桶里面去做相应的排序的。【类似于哈希分区的操作】

无论是分区表还是分桶表，本质都是为了优化，去加快查询的速度的。

• ROW FORMAT（重点）
  指定SERDE，SERDE是Serializer and Deserializer的简写即序列化和反序列化器。Hive使用SERDE序列化和反序列化每行数据。详情可参考 Hive-Serde。

ROW FORMAT语法一：DELIMITED关键字表示对文件中的每个字段按照特定分割符进行分割，其会使用默认的SERDE对每行数据进行序列化和反序列化。

ROW FORAMT DELIMITED 
[FIELDS TERMINATED BY char] 
[COLLECTION ITEMS TERMINATED BY char] 
[MAP KEYS TERMINATED BY char] 
[LINES TERMINATED BY char] 
[NULL DEFINED AS char]

注：

fields terminated by ：列分隔符
collection items terminated by ： map、struct和array中每个元素之间的分隔符即集合类型的分隔符，map指的是键值对之间的分隔符，struct指的是结构体当中的字段之间的分隔符，array指的是数组里面每一个元素的分隔符。
map keys terminated by ：map中的key与value的分隔符
lines terminated by ：行分隔符

ROW FORMAT语法二：SERDE关键字可用于指定其他内置的SERDE或者用户自定义的SERDE。例如JSON SERDE，可用于处理JSON字符串。

ROW FORMAT SERDE serde_name [WITH SERDEPROPERTIES 
(property_name=property_value,property_name=property_value, ...)] 

• STORED AS（重点）
  指定文件格式，常用的文件格式有，textfile（默认值），sequence file，orc file、parquet file等等。指的是表对应的HDFS上面的文件的格式。

• LOCATION
  指定表所对应的HDFS路径，若不指定路径，其默认值为
  ${hive.metastore.warehouse.dir}/db_name.db/table_name

• TBLPROPERTIES
  用于配置表的一些KV键值对参数

2）Create Table As Select（CTAS）建表

该语法允许用户利用select查询语句返回的结果，直接建表，表的结构和查询语句的结构保持一致，且保证包含select查询语句放回的内容。

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name 
[COMMENT table_comment] 
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]

通过这种方式创建的表只能是内部表，不能是外部表。

3）Create Table Like语法

该语法允许用户复刻一张已经存在的表结构，与上述的CTAS语法不同，该语法创建出来的表中不包含数据。

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[LIKE exist_table_name]
[ROW FORMAT row_format] 
[STORED AS file_format] 
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, ...)]

4）数据类型及转换

基本数据类型如下：

Hive	说明	定义
tinyint	1byte有符号整数
smallint	2byte有符号整数
int	4byte有符号整数
bigint	8byte有符号整数
boolean	布尔类型，true或者false
float	单精度浮点数
double	双精度浮点数
decimal	十进制精准数字类型	decimal(16,2) 表示小数+整数最多16位
varchar	字符序列，需指定最大长度，最大长度的范围是[1,65535]	varchar(32)
string	字符串，无需指定最大长度
timestamp	时间类型
binary	二进制数据

decimal(16,2) ：参数1–表示小数与整数之和最多16位 ；参数2–声明小数部分有2位。

复杂数据类型如下：

类型	说明	定义	取值
array	数组是一组相同类型的值的集合	array	arr[0]
map	map是一组相同类型的键-值对集合	map<string, int>	map[‘key’]
struct	结构体由多个属性组成，每个属性都有自己的属性名和数据类型	struct<id:int, name:string>	struct.id

• 注：类型转换
  Hive的基本数据类型可以做类型转换，转换的方式包括隐式转换以及显示转换。

方式一：隐式转换

具体规则如下：

1、任何整数类型都可以隐式地转换为一个范围更广的类型，如tinyint可以转换成int，int可以转换成bigint。
2、所有整数类型、float和string类型都可以隐式地转换成double。
3、tinyint、smallint、int都可以转换为float。
4、boolean类型不可以转换为任何其它的类型。

详细内容查询：转换规则表

有点点类似于最小公倍数的概念。

方式二：显示转换

可以借助cast函数完成显示的类型转换

cast(expr as <type>) 

例如：将string类型的数据转换为int类型的数据

5）Hive上一张表的读写流程

• 读数据流程：HDFS files --> InputFileFormat --> <key, value> --> Deserializer --> Row object
  1、Hive的表会对应HDFS上的一条路径，数据都存在路径的文件当中，
  2、Hive使用inputFileFormat去读取相应的文件，而使用inputFileFormat读出来的文件一般是键值对<key，value>的形式。如果是文本文件的话，key对应的是行号，value对应的是一行的数据。
  3、之后Hive需要通过Deserializer反序列化器将value值封装成Row object。Row object相当于java对象，之后再进一步对Row object进行计算。

• 写数据流程：Row object --> Serializer --> <key, value> --> OutputFileFormat --> HDFS files
  1、一行数据Row object 先经过序列化Serializer的操作，之后拿到一个 <key, value> 键值对。
  2、之后通过OutputFileFormat 将数据写回到HDFS files上。

因此对于Hive上的任意一张表，都需要指定序列化、反序列化以及InputFileFormat和OutputFileFormat。

如果没有写的话，Hive使用的是默认值：接下来查看一下

--这个命令可以去查看完整的建表语句
show create table stu;

6）建表案例

（1）内部表

Hive中默认创建的表都是的内部表，有时也被称为管理表。对于内部表，Hive会完全管理表的元数据和数据文件。

创建内部表如下：

--建表案例一
create table if not exists student(
    id int comment 'id',
    name string
)
row format delimited fields terminated by '\t'
location '/user/hive/warehouse/student';

接下来创建一个datas文件夹，之后创建一个student.txt文件。

vim /home/wenxin/module/datas/student.txt

1001	student1
1002	student2
1003	student3
1004	student4
1005	student5
1006	student6
1007	student7
1008	student8
1009	student9
1010	student10
1011	student11
1012	student12
1013	student13
1014	student14
1015	student15
1016	student16

上传文件到Hive表指定的路径：

[root@hadoop102 datas]# hadoop fs -put student.txt /user/hive/warehouse/student

删除表，观察数据HDFS中的数据文件是否还在：

hive (default)> drop table student;

• 对于内部表，Hive会接管表的全部信息，包括元数据，HDFS的数据。

此时如果删除该表的话，会将HDFS上的文件也删除。

（2）外部表

外部表通常可用于处理其他工具上传的数据文件，对于外部表，Hive只负责管理元数据，不负责管理HDFS中的数据文件。

创建外部表如下：

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

• 和构建内部表的唯一区别就是external关键字。
• 对于外部表，如果在hive上删除了该表，则hdfs上的数据不会删除。

上传文件到Hive表指定的路径

[root@hadoop102 datas]$ hadoop fs -put student.txt /user/hive/warehouse/student

删除表，观察数据HDFS中的数据文件是否还在

hive (default)> drop table student;

2）SERDE和复杂数据类型

本案例重点练习SERDE和复杂数据类型的使用。

若现有如下格式的JSON文件需要由Hive进行分析处理，请考虑如何设计表？
注：以下内容为格式化之后的结果，文件中每行数据为一个完整的JSON字符串。

{
    "name": "dasongsong",
    "friends": [
        "bingbing",
        "lili"
    ],
    "students": {
        "xiaohaihai": 18,
        "xiaoyangyang": 16
    },
    "address": {
        "street": "hui long guan",
        "city": "beijing",
        "postal_code": 10010
    }
}

思路：依照文件内容的结构去设计表结构。

• 上面展示的数据就是一行完整的json字符串。

我们可以考虑使用专门负责JSON文件的JSON Serde，设计表字段时，表的字段与JSON字符串中的一级字段保持一致，对于具有嵌套结构的JSON字符串，考虑使用合适复杂数据类型保存其内容。最终设计出的表结构如下：

hive>
create table teacher
(
    name     string,
    friends  array<string>,
    students map<string,int>,
    address  struct<city:string,street:string,postal_code:int>
)
row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe'
location '/user/hive/warehouse/teacher';

创建该表，并准备以下文件。注意，需要确保文件中每行数据都是一个完整的JSON字符串，JSON SERDE才能正确的处理。

vim /opt/module/datas/teacher.txt

上传文件到Hive表指定的路径：

[root@hadoop102 datas]$ hadoop fs -put teacher.txt /user/hive/warehouse/teacher

（1）create table as select

hive>
create table teacher1 as select * from teacher;

（2）create table like

hive>
create table teacher2 like teacher;

（2）查看表

1）展示所有表
（1）语法

SHOW TABLES [IN database_name] LIKE ['identifier_with_wildcards'];

注：like通配表达式说明：*表示任意个任意字符，|表示或的关系。
（2）案例：模糊匹配

--查看表并进行模糊匹配
show tables like 'stu*';
--查看db_hive数据库当中是否存在以stu开头的表
show tables in db_hive1 like 'stu*';

2）查看表信息

（1）语法

DESCRIBE [EXTENDED | FORMATTED] [db_name.]table_name

--查看表信息
describe teacher;
--查看表详细信息
describe extended teacher;
--对详细信息进行可视化的展示
describe formatted teacher;

注：

• EXTENDED：展示详细信息
• FORMATTED：对详细信息进行格式化的展示

1、查看基本信息

hive> desc stu;

2、查看更多信息

hive> desc formatted stu;

（3）修改表

（1）语法

ALTER TABLE table_name RENAME TO new_table_name

（2）案例

将表名从stu修改成stu1

hive (default)> alter table stu rename to stu1;

2）修改列信息

• 下面修改的数据只能对原数据信息生效，表上已存在的HDFS数据文件不会被修改

（1）语法

• 增加列
  该语句允许用户增加新的列，新增列的位置位于末尾。

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

• 更新列
  该语句允许用户修改指定列的列名、数据类型、注释信息以及在表中的位置。

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

• 替换列
  该语句允许用户用新的列集替换表中原有的全部列。

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

2）案例

（1）查询表结构

hive (default)> desc stu;

（2）添加列

hive (default)> alter table stu add columns(age int);

调整gender字段的类型和在表当中的顺序：

-- 先修改数据类型
alter table stu change column gender gender int;

-- 然后调整位置
alter table stu change column gender gender int after number;

• 但是由于我没有修改gender的名字，因此写了两遍gender
  
• 只有兼容才能修改，并且兼容的规则和隐式转换是相一致的。

当然，可以自动设置成不会报错：

set hive.metastore.disallow.incompatible.col.type.changes=false;

• 执行完上面这句话之后，再进行类型的改变就能成功了

（3）查询表结构

hive (default)> desc stu;

（4）更新列

hive (default)> alter table stu change column age ages double;

（6）替换列

hive (default)> alter table stu replace columns(id int, name string);

（4）删除表

1）语法

DROP TABLE [IF EXISTS] table_name;

2）案例

hive (default)> drop table stu;

（5）清空表

1）语法

TRUNCATE [TABLE] table_name

注意：truncate只能清空管理表，不能删除外部表中数据。只对内部表有效

2）案例

hive (default)> truncate table student;

3、DML数据操作

3.1 Load

Load语句可将文件导入到Hive表中。

1）语法

hive> LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] 
INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)];

关键字说明：
（1）local：表示从本地加载数据到Hive表；否则从HDFS加载数据到Hive表。【本地节点实际上是取决于客户端的，有命令行以及JDBC–hiveserver2两种】
（2）overwrite：表示覆盖表中已有数据，否则表示追加。
（3）partition：表示上传到指定分区，若目标是分区表，需指定分区。

（0）创建一张表

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

必须得保证表的结构和文件的结构是一致的。

（1）加载本地文件到hive

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

• 如果在into前面加上overwrite字段，则表示覆盖，只会有一份数据。
• 如果把local去掉，就不是从本地导数据，而是从HDFS上导入。

（2）加载HDFS文件到hive中
①上传文件到HDFS

[root@hadoop102 ~]$ hadoop fs -put /home/wenxin/module/datas/student.txt /user/root

②加载HDFS上数据，导入完成后去HDFS上查看文件是否还存在【结果发现不存在了】

hive (default)> 
load data inpath '/user/atguigu/student.txt' 
into table student;

（3）加载数据覆盖表中已有的数据
①上传文件到HDFS

hive (default)> dfs -put /opt/module/datas/student.txt /user/atguigu;

②加载数据覆盖表中已有的数据

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

3.2 Insert

（1）将查询结果插入表中

1）语法

INSERT (INTO | OVERWRITE) TABLE tablename 
[PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement;

关键字说明：
（1）INTO：将结果追加到目标表
（2）OVERWRITE：用结果覆盖原有数据

2）案例

（1）新建一张表

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

（2）根据查询结果插入数据

hive (default)> insert overwrite table student3 
select 
    id, 
    name 
from student;

（2）将给定Values插入表中

1）语法

INSERT (INTO | OVERWRITE) TABLE tablename 
[PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES values_row [, values_row ...]

• 注意：此处不能使用into，只能使用overwrite
  2）案例

hive (default)> insert into table  student1 values(1,'wangwu'),(2,'zhaoliu');

（3） 将查询结果写入目标路径

1）语法

INSERT OVERWRITE [LOCAL] DIRECTORY directory
  [ROW FORMAT row_format] [STORED AS file_format] select_statement;

2）案例

insert overwrite local directory '/opt/module/datas/student' ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.JsonSerDe'
select id,name from student;

3.3 Export&Import

Export导出语句可将表的数据和元数据信息一并到处的HDFS路径，Import可将Export导出的内容导入Hive，表的数据和元数据信息都会恢复。Export和Import可用于两个Hive实例之间的数据迁移。