很高兴遇到你~
（1）Hive常用日期格式处理
（2）Hive常用函数
（3）Hive常用语句（实用）
- 数据加载清理与建表
- 表检索与表结构查询
（4）HiveSQL使用技巧与优化
（5）HiveSQL使用注意项
Hive常用日期格式处理
--获取当前日期
select current_date; --2021-06-20
select current_timestamp; --2021-06-20 17:01:01
select from_unixtime(unix_timestamp()); --2021-06-20 17:01:01
select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss'); --2021-06-20 17:01:01
select from_unixtime(unix_timestamp(),'yyyy-MM-dd'); --2021-06-20
--返回日期中的年、月、日、时、分、秒
select year(current_timestamp); --2021
select month(current_timestamp); --6
select day(current_timestamp); --20
select hour('2021-06-20 17:27:01'); --17
select minute('2021-06-20 17:27:01'); --27
select second('2021-06-20 17:27:01'); --1
--返回日期在当前的周数
select weekofyear(current_timestamp) --24
--返回结束日期减去开始日期的天数
select datediff('2020-08-16','2020-08-11') --5
--返回开始日期startdate增加days天后的日期
select date_add('2020-08-16',10) --2020-08-26
--返回开始日期startdate减少days天后的日期
select date_sub('2016-08-16',10) --2020-08-06
--返回当月的第一天
select trunc('2020-08-16','MM') --2016-08-01
--返回当年的第一天
select trunc('2020-08-16','YEAR') --2016-01-01
--日期转换函数
from_unixtime() --时间戳转日期函数，返回值：string
unix_timestamp() --日期转时间戳函数，返回值: bigint，如果转化失败，则返回0
--日期由yyyymmdd格式转为yyyy-mm-dd格式
select from_unixtime(unix_timestamp('20200905','yyyyMMdd'),'yyyy-MM-dd')
select concat(substr('20200905',1,4),'-',substr('20200905',5,2),'-',substr('20200905',7,2))
--日期由yyyy-mm-dd格式转为yyyymmdd格式
select from_unixtime(unix_timestamp('2020-09-05','yyyy-MM-dd'),'yyyyMMdd')
select concat(substr('2020-09-05',1,4),substr('2020-09-05',6,2),substr('2020-09-05',9,2))
select date_format('2020-08-16','yyyyMMdd') --20200816
--返回日期时间字段中的日期部分
select to_date('2020-08-16 10:03:01') --2020-08-16
Hive常用函数
--条件转换nvl&coalesce
SELECT NVL(NULL,'TEST') --TEST 如果为NULL，则为后一个的值
SELECT COALESCE(NULL,'') --'' 如果为NULL则转为''，此时等同于NVL
SELECT COALESCE(NULL,NULL,NULL,'TEST') --TEST 可以多个字段，直到遇到不为NULL的才返回
SELECT COALESCE(NULL,NULL,NULL,NULL) --NULL 都是NULL则返回NULL
--单条件判断使用IF
--IF(BOOLEAN condition,T valueTrue,T valueFalseOrNull)
SELECT IF(TRUE,COLUMN1,COLUMN2) --COLUMN1
SELECT IF(FALSE,COLUMN1,COLUMN2) --COLUMN2
SELECT IF(NULL,COLUMN1,COLUMN2) --COLUMN1
--字符串截取substr&substring(两个函数一致)
--substr(string str,int start) 从start返回到结尾
--substr(string str,int start,int len) 从start开始返回len位
SELECT SUBSTR('abcdef',1) --abcdef
SELECT SUBSTR('abcdef',3) --cdef
SELECT SUBSTR('abcdef',-1) --f
SELECT SUBSTR('abcdef',-3) --def
SELECT SUBSTRING('abcdef',2,2) --bc
SELECT SUBSTRING(LEN('abcdef')-2) --ef 截取字段后两位
--字符串拼接concat&concat_ws
--concat函数拼接字符串中有一个字段是NULL则返回NULL，concat_ws不是
SELECT CONCAT('A','B','C') --ABC
SELECT CONCAT_WS('-','A','B','C') --A-B-C
SELECT CONCAT('A','B',NULL) --NULL
SELECT CONCAT_WS('-','A','B',NULL,'C') --A-B-C
SELECT CONCAT_WS(NULL,'A','B',NULL,'C') --NULL
--取空格Trim(同其它数据库使用一致)
SELECT TRIM(' abcdef ') --'abcdef'
--数据类型转换CAST
SELECT CAST(123 AS STRING)
SELECT CAST(CAST(123 AS STRING) AS INT)
SELECT CAST(ROW_NUMBER() OVER(PARTITION BY TXN_DT ORDER BY ID) AS STRING) FROM TABLE
--字符替换replace
SELECT REPLACE('2021-06-26','-','') --20210626
--左右补位lpad&rpad
SELECT LPAD('abcd',10,'0') --000000abcd 不足10位左补0
SELECT RPAD('abcd',10,'0') --abcd000000 不足10位右补0
Hive常用语句--数据加载清理与建表
--创建外部表
CREATE EXTERNAL TABLE IF NOT EXISTS stocks (
exchange STRING,
symbol STRING,
price_open FLOAT,
price_close FLOAT,
volume INT)
COMMENT 'this is stocks table' --表描述/注释信息
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|' --表数据分隔符，这里的分隔符只能是一个分隔符
LINES TERMINATED BY '\n' --行分隔符'\n'，一般缺省，一般均用\n作为换行
stored as textfile --存储格式
LOCATION '/data/stocks';
--多分隔符使用MuLtiDelimitSerDe
create external table t(id INT,name STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES("field.delim"="!^")
LOCATION '/user/hive/warehouse/t';
--Load加载时一般先创建临时表textfile存储格式
DROP TABLE IF EXISTS DB_NAME.TABLE_NAME;
CREATE TABLE IF NOT EXISTS DB_NAME.TABLE_NAME(
COLUMN1 STRING,
COLUMN2 VARCHAR(10),
PT_DT STRING,
C_TEMP STRING --文件加载一般可给临时表最后多加一个temp字段，用于解决文本有行尾分隔符，或者有误操作多字段数据等情况
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.MultiDelimitSerDe'
WITH SERDEPROPERTIES("field.delim"="!^") --指定分隔符
STORED AS TEXTFILE; --指定存储格式
--复制表结构创建表
CREATE [EXTERNAL] TABLE IF NOT EXISTS mydb.employees_temp
LIKE mydb.employees
LOCATION '/path/to/data';
--将内部表转换为外部表
alter table log4 set tblproperties(
'EXTERNAL' = 'TRUE'
);
alter table log4 set tblproperties(
'EXTERNAL' = 'false'
);
alter table log4 set tblproperties(
'EXTERNAL' = 'FALSE'
);
--创建分区表
CREATE TABLE employees (
name STRING,
salary FLOAT,
)
comment '员工表（标注释）'
PARTITIONED BY (PT_DT STRING COMMENT '分区日期');
--标准使用创建分区表格式，其中tblproperties是表属性，设置压缩格式等
DROP TABLE IF EXISTS DB_NAME.TABLE_NAME; --建表先删除再创建
CREATE TABLE IF NOT EXISTS DB_NAME.TABLE_NAME
(
H_ROWKEY STRING COMMENT 'rowkey',
ID STRING COMMENT 'id',
BRH_NO VARCHAR(5) COMMENT '机构号',
ST_DT VARCHAR(10) COMMENT '开始日期',
END_DT VARCHAR(10) COMMENT '结束日期',
MD5 VARCHAR(32) COMMENT 'md5值'
)
COMMENT '表描述/注释信息'
PARTITIONED BY(PT_DT STRING COMMENT '分区日期')
STORED AS PARQUET
TBLPROPERTIES('PARQUET.COMPRESSION'='SNAPPY');
--load加载一般加载到创建的textfile格式的temp表
--load数据加载，从本地
LOAD DATA LOCAL INPATH '/nas/temp/teble_name_file' overwrite into table db_name.table_name;
--load数据加载，从本地加载到分区表，overwrite可根据情况缺省，缺省时为追加加载
LOAD DATA LOCAL INPATH '/nas/temp/teble_name_file' overwrite into table db_name.table_name
PARTITION (pt_dt='2020-01-01');
--load数据加载，从hdfs
LOAD DATA INPATH '/user/user_name/teble_name_file' overwrite into table db_name.table_name;
--load数据加载，从hdfs加载到分区表，overwrite可根据情况缺省，缺省时为追加加载
LOAD DATA INPATH '/user/user_name/teble_name_file' overwrite into table db_name.table_name;
PARTITION (pt_dt='2020-01-01');
--将一个表的数据插入另一个表
insert into table table1 select * from table2;
--INSERT OVERWRITE插入
--会覆盖该表已有的数据
--针对非分区表，相当于truncate再insert
--针对分区表，相当于truncate partitions再insert，涉及的partition都会进行truncate
INSERT OVERWRITE TABLE employees
PARTITION (pt_dt='2020-01-01')
SELECT * FROM staged_employees se
WHERE pt_dt='2020-01-01';
INSERT OVERWRITE TABLE TABLE_NAME SELECT * FROM TABLE_NAME WHERE where_statement;
--INSERT INTO插入
--追加数据到该表，不会影响该表已有的数据
INSERT INTO TABLE TABLE_NAME partition(col_name='col_value') column1,column2,…… FROM OTHER_TABLE_NAME;
--INSERT INTO values
insert into table_name values(1,'value1');
insert into table_name values(2,'value2');
--分区表一般设置如下两个属性，开启动态分区
set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = nonstrict;
--动态分区加载（动态分区加载必须开启动态分区）
INSERT OVERWRITE TABLE ${DB_NAME}.TABLE_NAME PARTITION(PT_DT)
SELECT
CONCAT(SUBSTR(COALESCE(TRIM(KEHUZH),''),1,2),
COALESCE(END_DT,'')),
END_DT AS PT_DT
FROM ${DB_NAME}.TABLE_NAME_H
WHERE END_DT>='{load_time}';
--数据归档
INSERT INTO TABLE ${DB_NAME}.TABLE_NAME PARTITION(PT_DT)
SELECT ST_DT,
END_DT,
CASE WHEN END_DT<='2018-12-31' THEN CONCAT(SUBSTR(END_DT,1,4),'-01-01')
ELSE CONCAT(SUBSTR(END_DT,1,7),'-01')
END AS PT_DT
FROM ${DB_NAME}.TABLE_NAME_TEMP
WHERE END_DT<='2020-06-30'
AND END_DT>=CONCAT(SUBSTR('${load_time}',1,4),'-01-01')
AND END_DT<=CONCAT(SUBSTR('${load_time}',1,4),'-12-31');
--导出数据
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/ca_employees'
SELECT name, salary, address
FROM employees
WHERE se.state = 'CA';
--Hive可以直接将查询结果insert到hdfs目录或者本地目录
INSERT OVERWRITE DIRECTORY '/tmp/hdfs_out' SELECT a.* FROM invites a WHERE a.ds='<DATE>';
INSERT OVERWRITE LOCAL DIRECTORY '/tmp/local_out' SELECT a.* FROM pokes a;
--删除表
DROP TABLE IF EXISTS table_name;
--修改表名
ALTER TABLE table_name RENAME TO table_new_name;
--修改列
alter table table_name change column ip myip String;
alter table 表名 change column 字段名 新字段名 字段类型 [描述信息];
--修改列（使用after关键字，将修改后的字段放在某个字段后）
ALTER TABLE log_messages
CHANGE COLUMN hms hours_minutes_seconds INT
COMMENT 'The hours, minutes, and seconds part of the timestamp'
AFTER severity;
--使用first关键字，将修改的字段调整到第一个字段
alter table table_name change column ip myip int comment 'this is myip' first;
--增加列（使用add columns,后面跟括号，括号里是要加入的字段及字段描述，多个字段用逗号分开）
ALTER TABLE log_messages ADD COLUMNS (
app_name STRING COMMENT 'Application name',
session_id LONG COMMENT 'The current session id');
--删除 替换列（使用replace columns,后面跟括号，括号里是要删除的字段，多个字段间用逗号）
ALTER TABLE log_messages REPLACE COLUMNS (
hours_mins_secs INT COMMENT 'hour, minute, seconds from timestamp',
severity STRING COMMENT 'The message severity'
message STRING COMMENT 'The rest of the message');
alter table log4 replace columns(x int,y int);
--添加分区
ALTER TABLE table_name ADD PARTITION(partCol='value1') location '/user/hive/warehouse/table_name';
--删除分区
ALTER TABLE table_name DROP IF EXISTS PARTITION(PT_DT='2008-08-08');
--修改分区
ALTER TABLE table_name PARTITION(partCol='value1') set location 'new location';
Hive常用语句--表检索与表结构查询
--数据库查询与切换 show databases like 'bdpd.*' use database --表清单查看 show tables --模糊匹配查找表 show tables 'empl.*' --employees --查看表分区 SHOW PARTITIONS employees; --查看建表语句，查看分区字段 show create table table_name; --查看表信息 DESCRIBE employees; --查看表扩展信息，可以显示表所在hdfs路径，快速获取表数据条数numRows，分区个数numPartitions，存储压缩格式等 describe extended mydb.tablename;