hive语法全解

create table if not exists student(id int,name string,sex string,age int,department string) row format delimited fields terminated by ',' STORED as textfile location '/hivetest';

说明：

/hivetest下有数据，相当于将student表和hdfs上/hivetest下的数据进行关联了。

因为表中的数据本身也会存储在hdfs，覆盖配置文件中的目录，我们配置文件中配置的hive的表的存储路径在/usr/myhive/warehouse（表的数据），

不会生成新的目录（student表名）；如果不加location，会在存储路径下新建一个目录student(表名)。

如果建的表不在hdfs上，在mysql中存储表的元数据。

create table if not exists student01(id int,name string,sex string,age int,department string) row format delimited fields terminated by ',' STORED as textfile location '/hive02';

说明：

/hive02下没数据，是空目录

查询student01，没有数据。

2）建外部表

create external table if not exists student_ext(id int,name string,sex string,age int,department string) row format delimited fields terminated by ',' STORED as textfile location '/hive02';

内部表和外部表的区别：

删除表测试：

drop table student; 内部表进行删除的时候元数据+原始数据一并删除

drop table student_ext; 外部表进行删除只删除元数据不删原始数据公共数据访问一定建外部表

3）建分区表

分区字段一定不能在建表字段中出现

create external table if not exists student_ptn (id int,name string,sex string,age int) partitioned by (department string) row format delimited fields terminated by ',';

//添加分区

alter table student_ptn add partition (department='IS'); /user/myhive/warehouse/bd1804_day01.db/student_ptn/department=IS

alter table student_ptn add partition (department='MS’); /user/myhive/warehouse/bd1804_day01.db/student_ptn/department=MS

alter table student_ptn add partition (department='CS'); /user/myhive/warehouse/bd1804_day01.db/student_ptn/department=CS

alter table student_ptn add partition (department='PS');

/user/myhive/warehouse/bd1804_day01.db/student_ptn/department=PS

减少扫描范围提升查询性能

4）分桶表：

create external table if not exists student_bk (id int,name string,sex string,age int,department string) CLUSTERED BY (age) SORTED BY (id desc) INTO 3 BUCKETS row format delimited fields terminated by ',';

age.hashcode()%3 ----0 bucket0 ---1 bucket1 -----2 bucket2 余数为2的数据对应分到bucket2中

1）提升抽样性能：抽取一个桶的数据

2）提升join的性能

2、ctas建表

ctas：把查询语句的结果存储在一个表中

create table tablename as select ...... from ...

create table stu_ctas as select id,name,age from student01;

限制：

1.不能创建外部表

2.不能创建分区表

3.不能创建分桶表

3、复制表:like

create table stu_copy like student01;

仅仅复制表结构，不复制表的数据

表的存储路径在默认的路径下

查看表列表

show tables;

show tables in db;查看指定数据库下的表列表

show tables like '';模糊查询

查看建表语句

show create table student_ptn;

CREATE EXTERNAL TABLE `student_ptn`(`id` int,`name` string,`sex` string, `age` int)

PARTITIONED BY (

`department` string)

ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'

WITH SERDEPROPERTIES (

'field.delim'=',',

'serialization.format'=',')

STORED AS INPUTFORMAT

'org.apache.hadoop.mapred.TextInputFormat'

OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'

LOCATION

'hdfs://hadoop01:9000/user/myhive/warehouse/bd1804_day01.db/student_ptn'

TBLPROPERTIES (

'transient_lastDdlTime'='1532588649')

查看表的详细信息

desc student_ptn;查看表的字段信息

desc extended student_ptn; 查看表的详细信息（显示不友好）

desc formatted student_ptn; 查看表的详细信息（格式化之后的，显示友好）

show partitions student_ptn; 查看表的分区

show partitions table_name partition(city='beijing')

查看hive函数列表

show functions;

修改表

修改表名称：

alter table student01 rename to stu_new;

修改表字段：

添加一个列：

alter table stu_copy_new add columns(city string,family int);

修改一个列： change 原列名新列名新列类型

alter table stu_copy_new change family home int; 改列名称

alter table stu_copy_new change home home string; 改类型

alter table stu_copy_new change city city int;

说明：

hive2.3.2版本中

修改类型的时候一定注意小类型----大类型改大类型不可以改为小类型

int-string 可以 string--int不可以

hive1.2.2版本中数据类型可以随便改的

删除列：不支持

替换列：所有列 ??? -> string类型的问题

//alter table stu_copy_new replace columns (name string,ss int); string在前会报错

alter table stu_copy_new replace columns (name int,ss string);//替换所有的列

alter table stu_copy_new replace columns (id int);//替换所有的列

修改分区：

添加分区：

alter table student_ptn add partition(department='aa') partition(department='bb');

修改分区：修改分区的存储路径

1）添加分区的时候指定分区路径

alter table student_ptn add partition(department='cc') location '/usr/myhive/ptn';

2)修改已经添加的分区的路径

