时间戳

首页 > 技术文章 > SQL学习笔记系列（十）Hive SQL&Spark SQL

SQL学习笔记系列（十）Hive SQL&Spark SQL

HLBBLOG 2021-08-14 18:25 原文

Hive数据类型

基本数据类型【1】

数据类型 所占字节
TINYINT 1字节整数
SMALLINT 2字节整数
INT/INTRGER 4字节整数
BIGINT 8字节整数
FLOAT 4字节单精度浮点数
DOUBLE 8字节双精度浮点数
DATE
STRING

复杂数据类型

数据类型 描述 示例
ARRAY 一组有序字段。字段类型必须相同 Array(1,2)
MAP 一组无序的键值对，键的类型必须是原子的，值可以是任何类型，同一个映射的键的类型必须相同，值的类型也必须相同 Map('a',1,'b',2)
STRUT 一组命名的字段，字段类型可以不同 Struct('a',1,1,0)

分区表

hive中没有索引，导致查询速度很慢，如果不设置分区，则每次查询会全表扫描

  • 如果该表为分区表时，在where条件中必须对分区字段进行限制
  • 如果该表为分区表且为全量表，需要注意分区字段的限制情况
    • 如：where dt=get_dt_date(get_date(-1))
      and dt between '20210813' and '20210814' 是不行的
    • 全量表： 一个dt就是截至对应dt及以前的历史全量数据，所以只需要拿最新的dt就是最新全量数据。如果对全量分区表的分区字段（dt）进行范围选择，会导致报错。 （订单表一般是全量表，或者说除了流量、曝光这些发生了之后状态就不会再变化的表是增量表以外，其他基本都是全量表）
    • 增量表：一个dt只是当天的数据，一天天累计下来，所以就叫增量表。（流量表一般是增量表）
  • 非分区表的全量表可以直接对dt进行范围的选择

执行顺序

from->where->group by->having->select->order by->limit

时间函数

hive数据库中存储的时间是string类型的(string是字符串，int是整数)

把时间戳转为日期

  • from_unixtime(paytime,'yyyy-MM-dd hh:mm:ss')

转化UNIX时间戳（从1970-01-01 00:00:00 UTC到指定时间的秒数）到当前时区的时间格式，返回值: string

select from_unixtime(1323308943,'yyyy-MM-dd HH:mm:ss');
--2011-12-08 09:49:03
select from_unixtime(1323308943,'yyyyMMdd');
--20111208
select from_unixtime(1323308943,'yyyy-MM-dd');
--2011-12-08
select from_unixtime(1323308943,'yyyy-MM');
--2011-12

把日期转为时间戳

  • unix_timestamp(string date)
select unix_timestamp()
--1323309615(获得当前时区的UNIX时间戳)

select unix_timestamp('2011-12-07 13:01:03')
--1323234063(转换格式为“yyyy-MM-dd HH:mm:ss“的日期到UNIX时间戳。如果转化失败，则返回0。)

select unix_timestamp('20111207 13:01:03','yyyyMMdd HH:mm:ss');
--1323234063

select unix_timestamp('2011-12-07 13:05','yyyy-MM-dd HH:mm');
--1323234300

select unix_timestamp('2011-12','yyyy-MM');
--1322668800（转换pattern格式的日期到UNIX时间戳。如果转化失败，则返回0）

只能识别 2011-12-07 13:01:03 这种完全格式的时间,返回值: bigint

日期转换函数

  • date_format（date/timestamp/string ts, string fmt）
select date_format('2015-04-08', 'y');
--2015
select date_format('2015-04-08', 'yyyy');
--2015
select date_format('2015-04-08', 'yyyy-MM');
--2015-04
select date_format('2015-04-08 10:10:01', 'yyyy-MM');
--2015-04
select date_format('2015-04-08', 'yyyy-MM-dd');
--2015-04-08

日期时间转日期

  • to_date(string/timestamp)

说明: 返回日期时间字段中的日期部分。返回值:string,year()、month()、day（）、month（）、minute（）、second（）同理。

select to_date('2011-12-08 10:03:01');
--2011-12-08
select to_date('2011-12-08');
--2011-12-08
select to_date('2011-12');
--NULL
  • get_date()

get_date()可以把输入的日期往前或往后偏移，或是获取当前日期，或是获取当前日期往前后偏移N天的日期

日期转周

  • weekofyear (string date)

说明: 返回日期在当前的周数,返回值: int

select weekofyear('2011-12-08 10:03:01');
--49

计算日期间隔

  • datediff(string enddate,string startdate)
    结束时间-开始时间

