Hive入门及常用指令

最新推荐文章于 2024-04-23 21:10:11 发布
最新推荐文章于 2024-04-23 21:10:11 发布
阅读量2.1k 收藏 5
点赞数
分类专栏： 大数据 文章标签： hive



Hive

show databases; # 查看某个数据库
use 数据库; # 进入某个数据库
show tables; # 展示所有表
desc 表名; # 显示表结构
show partitions 表名; # 显示表名的分区
show create table_name; # 显示创建表的结构

建表语句

内部表

use xxdb; create table xxx;

创建一个表，结构与其他一样

create table xxx like xxx;

外部表

use xxdb; create external table xxx;

分区表

use xxdb; create external table xxx (l int) partitoned by (d string)

内外部表转化

alter table table_name set TBLPROPROTIES (‘EXTERNAL’=‘TRUE’); # 内部表转外部表
alter table table_name set TBLPROPROTIES (‘EXTERNAL’=‘FALSE’);# 外部表转内部表

表结构修改

重命名表

use xxxdb; alter table table_name rename to new_table_name;

增加字段

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)；

删除表

use xxxdb; drop table table_name;

删除分区

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

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

字段类型

tinyint, smallint, int, bigint, float, decimal, boolean, string

复合数据类型

struct, array, map

复合数据类型