alter table student_ptn partition(department='aa') set location '/usr/myhive/ptn01';

删除分区：

alter table student_ptn drop partition(department='aa');

清空表

truncate table student01;

仅仅清空表数据表结构依然保留

删除表

drop table student01;

总结命令:

show databases;

show databases like 'my*';

查看数据库列表

show tables;

show tables in db_name;

查看数据表

show create table table_name; 查看数据表的建表语句

show functions; 查看 hive 函数列表

show partitions table_name;

show partitions table_name partition(city='beijing')

查看 hive 表的分区

desc table_name;

desc extended table_name;

desc formatted table_name;

查看表的详细信息（元数据信息）

desc database db_name; 查看数据库的详细属性信息

desc database extended db_name;

truncate table table_name; 清空数据表

dml:

数据插入：

set hive.exec.mode.local.auto 默认不走本地，走集群

设置hive的本地模式：set hive.exec.mode.local.auto=true; 走本地，单机模式

1）load的方式进行数据加载

load data

本地数据进行加载加关键字local，local inpath

hdfs的数据进行加载不需要加local，inpath

into table tablename;

完整语句：

create table if not exists stu_test01(id int,name string,sex string,age int,department string) row format delimited fields terminated by ',';

1)从本地进行加载

load data local inpath '/home/hadoop/apps/stu.txt' into table stu_test01;

将本地的数据上传到hdfs表的数据存储目录下，本地的数据依然存在

2）从hdfs加载数据

load data inpath '/hive02/stu.txt' into table stu_test01;

hdfs本身就存储的有这个数据，当执行这个操作的时候，如果是复制的操作？hdfs存储了2份，没有必要，应该是移动的过程

2）insert方式

1）insert into table stu_test01 values(1,"zs","f",120,"ycdf");

不建议的

会先创建一个临时表values__tmp__table__1 将插入的数据放在临时表中，后面再从临时表中复制过来

2）单重插入

建表：

create table stu_test02 like stu_test01;

使用查询语句进行插入，每次插入数据是一次查询结果

insert into table stu_test02 select .. from ...

insert into table stu_test02 select * from stu_test01 where age<=20;

数据每插入一次，对stu_test01扫描一次

例子：

需求：将stu_test01中的数据插入到分区表中按年龄进行分区：小于18放在-17 18-18 >18---19

1)创建分区表：

create external table if not exists student_ptn01 (id int,name string,sex string,department string) partitioned by (age int) row format delimited fields terminated by ',';

2）添加分区

alter table student_ptn01 add partition(age=17) partition(age=18) partition(age=19);

3)向分区中添加数据必须制定分区名 *****

注意：分区表中添加数据的时候必须指定分区名，否则会报错，指定分区名用partition(分区)

insert into table student_ptn01 partition(age=17) select id,name,sex,department from stu_test01 where age<18;

