HiveQL 字符串转日期 hive sql字符串转日期格式

文章目录

• 一. Hadoop与Sql
• 二. HDFS 文件存储
• 三. Hive下载与安装

• 1.Hive简介
• 2.hive Sql
• 3.hive 架构

• 3.1 MetaStore
• 3.2 Hive vs RDMBS
• 3.3 Hive的适用场景

• 4.hive 部署

• 4.1下载
• 4.2解压
• 4.3添加Hive的bin到环境变量:
• 4.4 修改配置文件
• 4.5 Hive基本命令

• 四. Hive DDL与DML基本操作

• 1. db <==> HDFS directory
• 2. table <==> HDFS directory
• 3.分区表
• 4.DDL操作

• 4.1 Create
• 4.2 Drop
• 4.3 数据类型与分隔符
• 4.4 导入数据
• 4.5 修改表名
• 4.6 DROP和TRUNCATE的区别
• 4.7 内部表与外部表
• 4.8 插入数据
• 4.9 插入数据到静态分区
• 4.10 插入数据到动态分区

• 五. Hive 函数的使用

• 1.between 800 and 1500 == [800,1500]
• 2.聚合函数
• 3. group by 分组
• 4.join：表与表之间的关联
• 5.日期 JSON处理
• 6. Array、Map、Struct 数据类型
• 7.分组 TOPN
• 8. explode 函数 (行转列)

一. Hadoop与Sql

Hadoop
	广义
		Hadoop、Hive、Sqoop
		圈内的这些框架：
			Hadoop
			Hive
			HBase
			Sqoop
			Spark
			Flink
			Presto/Impala...
			CDH/HDP
	狭义
	
	
hadoop.apache.org/	
项目名称.apache.org	
	
MR编程：
	麻烦、痛苦 Java
	
SQL：
	Database  Table
	select * from xxx group by y...

SQL on Hadoop

二. HDFS 文件存储

HDFS： 
	mkdir -p hdfspath
	put xxx hdfspath
	
	HDFS上的数据：文本
		id、name、age
	1,pk,30   1\tpk\t30  1 pk 30
	2,J,18
	普通的不能在普通的文本文件 如何使用SQL进行查询呢？
	
	Table：column name/type
		id int
		name string/varchar2...
		age int
		==> Schema  元数据(描述数据的数据)  源数据
	==> SQL
		id  index:0
		name  index:1
		age  index:2
	
	
	文本的路径在哪？
	文本对应的schema？
	文本的分隔符是什么？
	===> 才可以把文件系统上的文件和Table对应起来  Schema
	
	
	元数据：meta   文本文件的描述数据 MySQL
	源数据：raw    HDFS存的文本文件
	
	想使用SQL进行查询Hadoop上的数据
		源数据和元数据都是必不可少的
	
	Hive的数据存放在哪里？HDFS + MySQL
	
批流一体：Spark/Flink
	批：离线
	流：实时
	
	
	
	
data warehouse
	离线/实时
	
reading,  SQL
writing,  结果、中间结果
managing large datasets  大数据集
residing in distributed storage  FS：HDFS、S3...
using SQL ==> 大家喜欢的、简单	
	
A command line tool and 
JDBC driver 
are provided to connect users to Hive

三. Hive下载与安装

1.Hive简介

Facebook  海量结构化的数据统计问题
	构建在Hadoop之上的数据仓库/DW
		Hadoop: HDFS  MR  YARN
	职责：SQL ==> 对应的引擎的作业跑就行 
	其实就是一个客户端而已，Hive并不存在集群的概念
	底层引擎：MR  Tez  Spark
		只要一个参数进行引擎的切换，SQL用法不变的

2.hive Sql

Hive QL(HQL)：和我们RDBMS的SQL类似  类SQL
	离线/批计算
		在Hive上一个SQL的执行速度  vs MySQL上的同一个SQL执行速度？
	
	UI有一个文本框，输入SQL，提交按钮

	SQL再简单，只要是需要参与计算(count(1)) 是需要时间的
	复杂的SQL，等？
	离线的作业都是定时调度/手工执行 ==> 把结果写到某个Hive
	UI去拿结果(不参与计算：select xx from t)
	
	支持函数Function：build-in & UDF

3.hive 架构

3.1 MetaStore

***** 需要做HA
	一旦你的Meta挂了
	HDFS源数据的元数据就没有

    Hive的元数据是存放在MySQL  <== MetaStore
    MetaStore是一个通用的组件
	SparkSQL/Flink/Impala/Presto
	Hive里面创建的表 在其他框架也是能访问