日期减少函数

  • date_sub(stringstartdate,int_days)

日期增加函数

  • date_add(string,startdate,int days)

格式转换函数

  • CAST (expression AS data_type)

CAST()函数将任何类型的值转换为具有指定类型的值。目标类型可以是以下类型之一：BINARY，CHAR，DATE，DATETIME，TIME，DECIMAL，SIGNED，UNSIGNED

cast(create_time as timestamp)
--把create_time创建时间转化成timestamp类型

条件函数

  • case when
  • if

字符串函数

截取函数

  • substr(string,int start,int len)如果不指定截取长度，则从起始位一直截取到最后

json提取函数

  • string型字符串，存储为json格式：get_json_object(string json_string,'\(字段名')（'\)字段名）为string path

在json字符串中提取出想要的字段和值

--如在字段extra1(string)：{"systemtype": "android", "education": "doctor", "marriage_status": "1", "phonebrand": "VIVO"} ；提取出extra1和extra2中的手机品牌:

select get_json_object(extra1,'$.phonebrand') as phone_brand,count(distinct user_id)
from user_info
group by get_json_object(extra1,'$.phonebrand');
  • map<string,string>,存储为json格式：**extea2['字段名']

替换函数

  • regexp_replace（string,'要被替代的','替代的'）
regexp_replace(substr(create_time,1,10),'-','')
regexp_replace(to_date(create_time),'-','')

替换NULL值函数

  • if null():IFNULL函数接受两个参数，如果不为NULL则返回第一个参数，否则返回第二个参数
  • coalesce():COALESCE函数需要许多参数，并返回第一个非NULL参数。如果所有参数都为NULL，则COALESCE函数返回NULL,如果参数的数量为2，则与if null相同

聚合统计函数

sum()、count()等（sum(),avg(),max(),count(),min()这五个聚合函数之间不可以互相嵌套。 如不允许嵌套组合avg(count(*))；）

其他函数

ABS()

求绝对值

日期格式化

常用表现形式

yyyy-MM-dd HH:mm:ss

大写

大写的H

  • 为了区分12小时制与24小时制
  • 小写的h是12小时制，大写的H是24小时制

大写的M

  • M大写是为了区分“月”与“分”

数量

mm/m/ss/s

  • 前导零的问题
  • m,s表示非零开始，mm,ss表示从零开始
  • mm显示为02，m显示为2

HH/H/hh/h

  • 显示为：01/1/01 AM/1 AM

dddd/ddd/dd/d

  • 中文显示为：星期三/周三(有的语言显示为“三”)/01/1
  • 英文显示为：dddd是全称，ddd是简称；dddd/ddd表示星期几，dd/d表示几号
  • 有的（Hive/presto sql）是会直接表示为前导零的问题：dddd表示为0011（11号）

MMMM/MMM/MM/M

  • 中文显示： 一月/一月/01/1
  • 英文显示：MMMM显示全称，MMM显示缩写

yyyy/yyy/yy/y

  • 显示为：2014/2014/14/4

应用技巧

where 1=0

where 1=0; 这个条件始终为false，结果不会返回任何数据，只有表结构，可用于快速建表或者观察表结构

连接字段类型

在连接表时，需要注意表的连接条件的字段类型是否是一致的，如果出现不一致的情况，需要进行转换

如：表1和表2的连接条件为user_id，如果表1的user_id是bigint型，表2的user_id是string型，如果直接连接，则会导致数据出现的膨胀的情况，则需要对数据进行转换，如cast(user_id as bigint),，将string类型的user_id转换成整型的user_id。

中位数

percentile_approx(字段名,0.5)

优化

原子化操作

尽量原子化操作，尽量避免一个SQL包含复杂逻辑,可以使用中间表来完成复杂的逻辑

order by优化【3】

order by会引发全局排序，使用distribute和sort进行分组排序效率更快（sort by 替换order by，配合distribute by一起使用）

select * from store cluster by merid;

select * 
from store 
distribute by merid 
sort by merid asc

这里需要注意的是distribute by必须要写在sort by之前

distinct优化【5】

能使用group by代替distinct就不要使用distinct,group by 代替distinct去重数据，会有多个mapreduce执行，大数据量情况下比较好用。

join优化【4】

  • 小表前置，因为hive在解析sql的时候会把第一个表放进内存
  • join前过滤掉不需要的数据(尽量尽早地过滤数据，减少每个阶段的数据量)
  • 适当场合使用LEFT SEMI JOIN【6】
select a.key,a.value
from a
where a.key in (select b.key from b)

--或
select a.key,a.value
from a
join b
on a.key=b.key