insert into table student_ptn01 partition(age=18) select id,name,sex,department from stu_test01 where age=18;

insert into table student_ptn01 partition(age=19) select id,name,sex,department from stu_test01 where age>18;

插入3个分区的数据对表扫描3次分区很多的这个方式不好？

3）多重插入：插入多个查询结果但是对表扫描仅仅1次

from 原来表名

insert into table 新表名1 select .....

insert into table 新表名2 select ....

改进上面的需求：（对分区表进行多重插入）

from stu_test01

insert into table student_ptn01 partition (age=17) select id,name,sex,department where age<18

insert into table student_ptn01 partition (age=18) select id,name,sex,department where age=18

insert into table student_ptn01 partition (age=19) select id,name,sex,department where age>18;

注意分区表的数据插入问题：

1）load的方式

load data local inpath '/home/hadoop/apps/stu.txt' into table student_ptn01 partition (age=17);

load方式没有检查机制，它默认把最后一个字段看做分区字段，所以造成文本中的数据字段跟load之后表中的字段结构不一样。

比如文本中age在倒数第二列，分区表中，age在最后一列。

分区表一般不使用load方式加载

2）insert方式

分区表使用的插入方式

分区表的数据查询的问题：

分区表进行数据查询的时候将分区字段看做普通字段（过滤条件字段）进行查询

select * from student_ptn01 where age=18;//查询分区为18的分区中的数据仅仅扫描分区号18的分区的目录数据

select * from student_ptn01; //全表扫描，查询所有的分区中的数据

注意：分区字段一定是查询结果的最后几个字段

95001 李勇男 CS 19（分区字段）

3）分区表的数据插入

静态分区插入数据：必须先手动创建分区，然后在在指定的分区中插入数据

创建一个分区表，添加分区，向指定分区中添加数据

17-----<18

18-----=18

19----->18

日期做分区：分区数量很多这时候不太实用

不适用的场景：

1）分区数量比较多的时候

2）不知道分区名

动态分区插入数据：

分区会根据数据动态的进行生成，不需要手动添加

1）先创建一个分区表：

create external table if not exists stu_dynamic_ptn (id int,name string,sex string,age int) partitioned by (department string) row format delimited fields terminated by ',';

2）向分区表中动态插入数据

不需要指定分区名只需要指定分区字段查询语句中一定要查询出来分区字段

insert into table stu_dynamic_ptn partition(department) select id,name,sex,age,department from stu_test01;

报错：FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict

需要设置：set hive.exec.dynamic.partition.mode=nonstrict

将动态分区设定为非严格模式

注意：

insert into table stu_dynamic_ptn partition(department) select id,department,sex,age,name from stu_test01;

进行查询的时候默认将最后一个查询的字段作为分区字段，此处按照name进行分区

查看分区：show partitions stu_dynamic_ptn;

若有多个分区字段：

1）create external table if not exists stu_dynamic_ptn01 (id int,name string,sex string) partitioned by (department string,age int) row format delimited fields terminated by ',';

2）动态的方式添加：

insert into table stu_dynamic_ptn01 partition(department,age) select id,name,sex,department,age from stu_test01;

第一个分区字段就是第一级目录，第二分区就是二级目录

/user/myhive/warehouse/bd1804_day01.db/stu_dynamic_ptn01/department=CS/age=18

注意：字段保持一致

hive的1.2.2版本中，如果是多字段分区，必须指定至少一个分区为静态分区

4）分桶表的数据插入：

1） load的方式不支持

load data local inpath '/home/hadoop/apps/stu.txt' into table student_bk;

分桶-----类似于mr中的分区，必须通过mr进行数据插入

load方式是通过与表字段关联进行加载数据，不走mr

/part-00000 part-00001

2）insert 方式

建表语句：

SORTED BY (id desc) 指每一个桶中的排序规则

插入数据：

insert into table student_bk select * from stu_test01;

