搜文章
推荐 原创 视频 Java开发 iOS开发 前端开发 JavaScript开发 Android开发 PHP开发 数据库 开发工具 Python开发 Kotlin开发 Ruby开发 .NET开发 服务器运维 开放平台 架构师 大数据 云计算 人工智能 开发语言 其它开发

Hive环境下SQL使用指南

利用Python和R做好大数据风控 2018-03-01

Hive简介


Hive是在分布式文件系统HDFS之上,用MapReduce作为引擎的一个SQL查询系统。(HDFS和MapReduce是大数据处理平台Hadoop的核心)


Hue是一个Web查询界面,可以在其中运行HiveQL(SQL)并返回查询结果。


Hive 语法


SQL 通用性


关键字 select, from, where, group by, having, order by, join, on, union all, insert into, truncate, drop table , exists ... 等都用法与其他SQL都相同


Hive 特殊性

不支持 union,只支持 union all;如果需要排重,则在外嵌套一层 distinct;union all 等价于 insert into table (demo)


不支持 delete, update;实现方式where, left join 等条件查询出最终需要的结果,通过 insert overwrite table (覆盖原表) 写入数据


不支持所有 join 操作时两个表字段之间的不等关系;需先筛选出匹配子集再将结果与原表关联


不支持中文字段名


Hive vs SQL Server

Hive区分字符的大小写,而SQL Sever不区分,故而在 join 的on 条件上两个字符的匹配需要同时转为大写或小写,在 group by 、 partition by 、distinct 及 where 条件上也要注意


Hive不支持类似与 SQL Server 会话中临时表机制 into #temp,直接在 tmp_xpt 中建表即可,多段语句之间用英文分号 “;”分割,SQL Server 可以不用分号会自动识别(不好的习惯)


数据类型与转换

Data Types (Hive支持的数据类型)


字符型:一律使用 string ,没有长度限制,Hive的机制不会造成空间浪费。需要注意的是:用 char(n), varchar(n) 可固定字符长度,但超出n的字符会自动截断,不会报错!


日期型:多数情况下都是使用 string , Hive 能自动识别 ‘2018-01-15’ 是一个日期,与 date/timestamp 都通用,可以用来比较计算


类型转换:cast(字段 as 类型) ;部分函数对输入的类型有限制要求,必要是做 cast 转换即可。


Hive 函数

Hive Operators and User-Defined Functions (UDFs) (系统自带函数)

日期和时间函数

日期和时间函数

 

-- 获取系统当前日期和时间

select current_date, current_date();           -- 2018-01-12, 2018-01-12 -- 返回 date 类型

select current_timestamp, current_timestamp(); -- 2018-01-12 16:53:58.581,  2018-01-12 16:53:58.581  -- 返回 timestamp 类型


-- 不推荐使用如下方式,除非指定其他日期和时间的格式

select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss') -- 与 current_timestamp() 等价


-- 去掉时分秒、增减日期

select to_date('2010-08-19 14:38:24'); -- 结果是 '2010-08-19' ,去掉时分秒,只取年月日  -- 注意,to_date() 之后返回的是 string 类型

select date_sub('2010-08-19 14:38:24', 1);   -- 结果是 '2010-08-18'

select date_add('2010-08-19 14:38:24', 1);   -- 结果是 '2010-08-20'


-- 返回年季月周日时分秒的整数


select year('2010-08-19 14:38:24'), quarter('2010-08-19 14:38:24'), month('2010-08-19 14:38:24');  -- 年份, 季度, 月份

select weekofyear('2010-08-19 14:38:24'); -- 年周

select day('2010-08-19 14:38:24'), dayofmonth('2010-08-19 14:38:24');  -- 当月中的第几天

select dayofyear('2010-08-19 14:38:24'); -- 当年中的第几天

select hour('2010-08-19 14:38:24'), minute('2010-08-19 14:38:24'), second('2010-08-19 14:38:24'); -- 小时,分钟,秒

-- 月初第一天,月末最后一天


-- 月初第一天:按日期格式取月份,按月分组的最佳选择;通过字符串处理提取年月字符 '2010-08' 或者拼接长 '201008' 都过于麻烦,除非有必要,否则不推荐


select trunc('2010-08-19 14:38:24', 'MM');   -- 结果是 '2010-08-01' 月初第一天,'MM' 要大写, 支持这三种写法 MONTH/MON/MM, YEAR/YYYY/YY.

select last_day('2010-08-19 14:38:24');      -- 结果是 '2010-08-31' 月末最后一天


-- trunc 是截断,除了 月,也支持 年


select trunc('2010-08-19 14:38:24', 'YY');   -- 结果是 '2010-01-01' 当前年的第一个日期,'YY' 要大写, 支持这三种写法 YEAR/YYYY/YY.