# array
create table person(name string,work_locations array<string>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';
# 数据
biansutao beijing,shanghai,tianjin,hangzhou
linan changchu,chengdu,wuhan
# 入库数据
LOAD DATA LOCAL INPATH '/home/hadoop/person.txt' OVERWRITE INTO TABLE person;
select * from person;
# biansutao       ["beijing","shanghai","tianjin","hangzhou"]
# linan           ["changchu","chengdu","wuhan"]

# map
create table score(name string, score map<string,int>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ','
MAP KEYS TERMINATED BY ':';
# 数据
biansutao '数学':80,'语文':89,'英语':95
jobs '语文':60,'数学':80,'英语':99
# 入库数据
LOAD DATA LOCAL INPATH '/home/hadoop/score.txt' OVERWRITE INTO TABLE score;
select * from score;
# biansutao       {"数学":80,"语文":89,"英语":95}
# jobs            {"语文":60,"数学":80,"英语":99}

# struct
CREATE TABLE test(id int,course struct<course:string,score:int>)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
COLLECTION ITEMS TERMINATED BY ',';
# 数据
1 english,80
2 math,89
3 chinese,95
# 入库
LOAD DATA LOCAL INPATH '/home/hadoop/test.txt' OVERWRITE INTO TABLE test;
# 查询
select * from test;
# 1       {"course":"english","score":80}
# 2       {"course":"math","score":89}
# 3       {"course":"chinese","score":95}
 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45

配置优化

# 开启任务并行执行
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
 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32

常用函数

# 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类型，并得到其中的元素key的值
# 第一个参数填写json对象变量，第二个参数使用$表示json变量标识，然后用.读取对象或数组；
get_json_object(string s, '$.key')
# 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'，
parse_url()
# 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
# 去除两边空格
trim()
# 大小写转换
lower(), upper()
# 返回列表中第一个非空元素,如果所有值都为空，则返回null
coalesce(v1, v2, v3, ...)
# 返回当前时间
from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss')
# 返回第二个参数在待查找字符串中的位置（找不到返回0）
instr(string str, string search_str)
# 字符串连接
concat(string A, string B, string C, ...)
# 自定义分隔符sep的字符串连接
concat_ws(string sep, string A, string B, string C, ...)
# 返回字符串长度
length()
# 反转字符串
reverse()
# 字符串截取
substring(string A, int start, int len)
# 将字符串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)
# 按照pat字符串分割str，返回分割后的字符串数组
split(string str, string pat)
# 类型转换
cast(expr as type)
# 将字符串转为map, item_pat指定item之间的间隔符号，dict_pat指定键与值之间的间隔
str_to_map(string A, string item_pat, string dict_pat)
# 提取出map的key, 返回key的array
map_keys(map m)
# 日期函数
# 日期比较函数，返回相差天数，datediff('${cur_date},d)
    datediff(date1, date2)

 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
  • 25
  • 26
  • 27
  • 28
  • 29
  • 30
  • 31
  • 32
  • 33
  • 34
  • 35
  • 36
  • 37
  • 38
  • 39
  • 40
  • 41
  • 42
  • 43
  • 44
  • 45
  • 46
  • 47
  • 48
  • 49
  • 50
  • 51
  • 52
  • 53
  • 54

HQL和SQL的差异点

# 1 select distinct 后必须指定字段名
# 2 join 条件仅支持等值关联且不支持or条件
# 3 子查询不能在select中使用；
# 4 HQL中没有UNION，可使用distinct+ union all 实现 UNION；
# 5 HQL以分号分隔，必须在每个语句结尾写上分号；
# 6 HQL中字符串的比较比较严格，区分大小写及空格，因此在比较时建议upper(trim(a))=upper(trim(b))
# 7 日期判断，建议使用to_date(),如：to_date(orderdate)=‘2016-07-18’
# 8 关键字必须在字段名上加``符号，如select `exchange` from xxdb.xxtb;
# 9 数据库和表/视图之间仅有1个点，如xx_db.xx_tb;

# HQL不支持update/delete
# 实际采用union all + left join (is null)变相实现update
# 思路：
# 1 取出增量数据；
# 2 使用昨日分区的全量数据通过主键左连接增量数据，并且只取增量表中主键为空的数据（即，取未发生变化的全量数据）；
# 3 合并1、2的数据覆盖至最新的分区，即实现了update；

# HQL delete实现
# 采用not exists/left join(is null)的方法变相实现。
# 1.取出已删除的主键数据（表B）；
# 2.使用上一个分区的全量数据（表A）通过主键左连接A，并且只取A中主键为空的数据，然后直接insert overwrite至新的分区；
 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21

# hive
hive是基于hadoop的一个数据仓库工具，可以将结构化的数据文件映射为一张数据库库表，并提供类SQL查询功能。

基本组成

用户接口：CLI，shell命令行；JDBC/ODBC是hive的java实现；webGUI是通过浏览器访问hive；
元数据存储：通常是存储在关系数据库如mysql, derby中；hive的元数据包括表的名字，表的列和分区及其属性，表的属性（是否为外部表），表的数据所在目录等。
解释器，编译器，优化器完成HQL查询语句从词法分析，语法分析，编译，优化以及查询计划的生成。生成的查询存储在HDFS中，并随后有mapreduce调用执行。
因此，hive与Hadoop的关系可以理解为用户发出SQL查询语句，hive将查询存储在HDFS中，然后由mapreduce调用执行。

table

Hive 中的 Table 和数据库中的 Table 在概念上是类似的，每一个 Table 在 Hive 中都有一个相应的目录存储数据。例如，一个表 pvs，它在 HDFS 中的路径为：/wh/pvs，其中，wh 是在 hive-site.xml 中由 ${hive.metastore.warehouse.dir} 指定的数据仓库的目录，所有的 Table 数据（不包括 External Table）都保存在这个目录中。

partition

Partition 对应于数据库中的 Partition 列的密集索引，但是 Hive 中 Partition 的组织方式和数据库中的很不相同。在 Hive 中，表中的一个 Partition 对应于表下的一个目录，所有的 Partition 的数据都存储在对应的目录中。例如：pvs 表中包含 ds 和 city 两个 Partition，则对应于 ds = 20090801, ctry = US 的 HDFS 子目录为：/wh/pvs/ds=20090801/ctry=US；对应于 ds = 20090801, ctry = CA 的 HDFS 子目录为；/wh/pvs/ds=20090801/ctry=CA

buckets

Buckets 对指定列计算 hash，根据 hash 值切分数据，目的是为了并行，每一个 Bucket 对应一个文件。将 user 列分散至 32 个 bucket，首先对 user 列的值计算 hash，对应 hash 值为 0 的 HDFS 目录为：/wh/pvs/ds=20090801/ctry=US/part-00000；hash 值为 20 的 HDFS 目录为：/wh/pvs/ds=20090801/ctry=US/part-00020

external table

External Table 指向已经在 HDFS 中存在的数据，可以创建 Partition。它和 Table 在元数据的组织上是相同的，而实际数据的存储则有较大的差异。
Table 的创建过程和数据加载过程（这两个过程可以在同一个语句中完成），在加载数据的过程中，实际数据会被移动到数据仓库目录中；之后对数据对访问将会直接在数据仓库目录中完成。删除表时，表中的数据和元数据将会被同时删除。
External Table 只有一个过程，加载数据和创建表同时完成（CREATE EXTERNAL TABLE ……LOCATION），实际数据是存储在 LOCATION 后面指定的 HDFS 路径中，并不会移动到数据仓库目录中。当删除一个 External Table 时，仅删除元数据，表中的数据不会真正被删除。

全量数据和增量数据

查看分区信息
如果分区的大小随时间增加而增加，则最新的分区为全量数据
如果分区的大小随时间增加而大小上下变化，则每个分区都是增量数据

实际使用

# 增加分区
insert overwrite table table_name partition (d=‘${pre_date}’)

建表语句

进行分区，每个分区相当于是一个文件夹，如果是双分区，则第二个分区作为第一个分区的子文件夹

drop table if exists employees;
create table if not exists employees(
name string,
salary float,
subordinate array<string>,
deductions map<string,float>,
address struct<street:string,city:string,num:int>
) partitioned by (date_time string, type string)
row format delimited
fields terminated by ‘\t’
collection items terminated by ‘,’
map keys terminated by ‘:’
lines terminated by ‘\n’
stored as textfile
location ‘/hive/…’;

hive桶

分区是粗粒度的，桶是细粒度的

hive针对某一列进行分桶，对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶中

create table bucketed_user(id int, name string)
clustered by (id) sorted by (name) into 4 buckets
row format delimited
fields terminated by ‘\t’
stored as textfile;

注意，使用桶表的时候我们要开启桶表

set hive.enforce.bucketing=true;

将employee表中的name和salary查询出来插入到表中

insert overwrite table bucketed_user select salary, name from employees

如果字段类型是string，则通过get_json_object提取数据；
如果字段类型是struct或map,则通过col[‘xx’]方式提取数据；

shell指令

#!/bin/bash
hive -e "use xxxdb;"

cnt = `hive -e "..."`
echo "cnt=${cnt}"

# 循环语句
for ((i=1; i<=10; i+=1))
do
pre_date=`date -d -${i}days +%F`
done

# 定义日期
pre_date=`date -d -1days +%F`
pre_week=`date -d -7days +%F`

# 设置环境变量
export JAVA_HOME=jdk;
 
 
  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18

References:

复合数据结构map, struct, array用法，非常用函数介绍：https://my.oschina.net/leejun2005/blog/120463

regexp_extract: https://www.cnblogs.com/skyEva/p/5175377.html

lateral view和explode: https://blog.youkuaiyun.com/bitcarmanlee/article/details/51926530

基本概念：分区，桶…：https://blog.youkuaiyun.com/gamer_gyt/article/details/47210331

hive基本概念：https://blog.youkuaiyun.com/tototuzuoquan/article/details/73003730






1、创建普通表
create table test(id string,name string)
row format delimited fields terminated by ‘,’
stored as textfile;
 
查看在HDFS上面储存的路径 hadoop fs -ls /user/hive/warehouse/test
 
2、加载数据

本地加载：load data local inpath ‘/data/1.txt’ into table test;
HDFS加载 ： load data inpath ‘/user/data/1.txt’ into table test;
 
3、创建一个外部表
create external table test_external(id string,name string)
row format delimited fields terminated by ‘,’
stored as textfile LOCATION ‘/data’;
 
从hdfs上加载数据
load data inpath ‘/user/data/1.txt’ into table test_external;
 
抹掉之前的数据重写
load data inpath ‘/user/data/1.txt’ overwrite into table test_external;
 
4、删除表
drop table test
drop table test_external



1、hive分区表的创建
create table patition_table(name string,salary float,gender string)
partitioned by (dt string,dep string)
row format delimited fields terminated by ‘,’
stored as textfile;
 
2、查看表结构
desc patition_table;
 
3、查看分区
show partitions patition_table;
但如果表里面都没有数据，上面这个语句执行后不出任何结果
如果有数据显示如下
dt=2014-03-29/dep=USA
 
4、加载数据到分区表内
load data local inpath ‘/data/1.txt’ into table patition_table partition(dt=’2014-03-29’,dep=’USA’);
 
5、在hdfs上的路径分布如下
/user/hive/warehouse/patition_table/dt=2014-03-29/dep=USA/
 
6、删除一个分区
alter table patition_table drop partition (dt=’2014-03-29’,dept=’USA’);
 
7、查看前几条
select * from tablename limit 1;
在hive中不支持 select * from tablename limit 1 5; //1 to 5
 
8、selcet的嵌套
from (select name,salary,from patition_table) e selecet e.name,e.salary where e.salary>1000;
 
9、where in操作
select * from patition_table where salary in(7000,3000);
 
10、case when then 操作
select * case when salary,
case when salary <5000 then ‘L1’
 when salary >=5000 and salary <10000  then ‘L2’
 when salary >=10000 and salary <15000 then ‘L3’
 when salary >=15000 then ‘L4’
 else ‘L0’
 end
as salary_level,gender from patition_table;
 
11、having操作
select gender from patition_table group by gender having sum(salary)>100000;
如果不支持having
select gender from(select gender,sum(salary) as salary_total from patition_table group by(gender))e where e.salary_total>100000;
注意一下having后面跟着一定是一个聚合函数
 
12、group by
select gender,sum(salary) from patition_table group by gender;
 
13、给外部表加分区时，需要执行MSCK REPAIR TABLE  tablename
在创建动态分区时

1、group by操作
 group by 操作是在map端进行操作
 hive.map.aggr=false //(默认)
 hive.map.aggr=true //作用是在map端做一次聚合操作，要求内存要足够
 
2、join操作，只支持等值join
select a.val,b.val from a join b on(a.key=b.key)
 
3、mapjoin 操作，只适合一个大表时
select /*+mapjoin(b)*/ a.key,a.value from a join b on a.key=b.key;//小表放map join里面
 
4、left semi join操作
select * from things left semi join sales on (sales.id = things.id);
 
5、order by 和 sort by
 select * from order_test order by math;
注意:
order by 是做一个全局的排序，所有的数据发送到一个reduce上操作
set hive.mapred.mode=nonstrict //(默认值)
set hive.mapred.mode=strict //在该模式下必须指定limit
 
sort by只能保证在同一个reduce上排序，使用sort by 可以指定reduce个数(set mapred.reduce.tasks=<number>)
 
6、union all操作，作为多表合并操作
 union all 要求各表select出的字段类型必须完全匹配
 hive不支持顶层的union，只支持将union封装在子查询中，而且子查询中必须要有别名
 select * from (
 select count(*) as type1 from a  where a.math>70
 union all
 select count(*)as tyge1 from a where a.englist>80
 )tmp_user
 
7、索引
 1)、先创建一张表
  create table index_test(id int,name string)partitioned by (dt string) row format delimited fields terminated by ‘,’;
 2)、创建一个临时的表
  create table index_tmp(id int ,name string,dt string)  row format delimited fields terminated by ‘,’;
 
 3)、加载数据到临时表中
