常用hive语句

前言

总结常用hive语句, 方便查阅
作为对比,mysql语法请参照:https://blog.youkuaiyun.com/IAmListening/article/details/89386934

操作database

create 创建

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
    [COMMENT database_comment]
    [LOCATION hdfs_path]
    [WITH DBPROPERTIES (property_name=property_value, ...)];

1. schema和database的用法相同,这里记录一下混个眼熟
2. 数据库可以直接从hdfs上整体加载进来

drop 删除

DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE];

1. restrict是默认的删除方式,使用它等同于不加相应关键词
2. restrict(或者不加)只能够删除空库,cascade可以删除非空库

alter 更改

功能	语句
更改属性	ALTER DATABASE database_name SET DBPROPERTIES (property_name=property_value, ...);
更改拥有者	ALTER (DATABASE|SCHEMA) database_name SET OWNER [USER|ROLE] user_or_role;
更改数据源	ALTER (DATABASE|SCHEMA) database_name SET LOCATION hdfs_path;

1. dbproperties就是database properties的简写
2. 创建数据库时指定的属性大多都可以改变,但是库名不可以

其它

功能	语句
查看库信息	desc database mydata;
切换到指定数据库	use database_name;
查看当前数据库	select current_database();
设置显示当前库	set hive.cli.print.current.db=true;
设置显示表头	hive.cli.print.header=true;
开启本地模式	set hive.exec.mode.local.auto=true;
log4j文件指定日志存放	hive.log.dir=/tmp/atguigu/hive.log

操作table

创建: create

1. 使用已有表的表结构, 创建新表

-- 仅复制结构, 关键词like
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
	LIKE existing_table_or_view_name
	[LOCATION hdfs_path];

-- 复制表结构和内容, as select语句
CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
	[AS select_statement];

2. 完整的创建一张表

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
--	temporary:临时表 external:外部表
(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 [ESCAPED BY char]] 
-- 设置列分隔符(escaped by : 指定转义字符)
[COLLECTION ITEMS TERMINATED BY char]
--	设置集合的分隔符
[MAP KEYS TERMINATED BY char]
-- 设置键值对的分隔符
[LINES TERMINATED BY char]
-- 设置行分隔符
[NULL DEFINED AS ``char``]
-- 设置将null值存储为什么字符,默认为/n.
-- 如果表中有大量null,使用默认字符可能会占据大量空间.可以自定义为''
]
[STORED AS file_format]
-- 指定存储类型
[LOCATION hdfs_path]
-- 指定从数据库(在hdfs上的)的位置
;

导入: load/insert

功能	语句
从本地追加导入	load data local inpath '/root/person.txt' into table mytable;
从hdfs覆盖导入	load data inpath '/person.txt' overwrite into table mytable;
从其它表追加导入	insert into table mytable select * from mytable;

查询数据(dql): select

select name
-- 聚合函数, 别名可以在hiving和order by中使用
, count(name) as con
-- 开窗函数, 结算顺序在group by之后, 所以可以使用聚合函数
, row_number() over(partition by count(name) order by name)
from mytable
where age>13
group by name
having con > 1
order by con
limit 3;

删除: drop

功能	语句
删除	DROP TABLE [IF EXISTS] table_name [PURGE];
仅清空数据	TRUNCATE TABLE table_name [PARTITION partition_spec];

更改: alter

功能	语句
改名	alter table mytable rename to pstable;
增加列	alter table pstable add columns (info string, info2 string);
修改列	alter table pstable change column name myname string after age;
重置列	alter table pstable replace columns (name string, age int, love string);

更改表配置

功能	语句
设置跳过第一行	tblproperties ('skip.header.line.count'='1')
更改表说明	alter table table_name set tblproperties 'comment'='table_comment';
更改为内部表	alter table table_name set tblproperties 'EXTERNAL'='FALSE';
更改为外部表	alter table table_name set tblproperties 'EXTERNAL'='TRUE';
更改列分隔符	alter table table_name set serdeproperties ('field.delim'='\t');
查看表配置	show tblproperties table_name;

其它

功能	代码
查看表(简单)	desc mytable;
查看表(详细)	desc extended mytable;
查看建表语句	show create table mytable;
txt导出	insert overwrite [local] directory 'path' [row format delimited] select_statement;

分区

增加分区

ALTER TABLE table_name ADD [IF NOT EXISTS]
PARTITION partition_spec [LOCATION 'location']
[, PARTITION partition_spec [LOCATION 'location'], ...];

注1: 分区值必须是String类型
注2: add方法改变了表的元数据, 但是不会移动源数据

其它

功能	语句
删除分区	alter table table_name drop partition (partition_spec);
显示分区	show partitions table_name;
加载内容	load data local inpath 'location' into table table_name;

注1: 动态分区不能用load方式加载
这是因为动态分区需要使用列名,而load过程无法指定列名
注2: 纯动态分区需要调整设置, 如下

属性	键值对
是否允许纯动态分区	hive.exec.dynamic.partition=true
禁用严格模式	hive.exec.dynamic.partition.mode=nonstrict (strict启用)
最大区块	hive.exec.max.dynamic.partitions=1000
每个区块的最大容量	hive.exec.max.dynamic.partitions.pernode=100

分桶

增加分桶

ALTER TABLE table_name CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name, ...)]
INTO num_buckets BUCKETS;

加载数据

insert into table table_name select_statement;

注: 分桶数据只能使用insert into, 因为如果用load直接加载,分桶就失去了效果
查询数据

select * from table_name tablesample (bucket x out of y on uid);

注1:其中x表示从第几桶开始查询,起始为1
注2.:y是查询总的桶数.y可以是总的桶数的倍数或者是因子;x不能大于y

分桶相关的set

功能	键值对
强制分桶	set hive.enforce.bucketing=false/true
设置reduce个数	set mapreduce.job.reduces=4