bucket-1: age%3---------0

bucket-2: age%3---------1

bucket-3: age%3---------2

注意：

hive1.2.2 中需要设置参数：（设置之后才能分桶）

set hive.enforce.bucketing=true; //开启分桶

set mapreduce.job.reduces=3； //设置reduce，几个分桶设置几个（hive1.2.2默认是一个）

In order to change the average load for a reducer (in bytes):

set hive.exec.reducers.bytes.per.reducer=<number> 设置每一个reducetask的吞吐量（一次加载数据量） 256000000byte

In order to limit the maximum number of reducers:

set hive.exec.reducers.max=<number> 设置reducetask的最大运行数量 1009

In order to set a constant number of reducers:

set mapreduce.job.reduces=<number> //设置reducetask实际运行数量（hive1.2.2默认是-1,1个）

说明：

在hive1.0版本中，很多需要设置参数

5）数据导出

将hive表中的数据导出到本地或hdfs

注意：一般导出到本地；如果数据太多，导出到本地会将磁盘占满，只适合数据量少的时候

insert overwrite local directory '' select ...

insert overwrite local directory '/home/hadoop/apps/hivedata' select * from stu_dynamic_ptn where department='IS';

查询：

select 表a join 表b on ..... where .... group by ..... order by ... limit

1）join:

注意：

1）hive中的join只支持等值连接和 and连接

a表:aid name age b表:bid sex bage

c:cage class

select * from a join b on a.aid > b.bid; 转换MR 不支持的

MAP端的key怎么设置 key--->不好设置

hive中本身保存的就是很多的map reduce模板，没有 a.aid>b.bid这种模板

select * from a join b on a.aid=b.bid or a.age=b.bage; 不支持

map的key怎么设置： key复杂

支持：select * from a join b on a.aid=b.bid and a.age=b.bage;

2）多连接条件多表连接

关联字段不一样：（有几个关联字段，转换为几个MR程序）

select * from a join b on a.aid=b.bid join c on b.bage=c.cage;

关联字段一样：（转换为一个MR程序）

select * from a join b on a.aid=b.bid join c on b.bid=c.cid;

reduce端的join情况：map端key：3个表的共同的关联字段

map端的join情况：可以将两个表先加载到本地缓存中，另外一个表读取的时候进行关联，转换一个MR程序

2）常见的关联的方式：

内连接：(inner) join

取两个表的交集

外连接：

左外连接：left outer join

select * from a left outer join b on a.id=b.id;

以join左侧的表为连接依据，右侧的表可以关联上的就关联如果关联不上补null

1 zs 38

2 fpp 90

3 hh null

右外连接：right outer join

select * from a right outer join b on a.id=b.id;

以join右侧的表为连接依据，左侧的表可以关联上的就关联如果关联不上补null

zs 1 38

fpp 2 90

null 4 88

全外连接：full outer join

select * from testa a full outer join testb b on a.aid=b.bid;

并集左侧表有的和右侧表有的全部进行关联上

能关联上的字段就关联关联不上全部补null

1 zs 1 38

2 fpp 2 90

3 hh NULL NULL

NULL NULL 4 88

半连接：左半连接 left semi join-------in

结果只有左半表的数据，并且是能和右表关联上的数据，也就是内连接的一半

select * from testa a left semi join testb b on a.aid=b.bid;

set hive.exec.mode.local.auto=true;本地模式（数据少，用本地快）

1 zs

2 fpp

应用：

select .... from where ziduan in(); 查询某一个字段是否在给定范围内，用in查询MR性能不高，这个时候就出现了半连接

3）hive中的几个by

order by：排序全局排序

select * from stu_test01 order by age;

sort by：排序局部排序

针对于每一个reducetask的结果进行局部排序

默认只有一个reducetask任务的时候，sort by和order by效果一样

set mapreduce.job.reduces=3;

select * from stu_test01 sort by age;

