hive笔记

纯笔记

        笔记基于《Hive编程指南》
 

数据定义

查看数据库、表、分区

show databases;
show databases like 'h.*'; -- 模糊查询

show tables;
show tables in database_name; -- 查看某个数据库下的表格
show tables like 'h.*'; -- 模糊查询
show tables 'tem.*'; -- 模糊查询

显示所有分区

show partitions database_name.table_name; 

在某一分区下查看其他分区

show partitions database_name.table_name partitions(fea_1='text');

建库

create database if not exists database_name;

建表

create table if not exists database_name.table_name (
    fea_1 int, -- 整数
    fea_2 float, -- 单精度浮点
    fea_3 double, -- 双精度浮点
    fea_4 string, -- 字符串
    fea_5 boolean, -- 布尔
    fea_6 timestamp, -- 时间戳：整数、浮点数、字符串
)
row format delimited
comment '新建一个表' -- 添加描述信息
tblproperties ('creator'='Joey', 'created_at'='2021-04-13 13:12:09') -- 键值对的格式为表格增加额外的文档说明
partitioned by (fea_5 boolean, fea_6 timestamp) -- 建立分区
fields terminated by ','; -- 分隔符

创建外部表

create external table if not exists table_name (
    -- external 表格这个新的表为外部表
    fea_1 int, -- 整数
    fea_2 float -- 单精度浮点
)
row format delimited fields terminated by ','
location '/data/stocks'; --location 表示外部数据来自于/data/stocks文件目录下
-- 整个语句为读取所有位于/data/stocks目录下的以逗号分隔的数据,建立一个新的表

查看表或者库的结构信息

describe database database_name; -- 查看数据库信息
describe table database_name.table_name; -- 查看表格信息
describe table database_name.table_name.feature_name; --查看某一列的信息

切换数据库

use database_name;

初始设置

set hive.cli.print.current.db=true; -- 显示当前所在的数据库
set hive.mapred.mode=strict; -- 严格模式,禁止提交未添加分区过滤的任务
    -- 1、查询分区表在where语句中含有分区特征为过滤条件,不允许扫描所有分区
    -- 2、对于使用order by 语句的查询,必须使用 limit 进行限制
    -- 3、限制笛卡尔积的查询；=号判断情况,尽量使用on而不是where
set hive.mapred.mode=nonstrict; -- 非严格模式
set mapreduce.map.memory.mb=4096; -- 防止在map阶段出现内存不足的情况
set mapreduce.reduce.memory.mb=4096; -- 防止在reduce阶段出现内存不足的情况
set hive.execution.engine=mr; -- 修改查询引擎
set hive.cli.print.header=true;  -- 打印列名
set hive.resultset.use.unique.column.names=false; -- 不显示表名
set hive.exec.mode.local.auto=true; -- 本地模式,对于没必要使用mapReduce的查询语句,可以避免触发MapReduce任务
set hive.exec.parallel=True; -- 并行执行
set hive.exec.parallel.thread.number=16; -- 并行执行任务数量

linux环境下查询常用配置

set hive.cli.print.current.db=true; 
set hive.cli.print.header=true; -- 打印表头
set hive.resultset.use.unique.column.names=false; -- 不打印数据库名

删除数据库

drop database if exists database_name; -- 删除空数据库,如果库里面有表格,需要先删除表格,再删库
drop database if exists database_name cascade; -- 删除带表格的数据库

修改数据库

alter database database_name set dbproperties ('edited-by' = 'Joey');

复制表

create table if not exists database_name.table_name_copy like database_name.table_name;

载入数据

load data local inpath 'data_path'
into table database_name.table_name
partition (fea_name_1='', fea_name_2='');

载入文件数据

-- 覆盖写
load data local inpath '/home/da/yangshijin/blacklist/tab_bwg_product_black_list_offline.txt' overwrite into table gcenter.d2_bwg_product_black_list partition(type=0);
-- 追加写
load data local inpath 'AA01_p13_new_etl.txt' into table testing_sample_data partition(client_nmbr='AA01',batch='p13');

添加分区

alter table table_name add partition(fea_1='text', fea_2=2021);
alter table table_name add if not exists partition(fea_1='text', fea_2=2021);

