Hive数据库与表操作全指南

目录
Hive数据库操作详解
 创建数据库
1）语法
2）案例
 查询数据库
1）展示所有数据库
（1）语法
（2）案例
2）检察数据库信息
（1）语法
（2）案例
 修改数据库
1）语法
2）案例
 删除数据库
1）语法
2）案例
 切换当前数据库
1）语法
 Hive表操作详解
 创建表
 语法
1）平凡建表
（1）完整语法
（2）关键字阐明：
2）Create Table As Select（CTAS）建表
3）Create Table Like语法
 案例
1）内部表与外部表
（1）内部表
（2）外部表
2）SERDE和复杂数据类型
3）create table as select 和 create table like
（1）create table as select
（2）create table like
 检察表
1）展示所有表
（1）语法
（2）案例
2）检察表信息
（1）语法
（2）案例
 修改表
1）重定名表
（1）语法
（2）案例
2）修改列信息
（1）语法
（2）案例
 删除表
1）语法
2）案例
 清空表
1）语法
2）案例

---

Hive数据库操作详解

 创建数据库

1）语法

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

复制代码

2）案例

• 创建一个数据库，不指定路径
  
  1. hive (default)> create database db_hive1;

  复制代码
  注：若不指定路径，其默认路径为 ${hive.metastore.warehouse.dir}/database_name.db
  
• 创建一个数据库，指定路径
  
  1. hive (default)> create database db_hive2 location '/db_hive2';

  复制代码
• 创建一个数据库，带有 dbproperties
  
  1. hive (default)> create database db_hive3 with dbproperties('create_date'='2022-11-18');

  复制代码

 查询数据库

1）展示所有数据库

（1）语法

1. SHOW DATABASES [LIKE 'identifier_with_wildcards'];

复制代码

注：LIKE 通配表达式阐明：* 表示恣意个恣意字符，| 表示或的关系。
（2）案例

1. hive> show databases like 'db_hive*';

复制代码

1. OK
2. db_hive_1
3. db_hive_2

复制代码

2）检察数据库信息

（1）语法

1. DESCRIBE DATABASE [EXTENDED] db_name;

复制代码

（2）案例

• 检察基本信息
  
  1. hive> desc database db_hive3;

  复制代码
  1. OK
  2. db_hive    hdfs://hadoop12:8020/user/hive/warehouse/db_hive.db   lzl   USER

  复制代码
• 检察更多信息
  
  1. hive> desc database extended db_hive3;

  复制代码
  1. OK
  2. db_name    comment    location    owner_name    owner_type    parameters
  3. db_hive3    hdfs://hadoop12:8020/user/hive/warehouse/db_hive3.db    lzl    USER    {create_date=2022-11-18}

  复制代码

 修改数据库

用户可以利用 ALTER DATABASE 命令修改数据库某些信息，其中可以或许修改的信息包括 dbproperties、location、owner user。必要留意的是：修改数据库 location，不会改变当前已有表的路径信息，而只是改变后续创建的新表的默认的父目录。
1）语法

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

  复制代码
• 修改 location
  
  1. ALTER DATABASE database_name SET LOCATION hdfs_path;

  复制代码
• 修改 owner user
  
  1. ALTER DATABASE database_name SET OWNER USER user_name;

  复制代码

2）案例

• 修改 dbproperties
  
  1. hive> ALTER DATABASE db_hive3 SET DBPROPERTIES ('create_date'='2022-11-20');

  复制代码

 删除数据库

1）语法

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

复制代码

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

• 删除空数据库
  
  1. hive> drop database db_hive2;

  复制代码
• 删除非空数据库
  
  1. hive> drop database db_hive3 cascade;

  复制代码

 切换当前数据库

1）语法

1. USE database_name;

复制代码

 Hive表操作详解

 创建表

 语法

1）平凡建表

（1）完整语法

1. CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
2. (
3.   (col_name data_type [COMMENT col_comment], ...)
4. )
5. [COMMENT table_comment]
6. [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
7. [CLUSTERED BY (col_name, col_name, ...)
8. [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
9. [ROW FORMAT row_format]
10. [STORED AS file_format]
11. [LOCATION hdfs_path]
12. [TBLPROPERTIES (property_name=property_value, ...)];

复制代码

（2）关键字阐明：

• TEMPORARY
    临时表，该表只在当前会话可见，会话竣事，表会被删除。
  
• EXTERNAL（重点）
    外部表，与之相对应的是内部表（管理表）。管理表意味着Hive会完全接管该表，包括元数据和HDFS中的数据。而外部表则意味着Hive只接管元数据，而不完全接管HDFS中的数据。
  
• data_type（重点）
    Hive中的字段类型可分为基本数据类型和复杂数据类型。
    基本数据类型如下：
     Hive阐明界说tinyint1byte有符号整数smallint2byte有符号整数int4byte有符号整数bigint8byte有符号整数boolean布尔类型，true大概falsefloat单精度浮点数double双精度浮点数decimal十进制精准数字类型decimal(16,2)varchar字符序列，需指定最大长度，最大长度的范围是[1,65535]varchar(32)string字符串，无需指定最大长度timestamp时间类型binary二进制数据复杂数据类型如下：
     类型阐明界说取值array数组是一组相同类型的值的集合array<string>arr[0]mapmap是一组相同类型的键-值对集合map<string, int>map['key']struct结构体由多个属性组成，每个属性都有本身的属性名和数据类型struct<id:int, name:string>struct.id注：类型转换
    Hive的基本数据类型可以做类型转换，转换的方式包括隐式转换以及表现转换。
    方式一：隐式转换
    具体规则如下：
    a. 任何整数类型都可以隐式地转换为一个范围更广的类型，如tinyint可以转换成int，int可以转换成bigint。
    b. 所有整数类型、float和string类型都可以隐式地转换成double。
    c. tinyint、smallint、int都可以转换为float。
    d. boolean类型不可以转换为任何别的的类型。
    详情可参考Hive官方阐明：Allowed Implicit Conversions
  
  https://tongyi.aliyun.com/qianwen/?sessionId=05dafa94c8504e1faa491422eb8defe2#LanguageManualTypes-AllowedImplicitConversions
    方式二：表现转换
    可以借助cast函数完成表现的类型转换
    a. 语法
  
  1. cast(expr as <type>)

  复制代码
  b. 案例
  
  1. hive (default)> select '1' + 2, cast('1' as int) + 2;

  复制代码
  1. _c0  _c1
  2. 3.0  3

  复制代码
• PARTITIONED BY（重点）
    创建分区表
  
• CLUSTERED BY ... SORTED BY .. INTO ... BUCKETS（重点）
    创建分桶表
  
• ROW FORMAT（重点）
    指定SERDE，SERDE是Serializer and Deserializer的简写。Hive利用SERDE序列化和反序列化每行数据。详情可参考 Hive-Serde。语法阐明如下：
    语法一： DELIMITED关键字表示对文件中的每个字段按照特定分割符举行分割，其会利用默认的SERDE对每行数据举行序列化和反序列化。
  
  1. ROW FORMAT DELIMITED
  2. [FIELDS TERMINATED BY char]
  3. [COLLECTION ITEMS TERMINATED BY char]
  4. [MAP KEYS TERMINATED BY char]
  5. [LINES TERMINATED BY char]
  6. [NULL DEFINED AS char]

  复制代码
  注：
  
  
  
  • FIELDS TERMINATED BY：列分隔符
    
  • COLLECTION ITEMS TERMINATED BY：map、struct和array中每个元素之间的分隔符
    
  • MAP KEYS TERMINATED BY：map中的key与value的分隔符
    
  • LINES TERMINATED BY：行分隔符
    
  语法二： SERDE关键字可用于指定其他内置的SERDE大概用户自界说的SERDE。例如JSON SERDE，可用于处置惩罚JSON字符串。
  
  1. ROW FORMAT SERDE serde_name
  2. [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

  复制代码
  
  
• STORED AS（重点）
    指定文件格式，常用的文件格式有textfile（默认值），sequence file，orc file、parquet file等等。
  
• LOCATION
    指定表所对应的HDFS路径，若不指定路径，其默认值为 ${hive.metastore.warehouse.dir}/db_name.db/table_name
  
• TBLPROPERTIES
    用于配置表的一些KV键值对参数
  

2）Create Table As Select（CTAS）建表

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

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

复制代码

3）Create Table Like语法

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

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

复制代码

 案例

1）内部表与外部表

（1）内部表

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

1. create table if not exists student(
2.   id int,
3.   name string
4. )
5. row format delimited fields terminated by '\t'
6. location '/user/hive/warehouse/student';

复制代码

预备其必要的文件如下，留意字段之间的分隔符。

1. [lzl@hadoop12 datas]$ vim /opt/module/datas/student.txt

复制代码

1. 1001        student1
2. 1002        student2
3. 1003        student3
4. 1004        student4
5. 1005        student5
6. 1006        student6
7. 1007        student7
8. 1008        student8
9. 1009        student9
10. 1010        student10
11. 1011        student11
12. 1012        student12
13. 1013        student13
14. 1014        student14
15. 1015        student15
16. 1016        student16

复制代码

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

1. [lzl@hadoop12 datas]$ hadoop fs -put student.txt /user/hive/warehouse/student

复制代码

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

1. hive (default)> drop table student;

复制代码

（2）外部表

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

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

复制代码

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

1. [lzl@hadoop12 datas]$ hadoop fs -put student.txt /user/hive/warehouse/student

复制代码

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

1. hive (default)> drop table student;

复制代码

2）SERDE和复杂数据类型

本案例重点训练SERDE和复杂数据类型的利用。
若现有如下格式的JSON文件必要由Hive举行分析处置惩罚，请考虑如何计划表？
注：以下内容为格式化之后的效果，文件中每行数据为一个完整的JSON字符串。

1. {
2.   "name": "dasongsong",
3.   "friends": [
4.     "bingbing",
5.     "lili"
6.   ],
7.   "students": {
8.     "xiaohaihai": 18,
9.     "xiaoyangyang": 16
10.   },
11.   "address": {
12.     "street": "hui long guan",
13.     "city": "beijing",
14.     "postal_code": 10010
15.   }
16. }

复制代码

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

1. hive>
2. create table teacher
3. (
4.   name   string,
5.   friends array<string>,
6.   students map<string,int>,
7.   address struct<city:string,street:string,postal_code:int>
8. )
9. row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe'
10. location '/user/hive/warehouse/teacher';

复制代码

创建该表，并预备以下文件。留意，必要确保文件中每行数据都是一个完整的JSON字符串，JSON SERDE才能准确地处置惩罚。

1. [lzl@hadoop12 datas]$ vim /opt/module/datas/teacher.txt

复制代码

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

复制代码

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

1. [lzl@hadoop12 datas]$ hadoop fs -put teacher.txt /user/hive/warehouse/teacher

复制代码

尝试从复杂数据类型的字段中取值
3）create table as select 和 create table like

（1）create table as select

1. hive>
2. create table teacher1 as select * from teacher;

复制代码

（2）create table like

1. hive>
2. create table teacher2 like teacher;

复制代码

 
 检察表

1）展示所有表

（1）语法

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

复制代码

注：LIKE 通配表达式阐明：* 表示恣意个恣意字符，| 表示或的关系。
（2）案例

1. hive> show tables like 'stu*';

复制代码

2）检察表信息

（1）语法

1. DESCRIBE [EXTENDED | FORMATTED] [db_name.]table_name

复制代码

注：EXTENDED：展示具体信息
FORMATTED：对具体信息举行格式化的展示
（2）案例

• 检察基本信息
  
  1. hive> desc stu;

  复制代码
• 检察更多信息
  
  1. hive> desc formatted stu;

  复制代码

 修改表

1）重定名表

（1）语法

1. ALTER TABLE table_name RENAME TO new_table_name

复制代码

（2）案例

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

复制代码

2）修改列信息

（1）语法

• 增加列
    该语句允许用户增加新的列，新增列的位置位于末了。
  
  1. ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], ...)

  复制代码
• 更新列
    该语句允许用户修改指定列的列名、数据类型、注释信息以及在表中的位置。
  
  1. ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]

  复制代码
• 替换列
    该语句允许用户用新的列集替换表中原有的全部列。
  
  1. ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...)

  复制代码

（2）案例

• 查询表结构
  
  1. hive (default)> desc stu;

  复制代码
• 添加列
  
  1. hive (default)> alter table stu add columns(age int);

  复制代码
• 查询表结构
  
  1. hive (default)> desc stu;

  复制代码
• 更新列
  
  1. hive (default)> alter table stu change column age ages double;

  复制代码
• 替换列
  
  1. hive (default)> alter table stu replace columns(id int, name string);

  复制代码

 删除表

1）语法

1. DROP TABLE [IF EXISTS] table_name;

复制代码

2）案例

1. hive (default)> drop table stu;

复制代码

 清空表

1）语法

1. TRUNCATE [TABLE] table_name

复制代码

留意：TRUNCATE 只能清空管理表，不能删除外部表中数据。
2）案例

1. hive (default)> truncate table student;

复制代码

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。