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(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 --正则匹配函数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 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 table_name可以快速得到表的数据总量，在数据验证中非常实用 describe extended mydb.tablename; --数据验证时，对于简单的不需要聚合的类似SELECT<COL> FROM <TABLE> LIMIT N语句，不需要起MapReduce job，直接通过Fetch task查取数据 set hive.fetch.task.conversion=more;