1、rcfile用途，压缩比例高，读取列更快，保证同一个record在一个快上
  1)、建表
create table hive_rc(name string,gender string, age int) row format delimited fields terminated by ‘,’ stored as rcfile;
  
  2)、load数据到hive_rc表中
由于以rcfile的存储格式中无法直接加载数据，需创建一个临时表先把数据记载到临时表中
create table hive_raw(name string,gender string, age int) row format delimited fields terminated by ‘,’ stored as textile;
 
load data local in path  ‘数据存放目录’into table hive_raw;
insert into table hive_rc select * from hive_raw;
 
2、hive自定义表结构
 
1)、文件格式如下:
     000377201207221125^^apple iphone 4s^^2
     132288201210331629^^thinking in java^^1
     132288201210331629^^thin sss^^1111
 
2)、add jar /$hive-x.y.z/lib/hive-contrib-x.y.z.jar
3)、创建一张表
create external table hive_ser(
times String,
product_name String,
sale_num string
)row format
serde ‘org.apache.hadoop.hive.contrib.serde2.RegexSerDe’
with serdeproperties
(‘input.regex’=’([^^]*)\\^\\^([^^]*)\\^\\^([^^]*)’,’output.format.string’=’%1 s s s %3$s’)
stored as textfile
 
3、复合类型
 1)、假设数据如下
  192.168.1.1,3105007010|3105007011|3105007012
  192.168.1.2,3105007020|3105007011|3105007022
 2)、创建表
