您的位置 首页 > 数码极客

hive中如何转换时间格式 hive 字符串转时间格式!

一、数据库

1 创建库语句

CREATE DATABASE [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, ...)];

2查询数据库

2.1 显示数据库

show databases;

2.2 过滤显示查询的数据库

show databases like 'd*';

2.3 显示数据库信息

desc database ods;

2.4 显示数据库详细信息,extended

desc database extended ods;

2.5 切换数据库详细信息,use

use dwd;

3 修改数据库

3.1 修改数据库属性,Alter

ALTER (DATABASE|SCHEMA) database_name SET DBPROPERTIES (property_name=property_value, …);

3.2 删除数据库,drop

3.2.1 删除空的数据库

drop database dwd;

3.2.2删除非空的数据库,使用cascade强制删除。

drop database dwd cascade;

二、表

1 建表语句

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name [(col_name data_type [COMMENT col_comment], ...)] [COMMENT table_comment] [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)] [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS] [ROW FORMAT row_format] [STORED AS file_format] [LOCATION hdfs_path] [TBLPROPERTIES (property_name=property_value, ...)] [AS SELECT_statement]

(1)CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在,则抛出异常;用户可以用 IF NOT EXISTS 选项来忽略这个异常。

(2)EXTERNAL关键字可以让用户创建一个外部表,在建表的同时可以指定一个指向实际数据的路径(LOCATION),在删除表的时候,内部表的元数据和数据会被一起删除,而外部表只删除元数据,不删除数据。

(3)COMMENT:为表和列添加注释。

(4)PARTITIONED BY创建分区表

(5)CLUSTERED BY创建分桶表

(6)SORTED BY不常用,对桶中的一个或多个列另外排序

(7)ROW FORMAT

DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]

[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED,将会使用自带的SerDe。在建表的时候,用户还需要为表指定列,用户在指定表的列的同时也会指定自定义的SerDe,Hive通过SerDe确定表的具体的列的数据。

SerDe是Serialize/Deserilize的简称, hive使用Serde进行行对象的序列与反序列化。

(8)STORED AS指定存储文件类型

常用的存储文件类型:SEQUENCEFILE(二进制序列文件)、TEXTFILE(文本)、RCFILE(列式存储格式文件)

如果文件数据是纯文本,可以使用STORED AS TEXTFILE。如果数据需要压缩,使用 STORED AS SEQUENCEFILE。

(9)LOCATION :指定表在HDFS上的存储位置。

(10)AS:后跟查询语句,根据查询结果创建表。

(11)LIKE允许用户复制现有的表结构,但是不复制数据。

(12)TBLPROPERTIES设置表的属性,在表明压缩类型时用过。(例如”orc.compress”=”SNAPPY”)

2 查询表信息

2.1 查看表的元数据信息 desc

DESCRIBE FORMATTED orders;

2.2 修改 alter

2.2.1 修改表名:

alter table dept rename to depts;

2.2.2 修改表属性:

修改列名和列数据类型:alter table depts change dept_name dname string ; 修改位置放置第一位:alter table depts change id did string first; 修改位置指定某一列后面:alter table depts change dname dname string after did;

2.2.3 修改列,注意如果修改的字段类型和之前的字段类型不一致,之前的数据就无法显示

alter table depts add columns(daddress string);

2.2.4 添加列(慎用)

alter table depts add partition(dt=20200713); alter table depts add partition(dt=20200713) location '/user/te;;

2.2.5 添加分区

alter table depts partition(dt=20170404) rename to partition(dt=20170405); alter table depts partition(dt=20170404) set location '/user/te;;

2.2.6 修改分区

alter table depts drop if exists partition(dt=20170404);

2.2.7 删除分区

alter table depts drop if exists partition(dt=20170404);

2.3 查询表数据

Hive中的SELECT基础语法和标准SQL语法基本一致,支持WHERE、DISTINCT、GROUP BY、ORDER BY、HAVING、LIMIT、子查询等;

具体语法:

[WITH CommonTableExpression (, CommonTableExpression)*] SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference [WHERE where_condition] [GROUP BY col_list] [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY col_list] ] [LIMIT number]

2.3.1 常用关键词简介

(1) ORDER BY和SORT BY

ORDER BY用于全局排序,就是对指定的所有排序键进行全局排序,使用ORDER BY的查询语句,最后会用一个Reduce Task来完成全局排序。

explain select id,name from emp where deptid = 1001

explain select id,name from emp where deptid = 1001 order by id

SORT BY用于分区内排序,即每个Reduce任务内排序。


设定了2个reduce,从结果可以看出,每个reduce内做了排序。设定一个reduce,从结果看和order by一致。

(2)DISTRIBUTE BY和CLUSTER BY

distribute by:按照指定的字段或表达式对数据进行划分,输出到对应的Reduce或者文件中。

cluster by:除了兼具distribute by的功能,还兼具sort by的排序功能。

