Hive笔记-2

第 3 章 DDL (Data Definition Language) 数据定义

• DDL数据定义语言

• DML数据操作语言

3.1 数据库 (database)

3.1.1 创建数据库

1) 语法

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

2) 案例

1. 默认路径

1. 创建数据库指定路径

3.创建数据库,带有dbproperties

测试dbproperties

创建dbproperties

3.1.2 查询数据库

1) 展示所有数据库

(1) 语法

查看数据库:

Show Databases;

Like 就是模糊匹配的作用

Like后面可以写一个通配的表达式

查看数据库信息:

案例:

3.1.3 修改数据库

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;

2) 案例

(1) 修改 dbproperties (!!!------重要------!!!)

查看修改的信息有没有生效:

3.1.4 删除数据库

1) 语法

3.1.5 切换当前数据库

1) 语法

3.2 表(table)

3.2.1 创建表

3.2.1.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, ...)]

经典咏柳传 建表

TEMPORARY 临时表

EXTERNAL 内部表

data_type 数据类型

Hive读写文件的操作流程:

Deserializer 反序列化集合

Serializer 序列化集合

(2) 关键字说明:

• 1.TEMPORARY(临时表)

临时表,该表只在当前会话可见,会话结束,表会被删除。

• 2.EXTERNAL (重点)

外部表，与之相对应的是内部表（管理表）。管理表意味着Hive会完全接管该表，包括元数据和HDFS中的数据。而外部表则意味着Hive只接管元数据，而不完全接管HDFS中的数据。

• 3.data_type (重点)

Hive 中的字段类型可分为基本数据类型和复杂数据类型。

基本数据类型如下:

其中decimal为十进制精准数字类型

decimal接收两个参数decimal(16,2)

第1个参数是 十进制数,

第2个参数是 小数点的位数

使用string类型无需指定长度

复杂数据类型如下:

注: 类型转换

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

方式一: 隐式转换(自动转换)

具体规则如下:

a.任何整数类型都可以隐式地转换为一个范围更广的类型,如tinyint可以转换为int,int可以转换为bigint。

b.所有整数类型、float和string类型都可以隐式地转换成double。

c.tinyint、smallint、int都可以转换为float。

d.boolean类型不可以转换为任何其他的类型。

详情可参考Hive官方说明：Allowed Implicit Conversions

为什么string类型加int类型会变成double类型

因为根据官方表,两个类型都能转的类型是double类型,所以是double类型

方式二: 显示转换(强制转换)

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

a.语法

cast (expr as <type>)

expr是要转的数据, <type>是要转的类型

b.案例

hive (default)> select '1' + 2, cast('1' as int) + 2;
​
_c0    _c1
3.0     3

• 4.PARTITIONED BY （重点）

创建分区表

• 5.CLUSTERED BY ...SORTED BY...INTO...BUCKETS （重点）

创建分桶表

• 6.ROW FORMAT （重点）

指定SERDE，SERDE是Serializer and Deserializer的简写。Hive使用SERDE序列化和反序列化每行数据。详情可参考 Hive-Serde。语法说明如下：

语法一：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 keys terminated by ：map中的key与value的分隔符

lines terminated by ：行分隔符

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

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

• 7. STORED AS （重点）

指定文件格式，常用的文件格式有，textfile（默认值），sequence file，orc file、parquet file等等。

• 8. LOCATION

指定表所对应的HDFS路径，若不指定路径，其默认值为

${hive.metastore.warehouse.dir}/db_name.db/table_name

• 9. TBLPROPERTILES

用于配置表的一些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, ...)]

只复制了表结构,没有表数据,打开这张表是个空表

三种建表方式 :

普通建表(insert) Create Table As Select(CTAS)(复制数据) Create Table Like(不复制数据)

案例1(创建内外部表):

comment 注释(注解).

• if not exists student 是一个条件字句,表示如果student表不存在就创建student表。若存在则不创建。

• row format delimited fields terminated by '\t'：这个子句指定了表的数据存储格式。在这种情况下，它告诉Hive表的字段是由制表符（\t）分隔的。

• location '/user/hive/warehouse/student'：这个子句指定了Hive在HDFS上存储表数据的位置。在这个例子中，数据将被存储在HDFS的/user/hive/warehouse/student目录下。

自己在Datagrip运行:

Linux上student文件里的数据:

运行结果:

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

案例2(SERDE和复杂数据类型):

三大复杂数据类型: array map struct

运行结果:

实操演示:

给他挂到HDFS上:

在Datagrip上运行:

创建成功:

可以select拿到自己想要拿到的数据:

案例3(create table as select 和 create table like):

create table as select

案例实操:

create table like

案例实操: