数据库相关操作
1. 查看hive下所有数据库
show databases
--result:
--class67
--default
--zhiyun
2. 创建数据库
--语法
CREATE database databaseName
--范式
CREATE database myDatabase
3. 切换数据库
--语法
use databaseName
--范式
use mydataBase
4. 删除数据库
--语法
DROP database databaseName
--范式
DROP database mydataBase
数据表相关操作
hive建立数据表方式(官网)
1. 直接建表(指定字段没有数据)
完整建表语句
--完整建表语法
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
[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 delimited
fields terminated BY 'char' --列分隔符
collection items terminated BY 'char' --集合内元素分隔符
MAP keys terminated BY 'char' --键值对分隔符
]
[STORED AS file_format] --表中数据文件的存储格式
]
[LOCATION hdfs_path] --设置表位置
[TBLPROPERTIES (property_name=property_value, ...)] --设置建表参数
[AS select_statement]; --查询建表
--范式1
--建立一张普通表(内部表)
CREATE TABLE myTable(
id int,
name string
)
ROW format delimited
fields terminated BY ','
2. 查询建表法(不用指定字段, 字段结构和数据来自于子查询)
--语法
CREATE TABLE TableName --注意在这里不声明字段
[除字段声明外的所有完整语句都可以书写]
AS SELECT子句
--范式1
CREATE TABLE myTable_select
AS
SELECT * FROM myTable
3. like建表法(不用指定字段,没有数据)
--语法
CREATE TABLE targetName
LIKE sourceTable
--范式
CREATE TABLE myTableLike
LIKE myTable
hive中的四种数据表
1. 内部表(Managed)---Default
--建立内部表(默认)
CREATE TABLE tableName
[完整建表语句]
--范式
CREATE TABLE myTableManaged
(id int,name string)
ROW format delimited
fields terminated BY ','
2. 外部表(External)
--语法
CREATE EXTERNAL TABLE tableName
[完整建表语句]
--范式
CREATE EXTERNAL TABLE myTableExternal
(
id int,
name string
)
ROW format delimited
fields terminated BY ','
内部表和外部表的相互转换
--外部表和内部表的相互转换
ALTER TABLE tablename SET TBLPROPERTIES ('EXTERNAL' = 'true')
3. 分区表
3.1 建立静态分区表
建立分区表
--1.建立分区表
--关键字: partitioned by
--语法 表字段中不包含分区字段
CREATE TABLE tableName
(字段)
partitioned BY (分区字段 字段类型)
ROW format delimited
fields terminated BY ','
CREATE TABLE myTableStaticPar
(
id int,
name string
)
partitioned BY (province string)
ROW format delimited
fields terminated BY ','
为分区表上传数据
--两种方式加载数据
--1.load 加载数据(无需维护元数据)
--本地数据加载
load data local inpath 'LocalPath(Linux)' into table tableName partition(分区字段='分区值');
--HDFS加载数据
load data inpath 'HDFSPath' into table tableName partition(分区字段='分区值');
--2. put加载数据(linux shell 执行)
hadoop fs -put LinuxPath TablePath(HDFS)
维护分区元素据
--语法
msck repair TABLE tablename
--范式
msck repair TABLE myTableStaticPar
查询数据
3.2 建立动态分区表
动态分区的核心是Insert+select
需要将数据存入普通表中,经过Insert+select进入分区表
设置动态分区参数
--设置动态分区
set hive.exec.dynamic.partition=true;
set hive.exec.max.dynamic.partitions=10000;
set hive.exec.max.dynamic.partitions.pernode=10000;
--开启hive 的非严格模式
set hive.exec.dynamic.partition.mode=nonstrict;
建立普通表(包含分区字段)
--建立普通表(包含分区字段)
CREATE TABLE myCommonTable
(
id int,
name string,
province string
)
ROW format delimited
fields terminated BY ','
建立分区表
--建立分区表
CREATE TABLE myTableAsynPar
(
id int,
name string
)
partitioned BY (province string)
ROW format delimited
fields terminated BY ','
数据导入普通表
load DATA | put
数据插入分区表
--将数据由普通表导入到分区表
insert into targetTable partition(分区字段) select * from sourceTable --[insert 操作会触发 mapreduce计算]
INSERT INTO myTableAsynPar PARTITION(province) SELECT * FROM myCommonTable
查询数据
4. 分桶表
创建普通表(包含分桶字段)
CREATE TABLE myCommonTable
(
id int,
name string,
province string
)
ROW format delimited
fields terminated BY ','
创建分桶表(包含分桶字段)
CREATE TABLE myTableBuckets
(
id int,
name string,
province string
)
clustered by(id)
INTO 3 buckets --桶数
ROW format delimited fields terminated BY ','
数据插入
--数据由普通表插入分桶表 自动分桶
INSERT INTO bucket SELECT * FROM div_bucket
表分区操作
为表中添加新分区
--为表中添加新分区(SQL添加的分区会自动生成元数据)
--语法
ALTER TABLE tableName ADD PARTITION(分区字段='分区值')
--范式
ALTER TABLE emp_info ADD PARTITION(province='bj');
修改分区字段值
--修改分区字段值
--语法
ALTER TABLE tableName PARTITION(分区字段='分区值1') RENAME TO PARTITION(分区字段='分区值2')
--范式
ALTER TABLE emp_info PARTITION(province='sd') RENAME TO partition(province='qd');
删除表中已有分区
--删除表中已有分区(删除分区的MetaData)
--字段
ALTER TABLE tableName DROP PARTITION(分区字段='分区值')
--范式
ALTER TABLE emp_info DROP partition(province='qd')
表中数据文件存储格式
在HIVE中的表中数据以文件的形式存在
数据文件的存储有不同的存储格式(textfile orc parquet...)
每种存储格式下又有不同的压缩算法
常见存储格式
Hive中数据源文件的常见存储格式有三种
1. TextFile ---Defaul 默认存储格式
2. ORC压缩格式
3. PARQUET 压缩格式
orc 压缩效率 高于 parquet 高于 TextFile
--一般在hadoop集群上运行一个MapReduce会有以下步骤: input-> Map-> shuffle(落盘) -> reduce -> output
-- 如果我们采用了数据压缩,在map阶段产生的数据大小就会减少,会减少磁盘的IO,同时还能够减少网络的IO
常见压缩算法
Snnapy 压缩算法
ZLIB 压缩算法 : ORC 格式默认的压缩算法
GZIP 压缩算法
LZO 压缩算法
zstd 压缩算法: paruqet 格式默认使用的压缩算法
压缩的文件都不能直接加载进入表中,必须要先进入普通表查询进入压缩表中(也就是必须要走MapReduce)
默认存储格式 TextFile
1. 创建TextFile格式的表
--范式
CREATE TABLE myTableTextFile
(
count_date string,
county string,
state string,
code int,
cases int,
deaths int
)
row format delimited
fields terminated by ',';
2. 修改TextFile的压缩算法
--开启参数
SET hive.exec.compress.output=true;
-- textfile 使用 snnapy 压缩
CREATE TABLE myTableTextfile2(
count_date string,
county string,
state string,
code int,
cases int,
deaths int) row format delimited fields terminated by ','
tblproperties ("compress"="SNAPPY");
ORC压缩存储格式
1. 创建ORC存储格式的表
-- 创建 orc 存储格式的表
CREATE TABLE myTableORC(
count_date string,
county string,
state string,
code int,
cases int,
deaths int)
row format delimited fields terminated by ','
stored as orc;
-- 带有压缩格式的文件必须要经过 底层mapreduce 计算加工才行
--普通表 插入 ORC压缩表
insert into myTableTextFile select * from myTableORC
2. 修改ORC存储格式默认的压缩方式
-- 修改 orc 格式 的 默认压缩算法
CREATE TABLE myTableORC(
count_date string,
county string,
state string,
code int,
cases int,
deaths int)
row format delimited fields terminated by ','
stored as orc
tblproperties ("orc.compress"="SNAPPY");
insert into myTableORC select * from myTableTextFile;
3. PARQUET压缩存储格式
-- parquet 存储格式
CREATE TABLE myTableParquet(
count_date string,
county string,
state string,
code int,
cases int,
deaths int)
row format delimited fields terminated by ','
stored as parquet;
-- 使用insert方式 导入数据, 走mapreduce程序
insert into myTableParquet select * from myTableTextFile;