06 hive中建表语法、内部表、外部表

 

---

二、数据表

 

2.1 创建数据表语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name

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

   [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]

   [ROW FORMAT row_format]

   [STORED AS file_format]

   [LOCATION hdfs_path]

 

说明：

[EXTERNAL] ：默认是内部表，如果加上就是外部表

PARTITIONED BY ：如果添加了此关键字，表示当前是个分区表

CLUSTERED BY ：如果添加此关键字，表示是个分桶表

SORTED BY ：按照哪些字段进行排序操作

INTO num_buckets BUCKETS : 要分多少个桶

ROW FORMAT ：表示字段与字段之间的分隔符

STORED AS ： 当前存储的格式 比如 SEQUENCEFILE|TEXTFILE|RCFILE 来指定该表数据的存储格式，hive中，表的默认存储格式为TextFile。

 

LOCATION ： 指定表在hdfs上存储位置

LIKE ：允许用户复制现有的表结构，但是不复制数据。

 

在hive中，一共有4种类型的表：

1. 内部表

2. 外部表

3. 分区表

4. 分桶表

 

2.2 内部表

2.2.1 什么是内部表

未被external修饰的是内部表（managed table）,

内部表又称管理表,内部表数据存储的位置由hive.metastore.warehouse.dir参数决定（默认：/user/hive/warehouse），

删除内部表会直接删除元数据（metadata）及存储数据，因此内部表不适合和其他工具共享数据。

2.2.2 创建按照指定分割符的表

create table if not exists stu2(id int,name string) row format delimited fields terminated by '\t' stored as textfile location '/user/stu2';

2.2.3 创建相同表结构的表

create table stu4 like stu2;

2.2.4 查询表的详细类型

desc [formatted] stu2;

2.2.4 删除表

drop table stu2;

---

 

2.3 外部表

外部表就是 external 关键字创建的表，外部表的数据来自于 HDFS 上的已有的文件，只是将这个文件和外部表的表结构进行映射，建立表结构和数据的关系，如果删除外部表，数据不会被删除，只是删除了表结构。

2.3.1 将外部表和 HDFS 文件建立关联

Load命令用于将外部数据加载到Hive表中

语法：

loaddata [local] inpath '/export/server/datas/student.txt' overwrite | into table student [partition (partcol1=val1,…)];

参数:

1. load data:表示加载数据

2. local:表示从本地加载数据到hive表；否则从HDFS加载数据到hive表

3. inpath:表示加载数据的路径

4. overwrite:表示覆盖表中已有数据，否则表示追加

5. into table:表示加载到哪张表

6. student:表示具体的表

7. partition:表示上传到指定分区

2.3.2 案例

分别创建老师与学生表外部表，并向表中加载数据

源数据如下:

student.txt

01 赵雷 1990-01-01 男 02 钱电 1990-12-21 男 03 孙风 1990-05-20 男 04 李云 1990-08-06 男 05 周梅 1991-12-01 女 06 吴兰 1992-03-01 女 07 郑竹 1989-07-01 女 08 王菊 1990-01-20 女

 

teacher.txt

01 张三 02 李四 03 王五

 

1、创建老师表：

create external table teacher (t_id string,t_name string) row format delimited fields terminated by '\t';

2、创建学生表：

create external table student(s_id string,s_name string,s_birth string, s_sex string) row format delimited fields terminated by '\t';

3、从本地文件系统向表中加载数据

load data local inpath'/export/server/hivedatas/student.txt' into table student;

4、加载数据并覆盖已有数据

load data local inpath'/export/server/hivedatas/student.txt' overwrite  into table student;

 

5、从hdfs文件系统向表中加载数据

需要提前将数据上传到hdfs文件系统，其实就是一个移动文件的操作

cd /export/server/hivedatas hadoop fs-mkdir-p/hivedatas hadoop fs-put teacher.txt/hivedatas/ load data inpath'/hivedatas/teacher.txt' into table teacher;

 

注意,如果删掉student表，hdfs的数据仍然存在，并且重新创建表之后，表中就直接存在数据了,因为我们的student表使用的是外部表，drop table之后，表当中的数据依然保留在hdfs上面了

 

三、内部表和外部表之间的转换

1. 查询表的类型

desc formatted hive_map;

1. 修改内部表hive_map为外部表

alter table hive_map set tblproperties('EXTERNAL'='TRUE');

 

  3.修改外部表hive_map为内部表

alter table hive_map set tblproperties('EXTERNAL'='FALSE');

 

 

注意：('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法，区分大小写！