Hive sql

DML-Load加载数据：

load data [local] inpath 'filepath' [overwrite] into table tablename

[partition(partcol1=val1,partcol2=val2...)]

有local就在本地文件系统

没有local就在hdfs分布式文件系统

如果有overwrite关键字，则目标表（或者分区）中的已经存在的数据就会被删除，然后再将filepath指向的文件/目录中的内容添加到表/分区表中。

将数据文件插入到分区表中，但是不指定分区的情况：

在这种load不指定分区的情况下，会将load转变为insert as select。

DML-insert使用方式：

insert+select表示：将后面查询返回的结果作为内容插入到指定表当中，注意overwrite将覆盖已有数据。

1.需要保证查询结果列和数目和需要插入数据表格的列数据一致。

2.如果查询出来的数据类型和插入表格对应的列数据类型不一致，将会进行转换，但是不能保证转换一定成功，转换失败的数据将会为null。

insert overwrite table 和 insert into table。

动态分区插入：****（先设置成非严格模式）

set hive.exec.dynamic.partition = true;

set hive.exec.dynamic.partition.mode = nonstrict;

​​​​​​​

select语法：

select [ALL|DISTINCT] select_expr,select_expr,....

from table

[where where_condition]

[group by col_list]

[order by col_list]

[

cluster by col_list | [distribute by col_list][sort by col_list]

**] **

[limit [offset,] rows];

1. select_expr的4种可能性：

查询所有字段或者指定字段

select * from t_usa_covid19_p;

select country,cases,deaths from t_usa_covid19_p;

查询匹配正则表达式的所有字段

SET hive.support.quoted.identifiers = none;

select '^c.*' from t_usa_covid19_p;;

查询当前数据库(省去from关键字)

select current_database();

查询使用函数

select count(country) from t_usa_covid19_p;

2.ALL和DISTINCT：

在select中默认是all，代表我们返回所有匹配的行。

如果是distinct的话，就代表是去重。

3.where：

在where中不能使用聚合函数，因为使用聚合函数的前提是已经由where进行筛选之后，获得了确定的结果集之后的，才能再使用聚合函数。

select * from t_usa_covid19_p where 1>2;

这个where的结果是false，如果是false的话，那么前面的select就不会执行，所以这个语句的查询结果就是空集。

select * from t_usa_covid19_p where 1=1;

这个where的结果是true，所以前面的select就会执行。

select * from t_usa_covid19_p where length(state)>10;

这个就会找到所有state的长度大于10的结果。

select * from A where A.a IN (select foo from B);

where子句支持子查询。

4.分区查询：

where的条件可以用来做分区查询，提高查询效率。

5.group by概念：

group by语句用于结合聚合函数，根据一个或者多个列对结果集进行分组。

但是出现在group by中select_expr的字段：要么是group by分组的字段，要么是被聚合函数应用的字段。

6.having：

在sql中增加having子句的原因是，where无法和聚合函数一起使用，那么having负责的是在group by分组之后的筛选结果集的任务。

也就是先执行where，然后再分组，然后再用having进行二次筛选。

where是在分组前过滤，having在分组后过滤。

7.limit：

limit 5，代表从偏移量0开始返回5行数据。

limit 2，3 代表从偏移量2（也就是第3行）开始返回3行数据。

8.执行顺序：

在查询过程中执行顺序：

from>where>group by>having>order>select;

9.order by:

Hive中的order by语法类似于标准sql语言中的order by语法，会对输出的结果进行全局排序。因此当底层使用MapReduce引擎执行的时候，只会有一个reducetask执行，如果输出的行数太大了，就会导致需要很长的时间来完成。

asc升序，desc降序。

10.cluster by：

根据指定字段将数据分组，然后再在组内进行排序，而且只能是正序排序。

说白了就是根据同一个字段，分且排序。

两个例子：

select * from student cluster by num;

这个就是分为一组去排序。因为默认是1。

