Hive知识点（四）--表DDL操作

1.DDL

CREATE TABLE table_name
[(col_name data_type [column_constraint_specification] [COMMENT col_comment], … [constraint_specification])]
[
[ROW FORMAT row_format]
AS select_statement

CREATE TABLE table_name
LIKE existing_table_or_view_name
[LOCATION hdfs_path];

row_format

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

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

column_constraint_specification:

[ PRIMARY KEY|UNIQUE|NOT NULL|DEFAULT [default_value]|CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

default_value:

[ LITERAL|CURRENT_USER()|CURRENT_DATE()|CURRENT_TIMESTAMP()|NULL ]

constraint_specification:

[, PRIMARY KEY (col_name, …) DISABLE NOVALIDATE RELY/NORELY ]
[, PRIMARY KEY (col_name, …) DISABLE NOVALIDATE RELY/NORELY ]
[, CONSTRAINT constraint_name FOREIGN KEY (col_name, …) REFERENCES table_name(col_name, …) DISABLE NOVALIDATE
[, CONSTRAINT constraint_name UNIQUE (col_name, …) DISABLE NOVALIDATE RELY/NORELY ]
[, CONSTRAINT constraint_name CHECK [check_expression] ENABLE|DISABLE NOVALIDATE RELY/NORELY ]

1.表的创建

create table 表名(字段 字段类型,...);  #创建表
create table 表名(字段 字段类型,...)row format delimited fileds terminated by '\t'; #创建表，行与行通过Tab键（\t）隔开
create table 表名 as select * from 表名1;  #创建和表名1相同的字段+数据
create table 表名 as select 字段1,字段2,... from 表名1;  #创建子表，企业中常见，不同全表扫描
create table 表名 like 表明1;  #创建和表名1相同的字段，不导入数据，和as不同

如：
create table ruozedata_emp_external(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int
) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
location '/hive/external/emp/';

row format    #行的格式信息
delimited fields terminate by '\t'   #字段与字段之间的分隔符  这里指定用\t   [表示字段与字段之间是用Tab进行分割的]

2.表的删除

drop table 表名; #删除表
truncate table 表名;  #删除表数据，表+字段都存在，删除数据，相当于对表进行初始化，只有字段

2.表的查看

desc 表名;
desc extended 表名;  #查看表
desc formatted 表名;  #查看表信息，推荐使用