-- 下一个星期几


select next_day('2018-01-15', 'MO'); -- 2018-01-22  第二个参数是星期参数,支持 Mo, tue, FRIDAY 这三种

select next_day('2018-01-15', 'TU'); -- 2018-01-16


--- 划重点:日期与时间差值 default.date_diff() 是大数据平台开发的自定义函数,用法如下


select default.date_diff('yy', '2010-08-19 14:38:24', '2019-08-19 14:38:24');

select default.date_diff('mm', '2008-02-29 14:38:24', '2008-01-28 14:38:24');

select default.date_diff('dd', '2008-02-29 14:38:24', '2008-03-01 14:38:24');

select default.date_diff('mi', '2019-08-19 12:05:24', '2019-08-19 12:10:01')


-- yy mm dd mi 是小写,尽量不要用系统自带函数 datediff('2009-03-01', '2009-02-27') -- 有坑


-- 添加月份


default.add_months_new('2017-02-28', 1) ; -- 结果是 '2017-03-28' ; 尽量不要用系统自带函数 add_months('2017-02-28', 1)的结果是 '2017-03-31' -- 有坑

default.add_months_new('2017-02-28', -1); -- 结果是 '2017-01-28'


字符函数

-- 字符串长度


select length('1b_'), length('买单侠'), length(''); -- 3, 3, 0


-- 字符串连接


select concat('foo', 'bar'), concat('foo', 'bar', ', ', 'more ', 'string'), oncat('foo', 1);  -- 'foobar', 'foobar, more string', 'foo1'


-- 带分隔符字符串连接


select concat_ws('-', 'foo', 'bar'), concat_ws('-', 'foo', 'bar', ', ', 'more ', 'string'), concat_ws('0','foo', '1'); -- 'foo-bar', 'foo-bar-, -more -string', 'foo01'


-- 数字格式化


select format_number(3.1415926, 2), format_number(3.1415926, 0), format_number(1234567.89, 0); -- '3.14', '3', '1,234,568' -- 数字格式化函数,返回结果是字符串


-- 字符位置


select instr('foobar', 'foo'), instr('foobar', 'bar'),  instr('foobar', 'xyz'); 1, 4, 0 -- 查找字符所在位置;找不到为0

select locate('foo', 'foobar'), locate('bar', 'foobar'),  locate('xyz', 'foobar'); 1, 4, 0 -- locate 与 instr 等价,只是参数位置想法


-- 字符大小写转化 -- 在 hive 中,字符是区分大小写的,故而在表关联和分组时如果不想区分大小写(大小写等价),则应将字段转为统一的大小或者小写


select lower('fOoBaR'), lcase('fOoBaR'); --  'foobar',  'foobar' -- lower() 与 lcase() 等价

select upper('fOoBaR'), ucase('fOoBaR'); --  'FOOBAR',  'FOOBAR' -- upper() 与 ucase() 等价


-- 去掉多余的空格


select trim(' fOoBaR '), ltrim(' fOoBaR '), rtrim(' fOoBaR '); 'fOoBaR', 'fOoBaR ', 'fOoBaR ' -- l h和 r 是左和右的区别


-- 截取字符串:从某个位置开始截取n个字符串,如果不知道截取长度,则从某个位置开始到最后


select substr('512501197203035172',7,8), substring('512501197203035172',7,8), substr('512501197203035172',-2, 1); -- '19720303', '19720303', '7'

select substr('512501197203035172',7), substring('512501197203035172',7), substr('512501197203035172',-2); -- '197203035172', '197203035172', '72'


-- 反转字符串


select reverse('2111F1F11FFFF'); -- FFFF11F1F1112


-- 按目标长度填充字符


select rpad('##############2111F1F11FFFF', 60, '>'), lpad('abcde', 10, '>'); -- '##############2111F1F11FFFF>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>', '>>>>>abcde'


-- 生成重复的字符串


select repeat('>', 5), repeat('abc',2); --'>>>>>', 'abcabc'


-- 分割字符串;返回的是数组


select split('abtcdtef','t'); -- ["ab","cd","ef"] 数组


正则表达式字符函数

-- regexp_extract 抽取没有替换常用,中间换被截断


select regexp_extract('测试36548719971209201X名字', '[0-9xX]+', 0) -- 36548719971209201X

select regexp_extract('测试3654871997字符1209201X名字', '[0-9xX]+', 0) -- 3654871997


-- 提取数字和字符X,用来提取身份证号 ,等价于替换非数字和字符xX


select regexp_replace('测试3654871997字符1209201X名字', '[^0-9xX]', '')  -- 36548719971209201X


-- 提取中文字符 = 替换非中文字符后后只保留中文字符;


