#HIVE操作
#建表
#数据类型
#数据类型 java mysql hive
#字符串 String char(n)/varchar(n)/text/… string/char(255)/varchar(65536)
#字符串 char
#整数 byte/short/int/long smallint/int(n)/bigint(n) smallint/int/bigint
#小数 float/double/BigDecial float/double/money/real float/double
numeric(m,n)/decimal(m,n) decimal(38,18)
#布尔 boolean bit boolean
#日期 java.util.Date date/datetime/timestamp date/timestamp
#列表 HashSet set(‘V1’,‘V2’,‘V3’,…) array<data_type>
#结构体 struct<col_name:data_type,…>
#键值 map<key_type,value_type>
#符合类型 uniontype<data_type,…>
#函数
desc function extended FUNC_NAME;
https://www.cnblogs.com/demonoliu/p/7725425.html
#数学函数
1、log(double base, double a) #以base为底的a的对数
2、pow(double a, double p) #计算a的p次幂
3、conv(bigint/string num, #将bigint/string类型的num从from_base进制转换成to_base进制
int from_base, int to_base) conv(21,10,2)—10101(把21从十进制转化为2进制)
4、pmod(int/double a, int/double b)#a对b取余
5、hex(string a) unhex(string a) #求一个字符串a的正反16进制转化 hex(‘abc’)—616263类似于简单的加密
6、[a]sin(double/decimal a) #求a的正反正弦值
7、[a]cos(double/decimal a) #求正反余弦值
8、[a]tan(double/decimal a) #求正反正切值
9、degrees(double/decimal a) #将弧度值转换角度值
10、radians(double/decimal a) #将角度值转换成弧度值
11、positive(int/double a) #返回a
12、negative(int/double a) #返回a的相反数
13、sign(double/decimal a) #如果a是正数则返回1.0，是负数则返回-1.0，否则返回0.0
14、e()/pi() #数学常数e/pi
15、greatest(T v1, T v2, …) #求最大值
16、least(T v1, T v2, …) #求最小值
17、bround(double a,int d) #double 财务舍入法
18、factorial(int a) #求a 20以内的阶乘
19、shiftleft(int a, int b) #按位左移
20、shiftright(int a, int b) #按位右移
#集合函数
1、int size(Map<K.V>) #求map的长度 比如select map_keys(Map(1.3,3.4);结果为2
2、int size(Array<T>) #求数组的长度
3、array<K> map_keys(Map<K.V>) #返回map中的所有key
4、array<V> map_values(Map<K.V>)#返回map中的所有value
5、array<T> sort_array(Array<T>)#按自然顺序对数组进行排序并返回
6、boolean array_contains(Array<T>, value)#如该数组Array<T>包含value返回true，否则返回false
array（T...)创建数组函数
map((K,V)...)创建键值对函数
struct（V。。。)创建结构体函数
#类型转换函数
type cast(expr as <type>)#将expr转换成type类型
cast("1" as bigint) 将字符串1转换成了bigint类型，如果转换失败将返回NULL
#日期函数
1、string from_unixtime(bigint unixtime, string format)#将时间的秒值转换成format格式
from_unixtime(1250111000,"yyyy-MM-dd") #得到2009-03-12
2、string date_format(date/timestamp/string date,string format)
date_format('2009-03-12','MM') 得到03
3、string current_date()
4、string to_date(string timestamp)#返回时间字符串的日期部分
5、string current_timestamp()当前时间戳
6、bigint unix_timestamp()#获取本地时区下的时间戳
7、bigint unix_timestamp(string date)#将格式为yyyy-MM-dd HH:mm:ss的时间字符串转换成时间戳 如unix_timestamp('2009-03-20 11:30:01') = 1237573801
8、bigint unix_timestamp(string date, string format_pattern)#将指定时间字符串格式字符串转换成Unix时间戳，如果格式不对返回0 如：unix_timestamp('2009-03-20', 'yyyy-MM-dd') = 1237532400
9、string date_add(string date,int days)
10、string add_months(current_date(),±2);
11、string next_day(current_date(),'Mo/Tu/WE/Th/Fr/Sa/Su');#日期之后的下一个Mo/Tu/WE/Th/Fr/Sa/Su
12、string last_day(current_date());#返天
13、string trunc(current_date(),'YY/MM');#返回日期的本年/本月的第一天
#返回日期的本季度第一天
concat_ws('-',cast(year(current_date()) as string),cast(ceil(month(current_date())/3)*3-2 as string),'01');
#返回日期的本周的第一天
date_add(next_day(current_date(),'SU'),-7);
14、int datediff(string enddate, string startdate)#计算开始时间startdate到结束时间enddate相差的天数
15、double months_between(string bigdate,string smalldate)#
#条件函数
1、T if(boolean Condition, T vTrue, T vFalse) #如果testCondition 为true就返回valueTrue,否则返回valueFalseOrNull ，（valueTrue，valueFalseOrNull为泛型）
2、T nvl(T value, T default_value) #如果value值为NULL就返回default_value,否则返回value
3、T coalesce(T v1, T v2, ...) #返回第一个非null的值，如果全部都为NULL就返回NULL 如：COALESCE (NULL,44,55)=44/strong>
4、T case a when b then c [when d then e]* [else f] end #如果a=b就返回c,a=d就返回e，否则返回f 如CASE 4 WHEN 5 THEN 5 WHEN 4 THEN 4 ELSE 3 END 将返回4
5、T case when a then b [when c then d]* [else e] end #如果a=ture就返回b,c= ture就返回d，否则返回e 如：CASE WHEN 5>0 THEN 5 WHEN 4>0 THEN 4 ELSE 0 END 将返回5；CASE WHEN 5<0 THEN 5 WHEN 4<0 THEN 4 ELSE 0 END 将返回0
6、boolean isnull( col/T ) #如果a为null就返回true，否则返回false
7、boolean isnotnull ( col/T) #如果a为非null就返回true，否则返回false
#字符函数
1、int ascii(string str)#返回str中首个ASCII字符串的整数值
2、string concat(string|binary A, string|binary B...)#对二进制字节码或字符串按次序进行拼接
3、string concat_ws(string SEP, string A, string B...)#与concat()类似，但使用指定的分隔符号进行分隔
4、string concat_ws(string SEP, array<string>)#拼接Array中的元素并用指定分隔符进行分隔
格式化字符串 printf（string,T...) '%s %d %.nf'
5、array<array<string>> sentences(string sentence);#字符串str将被转换成单词数组，不同符号结果不一样[["hello","world"],["how","are","you"]]
select sentences('hello world?how are you');----[["hello","world"],["how","are","you"]]
select sentences('hello world!how are you');----[["hello","world"],["how","are","you"]]
select sentences('hello world,how are you');----[["hello","world","how","are","you"]]
6、array<struct<string,double>> ngrams(array<array<string>>, int N, int K)
#按N个单词出现频次，倒序取TOP K
select ngrams(sentences('hello world?how are you'),1,2);
词频，连续1个单词倒序以后前2位
[{"ngram":["how"],"estfrequency":1.0},{"ngram":["you"],"estfrequency":1.0}]
7、array<struct<string,double>> context_ngrams(array<array<string>> arr,array<string>,int k)
#与ngram类似，但context_ngram()允许你预算指定上下文(数组)来去查找子序列
#与array中指定单词之后配合出现的频次，倒序取TOP K
select context_ngrams(sentences('hello kb10?how are youhello world,are you ok?'),array('you',null),2);
结果：[{"ngram":["ok"],"estfrequency":1.0}]
8、string decode(binary bin, string charset)
#使用指定的字符集charset将二进制值bin解码成字符串，支持的字符集有：'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'，如果任意输入参数为NULL都将返回NULL
9、binary encode(string src, string charset)
#使用指定的字符集charset将字符串编码成二进制值，支持的字符集有：'US-ASCII', 'ISO-8859-1', 'UTF-8', 'UTF-16BE', 'UTF-16LE', 'UTF-16'，如果任一输入参数为NULL都将返回NULL
select encode('我爱中华001abc你们','UTF-16BE')
select decode(encode('我爱中华001abc你们','UTF-16BE'),'UTF-16BE')
10、string format_number(number x, int d)
#将数值X转换成"#,###,###.##"格式字符串，并保留d位小数，如果d为0，将进行四舍五入且不保留小数
select format_number(478493145631.2546,5);
478,493,145,631.25460
11、string get_json_object(string json_string, string path)
#从指定路径上的JSON字符串抽取出JSON对象，并返回这个对象的JSON格式，如果输入的JSON是非法的将返回NULL,注意此路径上JSON字符串只能由数字 字母 下划线组成且不能有大写字母和特殊字符，且key不能由数字开头，这是由于Hive对列名的限制
select get_json_object('{"name":"henry"}','$.name')--- henry
select get_json_object('{"name":"henry","info":{"city":"nj"}}','$.info.city');---nj
select get_json_object('{"name":"henry","info":["city","nj"]}','$.info[0]');---city
12、boolean in_file(string line, string path)#path中指向的文件是否包含line
13、string parse_url(string urlString, string partToExtract [, string keyToExtract])
(url元素提取）
#返回从URL中抽取指定部分的内容，参数url是URL字符串，而参数partToExtract是要抽取的部分，
这个参数包含(HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, and USERINFO,
例如：parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'HOST') ='facebook.com'，
如果参数partToExtract值为QUERY则必须指定第三个参数key
如：parse_url('http://facebook.com/path1/p.php?k1=v1&k2=v2#Ref1', 'QUERY', 'k1') =‘v1’
14、string printf(String format, T...t)#按照printf风格格式输出字符串
select printf('%s,%d,%.2f','henry',18,23456.789);---henry,18,23456.79
15、Boolean like # %
16、Boolean rlike # [] {} ? + \d \w ...正则模糊函数
17、string regexp_replace(string src, string regex, string replacement)
#按照Java正则表达式PATTERN将字符串src中符合条件的部分成replacement所指定的字符串，如里REPLACEMENT这空的话，抽符合正则的部分将被去掉 如：regexp_replace("foobar", "oo|ar", "") = 'fb.' 注意些预定义字符的使用，如第二个参数如果使用'\s'将被匹配到s,'\\s'才是匹配空格
select regexp_replace('you me he y_ou young yuuuuuug','y\\w{2,3}','YOU');----YOU me he YOU YOUg YOUuuug
18、string regexp_extract(string src, string regex, int index)
抽取字符串subject中符合正则表达式pattern的第index个部分的子字符串，注意些预定义字符的使用，如第二个参数如果使用'\s'将被匹配到s,'\\s'才是匹配空格
select regexp_extract('namehenryokdarlingduck','name(.*?)ok(.*?)duck',2);
19、array<string> split(string str, string regex)
按照正则表达式pat来分割字符串str,并将分割后的数组字符串的形式返回
select split(regexp_replace('["henry","pola","ariel"]','\\[|\\]|"',''),',');---["henry","pola","ariel"]
select split('henry.chen@hotmail.com','\\.|@');----["henry","chen","hotmail","com"]
20、map<string,string> str_to_map(string str,string spel1,string spel2)
将字符串str按照指定分隔符转换成Map，第一个参数是需要转换字符串，第二个参数是键值对之间的分隔符，默认为逗号;第三个参数是键值之间的分隔符，默认为"："
select str_to_map('name:henry,age:22,gender:male');
select str_to_map('name#henry|age#22|gender#male','\\|','#');
结果： {"name":"henry","gender":"male","age":"22"}
array/struct/map转类型
21、string translate(string|char|varchar input, string|char|varchar from, string|char|varchar to)
(字符替换函数)
将input出现在from中的字符串替换成to中的字符串 如：translate("MOBIN","BIN","M")="MOM"
select translate('abcabcabaac','ab','*#');----*#c*#c*#**c
22、initcap 首字母大写
23、string substr(string|binary A, int start) substring(string|binary A, int start)
对于字符串A,从start位置开始截取字符串并返回
24、string substr(string|binary A, int start, int len) substring(string|binary A, int start, int len)
对于二进制/字符串A,从start位置开始截取长度为length的字符串并返回
select substr('henry',2,3);---enr
select substr('henry',2);enry
25、int locate(string substr, string str[, int pos])#查找字符串str中的pos位置后字符串substr第一次出现的位置，pos从1开始
select locate('en','henry',1);--2
26、int instr(string str, string substr) #查找字符串str中子字符串substr出现的位置，如果查找失败将返回0，如果任一参数为Null将返回null，注意位置为从1开始的
select instr('henry','en');--2
md5单向加密
base64
select base64(cast('henry' as binary));---aGVucnk=
select unbase64('aGVucnk=');----henry
hex/unhex
binary unbase64(string str)将64位的字符串转换二进制值
sha2(string/binary,int(0/1));单向加密int选0或1
select sha2('henry',1);--3eca10f30041813f045165784e24b5a950a6cc7e
aes_encrype
aes_decrype
int levenshtein(string A, string B) Returns the Levenshtein distance between two strings (as of Hive 1.2.0). For example, levenshtein('kitten', 'sitting') results in 3..
计算两个字符串之间的差异大小 如：levenshtein('kitten', 'sitting') = 3
string soundex(string A) Returns soundex code of the string (as of Hive 1.2.0). For example, soundex('Miller') results in M460..
将普通字符串转换成soundex字符串
select soundex('bc');--B200
string initcap(string A) Returns string, with the first letter of each word in uppercase, all other letters in lowercase. Words are delimited by whitespace. (As of Hive 1.1.0.).
将字符串A转换第一个字母大写其余字母的字符串
#聚合函数
BIGINT count(*), #统计总行数，包括含有NULL值的行
int count(expr), #统计提供非NULL的expr表达式值的行数
BIGINT count(DISTINCT expr[, expr...])#统计提供非NULL且去重后的expr表达式值的行数
double sum(col), sum(DISTINCT col)
sum(col),表示求指定列的和，sum(DISTINCT col)#表示求去重后的列的和
double avg(col), #表示求指定列的平均值
avg(DISTINCT col)#表示求去重后的列的平均值
double min(col) #求指定列的最小值
double max(col) #求指定列的最大值
double var_pop(col) #求指定列数值的方差:离散程度
double var_samp(col) #求指定列数值的样本方差：变异程度
double stddev_pop(col) #求指定列数值的标准偏差
double stddev_samp(col) #求指定列数值的样本标准偏差
double covar_pop(col1, col2) #求指定列数值的协方差
double covar_samp(col1, col2) #求指定列数值的样本协方差
double corr(col1, col2) #返回两列数值的相关系数
double percentile(BIGINT col, p) #返回col的p%分位数
collect_list(col) #列转行 （不去重）
collect_set(col) #列转行 （去重）
#表生成函数
1、Array Type explode(array<TYPE> a)#对于a中的每个元素，将生成一行且包含该元素
2、N rows explode(ARRAY<T>) 每行对应数组中的一个元素
select name,city from employee_id lateral view explode(cities) ct as city;
3、N rows explode(MAP<K,v>) 每行对应每个map键-值，其中一个字段是map的键，另一个字段是map的值
select name,pos,score from employee_id lateral view explode(scores) st as pos,score;
4、N rows posexplode(ARRAY)#与explode类似，不同的是还返回各元素在数组中的位置
select posexplode(array('aa','bb','cc'));
5、N rows stack(INT n, v_1, v_2, ..., v_k)#把M列转换成N行，每行有M/N个字段，其中n必须是个常数
select stack(2,'aa','bb','cc','dd');
6、tuple json_tuple(jsonStr, k1, k2, ...)#从一个JSON字符串中获取多个键并作为一个元组返回，与get_json_object不同的是此函数能一次获取多个键值
7、tuple parse_url_tuple(url, p1, p2, ...)#返回从URL中抽取指定N部分的内容，参数url是URL字符串，而参数p1,p2,....是要抽取的部分，这个参数包含HOST, PATH, QUERY, REF, PROTOCOL, AUTHORITY, FILE, USERINFO, QUERY:<KEY>
select printf('%s %s',fn,ln) name,age,hobby from jsontuple
lateral view json_tuple(line,'name','hobbies','age') jt as name,hobbies,age
lateral view json_tuple(name,'first','last') jt1 as fn,ln
lateral view explode(split(regexp_replace(hobbies,'\\[|\\]|"',''),',')) hs as hobby;
8、inline(ARRAY<STRUCT[,STRUCT]>)#将结构体数组提取出来并插入到表中
select inline(array(struct('aa','bb','cc'),struct('ee','dd','ff')));
#----------------------------------------------------------------------------
#https://www.cnblogs.com/hyunbar/p/13524855.html
#窗口函数
1.1 窗口函数
FIRST_VALUE：取分组内排序后，截止到当前行，第一个值
LAST_VALUE： 取分组内排序后，截止到当前行，最后一个值
LEAD(col,n,DEFAULT) ：用于统计窗口内往后第n行值。
第一个参数为列名，
第二个参数为往下第n行（可选，默认为1），
第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）
LAG(col,n,DEFAULT) ：用于统计窗口内往前第n行值。
第一个参数为列名，
第二个参数为往上第n行（可选，默认为1），
第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）
1.2 OVER从句
1、使用标准的聚合函数COUNT、SUM、MIN、MAX、AVG
2、使用PARTITION BY语句，使用一个或者多个原始数据类型的列
3、使用PARTITION BY与ORDER BY语句，使用一个或者多个数据类型的分区或者排序列
4、使用窗口规范，窗口规范支持以下格式：
(ROWS | RANGE) BETWEEN (UNBOUNDED | [num]) PRECEDING AND ([num] PRECEDING | CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN CURRENT ROW AND (CURRENT ROW | (UNBOUNDED | [num]) FOLLOWING)
(ROWS | RANGE) BETWEEN [num] FOLLOWING AND (UNBOUNDED | [num]) FOLLOWING
5、当ORDER BY后面缺少窗口从句条件，窗口规范默认是 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
6、当ORDER BY和窗口从句都缺失, 窗口规范默认是 ROW BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
7、OVER从句支持以下函数， 但是并不支持和窗口一起使用它们。
8、Ranking函数: Rank, NTile, DenseRank, CumeDist, PercentRank.
Lead 和 Lag 函数.
1.3 分析函数
ROW_NUMBER()： 从1开始，按照顺序，生成分组内记录的序列,比如，按照pv降序排列，生成分组内每天的pv名次,ROW_NUMBER()的应用场景非常多，再比如，获取分组内排序第一的记录;获取一个session中的第一条refer等。
RANK()： 生成数据项在分组中的排名，排名相等会在名次中留下空位（1,2,2,4）
DENSE_RANK() ：生成数据项在分组中的排名，排名相等会在名次中不会留下空位（1,2,2,3）
CUME_DIST（col）： 小于等于当前值的行数/分组内总行数。比如，统计小于等于当前薪水的人数，所占总人数的比例
PERCENT_RANK（col)： 分组内当前行的RANK值-1/分组内总行数-1
NTILE(n) ：用于将分组数据按照顺序切分成n片，返回当前切片值，如果切片不均匀，默认增加第一个切片的分布。
NTILE不支持ROWS BETWEEN，
比如 NTILE(2) OVER(PARTITION BY cookieid ORDER BY createtime ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)。
#over从句
over
----mapreduce优化
set mapreduce.job.reduces=partition_number;
set hive.map.aggr=true
----group by 优化
不可以和over从句配合，必须聚合
set hive.groupby.skewindata=true
------分区表
partition by + order by
distribute by + sort by
cluster by =>distribute by + sort by 基本不用
aggr/analyze/window over(partition by f1 order by f2/distribute by f1 sort by f2
rows/range between ? and ?) alias
over()
unbounded preceding : table first row
unbounded following : table last row 如果over加上order by 为current row
扫一扫
2148
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