hive中的sort by排序时，map的key是随机选的

reducetask0

95008 李娜女 18 CS

95015 王君男 18 MA

95019 邢小丽女 19 IS

reducetask1

95021 周二男 17 MA

95022 郑明男 20 MA

95013 冯伟男 21 CS

95006 孙庆男 23 CS

reducetask2

95021 周二男 17 MA

95015 王君男 18 MA

distribute by：类似于分桶（也被叫分组）

仅仅会将数据按照给定的字段进行分组，分组的个数取决于reducetask的个数

不会进行排序

set mapreduce.job.reduces=3;

select * from stu_test01 distribute by age; //按照年龄分组

第一组： age%3=0

95015 王君男 18 MA

95013 冯伟男 21 CS

95020 赵钱男 21 IS

95008 李娜女 18 CS

第二组：age%3=1

95002 刘晨女 19 IS

95010 孔小涛男 19 CS

95003 王敏女 22 MA

第三组：age%3=2

95022 郑明男 20 MA

95021 周二男 17 MA

95012 孙花女 20 CS

95006 孙庆男 23 CS

如果想排序，就和sort by结合（局部排序）

select * from stu_test01 distribute by age sort by age desc;

这个语句可以指定map的key（distribute by age），再指定排序规则

95015 王君男 18 MA

95020 赵钱男 21 IS

1 zs f 120 ycdf

95018 王一女 19 IS

95007 易思玲女 19 MA

95021 周二男 17 MA

95022 郑明男 20 MA

95012 孙花女 20 CS

cluster by：先分组，再排序；分组和排序字段必须一致

select * from stu_test01 cluster by age;

应用：

当分组字段和排序字段一样的时候cluster by==distribute by+sort by

当分组字段和排序字段不一样的时候只能用distribute by+sort by不能使用cluster by

4）hive的分析函数：

group by ：分组

hive和mysql中，当查询语句中有分组的时候，查询字段中只能有两种情况

1）分组字段

2）根据分组字段的聚合函数（sum、count、avg）

select department,sum(age) sum from stu_test01 group by department;

select id,department,sum(age) sum from stu_test01 group by department,id;

需求：求每个部门中的年龄之和？

select id,department,sum(age) sum from stu_test01 group by department，id;

需求：求部门表中的每个部门中年龄最大的？

select department, age from stu_test01 group by department,age order by age desc limit 1; limit只是对于全局，所以求不了每个部门中最大的，需要用分析函数解决

3个分析函数 (添加排名的函数)，都需要结合over（窗口函数）：（TopN需要用分析函数）

1、row_number() ----- 适合求topn

row_number() over(partition by 分组字段 order by 指定排序规则)

row_number() over(distribute by 分组字段 sort by 排序规则)

这两个一样，后面的分析函数类似

select department,age,row_number() over(distribute by department sort by age) index from stu_test01;

进行添加排名的时候是每组从1开始依次递增的

CS 16 1

CS 17 2

CS 18 3

IS 15 1

IS 17 2

2、rank()

select department,age,rank() over(distribute by department sort by age) index from stu_test01;

进行添加排名的时候会将并列的排名设置为相同的，后面的排名会自动跳跃（跳过并列的个数）增加

CS 18 1

CS 20 3

IS 15 1

IS 17 2

3、dense_rank()

select department,age,dense_rank() over(distribute by department sort by age) index from stu_test01;

进行添加排名的时候相同的排名会显示相同的编号，但是后面的会顺序递增，不进行跳跃

CS 18 1

CS 20 2

IS 15 1

IS 17 2

hive的数据类型：

基本数据类型：

int ( tinyint smallint int bigint ) 对应于java中byte short int long

double float

string

timestamp

复杂数据类型: （都可以指定泛型）

数组：array

映射：map

对象：struct

1）数组数据类型

数据：id name honey

1 xiaolv luhan,wuyifan,chenglong,2012