(3)GROUP BY语句通常会和聚合函数一起使用,按照一个或者多个列队结果进行分组,然后对每个组执行聚合操作。

(4)子查询

子查询和标准SQL中的子查询语法和用法基本一致,需要注意的是,Hive中如果是从一个子查询进行SELECT查询,那么子查询必须设置一个别名。

SELECT col FROM ( SELECT a+b AS col FROM t1 ) t2

where 语句中也支持子查询。

SELECT * FROM A WHERE A.a IN (SELECT foo FROM B); SELECT A FROM T1 WHERE EXISTS (SELECT B FROM T2 WHERE T1.X = T2.Y)

WITH..AS..也叫做子查询部分,语句允许hive定义一个sql片段,供整个sql使用,即将子查询作为一个表的语法,叫做Common Table Expression(CTE)

with q1 as (select * from src where key= '5'), q2 as (select * from src s2 where key = '4') select * from q1 union all select * from q2; with q1 as ( select key, value from src where key = '5') from q1 insert overwrite table s1 select * from q1;

(5)Hive查询中有两个虚拟列:INPUT__FILE__NAME:数据对应的HDFS文件名;BLOCK__OFFSET__INSIDE__FILE:该行记录在文件中的偏移量;

(6)HAVING 语句

having与where不同点

·where后面不能写分组函数,而having后面可以使用分组函数。

·having只用于group by分组统计语句。

(7) JOIN语句

Hive中除了支持和传统数据库中一样的内关联、左关联、右关联、全关联,还支持LEFT SEMI JOIN和CROSS JOIN,但这两种JOIN类型也可以用前面的代替。

·内关联(JOIN)

·左外关联(LEFT [OUTER] JOIN)

·右外关联(RIHGHT [OUTER] JOIN)

(8)LIKE语句

如果字符串A或者字符串B为NULL,则返回NULL;如果字符串A符合表达式B的正则语法,则为TRUE;否则为FALSE。B中字符”_”表示任意单个字符,而字符”%”表示任意数量的字符。

年龄表

查询年龄为1开头的数据:select * from ages where age like '1%';

查询年龄第二位是1的数据:select * from ages where age like '_1%';

RLIKE子句是Hive中LIKE功能的一个扩展,其可以通过Java的正则表达式这个更强大的语言来指定匹配条件。

2.3.2 常用函数简介

(1) 字符串连接函数,concat

语法: concat(string A, string B...)

返回值: string

说明:返回输入字符串连接后的结果,支持任意个输入字符串

举例:select concat('abc','def','gh') from ages ;

(2)带分隔符字符串连接函数:concat_ws

语法: concat_ws(string SEP, string A, string B...)

返回值: string

说明:返回输入字符串,连接后的结果,SEP表示各个字符串间的分隔符

举例:select concat_ws(',','abc','def','gh') from ages ;

除此之外还可以将数组的形式转换为字符串concat_ws(string SEP, array)

举例:select concat_ws('|',array('ads','des','ss')) from ages ;

(3)集合去重函数:collect_set

语法: collect_set(col)

返回值: array

说明: 将col字段进行去重,合并成一个数组。

举例:cookies表

select cookieid,collect_set(name) from cookies group by cookieid ;

(4) UDTF一进多出

列转行:explode

语法: explode(ARRAY)

返回值: 多行

说明:将数组转换为多行

举例:select explode(array('ads','des','ss'))from ages;

除此之外还可以将map拆分为多行。

(5)UDAF多进一出

聚合函数count,sum,avg等

(6)窗口函数

一般和聚合函数配合使用

OVER():指定分析函数工作的数据窗口大小,这个数据窗口大小可能会随着行的变而变化。

举例:

orders表

总结:over是对分的组得到结果的count ,因为搜到了4个人,所以count数为4。直接count是分组后对组中的数据进行count。


总结:添加partition by name 表示按照name分组进行sum求和。

(7) 排序函数

RANK() 排序相同时会重复,总数不会变

DENSE_RANK() 排序相同时会重复,总数会减少

ROW_NUMBER() 会根据顺序计算

举例:scores表

select name, subject, score, rank() over(partition by subject order by score desc) rp, dense_rank() over(partition by subject order by score desc) drp, row_number() over(partition by subject order by score desc) rmp from scores;

得到结果:

(8)函数总结大全

一、关系运算:

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

4. 获取map中所有value集合

5. 获取map中所有key集合

6. 数组排序

八、类型转换函数

1. 二进制转换:binary

2. 基础类型之间强制转换:cast

九、日期函数

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

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

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

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







责任编辑: 鲁达

1.内容基于多重复合算法人工智能语言模型创作,旨在以深度学习研究为目的传播信息知识,内容观点与本网站无关,反馈举报请
2.仅供读者参考,本网站未对该内容进行证实,对其原创性、真实性、完整性、及时性不作任何保证;
3.本站属于非营利性站点无毒无广告,请读者放心使用!

“hive中如何转换时间格式,hive,字符串转时间格式,hive的时间格式,hive带毫秒的时间格式”边界阅读