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
SELECT'a'||'b'--ab 等价于concat
select'a'||'b'||NULL--NULL
--取空格Trim(同其它数据库使⽤⼀致)
SELECT TRIM(' abcdef ')  --'abcdef'
--数据类型转换CAST
SELECT CAST(123AS STRING)
SELECT CAST(CAST(123AS 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
--正则匹配函数regexp_replace
SELECT REGEXP_REPLACE('2021/01/01','/|-','')  --20210101
SELECT REGEXP_REPLACE('2021-01-01','/|-','')  --20210101
--左右补位lpad&rpad
SELECT LPAD('abcd',10,'0')    --000000abcd 不⾜10位左补0
SELECT RPAD('abcd',10,'0')    --abcd000000 不⾜10位右补0
--HiveSQL也⽀持开窗函数，如：分组排序等使⽤
--取出每⽇每个账号最新的⼀条数据
SELECT*
FROM (SELECT*,ROW_NUMBER()OVER(PARTITION BY ACC_DATE,ACCOUNT ORDER BY ST_DT DESC) AS CNT
FROM ${DB_NAME}.TABLE_NAME
WHERE ACC_DATE='${load_time}') as T
WHERE T.CNT=1
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 ployees_temp
ployees
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');
--分区表⼀般设置如下两个属性，开启动态分区
dynamic.partition = true;
de = 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
--模糊匹配查表
unix时间戳转换日期格式show tables 'empl.*'--employees
--查看表分区
SHOW PARTITIONS employees;
--查看建表语句，查看分区字段
show create table table_name;
--查看表信息
DESCRIBE employees;
--查看表扩展信息，可以显⽰表所在hdfs路径，快速获取表数据条数numRows，分区个数numPartitions，存储压缩格式等
--describe extended table_name可以快速得到表的数据总量，在数据验证中⾮常实⽤
describe extended mydb.tablename;
--数据验证时，对于简单的不需要聚合的类似SELECT<COL> FROM <TABLE> LIMIT N语句，不需要起MapReduce job，直接通过Fetch task查取数据set hive.version=more;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。