先设置reducetask数量为2。

set mapreduce.job.reduces = 2;

select * from student cluster by num;

但是其实这也是他的局限性，如果我分组和排序的字段不是同一个字段，那么cluster by就无法处理了，而且局限性还有一个，那就是只能是升序排序。

11.distribute by+order by：

distribute管的是分组，order by管的是排序。

那么其实distribute by+order by就是相当于cluster by的。

总结一下，其实就是order by只能是全局排序，也就是reducetask只能有1个，不管你自己设置的mapreduce.job.reduces = 多少，只能是1。那么cluster by就是多了一个分组的功能，但是分组和排序的字段只能是1个，而且只能是升序排序。distribute by可以管分组，但是不管排序；sort by管分组后的排序，但是不管分组。如果将distribute by 和 sort by合起来使用就能够做到，又分组又排序。

12.union联合查询：

用于将多个select语句的结果合并为一个结果集。

union all 不去重，所有结果集加在一起。

union distinct 去重，所有结果集加在一起。

如果union后面不加all，那就是默认distinct，自动会去重的。

如果想要针对其中的select语句设置limit参数，那就必须如下操作：

如下就是，先联合，然后limit再生效。

13.from子句中子查询：

这里面的tmp 其实是给“select num,name from student_local”这个句子命名的

14.where子句中子查询：

where子句支持的子查询：

1.不相关子查询：该子查询不引用父查询中的列，可以将查询结果视为IN和NOT IN语句的常量。

select *

from t1

where t1.num IN (select num from t2 limit 2);

子查询根本没有涉及到父查询中的表和字段。

2.相关子查询：子查询引用父查询中的列。

select A

from t1

where exists (select B from t2 where t1.X = t2.Y);

在这里，子查询引用了父查询中的字段，这个就叫相关子查询。

关于相关子查询，其实是这样的，会先执行select A from t1这个操作，也就是先执行父查询这个操作，然后会逐个的到子查询的集合中去检查是否存在这个父查询的数据，如果子查询的集合中存在我这个父查询的条件，那么我就返回这行数据。但是在不相关子查询中，会先执行子查询的结果集，然后再根据子查询去限制父查询的结果。

in语句，实际上是先计算子查询的，是把外表和内表作hash连接。

exists语句，实际上是对外表做loop循环，每次loop循环再去对内表进行查询。也就是外表一行一行的把数据拿出来跟内表去比对。这样就不用去循环内表了，因为内表比较大。

子查询大的用exists，子查询小的用in。

15.CTE（common table expressions）：

公用表表达式（CTE）是一个临时结果集：该结果集是从WITH子句中指定的简单查询派生而来的，紧接在SELECT或者INSERT关键字之前。

CTE仅在单个语句的执行范围内定义。

CTE可以在SELECT，INSERT，CREATE TABLE AS SELECT 或者CREATE VIEW AS SELECT语句中使用。

例子：

with q1 as (select num,name,age from student where num = 95002)

select *

from q1;

with q1 as (select num,name,age from student where num = 95002)

from q1

select *;

CTEs链式

with q1 as(select * from student where num = 95002),

     q2  as(select num,name,age from q1)

select * from (select num from q2) a;

with q1 as(select * from student where num = 95002)

insert overwrite table s1

select * from q1;

16.6种join语法：

inner join（内连接）：

left join（左连接）：

right join（右连接）：

full outer join（全外连接）：可以是full join

left semi join（左半开连接）：会返回左边表的记录，前提是其记录对于右边的表满足on语句中的判定条件。相当于是inner join，但是只返回左表的信息。

cross join（交叉连接，也叫做笛卡尔乘积）：两个表的行数乘积。

Hive内置运算符：

show functions 显示所有的函数和运算符

describe function count 查看运算符或者函数的使用说明

describe function extended count 使用extended可以查看更加详细的使用说明

关系运算符：

关系运算符是二元运算符，执行的是两个操作数的比较运算。

