2023年6月27日发(作者：)

HiveSQL常⽤（上篇：常⽤函数与语句）很⾼兴遇到你~

Hive常⽤⽇期格式处理--获取当前⽇期select current_date; --2021-06-20select current_timestamp; --2021-06-20 17:01:01select from_unixtime(unix_timestamp()); --2021-06-20 17:01:01select from_unixtime(unix_timestamp(),'yyyy-MM-dd HH:mm:ss'); --2021-06-20 17:01:01select from_unixtime(unix_timestamp(),'yyyy-MM-dd'); --2021-06-20--返回⽇期中的年、⽉、⽇、时、分、秒select year(current_timestamp); --2021select month(current_timestamp); --6select day(current_timestamp); --20select hour('2021-06-20 17:27:01'); --17select minute('2021-06-20 17:27:01'); --27select 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() --时间戳转⽇期函数，返回值：stringunix_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&coalesceSELECT NVL(NULL,'TEST') --TEST 如果为NULL，则为后⼀个的值SELECT COALESCE(NULL,'') --'' 如果为NULL则转为''，此时等同于NVLSELECT 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) --COLUMN1SELECT IF(FALSE,COLUMN1,COLUMN2) --COLUMN2SELECT 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) --abcdefSELECT SUBSTR('abcdef',3) --cdefSELECT SUBSTR('abcdef',-1) --fSELECT SUBSTR('abcdef',-3) --defSELECT SUBSTRING('abcdef',2,2) --bcSELECT SUBSTRING(LEN('abcdef')-2) --ef 截取字段后两位--字符串拼接concat&concat_ws--concat函数拼接字符串中有⼀个字段是NULL则返回NULL，concat_ws不是SELECT CONCAT('A','B','C') --ABCSELECT CONCAT_WS('-','A','B','C') --A-B-CSELECT CONCAT('A','B',NULL) --NULLSELECT CONCAT_WS('-','A','B',NULL,'C') --A-B-CSELECT CONCAT_WS(NULL,'A','B',NULL,'C') --NULLSELECT 'a'||'b' --ab 等价于concatselect 'a'||'b'||NULL --NULL--取空格Trim(同其它数据库使⽤⼀致)SELECT TRIM(' abcdef ') --'abcdef'--数据类型转换CASTSELECT 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--字符替换replaceSELECT REPLACE('2021-06-26','-','') --20210626--正则匹配函数regexp_replaceSELECT REGEXP_REPLACE('2021/01/01','/|-','') --20210101SELECT REGEXP_REPLACE('2021-01-01','/|-','') --20210101--左右补位lpad&rpadSELECT LPAD('abcd',10,'0') --000000abcd 不⾜10位左补0SELECT 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 CNTFROM ${DB_NAME}.TABLE_NAMEWHERE ACC_DATE='${load_time}') as TWHERE =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';--多分隔符使⽤MuLtiDelimitSerDecreate external table t(id INT,name STRING)ROW FORMAT SERDE 'elimitSerDe'WITH SERDEPROPERTIES(""="!^")LOCATION '/user/hive/warehouse/t';--Load加载时⼀般先创建临时表textfile存储格式DROP TABLE IF EXISTS DB__NAME;CREATE TABLE IF NOT EXISTS DB__NAME(COLUMN1 STRING,COLUMN2 VARCHAR(10),PT_DT STRING,C_TEMP STRING --⽂件加载⼀般可给临时表最后多加⼀个temp字段，⽤于解决⽂本有⾏尾分隔符，或者有误操作多字段数据等情况)ROW FORMAT SERDE 'elimitSerDe'WITH SERDEPROPERTIES(""="!^") --指定分隔符STORED AS TEXTFILE; --指定存储格式--复制表结构创建表CREATE [EXTERNAL] TABLE IF NOT EXISTS ees_tempLIKE eesLOCATION '/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; --建表先删除再创建CREATE TABLE IF NOT EXISTS DB__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 PARQUETTBLPROPERTIES('SSION'='SNAPPY');--load加载⼀般加载到创建的textfile格式的temp表--load数据加载，从本地LOAD DATA LOCAL INPATH '/nas/temp/teble_name_file' overwrite into table db__name;--load数据加载，从本地加载到分区表，overwrite可根据情况缺省，缺省时为追加加载LOAD DATA LOCAL INPATH '/nas/temp/teble_name_file' overwrite into table db__namePARTITION (pt_dt='2020-01-01');--load数据加载，从hdfsLOAD DATA INPATH '/user/user_name/teble_name_file' overwrite into table db__name;--load数据加载，从hdfs加载到分区表，overwrite可根据情况缺省，缺省时为追加加载LOAD DATA INPATH '/user/user_name/teble_name_file' overwrite into table db__name;PARTITION (pt_dt='2020-01-01');--将⼀个表的数据插⼊另⼀个表insert into table table1 select * from table2;--INSERT OVERWRITE插⼊--会覆盖该表已有的数据--针对⾮分区表，相当于truncate再insert--针对分区表，相当于truncate partitions再insert，涉及的partition都会进⾏truncateINSERT OVERWRITE TABLE employeesPARTITION (pt_dt='2020-01-01')SELECT * FROM staged_employees seWHERE 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 valuesinsert into table_name values(1,'value1');insert into table_name values(2,'value2');--分区表⼀般设置如下两个属性，开启动态分区set ion = true;set = nonstrict;--动态分区加载（动态分区加载必须开启动态分区）INSERT OVERWRITE TABLE ${DB_NAME}.TABLE_NAME PARTITION(PT_DT)SELECTCONCAT(SUBSTR(COALESCE(TRIM(KEHUZH),''),1,2),COALESCE(END_DT,'')),END_DT AS PT_DTFROM ${DB_NAME}.TABLE_NAME_HWHERE 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_DTFROM ${DB_NAME}.TABLE_NAME_TEMPWHERE 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, addressFROM employeesWHERE = 'CA';--Hive可以直接将查询结果insert到hdfs⽬录或者本地⽬录INSERT OVERWRITE DIRECTORY '/tmp/hdfs_out' SELECT a.* FROM invites a WHERE ='';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_messagesCHANGE COLUMN hms hours_minutes_seconds INTCOMMENT '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 ame;--数据验证时，对于简单的不需要聚合的类似SELECT FROM

LIMIT N语句，不需要起MapReduce job，直接通过Fetch task查取数据set sion=more;