前言

总结常用hive语句, 方便查阅

操作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