Hive常用命令

作者: 雪飘千里 | 来源:发表于2019-10-28 11:07 被阅读0次

1、给用户赋予权限

# 角色创建与删除相关命令
create role rolename;
drop role name;
# 将某个角色赋予某个用户
grant role name to user username；
# 使某个用户拥有databasename数据库的select权限
grant select on database databasename to user username;

2、数据库新建/删除操作

创建数据库

create database hello_world;
create database if not exists database_name

查看所有的数据库

show databases;

进入某个数据库

use  hello_world;

删除数据库

//删除空的数据库
drop database if exists database_name; 
//先删除数据库中的表再删除数据库
drop database if exists database_name cascade;

3、hive表操作

查看所有表

show tables;

显示表结构

desc hello_world_inner;

显示表的分区

show partitions hello_world_parti;

显示创建表的语句

show create table table_name;

删除表

drop table t1;  
drop table if exists t1;

清空表（保留表结构）
内部表
```
truncate table 表名; 
```
外部表
由于外部表不能直接删除，所以用shell命令直接删除hdfs上的数据
```
#!/bin/bash
hdfs dfs -rm -r /user/hive/temp_table
```
重命名表名称

alter table table_name rename to new_table_name;

4、建表语句

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]

创建内部表

create table hello_world_inner
(
    id bigint, 
    account string, 
    name string,
    age int
)
row format delimited fields terminated by ',';

创建内部表

create EXTERNAL  table hello_world_inner
(
    id bigint, 
    account string, 
    name string,
    age int
)
row format delimited fields terminated by ',';

创建分区表

create table hello_world_parti
(
    id bigint,
    name string
)
partitioned by (dt string, country string);

参数说明：
EXTERNAL：创建外部表，在建表的同时可以指定源数据的路径（LOCATION），创建内部表时，会将数据移动到数据仓库指向的路径，若创建外部表不会有任何改变。在删除表时，内部表的元数据和源数据都会被删除，外部表不会删除源数据。

COMMENT：为表和列增加注释

PARTITIONED BY：创建分区表，
——PARTITIONED BY(dt STRING, country STRING)
CLUSTERED BY：创建分桶表
SORTED BY：创建排序后分桶表（不常用）
——CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS

ROW FORMAT DELIMITED：是用来设置创建的表在加载数据的时候，支持的列分隔符。Hive默认的分隔符是\001，属于不可见字符，这个字符在vi里是^A
—— ROW FORMAT DELIMITED FIELDS TERMINATED BY '\001'；

STORED AS：指定存储文件类型 sequencefile （二进制序列文件）、textfile（文本）、rcfile（列式存储格式文件）、ORC
如果文件数据是纯文本，可以使用 STORED AS TEXTFILE。
如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

LOCATION：指定表在 hdfs 上的存储位置

5、表结构修改

增加字段

alter table table_name add columns (newcol1 int comment ‘新增’)；

修改字段名称及类型

alter table table_name change col_name new_col_name new_type；

删除字段(COLUMNS中只放保留的字段)

alter table table_name replace columns (col1 int,col2 string,col3 string)；

删除分区

