Hive语法学习总结

hive参数

一 hive常用交互命令hive -e 'sql语句'hive -f  sql文件  //文件中是sql语句二 参数的设置方式一：在客户端中设置参数(当次有效)set 参数名=参数值;方式二：在启动客户端时设置参数(当次有效)hive -hiveconf 参数名=参数值beeline --hiveconf 参数名=参数值  -u jdbc:hive://hadoop102:10000 -n atguigu方式三：永久有效将参数设置在配置文件(hive-site.xml)hive客户端（不需要重启服务--因为hive客户端不需要连接hiveserver2服务）beeline客户端（需要重启服务-因为beeline客户端需要连接hiveserver2服务）
--开启自动转换为本地模式
set hive.exec.mode.local.auto=true; 

库操作

1.创建库

CREATE DATABASE [IF NOT EXISTS] 库名
[COMMENT '库的描述信息-类似于注释']
[LOCATION '库在HDFS上对应的目录的所在路径']
[WITH DBPROPERTIES ('属性名'='属性值', ...)];

2.具体案例

create database db3;#if not exists : 如果库不存在则创建 存在则不创建。如果没有该字段库存在则报错
create database if not exists db3;create database db4
comment 'this is db4'
location '/db4'
with dbproperties('ver'='1.0');create database db5
comment 'this is db4'
location '/db55'  #元数据中是对应的
with dbproperties('ver'='1.0');

3.库的其他操作

#查看库
#通配符可以是  *任意个数任意内容的字符   |表示或
#格式：SHOW DATABASES [LIKE '匹配规则-不是正则表达式'];
show databases;#查看库的信息 : 
#extended ：有了该字段才可以查看库的属性
#格式 ：desc database [extended] 库名;
desc database db5;#选库:use 库名
use db1;#删除库 ：
#[IF EXISTS]  ：如果库存在则删除不存在则不删。如果没有该字段库不存在则报错。
#[RESTRICT|CASCADE] ：默认使用的是RESTRICT只能删除空库  cascade:可以删除非空的库
#格式：DROP DATABASE [IF EXISTS] 库名 [RESTRICT|CASCADE];
drop database db1;
drop database if exists db1;
drop database db2 cascade;#查看库的信息-建库语句
show create database 库名;#修改数据库
--修改dbproperties
#ALTER DATABASE 库名 SET DBPROPERTIES ('属性名'='属性值', ...);
alter database db3 set dbproperties('ver'='1.0');--修改location
#ALTER DATABASE 库名 SET LOCATION 'HDFS的路径';
alter database db3 set location 'hdfs://hadoop102:8020/demo/db33'--修改owner user
#ALTER DATABASE 库名 SET OWNER USER 用户名;
desc database extended db3;

表和库的路径演示

create database d1
location '/d1';create table stu(
id int
)
location '/d2';

表的操作

创建表

		#[TEMPORARY] ：创建一张临时表（当退出客户端时临时表被删除）#[EXTERNAL] ：创建外部表 如果没有该字段创建的是管理表#[IF NOT EXISTS] ：如果表不存在则创建存在则不创建 如果没有该字段表存在则报错CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [库名.]表名   [(字段名 字段类型 [COMMENT 字段的描述信息], 字段名2 字段类型 [COMMENT 字段的描述信息],...)][COMMENT 表的描述信息]#创建分区表 - 后面讲[PARTITIONED BY (字段名 字段类型 [COMMENT 字段的描述信息], ...)]#创建分桶表 - 后面讲[CLUSTERED BY (字段名1, 字段名2, ...)#对分桶字段中的内容排序  - 后面讲[SORTED BY (字段名 [ASC|DESC], ...)] INTO 桶的数量 BUCKETS][ROW FORMAT row_format] #存储格式 - 后面讲(默认是textfile)[STORED AS file_format]#一张表对应HDFS上的一个目录[LOCATION hdfs的路径]#表的属性[TBLPROPERTIES ('属性名'='属性值', ...)]2.创建表案例#案例一#[IF NOT EXISTS] ：如果表不存在则创建存在则不创建 如果没有该字段表存在则报错create table if not exists emp(id int comment 'this is id',name string comment 'this is name')comment 'this is emp'location '/demo/emp'tblproperties('ver'='1.0');#案例二（创建临时表）#[TEMPORARY] ：创建一张临时表（当退出客户端时临时表被删除）create TEMPORARY table emp2(id int,name string);#案例三 (基于现有的表创建一张新表)#create table 新表名 like 已经存在的表的表名;create table emp3 like emp;#案例四：将查询的结果创建成一张表create table 表名asselect查询语句;create table emp5
as
select * from emp;

ROW FORAMT DELIMITED 
[FIELDS TERMINATED BY char]  -- 各字段之间用什么分隔
[COLLECTION ITEMS TERMINATED BY char]  -- 复杂数据类型中各元素之间用什么分隔开
[MAP KEYS TERMINATED BY char]  -- map的key和value用什么分隔开
[LINES TERMINATED BY char] 	-- 每条数据之间用什么分隔
[NULL DEFINED AS char]	-- 数据中的null在文件中用什么字符表示=======================================================================建表语句格式一（数据不是json数据）
create table student(
name string,
friends array<string>,
students map<string,int>,
address struct<street:string,city:string,postal_code:int>
)
row format delimited
fields terminated by ',' 			-- 各字段之间用什么分隔
collection items terminated by '-'		-- 复杂数据类型中各元素之间用什么分隔开
map keys terminated by ':';				-- map的key和value用什么分隔开=====================================================================建表语句格式一（数据是json数据）
create table student_json(
name string,
friends array<string>,
students map<string,int>,
address struct<street:string,city:string,postal_code:int>
)
row format serde 'org.apache.hadoop.hive.serde2.JsonSerDe';  -- 用来处理json数据=======================================================================复杂数据类型如何查里面的元素：
select friends[0],students['xiaoyangyang'],address.city from student_json;说明：
select 数组类型[索引值],map类型['key'],struct类型.属性名 from student_json;=======================================================================
1.表的操作#查看所有表show tables;#查看表信息#[formatted] :可以查看表的详细信息。没有该字段只能看字段信息desc [formatted] 表名;

