HiveSQL常用(上篇:常用函数与语句)

很高兴遇到你~

(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;

 

上一篇:Unity3D游戏引擎最详尽基础教程


下一篇:Android JNI 增强应用程序性能