alter table table_name drop if exists partitions (d=‘2016-07-01');

注意：若是外部表，则还需要删除文件(hadoop fs -rm -r -f hdfspath)

修改字段分隔符和修改序列化分隔符

//修改字段分隔符
alter table aaa set serdeproperties('field.delim'='\t');
//修改序列化分隔符
alter table aaa set serdeproperties('serialization.format'='1');
//aaa为表名

6、字段类型

7、加载数据到hive表

1、从HDFS加载

//使用data_base_zhangkai数据库
use data_base_zhangkai;
LOAD DATA INPATH '/user/zhangkai/aaa' OVERWRITE INTO TABLE ods_aaa;

注意：INPATH后面的文件路径不能和hive表路径在hdfs上一致，最好是两个不同的文件路径，在加载过程中，源路径下的文件会被移动到hive表所在路径下，如果一致，会找不到文件错误；

2、从本地加载

use data_base_zhangkai;
LOAD LOCLDATA INPATH '/user/zhangkai/aaa' OVERWRITE INTO TABLE ods_aaa;

8、存储格式

Hive支持内置和自定义开发的文件格式。以下是Hive内置的一些格式：

默认是文本格式.
textfile 存储空间消耗比较大，并且压缩的text 无法分割和合并查询的效率最低,可以直接存储，加载数据的速度最高.
sequencefile 存储空间消耗最大,压缩的文件可以分割和合并查询效率高，需要通过text文件转化来加载.
rcfile 存储空间最小，查询的效率最高，需要通过text文件转化来加载，加载的速度最低.

相比传统的行式存储引擎，列式存储引擎具有更高的压缩比，更少的IO操作而备受青睐（注：列式存储不是万能高效的，很多场景下行式存储仍更加高效），尤其是在数据列（column）数很多，但每次操作仅针对若干列的情景，列式存储引擎的性价比更高。

1、TEXTFILE
默认格式，建表时不指定默认为这个格式，导入数据时会直接把数据文件拷贝到hdfs上不进行处理。源文件可以直接通过hadoop fs -cat 查看.
存储方式：行存储
磁盘开销大,数据解析开销大.
压缩的text文件 hive无法进行合并和拆分
2、SEQUENCEFILE
一种Hadoop API提供的二进制文件，使用方便、可分割、可压缩等特点。
SEQUENCEFILE将数据以< key,value>的形式序列化到文件中。序列化和反序列化使用Hadoop 的标准的Writable 接口实现。key为空，用value 存放实际的值，这样可以避免map 阶段的排序过程。
三种压缩选择：NONE, RECORD, BLOCK。 Record压缩率低，一般建议使用BLOCK压缩。文件和Hadoop api中的mapfile是相互兼容的。
```
    //使用时设置参数: 
set hive.exec.compress.output=true; 
set io.seqfile.compression.type=BLOCK;      – NONE/RECORD/BLOCK 
create table test2(str STRING) STORED AS SEQUENCEFILE; 
```
3、RCFILE
一种行列存储相结合的存储方式。首先，其将数据按行分块，保证同一个record在一个块上，避免读一个记录需要读取多个block。其次，块数据列式存储，有利于数据压缩和快速的列存取。
理论上具有高查询效率（但hive官方说效果不明显，只有存储上能省10%的空间，所以不好用，可以不用）。
RCFile结合行存储查询的快速和列存储节省空间的特点
1）同一行的数据位于同一节点，因此元组重构的开销很低；
2）块内列存储，可以进行列维度的数据压缩，跳过不必要的列读取。
查询过程中，在IO上跳过不关心的列。实际过程是，在map阶段从远端拷贝仍然拷贝整个数据块到本地目录，也并不是真正直接跳过列，而是通过扫描每一个row group的头部定义来实现的。
但是在整个HDFS Block 级别的头部并没有定义每个列从哪个row group起始到哪个row group结束。所以在读取所有列的情况下，RCFile的性能反而没有SequenceFile高。
4、ORC
hive给出的新格式，属于RCFILE的升级版。
ORC（OptimizedRC File）存储源自于RC（RecordColumnar File）这种存储格式，RC是一种列式存储引擎，对schema演化（修改schema需要重新生成数据）支持较差，而ORC是对RC改进，但它仍对schema演化支持较差，主要是在压缩编码，查询性能方面做了优化。RC/ORC最初是在Hive中得到使用，最后发展势头不错，独立成一个单独的项目。Hive 1.x版本对事务和update操作的支持，便是基于ORC实现的（其他存储格式暂不支持）。ORC发展到今天，已经具备一些非常高级的feature，比如支持update操作，支持ACID，支持struct，array复杂类型。你可以使用复杂类型构建一个类似于parquet的嵌套式数据架构，但当层数非常多时，写起来非常麻烦和复杂，而parquet提供的schema表达方式更容易表示出多级嵌套的数据类型。
ORC是RCfile的升级版，性能有大幅度提升，而且数据可以压缩存储，压缩比和Lzo压缩差不多，比text文件压缩比可以达到70%的空间。而且读性能非常高，可以实现高效查询。

9、配置优化

# 开启任务并行执行
set hive.exec.parallel=true
# 设置运行内存
set mapreduce.map.memory.mb=1024;
set mapreduce.reduce.memory.mb=1024;
# 指定队列
set mapreduce.job.queuename=jppkg_high;
# 动态分区，为了防止一个reduce处理写入一个分区导致速度严重降低，下面需设置为false
# 默认为true
set hive.optimize.sort.dynamic.partition=false;
# 设置变量
set hivevar:factor_timedecay=-0.3;
set hivevar:pre_month=${zdt.addDay(-30).format("yyyy-MM-dd")};
set hivevar:pre_date=${zdt.addDay(-1).format("yyyy-MM-dd")};
set hivevar:cur_date=${zdt.format("yyyy-MM-dd")};
# 添加第三方jar包, 添加临时函数
add jar ***.jar;
# 压缩输出，ORC默认自带压缩，不需要额外指定,如果使用非ORCFile,则设置如下
hive.exec.compress.output=true
# 如果一个大文件可以拆分，为防止一个Map读取过大的数据，拖慢整体流程，需设置
hive.hadoop.suports.splittable.combineinputformat
# 避免因数据倾斜造成的计算效率，默认false
hive.groupby.skewindata
# 避免因join引起的数据倾斜
hive.optimize.skewjoin
# map中会做部分聚集操作，效率高，但需要更多内存
hive.map.aggr   -- 默认打开
hive.groupby.mapaggr.checkinterval  -- 在Map端进行聚合操作的条目数目
# 当多个group by语句有相同的分组列，则会优化为一个MR任务。默认关闭。
hive.multigroupby.singlemr
# 自动使用索引，默认不开启，需配合row group index，可以提高计算速度
hive.optimize.index.filter

10、常用函数

if 函数，如果满足条件，则返回A，否则返回B
if (boolean condition, T A, T B)
case 条件判断函数, 当a为b时则返回c；当a为d时，返回e；否则返回f
case a when b then c when d then e else f end
Json操作
将字符串类型的数据读取为json类型，并得到其中的元素key的值
第一个参数填写json对象变量，第二个参数使用 $表示json变量标识，然后用.读取对象或数组； get_json_object(string s, '$ .key')
url解析 parse_url()
parse_url('http://facebook.com/path/p1.php?query=1','HOST')返回'facebook.com'
parse_url('http://facebook.com/path/p1.php?query=1','PATH')返回'/path/p1.php'
parse_url('http://facebook.com/path/p1.php?query=1','QUERY')返回'query=1'，
explode ：将hive一行中复杂的array或者map结构拆分成多行
explode(colname)
lateral view ：将一行数据adid_list拆分为多行adid后，使用lateral view使之成为一个虚表adTable，使得每行的数据adid与之前的pageid一一对应, 因此最后pageAds表结构已发生改变，增加了一列adid
select pageid, adid from pageAds
lateral view explode(adid_list) adTable as adid

字符串函数
- 字符串分割str，按照pat，返回分割后的字符串数组
  split(string str, string pat)
- 将字符串转为map, item_pat指定item之间的间隔符号，dict_pat指定键与值之间的间隔
  str_to_map(string A, string item_pat, string dict_pat)
- 反转字符串
  reverse()
- 字符串截取
  substring(string A, int start, int len)
- 字符串连接
  concat(string A, string B, string C, ...)
- 自定义分隔符sep的字符串连接
  concat_ws(string sep, string A, string B, string C, ...)
- 返回字符串长度
  length()
- 去除两边空格
  trim()
- 大小写转换
  lower(), upper()
- 返回列表中第一个非空元素，如果所有值都为空，则返回null
  coalesce(v1, v2, v3, ...)
- 返回第二个参数在待查找字符串中的位置（找不到返回0）
  instr(string str, string search_str)
- 将字符串A中的符合java正则表达式pat的部分替换为C;
  regexp_replace(string A, string pat, string C)
- 将字符串subject按照pattern正则表达式的规则进行拆分，返回index指定的字符，0:显示与之匹配的整个字符串， 1：显示第一个括号里的， 2：显示第二个括号里的
  regexp_extract(string subject, string pattern, int index)

类型转换
cast(expr as type)

提取出map的keys, 返回key的array
map_keys(map m)
日期函数
- 日期比较函数，返回相差天数，
  datediff('${cur_date},d)
  datediff(date1, date2)
- 返回当前时间
  from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss')

行转列
collect_set 去重
collect_list 不去重

//sub 为数组
SELECT p.member_id, collect_set(p.sub1) sub from temp p group by p.member_id;
// 把数组以逗号拼接为字符串
SELECT p.member_id, concat_ws(',',collect_set(p.sub1)) sub from temp p group by p.member_id;

列转行

//将表中path列（字符串）的数据以'/'切割为
select 
     member_id,parent_id
  from 
      recommend_path 
  LATERAL VIEW explode(split(path,'/')) idtable as parent_id 
  where parent_id != "" and parent_id is not null and parent_id != member_id

使用

从身份证中获取年龄

//id_no为身份证号
select (from_unixtime(unix_timestamp(),'yyyy')-substr(id_no,7,4))
age,id_no from aaa_bank_info  limit 100

日期判断
使用to_date(),如：to_date(orderdate)=‘2016-07-18’
字符串比较
HQL中字符串的比较比较严格，区分大小写及空格，因此在比较时建议upper(trim(a))=upper(trim(b))
update操作
HQL不支持update
实际采用union all + left join (is null)变相实现update
思路：
1.取出增量数据；
2.使用昨日分区的全量数据通过主键左连接增量数据，并且只取增量表中主键为空的数据（即，取未发生变化的全量数据）；
3.合并1、2的数据覆盖至最新的分区，即实现了update；
delete实现
采用not exists/left join(is null)的方法变相实现。
1.取出已删除的主键数据（表B）；
2.使用上一个分区的全量数据（表A）通过主键左连接A，并且只取A中主键为空的数据，然后直接insert overwrite至新的分区；
UNION实现
HQL中没有UNION，可使用distinct+ union all 实现 UNION；
with

join

Hive常用命令

1、给用户赋予权限

2、数据库新建/删除操作

3、hive表操作

4、建表语句

5、表结构修改

6、字段类型

7、加载数据到hive表

8、存储格式

9、配置优化

10、常用函数

使用

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读