hive建表语法，内部表与外部表_create external table if not exists

一、hive建表语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] employee_external (    //EXTERNAL关键字添加为外部表
    name string,
    work_place ARRAY<string>,
    sex_age STRUCT<sex:string,age:int>,
    skills_score MAP<string,int>,
    depart_title MAP<STRING,ARRAY<STRING>>
)
COMMENT 'This is an external table'                            //对表的注释
ROW FORMAT DELIMITED                                      
FIELDS TERMINATED BY '|'                                       //如何分隔列（字段）
COLLECTION ITEMS TERMINATED BY ','                             //如何分隔集合和映射
MAP KEYS TERMINATED BY ':'
STORED AS TEXTFILE                                             //文件存储格式
LOCATION '/user/root/employee';                                // 数据存储路径（HDFS)
1
2
3
4
5
6
7
8
9
10
11
12
13
14

二、hive外部表

1、准备测试数据、放入虚拟机/data目录下

2、创建外部表

create external table if not exists test1(
     id int,
     name string
     )
     row format delimited
     FIELDS TERMINATED BY ','；
1
2
3
4
5
6

3、装载数据

load data local inpath '/data/test.txt' overwrite into table test1;
1

4、查询test1表

5、查询test1在hdfs上的位置

6、通过desc命令查看表的状态

7、删除表，查看表在hdfs上的状态

test1还在！

8、重新创建外部表test3，通过location关键字指向test1

9、查询test3

总结：外部表的删除，只会删除元数据，并不会删除数据本身

三、hive内部表（管理表）

1、创建内部表

create table if not exists test2(
    >      id int,
    >      name string
    >      )
    >      row format delimited
    >      FIELDS TERMINATED BY ',';
1
2
3
4
5
6

2、装载数据

load data local inpath '/data/test.txt' overwrite into table test2;
1

3、查询test2表

4、查询test2在hdfs上的位置

5、通过desc命令查看表的状态

6、删除表，查看表在hdfs上的状态

test2表不在了

总结：内部表的删除会将数据一起删除

四、内部表与外部表的区别

1.未被external修饰的是内部表【managed table】，被external修饰的为外部表【external table】。

2.内部表数据由Hive自身管理，外部表数据由HDFS管理。

3.内部表数据存储在hive.metastore.warehouse.dir【默认:/user/hive/warehouse】，外部表数据存储位置由用户自己决定。

4.删除内部表会直接删除元数据【metadata】及存储数据，删除外部表仅仅删除元数据，HDFS上的文件不会被删除

五、内外部表的互相转换

在工作中，推荐使用外部表，如果操作失误，数据还可以保留，如果建表的时候建错了，可以通过以下命令将内部表转换成外部表

根据上述操作 test3 现在为外部表

1、外部表转换为内部表

先查看一下test3表的状态

执行命令
alter table test3 set tblproperties('EXTERNAL'='false');

再次查看test3状态

2、内部表转换外部表

执行命令
alter table test3 set tblproperties('EXTERNAL'='true');
即可