create table login_array(
ip string,
uid array<bigint>
)
partitioned by (dt string)
row format delimited
fields terminated by ‘,’
collection items terminated by ‘|’
stored as textfile
3)、加载数据
4)、使用下标访问数组
 select ip,uid[0] from login_array;
5)、读取uid的长度
 select size(uid) as total from login_array;
6)、数组查找
 select * from login_array where array_contains(uid,2105007010)
 
 
map类型
1)、数据格式如下
2014-03-03 12:22:34#127.0.0.1#get#amap#src=123&code=456&cookie=789#status=success&time=2s
2014-03-03 12:22:34#127.0.0.1#get#autonavi#src=123&code=456&cookie=789#status=success&time=2s
2)、创建表
create external table map_test_raw(ts string,ip string,type string,logtype string,request Map<string,string>,response Map<string,string>)
row format delimited fields terminated by ‘#’
collection items terminated by ‘=’
stored as textfile
 
 
struct类型
1)、数据格式如下
192.168.1.1,zhangsan#40
192.168.1.1,lisi#41
 2)、创建表
create table login_struct(
ip string,
user struct<name:string,age:int>
)
row format delimited fields terminated by ‘,’
collection items terminated by ‘#’
stored as textfile
3)、加载数据
4)、查询数据
select user.name from login_struct load data local inpath ‘数据存放路径’into table index_tmp;
 