每个关系运算符都返回boolean类型结果（true或者falze）

举例子：

null 或者not null

select 1 from dual where 'itcast' is null; //这个语句where的条件是false，所以不会执行。

select 1 from dual where 'itcast' is not null; //这个就返回1，写死在这里的。

--like比较：_表示任意单个字符 %表示任意数量字符。

--否定比较：NOT A like B。

select 1 from dual where 'itcast' like 'it_'; //后面的_代表只有一个字符，所以返回空

select 1 from dual where 'itcast' like 'it%'; //后面的%代表可以自由匹配几个字符都行，所以返回1。

select 1 from dual where 'itcast' not like 'hadoo_'; //返回1，not like就是不匹配

select 1 from dual where not 'itcast' like 'hadoo_'; //也返回1，因为不匹配

--rlike：确定字符串是否匹配正则表达式，是REGEXP_LIKE()的同义词。

select 1 from dual where 'itcast' rlike '^i.*t$';

select 1 from dual where '123456' rlike '^\d+$'; --判断是否全为数字。

select 1 from dual where '123456aa' rlike '^\d+$'; --明显不都是数字。

算术运算符：

算术运算符操作数必须是数值类型。分为一元运算符和二元运算符：

一元运算符，只有一个操作数；

二元运算符有两个操作数，运算符在两个操作数之间。

--取整操作：div 给出将A除以B所得的整数部分。

select 17 div 3； --结果就是5

--取余操作：也叫做取模操作

select 17 % 3； --结果就是2

--位与操作： & A和B按位进行与操作的结果。

select 4 & 8 from dual；--4转换二进制：0100 8转换二进制：1000 结果是0

select 6 & 4 from dual；--4转换二进制：0100 6转换二进制：0110 结果是4

--位或操作：^ A和B按位进行异或操作的结果 异或表示两者的值不同，则结果为1。

select 4 ｜ 8 from dual; --4转换二进制： 0100 8转换二进制： 1000 结果是 12

select 6 ｜ 4 from dual; --6转换二进制： 0110 4转换二进制： 0100 结果是 6

--位异或操作：^ A和B按位进行异或操作的结果 异或表示两者的值不同，则结果为1

select 4 ^ 8 from dual; --4转换二进制： 0100 8转换二进制： 1000 结果是 12

select 6 ^ 4 from dual; --6转换二进制： 0110 4转换二进制： 0100 结果是 2

逻辑运算符：

​​​​​​​--与操作： A and B 如果A和B均为true，则为true，否则为false。如果A或者B为NULL，则为NULL。

select 1 from dual where 3>1 and 2>1; //结果为1

--或操作：A or B 如果A或B或两者均为TRUE，则为TRUE，否则为false。

select 1 from dual where 3>1 or 2!=2; // 结果为1

--非操作：NOT A , !A. 如果A为false，则为true，如果A为NULL，则为NULL，否则为FALSE。

select 1 from dual where not 2>1; //返回结果是空。

select 1 from dual where !2=1; //。!2=1 和 not 2=1的结果其实是等价的。

--在：A IN（val1,val2,...）如果A等于任何值，则为TRUE。

select 1 from dual where 11 in (11,22,33); //结果为1

--不在：A NOT IN（val1,val2,...）如果A不等于任何值，则为TRUE。

select 1 from dual where 11 not in(22,33,44); //结果为true，因为11不在这里面。

--逻辑是否存在：[NOT] EXISTS (subquery)

--将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或者FALSE）来决定主查询的数据结果是否得以保留。相关子查询。

select A.* from A where exists (select B.id from B where A.id = B.id);

Hive的函数：

内置函数（built-in Functions）：

数学类型函数：

--取整函数

select round(3.1415926);

--指定精度取整函数：round(double a,int d) //返回指定精度d的double类型。

--向下取整函数：

select floor(3.1415926); //3

select floor(-3.1415926); //-4

