hive DDL-Table

Create/Drop/Truncate Table

Create Table

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
    [(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
    [COMMENT table_comment]
    [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
        ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
    [LOCATION hdfs_path]  --外部表存储位置
    [TBLPROPERTIES (property_name=property_value, ...)]  --表属性
    [ROW FORMAT row_format] 

创建db.tb(– mysql不支持)

CREATE TABLE database_name.table_name(col_name data_type)

创建临时表 e.g.

CREATE TEMPORARY TABLE tmp_tb_01 (col_name string);

查看表结构

(字段名,类型)

desc table_name;

格式化查看Table_Type
内/外部表

desc formatted tb_name;

MANAGED_TABLE　　管理表
EXTERNAL_TABLE　　 外部表

显示建表语句

show create table tb_name

删除表

drop table tb_name

清空表内容

truncate table tb_name;

---

创建集合类型表

CREATE TABLE tb_name(  
    name string,  
    array<data_type>,  
    map<primitive_type/data_type>,  
    strut<col_name:data_type,...>,  
    uniontype<data_type,data_type,...>  
    )

e.g.

CREATE TABLE tb_name(
    name string,
    likes ARRAY<string>,
    cards MAP<string,string>,
    address STRUCT<省份:sring,市:string>
    );

分隔符

row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]]
        [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char]
        [LINES TERMINATED BY char]
        [NULL DEFINED AS char]   -- (Note: Available in Hive 0.13 and later)
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]

FIELDS：字段与字段之间的分隔符
COLLECTION：集合之间
MAP KEYS：MAP中key与value之间
LINES：行

e.g.
创建表时指定分隔符

CREATE TABLE tb_name(name string COMMENT 'the name',age COMMENT 'the age')
    ROW FORMAT
        DELIMITED FIELDS TERMINATED BY '\t'
        COLLECTION ITEMS TERMINATED BY ','
        MAP KEYS TERMINATED BY ':'

txt内容e.g.

张三 篮球,乒乓球 1001:中国银行,1002:工商银行 四川,成都

分隔符：
\n 换行　　\r 回车　　\t 跳格

---

复制字段到新表（不复制数据）

create table tb_2 like tb_1;

复制数据创建新表

create table tb_2 as select col_1,col_2 from tb_1;

修改表结构

修改列位置

tip：只适用于新建表，修改字段位置，数据位置不会改变
change
修改name和age位置e.g.

ALTER TABLE tb_name CHANGE name name string AFTER age;

增加列

ALTER TABLE tb_name ADD columns(col_new string);

插入

insert into table tb_1(name,age)
    select name,age from tb_2;

小知识点：
统计时计入为空的值

select count(1) from (select name from tb_user) bieming;