//生产线上不做2、3步骤，都已做过
 
 4)、设置 set hive.exec.dynamic.partition.mode=nonstrict;
        set hive.exec.dynamic.partition=true;
 5)、insert overwrite table index_test partition(dt) select id,name,dt from index_tmp;
 6)、create index index1_index_test on table index_test(id) as ‘org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler’with deferred rebuild
 7)、alter index index1_index_test on index_test rebuild;
 
 8)、show index on index_test;
 9)、show partitions index_test;
 
注意:创建索引的时候，表必须要有partition
在视图上不能创建index
index可以通过stored as 配置存储格式
 
在hdfs上存放的位置:
 
 
8、bucket操作
  是将表或分区中指定列的值为key进行hash，抽样
  set hive.enforce.bucketing=true;
 1)、创建表
create table tb_tmp(id int,age int,name string,timeflag bigint)
row format delimited fields terminated by ‘,’;
 
 2)、创建bucket表
create table tb_stu(id int,age int,name string, timeflag bigint) clustered by(id)
stored by(age) into 5 buckets row format delimited fields terminated by’,’;
 
3)、加载数据到tb_tmp下，创建数据
 
4)、将数据导入bucket表中
insert into table tb_stu select id,age,name,timeflag from tb_tmp;
 
5)、输出每逢5的记录数如1-10，抽取第5条第10条记录数来
select * from tb_stu tablesample(bucket 1 out of 5 on id);//在一个bucket每5个抽出一个记录数

1关系运算符
 1)、等值 =
 2)、不等值 <>
 3)、小于 <<
 4)、小于等于 <=
 5)、大于 >
 6)、大于等于 >=
 7)、空值 is null
 8)、非空值 is not null
 9)、like 比较 like
10)、java的like操作 rlike
11)、regexp操作 regexp
 
2、udf类型
1)、创建表
create table udf_table(
dt string,
username string,
score float,
total int,
)
row format delimited fields terminated by ‘,’
stored as textfile
2)、数据模型如下
[29/Sep/2013:00:10:07 +0800],gavin,23.12,45
3)、查询语句
select * from udf_table where username is NULL  //注意这里的null必须大写
 
3、数学运算
1)、加法 +
2)、减法 -
3)、乘法 *
4)、除法 /
5)、取余 %
6)、位与操作 &
7)、位或操作 |
8)、位异或操作 ^
9)、位取反操作 ~
 
select total+5 from udf_table
 
4、逻辑运算 and ,or ,not
5、数值运算
 1)、取整函数 round
 2)、指定精度取整函数 round
 3)、向下取整函数 floor
 4)、向上取整函数 ceil
 5)、向上取整函数ceiling
 6)、取随机数函数 rand
 7)、自然指数函数exp
 8)、以10为底对数函数log10
 9)、以2为底对数函数log2
