SQL学习笔记

SELECT

USER,

programid 

FROM(

　　SELECT

     USER,

     programid, 

     row_number() over(partition BY USER ORDER BY sum(playtime)) r 

   FROM

      test 

   GROUP BY 

       USER,

       programid

) tmp 

WHERE r=1

转载：https://blog.csdn.net/IAmListening/article/details/90514263

hive 求本周上周 时间

当天是星期几  pmod(datediff(‘2018-07-31‘, ‘2012-01-01‘), 7)
本周一    date_sub(‘2018-07-31‘,pmod(datediff(‘2018-07-31‘, ‘2012-01-01‘), 7)-1)
上周一   date_sub(‘2018-07-31‘,pmod(datediff(‘2018-07-31‘, ‘2012-01-01‘), 7)-1+7) 

修改列名和列数据类型：alter table aa change col2 name string ;
修改位置放置第一位：alter table aa change col2 name string first;
修改位置指定某一列后面：alter table aa change col1 dept string after name;

添加列(慎用，添加到最后一列) ：alter table aa add columns(col3 string);

修改列名a到a1,a1是string 类型：ALTER TABLE iteblog CHANGE a a1 INT;

修改列名a为a1,a1是string类型且a1字段指定b字段后面：ALTER TABLE iteblog CHANGE a a1 STRING AFTER b;

修改列名b为b1,b1是int类型且b1字段在表的第一列：ALTER TABLE iteblog CHANGE b b1 INT FIRST;

表重命名：alter table aa rename to aa_test;

添加分区：alter table aa add partition(statdate=20170404);

        alter table bb add partition(statdate=20170404) location ‘/user/gaofei.lu/20170404.txt‘

显示分区：show partitions aa;

修改分区：alter table aa partition(statdate=20170404) rename to partition(statdate=20170405);

               alter table bb partition(statdate=20170404) set location ‘/user/gaofei.lu/aa.txt‘;

删除分区：alter table aa drop if exists partition(statdate=20170404);

设置hive  on spark :  set hive.execution.engine=spark

Json 字符串 解析：

SELECT get_json_object(‘{"website":"www.iteblog.com","name":"过往记忆"}‘, ‘$.website‘);  --一次只能拿到一个字段

www.iteblog.com

SELECT json_tuple(‘{"website":"www.iteblog.com","name":"过往记忆"}‘, ‘website‘, ‘name‘); --一次只能拿到多个字段

www.iteblog.com 过往记忆

使用 Hive 自带的函数解析 Json 数组

explode() 接收一个 array 或 map 类型的数据作为输入，然后将 array 或 map 里面的元素按照每行的形式输出。其可以配合 LATERAL VIEW 一起使用。

select explode(array(‘A‘,‘B‘,‘C‘));
A
B
C

select explode(map(‘A‘,10,‘B‘,20,‘C‘,30));
A 10
B 20
C 30

SELECTexplode(split(regexp_replace(regexp_replace(‘[{"website":"www.iteblog.com","name":"过往记忆"},{"website":"carbondata.iteblog.com","name":"carbondata 中文文档"}]‘, ‘{‘,‘\\}\\;\\{‘),‘\\[|\\]‘,‘‘),‘\\;‘));

{"website":"www.iteblog.com","name":"过往记忆"}
{"website":"carbondata.iteblog.com","name":"carbondata 中文文档"}

explode 函数只能接收数组或 map 类型的数据，而 split 函数生成的结果就是数组；
第一个 regexp_replace 的作用是将 Json 数组元素之间的逗号换成分号，所以使用完这个函数之后，[{"website":"www.iteblog.com","name":"过往记忆"},{"website":"carbondata.iteblog.com","name":"carbondata 中文文档"}] 会变成 [{"website":"www.iteblog.com","name":"过往记忆"};{"website":"carbondata.iteblog.com","name":"carbondata 中文文档"}]
第二个 regexp_replace 的作用是将 Json 数组两边的中括号去掉，所以使用完这个函数之后，[{"website":"www.iteblog.com","name":"过往记忆"},{"website":"carbondata.iteblog.com","name":"carbondata 中文文档"}] 会变成 {"website":"www.iteblog.com","name":"过往记忆"},{"website":"carbondata.iteblog.com","name":"carbondata 中文文档"}

select json_tuple(json, ‘website‘, ‘name‘) from (SELECT explode(split(regexp_replace(regexp_replace(‘[{"website":"www.iteblog.com","name":"过往记忆"},{"website":"carbondateblog.com","name":"carbondata 中文文档"}]‘, ‘\\}\\,\\{‘,‘\\}\\;\\{‘),‘\\[|\\]‘,‘‘),‘\\;‘)) as json) iteblog;

www.iteblog.com 过往记忆