CREATE DATAbase [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, ...)]; create database testbase;#创建testbase数据库
(1) IF NOT EXISTS:判断该数据库是否存在,可选
(2) COMMENT database_comment:添加注释,可选
(3) LOCATION hdfs_path:指定数据库存放路径,默认为user目录下,可选
(4) WITH DBPROPERTIES (property_name=property_value, …):参数信息,只是一些描述信息,可以添加当前创建库的是谁,时间是什么等信息,可选
(1) 查看数据库及其详情
show databases; # 查看所有数据库 desc databases default; #查看数据库信息 desc database extended default; #显示数据库详细信息
(2) 修改数据库
alter database testbase set dbproperties('createtime'='20211015'); # 修改创建时间 desc database extended testbase;#查看数据库
(3) 删除数据库
drop database testbase;#没有数据情况下 drop database testbase cascade;#有数据情况下,使用cascade强制删除二、hive表 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)EXTERNAL:创建一个外部表,内部表删除时,元数据和表中数据都删除,外部表删除表时不删除表中数据,删除元数据。
(2)COMMENT:为表和列添加注释
(3)PARTITIonED BY 创建分区表
(4)CLUSTERED BY 创建分桶表
(5)SORTED BY 不常用,对桶中的一个或多个列另外排序
(6)ROW FORMAT DELIMITED
[FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[LINES TERMINATED BY char]
(7) STORED AS S 指定存储文件类型
(8)LOCATION :指定表在 HDFS 上的存储位置。
(9)AS:后跟查询语句,根据查询结果创建表。
(10))LIKE 允许用户复制现有的表结构,但是不复制数据。
row format delimited fields terminated by ',' collection items terminated by '_' map keys terminated by ':' lines terminated by 'n';
字段解释:
row format delimited fields terminated by ‘,’ – 列分隔符
collection items terminated by ‘_’ --MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
map keys terminated by ‘:’ – MAP 中的 key 与 value 的分隔符
lines terminated by ‘n’; – 行分隔符
alter table text_table set tblproperties('EXTERNAL'='TRUE'); alter table text_table set tblproperties('EXTERNAL'='FALSE');3. 修改表
(1) 修改表名
ALTER TABLE table_name RENAME TO new_table_name;#语法 alter table text_table rename to text1;#案例
(2)增加修改替换列信息
更新列
ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name] alter table test1 change column id dept string;# 修改id列名为dept desc test1;查看表结构信息 desc formatted test1;查看表的详细信息
增加替换列
ALTER TABLE table_name ADD|REPLACE COLUMNS (col_name data_type [COMMENT col_comment], ...) alter table test1 add columns (name string);#增加列 alter table test1 replace columns (id string,dname string,age int);# 替换列为id,dname,age4. row format 的使用
(1)指定数据的分隔符
create table test1(id int,name string) row format delimited fields terminated by ',';#使用逗号分开数据
创建数据stu.txt
1001,lisi
1002,wangwu
1003,zhangsan
上传到hdfs的test1下。
查看表,数据自动分割。
(1)向表中加载数据
语法
load data [local] inpath '数据的 path' [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:表示上传到指定分区
案例
#创建student表,以逗号为分隔符 create table student(id int,name string) row format delimited fields terminated by ','; #加载本地数据到student表中 load data local inpath '/opt/moudle/hive/student.txt' into table student; #加载HDFS上的数据到student表中 load data inpath '/user/student.txt' into table student; #覆盖表中数据 load data inpath '/user/student.txt' overwrite into table student;
(2)load和-put的区别(尽量不适用-put)
使用load加载数据后,select count(*) from student;
命令走MR
使用-put上传数据,select count(*) from student1;
命令不走MR
(1)基本插入数据
insert into test values(1001,'zhangsan'),(1002,'lisi');
(2)根据单表插入数据
将student1中的数据插入到test表中,并覆盖
insert overwrite table test select id,name from student1;
(3)查询一张表插入多张表
from student insert overwrite table student1 select id,name insert into table test select id,name;3. 创建表并插入数据AS SELECT
创建test1表,并将student中的数据插入到test1表中
create table test1 as select id,name from student;4. 创建表并指定hdfs上的位置
create external table test2(id int,name string) row format delimited fields terminated by ',' location '/user/hive/warehouse/testbase.db';四、DML数据操作之数据导出 1. Insert导出
(1)将查询到的结果导出到本地
自动创建data/export/student目录,并将数据导入到文件夹中
insert overwrite local directory '/opt/moudle/hive/data/export/student' select *from student;
(2)将查询到的结果格式化导出到本地
insert overwrite local directory '/opt/moudle/hive/data/export/student1' row format delimited fields terminated by ',' select *from student;
(3)将查询结果导出到HDFS上
没事别用overwrite,用into好点
insert overwrite directory '/user/student2' ROW FORMAT DELIMITED FIELDS TERMINATED BY 't' select * from student;2. hadoop命令将数据导出到本地
dfs -get /user/hive/warehouse/student/student.txt /opt/module/data/export/student3.txt;3. Hive shell 命令导出
bin/hive -e 'select * from default.student;' > /opt/module/hive/data/export/student4.txt;4. Export导出数据到HDFS上
export table default.student to '/user/hive/warehouse/export/student';
- 清空表中数据Truncate
Truncate 只能删除管理表,不能删除外部表中数据
truncate table student;