hive 的 DDL 语法
对数据库的操作
create database if not exists myhive;说明：hive的表存放位置模式是由hive-site.xml当中的一个属性指定的 :hive.metastore.warehouse.dir
创建数据库并指定hdfs存储位置 :create database myhive2 location '/myhive2';
复制代码
alter database myhive2 set dbproperties('createtime'='20180611');
复制代码
说明：可以使用 alter database 命令来修改数据库的一些属性。但是数据库的元数据信息是不可更改的，包括数据库的名称以及数据库所在的位置
查看数据库基本信息hive (myhive)> desc database myhive2;
查看数据库更多详细信息hive (myhive)> desc database extended myhive2;
复制代码
删除一个空数据库，如果数据库下面有数据表，那么就会报错drop database myhive2;
强制删除数据库，包含数据库下面的表一起删除drop database myhive cascade;
复制代码
对数据表的操作
对管理表(内部表)的操作:
hive (myhive)> use myhive; -- 使用myhive数据库hive (myhive)> create table stu(id int,name string);hive (myhive)> insert into stu values (1,"zhangsan");hive (myhive)> insert into stu values (1,"zhangsan"),(2,"lisi"); -- 一次插入多条数据hive (myhive)> select * from stu;
复制代码
对 decimal 类型简单解释下：
用法：decimal(11,2) 代表最多有 11 位数字，其中后 2 位是小数，整数部分是 9 位；如果整数部分超过 9 位，则这个字段就会变成 null；如果小数部分不足 2 位，则后面用 0 补齐两位，如果小数部分超过两位，则超出部分四舍五入
也可直接写 decimal，后面不指定位数，默认是 decimal(10,0) 整数 10 位，没有小数
create table if not exists stu2(id int ,name string) row format delimited fields terminated by '\t' stored as textfile location '/user/stu2';
复制代码
row format delimited fields terminated by '\t' 指定字段分隔符，默认分隔符为 '\001'
stored as 指定存储格式
location 指定存储位置
注意：
hive 表创建的时候可以用 location 指定一个文件或者文件夹，当指定文件夹时，hive 会加载文件夹下的所有文件，当表中无分区时，这个文件夹下不能再有文件夹，否则报错
当表是分区表时，比如 partitioned by (day string)， 则这个文件夹下的每一个文件夹就是一个分区，且文件夹名为 day=20201123
这种格式，然后使用：msck repair table score; 修复表结构，成功之后即可看到数据已经全部加载到表当中去了
create table stu3 as select * from stu2;
复制代码
create table stu4 like stu2;
复制代码
只查询表内字段及属性desc stu2;
详细查询desc formatted stu2;
复制代码
对外部表操作
外部表因为是指定其他的 hdfs 路径的数据加载到表当中来，所以 hive 表会认为自己不完全独占这份数据，所以删除 hive 表的时候，数据仍然存放在 hdfs 当中，不会删掉，只会删除表的元数据
create external table student (s_id string,s_name string) row format delimited fields terminated by '\t';
复制代码
追加操作load data local inpath '/export/servers/hivedatas/student.csv' into table student;
覆盖操作load data local inpath '/export/servers/hivedatas/student.csv' overwrite into table student;
复制代码
load data inpath '/hivedatas/techer.csv' into table techer;
加载数据到指定分区load data inpath '/hivedatas/techer.csv' into table techer partition(cur_date=20201210);
复制代码
注意：
使用 load data local 表示从本地文件系统加载，文件会拷贝到 hdfs 上
使用 load data 表示从 hdfs 文件系统加载，文件会直接移动到 hive 相关目录下，注意不是拷贝过去，因为 hive 认为 hdfs 文件已经有 3 副本了，没必要再次拷贝了
如果表是分区表，load 时不指定分区会报错
如果加载相同文件名的文件，会被自动重命名
对分区表的操作
create table score(s_id string, s_score int) partitioned by (month string);
复制代码
create table score2 (s_id string, s_score int) partitioned by (year string,month string,day string);
复制代码
load data local inpath '/export/servers/hivedatas/score.csv' into table score partition (month='201806');
复制代码
load data local inpath '/export/servers/hivedatas/score.csv' into table score2 partition(year='2018',month='06',day='01');
复制代码
alter table score add partition(month='201805');
复制代码
alter table score add partition(month='201804') partition(month = '201803');
复制代码
注意：添加分区之后就可以在 hdfs 文件系统当中看到表下面多了一个文件夹
alter table score drop partition(month = '201806');
复制代码
对分桶表操作
将数据按照指定的字段进行分成多个桶中去，就是按照分桶字段进行哈希划分到多个文件当中去
分区就是分文件夹，分桶就是分文件
分桶优点：
1. 提高 join 查询效率
2. 提高抽样效率
set hive.enforce.bucketing=true;
复制代码
set mapreduce.job.reduces=3;
复制代码
create table course (c_id string,c_name string) clustered by(c_id) into 3 buckets;
复制代码
桶表的数据加载：由于桶表的数据加载通过 hdfs dfs -put 文件或者通过 load data 均不可以，只能通过 insert overwrite 进行加载
所以把文件加载到桶表中，需要先创建普通表，并通过 insert overwrite 的方式将普通表的数据通过查询的方式加载到桶表当中去
insert overwrite table course select * from course_common cluster by(c_id); -- 最后指定桶字段
复制代码
修改表和删除表
alter table old_table_name rename to new_table_name;
复制代码
查询表结构desc score5;
添加列alter table score5 add columns (mycol string, mysco string);
更新列alter table score5 change column mysco mysconew int;
复制代码
truncate table score6;
说明：只能清空管理表，也就是内部表；清空外部表，会产生错误
复制代码
注意：truncate 和 drop ：
如果 hdfs 开启了回收站，drop 删除的表数据是可以从回收站恢复的，表结构恢复不了，需要自己重新创建；truncate 清空的表是不进回收站的，所以无法恢复 truncate 清空的表
所以 truncate 一定慎用，一旦清空将无力回天
向 hive 表中加载数据
insert into table score partition(month ='201807') values ('001','002','100');
复制代码
load data local inpath '/export/servers/hivedatas/score.csv' overwrite into table score partition(month='201806');
复制代码
insert overwrite table score2 partition(month = '201806') select s_id,c_id,s_score from score1;
复制代码
create table score2 as select * from score1;
复制代码
create external table score6 (s_id string,c_id string,s_score int) row format delimited fields terminated by ',' location '/myscore';
复制代码
create table techer2 like techer; --依据已有表结构创建表
export table techer to '/export/techer';
import table techer2 from '/export/techer';
复制代码
hive 表中数据导出
将查询的结果导出到本地insert overwrite local directory '/export/servers/exporthive' select * from score;
将查询的结果格式化导出到本地insert overwrite local directory '/export/servers/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from student;
将查询的结果导出到HDFS上(没有local)insert overwrite directory '/export/servers/exporthive' row format delimited fields terminated by '\t' collection items terminated by '#' select * from score;
复制代码
dfs -get /export/servers/exporthive/000000_0 /export/servers/exporthive/local.txt;
复制代码
基本语法：（hive -f/-e 执行语句或者脚本 > file）
hive -e "select * from myhive.score;" > /export/servers/exporthive/score.txt
hive -f export.sh > /export/servers/exporthive/score.txt
复制代码
export table score to '/export/exporthive/score';
复制代码
hive 的 DQL 查询语法
单表查询
SELECT [ALL | DISTINCT] select_expr, select_expr, ... FROM table_reference[WHERE where_condition] [GROUP BY col_list [HAVING condition]] [CLUSTER BY col_list | [DISTRIBUTE BY col_list] [SORT BY| ORDER BY col_list] ] [LIMIT number]
复制代码
注意：
1、order by 会对输入做全局排序，因此只有一个 reducer，会导致当输入规模较大时，需要较长的计算时间。
2、sort by 不是全局排序，其在数据进入 reducer 前完成排序。因此，如果用 sort by 进行排序，并且设置 mapred.reduce.tasks>1，则 sort by 只保证每个 reducer 的输出有序，不保证全局有序。
3、distribute by(字段)根据指定的字段将数据分到不同的 reducer，且分发算法是 hash 散列。
4、Cluster by(字段) 除了具有 Distribute by 的功能外，还会对该字段进行排序。
因此，如果分桶和 sort 字段是同一个时，此时，cluster by = distribute by + sort by
select * from score where s_score < 60;
复制代码
注意：
小于某个值是不包含 null 的，如上查询结果是把 s_score 为 null 的行剔除的
select s_id ,avg(s_score) from score group by s_id;
分组后对数据进行筛选，使用having select s_id ,avg(s_score) avgscore from score group by s_id having avgscore > 85;
复制代码
注意：
如果使用 group by 分组，则 select 后面只能写分组的字段或者聚合函数
where 和 having 区别：
1 having 是在 group by 分完组之后再对数据进行筛选，所以 having 要筛选的字段只能是分组字段或者聚合函数
2 where 是从数据表中的字段直接进行的筛选的，所以不能跟在 gruop
by 后面，也不能使用聚合函数
INNER JOIN 内连接：只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来select * from techer t [inner] join course c on t.t_id = c.t_id; -- inner 可省略
LEFT OUTER JOIN 左外连接：左边所有数据会被返回，右边符合条件的被返回select * from techer t left join course c on t.t_id = c.t_id; -- outer可省略
RIGHT OUTER JOIN 右外连接：右边所有数据会被返回，左边符合条件的被返回、select * from techer t right join course c on t.t_id = c.t_id;
FULL OUTER JOIN 满外(全外)连接: 将会返回所有表中符合条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用NULL值替代。SELECT * FROM techer t FULL JOIN course c ON t.t_id = c.t_id ;
复制代码
注：
1. hive2 版本已经支持不等值连接，就是 join on 条件后面可以使用大于小于符号了;并且也支持 join on 条件后跟 or (早前版本 on 后只支持 = 和 and，不支持 \> \< 和 or)
如 hive 执行引擎使用 MapReduce，一个 join 就会启动一个 job，一条 sql 语句中如有多个 join，则会启动多个 job
注意：表之间用逗号(,)连接和 inner join 是一样的
select * from tablea,tableb where tablea.id=tableb.id;
它们的执行效率没有区别，只是书写方式不同，用逗号是 sql 89 标准，join 是 sql 92 标准。用逗号连接后面过滤条件用 where ，用 join 连接后面过滤条件是 on。
in/exists作用一样，都是查询出符合结果集的数据SELECT * FROM student s where id in (1001,1002,1003)
not in/ nit exists作用一样，都是查询出不符合结果集的数据SELECT * FROM student s where id not in (1001,1002,1003)
复制代码
为什么把这个单独拿出来说，因为它和其他的 join 语句不太一样，这个语句的作用和 in/exists 作用是一样的，是 in/exists 更高效的实现SELECT A.* FROM A where id in (select id from B)
SELECT A.* FROM A left semi join B ON A.id=B.id
上述两个 sql 语句执行结果完全一样，只不过第二个执行效率高
复制代码
注意：
1、left semi join 的限制是：join 子句中右边的表只能在 on 子句中设置过滤条件，在 where 子句、select 子句或其他地方过滤都不行。
2、left semi join 中 on 后面的过滤条件只能是等于号，不能是其他的。
3、left semi join 是只传递表的 join key 给 map 阶段，因此 left semi join 中最后 select 的结果只许出现左表。
4、因为 left semi join 是 in(keySet) 的关系，遇到右表重复记录，左表会跳过。
全局排序，只会有一个reduceASC（ascend）: 升序（默认） DESC（descend）: 降序SELECT * FROM student s LEFT JOIN score sco ON s.s_id = sco.s_id ORDER BY sco.s_score DESC;
复制代码
注意：order by 是全局排序，所以最后只有一个 reduce，也就是在一个节点执行，如果数据量太大，就会耗费较长时间
每个MapReduce内部进行排序，对全局结果集来说不是排序。
设置reduce个数set mapreduce.job.reduces=3;
查看设置reduce个数set mapreduce.job.reduces;
查询成绩按照成绩降序排列select * from score sort by s_score; 将查询结果导入到文件中（按照成绩降序排列）insert overwrite local directory '/export/servers/hivedatas/sort' select * from score sort by s_score;
复制代码
distribute by：类似MR中partition，进行分区，结合sort by使用
设置reduce的个数，将我们对应的s_id划分到对应的reduce当中去set mapreduce.job.reduces=7;
通过distribute by 进行数据的分区select * from score distribute by s_id sort by s_score;
复制代码
注意：Hive 要求 distribute by 语句要写在 sort by 语句之前
当distribute by和sort by字段相同时，可以使用cluster by方式.cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是正序排序，不能指定排序规则为ASC或者DESC。
以下两种写法等价select * from score cluster by s_id;select * from score distribute by s_id sort by s_id;
复制代码
评论 (1 条评论)