DDL数据定义

DDL数据定义

创建数据库

CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];

查询数据库详情

show databases;//显示数据库
desc database database_name;//显示数据库信息
desc database extend database_name;
use database_name;//切换数据库

修改数据库

alter database database_name set  dbproperties('createtime'='20170830');
//用户可以使用 ALTER DATABASE 命令为某个数据库的 DBPROPERTIES 设置键-值对属性值，
来描述这个数据库的属性信息。

删除数据库

drop database database_name;//删除空数据库
drop database if exists database_name;
//如果删除的数据库不存在，最好采用 if exists 判断数据库是否存在
drop database database_name cascade;
//如果数据库不为空，可以采用 cascade 命令，强制删除

创建表

建表语法

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]
  // 跟外部表配合使用，比如：映射HBase表，然后可以使用HQL对hbase数据进行查询，当然速度比较慢
  [TBLPROPERTIES (property_name=property_value, ...)]  (Note:  only available starting with 0.6.0)
  [AS select_statement]  (Note: this feature is only available starting with 0.5.0.)

字段解释说明

（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；

用户可以用 IF NOT EXISTS 选项来忽略这个异常。

（2）EXTERNAL 关键字可以让用户创建一个外部表，在建表的同时可以指定一个指向实际数据的路径（LOCATION），在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

（3）COMMENT：为表和列添加注释。

（4）PARTITIONED BY 创建分区表

（5）CLUSTERED BY 创建分桶表

（6）SORTED BY 不常用，对桶中的一个或多个列另外排序

（7）ROW FORMAT

DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char]
 [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
 | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, 
property_name=property_value, ...)]

用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 ROW

FORMAT 或者 ROW FORMAT DELIMITED，将会使用自带的 SerDe。在建表的时候，用户还需

要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表

的具体的列的数据。

SerDe 是 Serialize/Deserilize 的简称， hive 使用 Serde 进行行对象的序列与反序列化。

（8）STORED AS 指定存储文件类型

常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列

式存储格式文件）

如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED

AS SEQUENCEFILE。

（9）LOCATION ：指定表在 HDFS 上的存储位置。

（10）AS：后跟查询语句，根据查询结果创建表。

（11）LIKE 允许用户复制现有的表结构，但是不复制数据。

内部表

内部表又称管理表，默认创建不知道表属性的都为内部表。

hive在默认情况下会将这些表的数据存储在有配置项（/user/hive/warehouse）所定义的目录子目录下，当我们删除一个内部表时，hive也会删除这个表中的数据内部表(管理表)不适合与其他工具共享数据。

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

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

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

外部表

hive不会认为其完全拥有这份数据，删除该表并不会删除掉这份数据，不过描述的元数据信息会被删除掉。

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

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

内部表与外部表的使用场景

每天将收集到的网站日志定期的流入到HDFS的文本文件中。在外部表的基础上做大量的统计分析，用到的中间表，结果表使用内部表存储，数据通过SELECT＋INSERT进入内部表

内部表与外部表的互相转换

desc formatted table_name1;//查询表的类型，假定为内部表
alter table table_name1 set tblproperties('EXTERNAL'='TRUE');//修改内部表为外部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');//修改外部表 student2 为内部表

注意：(‘EXTERNAL’=‘TRUE’)和(‘EXTERNAL’=‘FALSE’)为固定写法，区分大小写

修改表

重命名表

alter table table_name rename to new_table_name;//对table_name进行重命名

增加/修改/替换列信息

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name 
column_type [COMMENT col_comment] [FIRST|AFTER column_name]//更新列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT 
col_comment], ...)//增加和替换列
注：ADD 是代表新增一字段，字段位置在所有列后面(partition 列前)，
REPLACE 则是表示替换表中所有字段

DML数据操作

数据导入

装载数据

load data local inpath '数据本地路径/usr/local/data/students.txt'[overwrite] into table student [partition (partcoll=vall,....)];

（1）load data:表示加载数据

（2）local:表示从本地加载数据到 hive 表；否则从 HDFS 加载数据到 hive 表

（3）inpath:表示加载数据的路径

（4）overwrite:表示覆盖表中已有数据，否则表示追加

（5）into table:表示加载到哪张表

（6）student:表示具体的表

（7）partition:表示上传到指定分区

//清空表
truncate table students;
// 加上 local 关键字 可以将Linux本地目录下的文件 上传到 hive表对应HDFS 目录下 原文件不会被删除
load data local inpath '/usr/local/soft/data/students.txt' into table students;
// overwrite 覆盖加载
load data local inpath '/usr/local/soft/data/students.txt' overwrite into table students;

通过查询语句向表内插入数据

创建一张表

hive (default)> create table student_par(
id int, name string
)row format delimited fields terminated by '\t';

基本模式插入

insert overwrite table student_par
select id, name from student where month='201709';

insert into：以追加数据的方式插入到表或分区，原有数据不会删除

insert overwrite：会覆盖表中已存在的数据

注意：insert 不支持插入部分字段

多表插入模式

from student
 insert overwrite table student partition(month='201707')
 select id, name where month='201709'
 insert overwrite table student partition(month='201706')
 select id, name where month='201709';

查询语句中创建并加载数据

create table xxx as SQL语句

create table if not exists student3
as select id, name from student;

insert into table xxxx SQL语句 （没有as）

// 将 students表的数据插入到students2 这是复制 不是移动 students表中的表中的数据不会丢失
insert into table students2 select * from students;

// 覆盖插入 把into 换成 overwrite
insert overwrite table students2 select * from students;

创建表时通过Location指定加载数据路径

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