---可以改写成
select a.key,a.value
from a
left semi join b
on a.key=b.key

最后select的结果只会出现左表，右表的非匹配字段不会出现(join右边的表只能出现在join条件中)

多表连接查询

  • 在连接查询时，先去重，再做表连接，先连接再去重执行效率会低。
  • 在表的数据量级很大的时候先分别查询出结果再进行连接效率会快些。
  • 多表关联时尽量key相同，会当成同一个mr任务执行

LATERAL VIEW

EXPLODE()

将一行数据转换成列数据，可以用于array和map类型的数据。

用于array

select explode(arraycol) as newcol from tablename;
  • explode()：函数中的参数传入的是arrary数据类型的列名。
  • newcol：是给转换成的列命名一个新的名字，用于代表转换之后的列名。
  • tablename：原表名

用于map

select explode(mapcol) as (keyname,valuename) from tablename;
  • explode()：函数中的参数传入的是map数据类型的列名。
  • 由于map是kay-value结构的，所以它在转换的时候会转换成两列，一列是kay转换而成的，一列是value转换而成的。
  • keyname：表示key转换成的列名称，用于代表key转换之后的列名。
  • valuename：表示value转换成的列名称，用于代表value转换之后的列名称。

注意：这两个值需要在as之后用括号括起来然后以逗号分隔。

局限性

  • 不能关联原有的表中的其他字段。
  • 不能与group by、cluster by、distribute by、sort by联用。
  • 不能进行UDTF嵌套。
  • 不允许选择其他表达式。

UDTF

UDTF 是 User-Defined Table-Generating Functions的缩写，即用户定义的表生成函数。UDTF用来解决从原始表中的一行生成多行数据的问题。典型的UDTF有EXPLODE函数，它能将array或者map展开。

  • 例：考虑以下名为 pageAds 的原始表，它有两列：pageid（网页名称）和 adid_list（网页上显示的广告数组）：
名称 类型
pageid STRING
adid_list Array

假设其中有以下两行：

pageid adid_list
conotact_page [3,4,5]
front_page [1,2,3]

使用EXPLODE函数可以展开 adid_list 这一列，也就是说，SQL 语句的结果如下所示：

SELECT
  EXPLODE(adid_list)
FROM
  pageAds;
-- possible result:
-- 1
-- 2
-- 3
-- 3
-- 4
-- 5

不过，UDTF有一个限制，它出现在SELECT子句中的时候，不能与其它列共同出现。也就是说下列 SQL 语句会报语法错误：

SELECT
  pageid, EXPLODE(adid_list) AS adid
FROM
  pageAds;

为了解决这个问题，我们就需要引入 LATERAL VIEW

LaTERAL VIEW

ateral view是Hive中提供给UDTF的结合，它可以解决UDTF不能添加额外的select列的问题。lateral view其实就是用来和类似explode这种UDTF函数联用的，lateral view会将UDTF生成的结果放到一个虚拟表中，然后这个虚拟表会和输入行进行join来达到连接UDTF外的select字段的目的。

格式一

LATERAL VIEW udtf(expression) tableAlias AS columnAlias (',' columnAlias)*
  • lateral view在UDTF前使用，表示连接UDTF所分裂的字段。
  • UDTF(expression)：使用的UDTF函数，例如explode()。
  • tableAlias：表示UDTF函数转换的虚拟表的名称。
  • columnAlias：表示虚拟表的虚拟字段名称，如果分裂之后有一个列，则写一个即可；如果分裂之后有多个列，按照列的顺序在括号中声明所有虚拟列名，以逗号隔开。

格式二

from basetable (lateral view)*
  • 在from子句中使用，一般和格式一搭配使用，这个格式只是说明了lateral view的使用位置。
  • from子句后面也可以跟多个lateral view语句，使用空格间隔就可以了。

格式三

from basetable (lateral view outer)*

它比格式二只是多了一个outer，这个outer的作用是在UDTF转换列的时候将其中的空也给展示出来，UDTF默认是忽略输出空的，加上outer之后，会将空也输出，显示为NULL。这个功能是在Hive0.12是开始支持的。

实例

继续以上述名为 pageAds 的原始表为例，为了同时 SELECT 出 pageid 和 adid，我们需要使用 LATERAL VIEW：

SELECT
  pageid, adid
FROM
  pageAds
  LATERAL VIEW
    EXPLODE(adid_list) tmp AS adid;

-- possible result:
-- front_page	1
-- front_page	2
-- front_page	3
-- contact_page	3
-- contact_page	4
-- contact_page	5

参考

推荐阅读