知道hive的这些ddl和dml操作语句，离从一个小白变大神就不远了

DDL：

库操作：

创建库：

create database 库名；

create database if not exists 库名；

查看库的列表：

show databases;

show databases like 's*'; 查看以s开头的数据库；

使用库:

use database;

查看正在使用的库：

select current_database(); --是一个函数

查看库信息：

desc database 库名；

删除库：

drop database 库名； 只能删除空库，里面没有表的

drop database if exists 库名；

drop database 库名 cascade; 删除库 非空库

drop database 库名 restrict; 默认情况下的删除库的操作

修改库：基本不用

if not exists

if exists 避免错误 同样适用于表的操作

表的操作：

创建表

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]

[STORED as file_format]

[LOCATION hdfs_path]

1) [EXTERNAL] 关键字：是否创建外部表 不加创建的是内部表 加上创建的是外部表

2) [PARTITIONED BY （col_name data_type [COMMENT col_comment],...]

PARTITIONED BY:指定分区字段

注意：分区字段一定不是建表字段中的某一个或多个 是一个全新的字段

3）[CLUSTERED BY (col_name,col_name,...) 指定分桶字段

[SORTED BY (col_name [ASC|DESC],...)] INTO num_buckets BUCKETS] 指定排序字段

注意：分桶字段一定是建表语句中的某一个字段或多个字段

排序规则指定的是在桶一个分桶内的排序规则

INTO num_buckets BUCKETS 指定分桶个数

4)[ROW FORMAT row_format] 指定分隔符

delimited fields terminated by '' 指定列分隔符

lines terminated by '' 指定行分隔符

5）[STORED as file_format] 指定最终表数据的存储格式

textfile 文本格式 默认的格式（默认）

rcfile 行列结合各式

parquet 压缩格式

6）[LOCATION hdfs_path] 指定最终表数据的存储格式

不指定这个默认在我们配置的路径下存储 /user/hive/warehouse

指定了则存储在指定的路径下

建表

1）内部表

create table if not exists manage_table

(id int

,age int

)

row format delimited fields terminated by ','

;

2)外部表

create external table if not exists external_table

(id int

,age int

,name string

)

row format delimited fields terminated by ','

;

3)分区表

create table if not exists ptn_table

(id int

,age int

)

partitioned by (city string)

row format delimited fields terminated by ','

;

分区表中的数据存储在不同的目录下

分区表在添加数据之前先添加分区

alter table pnt_table add if not exists partition(city='beijing');

alter table pnt_table add if not exists partition(city='wuhan');

add partition 添加分区

4）分桶表 ------hadoop中的分区的概念 默认分区按照hash分区

create table if not exists buk_table

(id int

,age int

,name string

)

clustered by (id)

sorted by (age desc)

into 4 buckets

row format delimited fields terminated by ','

;

clustered by (id)：相当于指定map端发出的key id.hashCode()

into 4 buckets: 相当于指定numreducetasks id.hashCode() %4 :对id取hashcode值%4

分桶表的作用：