--向上取整函数：

select ceil(3.1415926);

select ceil(-3.1415926);

--取随机数函数：rand 每次执行都不一样 返回一个0到1范围内的随机数

select rand();

--指定种子取随机数函数：rand(int seed) 得到一个稳定的随机数序列

select rand(2);

日期类型函数：

--获取当前日期：

select current_date();

--获取当前时间戳：

select current_timestamp();

--获取当前unix时间戳函数：unix_timestamp

select unix_timestamp(); //此时此刻所对应的时间戳。

--日期转unix时间戳函数：unix_timestamp

select unix_timestamp("2011-12-07 13:01:03"); //这个时间的时间戳。

--指定格式日期转unix时间戳函数：unix_timestamp

select unix_timestamp('2011120713:01:03','yyyyMMdd HH:mm:ss');

--unix时间戳转日期函数：from_unixtime

select from_unixtime(xxxx);

select from_unixtime(0,'yyyy-MM-dd HH:mm:ss'); //结果为1970-01-01 00:00:00

--日期比较函数：datediff 日期格式要求'yyyy-MM-dd HH:mm:ss' or 'yyyy-MM-dd'

select datediff('2012-12-08','2012-05-09'); //显示相差的结果：213

--日期增加函数：date_add('2012-02-28',10); //结果为2012-02-18

--日期减少函数：date_sub('2012-01-1',10); //结果为2011-12-22

字符串函数：

describe function extended concat；

--拼接函数：

select concat("angela","baby");

--规定分隔符的拼接函数：

select concat_ws('.','www',array('itcast','cn'));

--字符串截取函数：

select substr("angelababy",2); //ngelababy是从2开始截取的。

select substr("angelababy",-2)；//pos是从1开始的索引，如果为负数则倒着数，结果为by。

select substr("angelababy",2,2); //结果为ng。

--正则表达式替换函数：regexp_replace(str,regexp,rep)

select regexp_replace('100-200','(\d+)','num'); 结果为num-num。

--正则表达式解析函数：regexp_extract(str,regexp[,idx])提取正则匹配到的指定组内容。

select regexp_extract('100-200','(\d+)-(\d+)',2); 结果为200。

-URL解析函数：parse_url 一次解析出多个，可以使用parse_url_tuple这个UDTF函数。

select parse_url('http://www.itcast.cn/path/p1.php?query=1','HOST');

-分割字符串函数：split(str,regex);

select split('apache hive','\s+'); //记住\s+代表任意的空白字符。拆出来的结果是一个数组。

-返回最后一个有数值的分区的函数：max_pt(table_full_name)

select * from table_name where pt = max_pt(table_name);

这样就会返回最后一个有数据的一级分区。

-拆分函数：split_part(拆分字段，拆分的标识符，从哪段开始拆分，要前几段)

split_part(ip,'.',1,3)；这个例子如果ip是234.213.32.34的话，那么最后的答案就应该是234.213.32。

-date_format(date,pattern);

将时间变成我们想要的格式。

date_format(2018-09-21,MMMM); September（九月份）。

​​​​​​​

集合函数：

用的少，如果需要用直接去视频里面看就行。

条件函数：

​​​​​​​if条件判断：if（boolean testCondition, T valueTrue, T valueFalseOrNull）

select if(1=2,100,200);

select if(sex='男','M','W') from student limit 3; //完成了从中文到字符的一个替换过程。

--空判断函数：isnull();

select isnull("allen");

--非空判断函数：isnotnull();

select isnotnull("allen");

select isnotnull(null);

--空值转换函数：nvl(T value,T default_value)

select nvl("allen","itcast"); 如果第一个不为空，那就是第一个值。

select nvl(null,"itcast"); 如果第一个是空的，那么就要第二个值。

--非空查找函数：COALESCE(T v1, T v2, ...);

--返回参数中的第一个非空值；如果所有值都为NULL，那么返回NULL。

