hive建表、内部表和外部表_hivesql create as select

Hive建表

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] 
   // 指定Hive储存格式：textFile、rcFile、SequenceFile 默认为：textFile
  [STORED AS file_format]
   
  | STORED BY 'storage.handler.class.name' [ WITH SERDEPROPERTIES (...) ]  (Note:  only available starting with 0.6.0)
  ]
  // 指定储存位置
  [LOCATION hdfs_path]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

建表1：全部使用默认建表方式

create table students
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; // 必选，指定列分隔符 
1
2
3
4
5
6
7
8
9

建表2：指定location （这种方式也比较常用）

create table students2
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input1'; // 指定Hive表的数据的存储位置，一般在数据已经上传到HDFS，想要直接使用，会指定Location，通常Locaion会跟外部表一起使用，内部表一般使用默认的location
1
2
3
4
5
6
7
8
9
10

建表3：指定存储格式

create table students3
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS rcfile; // 指定储存格式为rcfile，inputFormat:RCFileInputFormat,outputFormat:RCFileOutputFormat，如果不指定，默认为textfile，注意：除textfile以外，其他的存储格式的数据都不能直接加载，需要使用从表加载的方式。
1
2
3
4
5
6
7
8
9
10

建表4：create table xxxx as select_statement(SQL语句) (这种方式比较常用)

create table students4 as select * from students2;
1

建表5：create table xxxx like table_name 只想建表，不需要加载数据

create table students5 like students;
1

Hive 内部表（Managed tables）vs 外部表（External tables）

建表：

// 内部表
create table students_internal
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input2';

// 外部表
create external table students_external
(
    id bigint,
    name string,
    age int,
    gender string,
    clazz string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/input3';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

加载数据：

hive> dfs -put /usr/local/soft/data/students.txt /input2/;
hive> dfs -put /usr/local/soft/data/students.txt /input3/;
1
2

删除表：

hive> drop table students_internal;
Moved: 'hdfs://master:9000/input2' to trash at: hdfs://master:9000/user/root/.Trash/Current
OK
Time taken: 0.474 seconds
hive> drop table students_external;
OK
Time taken: 0.09 seconds
hive> 
1
2
3
4
5
6
7
8

可以看出，删除内部表的时候，表中的数据（HDFS上的文件）会被同表的元数据一起删除

删除外部表的时候，只会删除表的元数据，不会删除表中的数据（HDFS上的文件）

一般在公司中，使用外部表多一点，因为数据可以需要被多个程序使用，避免误删，通常外部表会结合location一起使用

外部表还可以将其他数据源中的数据 映射到 hive中，比如说：hbase，ElasticSearch…

设计外部表的初衷就是 让 表的元数据 与 数据 解耦