修改分区路径===不转移不删除旧数据

alter table table_name partition(fea_1='text', fea_2=2021) 
set location 'partition_path';

修改分区名

ALTER TABLE table_name PARTITION (dt='2008-08-08') RENAME TO PARTITION (dt='20080808');

删除分区

alter table table_name drop if exists partition(fea_1='text', fea_2=2021);

修改列名

ALTER TABLE 表名 CHANGE 旧字段 新字段 类型;

删除表

drop table if exists database_name.table_name;

表格重命名-修改表名

alter table table_name rename to table_name_new;

修改列信息-修改列名
-- 格式

ALTER TABLE 表名 CHANGE 旧字段 新字段 类型;
alter table table_name change column col_old col_new int
after other_column -- 修改列名后移动到other_column之后
first -- 修改列名后移动到第一列
;

增加列

alter table table_name add columns (
    fea_1 string comment '添加的第一个特征',
    fea_2 long comment '添加个第二个特征'
);

删除或替换列(删除所有字段并重新指定新字段名)

alter table_name replace columns (
    fea_1 int comment 'comment_fea_1',
    fea_2 string comment 'comment_fea_2'
);

修改表属性

alter table table_name set tblproperties (
    'notes' = 'The process id is no longer captured; this column is always NULL');

数据操作

1、向管理表中装载数据

load data local inpath 'data_path' 
into table table_name -- 追加写入
overwrite into table table_name -- 覆盖写入
partition (fea_1='text', fea_2='text');

2、通过查询语句向表中插入数据

insert into table table_name -- 追加写入
insert overwrite table table_name -- 覆盖写入
partition (fea_1='text_1', fea_2='text_2')
select * from table_name as A
where A.fea_1='text_1' and A.fea_2='text_2';

动态插入分区

insert overwrite table table_name
partition(country, city)
select ..., se.country, se.city 
from table_name as se;

动态插入分区

set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.dynamic.partition=true;
insert overwrite table temp.temp_for_err_col partition(client_nmbr, batch);

动态+静态插入分区: 静态分区键必须在动态分区键之前

insert overwrite table table_name
partition (country='US', city) -- country静态值为US,city为动态插入
select ..., se.country, se.city 
from table_name as se
where se.country='US';

数据查重
-- 查找到重复数据，保留排序顺位第一条数据

select * from
(select *,row_number()over(partition by 唯一id,分区 order by 唯一id desc) 
as row_numbers from 库名.表名) 
as t1 where t1.row_numbers!=1 limit 100;

查看某一列不同的值有多少

select distinct y_label from table_name;

HiveSQL 查询

1、select ... from

select fea_1,fea_2 from table_name;
select fea_1,fea_2[0] from table_name; -- fea_2[0]表述提取fea_2数组中的第一个元素,若不存在,返回NULL
select fea_1,fea_2["key_1"] from table_name; -- fea_2["key_1"]表述提取fea_2字典中key_1键对应的值,若不存在,返回NULL
select fea_1,fea_2.key_1 from table_name; -- fea_2.key_1表述提取fea_2字典中key_1键对应的值,若不存在,返回NULL
select fea_1,'text.*' from table_name; -- 用正则提取特征

2、常用函数
2.1 算术运算符

+ - * / %(取余数) &(按位取与) |(按位取或) ^(按位取亦或) ~(按位取反)

2.2 聚合函数

count(*) 统计总行数,包括含有NULL值的行
count(expr) 计算提供expr表达式的值非NULL的行数
count(distinct expr[, expr_1, ...]) 计算提供的expr表达式的值去重后非NULL的行数
同理,sum(distinct x),avg(distinct x),min(x),max(x)
variance(col),var_pop(col) 求方差
var_samp(col) 求样本方差
stddev_pop() 求标准偏差
stddev_samp() 求标准样本偏差
covar_pop(col_1, col_2) 求协方差
covar_samp(col_1, col_2) 求样本协方差
corr(col_1, col2, ...) 求相关系数
percentile(bigint int_expr, p) int_expr在p(范围[0,1])处的对应的百分比
percentile(bigint int_expr, array(p1, p2, p3, ...)) int_expr在p(范围[0,1])处的对应的百分比
percentile_approx(double col, p[, NB]) col在p(范围[0,1])处对应的百分比,其中p是double型,NB是用于估计的直方图中的仓库数量,默认10000
collect_set(col) 返回集合col元素排重后的数组

