Hive SQL

数据库相关操作

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;