Hive数据结构及HiveDDL操作—Hadoop生态圈（六）

一、数据库操作

数据库是表的集合， HDFS中表现为一个文件夹 。默认在hive根目录下的warehouse目录下：

• 创建数据库

-- 创建数据库(这里我是在zeppelin里运行，尾部不用加;)
create database if not exists myhive

示例结果：

• 数据库其他操作

-- 使用数据库
use myhive

-- 查看所有数据库
show databases

-- 查看数据库信息
describe database myhive

-- 删除数据库
drop database if exists myhive cascade

示例结果（describe database myhive）：

• 如果数据库是空数据库，可以直接用drop删除，如果数据库不为空，可以采用cascade命令 ，强制删除。

drop database test

drop database test2 cascade

• 如果数据库较多，可以过滤显示查询的数据库，比如：

show databases like 'test*'

---

二、表操作

2.1 建表

• 建表语法：

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]
[Like table_name]

• 字段解释说明：
  • CREATE TABLE：创建一个指定名字的表；如果相同名字的表已经存在则抛出异常；用户可以用IF NOT EXISTS选项来忽略这个异常；
  • EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径(LOCATION)Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据;
  • COMMENT：为表和列添加注释;
  • PARTITIONED BY：创建分区表
  • CLUSTERED BY：创建分桶表
  • SORTED BY：不常用
  • ROW FORMAT:用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe Hive通过 SerDe确定表的具体的列的数据;
  • STORED AS 指定存储文件类型
  • LOCATION ：指定表在 HDFS上的存储位置。
  • LIKE允许用户复制现有的表结构，但是不复制数据。

---

a.内部表

        内部表又称管理表。Hive默认情况下会将这些表的数据存储在由配置项hive.metastore.warehouse.dir(例如，/opt/software/hadoop/hive/warehouse)所定义的目录的子目录下。当我们删除一个管理表时，Hive也会删除这个表中数据。

eg.

create table if not exists clientinfo(
clientName struct<first:string,last:string>,
age int,
hobbies array<string>,
address struct<province:string,city:string,district:string>,
detailAdd string,
deliveryAdd map<string,string>
)
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'
lines terminated by '\n'
stored as textfile
location '/opt/software/hadoop/hive110/warehouse/clientinfo'

---

b.外部表

        外部表数据存放在HDFS上，删除表时无法将数据删除，只能删除表结构。
eg.

create external table shop(
shopId int,
shopName string,
online boolean,
contact struct<mobile:string,fixed:string>,
address array<string>,
volumn map<string,decimal>
)
row format delimited 
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'	

---

c.临时表

        表只对当前session有效， session退出后，表自动删除。

语法：

CREATE TEMPORARY TABLE …

• 如果创建的临时表表名已存在，那么当前 session引用到该表名时实际用的是临时表，只有 drop或 rename临时表名才能使用原始表
• 临时表限制：不支持分区字段和创建索引

---

d.建表高阶语句

• CTAS as select方式建表
  • CTAS不能创建 partition, external, bucket table

eg.

create table shop_bak as select* from shop;

• CTE (CTAS with Common Table Expression)

create shop_bak as
WITH
r3 as (select name from employee where shopid= 2)
select* from r3

---

e.表的删除与修改

        删除表：With PERGE直接删除（可选），否则会放到 .Trash目录 。
eg.

-- 删除表
drop table if exists shop [purge]

-- 清空表数据
truncate table shop

---

f.装载数据

• LOAD用于在 Hive中移动数据；
• LOCAL：指定文件位于本地文件系统，执行后为拷贝数据,没有local则表示从HDFS上直接 移动数据；
• OVERWRITE：表示覆盖表中现有数据；

eg.

load data inpath '/tmp/shop.log' into table shop

load data local inpath '/root/shop.log' into table shop

load data local inpath '/root/shop.log' overwrite into table shop

---

2.2 分区表

        分区表实际上就是对应一个HDFS文件系统上的独立的 文件夹 ，该文件夹下是该分区所有的数据文件。 Hive中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过 WHERE子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

a.静态分区

e.g.

-- 创建分区表
create external table user_consumption(
buytime date,
paymoney decimal
)
partitioned by (username string)
row format delimited 
fields terminated by ','

-- 导入数据
load data local inpath '/root/jack.log' overwrite into table user_consumption partition(username ='jack')
load data local inpath '/root/mary.log' overwrite into table user_consumption partition(username ='mary')

