mysql 外部表_DDL-内部表&外部表

创建表的语法格式

摘自Hive 官方文档，写的相当清晰，可以当手册来看了。

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name -- (Note: TEMPORARY available in Hive 0.14.0 and later) [(col_name data_type [COMMENT col_comment], ... [constraint_specification])]

[COMMENT table_comment]

[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]

[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]

[SKEWED BY (col_name, col_name, ...) -- (Note: Available in Hive 0.10.0 and later)] ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)

[STORED AS DIRECTORIES]

[

[ROW FORMAT row_format]

[STORED AS file_format]

| STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] -- (Note: Available in Hive 0.6.0 and later) ]

[LOCATION hdfs_path]

[TBLPROPERTIES (property_name=property_value, ...)] -- (Note: Available in Hive 0.6.0 and later) [AS select_statement]; -- (Note: Available in Hive 0.5.0 and later; not supported for external tables)

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name

LIKE existing_table_or_view_name

[LOCATION hdfs_path];

data_type

: primitive_type

| array_type

| map_type

| struct_type

| union_type -- (Note: Available in Hive 0.7.0 and later)

primitive_type

: TINYINT

| SMALLINT

| INT

| BIGINT

| BOOLEAN

| FLOAT

| DOUBLE

| DOUBLE PRECISION -- (Note: Available in Hive 2.2.0 and later) | STRING

| BINARY -- (Note: Available in Hive 0.8.0 and later) | TIMESTAMP -- (Note: Available in Hive 0.8.0 and later) | DECIMAL -- (Note: Available in Hive 0.11.0 and later) | DECIMAL(precision, scale) -- (Note: Available in Hive 0.13.0 and later) | DATE -- (Note: Available in Hive 0.12.0 and later) | VARCHAR -- (Note: Available in Hive 0.12.0 and later) | CHAR -- (Note: Available in Hive 0.13.0 and later)

array_type

: ARRAY < data_type >

map_type

: MAP < primitive_type, data_type >

struct_type

: STRUCT < col_name : data_type [COMMENT col_comment], ...>

union_type

: UNIONTYPE < data_type, data_type, ... > -- (Note: Available in Hive 0.7.0 and later)

row_format

: DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]

[MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]

[NULL DEFINED AS char] -- (Note: Available in Hive 0.13 and later) | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

file_format:

: SEQUENCEFILE

| TEXTFILE -- (Default, depending on hive.default.fileformat configuration) | RCFILE -- (Note: Available in Hive 0.6.0 and later) | ORC -- (Note: Available in Hive 0.11.0 and later) | PARQUET -- (Note: Available in Hive 0.13.0 and later) | AVRO -- (Note: Available in Hive 0.14.0 and later) | JSONFILE -- (Note: Available in Hive 4.0.0 and later) | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

constraint_specification:

: [, PRIMARY KEY (col_name, ...) DISABLE NOVALIDATE ]