>>> 通常可以设置 set hive.map.aggr=true; 来提高聚合的性能,但有可能会消耗更多的内存

2.3 逻辑运算符

A=B
A <=> B 当A和B都为NULL时,返回TRUE,其他情况等于A=B
<> != 不等于
>= <= > < 
A [not] BETWEEN B AND C
A is [not] NULL 判断空值
A [not] like B B是sql语句下的简单正则表达式
A rlike B 或 A regrep B B是一个正则表达式,使用的是JDK中正则表达式的规则,比like更强大一些

2.4 数学函数

round(double d) 返回double型d的bigint类型的近似值
round(double d, int n) 返回double型d的保留n位小数的double型的近似值
floor(double d) 向上取整
ceil(double d)  向下取整
ceiling(double d) 向下取整
rand() rand(int seed) 每行返回一个double型随机数,整数seed是随机因子
exp(double d) 返回e的d幂次方,返回的是个double型数值
ln(double d) 以自然数为底d的对数,返回double型数值
log10(double d) 以10为底d的对数,返回double型数值
log2(double d) 以2为底d的对数,返回double型数值
log(double base, double d) 以base为d的对数,返回double型数值
pow(double d, double p) 
power(double d, double p) 计算d的p次幂,返回double值
sqrt(double d) 计算d的平方根
bin(double i) 计算二进制值i的string类型值,其中i是bigint类型
hex(bigint i) 计算十六进制i的string类型值,其中i是bigint类型
hex(string i) 计算十六进制i的string类型值,其中i是string类型
hex(binary b) 计算二进制表达的b的string类型值
unhex(string i) hex(string str)的逆方法
conv(bigint num, int from_base, int to_base) 将bigint类型的num从from_base进制转换成to_base进制,并返回string结果
conv(string num, int from_base, int to_base) 
abs(double d) 计算d的绝对值
pmod(int a, int b) a对b取模
sin(double d) 返回d的正弦值
asin(double d) d的反正弦值
cos(double d) d的余弦值
acos(double d) d的反余弦值
tan(double d) 正切值
atan(double d) 反正切值
degrees(double d) 将double型弧度值d转换成角度值
radians(double d) 将double型角度值d转换成弧度值
positive(int i) 
positive(double i) 返回i的正数(等价于 \+i)
negative(int i) 
negative(double i) 返回i的负数(等价于 -i)
sign(double d) 如果d是正数,返回float值1.0；如果d是负数,返回-1.0,否则返回0.0
e() 数学常数e,也就是超越数
pi() 数学常数pi, 也就是圆周率

2.5 表生成函数

explode(ARRAY array) 返回0到多行结果,每行都对应输入的array数组中的一个元素
explode(MAP map) 返回0到多行结果,每行对应每个map键值对,其中一个字段是map,另一个字段对应map的值
explode(ARRAY<TYPE> a) 对于a中的每个元素,explode()会生成一行记录包含这个元素
inline(ARRAY<struct[,struct]>) 将结构体数组提取出来并插入到表中
json_tuple(string, jsonStr, p1, p2, ..., pN) 可以接受多个标签名称,对输入的json字符串进行处理
    与get_json_object这个udf类似,不过更加高效,一次执行就可获得多个键值
parse_url_tuple(url, partname1, partname2,...,partnameN) 从url中解析出N个部分信息。输入参数是URL以及多个要抽取的部分的名称。
    所有输入的参数的类型都是string。部分名称是大小写敏感的,而且不因包含有空格：HOST,PATH,QUERY,PROTOCOL,AUTHORITY,FILE,USERINFO,QUERY:<KEY_NAME>
parse_url(string url, string partname[, string key]) 从url中抽取指定部分的内容。
    partname表示要抽取的部分名称,对大小写敏感,可选：HOST,PATH,QUERY,REF,PROTOCOL,AUTHORITY,FILE,USERINFO,QUERY:<KEY>
        如果partname是QUERY的话,那么还需要指定第三个参数key
