mySQL和Hive的区别

SQL和HQL的区别

整体

1、存储位置：Hive在Hadoop上；Mysql将数据存储在设备或本地系统中；
2、数据更新：Hive不支持数据的改写和添加，是在加载的时候就已经确定好了；数据库可以CRUD；
3、索引：Hive无索引，每次扫描所有数据，底层是MR，并行计算，适用于大数据量；MySQL有索引，适合在线查询数据；
4、执行：Hive底层是MapReduce；MySQL底层是执行引擎；
5、可扩展性：Hive：大数据量；MySQL:相对就很少了。

SQL执行顺序：
from -> where -> group by -> having -> select -> order by -> limit

语法

内容

SQL

Hive

非等值连接

支持

不支持

子查询

支持

不支持

insert和update

支持

不支持,仅支持覆盖重写整个表

IS [NOT] NULL

null代表空值

String类型的字段若是空(empty)字符串, 即长度为0, 那么对它进行IS NULL的判断结果是False

数组拆分

LATERAL VIEW explode(数组类型字段) newTable AS newzd

数组包含

find_in_set(value,Array)

array_contains(Array, value)

分号

语句结束标识

需要对分号进行转义表示

不等于

!=或者<>

不能使用!=,只能使用<>

group by别名问题

select中新命名的别名可以直接在group by 中使用

不能直接使用别名，只能使用原内容或者再嵌套一层

更多相关细节：
https://zhuanlan.zhihu.com/p/322399014
https://www.cnblogs.com/yxzfscg/p/4892124.html

SQL相关面试题

学生成绩表Grade

id

name

subject

grade

001

张三

语文

81

001

张三

数学

75

002

李四

语文

76

002

李四

数学

90

003

王五

语文

81

003

王五

数学

100

学生班级表Class

name

class

张三

一班

李四

二班

王五

二班

题目一：用一条SQL 语句查询出每门课都大于80分的学生姓名
法一：

select name 
from Grade 
group by name 
having min(grade) > 80
1
2
3
4

法二：

select distinct name 
from Grade 
where name not in (select distinct name 
                   from Grade 
                   where grade <= 80)
1
2
3
4
5

题目二：用一条SQL语句查询出每个班语文成绩排名第一的学生班级以及姓名
法一：

select class,name
from
(select Grade.name,class,grade,
 dense_rank() over(partition by class order by Grade.grade desc) as ranking
from Grade,Class
where Grade.name = Class.name and subject = "语文"
)
where ranking = 1
1
2
3
4
5
6
7
8

法二：

select class,name 
from
(select class,max(grade) as grade
from  Grade,Class
where Grade.name = Class.name and subject = "语文"
group by class
) a 
join
(select class,name,grade
 from  Grade,Class
 where Grade.name = Class.name and subject = "语文"
) b
 on a.class = b.class and a.grade = b.grade
1
2
3
4
5
6
7
8
9
10
11
12
13

注意：
dense_rank()是密集排列，结果是1、1、2、3、4、5
rank()是跳跃排列，结果是1、1、3、4、5、6
row_number()是不重复排列，结果是1、2、3、4、5、6
题目三：语文成绩全校排名第三的学生姓名和成绩

select name,grade
from Grade
where subject = '语文'
order by grade desc
limit 2,1
-- 含义是跳过2条取出1条数据，limit跳过2条信息，读取1条信息，即读取第3条数据
1
2
3
4
5
6

题目四：将学生姓名表转化为横表

-- 此种写法某门课没有成绩记录的学生，成绩将变为0
select name,max(math),max(chinese),max(english)
from
(select name,
          case when subject = "语文" then grade else 0 end as chinese,
          case when subject = "数学" then grade else 0 end as math,
          case when subject = "英语" then grade else 0 end as english
 from Grade
 )
 group by name
1
2
3
4
5
6
7
8
9
10

若横表转竖表

select name , '语文' as subject, chinese as grade from tb1
union all
select name , '数学' as subject, math as grade from tb1
union all
select name , '英语' as subject, english as grade from tb1
1
2
3
4
5

union all与union的区别是union all不会去除重复记录，union会去除重复记录，为了保证数据库效果，除非必要，还是使用union all。

题目五：查询每门功课的及格人数和不及格人数

select subject,
     sum(case when grade >= 60 then 1 else 0 end) as 及格人数，
     sum(case when grade < 60 then 1 else 0 end) as 不及格人数
from Grade
group by subject
1
2
3
4
5

题目五： 将每个学生的成绩按照由大到小写在一起，用逗号隔开

