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


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值