stack(INT n, col_1, ..., colM) 把M列转换成N行,每行都M/N个字段,n必须是个常数

2.6 其他内置函数
-- 编码

ascii(string s) 
    返回字符串s中首个ASCII字符的整数值
base64(BINARY bin) 
    将二进制值bin转换成基于64位的字符串
unbase64(string str) 
    将基于64位的字符串str转换成二进制值
binary(string s) 将输入的值转换成二进制值
decode(binary bin, string charset) 
    使用指定的字符集charset将二进制值bin解码成字符串
    (支持的字符集有：'US_ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16')
    如果任意输入参数为null,则结果为null
encode(string src, string charset) 
    使用指定的字符集charset将字符串sec编码成二进制值；支持的字符集与decode相同

类型转化

cast(<expr> as <type>) 将expr转换成type类型,如果转换失败,则返回NULL

字符串函数

concat(binary s1, binary s2, ...) 
    将二进制字节码按次序拼接成一个字符串
concat(string s1, string s2, ...) 
    将字符串s1,s2等拼接成一个字符串。
concat_ws(binary separator, binary s1, string s2) 
    和concat类似,不过可以使用执行的分隔符进行拼接
ngrams(array<array< string>> arr,int n,int k, int pf) 
    计算文件中前k个字尾。pf是精度系数
context_ngrams(array<array<string>>, array<string>, int k, int pf) 
    和ngrams类似,但是从每个外层数组的第二个单词数组来查找K的字尾
find_in_set(string s, string commaSeparatedString) 
    返回在以逗号分割的字符串中s出现的位置,如果没有找到则返回null
format_number(number x, int d) 
    将数值x转换成'#,###,###.##'格式字符,并保留d位小数。如果d为0,那么输出值就没有小数点后面的值
in 例如 test in (val_1, val_2,...)
    表示如果test值等于后面列表中的任意值的话,则返回true
in_file(string s, string filename) 
    如果文件名为filename的文件中有完整一行数据和字符串s完全匹配的话,则返回true
instr(string str, string substr) 
    查找字符串str中子字符串substr第一次出现的位置
length(string s) 
    计算字符串s的长度
locate(string substr, string str[,int pos]) 
    查找在字符串str中pos位置后字符串substr第一次出现的位置
lower(string s) 大写字母转小写
lcase(string s) 跟lower一致
upper(string s) 小写字母转大写
ucase(string s) 跟upper一致
lpad(string s, int lenm, string pad) 
    从左边开始对字符串s使用字符串pad进行填充,最终达到len长度为止。
    如果字符串s本身长度比len大的话,那么多余的部分会被去掉
ltrim(string s) 
    将字符串s左边出现的空格全部去掉。例如ltrim(' hive ')的结果是'hive '
rtrim(string s) 
    将字符串s右边出现的空格全部去掉。例如rtrim(' hive ')的结果是' hive'
trim(string s) 
    将字符串s前后出现的空格全部去除掉
printf(string format, obj ... args) 
    按照printf风格格式化输出输入的字符串
regexp_pattern(string subject, string regex_pattern, string index) 
    抽取字符串subject中符合正则表达式regex_pattern的第index个部分的子字符串
regexp_replace(string s, string regex, string replacement) 
    将字符串s中符合条件的部分替换成replacement所指定的字符串,如果replacement为空,查找到的部分将被去除掉
repeat(string s, int n) 
    重复输出n次字符串s
reverse(string s) 反转字符串s
rpad(string s, int len, string pad) 
    从右边开始对字符串s使用pad进行填充,直到达到len长度为止。如果字符串s本身长度比len大的话,那么多余的部分会被去掉
lpad(string s, int len, string pad) 
    从左边开始对字符串s使用pad进行填充,直到达到len长度为止。如果字符串s本身长度比len大的话,那么多余的部分会被去掉
sentences(string s, string lang, string locale) 
    将输入的字符串S转换成句子数组,每个句子又由一个单词数组构成。参数lang和locale是可选的,如果没有使用,则使用默认的本地化信息