select name,
group_concat(cast(grade as char) order by grade desc separator ',')
from Grade
group by name
1
2
3
4

题目六：按营业额倒序，累计超过3000w的前面的所有公司，用sum() over

题目七：获取每个学生的成绩均值和中位数
方法一：

select t.name,avg_gade,mid_grade
from
(select name,avg(grade) as mid_grade
from
(select name,grade,
row_number() over(partition by name order by grade asc, id **asc**) as 'id1',
row_number() over(partition by name order by grade desc, id **desc**) as 'id2'
from student) as newtable
where abs(id1-id2)=1 or id1=id2;
group by name
) t
join
(select name,avg(grade) as avg_grade
from student
) tt
on t.name = tt.name
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

参考：https://zhuanlan.zhihu.com/p/162089174

MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’
以下写法可以，将id = 改为 id in 则不可以。

select Candidate.Name
from Candidate
where id = 
    (
     select CandidateId
     from Vote
     group by CandidateId
     order by count(*) desc
     limit 1
    )
1
2
3
4
5
6
7
8
9
10

函数

作用

举例

concat(str1, str2,…)

将多个字符串连接成一个字符串

select concat (name , ‘,’ , score) as info from t1

concat_ws(separator, str1, str2, …)

将多个字符串连接成一个字符串，但是可以一次性指定分隔符

select concat (‘,’ , name, score) as info from t1

group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator ‘分隔符’] )

将group by产生的同一个分组中的值连接起来，返回一个字符串结果

select name, group_concat(score) from t1 group by name

题目八：分割字符串，将grade中的数值分割为数学、语文、英语
id|name|grade|
|–|–|–|–|
001|张三|81,80,88||
002|李四 | 75,80,90|
003|王五 | 80,90,100|
方法一：

select name,
          substring_index(grade,',',1) as 数学',
          substring_index(substring_index(grade,',',2),',','-1') as '语文',
          substring_index(grade,',',-1) as 英语
from Grade
1
2
3
4
5

字符串函数：
SUBSTRING_INDEX（str, delim, count）
delim:表示分割字符串，count:表示第几个分割字符串，当 count 为正数，取第 n 个分隔符之前的所有字符； 当 count 为负数，取倒数第 n 个分隔符之后的所有字符。
SUBSTRING(string,position):获取Position之后的所有字符
SUBSTRING(string,position,length)：获取position之后的长度为Length的字符串，其中position的长度从1开始。

疑问：
order by 1 和 order by 字段在效率上的区别，实际运行时，order by 1的效率比order by 字段的效率要高。

详细字符串操作的四种方式：
https://blog.csdn.net/qq_37260640/article/details/79731295utm_medium=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-5.no_search_link&depth_1-utm_source=distribute.pc_relevant.none-task-blog-2%7Edefault%7EBlogCommendFromBaidu%7Edefault-5.no_search_link

数据库常见面试题：https://www.cnblogs.com/diffrent/p/8854995.html

SQL使用问题
问题1：A left join B join C 和 A left join (B join C)的区别？
多表相连不符合交换律，即从左到右执行和从右到左执行的结果是不一样的，
A left join B join C = (A left join B) join C != A left join (B join C)

2、sort by 和order by的区别
order by实现的是全局排序，在hive引擎中将会只有1个reduce。而使用sort by会起多个reduce，只会在每个reduce中排序，如果不指定分组的话，跑出来的数据看起来是杂乱无章的，如果指定reduce个数是1，那么结果和order by是一致的。
order by一般配合group by使用，而group by需要配合聚合函数使用。
sort by分组时需要使用distribute by，和group by类似，但是它不需要配合聚合函数使用，也就不影响原数据的函数，这点和开窗函数有点类似。
参考：https://www.pianshen.com/article/17082054431/

3、如果group by的key中有null，会怎么样
group by 不对 null 进行分组统计。在使用 group by某列名进行分组统计时，该列名的数据有些为 null, 因而会出现 null 的数据行全部分成一组最终导致数据错误。
解决方法：为null值随机生成一个独一无二的数，这样为null的记录将不会被分组，维持原样。可以使用UUID() 函数生成这个独一无二的数。
group by IFNULL(‘列名’, UUID())

参考：https://www.cnblogs.com/CF1314/p/14132397.html
4、日期相关函数
date_format()
date_diff()
date_add()
select date_add(‘2021-05-01’, interval +1 day) as result

