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 过往记忆

 

关系型运算符优先级高到低为：NOT>AND>OR
如果where 后面有OR条件的话，则OR自动会把左右的查询条件分开

 

hive 中‘不等于’不管是用！ 或者<>符号实现，都会将空值即null过滤掉，此时要用

where （white_level<>'3' or white_level is null）
或者 where (white_level!='3' or white_level is null ) 来保留null 的情况。

 

Map join 怎么实现的？

 

hive中order by, sort by, distribute by, cluster by的用法和区别

1、order by 

     hive中的order by 和传统sql中的order by 一样，对数据做全局排序，加上排序，会新启动一个job进行排序，会把所有数据放到同一个reduce中进行处理，不管数据多少，不管文件多少，都启用一个reduce进行处理。如果指定了hive.mapred.mode=strict（默认值是nonstrict）,这时就必须指定limit来限制输出条数，原因是：所有的数据都会在同一个reducer端进行，数据量大的情况下可能不能出结果，那么在这样的严格模式下，必须指定输出的条数。

select id,sum(money) from t group by id 这条语句只用一个job就ok，

select id,sum(money) from t group by id order by id 如果加上order by 就会多一个job进行排序操作。

2、sort by 

sort  by 是局部排序，会在每个reduce端做排序，每个reduce端是排序的，也就是每个reduce出来的数据是有序的，但是全部不一定有序，除非一个reduce，一般情况下可以先进行局部排序完成后，再进行全局排序，会提高不少效率。

select id,sum(money) from t group by id sort by id;  这条语句也不会增加job，它在reduce端直接进行排序。

3、distribute by

    distribute by 是控制map端在reduce上是如何区分的，distribute by  会把指定的值发到同一个reduce中，比如 用上面数据distribute by id 它就会把id相同的值放到一个reduce中执行，不是一个值一个reduce，而是相同的值进入到一个reduce，例如用上面数据可以进入到2个reduce，一般情况下可以sort by 结合使用，先进行分组reduce，再进行排序。

如：select id,money,name from t distribute by id sort by id 

4、cluster  by

    这个其实就是distribute by 和sort by 结合使用的结果。

   如：select id,money,name from t cluster by id;  

   这条语句其实和select id,money,name from t distribute by id sort by id  这条语句的结果是一样的

Hive内部表和外部表的区别

未被external修饰的是内部表（managed table），被external修饰的为外部表（external table）；

区别：

内部表数据由Hive自身管理，外部表数据由HDFS管理；

内部表数据存储的位置是hive.metastore.warehouse.dir（默认：/user/hive/warehouse），外部表数据的存储位置由自己制定；

删除内部表会直接删除元数据（metadata）及存储数据；删除外部表仅仅会删除元数据，HDFS上的文件并不会被删除；

行式存储vs列式存储

行式数据库存储在hdfs上式按行进行存储的，一个block存储一或多行数据。而列式数据库在hdfs上则是按照列进行存储，一个block可能有一列或多列数据。

 

Hive静态分区动态分区

分区的概念

Hive的分区方式：由于Hive实际是存储在HDFS上的抽象，Hive的一个分区名对应HDFS上的一个目录名，子分区名就是子目录名，并不是一个实际字段。

分区的好处

产生背景：如果一个表中数据很多，我们查询时就很慢，耗费大量时间，如果要查询其中部分数据该怎么办呢，这是我们引入分区的概念。

Partition:分区，每张表中可以加入一个分区或者多个，方便查询，提高效率；并且HDFS上会有对应的分区目录：

语法：

Hive分区是在创建表的时候用Partitioned by 关键字定义的，但要注意，Partitioned by子句中定义的列是表中正式的列，

但是Hive下的数据文件中并不包含这些列，因为它们是目录名，真正的数据在分区目录下。

静态分区和 动态分区的区别

创建表的语法都一样

静态分区：加载数据的时候要指定分区的值（key=value），比较麻烦的是每次插入数据都要指定分区的值，创建多个分区多分区一样，以逗号分隔。

动态分区：

如果用上述的静态分区，插入的时候必须首先要知道有什么分区类型，而且每个分区写一个load data，太烦人。使用动态分区可解决以上问题，其可以根据查询得到的数据动态分配到分区里。其实动态分区与静态分区区别就是不指定分区目录，由系统自己选择。

首先，启动动态分区功能

hive> set hive.exec.dynamic.partition=true;

采用动态方式加载数据到目标表 
加载之前先设置一下下面的参数

hive (default)> set hive.exec.dynamic.partition.mode=nonstrict
1
开始加载

insert into table emp_dynamic_partition partition(deptno)
select empno , ename , job , mgr , hiredate , sal , comm, deptno from emp;

加载数据方式并没有指定具体的分区，只是指出了分区字段。

在select最后一个字段必须跟你的分区字段，这样就会自行根据deptno的value来分区。

删除分区：

ALTER TABLE my_partition_test_table DROP IF EXISTS PARTITION (day='2018-08-08');