size(MAP<k.v>) map中元素的个数
size(ARRAY<T>) 数组array的元素个数
space(int n) 返回n个空格
split(string s, string pattern) 
    按照正则表达式pattern分割字符串s,并将分割后的部分以字符串数组的方式返回
str_to_map(string s, string delim1, string delim2) 
    将字符串s按照指定分隔符转换成map,第一个参数是输入的字符串,第二个是键值对之间对那个的分隔符,第三个是键和值的分隔符
substr(string s, int start_index, int length) 
    对于字符串s,从start位置开始截取length长度的字符串
translate(input, from, to) 
    它会逐个的检查input字符串每个字符,如果在input中的某个字符在from的字符串中存在,则替换为在to字符串中的相同坐标下的字符。 
    三个参数中有一个是空,返回值也将是空值
    如果from字符串长度=to的字符串长度translate("input","abcdef-abcdef","123456"),替换不是说把abcdef替换成123456！！！而是把a替换成1,把b替换成2,把c替换成3,把d替换成4
    若from长度>to的长度,那么在from中比to中多出的字符将会被删除
    from长度<to的长度,不报错但是to里面长的字符没有意义
    from里有重复字符 比如abca,1231,重复的对应to的替换不会起作用,只关注第一个
replace(char, search_string, repalcement_string) 
    将char中的字符串search_string全部转换为replacement_string
    replace针对的是单个字符串,translate针对的是单个字符
-- 时间处理
from_unixtime(bigint unixtime[, string format]) 
    将时间戳秒数转成UTC时间,并用字符串表示,可以通过format规定的时间格式,指定输出的时间格式
unix_timestamp() 
    获取当前本地时区下的当前时间戳
unix_timestamp(string date)
    输入的时间字符串格式必须是yyyy-MM-dd HH:mm:ss,如果不符合则返回0,如果符合则将此时间字符串转换成Unix时间戳
    eg:unix_timestamp('2009-03-20 11:30:01')=1237573801
unix_timestamp(string date, string pattern) 
    将指定时间字符串格式字符串转换成Unix时间戳,如果格式不对则返回0。
    eg:unix_timestamp('2009-03-20', 'yyyy-MM-dd')=1237532400
to_date(string timestamp)
    返回时间字符串的日期部分
    eg:to_date('1970-01-01 00:00:00')='1970-01-01'
year(string date)
    返回时间字符串中的年份并使用INT类型表示
month(string date) 
    返回时间字符串钟的月份并使用INT类型表示
day(string date)
    返回时间字符串中的天并使用INT类型表示
hour(string date)
    返回时间字符串中的小时并使用INT类型表示
minute(string date)
    返回时间字符串中的分钟数
second(string date)
    返回时间字符串中的秒数
weekofyear(string date)
    返回时间字符串位于一年中第几个周内
datediff(string end_date, string start_date)
    计算开始时间start_date到结束时间end_date的天数差
    eg：datediff('2009-03-01', '2009-02-27') = 2
date_add(string start_date, int days)
    为开始时间start_date增加days天
date_sub(string start_date, int days)
    从开始时间start_date减去days天
from_utc_timestamp(timestamp timestamp, string timezone)
    如果给定的时间戳并非UTC,则将其转化为指定的时区下的时间戳
to_utc_timestamp(timestamp timestamp, string timezone)
    如果指定的时间戳是指定的时区下的时间戳,则将其转化成UTC下的时间戳

json解析

get_json_object(string json_string, string path) 从给定路径上的json字符串中抽取出json对象,并返回这个对象的json字符串形式。
    如果输入的json字符串是非法的,则返回NULL

3、group by 语句

-- 按照一个或多个列对结果进行分组,然后对每个组执行聚合操作
 select year(ymd), avg(price_close) from stocks 
 where exchange='NASDAQ' and symbol='AAPL'
 group by year(ymd);

having语句
    -- 通过一个简单的语法完成原本需要通过子查询才能对GROUP BY语句产生的分组进行条件过滤的任务。
    -- 如下是对前面的查询语句增加一个having语句来限制输出结果中平均收盘价要大于50

select year(ymd), avg(price_close) from stocks 
where exchange='NASDAQ' and symbol='AAPL'
group by year(ymd)
having avg(price_close) > 50;