2 fpp guanxiaotong,liuyifei,baozi

3 xiaohua sunhonglei,huangxiaoming,huangbo,baby

建表语句：

create table if not exists test_array(id int,name string,honey array<string>) row format delimited fields terminated by '\t' collection items terminated by ',';

collection items terminated by ',';指定集合中每个元素的分隔符

加载数据：

load data local inpath '/home/hadoop/apps/test_array' into table test_array;

查询：

select name,honey[0] from test_array;

取数据的时候如果没有，则返回null

2）map类型

name piaofang

luhan xx:50,aa:3

huangbo shangsheng:60,douniu:70,taijiong:10

建表：

create table if not exists test_map(name string,piaofang map<string,int>) row format delimited fields terminated by '\t' collection items terminated by ',' map keys terminated by ':';

fields terminated by '\t' 列分隔符

collection items terminated by ',' 元素分割符

map keys terminated by ':'; map中的每个元素的key-value的分隔符

指定分割符规律：从外到内依次指定

查询：

select name,piaofang["shangsheng"] from test_map;

3）struct对象类型

struct---class{int id;string name;int age}

id info

1 zs,jiangxi,123

2 ww,xinjiang,345

3 zl,jinan,678

建表：

create table if not exists test_struct(id int,info struct<name:string,address:string,num:bigint>) row format delimited fields terminated by '\t' collection items terminated by ',';

查询：

select id,info.name from test_struct;

应用场景对比：

数据类型相同并且单值的数据的个数不确定的时候-----array

键值对------map

数据比较规整，数据的字段个数形同，每个列数据类型一样-------struct 对象

视图：

view用ctas规则

创建视图：

create view my_view as select * from stu_test01 where age>18;

查看视图列表：

show tables;

show views;（只查出视图）

查看视图的详细信息：

desc my_view;

desc formatted my_view;

Table Type: VIRTUAL_VIEW 虚拟视图（类型：external、manage、VIRTUAL_VIEW ）

删除视图：

drop table my_view; 删除不了

drop view my_view; 删除视图

注意：

1）hive中的视图没有物化视图，只有逻辑视图

2）hive中的视图仅仅相当于一个查询语句的快捷方式

3）hive中的视图只支持查询，不支持insert update delete 在执行查询的时候，视图才开始被真正执行

4）hive中的视图会将自己代表的sql语句存储在元数据库

hive中的函数：

内置函数：

1）查看hive中有哪些内置函数：

show functions;

hive2.3.2版本-----271个函数

2）查看某一个内置函数的信息：

desc function 内置函数名称;

desc function extended 函数名; 查看详细

自定义函数：当内置函数满足不了需求的时候这时候需要使用自定义函数

看几个内置函数：

集合函数：

map：生成map集合的

select map(1,2,3,4);

将奇数位的看做key 将偶数位看做value

参数必须是偶数个

array：生成数组

select array(1,2,3);

create table test as select array(1,2,3,4) arr;

查询：select arr[0] from test;

数值型：（+、-、*、/...）

floor：取不大于给定参数的最大整数floor(-4.5)

ceil：大于参数的最小整数

字符串：

substr(str,pos,len)：下标从1开始

参数2：为正数：从左向右标的下标从1开始

为负数：从右向左标的下标从-1开始

截取的时候都是从左向右进行字符串截取

substring()：substring(str,-2) 取字符串的最后两个字符

instr(str,substr) 字符串查找；返回第一次查到的位置，不存在，则返回0

split（str,分隔符/正则表达式）

nvl()： nvl(value,default_value) getLong(属性，值)

select nvl(honey[3],”myself”) from test; //如果为空，就取默认值

字符串拼接

concat()

select concat(‘abc’,’aa’); //abcaa

指定分割符拼接

concat_ws()

select concat_ws(“-”,”ss”,”kk”,array(“ss”,”zz”)); //ss-kk-ss-zz

