1.DDL数据定义
1.1创建数据库
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
注意结尾的时候要加分号
1)创建一个数据库,数据库在HDFS上的默认存储路径是/user/hive/warehouse/*.db。
create database if not exists student; //在HDFS上的存储路径是/user/hive/warehouse/student.db
if not exists 可以省略 ,加上是为了避免要创建的数据库已经存在
2)创建一个数据库,指定数据库在HDFS上存放的位置
create database if not exists student location '/stu';
一般情况下,我们会创建数据库在默认的存储路径下
1.2查询数据库
1.2.1 显示数据库
1)显示数据库 show databases;
2)过滤显示查询数据库 show databases like 'stu*';
1.2.2 查看数据库详情
1)显示数据库信息 desc database student;
2)显示数据库详细信息,extended (可以查看出parameters的详细属性) desc database extended student;
1.2.3 切换当前数据库
use student;
1.3修改数据库
用户可以使用ALTER DATABASE命令为某个数据库的DBPROPERTIES设置键-值对属性值,来描述这个数据库的属性信息。数据库的其他元数据信息都是不可更改的,包括数据库名和数据库所在的目录位置
alter database student set dbproperties('createtime'='20170830');
查看修改后的数据库信息 desc database extended student;
1.3删除数据库
1)删除空数据库
drop database student;
2)如果删除的数据库不存在,最好采用 if exists判断数据库是否存在
drop database if exists student;
3)如果数据库不为空(数据库中有表格),可以采用cascade命令,强制删除
drop database student cascade;
1.4 创建表
1)见表语法
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]
[TBLPROPERTIES (property_name=property_value, ...)]
[AS select_statement]
1.4.1 管理表(内部表)
1)理论
默认创建的表都是所谓的管理表,有时也被称为内部表。因为这种表,Hive会(或多或少地)控制着数据的生命周期。Hive默认情况下会将这些表的数据存储在由配置项
hive.metastore.warehouse.dir(例如,/user/hive/warehouse)所定义的目录的子目录下。 当我们删除一个管理表时,Hive也会删除这个表中数据。管理表不适合和其他工具共享数据。
2)案例实操 创建表
1.普通创建表
create table if not exists student(
id int, name string
)
row format delimited fields terminated by '\t'
stored as textfile
location '/user/hive/warehouse/student'; // 不写location的话存储在默认位置
2.根据查询结果创建表(查询的结果会添加到新创建的表中)
create table if not exists student3 as select id, name from student;
3.根据已经存在的表结构创建表
create table if not exists student4 like student;
4.查询表的类型
desc formatted student;
1.4.2 外部表
1)理论
因为表是外部表,所以Hive并非认为其完全拥有这份数据。删除该表并不会删除掉这份数据,不过描述表的元数据信息会被删除掉。
2)管理表和外部表的使用场景
每天将收集到的网站日志定期流入HDFS文本文件。在外部表(原始日志表)的基础上做大量的统计分析,用到的中间表、结果表使用内部表存储,数据通过SELECT+INSERT进入内部表。
3)案例实操 (创建外部表)
1.创建外部表
create external table student(
id int,
name string)
row format delimited fields terminated by '\t'
location '/student';
2. 上传数据到HDFS
dfs -put /opt/module/datas/student.txt /student;
3.查看创建的表
select * from student;
4.查看表格式化数据(可以查看是否为外部表 所属的数据库)
desc formatted student;
5.删除外部表
delete table student;
外部表删除后,hdfs中的数据还在,但是metadata中stu_external的元数据已被删除
内部表删除后,hdfs中的数据也被删除,metadata中stu_external的元数据也被删除
1.4.3 管理表与外部表的互相转换
1.查询表的类型
desc formatted student;
2.修改内部表student为外部表
alter table student set tblproperties('EXTERNAL'='TRUE');
3.修改外部表student2为内部表
alter table student set tblproperties('EXTERNAL'='FALSE');
注意:('EXTERNAL'='TRUE')和('EXTERNAL'='FALSE')为固定写法,区分大小写!要大写
1.5 修改表
1.5.1 重命名表
alter table student rename to stu;
1.5.2 增加/修改/替换列信息
1.查询表结构 desc stu;
2.添加列 alter table stu add columns(age int);
添加多列 中间加逗号分开即可alter table stu2 add columns(ages int, ass string);
3.更新列 alter table stu2 change ages age string; 注意 类型只能从小类型装变成的大类型
4.替换所有列 alter table stu2 replace columns(ages int, ass string); 注意 类型只能从小类型装变成的大类型
1.6 删除表
drop table student;
2.DML数据操作
2.1 数据导入
2.1.1 向表中装载数据(Load)
1.语法
hive> load data [local] inpath '/opt/module/datas/student.txt' [overwrite] into table student [partition (partcol1=val1,…)];
(1)load data:表示加载数据
(2)local:表示从本地加载数据到hive表;否则从HDFS加载数据到hive表
(3)inpath:表示加载数据的路径
(4)overwrite:表示覆盖表中已有数据,否则表示追加
(5)into table:表示加载到哪张表
(6)student:表示具体的表
(7)partition:表示上传到指定分区
2.实操
1)创建一张表
create table student (
id int ,
name string)
row format delimited fields terminated by '\t';
2)加载本地文件到hive (这个类似于copy 本地还有此文件)
load data local inpath '/opt/module/data/student.txt' into table stu2;
3)加载HDFS文件到hive中(这个类似于mv 执行完成操作后,hdfs上没有此文件)
3.1)上传文件到HDFS
dfs -put /opt/module/datas/student.txt /user/atguigu/hive;
3.2)加载HDFS上数据
load data inpath '/user/atguigu/hive/student.txt' into table student;
3.3) 加载数据覆盖表中已有的数据 (原有数据被覆盖)
1.hdfs上的数据 load data inpath '/user/atguigu/hive/student.txt' overwrite into table student;
2.本地数据 load data local inpath '/opt/module/data/student.txt' overwrite into table student;
2.1.2通过查询语句向表中插入数据(Insert)
1.创建一张表
create table student (
id int ,
name string);
2.插入一条数据
insert into student values(1,'zahngsan');
3.插入一个表的数据(根据单张表查询结果)
insert into student select * from stu2;
insert into student select * from stu2 where id >1010;
insert overwrite table student select * from stu2 where id >1010; 必须加table
2.1.3查询语句中创建表并加载数据(As Select)
create table if not exists student3 as select id, name from student;
create table stu4 row format delimited fields terminated by '\t' as select * from stu6; //可以指定格式
2.1.4创建表时通过Location指定加载数据路径
1)上传数据到hdfs上
dfs -mkdir /student;
dfs -put /opt/module/datas/student.txt /student;
2)创建表,并指定在hdfs上的位置
create external table if not exists student5(
id int, name string
)
row format delimited fields terminated by '\t'
location '/student;
就是直接在有表数据的地方创建一个外部表。
2.1.5Import数据到指定Hive表中
注意:先用export导出后,再将数据导入。
import table student2 from '/user/hive/warehouse/export/student';
2.2数据导出
2.2.1Insert导出
1)将查询的结果导出到本地
insert overwrite local directory '/opt/module/datas/export/student' select * from student;
2)将查询的结果格式化导出到本地
insert overwrite local directory '/opt/module/datas/export/student1' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;
3)将查询的结果导出到HDFS上(没有local)
insert overwrite directory '/opt/module/datas/export/student' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' select * from student;
2.2.2Hadoop命令导出到本地
dfs -get /user/hive/warehouse/student/000000_0 /opt/module/datas/export/student3.txt;
2.2.3 Hive Shell 命令导出(退出hive命令行)
hive -e 'select * from default.student;' > /opt/module/datas/export/student4.txt;
2.2.4 Export导出到HDFS上
export table default.student to '/user/hive/warehouse/export/student';
导出的数据带有数据和表结构 matadata 想要使用的时候可以直接导出
2.3清除表中数据(Truncate)
注意:Truncate只能删除管理表,不能删除外部表中数据
truncate table student;