4、join语句

inner join 内连接(join)  
left outer join 左外连接
right outer join 右外连接
outer join 外连接(过滤NULL)
FULL outer join 完全外连接

left semi join 左半开连接：返回左边表的记录,前提是其记录对于右边表满足on语句zhong
on语句中不支持使用or进行判断

5、order by 和 sort by 全局排序
-- 两种语法的区别只是关键字的不同,查询结果几乎一致
-- 如果使用的reduce的个数大于1的话,输出结果的序列可能旧不太一致

-- order by 操作的运行时间比较长
-- desc 降序排序  asc(默认)升序排序
select s.ymd,s.symbol,s.price_close from stocks s
order by s.sym asc, s.symbol desc;

select s.ymd,s.symbol,s.price_close from stocks s
sort by s.sym asc, s.symbol desc;

6、含有sort by 的 distribute by
-- 根据distribute by 进行分组,将同一类别放到同一个reduce中,然后组内进行排序,实现局部排序

select s.ymd,s.symbol,s.price_close from stocks s
distribute by s.symbol
sort by s.symbol asc, s.ymd asc;
-- 若需要全局排序,还可以将以上结果再进行order by

-- 以上例子中s.symbol列被用在了distribute by语句中,而s.symbol列和s.ymd 位于sort by 语句中
-- 如果这两个语句涉及到的列完全相同,而且采用的是升序排序方式(默认方式),那么cluster by就等价于前面的两个语句
-- 相当于是前面两个句子的简写方式

select s.ymd,s.symbol,s.price_close from stock s 
cluster by s.symbol;

-- 相当于前面语句把s.ymd去掉
-- 由于去掉了排序方式,输出结果中其他字段采用原始排序方式,即降序排序
-- 使用distribute by   sort by语句或其简化版的cluster by语句会剥夺sort by的并行性
-- 然而这样可以实现输出文件的数据是全局排序的

7、类型转换 cast(feature_name as type) 如果取值不合法,将返回NULL

select name,salary from employees where cast(salary as float)<10000;

-- 类型转换BINARY值,binary类型只支持转换成string类型,再转换成别的类型

select (2.0*cast(cast(b as string) as double)) from src;

8、抽样查询

rand()抽样,返回一个随机值

9、UNION ALL
-- 可以将2个或多个表进行合并。每个union子查询都必须具有相同的列,且对应特征数据类型一致
-- 没有去重功能

tb_a union all tb_b;

10、case ... when ... then 句式
case ... when ... then ... else ... end 句式跟if条件语句类似,用于处理单个列的查询结果

select name,salary,
       ,case when salary<50000 then 'low'
             when salary>=50000 and salary<70000 then 'middle'
             when salary>=70000 and salary<100000 then 'high'
             else 'very_high'
       end as bracket
from employees;

11、什么情况下hive可以避免进行mapreduce
    对于比较简单的查询任务,可以设置set hive.exec.mode.local.auto=true;就可以不触发mapreduce,也就是本地模式

12、where语句
    用于过滤条件,多个条件可以使用and和or相连接,不能在where语句中使用列别名

13、谓词操作符

a=b a等于b,返回true,否则返回false
a<=>b 如果a和b都为null,则返回true,其他的和等号操作符的结果保持一致,如果任一为null,则结果为null
a<>b,a!=b a或者b为null,则返回null,如果a不等于b,则返回true,反之返回false
a<b
a>b
a=b
a<=b
a>=b
a [not] between b and c (包含b\c,类似于大于等于和小于等于)
a is [not] null
a [not] like b
a rlike b, a regexp b

14、浮点数的比较
-- 浮点数据分为float和double类型,double类型的精确度更高,有可能会导致使用浮点数判断大小时出现错误

-- 使用cast将数据转成指定的类型
select name,salary,deductions['taxes'] from employees
where deductions['taxes'] > cast(0.2 as float);

15、like 和 rlike
-- 使用正则匹配对数据进行清洗或者输出限制

16、抽样查询
16.1 数据块抽样
-- 基于行数的数据块抽样,并不适合所有的文件格式。这种抽样的最小抽样单元是一个HDFS数据块
-- 如果表的数据大小小于普通的块大小128M的话,那么会返回所有行

