Hive基础语法

正则匹配

-- 只匹配数字
SELECT  '123456' rlike '^\\d+$'; -- true
SELECT  '123456abc' rlike '^\\d+$'; -- false

修改表名、列名

-- 修改表名
ALTER TABLE name RENAME TO new_name
-- 增加列
ALTER TABLE name ADD COLUMNS (col_spec[, col_spec ...])
-- 删除列
ALTER TABLE name DROP [COLUMN] column_name
-- 修改列
ALTER TABLE name CHANGE column_name new_name new_type
ALTER TABLE name REPLACE COLUMNS (col_spec[, col_spec ...])

-- 例如
ALTER TABLE ods_im.wifi_friend_add32_d_incr CHANGE dim0 subType string;

建表

-- orc分区表
CREATE TABLE `ods_im.ods_test_d_incr`(
  `srcname` string,
  `notifybody` string,
  `msgtype` string,
  `msgextype` string,
  `biztype` string,
  `resultcode` string,
  `mid` string,
  `error` string,
  `deviceid` string,
  `notifytitle` string,
  `platform` string,
  `manufacturer` string,
  `uid` string,
  `request_time` string,
  `osversion` string,
  `ostype` string,
  `from` string,
  `msgflag` string,
  `settingid` string,
  `lastregistertime` string,
  `msgsubtype` string,
  `version` string,
  `notifytype` string,
  `bizaction` string,
  `createtime` string,
  `serverip` string,
  `importuid` string,
  `devicemodel` string,
  `to` string,
  `locale` string,
  `token` string,
  `upgrade` string,
  `dim1` string,
  `dim2` string,
  `dim3` string,
  `dim4` string,
  `dim5` string,
  `dim6` string,
  `dim7` string,
  `dim8` string,
  `dim9` string,
  `dim10` string,
  `dim11` string,
  `dim12` string,
  `dim13` string)
PARTITIONED BY (
  `pt` string,
  `hour` string,
  `t_source` string)
ROW FORMAT SERDE
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION
  'hdfs://nameservice1/user/hive/warehouse/ods_im.db/ods_michat_push_send_d_incr'

Hive动态分区表

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

create table temp_im.dpartition
(
  id int,
  name string)
partitioned by
  (pt string, 
  hour string, 
  t_source string);

drop table if exists temp_im.temp2;
create table temp_im.temp2(id string,name string,pt string)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY '\t' 
STORED AS TEXTFILE 
LOCATION '/tmp/xy';

insert overwrite table temp_im.dpartition partition(pt,hour,t_source)
select 
id,
name,
pt,
'NA',
'friend_remark' 
from  temp_im.temp2;

-- temp_im.temp2表数据，根据hive默认的\t作为列分割符
-rw-r--r--   2 im_stats supergroup         70 2019-11-20 14:58 /tmp/xy/a.txt
im_stats@datanode03:/data/pub-four-mfs/stats/tools/ods_handler$ cat a.txt
1   tom 2019-11-01
2   jack    2019-11-02
3   peter   2019-11-01
4   cc  2019-11-03