hive DDL

🧙 Questions

• 库操作
• 建表语句
• 表操作

☄️ Ideas

库操作

• 查看所有数据库

show databases;

• 创建ispong_db数据库

create database if not exists ispong_db;

• 删除数据库

drop database if exists ispong_db;

• 暴力直接删除db

drop database if exists ispong_db cascade;

• 存在表或者引用关系不可直接删除

drop database if exists ispong_db restrict;

建表语句

配置表的分隔符和换行符
ROW FORMAT
DELIMITED FIELDS TERMINATED BY ‘\001’
LINES TERMINATED BY ‘\n’

• 清理表

truncate table ispong_db.users_rcfile;

• 详细看表结构

desc formatted ispong_db.users_avro;

-- 未开启动态分区
insert into ispong_db.users_textfile PARTITION (sex='男') values ('ispong\nispong\nispong', 18),('zhangsan', 13);

-- 开启动态分区
SET hive.exec.dynamic.partition=true;
insert into ispong_db.users_textfile PARTITION (sex) values ('ispong\nispong\nispong', 18, '男'),('zhangsan', 13, '女');

set hive.exec.dynamic.partition.mode=nonstrict
insert into ispong_db.users_textfile PARTITION (sex) values ('ispong\nispong\nispong', 18, 'M'),('zhangsan', 13, 'F');

-- 查询数据
select * from ispong_db.users_orc;

• 查看hive在hadoop中的文件位置

hadoop fs -ls /user/hive/warehouse/ispong_db.db/users_jsonfile/sex=男

TEXTFILE表

默认分隔符 ‘\001’ 换行符 ‘\n’

CREATE TABLE IF NOT EXISTS ispong_db.users_textfile4(
    username   STRING  COMMENT '姓名',
    age        INT     COMMENT '年龄'
)
COMMENT '用户表'
PARTITIONED BY ( sex STRING COMMENT '性别')
STORED AS TEXTFILE;

实际数据

ispong^A18
zhangsan^A13

AVRO表

AVRO不支持timestamp类型

CREATE TABLE IF NOT EXISTS ispong_db.users_avro(
    username   STRING  COMMENT '姓名',
    age        INT     COMMENT '年龄'
)
COMMENT '用户表'
PARTITIONED BY ( sex STRING COMMENT '性别')
STORED AS AVRO;

CREATE TABLE IF NOT EXISTS ispong_db.users5
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
STORED AS 
  INPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerInputFormat'
  OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.avro.AvroContainerOutputFormat'
TBLPROPERTIES (
  'avro.schema.literal'='{
    "type":"record",
    "name":"User",
    "fields":[
      {"name":"username","type":"string"},
      {"name":"age","type":"int"}
    ]
  }'
)

实际数据
每个文件都包含表的信息
数据会被二次压缩

Obj^A^B^Vavro.schemaÐ^C{"type":"record","name":"users_avro","namespace":"ispong_db","doc":"ç<94>¨æ<88>·è¡¨","fields":[{"name":"username","type":["null","string"],"doc":"å§<93>å<90><8d>","default":null},{"name":"age","type":["null","int"],"doc":"å¹´é¾<84>","default":null}]}^@îñ^T^@^Zfæä^Whÿô^@ó^M¶^D,^B^Lispong^B$^B^Pzhangsan^B^Zîñ^T^@^Zfæä^Whÿô^@ó^M¶

RCFILE表

列式存储

CREATE TABLE IF NOT EXISTS ispong_db.users_rcfile(
    username   STRING  COMMENT '姓名',
    age        INT     COMMENT '年龄'
)
COMMENT '用户表'
PARTITIONED BY ( sex STRING COMMENT '性别')
STORED AS RCFILE;

实际数据

RCF^A^@^@^@^@^A^\hive.io.rcfile.column.number^A2Ê^YIAjUc^Uà^Wx<94> ×^H-^@^@^@^Q^@^@^@   ^@^@^@  ^A^F^F^A^F^B^B^A^Bispong18

ORC表

CREATE TABLE IF NOT EXISTS ispong_db.users_orc(
    username   STRING  COMMENT '姓名',
    age        INT     COMMENT '年龄'
)
COMMENT '用户表'
PARTITIONED BY ( sex STRING COMMENT '性别')
STORED AS ORC;

实际数据