select * from numberflat tablesample(0.1 percent) s;

16.2 分桶表的输入裁剪

select * from numberflat where number % 2 = 0;

视图

当sql语句较长时,可以通过创建视图把查询分割成多个小的、更可控的片段降低复杂度

from (
    select * from people join cart on cart.people_id=people.id
    where first_name='john'
    ) a select a.last_name where a.id=3;

创建视图：

create view shorter_join as 
select * from people join cart on cart.people_id=people.id
where first_name='john';

-- 如果视图涉及的表或者列不存在时，会导致视图查询失败
删除视图

drop view if exists view_name;

索引

创建索引

create index index_name on table table_name (col_name);
as 'org.apache.hadoop.hive.ql.index.compact.CompactIndexHandler'
with deferred rebuild
idxproperties ('creator' = 'me', 'created_at' = 'some_time')
in table index_name_table
partitioned by (col_name)
comment '说明';

重建索引
-- 如果指定了deferend rebuild, 那么新索引将呈现空白状态。在任何时候,都可以进行第一次索引创建或者使用alter index 对索引进行重建

alter index index_name
on table table_name
partition (index_name = 'index_name_new')
rebuild;

显示索引

show formatted index on table;

-- formatted关键字可以使输出中包含有列名称；
-- 将index关键字替换为indexs，输出中就可以列举多个索引信息

删除索引

drop index if exists index_name on table table_name;

表格修改

修改列名

ALTER TABLE 表名 CHANGE 旧字段 新字段 类型;

修改表名

alter table 旧表名 rename to 新表名;

修改分区

ALTER TABLE table_name PARTITION (dt='2008-08-08') RENAME TO PARTITION (dt='20080808');

删除分区

ALTER TABLE src.tab_t_score DROP IF EXISTS PARTITION (client_nmbr='AA124',batch='p6',model_code='AA124p6');

批量删除分区

alter table src.tab_g_score drop if exists partition (model_code='GD104000');

增加列：在最末尾增加一列

alter table table_name add columns (col_name type);
load data local inpath 'data_path.txt' into table_name; -- 为新增字段导入数据

分区

-- 通过建立分区，优化需要查询语句。
-- HDFS用于涉及存储数百万的大文件，而非数十亿的小文件。使用过多分区可能会创建大量非必要的hadoop文件和文件夹
-- 一个分区就对应着一个包含有多个文件的文件夹，影响计算处理能力
-- 常用的分区条件是日期

hive 优化

严格模式 
-- 1、对于分区表，除非where语句中含有分区字段过滤条件来限制数据范围，否则不允许执行
-- 2、对于使用了order by语句的查询，必须使用limit语句
-- 3、限制笛卡尔积的查询。若用户期望在执行join 查询的时候不使用on语句而是使用where语句
--    这时关系型数据库的执行优化器就可以高效地将where转化成on语句
--    hive没有这种优化，因此表足够大时，这个查询会出现不可控的情况

set hive.mapred.mode=strict;

并行执行

set hive.exec.parallel=true;

临时启动本地模式

set oldjobtracker=${hiveconf:mapred.job.tracker};
set mapred.job.tracker=local;
set mapred.tmp.dir = /home/edward/tmp;
set mapred.job.tracker=${oldjobtracker};

自动启动本地模式

set hive.exec.mode.local.auto=true;

限制mapper 和 reducer个数

set mapred.reduce.tasks=10;
set hive.exec.reducers.max = 4; -- 默认3，避免消耗过多资源

动态分区

set hive.exec.dynamic.partition.mode=nonstrict;
set hive.exec.dynamic.partition=true;
set hive.exec.max.dynamic.partitions=300000; -- 限制最大动态分区数量

推测执行

set mapred.map.tasks.speculative.execution=true;
set mapred.reduce.tasks.speculative.execution=true;

单个MapReduce中的GROUP BY
-- 将查询中的多个group by操作组装到单个mapreduce任务中

set hive.multigroupby.singlemr=false;

用户自定义函数(UDF)