10)、对数函数log
11)、幂运算函数pow
12)、幂运算函数power
13)、开平方函数 sqrt
14)、二进制函数 bin
15)、十六进制函数hex
16)、反转十六进制函数 unhex
17)、进制转换函数conv
18)、绝对值函数abs
19)、正取余函数pmod
20)、正弦函数 sin
21)、余弦函数cos
22)、反正弦函数asin
23)、反余弦函数acos
24)、positive函数 positive
25)、negative函数negative
 
6、日期函数
 1)、unix时间戳转日期函数 from_unixtime
 2)、获取当前unix时间戳函数unix_timestamp
 3)、日期转unix时间戳函数unix_timestamp
 4)、指定格式日期转unix时间戳函数 unix_timestamp
 5)、日期时间转日期函数to_date
 6)、日期转年函数year
 7)、日期转月函数month
 8)、日期转天函数day
 9)、日期转小时函数hour
10)、日期转分钟函数minute
11)、日期转秒函数second
12)、日期转周函数weekofyear
13)、日期比较函数 datediff
14)、日期增加函数 date_add
15)、日期减少函数 date_sub
 
7、if函数
1)、非空查找函数 coalesce
select coalesce(username) from udf_table
 
8、字符串函数
 1)、字符串长度 length
 2)、字符串反转函数 reverse
 3)、字符串连接函数 concat
 4)、带分隔符字符串连接函数 concat_ws
 5)、字符串截取函数 substr,substring
 6)、字符串转大写函数 upper,ucase
 7)、字符串转小写函数 lower,lcase
 8)、去空格函数 trim
 9)、左边去空格函数 ltrim
10)、右边去空格函数 rtrim

hive的调优
1、explain
explain select * from login_array where ip=’192.168.0.1’
explain extended select * from login_array where ip=’192.168.0.1’
这里会对要执行的语句进行优化
然后输入 select * from login_array where ip=’192.168.0.1’进行执行
 
调用本地的mr
hive.exec.mode.local.auto=true
set mapred.tmp.dir=/
 
2、设置hive任务执行在mr的队列
set mapred.queue.name=queue3
set mapred.job.queue.name=queue3
设置任务的优先级别
set mapred.job.priority=high
 
3、hive并行执行
hive会将一个任务转化成一个或多个stage
默认情况下hive只会执行一个stage
如果一个任务有多个stage，并且每个stage是依赖的，那么这个任务不可能并行执行
如果要设置并行执行
hive.exec.parallel 默认是false 如果要并行执行就要设置为true
<property>
<name>hive.exec.parallel</name>
<value>true</value>
</property>
 
4、设置mapper和reducer的个数
1)、map的个数由splits确定
2)、reduce的个数默认是1
3)、set mapred.reduce.tasks=15
 
 
5、jvm重用
1)、mapred.job.reuse.jvm.num.tasks 默认值是1 也就是jvm重用一次后就被销毁
       一般在小文件比较多得情况下使用
6、分区
1)、静态分区与动态分区
        静态分区一定会创建分区，不管select语句的结果有没有数据
        动态分区只有在select结果大于0的时候才会被创建分区
        动态分区会为每一个分区分配一个reduce 如set mapred.reduce.tasks=100
 
例如：
动态分区案例
insert overwrite table tbl_name partition(pt,if_online)
select field1,field2,….,pt,if_online from tbl where XXX
静态分区案例
insert overwrite table tbl_name partition(pt=20121023,if_online=1)
select field1,field2,….,field from tbl where XXX
 
 
7、推测执行
 mapreduce的配置
 set mapred.map.tasks.speculative.execution=false;
 set mapred.reduce.tasks.speculative.execution=false;
 hive.mapred.reduce.tasks.speculative.execution 设置成false
 
8、mapjoin,解决数据倾斜的场景下小表关联大表的问题，但如果小表很大
例子:先根据log取所欲的memberid,然后map join关联members取今天有日志的members的信息，然后再和log做map join
select /*+mapjoin(x)*/ * from log a
left outer join (select /*+mapjoin(c)*/d.* from (select distinct memberid from log) c
join members d
on c.memberid = d.memberid) x
on a.menberid = b.memberid
 
 hive安全问题
