赞
踩
一、数据类型
1. 基本类型
数据类型 | 大小 | 范围 | 示例 |
Tinyint | 1byte | -128~127 | 100Y |
Smallint | 2byte | -32,768~32,767 | 100S |
Int/Integer | 4byte | -2,147,483,648~2,147,483,647 | 100 |
Bigint | 8byte | -9,223,372,036,854,775,808~9,223,372,036,854,775,807 | 100L |
Float | 4byte | 单精度浮点数 | 3.1415926 |
Double | 8byte | 双精度浮点数 | 3.1415926 |
Decimal | - | 高精度浮点数 | Decimal(9,8) |
Boolean | - | 布尔型,True/False | true |
Binary | - | 二进制类型 | - |
(1)数字类型
- 整数类型:
-2,147,483,648~2,147,483,647之间的整数类型默认是Int型,除非指定了格式100Y、100S、100L、会自动转换为Tinyint、Smallint、Bigint- 浮点数类型:
浮点数默认会当作Double型;
Hive中的Decimal基于Java中的BigDecimal,Decimal不指定精度时默认为Decimal(10,0);
(2)字符串类型
- String(后面常用string)
类型可以用单引号(')或双引号('')定义,这个类型是定义字符串的常用类型。- Varchar
Varchar类型由长度定义,范围为1~65355,如果存入的字符串长度超过了定义长度,超出部分会被截断。尾部的空格也会作为字符串的一部分,影响字符串的比较。- Char
char是固定长度的,最大长度255,且尾部的空格不影响字符串的比较。
三种类型对尾部的区别,参考如下例子,每个字段都插入同样的字符且在尾部有不同的空格。
- --建表
- create table char_a(
- c1 char(4),
- c2 char(5),
- str1 string,
- str2 string,
- var1 varchar(4),
- var2 varchar(6));
- --插入数据
- insert into char_a values('ccc ','ccc ','ccc ','ccc ','ccc ','ccc ');
- --查询
- select c1=c2,str1=str2,var1=var2 from char_a;
- true false false
-
- --示例
- create table str_tab(a varchar(5), b string);
- insert into str_tab values ('aaaaaaa', 'aaaaaaaa');
- --hive是粗粒度的,虽然定义的a字符长度为5,insert了一个字符长度为7的字符串,依然可以插入,但是读的时候会进行类型的校验,因此在都的时候会把剩下两个截断,不显示。string类型无影响。
(3)日期与时间戳
Timestamp
Timestamp表示UTC时间,可以是以秒为单位的整数;带精度的浮点数,最大精确到小数点后9位,纳秒级;java.sql.Timestamp格式的字符串YYYY-MM-DD hh:mm:ss.fffffffff
Date
Hive中的Date只支持YYYY-MM-DD格式的日期,其余写法都是错误的,如需带上时分秒,请使用timestamp。
- --显示年月日时分秒
- create table time_tab(t timestamp);
- insert into time_tab values('2023-12-10 7:55:00');
- --只显示年月日
- create table date_tab(t date);
- insert into date_tab values('2023-12-10 7:55:00');
日期相关函数:
获取当前timestamp:
current_timestamp() --返回值:timestamp
获取当前日期:
current_date() --返回值:date
格式化Timestamp/Date/String 为字符串:
- date_format(date/timestamp/string ts, string fmt) --返回值:字符串
-
- --示例:
- --当前日期格式化
- select date_format(current_date(),'yyyy-MM-dd HH:mm:ss');
- --当前时间格式化
- select date_format(current_timestamp(),'yyyy|MM|dd HH-mm-ss');
- --字符串格式化
- select date_format('2023-12-10 9:39:59','yyyy-MM-dd');
Unix时间戳:
【当前时间或时间字符串】转【Unix时间戳】:
把一个日期类型的字段转换为Unix时间戳,应该是Bigint类型。从1970-01-01 00:00:00 UTC到指定时间的秒数,例如:1530752400。
- --获取指定字符串的Unix时间戳
- select unix_timestamp('2023-12-10 12:30:22');
- --获取当前timestamp的Unix时间戳
- select unix_timestamp(current_timestamp());
【Unix时间戳】转【时间字符串】:
- from_unixtime(bigint unixtime[,string format])
- --unixtime: 从1970-01-01 00:00:00 UTC到指定时间的秒数
- --format: 目标转换格式
- --返回值: string
- --说明: 转化为Unix时间戳(从1970-01-01 00:00:00 UTC到指定时间的秒数)到当前时区的时间格式
-
- 示例:
- --转换成yyyy-MM-dd HH:mm:ss
- select from_unixtime(1530755469);
- --转换成指定格式的字符串
- select from_unixtime(1530755469, "yyyy-MM-dd");
- --两个日期相差的天数
- select datediff('2023-10-10','2023-12-10');
2. 复杂类型
Struct
Struct类似于java的类变量使用,Hive中定义的struct类型也可以使用点来访问。从文件加载数据时,文件里的数据分隔符要和建表指定的一致。
struct(val1,val2,val3,...),只有字段值;name_struct(name1,val1,name2,val2,...),带有字段名和字段值;一般用struct都是带有字段名和字段值
Array
相当于java的数组,通过arr[下标] 获取元素数据。Array表示一组相同数据类型的集合,下标从零开始,可以用下标访问,如:arr[0]。
Map
相当于java的Map,Map是一组键值对的组合,可以通过key访问value,键值之间同样要在创建表时指定分隔符。如:map_col['name']
Hive除了支持Struct、Array、Map这些原生集合类型,还支持集合的组合,不支持集合里再组合多个集合。
示例:创建带有复合结构的表
- create table complex(
- id int,
- struct_col struct<name:string,country:string>,
- array_col array<string>,
- map_col map<string,string>,
- union_col map<string,array<string>>
- )
- row format delimited fields terminated by ','
- collection items terminated by '-'
- map keys terminated by ':';
- --其中:
- --这个子句表明Hive使用字符','作为列分隔符
- row format delimited fields terminated by ','
- --这个子句表明Hive使用字符'-'作为集合元素间分隔符(一个字符各个item的分隔符)
- collection items terminated by '-'
- --这个子句表明Hive使用字符作为Map的键和值之间分隔符
- map keys terminated by ':'
-
- --查询导入数据
- insert into table complex select 100, named_struct('name','yae','country','cn') as struct_col,array('99','21','33') as array_col,map('english','aaaa') as array_col,map('english','aaaa') as map_col,map('english',array('99','21','33')) as union_col;
- hadoop fs -get /hive/warehouse/yae.db/complex/000000_0
- cat 000000_0
- 100,yae-cn,99-21-33,english:aaaa,english:99^V^D21^V^D33
- --只能在第一层字段中做约束,如果字段中嵌入了复杂结构,是约束不了的
- --练习拿到字段中的数据
- select struct.name from complex;
- select array_col[0] from complex;
- select map_col['english'] from complex;
- select union_col['english'][2] from complex;
Hive读取json格式数据:
JSON是一个包含对象或数组的字符串。数据为 键 / 值 (name/value) 对;数据由逗号(,)分隔;大括号保存对象(Object);方括号保存数组(Array)。
例如:
- {"code":"100"}
- # 对象由花括号括起来,逗号分隔的成员构成,成员是字符串键和上文所述的值由逗号分隔的键值对组成;
- {"code":20,"type":"mysql"}
- # 数组是由方括号括起来的一组值构成:
- {"datesource":[
- {"code":"20","type":"mysql"},
- {"code":"20","type":"mysql"},
- {"code":"20","type":"mysql"}
- ]}
-
处理json串,Hive中提供了获取json数据的方法get_json_object
- --获取json串中level这个key对应的值,$代表这个json串.key
- select get_json_object('{"level":"2","time":"1650973942596","type":"0"}','$.level') as level;
- 2
- --获取json串中所有的key对应的值/读取多个字段(通过一个函数获取多个值/调用多次get_json_object函数)
- --time是关键字不能取为字段名
- select
- get_json_object('{"level:"2","time":"1650973942596","type":"0"}','$.level') as level,
- get_json_object('{"level:"2","time":"1650973942596","type":"0"}','$.time') as times,
- get_json_object('{"level:"2","time":"1650973942596","type":"0"}','$.type') as type;
为解决get_json_object一次解析不了整个json文件的问题,就有了json_tuple函数,一条便能处理一条json数据,基础语法为:
json_tuple(json_string, k1, k2 ... )
解析json字符串json_string,可以指定多个json数据中的key,返回对应的value,如果输入的json字符串无效,那么返回Null。
select json_tuple('{"level":"2","time":"123456486351","type":"0"}','level','time','type');
Hive解析JSON数组:
- [{"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}]
- 需求:解析JSON数组中website和name的数据
- 1. 第一步将[]去掉,需要用到regexp_replace函数
-
- 将字符串A中的符合Java正则表达式B的部分替换为C
- regexp_replace(string A, string B, string C)
-
- Hive中没有可以解析json数组的函数,就需要一步一步拆解,拿到数组中的json串,再对json串进行单独处理
- \\代表转义,匹配[或者]替换成空字符串
- select regexp_replace('[{"website":"baidu.com","name":"百度"},
- {"website":"google.com","name":"谷歌"}]','\\[|\\]','');
-
- 返回的也是一个字符串
- {"website":"baidu.com","name":"百度"},{"website":"google.com","name":"谷歌"}
- 拿到json串,不能按照,分隔,会切分成数组。需要将中间的,替换成能切分的符号比如|
-
- 2. 再次使用regexp_replace函数
- select regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},
- {"website":"google.com","name":"谷歌"}]','\\[|\\]',''), '\\}\\,\\{', '\\}\\|\\{')
-
- 返回
- {"website":"baidu.com","name":"百度"}|{"website":"google.com","name":"谷歌"}
-
- 3. 按照|进行切分后会转换成数组
- select split(regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},
- {"website":"google.com","name":"谷歌"}]','\\[|\\]',''), '\\}\\,\\{', '\\}\\|\\{'), '\\|');
- ["{\"website\":\"baidu.com\",\"name\":\"百度\"}","{\"website\":\"google.com\",\"name\":\"谷歌\"}"]
-
- 4. 想拿到数组中的每一个元素,调用get_json_object函数进行处理
- 需要把数组中的每一个元素当成一行一行数据进行打印,即explode函数:
- explode(array or map)
- explode()函数接收一个array或map类型的数据作为输入,然后将array或map里面的元素按照每行的形式输出,即将Hive一列中复杂的array或map结构拆分成多行显示,也被称为列转行函数。
- select json_tuple(json,'website','name') from (select explode(split(regexp_replace(regexp_replace('[{"website":"baidu.com","name":"百度"},
- {"website":"google.com","name":"谷歌"}]','\\[|\\]',''), '\\}\\,\\{', '\\}\\|\\{'), '\\|')) as json) t;
二、操作符
1. 关系操作符
以下操作符比较操作数(operands)从而产生true/false值
运算符 | 操作数 | 描述 |
A=B | 所有基本类型 | 如果表达A等于表达B,结果True,否则False。 |
A!=B | 所有基本类型 | 如果A不等于表达式B表达返回True,否则False。 |
A<B | 所有基本类型 | True,如果表达式小于表达式B,否则False。 |
A<=B | 所有基本类型 | True,如果表达式小于或等于表达式B,否则False。 |
A>B | 所有基本类型 | True,如果表达式A大于表达式B,否则False。 |
A>=B | 所有基本类型 | True,如果表达式A大于或等于表达式B,否则False |
A is null | 所有类型 | True,如果表达式的计算结果为null,否则False |
A is not null | 所有类型 | False,如果表达式A的计算结果为Null,否则True。 |
A like B | 字符串 | True,如果字符串模式A匹配到B,否则False。关系型数据库中的like功能。 |
A rlike B | 字符串 | B是否在A里面,在是True,否则是False(B可以是Java正则表达式) |
A regexp B | 字符串 | 等同于rlike |
- 是否包含aa
- select * from complex where map_col['english'] like '%aa%';
- \s表示空白字符,是否有空白字符,\S表示非空白字符
- select * from complex where map_col['english'] rlike '\\s';
- select * from complex where map_col['english'] rlike '\\S';
2. 算数运算符
Hive中可用的算术运算符:
运算符 | 操作 | 描述 |
A + B | 所有数字类型 | A加B的结果 |
A - B | 所有数字类型 | A减B的结果 |
A * B | 所有数字类型 | A乘以B的结果 |
A / B | 所有数字类型 | A除以B的结果 |
A % B | 所有数字类型 | A除以B产生的余数 |
A & B | 所有数字类型 | A和B的按位与结果 |
A | B | 所有数字类型 | A和B的按位或结果 |
A ^ B | 所有数字类型 | A和B的按位异或结果 |
~A | 所有数字类型 | A按位非的结果 |
- select 5/2;
- 2.5
- select 9%3;
- 0
- select 12%5;
- 2
- select ~7;
- -8
- select ~8;
- -9
3. 逻辑运算符
运算符是逻辑表达式,所有这些返回True或False
运算符 | 操作 | 描述 |
A and B | boolean | True,如果A和B都是True,返回True,否则False |
A or B | boolean | True,如果A或B或两者都是True,返回True,否则False |
not A | boolean | True,如果A是False,返回True,否则False |
- select 3>5 and 5<8;
- false
- select 3<5 and 5<8;
- true
- select 3>5 or 5<8;
- true
- select not 3>5;
- true
三、聚合函数
将多行聚合成一行
返回类型 | 函数 | 描述 |
Bigint | count(*), count(expr) | count(*) -返回检索行的总数 |
Double | sum(col), sum(distinct col) | 返回该组或该组中的列的不同值的分组和所有元素的总和 |
Double | avg(col), avg(distinct col) | 返回上述组或该组中的列的不同值的元素的平均值 |
Double | min(col) | 返回该组中的列的最小值 |
Double | max(col) | 返回该组中的列的最大值 |
count(1) count(*) count(字段)
count(1) count(*) 是包含null值,count(字段)不包含null值,count(1)稍微比count(*)快一点
- 准备数据文件data.txt
- 创建表
- create table `user_install_status_limit`(
- `aid` string,
- `pkgname` string,
- `uptime` bigint,
- `type` int,
- `country` string,
- `gpcategory` string
- ) row format delimited fields terminated by '\t';
- 将导出的100条data数据放到表的HDFS目录
- hadoop fs -put data.txt /hive/warehouse/yae.db/user_install_status_limit
- --其中
- aid: 每个用户的标识
- pkgname: 用户安装某个手机应用的包名
- uptime: 更新时间
- type: 系统预装应用还是用户自己装的应用
- country: 用户所在国家
- gpcategory: 应用的类型,游戏类、社交类、....
- --统计不同的用户 统计用户id以8d304开头的记录数
- select count(distinct aid), sum(if(aid like '8d304%',1,0)) from user_install_status_limit;
- --统计按照国家分组后的最大值
- select max(a.n) from
- (select country,count(1) n from user_install_status_limit group by country) a;
-
- --统计按照国家分组后的最大值和国家码
- --select * from 数据集1 t1 join 数据集2 t2 on t1.xx=t2.xx;
- --select * from 数据集2 t2 where t2.num in 数据集 t;
- --select * from 数据集 group by country order by num desc limit 1;
- --1. 通过join的方式
- select t2.* from
- (select max(num) as maxnum from
- (select country,count(*) as num from user_install_status_limit group by country) t) t1
- join
- (select country,count(*) as num from user_install_status_limit group by country) t2
- on t1.maxnum=t2.num;
- --2. 通过子查询的方式
- select t2.* from
- (select country,count(*) as num from user_install_status_limit group by country) t2
- where t2.num in (select max(num) as maxnum from
- (select country,count(*) as num from user_install_status_limit group by country) t);
- --3. 通过order by 排序limit 1,hive2.x版本order by后count(*)需要起别名,高版本不需要
- select country,count(*) as num from user_install_status_limit group by country order by num desc limit 1;
-
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。