5、了解数据倾斜&解决办法
数据倾斜有三种形式得倾斜：
一是分区不均，某几个分区对应的key太多。多数情况都是这种倾斜。
二是单个key对应的数据量太多
三是单条记录数据太大（比如数组中的值太多）
（1）加并行度
这是一种很简单的处理方案，将分区增多，数据打得更散，充分发挥分布式的优势。但是分区增量task也会增多，带来的额外的管理成本就更多了，分的太多反而跑得更慢，存储结果的成本也增加了，不是一个很好的解决方案。
可以在以下几个地方增加分区。
1.在倾斜的stage之前使用reparation重分区。
2.设置shuffle的并行度，大部分情况都使用这个。
（2）处理特殊case
这种就比较常见了，经常会发现很多stage跑到剩下一个task死活跑不过或者耗时非常久。倾斜的key我们可以通过group by key进行count来寻找，一般都是空值、空字符串、还有特别热点的key。如何处理这就看你的业务需求咯。
（3）利用小trick打散key
针对第二种倾斜的形式，我们可以在key上加随机前缀或后缀这样加盐的方式来将一个key变成多个key先进行一次shuffle，最后再还原回来。
例如我们需要进行分组统计，但是数据倾斜了，我们可以对key加随机前缀，把一个key变成多个进行count，最后sum。
这种方式比较麻烦特别是在join的情况下，要考虑的东西比较多。
加盐的方式也会数据量不是那么多的key也打的更散了，计算起来有点浪费资源。
（4）自定义分区方案
这种就更高端了些，需要自己去实现一个partitioner，不多说，还不如构造key来实现自定义分区。
参考：https://www.jianshu.com/p/3635cd26b26a

6、HIVE的抽样方法有哪些
数据块抽样（tablesample()函数）
按照hive表的比例、大小、行数对hive表进行随机抽样，在测试过程中发现，select语句不能带where条件且不支持子查询，可通过新建中间表或使用随机抽样解决，具体语句如下：
create table xxx_new as select * from xxx tablesample(10 percent)

分桶抽样
hive中分桶其实就是根据某一个字段Hash取模，放入指定数据的桶中，比如将表table_1按照ID分成100个桶，其算法是hash(id) % 100，这样，hash(id) % 100 = 0的数据被放到第一个桶中，hash(id) % 100 = 1的记录被放到第二个桶中。具体语法是TABLESAMPLE (BUCKET x OUT OF y [ON colname])
例如：将表随机分成10组，抽取其中的第一个桶的数据
select * from table_01 tablesample(bucket 1 out of 10 on rand())

随机抽样（rand()函数）
1）使用rand()函数进行随机抽样，limit关键字限制抽样返回的数据，其中rand函数前的distribute和sort关键字可以保证数据在mapper和reducer阶段是随机分布的，案例如下：
select * from table_name where col=xxx distribute by rand() sort by rand() limit num;
2）使用order 关键词
案例如下：
select * from table_name where col=xxx order by rand() limit num;
经测试对比，千万级数据中进行随机抽样 order by方式耗时更长，大约多30秒左右。
参考：https://www.cnblogs.com/w-j-q/p/14139007.html

数据库相关知识点

1、等值连接与自然连接的区别和联系
（1）自然连接一定是等值连接，但等值连接不一定是自然连接。
（2）等值连接要求相等的分量，不一定是公共属性；而自然连接要求相等的分量必须是公共属性。
（3）等值连接不把重复的属性除去；而自然连接要把重复的属性除去。
2、数据库的三范式分别是什么，有什么区别？



参考：http://www.blogjava.net/hijackwust/archive/2007/10/21/154793.html

3、什么是视图？和表的区别是什么？
（1）视图是已经编译好的sql语句，而表不是；
（2）视图没有实际的物理记录，而表有；
（3）表是内容，视图是窗口；
（4）表占用物理空间而视图不占用物理空间，表可以及时对它进行修改，但视图只是逻辑概念的存在，只能用创建的语句来修改；
（5）表是三级模式结构中的概念模式，视图是外模式；

4、什么是事务？什么是锁？
事务就是被绑定在一起作为一个逻辑工作单元的SQL 语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。事务具有四个特性，分别是原子性，一致性，隔离性和持久性。

特性

解释

原子性

一个原子事务要么完整执行，要么干脆不执行。

一致性

底层数据存储的完整性。在一次转账过程中，从某一账户中扣除的金额必须与另一账户中存入的金额相等。

隔离性

事务必须在不干扰其他进程或事务的前提下独立执行

持久性

在某个事务的执行过程中，对数据所作的所有改动都必须在事务成功结束前保存至某种物理存储设备。