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

最新推荐文章于 2025-05-22 10:48:58 发布

原创最新推荐文章于 2025-05-22 10:48:58 发布 · 1.5k 阅读

0 ·

CC 4.0 BY-SA版权

hive 专栏收录该内容

3 篇文章

订阅专栏

本文介绍如何在Hive中创建内外部表、加载数据、进行静态与动态分区操作，并展示了如何修改分区名称及查询特定分区的数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

//内表和外表的区分删外表表下的东西还在内表相反
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’;