创建外部表和内部表#创建外部表#[EXTERNAL] : 创建外部表 没有该字段就是内部表create  external table external_table(id int,name string)row format delimited fields terminated by '\t';#创建内部表-管理表create table man_table(id int,name string)row format delimited fields terminated by '\t';二 外部表和管理表的区别1.删除外部表时只会删除元数据。删除管理表时会将元数据和HDFS上的数据全部删除。2.truncate table只能清空管理表。不能清空外部表。三 外部表和管理表相互转换#修改表的属性：alter table 表名 set tblproperties('属性名'='属性值');#FALSE是管理表 TRUE是外部表alter table external_table set tblproperties('EXTERNAL'='FALSE');

1.查看表#[IN 库名] ：查看哪个库中的所有的表#LIKE ['identifier_with_wildcards']; 模糊查询#SHOW TABLES [IN 库名] LIKE ['identifier_with_wildcards'];2.修改表名ALTER TABLE 原表名 RENAME TO 新表名;3.列的操作#添加列：ALTER TABLE 表名 ADD COLUMNS (字段名 字段类型 [COMMENT 字段描述信息], ...)alter table emp add columns(age int comment 'this is age');#更新列的名字#ALTER TABLE 表名 CHANGE [COLUMN] 原字段名 新字段名 字段的类型 [COMMENT 字段的描述信息] [FIRST |  AFTER column_name]alter table emp change column age age2 int;#更新列的类型#注意类型 ：比如int转bigint可以 但是string转int不可以#ALTER TABLE 表名 CHANGE [COLUMN] 字段名 字段名 字段的新类型 [COMMENT 字段的描述信息] [FIRST |  AFTER column_name]alter table emp change age2 age2 string;#更新列的位置#注意：一定要注意字段的类型#ALTER TABLE 表名 CHANGE [COLUMN] 原字段名 原字段名 原字段类型 [COMMENT 字段的描述信息] [FIRST |  AFTER column_name]#将name字段放在列的第一个位置alter table emp change column name name string first;#将name字段放在id的后面alter table emp change column name name string after id;#替换列#ALTER TABLE 表名 REPLACE COLUMNS (字段名 字段类型 [COMMENT 字段的描述信息], ...)#注意：①替换是依次替换 ②一定要注意替换的类型-int替换string肯定报错 #	   ③如果替换的列比被替换的列少-字段就会少。数据本身没有变化。alter table emp replace columns(id3 string,age3 string,name3 string);4.删除表#[if exists] : 如果表存在则删除不存在则不删除。如果没有该字段表不存在报错-但是没有报错drop table [if exists] 表名;5.清空表#注意：不能清空外部表TRUNCATE [TABLE] 表名;6.查看表信息#[formatted] : 可以查看表更详细的信息desc [formatted] 表名;7.查看建表语句show create table 表名;

插入数据

一 Load格式#[LOCAL]  ：表示数据是在本地--从本地向表中导入数据。没有该字段表示从HDFS导数据到表中#[OVERWRITE] ：表示覆盖。如果没有该字段是追加LOAD DATA [LOCAL] INPATH '文件路径' [OVERWRITE] INTO TABLE 表名 #等到分区表再说[PARTITION (partcol1=val1, partcol2=val2 ...)];案例1：从本地向表中导入数据-追加load data local inpath '/opt/module/hive/datas/student2.txt' into table student;案例2：从本地向表中导入数据-覆盖load data local inpath '/opt/module/hive/datas/student2.txt' overwrite into table student;案例3：从HDFS向表中导入数据 - 会将HDFS上的数据移动到表所对应的目录中load data inpath '/demo/datas/student2.txt' into table student2;=========================================================二 Insert2.1 将查询的结果插入到表中#into :追加  overwrite:覆盖INSERT (INTO | OVERWRITE) TABLE 表名#分区表再说[PARTITION (partcol1=val1, partcol2=val2 ...)] #查询语句select语句;案例1 - 追加：insert into table student3 select id,name from student where id < 1010;案例2 - 追加：insert into table student3(id) select id from student where id < 1010;案例3 - 覆盖：insert overwrite table student3 select id,name from student where id < 1005;案例4（错误的） - 覆盖(不能指定插入哪个字段)：insert overwrite table student3(id) select id from student where id < 1003;2.2 插入给定数据INSERT INTO TABLE 表名（字段名1,字段名2,.......）#后面再说[PARTITION (partcol1[=val1], partcol2[=val2] ...)] VALUES (值1,值2,值3,........),(值1,值2,值3,........),........;案例：insert into table student3(id,name) values(1,'a'),(2,'b');2.3 导出-将表中的数据导出#[LOCAL] ：将表中的数据导出到本地。没有该字段导出到HDFS上INSERT OVERWRITE [LOCAL] DIRECTORY '导出的路径-导出到哪里'#可以指定导出的数据的各字段之间用什么分隔[ROW FORMAT row_format] #文件的存储格式-后面讲[STORED AS file_format] select语句;案例1：insert overwrite local directory '/home/atguigu/datas' select * from student;案例2：insert overwrite local directory '/home/atguigu/datas' row format delimited fields terminated by '\t' select * from student;