-- [u4e00-u9fa5] 是中文字符, \\ 是Hive中正则表达式的转义符号


select regexp_replace('测试3654871997字符1209201X名字', '[^\\u4e00-\\u9fa5]', '') -- 测试字符名字


-- 注意:replace() 函数在当前版本中不支持,都使用 regexp_replace

-- 另外:rlike 是 like 的正则表达式版本

-- hive


select /*+ mapjoin(a12)*/

distinct upper(a11.userid) as userId

, a11.name as ContactName

, a11.mobile

from (select distinct upper(userid) as userid from temp.lxh_test_1212) a12

join prod_appdb.prod_appdb_address_book_res a11 on upper(a11.userid) = upper(a12.userid)

and a11.name <> ''

and a11.name is not null

and length(regexp_extract(a11.name, '\\d+', 0)) <> length(a11.name) -- 排除联系人名称为纯数字

and (

   a11.name rlike ('亲|媳妇|神|么么哒|贱人|猪|欧巴|宝|爱妃|妖|10086|Honey|我|爸|爹|父|爷|老头|豆|佬|万岁|妈|夫人|母|大人|妈|阿吉|婆|先生|女士|领导|阿玛|娘|麻|千岁|家|达令|魔头|儿|女|丈|岳|姥|公|奶|伯|姑|姨|舅|婶|叔|嫂|姐|哥|兄|弟|妹|友|同|老|阿|小')

or substring(a11.name, 1, 1) = substring(a11.name, 2, 1) -- 名字格式为'AA'型(叠字)

   );

空值处理

首先要注意的是:与 NULL 比较的结果永远是 NULL ,在 where 条件中如果要包含空值,需要 column is null 的关键字。

-- 在 where 条件中常犯的一个错误就是 NULL 被排除在外缺没有意识到

select a.*

from edw.application a where a.Status >= 100 and a.InstalmentChannel <= 1

and (a.InstalmentPurposeId <> '1CD53D56-42E8-E411-87D5-80DB2F14945F' or a.InstalmentPurposeId is null) -- 剔除中移动 and a.date >= '2017-04-01' and a.date  < '2017-04-02'

空值处理函数

-- isnull() 在 SQL Sever 中是用来将空值转化为别的值,在 Hive中 isnull() 是一个逻辑判断表达式,用来判断是否为空值

select isnull(NULL), isnull(null), isnull(''), isnotnull(''); -- true, true, false, true


-- nvl() 是空值转换函数,等价与 SQL Server 中的 isnull()

select nvl(NULL, ''), nvl(null, 'abc'), nvl(null, current_date), nvl('x', 'y'); -- '0', 'abc', '2018-01-15',  'x'


-- where nvl(NULL, '') <> ''  先将空值转为空格,再来对空格结果做条件判断,就可实现控制和空格使用相同的处理方式


-- 在多个值/字段中按顺序返回第一个不为NULL的值,如果全部为NULL,则返回NULL

select coalesce(NULL, NULL, current_date), coalesce(NULL, current_date, NULL), coalesce(NULL, NULL, NULL) -- '2018-01-15', '2018-01-15', NULL


-- 实例:账单结清日期,优先取实还日期,期次取免除日期,最后取最后一次修改日期

select a11.appid, a11.numinstalment, min(to_date(a11.datedue)) as receivable_date -- 本金的还款日作为约定还款日, case when sum(a11.outstanding) = 0 then max(to_date(COALESCE(a11.datepay, a11.datewaive, a11.lastmodified))) else null end as received_date, max(to_date(a11.lastmodified)) as lastmodified_date -- 每期最后更新日期

from eods.ods_instalment a11  -- 还款计划表 where a11.instalmenttype = 10 -- 本金记录 group by a11.appid , a11.numinstalment

其他

select isnull(NULL), isnull('dd'); isnull() 在 Hive 中判断是否为 NULL,而 SQL Server 中是 控制转化

select nvl(null,1); 等价与 SQL Serve中的 isnull();

-- if(x>y, z, t) 单个条件判断可以if ,等价与 case when x>y then z else t end

窗口分析函数

官方参考文档  WindowingAndAnalytics(窗口函数/分析函数)

分析函数

RANK:生成数据项在分组中的排名,排名相等会在名次中留下空位

ROW_NUMBER :从1开始,按照顺序,生成分组内记录的序列

DENSE_RANK :生成数据项在分组中的排名,排名相等会在名次中不会留下空位

CUME_DIST:小于等于当前值的行数/分组内总行数

PERCENT_RANK :分组内当前行的RANK值-1/分组内总行数-1

NTILE :用于将分组数据按照顺序切分成n片,返回当前切片值

-- row_number()