3.2 Hive vs RDMBS

共同点：SQL、事务、insert (values)/update/delete、分布式
	不同点：规模、成本、数据体量

3.3 Hive的适用场景

离线、延时性不关注	

   有一个新的框架诞生，SQL写法与普通的SQL语法不同

4.hive 部署

4.1下载

archive.cloudera.com/cdh5/cdh/5/

	在生产上对应的软件版本一定要控制好
	版本选择的基本原则：尾巴一样(版本号)： cdh5.16.2
	Hadoop/Hive/Sqoop/HBase/Oozie...
	Spark例外
	所以Hive的版本：hive-1.1.0-cdh5.16.2
	hive-1.1.0-cdh5.16.2-src.tar.gz  源码
	hive-1.1.0-cdh5.16.2.tar.gz  安装包
	wget http://archive.cloudera.com/cdh5/cdh/5/hive-1.1.0-cdh5.16.2.tar.gz

4.2解压

tar -zxvf hive-1.1.0-cdh5.16.2.tar.gz -C ~/app/	
		
	配置文件：
		conf Hive相关的配置文件
		bin  Hive相关的脚本

4.3添加Hive的bin到环境变量:

export HIVE_HOME=/home/hadoop/app/hive-1.1.0-cdh5.16.2
export PATH=$HIVE_HOME/bin:$PATH
注意：改完一定要先source下，或者打开一个新的窗口

4.4 修改配置文件

Hive的元数据是在MySQL里面的
如果要访问MySQL的话，需要如下几个参数
	driver、url、user、password
还需要一个MySQL的驱动类
	***：不建议使用MySQL8的区别
	需要把MySQL的区别拷贝到$HIVE_HOME/lib
	
hive-site.xml
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>

<configuration>
	<property>
	  <name>javax.jdo.option.ConnectionURL</name>
	  <value>jdbc:mysql://localhost:3306/rzdata_hive?createDatabaseIfNotExist=true</value>
	</property>

	<property>
	  <name>javax.jdo.option.ConnectionDriverName</name>
	  <value>com.mysql.jdbc.Driver</value>
	</property>

	<property>
	  <name>javax.jdo.option.ConnectionUserName</name>
	  <value>root</value>
	</property>

	<property>
	  <name>javax.jdo.option.ConnectionPassword</name>
	  <value>pentaKill</value>
	</property>
</configuration>			
			
Hive相关的配置参数均来自：	
	https://cwiki.apache.org/confluence/display/Hive/Configuration+Properties

	hive-env.sh
		HADOOP_HOME
		export HADOOP_HEAPSIZE=1024

	
特别说明：
	使用Hive时一定要确保Hadoop环境是OK

4.5 Hive基本命令

(1)!clear;

(2)exit;

(3)show databases;

    default   Hive里默认就有一个名字为default的数据库
    default指向的路径是${hive.metastore.warehouse.dir}
    
(4)create database rz_hive;
   
    HDFS上的目录：	
    /user/hive/warehouse/rz_hive.db
		
	规则：${hive.metastore.warehouse.dir}/数据库的名称.db
		
	数据库存放在HDFS的什么位置呢？
		hive.metastore.warehouse.dir
			/user/hive/warehouse
	
	在default数据库下创建一个表
		create table student(id int, name string, age int);
		drop table student;
	表在HDFS上的目录：${数据库的目录}/表名/
	
(5) use 数据库名称    切换到指定的数据库名称对应数据库中去
		create table student2(id int, name string, age int);

	如何在控制台上显示出来当前处于哪个数据库呢？
		hive.cli.print.current.db
	
(6) Hive里面涉及到参数的问题如何设置呢？
		1) hive-site.xml  全局配置
		2) set hive.cli.print.current.db;  局部  对当前session有效
			set key; 显示key的值
			set key=value;
			set hive.cli.print.current.db=true;	
	
	hive.cli.print.current.db
	hive.cli.print.header
	
	调优的时候很多参数是可以调整的
	为什么不配置到全局的里面去呢？
		调优是针对作业的，不一定是针对全局的
	set key=value;  //设置参数
	sql... 
	set key         // 还原参数
	
(7)查看表结构命令：desc formatted student;
	
(8)Hive运行时的日志信息在哪里？
		hive.log.threshold=ALL
		hive.root.logger=WARN,DRFA
		hive.log.dir=${java.io.tmpdir}/${user.name}
		hive.log.file=hive.log
		
		${java.io.tmpdir}: /tmp
		${user.name}     : hadoop
		/tmp/hadoop/hive.log
		
	
    select * from student; 
	是在hive命令行里面执行的
	先要进入hive