select COALESCE(null,11,22,33); //11

select COALESCE(null,null.null,33); //33

select COALESCE(null,null,null); //null

--nullif(a,b)

--如果a=b，则返回null，否则返回第一个

select nullif(11,11); // null

select nullif(11,22); //11

--条件转换函数：

case a when b then c [when d then e] * [else f] end;

select case 100 when 50 then 'tom' when 100 then 'mary' else 'tim' end; // mary

select case sex when '男' then 'male' else 'female' end from student limit 3;

// male female female

类型转换函数：//任意数据类型之间显式的转换：cast

select cast(12.14 as bigint); //12

select cast(12.14 as string); // '12.14'

select cast("hello" as int); // null 结果出来就是空的。

数据脱敏函数：主要完成对数据脱敏转换功能，屏蔽原始数据，主要如下：

mask:查询回的数据，大写字母转换为X，小写字母转换为x，数字转换为n。

select mask("abc123DEF"); //xxxnnnXXX

select mask("abc123DEF", '-' , '.' , '^');

//大写字母替换成-，小写字母替换成. ，数字替换为^。

//...^^^---

mask_first_n(string str[, int n]):对于前几个进行脱敏替换。

select mask_first_n("abc123DEF",4); //xxxn23DEF。

mask_last_n(string str[, int n]);

select mask_last_n("abc123DEF",4); //abc12nXXX。

mask_show_first_n(string str[, int n]); //除了前4个，其余都进行脱敏操作。

select mask_show_first_n("abc123DEF",4); //abc1nnXXX。

mask_show_last_n(string str[, int n]); //除了后4个，其余都进行脱敏操作。

select mask_show_last_N("abc123DEF",4); //xxxnn3DEF。

mask_hash(string|char|varchar str); //返回字符串的hash编码。

select mask_hash("abc123DEF");

其余杂项函数：

哈希加密：取哈希值

select hash("allen");

SHA-1加密：sha1(string/binary)

select sha1("allen");

SHA-2家族算法加密：****sha2(string/binary, int) (SHA-224,SHA-256,SHA384,SHA-512)

select sha2("allen",224);

select sha2("allen",512);

crc32加密：

select crc32("allen");

MD5加密：md5(string/binary)

select md5("allen");

用户定义函数（User-Defined Functions）：

现在的范围是所有的函数，不一定是用户自定义的。

UDF：普通函数，一进一出。

UDAF：聚合函数，多进一出。

select sex from student;

select collect_set(sex) from student;

select collect_list(sex) from student;​​​​​​​

UDTF：表生成函数，一进多出。

explode函数：只能接受array和map这两种类型的函数。

select explode('array'(11,22,33,44,55));

select explode('map'("id",10086,"name","allen","age",18)); //3个元素。

​​​​​​​Lateral View是一种特殊的语法，主要搭配UDTF类型函数一起使用，用于解决UDTF函数的一些查询限制的问题。

只要是使用UDTF函数，就会固定搭配Lateral View使用。

举例子：

举例子：UDAF函数，也就是聚合函数。

--count(*):所有行进行统计，包括NULL行。

--count(1):所有行进行统计，包括NULL行。

--count(column):对column中非NULL进行统计（排除掉null）。

统计男生的数量第一种方法：select sum(if(sex='男',1,0);

统计男生的数量第二种方法：select sum(case when sex='男' then 1 else 0 end);

聚合函数不支持嵌套聚合函数：

select avg(count(*)) from student;

val1 val2

2 3

1 2

null 2

select sum(val1),sum(val1+val2) from tmp_1;// 3 8

sum在计算的时候val如果一个为null那就自动舍弃。

select

sum(coalesce(val1,0)),

sum(coalesce(val1,0)+val2)

from tmp_1;

下面这个的意思就是分成男女性别两组，然后max函数是自动计算struct里面第一个字段的最大值，所以也就是找出男女里面分别年龄最大的和他们的名字。

select

 sex,

 max(struct(age,name)).col1 as age,

 max(struct(age,name)).col2 as name

from student

group by sex;

增强聚合--grouping sets

select

 month,

 day,

 count(distinct cookieid) as nums,

 grouping_id

from cookie_info

group by month,day

grouping sets(month,day)

order by grouping__id

窗口函数：

不光是sum，还可以是avg，min，max等。

select cookieid,createtime,pv,sum(pv) over() as total_pv

from website_pv_info;

select cookieid,createtime,pv,sum(pv) over(partition by cookieid) as total_pv

from website_pv_info;

select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime) as current_total_pv