-- 在etl处理中，一个处理过程可能包含多个处理步骤。用户可以针对一些特定的处理过程编写自定义函数，让计算高效的执行
发现和描述函数

show functions;
describe function function_name; -- 查看函数简介
describe function extended function_name; -- 查看函数详细文档

-- 所有的聚合函数、用户自定义函数、内置函数，都统称为用户自定义聚合函数(UDAF)
-- 聚合函数支持输入从0行到多行的零个到多个列，然后输出单一值

用户自定义表生成函数(UDTF)
-- explode()函数以array类型数据作为输入，然后对数组中的数据进行迭代，返回多行结果，一行一个数组元素值

select explode(array(1,2,3)) as element from src;

hive只允许表生成函数以特定的方式使用。例如，一个显著的限制就是，我们无法从表中产生其他的列

select name, explode(subordinates) from employees; -- 无法正常执行

可通过lateral view实现

select name,sub from employees
lateral view explode(subordinates) subview as sub;

-- lateral view可以将explod这个UDTF得到的行转列的结果集合在一起提供服务
-- 使用lateral view 需要指定视图别名和生成的新列的别名，对上述例子来说分别是subview 和 sub

自定义函数需要一定的java编程能力

-- hcat命令行选项
-- -e 通过命令行执行DDL语句
--  hcat -e "show tables;"
-- -f 执行包含有DDL语句的脚本文件
--  hcat -f setup.sql
-- -g 为创建的表执行组
--  hcat -g mygroup
-- -p 为创建的表指定目录权限
--  hcat -p rwxr-xr-x
-- -D 将键值对以java系统属性的形式传递给HCatlog
--  hcat -D log.level=INFO

常见错误

-- 1、hive执行时,报错 No partition predicate
    -- 查询表为分区表,需要添加where条件,限定在具体分区查询。
-- 2、交互查询 报 Table not found 'xxxx'
    -- 查询语句中不带数据库名时,默认在当先选中的数据库中查找表,出现该问题时,确保数据库选择项正确。
-- 3、Permission denied
    -- 用户没有表查询权限,可以去数仓表管理申请表权限
-- 4、创建临时表时报错 终端禁止建表
    -- 如果临时表,后面还需要使用,请以 tmp_ 开头命名
-- 5、创建表时报错 “Error creating temporary folder on:” 
    -- 这种一般是没有数据库的权限,看一下选择的库是否正确,或者到 数仓库管理 页面申请读写权限
-- 6、交互查询一直报异常：Number of reduce tasks is set to 0 since there's no reduce operator
    -- 这并不是错误,只是提示这次查询不需要 reduce过程
-- 7、报错  org.apache.hadoop.yarn.exceptions.YarnRuntimeException:java.io.IOException: Split metadata size exceeded 10000000
    -- 这种情况一般是查询的数据量过大造成的,可以通过 缩短查询表的分区时间段来避免
    -- 或者在sql 前面增加 set mapreduce.jobtracker.split.metainfo.maxsize = －1 ; 
-- 8、报错 total number of created files now is 100161, which exceeds 100000. Killing the job.
    -- 报错的原因是 SQL结果创建文件数超过10w个
    -- 一种是 set hive.exec.max.created.files 这个参数,调大文件个数
    -- 另一种是在SQL 最后加一个 DISTRIBUTE BY rand()  ,随机重新分布一下结果数据
    -- 参考：https://blog.csdn.net/zhanglh046/article/details/78578898
-- 9、表字段类型不一致,导致join结果数据量异常
    -- 报错语句
    create  TABLE bdsp_test.tmp_wilson_09243 as
    SELECT
     A.message_id as A_message_id
    ,B.message_id as B_message_id
    FROM
      bdsp_test.tmp_wilson_09241 A
    left join
      bdsp_test.tmp_wilson_09242  B
    on A.message_id = B.message_id
    -- 解决方法：join的时候,显式指定类型转换
    create  TABLE bdsp_test.tmp_wilson_09243 as
    SELECT
     A.message_id as A_message_id
    ,B.message_id as B_message_id
    FROM
      bdsp_test.tmp_wilson_09241 A
    left join
      bdsp_test.tmp_wilson_09242  B
    on A.message_id = cast(B.message_id as string)