炸裂函数：一行转多行（针对数组、map）

explode()

name piaofang

luhan {"xx":50,"aa":3}

期望变成 luhan "xx":50

luhan "aa":3

explode(a) - separates the elements of array a into multiple rows, or the elements of a map into multiple rows and columns

将数组分成多个行或将map分成多个行和列（2列一列key另一列value）

参数只能是array或map类型

例子： select explode(piaofang) from test_map;

xx 50

aa 3

shangsheng 60

douniu 70

taijiong 10

例子：select explode(piaofang),name from test_map; 综合查询的时候报错

报错： SemanticException [Error 10081]: UDTF's are not supported outside the SELECT clause, nor nested in expressions

如果想要同时查询姓名和票房字段炸裂的结果，需要借助虚拟横向视图（ lateral view ）

格式：

select tf.* from (select 0) t lateral view explode(array(‘A’,’B’,’C’)) tf;（官网格式）

lateral view explode(array(‘A’,’B’,’C’)) tf：将炸裂的结果存储为虚拟横向视图

select name,tf.mykey,tf.myvalue

lateral view explode(piaofang) tf(视图别名) as mykey,myvalue(炸裂出来的字段别名)；

这个题的完整语句：

select name,tf.mykey,tf.myvalue from test_map lateral view explode(piaofang) tf as mykey,myvalue;

或者

select name,tf.* from test_map lateral view explode(piaofang) tf;

结果：

luhan xx 50

luhan aa 3

huangbo shangsheng 60

huangbo douniu 70

huangbo taijiong 10

hive内置函数大全

一、关系运算：

1. 等值比较: =

2. 等值比较:<=>

3. 不等值比较: <>和!=

4. 小于比较: <

5. 小于等于比较: <=

6. 大于比较: >

7. 大于等于比较: >=

8. 区间比较

9. 空值判断: IS NULL

10. 非空判断: IS NOT NULL

10. LIKE比较: LIKE

11. JAVA的LIKE操作: RLIKE

12. REGEXP操作: REGEXP

二、数学运算：

1. 加法操作: +

2. 减法操作: –

3. 乘法操作: *

4. 除法操作: /

5. 取余操作: %

6. 位与操作: &

7. 位或操作: |

8. 位异或操作: ^

9．位取反操作: ~

三、逻辑运算：

1. 逻辑与操作: AND 、&&

2. 逻辑或操作: OR 、||

3. 逻辑非操作: NOT、!

四、复合类型构造函数

1. map结构

2. struct结构

3. named_struct结构

4. array结构

5. create_union

五、复合类型操作符

1. 获取array中的元素

2. 获取map中的元素

3. 获取struct中的元素

六、数值计算函数

1. 取整函数: round

2. 指定精度取整函数: round

3. 向下取整函数: floor

4. 向上取整函数: ceil

5. 向上取整函数: ceiling

6. 取随机数函数: rand

7. 自然指数函数: exp

8. 以10为底对数函数: log10

9. 以2为底对数函数: log2

10. 对数函数: log

11. 幂运算函数: pow

12. 幂运算函数: power

13. 开平方函数: sqrt

14. 二进制函数: bin

15. 十六进制函数: hex

16. 反转十六进制函数: unhex

17. 进制转换函数: conv

18. 绝对值函数: abs

19. 正取余函数: pmod

20. 正弦函数: sin

21. 反正弦函数: asin

22. 余弦函数: cos

23. 反余弦函数: acos

24. positive函数: positive

25. negative函数: negative

七、集合操作函数

1. map类型大小：size

2. array类型大小：size

3. 判断元素数组是否包含元素：array_contains(array，value)

4. 获取map中所有value集合

5. 获取map中所有key集合

6. 数组排序

八、类型转换函数

1. 二进制转换：binary

2. 基础类型之间强制转换：cast cast(text as bigint)

九、日期函数