from website_pv_info;

窗口表达式：windows expression

select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime) as pv1

from website_pv_info;

select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between unbounded preceding and current row) as pv 2

from website_pv_info;

这两个其实是一个效果，都是从第一行到当前行。

select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and current row) as pv4

from website_pv_info;

select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between 3 preceding and 1 following) as pv5

​​​​​​​from website_pv_info;

select cookieid,createtime,pv,sum(pv) over(partition by cookieid order by createtime rows between current row and unbounded following) as pv6

​​​​​​​from website_pv_info;

窗口排序函数--row_number家族(包括dense_rank,rank)：

select

   cookieid,

   createtime,

   pv,

   RANK() over(partition by cookieid order by pv desc) as rn1, //rank

   DENSE_RANK() over(partition by cookieid order by pv desc) as rn2, //dense_rank

   ROW_NUMBER() over(partition by cookieid order by pv desc) as rn3 //row_number

from website_pv_info

where cookieid = 'cookie1';

适合TopN业务分析

select *

from(

select cookieid1,createtime,pv,row_number() over(partition by cookieid order by pv desc) as rn1

from website_pv_info​​​​​​​

) tmp

where tmp.rn1<=3;

窗口排序函数--ntile:

将每个分组内的数据分为指定的若干个桶里（分为若干个部分），并且为每一个桶分配一个桶编号。

如果不能平均分配，则优先分配较小编号的桶，并且各个桶中能放的行数最多相差1.

有时会有这样的需求，如果数据排序后分为三个部分，业务人员只关心其中的一部分，如何将这中间的三分之一数据拿出来呢？NTILE函数的作用就体现在这里。

select *

from(

select cookieid,createtime,pv,ntile(3) over(partition by cookieid order by createtime) as rn2

from website_pv_info​​​​​​​

) tmp

where tmp.rn2 = 1;//这就是前三分之一的部分，也就是第一部分。

---

select *

from(

select cookieid,createtime,pv,ntile(3) over(partition by cookieid order by createtime) as rn2

from website_pv_info​​​​​​​

) tmp

where tmp.rn2 = 2;//这就是中间那个部分，也就是第二部分。

---

select *

from(

select cookieid,createtime,pv,ntile(3) over(partition by cookieid order by createtime) as rn2

from website_pv_info​​​​​​​

) tmp

where tmp.rn2 = 3;//这就是最后那个部分，也就是第三部分。

总而言之，NTILE(x)可以将数据分成x个部分。

窗口分析函数：

LAG():用于统计窗口内往上第n行值

LEAD():用于统计窗口内往下第n行值

FIRST_VALUE():取分组内排序后，截止到当前行，第一个值

LAST_VALUE():取分组内排序后，截止到当前行，最后一个值

抽样函数：

三种方式：

1.随机抽样 ：使用rand()函数来确保随机获取数据，limit来限制抽取的数据个数。

速度有点慢，尤其是在表多的时候。

2.基于数据块抽样：

Block块采样允许随机获取n行数据，百分比数据或指定大小的数据。

但是缺点就是不随机。

3.基于分桶表抽样

针对分桶表进行了优化，优点是既随机速度也很快。

TABLESAMPLE（BUCKET * OUT OF y [ON colname]）--回头再详细了解吧，用的不多。