示例结果：

• 分区表其他操作

-- 查看分区数
show partitions user_consumption

-- 查看分区表结构
desc formatted user_consumption

-- 增加单个分区
alter table user_consumption add partition(username ='curry') 

-- 增加多个分区
alter table user_consumption add partition(username ='paul') partition(username ='shily') 

-- 删除单个分区
alter table user_consumption drop partition (username ='curry')

-- 删除多个分区
alter table user_consumption drop partition(username ='paul') partition(username ='shily')

---

b.动态分区

        当使用静态分区时，在向分区表中插入数据时，我们需要指定具体分区列的值。此外，hive还支持动态提供分区值（即在插入数据时，不指定具体的分区列值，而是仅仅指定分区字段）。动态分区在默认情况下是禁用的 (在hive2.3.4版本 后 默认是开启的 )，所以需要将hive.exec.dynamic.partition设为true。默认情况下，用户必须至少指定一个静态分区列，这是为了避免意外覆盖分区。要禁用此限制，可以设置分区模式为非严格模式 (即将 hive.exec.dynamic.partition.mode设为nonstrict，默认值为strict)。可以选择在命令行终端方式设置：

SET hive.exec.dynamic.partition=true
SET hive.exec.dynamic.partition.mode=nonstrict

e.g.

-- 建表transaction
create external table transaction(
transaction_id  int,
customer_id int,
store_id int,
price decimal,
product string,
date string,
time string
)
partitioned by (datep string)

-- 建表transaction_bak
create external table transaction_bak(
transaction_id  int,
customer_id int,
store_id int,
price decimal,
product string,
date string,
time string,
datep string
)
row format delimited
fields terminated by '\t'

--导入数据
load data local inpath '/opt/transaction.log' into table transaction_bak 

-- 开启动态分区
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nostrick;

-- 导入数据设置分区字段
insert overwrite table transaction partition(datep)
select transaction_id,
customer_id,
store_id,
price,
product,
date,
time,
date_format(regexp_replace(date,'/','-'),'yyyy-MM') datep from transaction_bak

示例结果：

---

2.3 分桶表

• 我们可以将Hive中的分桶原理理解成 MapReduce中的 HashPartitioner的原理。都是基于 hash值对数据进行分桶。
• MR：按照 key的 hash值除以 reduceTask个数进行取余 (reduce_id = key.hashcode % reduce.num)。
• Hive:按照分桶字段 (列 )的 hash值除以分桶的个数进行取余 (bucket_id = column.hashcode % bucket.num)。

e.g.

-- 开启Hive分桶的开关
set hive.enforce.bucketing = true

-- 创建分桶表
create external table employee_id(
name string,
id int,
cities array<string>,
info struct<gender:string,age:int>,
scores map<string,int>,
position map<string,string>
)
clustered by (name) into 5 buckets
row format delimited
fields terminated by '|'
collection items terminated by ','
map keys terminated by ':'

---

2.4 侧视图

        Lateral view与用户定义的表生成函数 (如 explode())一起使用。正如在内置的表生成函数中提到的，UDTF为每个输入行生成零个或多个输出行。Lateral view首先将UDTF应用于基表的每一行，然后将产生的输出行连接到输入行，形成一个具有提供的表别名的虚拟表。
        即使侧视图通常不会生成一行，用户也可以指定可选的 OUTER关键字来生成行。当使用的UDTF不生成任何行时，就会发生这种情况。在这种情况下，源行永远不会出现在结果中。可以使用 OUTER来防止这种情况，并在来自UDTF的列中使用空值生成行。
        Outer关键字可以把不输出的UDTF的空结果，输出成 NULL，防止丢失数据。

e.g.

select printf('%s %s',first,last) name,hobby,age from jsontuple 
lateral view json_tuple(line,'name','hobbies','age') jt as name,hobbies,age 
lateral view json_tuple(name,'first','last') jt1 as first,last
lateral view explode(split(regexp_replace(hobbies,'\\[|\\]|"',''),',')) hs as hobby

示例结果：

---

PS：如果有写错或者写的不好的地方，欢迎各位大佬在评论区留下宝贵的意见或者建议，敬上！如果这篇博客对您有帮助，希望您可以顺手帮我点个赞！不胜感谢！

原创作者：wsjslient

作者主页：https://blog.csdn.net/wsjslient

---