select t.*from (select a11.name, a11.relationship, a11.lastmodified, a11.contactpersontype, a11.appid, a11.mobilecontactid, a11.lastmodifiedby

 , row_number() over(partition by upper(a11.appid) order by a11.lastmodified desc) as rn from ods.ods_catfish_contactpersonobjects a11 where a11.contactpersontype = 2) t where rn = 1;


宽口函数

LEAD:向前偏移,默认偏移一行

LAG:向后偏移,默认偏移一行

FIRST_VALUE:窗口内第一个值

LAST_VALUE:窗口内最后一个值

lead() 是向前偏移, lag() 是向后偏移,默认偏移1行,超出窗口之外的值默认为NULL。


第一个参数偏移字段,必填;第二和第三个参数可选的,第二参数是 offset 偏移量,默认为1,可以改为任意正整数;第二个可选参数是超出窗口之外的默认值,比如第一行向前偏移1和最后一行向后偏移1就是超出窗口之外,可以更改为其他值,比如0。


窗口及排序是由 OVER (PARTITION BY + ORDER BY) 确定的。


SELECT a, LEAD(a) OVER (PARTITION BY b ORDER BY C) FROM T;       -- 向前偏移1

SELECT a, LAG(a, 3, 0) OVER (PARTITION BY b ORDER BY C) FROM T;  -- 向后偏移3,超出窗口之外的值取0


first_value() 是取第一个值,last_value() 是取最后一个值。第二个参数是 逻辑值 默认为 false,如果设定为 true ,则取第一个/最后一个不为NULL的值。


SELECT a, first_value(a) OVER (PARTITION BY b ORDER BY C) FROM T, ;       -- 取窗口中的第一个值(整个窗口都取第一个值)

SELECT a, last_value(a)  OVER (PARTITION BY b ORDER BY C) FROM T, ;        -- 取窗口中的最后一个值(整个窗口都取最后一个值)

SELECT a, first_value(a, true) OVER (PARTITION BY b ORDER BY C) FROM T, ;       -- 取窗口中的第一个非空的值(整个窗口都取第一个非空的值)


SELECT a, last_value(a, true)  OVER (PARTITION BY b ORDER BY C) FROM T, ;        -- 取窗口中的最后一个非空的值(整个窗口都取最后一个非空的值)


窗口统计分析函数

COUNT

SUM

MIN

MAX

AVG

OVER() 中只有 PARTITION BY 分组条件,则窗口同一组内值返回值相同,常用于占比值的分母

SELECT a, SUM(b) OVER (PARTITION BY c) FROM T;

OVER() 中有 PARTITION BY 分组条件,也有 ORDER BY 排序条件,则窗口内返回相同累计求和(比较)后的值;常用于累计求和

SELECT a, COUNT(b) OVER (PARTITION BY c, d) FROM T;

并且支持如下指定窗口的用法,比如从窗口第一行到当前行

(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

用户自定义函数

UDFs

函数列表如下,用法为 default.函数 

default.date_diff(类型, 开始日期, 结束日期):类型为 'yy'/'mm'/'dd'/'mi',均为小写,分别代码 年份/月份/天数/分钟 差值 ;用来替代系统函数 datediff(结束日期,开始日期)

default.add_months_new(日期,月数):用来替代系统函数 add_months

default.filter_cst_date(字符):将 mongon 中的时间 'Thu Mar 31 17:42:41 CST 2016'转为 '2016-03-31 17:42:41' 格式

default.date_equals(日期1,日期2):比较两个日期值是否相等,用于不带时分秒的日期,和有时分秒的日期之间的比较

default.concat_by_column(待拼接字符,排序条件):分组按顺序拼接字符串,字符串的分组汇总函数

其他

通过SQL插入数据

CREATE TABLE tmp_xpt.lxh_test_students (name STRING, age INT, gpa DECIMAL(3, 2));


INSERT INTO TABLE tmp_xpt.lxh_test_students VALUES ('fred flintstone', 35, 1.28), ('barney rubble', 32, 2.32); -- 核心问题拼接为一个SQL语句,而不要拆分为一行一句


版权声明:本站内容全部来自于腾讯微信公众号,属第三方自助推荐收录。《Hive环境下SQL使用指南》的版权归原作者「利用Python和R做好大数据风控」所有,文章言论观点不代表Lambda在线的观点, Lambda在线不承担任何法律责任。如需删除可联系QQ:516101458

文章来源: 阅读原文

相关阅读

关注利用Python和R做好大数据风控微信公众号

利用Python和R做好大数据风控微信公众号:gh_389826396b69

利用Python和R做好大数据风控

手机扫描上方二维码即可关注利用Python和R做好大数据风控微信公众号

利用Python和R做好大数据风控最新文章

精品公众号随机推荐