DDL数据定义语言
1、创建数据库
创建数据库的语句
CREATE DATABASE [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];
注意：创建的hive库默认是存放在hdfs上的，默认存放位置是/user/hive/warehouse/*.db
①创建一个数据库
create database data；
②如果已经存在数据库，则会报错
为了避免出现这种问题，可以在建库时候使用if not exists
create database if not exists data;
③创建的hive库默认是存放在hdfs上的，默认存放位置是/user/hive/warehouse/*.db，如果想要存放位置改变，在创建表的时候也可以指定
create database data2 location '/data2.db';
2、查询数据库
①显示数据库
show databases；
②查看数据库详情
1）显示数据库信息
desc database data;
2）显示数据库详细信息，extended
desc database extended data;
3）在创建数据库的时候还可以加上创建时间属性
create database data3 with dbproperties(“createtime”=”20211222”);
4）再次查询数据库详情，就会显示创建时间
desc database extended data3;
③切换当前数据库
use data2;
3、修改数据库
可以使用ALTER DATABASE命令为某个数据库属性进行设置
alter database data3 set dbprperties('createtime'='2021-12-22');
查看是否修改成功
desc database extended data3;
4、删除数据库
①删除空数据库
drop database data2;
②如果数据库不存在，则会报错
所以在删除之前我们可以使用if exists判断以下
drop database if exists data2;
③如果删除的数据库不为空，使用drop就会报错
所以如果删除不为空的数据库，可以使用cascade
drop database data cascade;
5、创建表
建表语句
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]
[LIKES existing_table_or_view_name]
字段说明
| CREATE TABLE | 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常 |
|---|---|
| EXTERNAL | 1) 关键字可以让用户创建一个外部表 在建表的同时可以指定一个指向实际数据的路径（LOCATION） 2) 在删除表的时候 内部表的元数据和数据会被一起删除， 外部表只删除元数据，不删除数据。 |
| COMMENT | 为表和列添加注释。 |
| PARTITIONED BY | 创建分区表 |
| CLUSTERED BY | 创建分桶表 |
| SORTED BY | 不常用，对桶中的一个或多个列另外排序 |
| ROW FROMAT | 见下表 |
| STORE AS | 指定存储文件类型 常用的文件存储类型： SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件） 如：store as textfile、store as sequencefile |
| LOCATION | 指定表在HDFS上的存储位置 |
| AS | 后跟查询语句，根据查询语句结果创建表 |
ROW FROMAT字段说明
| 字段 | 说明 |
|---|---|
| Fields | 指定字段之间的分隔符 |
| Collection | 用于指定集合中元素间的分隔符 |
| Map | 用于指定map集合中键值对间的分隔符 |
| Lines | 用于指定每行记录间的分隔符 |
| SerDe | 是Serialize/Deserialize的简称 用户在建表时可以自定义SerDe或使用自带的SerDe |
| 若未指定Row Format，则用自带的SerDe |
①内部表(管理表)
管理表，又称内部表，如果没有指定表的类型，默认创建的表就是内部表，当我们删除一个内部表时，其表的数据也会被删除。Hive默认会将这些表的数据存储在hive.metastore.warehouse.dir定义目录的子目录下。由于在删除的时候也会把数据删除掉，所以内部表不适合和其他工具共享数据
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';
建表语句说明
| 语句 | 说明 |
|---|---|
| fields terminated by ‘\t’ | 字段之间采用’\t’分割 |
| stored as textfile | 文件类型为textfile |
| location ‘/user/hive/warehouse/student’ | 在hdfs上的位置 |
2）创建数据文件student.txt
vim /opt/module/hive/datas/student.txt
1001 student1
1002 student2
1003 student3
1004 student4
1005 student5
1006 student6
1007 student7
1008 student8
1009 student9
3）上传数据
dfs -put /opt/module/hive/datas/student.txt /user/hive/warehouse/student;
4）查看数据是否上传
5）查询表的类型
desc formatted student;
6）根据查询结果创建表
create table if not exists student2 as select id, name from student;
7）根据已经存在的表结构创建表
create table if not exists student3 like student;
8）查询表的类型
desc formatted student2;
9）删除student，会发现hdfs上的数据也不存在了
drop table student;
总结：hive表中导入数据的两种方式：
1、使用load将文件数据导入表中，要求文件要按照hive表的规则进行编写
load data local inpath '/opt/module/hive/datas/personInfo.txt' into table personinfo;
2、指定了hive表的hdfs路径，将文件传到相应的路径下，要求文件要那招hive表的规则进行编写
dfs -put /opt/module/hive/datas/student.txt /user/hive/warehouse/student;
②外部表
因为表是外部表，所以Hive并非认为其完全拥有这份数据。
删除该表并不会删除掉这份数据，不过描述表的元数据信息会被删除掉。
在外部表（原始日志表）的基础上做大量的统计分析，用到的中间表、结果表使用内部表存储，数据通过SELECT+INSERT进入内部表。外部表多用于存储原始数据，为多个部门、小组所使用，采用外部表共易共享数据。
1）在/opt/module/hive/datas目录下编辑文件dept.txt，添加如下内容
vim /opt/module/hive/datas/dept.txt
10 ACCOUNTING 1700
20 RESEARCH 1800
30 SALES 1900
40 OPERATIONS 1700
2）在/opt/module/hive/datas目录下编辑文件emp.txt，添加如下内容
vim /opt/module/hive/datas/emp.txt
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
3）上传数据到HDFS
dfs -mkdir /user/hive/warehouse/dept;
dfs -mkdir /user/hive/warehouse/emp;
dfs -put /opt/module/hive/datas/dept.txt /user/hive/warehouse/dept;
dfs -put /opt/module/hive/datas/emp.txt /user/hive/warehouse/emp;
4）创建部门表的外部表
create external table if not exists dept(
deptno int,
dname string,
loc int
)
row format delimited
fields terminated by '\t';
5）创建员工表的外部表
create external table if not exists emp(
empno int,
ename string,
job string,
mgr int,
hiredate string,
sal double,
comm double,
deptno int)
row format delimited
fields terminated by '\t';
6）查看数据
select * from dept;
7）查看表结构
desc formatted dept;
8）删除表，只是删除了metadata信息，在hdfs上的数据还是存在的
drop table dept;
③内部表和外部表的相互转换
1）查询表的类型
desc formatted student2;
2）转换为外部表
alter table student2 set tblproperties('EXTERNAL'='TRUE');
3）再次查看表的信息
4）外部表转换为内部表
alter table student2 set tblproperties('EXTERNAL'='FALSE');
5）再次查看表信息
注意：(‘EXTERNAL’=‘TRUE’)和(‘EXTERNAL’=‘FALSE’)为固定写法，区分大小写！
6、表的修改
①重命名表
ALTER TABLE table_name RENAME TO new_table_name;
②增加/修改/替换列信息
1）更新列
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name];
更改字段的类型
2）增加和替换列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) ;
3）想要指定某个字段的位置
①指定某个字段在最前面
alter table student name name string first;
②将字段移动到指定字段的后面
alter table student change name name string after ename;
③替换列
alter table student replace columns(id int);
7、删除表
drop table tableNmae;
703
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
