欢迎光临
Hive sql
   

Hive sql

DML-Load加载数据:

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

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

有local就在本地文件系统

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

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

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

Hive sql,第1张

在这种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;

Hive sql,第2张​​​​​​​

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分组的字段,要么是被聚合函数应用的字段。

Hive sql,第3张

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:

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

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

两个例子:

1.

select * from student cluster by num;

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

2.

先设置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参数,那就必须如下操作:

Hive sql,第4张

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

Hive sql,第5张

13.from子句中子查询:

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

Hive sql,第6张

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(内连接):

Hive sql,第7张

left join(左连接):

Hive sql,第8张

right join(右连接):

Hive sql,第9张

full outer join(全外连接):可以是full join

Hive sql,第10张

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

cross join(交叉连接,也叫做笛卡尔乘积):两个表的行数乘积。

Hive sql,第11张

Hive内置运算符:

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

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

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

关系运算符:

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

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

Hive sql,第12张

举例子:

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+$'; --明显不都是数字。

算术运算符:

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

一元运算符,只有一个操作数;

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

Hive sql,第13张

--取整操作: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(九月份)。

​​​​​​​Hive sql,第14张

集合函数:

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

条件函数:

​​​​​​​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使用。

举例子:

Hive sql,第15张

举例子: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;

Hive sql,第16张

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

from website_pv_info;

Hive sql,第17张

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

from website_pv_info;

Hive sql,第18张

窗口表达式: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;

Hive sql,第19张

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'; 

Hive sql,第20张

适合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():取分组内排序后,截止到当前行,最后一个值

Hive sql,第21张

Hive sql,第22张

抽样函数:

三种方式:

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

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

Hive sql,第23张

2.基于数据块抽样:

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

但是缺点就是不随机。

Hive sql,第24张

3.基于分桶表抽样

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

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

 
 八下数学练习册答案  十一在爱情里代表什么意思  杜鹃花是春天开花吗  蝴蝶兰寓意 招财  seeyouagain什么意思  多肉落地生根花有毒吗 
打赏
版权声明:本文采用知识共享 署名4.0国际许可协议 [BY-NC-SA] 进行授权
文章名称:《Hive sql》
文章链接:https://goodmancom.com/wl/175950.html