分区字段:月份,城市
1. 分区表(load)
1. 创建一个分区表
CREATE TABLE tb_country(
name STRING,
house_type STRING,
house_area STRING,
region STRING,
floor_str STRING,
direction STRING,
total_price STRING,
square_price STRING,
build_date STRING
)
PARTITIONED BY (month_str STRING COMMENT 'first partition Month',
city_sar STRING COMMENT 'first partition City')
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n';
1.2 加载数据
LOAD DATA LOCAL INPATH '/opt/datas/2nd_bj_05_price.csv' INTO TABLE tb_country PARTITION (month_str="05",city_str="BJ");
1.3 统计BJ5月房数
select
count(1)
from
tb_country
where
month_str="05" and city_str="BJ";
1.4 查看分区信息
show partitions tb_country
2. 分区表(put命令上传数据)
2.1 创建对应目录
dfs -mkdir -p /user/hive/warehouse/db_lianjia.db/tb_country/month_str=05/city_str=XA
dfs -mkdir -p /user/hive/warehouse/db_lianjia.db/tb_country/month_str=06/city_str=XA
2.2 将对应文件上传到指定目录
dfs -put /opt/datas/2nd_xa_05_price.csv /user/hive/warehouse/db_lianjia.db/tb_country/month_str=05/city_str=XA
dfs -put /opt/datas/2nd_xa_06_price.csv /user/hive/warehouse/db_lianjia.db/tb_country/month_str=06/city_str=XA
2.3 修复元数据(方式一)
msck repair table db_lianjia.tb_country
2.3 添加元数据(方式二)
ALTER TABLE tb_country ADD IF NOT EXISTS PARTITION (month_str="05",city_str="SH")