(1）获得更高的查询处理效率。桶为表加上了额外的结构，Hive在处理有些查询时候利用这个结构。具体而言，连接两个在（包含连接列的）相同列上划分了桶的表，可以使用Map端连接（Map join）高效的实现。比如Join操作。对于Join操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值得桶进行Join操作就可以，可以大大减少Join的数据量。

(2）使取样（Sampling）更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的 一小部分数据上试运行查询，会带来很多方便。

5）复制表 like关键字

create table copy_table01 like external_table;

在复制表的时候复制出来的表到底是内部表还是外部表跟你在复制的时候是否指定了external关键字有关

注意：复制表 只能复制表结构并不能复制表数据

6） 创建查询表

create table as select...from table;

7）查看表

show tables;

show tables in 库名； 查看某个库的表

show tables like 's*'

查看表信息

desc 表名：显示表的字段信息

desc formatted 表名：格式化显示表的详细信息

desc extended 表名：显示表的详细信息

8）删除表

drop table if exists 表名

清空表

truncate table 表名；

仅仅是清空表中的数据，表结构仍然保留

9）修改表 alter

修改表名字

alter table 表名 rename to 新表名

修改表字段信息

(1）添加字段

alter table 表名 add columns();

例如：alter table copy_table01 add columns(sex string,department string);

所有的新的字段一定要指定类型

(2）修改字段 修改字段类型

alter table 表名 change 原始字段 新字段 新字段类型

例如：alter table copy_table01 change age age01 string

alter table copy_table01 change sex sex int 类型转换错误

注意：在进行修改表字段定义的时候一定要注意表字段类型之间的匹配，只能小类型转大类型

string类型相对于数值类型属于大类型

但是hive1.2.2中没有限制 各种类型之间都可以相互修改

3)替换列 （替换表的所有字段为新的字段） 不常用

alter table copy_table01 replace columns(id int,name string)

修改表分区信息

(1）添加分区

alter table ptn_tbale add if not exists partition (city ='beijing')

添加分区的过程中还可以指定分区的存储路径

alter table ptn_tbale add if not exists partition (city ='shenzhen')

lacation '/user/shenzhen'

添加分区的时候指定路径则分区存储在指定路径下，没有指定则存在默认路径下

(2）修改分区 修改分区的存储路径

alter table ptn_tbale set partition(city='shenzhen') lacation '/user/shenzhen'

(3）删除分区

alter table ptn_tbale drop if exists partition (city ='beijing')

10）其他辅助命令

查看建表语句：

show create table 表名；

查看hive函数列表

show functions ；

查看表的分区信息 一定针对分区表

show partitions table_name;

show partitions table_name partition(city='beijing');

DML

1.数据加载：load

语法：

load database

hdfs: inpath

本地： local inpath

'/home/hadoop' into table 表名

1）从本地加载数据

load data local inpath '/home/hadoop/student.txt' into table student_base ;

从本地加载数据相当于复制的工作 将本地的文件复制到hive的默认路径下

2）从hdfs加载

hive 原始的数据就存在hdfs上 如果在建一个内部表 复制？移动？

load data inpath '/student.txt' into table student_base ;

同在hdfs上的数据有必要存储两份吗？没有 所以这个操作是移动的操作

一般情况下像这种情况应该建内部表还是外部表？

如果是内部表？删除数据的时候数据和元数据会一并删除

如果是外部表删除数据的时候元数据会被删除，而原始数据能暴力流

这种情况（hdfs上原始存储的数据（公共数据）建外部表

数据加载成功之后，发现在/user/myhive/bd1803/bd1803.db/student_base目录下有两个文件

第一次加载从本地加载的 文件student.txt

第一次加载的时候从hdfs加载的 文件student.txt

这两个文件在加载的时候都会被加载到hive的指定目录下

由于在hdfs上相同的目录下不能有两个同名的文件，这时候会将第二次上传的数据重命名为student_copy01.txt

2,insert 方式：

1）单条数据插入：

insert into table student_base values(1,'zs','f',24,'CS');

会先建立一个临时表，然后将临时表中的数据拷贝到指定的表中

效率比较低，会出现很多小文件

2）多条数据插入:可以从一个表中查询 将查询结果全部插入到指定的表中 单重插入

insert into table student_base select * from stu where age>20;

一次只能插入到一个表中

3）多重插入：可以一次插入多个表 但是对基表值扫描一次 优点：减少基表的扫描次数,提升插入性能

from 表名 --要扫描的表

insert into table 表1 select ...

insert into tbale 表2 select ...

例如: stu01:大于等于24岁

stu02:小于24岁

from stu

insert into table stu01 select * where age>=24

insert into table stu02 select * where age<24;

分区表的数据插入：

create table if not exists stu_ptn(id int,name string,sex string,department string)

partitioned by (age int)

row format delimited fields terminated by ','

;

这种方式加载数据的时候一定先添加分区

alter table stu_ptn add partition (age=18);

1)load

向分区表中加载数据一定指定加载到哪个分区中

load data local inpath '/home/hadoop/student.txt' into table stu_ptn partition(age=18);

一般情况下分区表的数据加载不使用load方式， load方式不会进行数据监测

2）insert方式

insert into table stu_ptn partition(age=18) values(,,,,,,) 一般也不建议

多条数据插入

insert into table stu_ptn partition(age=18) select id,name,sex,department from stu where age=18;

正常情况下分区肯定有多个按年龄进行分区

数据加载时候这种方式可以吗？

分区比较少的时候：

alter table stu_ptn add partition (age=18);

alter table stu_ptn add partition (age=19);

alter table stu_ptn add partition (age=20);

from stu

insert into table stu_ptn partition(age=18) select id,name,sex,department from stu where age=18;

insert into table stu_ptn partition(age=18) select id,name,sex,department from stu where age=19;

insert into table stu_ptn partition(age=18) select id,name,sex,department from stu where age=20;

静态分区：在向分区表中添加数据之前手动的添加一个指定分区

分区个数不确定 添加分区的时候怎么添加？上面的方式不使用 要使用动态分区

动态分区：可以根据你的数据自动进行分区 不需要提前添加分区

create table if not exists stu_ptn01(id int,name string,sex string,department string)

partitioned by (age int)

row format delimited fields terminated by ','

;

insert into table stu_ptn partition(age) select id,name,age,sex,department from stu; 错误

insert into table stu_ptn partition(age) select id,name,sex,department，age from stu; 正确

动态分区在查询插入的时候动态分区字段必须放在最后一个字段，否则会将最后一个字段默认为你的分区字段

动态分区需要设置：

set hive.exec.dynamic.partitoin.mode=nostrict

查询属性值：

set hive.exec.dynamic.partitoin.mode;

hive.exec.dynamic.partitoin.mode=nostrict

hive.exec.dynamic.partitoin.mode=strict 严格模式 必须手动添加一个静态分区

set hive.exec.dynamic.partitoin.mode=nostrict

hive1.2版本中以下需要设置

set hive.exec.dynamic.partitoin=true； --开启动态分区

set hive.exec.dynamic.partitoin.mode=nostrict；

多个分区字段的时候：

create table if not exists stu_ptn02(id int,name string,sex string)

partitioned by (department string,age int)

row format delimited fields terminated by ','

;

insert into table stu_ptn02 partition(department,age)

select id,name,sex,department,age from stu;

多字段分区的时候目录结构按照分区字段顺序进行划分的

hive1.2中在进行动态分区的时候如果是多字段分区必须手动指定第一级的分区

分区表使用的时候分区字段可以看做一个普通字段

select * from stu_ptn02 where age=18;

只是在建表和添加数据的时候不一样

分桶表的数据插入

create table if not exists stu_bak(id int,name string,sex string,age int,departmrnt string)

clustered by (age) sorted by (sex) into 3 buckets

row format delimited fields terminated by ',';

1)load方式

load data local inpath '/home/hadoop/student.txt' into table stu_bak

load方式不能向分通表中添加数据

2）insert 方式

insert into table stu_bak select * from stu;

好了，我要接着专心的去做需求，改bug了！！！