hivetrunc（hivetruncate数据恢复）

本篇文章给大家谈谈hivetrunc，以及hivetruncate数据恢复对应的知识点，希望对各位有所帮助，不要忘了收藏本站喔。

本文目录一览：

• 1、hive 英文月份转数字
• 2、hive建表及导入数据
• 3、hive怎么实现trunc功能
• 4、impala中date用什么类型
• 5、Hive sql及窗口函数

hive 英文月份转数字

日期变换：

(1)dt转日期

to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd')))

(2)日期转dt

regexp_replace('${date}','-','')

(3)dt转当兄信陆月1号日期

to_date(from_unixtime(unix_timestamp(concat(substr('${dt}',1,6),'01'),'yyyyMMdd')))

trunc(to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd'))),'MM')

-- 下月1号坦缺日期

trunc(add_months(to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd'))),1),'MM')

(4)dt转当周星期一日期

next_day(date_add(to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd'))), -7), '羡顷Mo')

date_sub(next_day(to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd'))),'MO'),7)

-- 下周星期一日期

next_day(to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd'))),'MO')

(5)dt前六天日期(dt为星期天时得到的是本周周一的日期)

date_add(to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd'))), -6)

(5)dt转当季第一天日期

if(length(floor(substr('${dt}',5,2)/3.1)*3+1)=1,concat(substr('${dt}',1,4),'-0',floor(substr('${dt}',5,2)/3.1)*3+1,'-01'),concat(substr('${dt}',1,4),'-',floor(substr('${dt}',5,2)/3.1)*3+1,'-01'))

(6)dt转半年第一天日期

if(length(floor(substr('${dt}',5,2)/6.1)*6+1)=1,concat(substr('${dt}',1,4),'-0',floor(substr('${dt}',5,2)/6.1)*6+1,'-01'),concat(substr('${dt}',1,4),'-',floor(substr('${dt}',5,2)/6.1)*6+1,'-01'))

(7)dt转当年1号日期

concat(substr('${dt}',1,4),'-01-01')(8)在同时有日周月粒度时要注意数据的时间范围，有时每月的第一个自然周会跨月，比如2019年3月的第一周的日期是20190225-20190303where agent_business_date between date_add_day('${dt}',-31) and to_date(from_unixtime(unix_timestamp('${dt}','yyyyMMdd')))where dt between regexp_replace(date_add_day('${dt}',-31),'-','') and '${dt}'

------------------------------------------------------------------------------------------

-- 日期维度表表结构edw_public.dim_esf_edw_pub_date

------------------------------------------------------------------------------------------

col_name data_type comment

------------------------------------------------------------------------

calendar_date string 日期，格式为"YYYY-MM-DD"

week_english_name string 星期英文名

week_chinese_name string 星期中文名

day_of_week_number int 所属一周当中的第几天

calendar_month_code string 日期所属月份，格式为"YYYY-MM"

calendar_month_number int 所属月份数字

month_english_name string 月份英文名

month_chinese_name string 月份中文名

day_of_month_number int 所属月份当中的第几天

calendar_quater_code string 日期所属季度，格式为"YYYY-QT"

calendar_quater_number int 所属季度数字

day_of_quater_number int 所属季度当中的第几天

calendar_half_year_code string 日期所属半年，格式为"YYYY-HY"

calendar_half_year_number int 所属半年数字，1为上半年，2为下半年

calendar_year_code string 日期所属年份，格式为"YYYY"

day_of_year_number int 所属年份当中的第几天

work_day_flag string 工作日标志: Y - 是/ N - 否

holiday_flag string 节假日标志: Y - 是/ N - 否

-- 日期维度表的使用

-- 当天日期

SELECT

calendar_date

FROM

edw_public.dim_esf_edw_pub_date

WHERE

calendar_date = regexp_replace('${dt}','(\\d{4})(\\d{2})(\\d{2})','$1-$2-$3')

-- Finereport中日周月季半年年 各周期末日期的算法

select

${if(粒度 == 1," case when date(max(calendar_date))=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(max(calendar_date)) end as period_end_date","")}

${if(粒度 == 2," distinct case when day_of_week_number = 1 and date_add('day',6,date(calendar_date)) =date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) when day_of_week_number = 7 and date(calendar_date) =date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) when day_of_week_number = 1 then date_add('day',6,date(calendar_date)) when day_of_week_number = 7 then date(calendar_date) else date(calendar_date) end as period_end_date ","")}

${if(粒度 == 3," case when date(max(calendar_date))=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(max(calendar_date)) end as period_end_date ","")}

${if(粒度 == 4," case when date(max(calendar_date))=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(max(calendar_date)) end as period_end_date ","")}

${if(粒度 == 5," case when date(max(calendar_date))=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(max(calendar_date)) end as period_end_date ","")}

${if(粒度 == 6," case when date(max(calendar_date))=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(max(calendar_date)) end as period_end_date ","")}

from

edw_public.dim_esf_edw_pub_date

where calendar_date = '${开始时间}' and calendar_date = '${结束时间}'

${if(粒度 == 1," group by calendar_date ","")}

${if(粒度 == 2," and day_of_week_number in (1,7) ","")}

${if(粒度 == 3," group by calendar_month_code ","")}

${if(粒度 == 4," group by calendar_quater_code ","")}

${if(粒度 == 5," group by calendar_year_code ","")}

${if(粒度 == 6," group by calendar_half_year_code ","")}

-- Finereport中日周月季半年年 各周期期初期末日期的算法(这种计算方法当前日期是20190330，输入的日期范围是2019-03-01至2091-03-28则输出的月日期范围是2019-03-29)

select

${if(粒度 == 1,"date(calendar_date) as period_start_date, date(calendar_date) as period_end_date ","")}

${if(粒度 == 2,"case when day_of_week_number = 1 then date(calendar_date) when day_of_week_number = 7 then date_add('day',-6, date(calendar_date)) end as period_start_date, case when day_of_week_number = 1 and date_add('day',6, date(calendar_date)) =date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) when day_of_week_number = 7 and date(calendar_date)=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) when day_of_week_number = 1 then date_add('day',6, date(calendar_date)) when day_of_week_number = 7 then date(calendar_date) end as period_end_date ","")}

${if(粒度 == 3,"date(calendar_date) as period_start_date, case when date_add('day',-day(date(calendar_date)),date_add('month',1,(date(calendar_date))))=date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date_add('day',-day(date(calendar_date)),date_add('month',1,(date(calendar_date)))) end as period_end_date ","")}

${if(粒度 == 4,"calendar_date as period_start_date,date_add('day',-1,date_add('month',1,date(substr(calendar_date,1,4)||'-'||cast(cast(floor(cast(substr(calendar_date,6,2) as int)/3.1)*3+3 as int) as varchar)||'-01'))) as period_end_date ","")}

${if(粒度 == 5,"date(concat(substr(calendar_date,1,4),'-01','-01')) as period_start_date,case when date(concat(substr(calendar_date,1,4),'-12','-31'))= date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(concat(substr(calendar_date,1,4),'-12','-31')) end as period_end_date","")}

${if(粒度 == 6,"date(min(calendar_date)) as period_start_date,case when date(max(calendar_date))= date(date_add('day',-1,current_date)) then date(date_add('day',-1,current_date)) else date(max(calendar_date)) end as period_end_date","")}

from

edw_public.dim_esf_edw_pub_date

where calendar_date = '${开始时间}' and calendar_date = '${结束时间}'

${if(粒度 == 1," and 1 = 1 ","")}

${if(粒度 == 2," and day_of_week_number in (1,7) ","")}

${if(粒度 == 3," and day_of_month_number = 1","")}

${if(粒度 == 4," and day_of_quater_number = 1","")}

${if(粒度 == 5," and day_of_year_number = 1","")}

${if(粒度 == 6," group by calendar_half_year_code ","")}

------------------------------------------------------------------------------------------------

-- 根据输入的时间范围计算期末日期

------------------------------------------------------------------------------------------------

select t1.*

from

-- 日周月季年半年不同粒度的统计数据各存为了一张表

edw_reports.adm_xf_edw_house_sub_project_report_00${dtype}ly_di t1--日报

join

(

-- 日

SELECT

calendar_date

FROM

edw_public.dim_esf_edw_pub_date

WHERE

calendar_date BETWEEN '${bdt}' AND '${edt}'

AND '${dtype}' = '1_dai'

UNION

-- 月

SELECT

MAX(calendar_date) AS calendar_date

FROM

edw_public.dim_esf_edw_pub_date

WHERE

calendar_date BETWEEN '${bdt}' AND '${edt}'

AND '${dtype}' = '2_dai'

GROUP BY

calendar_month_number

UNION

-- 周

SELECT

calendar_date

FROM

edw_public.dim_esf_edw_pub_date

WHERE

calendar_date BETWEEN '${bdt}' AND '${edt}'

AND day_of_week_number = 7

AND '${dtype}' = '3_dai'

UNION

-- 季

SELECT

MAX(calendar_date) AS calendar_date

FROM

edw_public.dim_esf_edw_pub_date

WHERE

calendar_date BETWEEN '${bdt}' AND '${edt}'

AND '${dtype}' = '4_dai'

GROUP BY

calendar_quater_code

UNION

-- 年

SELECT

MAX(calendar_date) AS calendar_date

FROM

edw_public.dim_esf_edw_pub_date

WHERE

calendar_date BETWEEN '${bdt}' AND '${edt}'

AND '${dtype}' = '5_dai'

GROUP BY

calendar_year_code

UNION

-- 半年

SELECT

MAX(calendar_date) AS calendar_date

FROM

edw_public.dim_esf_edw_pub_date

WHERE

calendar_date BETWEEN '${bdt}' AND '${edt}'

AND '${dtype}' = '6_dai'

GROUP BY

calendar_half_year_code

UNION

SELECT

MAX(calendar_date) AS calendar_date

FROM

edw_public.dim_esf_edw_pub_date

WHERE

calendar_date BETWEEN '${bdt}' AND '${edt}'

ORDER BY

calendar_date

) t2

on t1.statistic_date = t2.calendar_date

where

statistic_date between '${bdt}' and '${edt}'

${if(len(tenant_name) == 0,"","and house_sub_project_organization_short_name = '" + tenant_name + "'")}

${if(len(status) == 0,"","and house_sub_project_cooperation_status_code = " + status)}

${if(len(tenant_type) == 0,"","and house_sub_project_organization_business_type_code= " + tenant_type)}

${if(len(project_type) == 0,"","and house_sub_project_cooperation_type_code= " + project_type)}

order by statistic_date

[img]

hive建表及导入数据

你导入的时候旁塌不用指定全路径吗？

还有你文件的分隔符是什么，你建表的悉启毁时候没有指定，睁备它默认应该就是'\x01'

hive怎么实现trunc功能

show create table 表名; 如果是这个表有分区的话，可以看到显示的内容里有渣嫌闭partition，partition里面跟的如裂就是分区者掘列名。

impala中date用什么类型

5 Function

指数据库举态内置的function，不讨论UDF。另外，操作符都不比较了，区别不大。

5.1 数学函数

功能 Oracle Hive Impala

ABS 绝对值，有 有 有

SIN/SINH/ASIN/COS/COSH/ACOS/TAN/TANH/ATAN/ATAN2

三角函数

其中ATAN2接受两个参数（二维平面中的坐标）

没有SINH/COSH/TANH/ATAN2 同hive

BITAND 按位与，有

CEIL 天花板值，有 有，还有个别名CEILING 有，同hive

EXP e的多少次，有 有，还有个函数E()返回e 有，同hive

FLOOR 地板值，有 有 有

LN 以e为底的log，有 有 有

LOG 以某个double为底的log，有 有，还有两个特殊底的log：LOG2和LOG10 有，同hive

MOD Oracle的MOD的计算方式为MOD(n2,n1)=n2 - n1 * FLOOR(n2/n1)，这与经典取模还不同，比如Oracle的MOD(-11,4)=-3，但经典取模等于1 在hive里取模用PMOD，返回值一定是个正数，比如PMOD(-11,4)=1。但这与经典取模还是不同，比如PMOD(-11,-4)=1，但经典取模等于-3 Impala的PMOD和hive的PMOD相同，另外Impala还有个FMOD和Oracle的MOD相同

POWER 求幂，有 有，还有个别名POW 有，同hive

REMAINDER 取余，REMAINDER(n2,n1)=n2 - n1 * FLOOR(n2/n1)

ROUND 舍入，支持1个参数和2个参数两种版本

有

另外还有一个BROUND，使用HALF_EVEN舍入模式，见官方手册

有，同Oracle

SIGN 符号函数，有 有 有

SQRT 开方，有 有 有

TRUNC 截取数值的小数点后多少位（如果是负值则往前推）

DEGREES/RADIANS 角度/弧度互转 同hive

POSITIVE/NEGATIVE 相当于在数值前加+/-号（因此没什么卵用） 同hive

PI 返回pi值 同hive

FACTORIAL 阶乘，1.2.0以后才有

CBRT 求立方根，1.2.0以后才有

SHIFTLEFT/SHIFTRIGHT/SHIFTRIGHTUNSIGNED 按位左移/右移/无符号右移

GREATEST/LEAST 返回一串值中的最大/最小值，这串值的类型可正段源以是任意，只要可比较大小（所以其实不是数学函数而是通用比较） 有，1.1.0以后 有

5.2 字符（串）函数

功能 Oracle Hive Impala

ASCII 输入必须char，返回该字符的ascii数值 输入String，返回该String第一个字符的ascii数值 同hive

BASE64 / UNBASE64 将二进制值转为base64的String（UN则是反向）

CONCAT

CONCAT(char1, char2)

char或燃逗char2均可为CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB, or NCLOB之一

concat(string|binary A, string|binary B)

可以有多个参数

另外提供CONCAT_WS用于指定特殊分隔符的连接

同hive（除了不支持binary类型），且也有CONCAT_WS

DECODE/ENCODE

string decode(binary bin, string charset)

binary encode(string src, string charset)

编码和解码用的，用于支持hive特有的binary类型

（其实oracle和impala也有DECODE，但作用完全不同，见条件函数中的DECODE）

FIND_IN_SET

find_in_set(string str, string strList)

strList是用’,’分割的一组string，该函数将寻找strList中第一个精确匹配的str

同hive

FORMAT_NUMBER 将数字格式化为string

GET_JSON_OBJECT 抽取JSON对象，不常用

IN_FILE

in_file(string str, string filename)

检测str是否为filename对应文件中的某行，不常用

INITCAP 将每个单词（以空白分隔）转换为首字母大写其余小写的形式 同oracle，1.1.0开始有 同oracle

INSTR

{ INSTR

| INSTRB

| INSTRC

| INSTR2

| INSTR4

}

(string , substring [, position [, occurrence ] ])

搜索子串，不同数据类型调用名不同，最多可带四个参数，其中第三个是开始位置，第四个是出现的第几次

instr(string str, string substr)

只接受两个参数

同hive

LENGTH

{ LENGTH

| LENGTHB

| LENGTHC

| LENGTH2

| LENGTH4

}

(char)

串长，不同数据类型调用名不同

有（仅LENGTH） 同hive

LEVENSHTEIN 返回两个串的Levenshtein距离（编辑距离）1.2.0后才有

LOCATE 特殊情况的INSTR，可以指定匹配的开始位置。Oracle因为本来INSTR就支持所以并不需要该函数 同hive

LOWER 转小写 有，且有一个别名叫LCASE 同hive

LPAD / RPAD

LPAD(expr1, n [, expr2 ])

在expr1之前用expr2填充n个字符，如expr2省略默认用n个单空格填充。RPAD类似只是在右边

有，但expr2不能省略 同hive

LTRIM / RTRIM

LTRIM(char [, set ])

去掉char左侧包含在set中的字符，如省略set，则只去除空格符。RTRIM类似只是在右边

有，但没有set参数，即只能去除空格符 同hive

Hive sql及窗口函数

hive函数：

1、根据指定条件返回结果：case when then else end as

2、基本类型转换：CAST（）

3、nvl：处理空字段：三个str时，是否为空可以指定返回不同的值

4、sql通配符：

5、count(1)与COUNT(*)：返回行数

如果表没有主键，那么count（1）比count（*）快；

如果有主键，那么count（主键，联合主键）比count（*）快；

count(1)跟count(主键)一样，只扫描主键。count(*)跟count(非主键)一样，扫描整个表。明显前者更快一些。

性能问题：

1.任何情况下SELECT COUNT(*) FROM tablename是最优选择,(指没有where的情况）；

2.尽量减少SELECT COUNT(*) FROM tablename WHERE COL = ‘value’ 这种查询；

3.杜绝SELECT COUNT(COL) FROM tablename WHERE COL2 = ‘value’ 的出现。

count(expression)：查询 is_reply=0 的数量： SELECT COUNT(IF(is_reply=0,1,NULL)) count FROM t_iov_help_feedback;

6、distinct与group by

distinct去重所有distinct之后所有的字段，如果有一个字段值不一致就不作为一条

group by是根据某一字段分组，然后查询出该条数据的所需字段，可以搭配 where max(time)或者Row_Number函数使用，求出最大的一条数据

7、使用with 临时表名 as() 的形式,简单的临时表直接嵌套进枝段sql中，复杂的和需要复用的表写到临猛行誉时表中，关联的时候先找到关联字段，过滤条件最好在临时表中先过滤后关联

处理json的函数：

split(json_array_string(schools), '\\|\\|') AS schools

get_json_object(school, '$.id') AS school_id,

字符串函数：

1、instr（’源字符串’ , ‘目标字符串’ ,’开始位置’,’第几次出现’）

instr(sourceString,destString,start,appearPosition)

1.sourceString代表源字符串； destString代表要从源字符串中查找的子串；

2.start代表查找的开始位置，这个参数可选的，默认为1；

3.appearPosition代表想从源字符中查找出第几次出现的destString，这个参数也是可选的， 默认为1

4.如果start的值为负数，则代表从右往左进行查找，但是位置数据仍然从左向右计带枯算。

5.返回值为：查找到的字符串的位置。如果没有查找到，返回0。

最简单例子： 在abcd中查找a的位置，从第一个字母开始查，查找第一次出现时的位置

select instr(‘abcd’,’a’,1,1) from dual; —1

应用于模糊查询：instr(字段名/列名, ‘查找字段’)

select code,name,dept,occupation from staff where instr(code, ‘001’) 0;

等同于 select code, name, dept, occupation from staff where code like ‘%001%’ ;

应用于判断包含关系：

select ccn,mas_loc from mas_loc where instr(‘FH,FHH,FHM’,ccn)0;

等同于 select ccn,mas_loc from mas_loc where ccn in (‘FH’,’FHH’,’FHM’);

2、substr（string A，int start，int len）和 substring（string A，int start，int len），用法一样

substr(time,1,8) 表示将time从第1位开始截取，截取的长度为8位

第一种用法：

substr（string A，int start）和 substring（string A，int start），用法一样

功效：返回字符串A从下标start位置到结尾的字符串

第二种用法：

substr（string A，int start，int len）和 substring（string A，int start，int len），用法一样

功效：返回字符串A从下标start位置开始，长度为len的字符串

3、get_json_object(form_data,'$.学生姓名') as student_name

json_tuple 函数的作用：用来解析json字符串中的多个字段

4、split(full_name, '\\.') [5] AS zq;  取的是数组里的第六个

日期(时间)函数：

1、to_date(event_time) 返回日期部分

2、date_sub：返回当前日期的相对时间

当前日期：select curdate() 

当前日期前一天：select  date_sub(curdate(),interval 1 day)

当前日期后一天：select date_sub(curdate(),interval -1 day)

date_sub(from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss'), 14)  将现在的时间总秒数转为标准格式时间，返回14天之前的时间

时间戳日期：

from_unixtime(unix_timestamp(), 'yyyy-MM-dd HH:mm:ss') 将现在的时间总秒数转为标准格式时间

from_unixtime(get_json_object(get_json_object(form_data,'$.挽单时间'),'$.$date')/1000) as retain_time

unix_timestamp('2019-08-15 16:40:00','yyyy-MM-dd HH:mm:ss')  --1565858400

日期时间戳：unix_timestamp()

date_format：yyyy-MM-dd HH:mm:ss 时间转格式化时间

select date_format('2019-10-07 13:24:20', 'yyyyMMdd000000')-- 20191007000000select date_format('2019-10-07', 'yyyyMMdd000000')-- 20191007000000

1.日期比较函数: datediff语法: datediff(string enddate,string startdate) 

返回值: int 

说明: 返回结束日期减去开始日期的天数。 

举例：  hive select datediff('2016-12-30','2016-12-29');  1

2.日期增加函数: date_add语法: date_add(string startdate, intdays) 

返回值: string 

说明: 返回开始日期startdate增加days天后的日期。 

举例：  hiveselect date_add('2016-12-29',10);  2017-01-08

3.日期减少函数: date_sub语法: date_sub (string startdate,int days) 

返回值: string 

说明: 返回开始日期startdate减少days天后的日期。 

举例：  hiveselect date_sub('2016-12-29',10);  2016-12-19

4.查询近30天的数据

select * from table where datediff(current_timestamp,create_time)=30；

create_time 为table里的字段，current_timestamp 返回当前时间 2018-06-01 11:00:00

3、trunc()函数的用法：当前日期的各种第一天,或者对数字进行不四舍五入的截取

日期：

1.select trunc(sysdate) from dual  --2011-3-18  今天的日期为2011-3-18

2.select trunc(sysdate, 'mm')   from   dual  --2011-3-1    返回当月第一天.

上月1号    trunc(add_months(current_date(),-1),'MM')

3.select trunc(sysdate,'yy') from dual  --2011-1-1       返回当年第一天

4.select trunc(sysdate,'dd') from dual  --2011-3-18    返回当前年月日

5.select trunc(sysdate,'yyyy') from dual  --2011-1-1   返回当年第一天

6.select trunc(sysdate,'d') from dual  --2011-3-13 (星期天)返回当前星期的第一天

7.select trunc(sysdate, 'hh') from dual   --2011-3-18 14:00:00   当前时间为14:41  

8.select trunc(sysdate, 'mi') from dual  --2011-3-18 14:41:00   TRUNC()函数没有秒的精确

数字：TRUNC（number,num_digits） Number 需要截尾取整的数字。Num_digits 的默认值为 0。TRUNC()函数截取时不进行四舍五入

11.select trunc(123.458,1) from dual --123.4

12.select trunc(123.458,-1) from dual --120

4、round()：四舍五入：

select round(1.455, 2)  #结果是：1.46，即四舍五入到十分位，也就是保留两位小数

select round(1.5)  #默认四舍五入到个位，结果是：2

select round(255, -1)  #结果是：260，即四舍五入到十位，此时个位是5会进位

floor()：地板数

ceil()天花板数

5、

6.日期转年函数: year语法:   year(string date) 

返回值: int

说明: 返回日期中的年。

举例：

hive   select year('2011-12-08 10:03:01') from dual;

2011

hive   select year('2012-12-08') fromdual;

2012

7.日期转月函数: month语法: month   (string date) 

返回值: int

说明: 返回日期中的月份。

举例：

hive   select month('2011-12-08 10:03:01') from dual;

12

hive   select month('2011-08-08') fromdual;

8

8.日期转天函数: day语法: day   (string date) 

返回值: int

说明: 返回日期中的天。

举例：

hive   select day('2011-12-08 10:03:01') from dual;

8

hive   select day('2011-12-24') fromdual;

24

9.日期转小时函数: hour语法: hour   (string date) 

返回值: int

说明: 返回日期中的小时。

举例：

hive   select hour('2011-12-08 10:03:01') from dual;

10

10.日期转分钟函数: minute语法: minute   (string date) 

返回值: int

说明: 返回日期中的分钟。

举例：

hive   select minute('2011-12-08 10:03:01') from dual;

3

11.日期转秒函数: second语法: second   (string date) 

返回值: int

说明: 返回日期中的秒。

举例：

hive   select second('2011-12-08 10:03:01') from dual;

1

12.日期转周函数: weekofyear语法:   weekofyear (string date) 

返回值: int

说明: 返回日期在当前的周数。

举例：

hive   select weekofyear('2011-12-08 10:03:01') from dual;

49

查看hive表在hdfs中的位置：show create table 表名;

在hive中hive2hive，hive2hdfs：

HDFS、本地、hive ----- Hive：使用 insert into | overwrite、loaddata local inpath "" into table student;

Hive ---- Hdfs、本地：使用：insert overwrite | local

网站访问量统计：

uv：每用户访问次数

ip：每ip(可能很多人)访问次数

PV:是指页面的浏览次数

VV:是指你访问网站的次数

sql：

基本函数：

count、max、min、sum、avg、like、rlike（'2%'、'_2%'、%2%'、'[2]'）（java正则）

and、or、not、in   

where、group by、having、{ join on 、full join}  、order by（desc降序）

sort by需要与distribut by集合结合使用：

hive (default) set mapreduce.job.reduces=3;  //先设置reduce的数量 

insert overwrite local directory '/opt/module/datas/distribute-by'

row format delimited fields terminated by '\t'

先按照部门编号分区，再按照员工编号降序排序。

select * from emp distribute by deptno sort by empno desc;

外部表  create external table if not exists dept

分区表：create table dept_partition ( deptno int, dname string, loc string )  partitioned by ( month string )

load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201809'); 

 alter table dept_partition add/drop partition(month='201805') ,partition(month='201804');

多分区联合查询：union

select * from dept_partition2 where month='201809' and day='10';

show partitions dept_partition;

desc formatted dept_partition;

二级分区表：create table dept_partition2 ( deptno int, dname string, loc string ) partitioned by (month string, day string) row format delimited fields terminated by '\t';

分桶抽样查询：分区针对的是数据的存储路径；分桶针对的是数据文件

create table stu_buck(id int, name string) clustered by(id) into 4 bucketsrow format delimited fields terminated by '\t';

设置开启分桶与reduce为1：

set hive.enforce.bucketing=true;

set mapreduce.job.reduces=-1;

分桶抽样：select * from stu_bucktablesample(bucket x out of y on id);

抽取，桶数/y，x是从哪个桶开始抽取，y越大 抽样数越少，y与抽样数成反比，x必须小于y

给空字段赋值：

如果员工的comm为NULL，则用-1代替或用其他字段代替  ：select nvl(comm,-1) from emp;

case when:如何符合记为1，用于统计、分组统计

select dept_id, sum(case sex when '男' then 1 else 0 end) man , sum(case sex when '女' then 1 else 0 end) woman from emp_sex group by dept_id;

用于组合归类汇总(行转列)：UDAF：多转一

concat：拼接查询结果

collect_set(col)：去重汇总，产生array类型字段，类似于distinct

select t.base, concat_ws('|',collect_set(t.name))   from (select concat_ws(',',xingzuo,blood_type) base,name  from person_info) t group by t.base;

解释：先第一次查询得到一张没有按照（星座血型）分组的表，然后分组，使用collect_set将名字组合成数组，然后使用concat将数组变成字符串

用于拆分数据：(列转行)：UDTF：一转多

explode(col)：将hive一列中复杂的array或者map结构拆分成多行。

lateral view  侧面显示：用于和UDTF一对多函数搭配使用

用法：lateral view udtf(expression) tablealias as cate

cate：炸开之后的列别名

temptable ：临时表表名

解释：用于和split, explode等UDTF一起使用，它能够将一列数据拆成多行数据，在此基础上可以对拆分后的数据进行聚合。

开窗函数：

Row_Number,Rank，Dense_Rank  over：针对统计查询使用

Row_Number：返回从1开始的序列

Rank:生成分组中的排名序号，会在名词s中留下空位。3 3 5

dense_rank：生成分组中的排名序号，不会在名词中留下空位。3 3 4

over：主要是分组排序，搭配窗口函数使用

结果：

SUM、AVG、MIN、MAX、count

preceding：往前

following：往后

current row：当前行

unbounded：unbounded preceding 从前面的起点， unbounded following：到后面的终点

sum：直接使用sum是总的求和，结合over使用可统计至每一行的结果、总的结果、当前行+之前多少行/之后多少行、当前行到往后所有行的求和。

over(rowsbetween 3/current rowprecedingandunboundedfollowing )  当前行到往后所有行的求和

ntile：分片，结合over使用，可以给数据分片，返回分片号

使用场景：统计出排名前百分之或n分之一的数据。

lead,lag,FIRST_VALUE,LAST_VALUE

lag与lead函数可以返回上下行的数据

lead(col,n,dafault) 用于统计窗口内往下第n行值

第一个参数为列名，第二个参数为往下第n行（可选，默认为1），第三个参数为默认值（当往下第n行为NULL时候，取默认值，如不指定，则为NULL）

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

第一个参数为列名，第二个参数为往上第n行（可选，默认为1），第三个参数为默认值（当往上第n行为NULL时候，取默认值，如不指定，则为NULL）

使用场景：通常用于统计某用户在某个网页上的停留时间

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

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

范围内求和:

cume_dist，percent_rank

–CUME_DIST :小于等于当前值的 行数 / 分组内总行数

–比如，统计小于等于当前薪水的人数，占总人数的比例

percent_rank:分组内当前行的RANK值-1/分组内总行数-1

总结：

在Spark中使用spark sql与hql一致，也可以直接使用sparkAPI实现。

HiveSql窗口函数主要应用于求TopN，分组排序TopN、TopN求和，前多少名前百分之几。

与Flink窗口函数不同。

Flink中的窗口是用于将无线数据流切分为有限块处理的手段。

window分类：

CountWindow：按照指定的数据条数生成一个 Window，与时间无关。

TimeWindow：按照时间生成 Window。

1. 滚动窗口（Tumbling Windows）：时间对齐，窗口长度固定，不重叠：：常用于时间段内的聚合计算

2.滑动窗口（Sliding Windows）：时间对齐，窗口长度固定，可以有重叠：：适用于一段时间内的统计（某接口最近 5min 的失败率来报警）

3. 会话窗口（Session Windows）无时间对齐，无长度，不重叠：：设置session间隔，超过时间间隔则窗口关闭。

关于hivetrunc和hivetruncate数据恢复的介绍到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站。