(9)交互式命令行:

    直接在linux控制台上执行即可
	hive -e "select * from student"
	hive -f 指定sql文件

(10)shell脚本：

	hive -e "select * from student day=${day}"
	hive -f 指定sql文件

    crontab/调度框架 定时调度
	离线作业执行粒度是一天一次
	每天凌晨3点去统计昨天的数据

四. Hive DDL与DML基本操作

1. db <==> HDFS directory

default  /user/hive/warehouse
rz_hive  /user/hive/warehouse/rz_hive.db

2. table <==> HDFS directory

default  /user/hive/warehouse/student/表的文件内容(可以有多个文件的)
rz_hive  /user/hive/warehouse/rz_hive.db/student/...

3.分区表

Hive: db/table/partition对应的都是HDFS上的文件夹

通过类似于
	【表文件夹/文件】
	【表文件夹/分区文件夹(1..n)/文件】

4.DDL操作

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

[]：表示可有可无

==>

4.1 Create

CREATE DATABASE IF NOT EXISTS hive;
CREATE DATABASE IF NOT EXISTS hive2 LOCATION '/hive/directory';

4.2 Drop

DROP DATABASE IF EXISTS hive2 CASCADE; 
生产：创建数据库   修改/删除(级联删除) 几乎是用不上的

One2Many : db2table
2:to

4.3 数据类型与分隔符

数据类型
	数值类型：int/float/double/bigint 
	字符串类型：string
	
	
分隔符
	(*****)log里面字段与字段之间的分隔符是什么
	生产中用的比较多: , \t
	行与行之间的分隔符是什么
	id name age
	1,pk,30
	2,xingxing,60
	
	1	pk	30
	2	xingxing	60
	
	GET www.pentaKill.com
	对于一个表里面，字段与字段的分隔符是固定
	不然的话读取不出来的


CREATE TABLE table_name 
[(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
[
[ROW FORMAT row_format] 
AS select_statement
 
CREATE TABLE table_name
  LIKE existing_table_or_view_name
  [LOCATION hdfs_path];

row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
 
file_format:
  : SEQUENCEFILE
  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)
  | ORC         -- (Note: Available in Hive 0.11.0 and later)
  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)
  | AVRO        -- (Note: Available in Hive 0.14.0 and later)
  | JSONFILE    -- (Note: Available in Hive 4.0.0 and later)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
 
column_constraint_specification:
  : [ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK  [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]
 
default_value:
  : [ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ] 
 
constraint_specification:
  : [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE 
    [, CONSTRAINT constraint_name UNIQUE (col_name, ...) DISABLE NOVALIDATE RELY/NORELY ]
    [, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]	
	


create external table pentaKill_emp_external(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
location '/hive/external/emp/'
;

4.4 导入数据

LOAD DATA LOCAL INPATH '/home/hadoop/data/emp.txt' OVERWRITE INTO TABLE pentaKill_emp;

LOCAL: 本地，你执行hive命令那个机器	
无LOCAL：HDFS路径	
	
Hive加载数据在生产上使用：
1) load data
2) insert (不是values)


create table pentaKill_emp2 like pentaKill_emp;
create table pentaKill_emp3 as select * from pentaKill_emp;

4.5 修改表名

ALTER TABLE pentaKill_emp2 RENAME TO pentaKill_emp2_new;

4.6 DROP和TRUNCATE的区别

4.7 内部表与外部表

EXTERNAL：外部表
	元数据被删除
	HDFS数据依然存在
	
MANAGED：内部表
	HDFS的数据被清空
	元数据也会被清空

架构：元数据信息是存放在MySQL

4.8 插入数据

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 ...)]
LOAD DATA LOCAL INPATH '/home/hadoop/data/emp.txt' INTO TABLE pentaKill_emp;
LOAD DATA INPATH '/test/emp.txt' INTO TABLE pentaKill_emp;

append
Standard syntax:
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1 FROM from_statement;
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1 FROM from_statement;
 
Hive extension (multiple inserts):
FROM from_statement
INSERT OVERWRITE TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...) [IF NOT EXISTS]] select_statement1
[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2]
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2] ...;
FROM from_statement
INSERT INTO TABLE tablename1 [PARTITION (partcol1=val1, partcol2=val2 ...)] select_statement1
[INSERT INTO TABLE tablename2 [PARTITION ...] select_statement2]
[INSERT OVERWRITE TABLE tablename2 [PARTITION ... [IF NOT EXISTS]] select_statement2] ...;
 