1. UNIX时间戳转日期函数: from_unixtime

2. 获取当前UNIX时间戳函数: unix_timestamp

3. 日期转UNIX时间戳函数: unix_timestamp

4. 指定格式日期转UNIX时间戳函数: unix_timestamp

5. 日期时间转日期函数: to_date

6. 日期转年函数: year

7. 日期转月函数: month

8. 日期转天函数: day

9. 日期转小时函数: hour

10. 日期转分钟函数: minute

11. 日期转秒函数: second

12. 日期转周函数: weekofyear

13. 日期比较函数: datediff

14. 日期增加函数: date_add

15. 日期减少函数: date_sub

十、条件函数

1. If函数: if if(expr1，expr2，expr3)类似于三目表达式

2. 非空查找函数: COALESCE

3. 条件判断函数：CASE

4. 条件判断函数：CASE

十一、字符串函数

1. 字符ascii码函数：ascii

2. base64字符串

3. 字符串连接函数：concat

4. 带分隔符字符串连接函数：concat_ws

5. 数组转换成字符串的函数：concat_ws

6. 小数位格式化成字符串函数：format_number

7. 字符串截取函数：substr,substring

8. 字符串截取函数：substr,substring

9. 字符串查找函数：instr instr(str，substr) 是否包含字符串，包含返回正数

10. 字符串长度函数：length

11. 字符串查找函数：locate

12. 字符串格式化函数：printf

13. 字符串转换成map函数：str_to_map

14. base64解码函数：unbase64(string str)

15. 字符串转大写函数：upper,ucase

16. 字符串转小写函数：lower,lcase

17. 去空格函数：trim

18. 左边去空格函数：ltrim

19. 右边去空格函数：rtrim

20. 正则表达式替换函数：regexp_replace

21. 正则表达式解析函数：regexp_extract

22. URL解析函数：parse_url

23. json解析函数：get_json_object

24. 空格字符串函数：space

25. 重复字符串函数：repeat

26. 左补足函数：lpad

27. 右补足函数：rpad

28. 分割字符串函数: split split(str，'\\|') 以|分割的数组

29. 集合查找函数: find_in_set

30. 分词函数：sentences

31. 分词后统计一起出现频次最高的TOP-K

32. 分词后统计与指定单词一起出现频次最高的TOP-K

十二、混合函数

1. 调用Java函数：java_method

2. 调用Java函数：reflect

3. 字符串的hash值：hash

十三、XPath解析XML函数

1. xpath

2. xpath_string

3. xpath_boolean

4. xpath_short, xpath_int, xpath_long

5. xpath_float, xpath_double, xpath_number

十四、汇总统计函数（UDAF）

1. 个数统计函数: count

2. 总和统计函数: sum

3. 平均值统计函数: avg

4. 最小值统计函数: min

5. 最大值统计函数: max

6. 非空集合总体变量函数: var_pop

7. 非空集合样本变量函数: var_samp

8. 总体标准偏离函数: stddev_pop

9. 样本标准偏离函数: stddev_samp

10．中位数函数: percentile

11. 中位数函数: percentile

12. 近似中位数函数: percentile_approx

13. 近似中位数函数: percentile_approx

14. 直方图: histogram_numeric

15. 集合去重数：collect_set

16. 集合不去重函数：collect_list

十五、表格生成函数Table-Generating Functions (UDTF)

1. 数组拆分成多行：explode

2. Map拆分成多行：explode

why do not

关注

6
点赞
踩
42

收藏

觉得还不错? 一键收藏
0
评论
hive语法全解

目录ddl: data define lauguage库操作：表操作：建表说明：创建表：查看表列表查看建表语句查看表的详细信息查看hive函数列表修改表清空表删除表dml:数据插入：1）load的方式进行数据加载2）insert方式例子：3）分区表的数据插入4）分桶表的数据插入：5）数据导出查询：1...
复制链接

扫一扫