hive 创建数据库 报错名称和描述 hive 创建数据库语句

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;