1、hive的hadoop安全整合
2、使用hive进行验证
3、hive的权限管理
4、分区级别的权限
5、自定义授权
6、使用hive进行验证
1)、hive-site.xml配置
  <property>
<name>hive.security.authorization.enabled</name>
<value>true</value>
  </property>
 
  <property>
<name>hive.security.authorization.createtable.owner.grants</name>
<value>all</value>
 </property>
 
2)、授权
grant select on database default to user zhangsan
收回权限
revoke select on database default from user zhangsan
 
3)、创建一个角色
create role zhangsan
4)、删除一个角色
drop role zhangsan
5)、将角色添加在某个用户组下
grant role zhangsan to user tifa
6)、查看tifa下被授权的用户
show role grant user zhangsan
7)、把select权限授权给zhangsan用户
grant select on database default to user zhangsan
8)、查看zhangsan被授予的权限
show grant user zhangsan on database default
 




【大数据入门核心技术-Hive】（十七）常用hive shell命令汇总
10-12 267
2、2、set -v 打印所有hadoop和hive配置变量。重置配置到默认hive-site.xml配置。执行linux shell命令。执行hdfs dfs 命令。二、常用实用hive语句。a、先插入初始化测试数据。a、先插入初始化测试数据。set -v 打印所有hadoop和hive配置变量。重置配置到默认hive-site.xml配置。执行linux shell命令。执行hdfs dfs 命令。二、常用实用hive语句。a、先插入初始化测试数据。a、先插入初始化测试数据。
Hive 基本使用--最常用的入门语句、Hive常用交互命令、hive其他命令操作
Jalen big data analysis的博客
11-29 722
一、Hive 基本使用--最常用的入门语句 ①启动外置 MySQL 版本hive [hdp@hdp02 ~]$ service mysql start #确保mysql服务开启 Starting MySQL SUCCESS! [hdp@hdp02 ~]$ jps #确保集群开启 ... [hdp@hdp02 ~]$ hive #使用hive的cli命令窗口连接 hive&g...
HiveSQL运行优化参数配置
数据掘金
09-28 2999
执行任务时Map的任务数配置 执行任务时Map的任务数配置，即执行任务时，上图标红的，number of mappers: 1 的数量。 map的个数设置其实对执行效率有很大的影响： 如果mappers数量过多，map任务启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费。而且，同时可执行的map数是受限的； 如果mappers数量过少，Hadoop的计算资源没有充分的利用，计算缓慢； map的个数主要的决定因素有： input的文件总个数，input的文件...
Hadoop 任务优先级设置（MapReduce）
csdn_wyl的博客
04-01 3227
作业提交到的队列：mapreduce.job.queuename 作业优先级：mapreduce.job.priority Pig版本： SET mapreduce.job.queuename root.etl.distcp; SET mapreduce.job.priority HIGH; Hive版本： SET mapreduce.job.q
hive指定hadoop执行队列
weixin_30763455的博客
07-22 1690
指定队列的命令： 有三种： set mapred.job.queue.name=queue3; SET mapreduce.job.queuename=queue3; set mapred.queue.names=queue3; 老版本一般 mapred开头 新版本是mapreduce开头 老版本对应的新版本参数可以查出来 队列自己设定， 测试时注意 sele...
Hadoop常用参数、命令笔记
没有格子衬衫的程序员
09-03 1030
查看压缩文件、普通文件 hadoop fs -text filename hadoop fs -text /user/vsearch/offline/bangxi/il/part-r-02998.snappy | head hadoop fs –cat filename //只能查看未压缩文件 kill任务 hadoop job -kill-task xxx 迁...
HIVE从入门到精通.pdf
01-17
### HIVE从入门到精通知识点概述 #### 一、Hive简介 - **背景与需求**：随着商业智能领域数据量的急剧增加，传统的数据仓库解决方案成本高昂，难以满足需求。Hadoop作为一种流行且开源的MapReduce实现，在Yahoo、...
Hadoop和Hive入门了解
最新发布
m0_73749550的博客
04-23 1378
本课程使用的集群模式方案是:需要注意的是，在集群模式下，windows的运行内存至少是16G。
Hive从入门到高级
默主归沙的博客
03-26 2599
目录 一、Hive的简介 1.1 出现的原因 1.2 hive是什么 1.3 Hive的优缺点 二、hive的框架原理(重点) 二、hive的框架原理(重点) 2.1 hive的框架结构 2.2 hive的运行原理 2.3 hive和hadoop的关系 2.4 Hive与关系型数据库的比较 三、hive的安装模式 3.3 远程模式 3.3.1 概念 3.3.2 hive的服务端的配置 3.3.3 服务项的启动操作 3.3.4 客户端连接hiveserver2 3.3.5
Hive基本操作
Shyllin的博客
05-14 277
Hive基本操作
Hive-1-基础
daiyuxiong的博客
12-07 207
Hadoop 简介 Hadoop是一个开源框架来存储和处理大型数据在分布式环境中。包含两个模块，一个是MapReduce，另一个是Hadoop分布式文件系统（HDFS）。 MapReduce：是一种并行编程模型，在大型集群普通硬件可用于处理大型结构化，半结构化和非结构化数据。 HDFS：Hadoop分布式文件系统是Hadoop框架的一部分，用于存储和处理数据集。提供了一个容错文件系统在普通硬...
hive的基本优化笔记
allen的博客
10-16 689
hive 优化一. 查看执行计划：explain select * from lijie.test where id = '1'; explain extended select * from lijie.test where id = '1';二. 本地化hive.exec.mode.local.auto=false; default三. 设置队列（选取资源丰富的队列）mapred.queue.n
hive-参数调优
weixin_44280356的博客
04-19 2626
set hive.vectorized.execution.enabled=false; set mapreduce.map.speculative=false; set mapreduce.reduce.speculative=false; use ads_chpp_dev; set mapreduce.job.queuename=badm; ...
hive参数传递，hive常用函数
weixin_43288858的博客
07-06 5463
目录1、hive参数传递1、hiveconf使用说明2、 hivevar使用说明2、Hive的常用函数1、系统内置函数2、数值计算3、日期函数3、条件函数（重点）4、字符串函数5、集合统计函数7、复杂类型长度统计函数8、行转列9、列转行2、lateral view、explode练习reflect函数hive自定义函数Json数据解析UDF开发练习Hive的分桶表Hive数据导入Hive数据导出Hive Shell 命令导出export导出到HDFS上Hive的静态分区和动态分区1、 静态分区2、动态分区分
hive------select语法介绍
xiongxianze的博客
07-09 682
例子：班级表classes.txt，学生表students.txt，两个表文件数据如下：classes：1,班级1 2,班级2 3,班级3student：11,1,张三 12,1,李四 13,1,王五 21,2,Tom 22,2,Gerry 23,2,Simon 24,2,Jim 91,\N,Jeffrey 92,\N,Leo 93,\N,even 94,\N,aaron 95,\N,addison
Hadoop Mapreduce优先级调度
a1a2a3a4的专栏
09-29 7860
作业提交到的队列：mapreduce.job.queuename 作业优先级：mapreduce.job.priority Pig版本： SET mapreduce.job.queuename root.etl.distcp; SET mapreduce.job.priority HIGH; Hive版本： SET mapreduce.job.q
Hadoop权限管理
不精通则死
12-26 2038
摘自：http://dongxicheng.org/mapreduce/hadoop-permission-management/ 1. 介绍 本文介绍的Hadoop权限管理包括以下几个模块： （1） 用户分组管理。用于按组为单位组织管理，某个用户只能向固定分组中提交作业，只能使用固定分组中配置的资源；同时可以限制每个用户提交的作业数，使用的资源量等 （2） 作业管理。包括作业提交
hive提交任务指定队列
qq_36470475的博客
07-27 2965
set mapred.job.queue.name=queue1; 老版本 set mapred.queue.names=queue1; 老版本 SET mapreduce.job.queuename=queue31; 新版本 mapreduce指定对列名称 -Dmapreduce.job.queuename=queue1
Hive入门：函数详解与数据操作指南
Hive入门教程深入解析 在Hive的世界里，掌握基础知识至关重要。Hive是Apache Hadoop的数据仓库工具，它允许用户通过SQL查询大规模数据存储系统。本篇文章旨在介绍Hive的核心概念、语法和常用函数，帮助读者快速上手...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值