hivelength函数 hive20函数大全
hive sql 怎么把转化成字符串
hive sql 怎么把转化成字符串
hivelength函数 hive20函数大全
hivelength函数 hive20函数大全
hive字符串函数
1. 字符串长度函数：length
语法: length(string A)
返回值: int
说明：返回字符串A的长度
impala中date用什么类型
5 Function
指数据库内置的function，不讨论UDF。另外，作符都不比较了，区别不大。
5.1 数学函数
功能 Oracle Hive Impala
ABS ，有 有 有
SIN/SINH/ASIN/COS/COSH/ACOS/TAN/TANH/ATAN/ATAN2
三角函数
其中ATAN2接受两个参数（二维平面中的坐标）
没有SINH/COSH/TANH/ATAN2 同hive
BITAND 按位与，有
CEIL 天花板值，有 有，还有个别名CEILING 有，同hive
EXP e的多少次，有 有，还有个函数E()返回e 有，同hive
FLOOR 地板值，有 有 有
LN 以e为底的log，有 有 有
LOG 以某个double为底的log，有 有，还有两个特殊底的log：LOG2和LOG10 有，同hive
MOD Oracle的MOD的计算方式为MOD(n2,n1)=n2 - n1 FLOOR(n2/n1)，这与经典取模还不同，比如Oracle的MOD(-11,4)=-3，但经典取模等于1 在hive里取模用PMOD，返回值一定是个正数，比如PMOD(-11,4)=1。但这与经典取模还是不同，比如PMOD(-11,-4)=1，但经典取模等于-3 Impala的PMOD和hive的PMOD相同，另外Impala还有个FMOD和Oracle的MOD相同
POWER 求幂，有 有，还有个别名POW 有，同hive
REMAINDER 取余，REMAINDER(n2,n1)=n2 - n1 FLOOR(n2/n1)
ROUND 舍入，支持1个参数和2个参数两种版本
有另外还有一个BROUND，使用HALF_EVEN舍入模式，见手册
有，同Oracle
SIGN 符号函数，有 有 有
SQRT 开方，有 有 有
TRUNC 截取数值的小数点后多少位（如果是负值则往前推）
DEGREES/RADIANS 角度/弧度互转 同hive
POSITIVE/NEGATIVE 相当于在数值前加+/-号（因此没什么卵用） 同hive
PI 返回pi值 同hive
FACTORIAL 阶乘，1.2.0以后才有
CBRT 求立方根，1.2.0以后才有
SHIFTLEFT/SHIFTRIGHT/SHIFTRIGHTUNSIGNED 按位左移/右移/无符号右移
GREATEST/LEAST 返回一串值中的/小值，这串值的类型可以是任意，只要可比较大小（所以其实不是数学函数而是通用比较） 有，1.1.0以后 有
5.2 字符（串）函数
功能 Oracle Hive Impala
ASCII 输入必须char，返回该字符的ascii数值 输入String，返回该String个字符的ascii数值 同hive
BASE64 / UNBASE64 将二进制值转为base64的String（UN则是反向）
CONCAT
CONCAT(char1, char2)
char或char2均可为CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB, or NCLOB之一
concat(string|binary A, string|binary B)
可以有多个参数
另外提供CONCAT_WS用于指定特殊分隔符的连接
同hive（除了不支持binary类型），且也有CONCAT_WS
DECODE/ENCODE
string decode(binary bin, string charset)
binary encode(string src, string charset)
编码和解码用的，用于支持hive特有的binary类型
（其实oracle和impala也有DECODE，但作用完全不同，见条件函数中的DECODE）
FIND_IN_SET
find_in_set(string str, string strList)
strList是用’,’分割的一组string，该函数将寻找strList中个匹配的str
同hive
FORMAT_NUMBER 将数字格式化为string
GET_JSON_OBJECT 抽取JSON对象，不常用
IN_FILE
in_file(string str, string filename)
检测str是否为filename对应文件中的某行，不常用
INITCAP 将每个单词（以空白分隔）转换为首字母大写其余小写的形式 同oracle，1.1.0开始有 同oracle
INSTR
{ INSTR
| INSTRB
| INSTRC
| INSTR2
| INSTR4
}(string , substring [, ition [, occurrence ] ])
搜索子串，不同数据类型调用名不同，多可带四个参数，其中第三个是开始位置，第四个是出现的第几次
instr(string str, string substr)
只接受两个参数
同hive
LENGTH
{ LENGTH
| LENGTHB
| LENGTHC
| LENGTH2
| LENGTH4
}(char)
串长，不同数据类型调用名不同
有（仅LENGTH） 同hive
LEVENSHTEIN 返回两个串的Lnshtein距离（编辑距离）1.2.0后才有
LOCATE 特殊情况的INSTR，可以指定匹配的开始位置。Oracle因为本来INSTR就支持所以并不需要该函数 同hive
LOWER 转小写 有，且有一个别名叫LCASE 同hive
LPAD / RPAD
LPAD(expr1, n [, expr2 ])
在expr1之前用expr2填充n个字符，如expr2省略默认用n个单空格填充。RPAD类似只是在右边
有，但expr2不能省略 同hive
LTRIM / RTRIM
LTRIM(char [, set ])
去掉char左侧包含在set中的字符，如省略set，则只去除空格符。RTRIM类似只是在右边
有，但没有set参数，即只能去除空格符 同hive
Hive字符串转struct
hive字符串函数
1. 字符串长度函数：length 语法: length(string A) 返回值: int 说明：返回字符串A的长度 举例： hive> select length('abcedfg') from lxw_dual; 7
2. 字符串反转函数：rrse 语法: rrse(string A) 返回值: string
Hive实战之Youtube数据集
本次实战的数据来自于"YouTube视频统计与社交网络"的数据集，是西蒙弗雷泽大学计算机学院在2008年所爬取的数据
数据集地址
数据之间采用"t"作为分隔符
具体数据如下：
数据量大小为1G，条数为500万+
使用环境为
hive-1.1.0-cdh5.4.5
hadoop-2.6.0-cdh5.4.5
演示形式为使用hive shell
我们一起来看看数据
主要的问题在于category和relatedIDs处理，由于Hive是支持array格式的，所以我们想到的是使用array来存储category和relatedIDs，但是我们发现category的分割符是"&"而realatedIDs的分隔符是"t"，我们在创建表格的时候能够指定array的分隔符，但是只能指定一个，所以再将数据导入到Hive表格之前我们需要对数据进行一定转换和清洗
并且数据中肯定会存在一些不完整数据和一些奇怪的格式，所以数据的清洗是必要的，我在这里所使用的数据清洗方式是使用Spark进行清洗，也可以使用自定义UDF函数来进行清洗
数据清洗注意点
1）我们可以看到每行数据以"t"作为分隔符，每行有十列数据，一列关联ID可以为空，那么我们对数据进行split之后数组的大小要大于8
2）数据中存在 "uNiKXDA8eyQ KRQE 1035 News & Politics 107" 这样格式的数据，所以在处理category时需要注意 News & Politics中间的 &
处理后的数据如下：
下面的实战都是基于数据清洗后的数据进行的
1）1的创建，文件格式为textfile
create table 1(videoId string, uploader string, age int, category array
row format delimited
fields terminated by "t"
collection s terminated by "&"
stored as textfile;
2）2的创建，文件格式为orc
create table 2(videoId string, uploader string, age int, category array
row format delimited
fields terminated by "t"
collection s terminated by "&"
stored as orc;
3）3的创建，文件格式为orc，进行桶分区
create table 3(videoId string, uploader string, age int, category array
clustered by (uploader) into 8 buckets
row format delimited
fields terminated by "t"
collection s terminated by "&"
stored as orc;
数据导入：
1）load data inpath "path" into table 1;
2）由于无法将textfile格式的数据导入到orc格式的表格，所以数据需要从1导入到2和3：
insert into table 2 select from 1;
insert into table 3 select from 1;
1）user_tmp的创建，文件格式textfile，24buckets
create table user_tmp(uploader string,videos int,friends int)
clustered by (uploader) into 24 buckets
row format delimited
fields terminated by "t"
stored as textfile;
2）user的创建，文件格式orc，24buckets
create table user(uploader string,videos int,friends int)
clustered by (uploader) into 24 buckets
row format delimited
fields terminated by "t"
stored as orc;
user表的数据导入也是同理
数据导入：
1）load data inpath "path" into table user_tmp;
2）由于无法将textfile格式的数据导入到orc格式的表格，所以数据需要从user_tmp导入到user：
insert into table user select from user_tmp;
1）统计出观看数多的10个视频
2）统计出视频类别热度的前10个类型
3）统计出视频观看数的50个视频的所属类别
4）统计出观看数多的前N个视频所关联的视频的所属类别排行
5）筛选出每个类别中热度的前10个视频
6）筛选出每个类别中评分的前10个视频
7）找出用户中上传视频多的10个用户的所有视频
8）筛选出每个类别中观看数Top10
select from 3 order by views desc limit 10;
结果如下：
select tagId, count(a.videoid) as sum from (select videoid,tagId from 3 lateral view explode(category) catetory as tagId) a group by a.tagId order by sum desc limit 10;
结果：
select tagId, count(a.videoid) as sum from (select videoid,tagId from (select from 3 order by views desc limit 20) e lateral view explode(category) catetory as tagId) a group by a.tagId order by sum desc;
结果：
思路：
结果：
思路：
结果如下：
select from _category where categoryId="Music" order by ratings desc limit 10;
结果如下：
思路：
结果如下：
【数仓】Hive数仓的explode()炸裂函数详解
我们知道数仓不满足范式，也就是说数仓中的字段是可再分的，不满足原子性，即DDL可以定义一个字段类型为数组。因此才有了explode()函数，用于给这个字段展开降维。
explode()可以把指定的数组字段拆分降维展开为多行。类似于UDTF函数，作用于单/多个数据行，并且产生多个数据行，以一个表作为输出。
但这样无法同时查询多列，即select name,explode(array) from table会报错。
原因时当使用UDTF函数的时候,Hive只允许对拆分字段进行访问。
要同时查询多列只能：
lateral view explode() 是把要拆分的字段array拆分后以多行存储在t表中的字段中（当然也可以另起一个临时表）。
如果一行数据有两列需要炸裂展开，可以这样写：
这样得到的行数是 n length(array1) length(array2)
声明：本站所有文章资源内容，如无特殊说明或标注，均为采集网络资源。如若本站内容侵犯了原著者的合法权益，可联系 836084111@qq.com 删除。