内表 外表区别 以及静态分区和动态分区

//内表 和外表的区分 删外表 表下的东西还在 内表相反
CREATE EXTERNAL TABLE CITY_ex(
province_code int,
province_name string,
city_code int,
city_name string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
LOCATION ‘/user/ljl/city’;

CREATE EXTERNAL TABLE CITY_in(
province_code int,
province_name string,
city_code int,
city_name string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘,’
LINES TERMINATED BY ‘\n’
LOCATION ‘/user/ljl/city’;

---

//建立一个user表，把’/home/bigdata/tanqi/hive/user.txt’倒到user表里
CREATE TABLE user(
uid INT,
city_code INT,
model string,
access string
)
row FORMAT delimited
fields terminated by ‘,’
lines terminated by ‘\n’;

load data local inpath ‘/home/bigdata/tanqi/hive/user.txt’ into table user;

---

//静态分区
CREATE TABLE user_daily(
uid int,
city_code int,
model string,
access string
)
partitioned by (p_date string);

INSERT OVERWRITE TABLE user_daily PARTITION (p_date=’2017-09-13’)
SELECT * FROM user;

//查看 插入分区的情况
show partitions user_daily;

//动态分区
set hive.exec.dynamic.partition.mode=nonstrict;
INSERT OVERWRITE TABLE user_daily PARTITION (p_date)
SELECT *,’2017-09-02’ FROM user
UNION ALL
SELECT *,’2017-09-03’ FROM user
;

//分区里名字的更改
ALTER TABLE user_daily PARTITION (p_date=’2017-01-01’) RENAME TO PARTITION (p_date=’20170101’);

//查 某个分区的内容
select * from user_daily where p_date=’2017-09-02’;