HiveSQL常用（上篇：常用函数与语句）

--获取当前日期
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 @H_508_72@month(CURRENT_TIMESTAMP); --6
SELEct day(CURRENT_TIMESTAMP); --20
SELEct hour('2021-06-20 17:27:01'); --17
SELEct @R_607_9275@e('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','@H_342_86@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

--条件转换nvl&coalesce
SELECT NVL(NULL,'TEST')  --TEST @R_489_5330@，则为后一个的值
SELECT COALESCE(NULL,'') --'' @R_489_5330@则转为''，此时等同于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

--创建外部表
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 '@H_342_86@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_@R_607_9275@es_seconds INT
COMMENT 'The hours, @R_607_9275@es, 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, @R_607_9275@e, 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';

--数据库查询与切换
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;