hive学习（二）

一、表的DDL操作

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]   row format delimited fields terminated by “分隔符”
[collection items terminated by ':'] (指定struct数据类型内的分隔符)
[map keys terminated by ':'] (指定map键值对的分隔符)
[STORED AS file_format] 
[LOCATION hdfs_path]

以下是语法的解释

2. 创建内部表

1）直接建表

create table if not exists teacher(
id int, 
name string
)
row format delimited fields terminated by '\t'	
stored as textfile;//默认是文本文件

2）查询建表法

//从已有表中获取信息建表
create table if not exists teacher1 as select id, name from teacher;

3）like建表法

create table if not exists teacher2 like teacher;

扩：查询表类型

hive > desc formatted teacher;