一、数据库操作
数据库是表的集合， 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 |
扫一扫
1264
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
