fengsong97用到的hive

最新推荐文章于 2020-12-19 22:46:42 发布

fengsong97

最新推荐文章于 2020-12-19 22:46:42 发布

阅读量2w

点赞数

分类专栏：大数据

本文链接：https://blog.youkuaiyun.com/qq_14880455/article/details/103285964

版权

大数据专栏收录该内容

4 篇文章

订阅专栏

本文深入探讨Hive内部表与外部表的区别，包括数据管理、建表方式及查询方法。同时，解析Hive分区表的创建与使用，以及如何进行有效的数据建模，如星型、雪花型和星座型模型。

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

hive介绍

hive 内外部表

hive 内部表 MANAGED_TABLE, 是被hive完全管理的表, 完全管理元数据和数据 (默认和建议创建为内部表),

数据会被放到特定的路径下 hdfs://nameservice/user/hive/warehouse/default.db/user

这个特定路径看配置: Hive的${HIVE_HOME}/conf/hive-site.xml 里的 hive.metastore.warehouse.dir 属性指向的就是Hive表数据存放的路径

简单建表示例

hive> create table default.user (id int, name string ) ;

hive 外部表 EXTERNAL_TABLE ,一般先有数据,再建表用于关联原来数据的表, hive只管理元数据, 不能完全管理数据 ( insert into/overwrite 表时数据相应改变, 但直接drop 表时数据会保留在hdfs 路径里)

简单建表示例

hive>create external table default.user_e ( id int , name string )

>row format delimited

>fields terminated by '\t'

>location '文件的hdfs路径';

查询是内部表还是外部表

hive> describe formatted user;

返回:

...

Table Type:

...

...

MANAGED_TABLE / EXTERNAL_TABLE

...

内部表和外部表的区别

看见建表时是否有EXTERNAL关键字
内部表是 hive完全管理的表, 完全管理元数据和数据
外部表一般是先有数据,再建表用于关联原来数据的表, hive完全管理元数据, 不完全管理数据 ( insert into/overwrite 表时数据相应改变, 但直接drop 表时数据会保留在hdfs 路径里 )

以下是官网中关于内外部表的介绍 , 不难理解：

A table created without the EXTERNAL clause is called a managed table because Hive manages its data.
Managed and External Tables
By default Hive creates managed tables, where files, metadata and statistics are managed by internal Hive processes. A managed table is stored under the hive.metastore.warehouse.dir path property, by default in a folder path similar to /apps/hive/warehouse/databasename.db/tablename/. The default location can be overridden by the location property during table creation. If a managed table or partition is dropped, the data and metadata associated with that table or partition are deleted. If the PURGE option is not specified, the data is moved to a trash folder for a defined duration.
Use managed tables when Hive should manage the lifecycle of the table, or when generating temporary tables.
An external table describes the metadata / schema on external files. External table files can be accessed and managed by processes outside of Hive. External tables can access data stored in sources such as Azure Storage Volumes (ASV) or remote HDFS locations. If the structure or partitioning of an external table is changed, an MSCK REPAIR TABLE table_name statement can be used to refresh metadata information.
Use external tables when files are already present or in remote locations, and the files should remain even if the table is dropped.
Managed or external tables can be identified using the DESCRIBE FORMATTED table_name command, which will display either MANAGED_TABLE or EXTERNAL_TABLE depending on table type.
Statistics can be managed on internal and external tables and partitions for query optimization.

Hive官网介绍：
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DDL#LanguageManualDDL-DescribeTable/View/Column