Hive动态分区(实战篇)

ORC建表语句

CREATE EXTERNAL TABLE dbname.`tablename` (
    `name` STRING COMMENT 'xx',
    `age` STRING COMMENT 'xx'
)
COMMENT 'xx'
PARTITIONED BY (
    `y` STRING COMMENT 'xx',
    `x` STRING COMMENT 'xx'
)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
WITH SERDEPROPERTIES (
    'field.delim' = '\t',
    'line.delim' = '\n',
    'serialization.format' = '\t'
)
STORED AS 
  INPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat';

动态刷新Hive分区

MSCK REPAIR TABLE dbname.tablename;

动态写入数据

set hive.exec.dynamic.partition=true;  
set hive.exec.dynamic.partition.mode=nonstrick;
insert into table dbname.tablename partition(x,y)   
select 
    age,
    name,
    x,
    y
from dbname.tablename;

合并分区小文件

ALTER TABLE xx.xx PARTITION (dt='xxxx-xx-0x') CONCATENATE