ORC^Q^@^@
^F^R^D^H^AP^@:^@^@ãRäbe^@^A!    ^NF%!.¶Ìâ<82>ü¼t!(-Á^SÀ^@^@+^@^@
^S
^C^@^@^@^R^L^H^A^R^F^H$^P$^X$P^@^M^@^@ispong^G^@^@F^@`^G^@^@N^@$V^@^@ãbã`^S`<90>àæ^BÑ<8c>^R
`<9a>IB^BH3^Bù<9c>@<9a> H³<81>ùL^RlBL^\^L@Ì^DÂ^@V^@^@ãÒãbá`^L`à<92>à`T^RâbË,.ÈÏK^W<82>Ò^R<@^Y^^^NF!6^N^U^A^U    <95>^@^F^@º^@^@ã`^Vèd<94>ââ`^Vp<96>^PUÐÓ`T^Råà^Qbbd<92>â(-N-ÊKÌM<95>bNLOUbâ`^Gbf^CF+^V^NÆ^@^F+  ^NF%!.¶Ìâ<82>ü¼t!(-Á^C<94>áá`^TbãP^QP<91>P      `p<98>à^G^@^H`^P^A^X<80><80>^P"^B^@^L(.0^D<82>ô^C^CORC^W

PARQUET表

CREATE TABLE IF NOT EXISTS ispong_db.users_parquet(
    username   STRING  COMMENT '姓名',
    age        INT     COMMENT '年龄'
)
COMMENT '用户表'
PARTITIONED BY ( sex STRING COMMENT '性别')
STORED AS PARQUET;

真实数据

PAR1^U^@^U ^U ,^U^B^U^@^U^F^U^H^\^X^Fispong^X^Fispong^V^@(^Fispong^X^Fispong^@^@^@^B^@^@^@^C^A^F^@^@^@ispong^U^@^U^T^U^T,^U^B^U^@^U^F^U^H^\^X^D^R^@^@^@^X^D^R^@^@^@^V^@(^D^R^@^@^@^X^D^R^@^@^@^@^@^@^B^@^@^@^C^A^R^@^@^@^U^B^Y<H^Khive_schema^U^D^@^U^L%^B^X^Husername%^@L^\^@^@^@^U^B%^B^X^Cage^@^V^B^Y^\^Y,&^H^\^U^L^Y5^H^@^F^Y^X^Husername^U^@^V^B^V<8a>^A^V<8a>^A&^H<^X^Fispong^X^Fispong^V^@(^Fispong^X^Fispong^@^Y^\^U^@^U^@^U^B^@^@^@&<92>^A^\^U^B^Y5^H^@^F^Y^X^Cage^U^@^V^B^Vn^Vn&<92>^A<^X^D^R^@^@^@^X^D^R^@^@^@^V^@(^D^R^@^@^@^X^D^R^@^@^@^@^Y^\^U^@^U^@^U^B^@^@^@^Vø^A^V^B^@^Y^\^X^Qwriter.model.name^X^N2.1.1-cdh6.2.0^@^X8parquet-mr version 1.9.0-cdh6.2.0 (build ${buildNumber})^Y,^\^@^@^\^@^@^@;^A^@^@PAR1

JSONFILE表

CREATE TABLE IF NOT EXISTS ispong_db.users_jsonfile(
    username   STRING  COMMENT '姓名',
    age        INT     COMMENT '年龄'
)
COMMENT '用户表'
PARTITIONED BY ( sex STRING COMMENT '性别')
STORED AS JSONFILE;

{"username":"ispong","age":18}

表操作

-- 简单版
desc ispong_db.users;

-- 格式化版
desc formatted ispong_db.users;

-- 未格式化版
desc extended ispong_db.users;

-- 添加字段
alter table ispong_db.users add columns (sex string comment 'comment for sex');

-- 修改备注
alter table ispong_db.users change sex sex STRING COMMENT 'comment for new sex';

-- 修改字段类型
alter table ispong_db.users change sex sex VARCHAR(100);

-- 修改字段顺序 不可以是分区字段
alter table ispong_db.users change sex sex STRING after username;
alter table ispong_db.users change sex sex VARCHAR(100) first;

-- 修改表名
alter table ispong_db.users rename to ispong_db.users_new_name;

-- 删除sex字段，最好从后往前删除字段
alter table ispong_db.users replace columns ( username STRING, age INT, birth DATE );

建视图

create view users as select user1.id ,username ,age from user1 left join user2 on user1.id=user2.id

CSV表

CREATE TABLE ispong_table(
  username String,
  age int
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  "separatorChar"="\t",
  "quoteChar"="‘",
  "escapeChar"="\\"
);

主键表

ENABLE NOVALIDATE 启用约束，仅对新数据约束
ENABLE VALIDATE 启用约束，对所有数据约束
实际hive的主键并未实现，只是保存了一个状态而已

CREATE TABLE IF NOT EXISTS ispong_db.users(
    username   STRING  COMMENT 'comment for username',
    age        INT     COMMENT 'comment for age',
    birth      DATE    COMMENT 'comment for birth',
    PRIMARY KEY (username) DISABLE NOVALIDATE RELY 
)
COMMENT 'comment for users'
STORED AS TEXTFILE;

🔗 Links

• hive website
• hive ddl docs
• create table docs
• create csv table