[, CONSTRAINT constraint_name FOREIGN KEY (col_name, ...) REFERENCES table_name(col_name, ...) DISABLE NOVALIDATE

内部表/管理表(Internal/Managed Tables)

内部表和MySQL中的表概念类似，每个内部表物理上对应HDFS上的一个目录，表内的所有数据都保存在这个目录下。hive-site.xml 中${hive.metastore.warehouse.dir}决定了仓储的根目录，default数据库的数据也存放在此目录下。假设我们新建了一个数据库yitian，数据库目录则为${hive.metastore.warehouse.dir}/yitian.db/，若在此目录下建表名为crew_info，则表的路径为${hive.metastore.warehouse.dir}/yitian.db/crew_info/。Hive默认创建的就是内部表。

创建内部表

准备数据crew_info.data：

1,Monkey D Luffy,蒙奇·D·路飞

2,Roronoa Zoro,罗罗诺亚·索隆

3,Nami,娜美

由于数据按,分隔，需要用row format指定分隔符。

create table yitian.crew_info (

id bigint

,english_name string

,chinese_name string

)

row format delimited fields terminated by ','

;

查看HDFS目录，可以看到生成了一个对应的文件夹：

$ hadoop fs -ls hdfs://localhost:9000/user/hive/warehouse/yitian.db/

Found 4 items

drwxr-xr-x - yitian supergroup 0 2019-05-20 17:02 hdfs://localhost:9000/user/hive/warehouse/yitian.db/crew_info

将数据导入表内(我是在数据文件所在的目录启动的Hive Cli，如果路径不对，可以改成本地的绝对路径)：

load data local inpath './crew_info.data' into table crew_info ;

查询表内数据：

hive> select * from crew_info;

OK

1 Monkey D Luffy 蒙奇·D·路飞

2 Roronoa Zoro 罗罗诺亚·索隆

3 Nami 娜美

Time taken: 1.898 seconds, Fetched: 10 row(s)

删除内部表：

drop table yitian.crew_info;

再次查看HDFS，目录也将会被删除。

更改表名

ALTER TABLE table_name RENAME TO new_table_name;

Hive 0.6之前，更改内部表的表名，并不会更改HDFS上的存储位置。从Hive 0.6开始，如果内部表创建的时候没有手动指定LOCATION，并且路径处于自己的数据库下，则更改表名会自动更改HDFS上的存储目录。

TRUNCATE TABLE

TRUNCATE用于删除所有的行，表结构依然保留，只能作用于内部表， 不能作用于外部表。DROP则是直接删除表，包括元数据和HDFS上的数据。

TRUNCATE TABLE table_name [PARTITION partition_spec];

partition_spec:

``: (partition_column = partition_col_value, partition_column = partition_col_value, ...)

外部表

外部表通过指定EXTERNAL关键字创建，并用LOCATION指定存储位置。外部表的数据存储在LOCATION指定的目录下。该目录添加了新文件，只要表定义格式和数据格式一致，Hive就能读取到该文件；如果删除了外部表，这个HDFS上的数据却不会被删除。

创建一个外部表，指定存储位置/user/hive/warehouse/ext/：

create external table yitian.external_crew_info (

id bigint

,english_name string

,chinese_name string

)

row format delimited fields terminated by ','

location '/user/hive/warehouse/ext/'

;

查看HDFS，确实生成了ext目录。

$ hadoop fs -ls hdfs://localhost:9000/user/hive/warehouse/

Found 7 items

drwxr-xr-x - yitian supergroup 0 2019-05-22 22:04 hdfs://localhost:9000/user/hive/warehouse/ext

drwxr-xr-x - yitian supergroup 0 2019-05-22 21:53 hdfs://localhost:9000/user/hive/warehouse/yitian.db

将数据文件复制到ext目录下：

$ hadoop fs -put crew_info.data hdfs://localhost:9000/user/hive/warehouse/ext/

$ hadoop fs -ls hdfs://localhost:9000/user/hive/warehouse/ext/

Found 1 items

-rw-r--r-- 1 yitian supergroup 265 2019-05-22 22:06 hdfs://localhost:9000/user/hive/warehouse/ext/crew_info.data

Hive中查看表内数据：

hive> select * from yitian.external_crew_info;

OK

1 Monkey D Luffy 蒙奇·D·路飞

2 Roronoa Zoro 罗罗诺亚·索隆

3 Nami 娜美

Time taken: 0.211 seconds, Fetched: 10 row(s)

删除外部表：

drop table yitian.external_crew_info;

查看HDFS，数据文件依然存在，不会被删除：

$ hadoop fs -ls hdfs://localhost:9000/user/hive/warehouse/ext/

Found 1 items

-rw-r--r-- 1 yitian supergroup 265 2019-05-22 22:06 hdfs://localhost:9000/user/hive/warehouse/ext/crew_info.data

如果外部表的结构或者分区发生了改变，需要使用MSCK REPAIR TABLE table_name;来更新元数据信息。

内部表vs外部表

内部表通过Hive指令对数据进行全生命周期管理，如果这份数据彻彻底底地想交给Hive来管理，就可以选用内部表。如果这份数据不仅仅有Hive来访问，还有其他工具想共用这份数据的话，可以选用外部表。

二者主要有以下几点区别：最重要的，对内部表而言DROP可以删除内部表的元数据和原始数据；对外部表而言，DROP只会删除元数据，无法删除HDFS上的原始数据

ARCHIVE/UNARCHIVE/TRUNCATE/MERGE/CONCATENATE这些指令仅对内部表有效

ACID/Transactional只适用于内部表

查询结果缓存(Query Results Caching)只适用于内部表

外部表只支持RELY constraint

Materialized View 的一些特性只适用于内部表

参考资料：