Hive extension (dynamic partition inserts):
INSERT OVERWRITE TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;
INSERT INTO TABLE tablename PARTITION (partcol1[=val1], partcol2[=val2] ...) select_statement FROM from_statement;




INSERT OVERWRITE [LOCAL] DIRECTORY directory1
  [ROW FORMAT row_format] [STORED AS file_format] (Note: Only available starting with Hive 0.11.0)
  SELECT ... FROM ...
  
  
INSERT OVERWRITE LOCAL DIRECTORY '/home/hadoop/data/tmp/' select empno,ename from pentaKill_emp;

4.9 插入数据到静态分区

RDBMS 分区
	who when what
	一天一个表
	log_yyyyMMdd
	union
	
	减少全表扫描的可能性
	降低IO

	/user/hive/warehouse/emp/day=20221001/...
	/user/hive/warehouse/emp/day=20221002/...
	Hive的分区其实对应的还是HDFS上的文件夹
	元数据里面肯定也有一张对应的表用来存放分区信息的


[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]

	...表示一张表可以有多个分区字段


create table emp_partition(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
) 
PARTITIONED BY (day string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
;	


LOAD DATA LOCAL INPATH '/home/hadoop/data/emp.txt' INTO TABLE emp_partition PARTITION(day="20220808");

4.10 插入数据到动态分区

手动创建hdfs的分区文件夹，需要MSCK，hive元数据表才知道； 等价于 add partition方法

MSCK REPAIR TABLE emp_partition [ADD/DROP/SYNC PARTITIONS];



把pentaKill_emp表中deptno=20的数据写到新表里
create table emp2(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double
) 
PARTITIONED BY (deptno int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
;	

LOAD DATA LOCAL INPATH '/home/hadoop/data/emp.txt' INTO TABLE emp_partition_2 PARTITION(day="20220808",hour="21");

动态分区

insert into table emp2 partition(deptno) select empno,ename,job,mgr,hiredate,sal,comm,deptno from pentaKill_emp;

五. Hive 函数的使用

1.between 800 and 1500 == [800,1500]

0.0 和 null 不是一个概念


user_id:  ""  ''  null  
不管是SQL还是代码业务逻辑：一定要考虑全面
	单元测试的重要性
		单元测试不是QA做的，而是RD做的

2.聚合函数

max/min/count/sum/avg

3. group by 分组

求每个部门的平均工资
	按照部门分组
	分组后求每个组的平均工资
select deptno,avg(sal) from pentaKill_emp group by deptno;	

每个部门、每个岗位的最高工资
	部门、岗位进行分组
	分组后求最高工资
	
select deptno,job, max(sal) from pentaKill_emp group by deptno,job;	


求每个部门的平均工资 > 2500
	where 
	having

select deptno,avg(sal) from pentaKill_emp group by deptno having avg(sal)>2500;	

hive.fetch.task.conversion
hive.execution.engine

4.join：表与表之间的关联

select
a.id,a.name,b.age
from
a full join b
on a.id = b.id;

默认的join是inner join
left：以左表为基准，左表的数据都在，匹配不上右表数据的字段为null

inner join = join
left outer join = left join

Hive内置 build-in
	FunctionRegistry

5.日期 JSON处理

时间
数学
字符串： 嵌套


create table hive_topn(
id int, age int, name string, sex string
)row format delimited fields terminated by ',';

拥有兄弟的人以及兄弟的名称

create table t_rate as
select
userid, movie,rate,time,
from_unixtime(cast(time as bigint)) as ts,
year(from_unixtime(cast(time as bigint))) as year,
month(from_unixtime(cast(time as bigint))) as month,
day(from_unixtime(cast(time as bigint))) as day,
hour(from_unixtime(cast(time as bigint))) as hour,
minute(from_unixtime(cast(time as bigint))) as minute
from
(select json_tuple(json,'movie','rate','time','userid') as(movie,rate,time,userid) from rating_json) t

6. Array、Map、Struct 数据类型

<==  定义表结构&查询数据

7.分组 TOPN

分组TopN: 每个性别中年龄最大的2个人  Top2

select
sex,age
from hive_topn
group by sex,age order by age desc

**(分组排序)深入骨髓，随手就来

select id,age,name,sex
from
(
select id,age,name,sex,
row_number() over(partition by sex order by age desc) as rank
from hive_topn
) t where rank<=2;

8. explode 函数 (行转列)

wc

select word,count(1) as cnts
from
(select explode(split(sentence,",")) as word from hive_wc ) t
group by word
order by cnts desc;