大数据分析工程师面试集锦3-SQL/SparkSql/HiveQL

点击上方“大数据与人工智能”，“星标或置顶公众号”

第一时间获取好内容

为什么考察SQL？

大数据分析工程师80%的时间都在与SQL打交道，通过SQL完成业务方的各种临时性需求分析和常规性报表统计。熟练的SQL技能能够大大提高工作效率。本文将SQL/SparkSql/HiveQL放在一起来梳理一份常见题型的面试题库。

面试题库

01SQL基础知识考察

对于面试初级数据分析师来说，SQL的面试重点会放在基础知识的考察，如果最基本的基础概念和语法都不能熟练回答出来的话，通过面试的几率就会很低。下面两张图是SQL基础概念和基础语法的考题大纲图，接下来围绕图中提到的概念来列举几个常见面试题。

图1 基础概念

图2 基础语法

考题模拟

题1：你觉得SQL是一种什么样的语言，说说你对它的认识。

答：SQL是Structured Query Language（结构化查询语言）的缩写。是一种专门用来与数据库沟通的语言，与大多数编程语言不同，SQL中只有很少的关键字，SQL语言的设计只为了达到一个目的---提供一种从数据库中读写数据的简单有效的方法。

SQL有如下优点：

（1）SQL不是某个特定数据库提供商专有的语言。几乎所有重要的DBMS都支持SQL，所以学习此语言使你几乎能与所有数据库打交道。

（2）SQL简单易学。它的语句全都是由有很强描述性的英语单词组成，而且这些单词的数目不多。

（3）SQL虽然看上去很简单，但实际上是一种强有力的语言，灵活使用其语言元素，可以进行非常复杂和高级的数据库操作。

题2：你是怎么理解数据库和表的？

答：数据库是一个以某种有组织的方式存储的数据集合。可以将数据库想象为一个文件柜，这个文件柜是一个存放数据的物理位置，不管数据是什么，也不管数据是如何组织的。通常容易将数据库和数据库软件的概念相混淆，数据库软件应称为数据库管理系统（DBMS）。数据库是通过DBMS创建和操纵的容器，而具体它究竟是什么，形式如何，各种数据库都不一样。

往文件柜里放资料时，并不是随便将它们扔进某个抽屉就完事了，而是在文件柜中创建文件，将相关的资料放入特定的文件中。在数据库领域中，这种文件称为表。是一种结构化的文件，可用来存放某种特定类型的数据。关于表的概念有以下注意事项：

数据库中的每个表都有一个名字来标识自己。这个名字是唯一的，即数据库中没有其他表具有相同的名字。

PS：每次回答面试官所提问的概念性问题时，最好在答出概念后，总结性的阐述一下相关注意事项，这样能够很快体现出你对这个概念的认知是很清楚的，且给面试官思路清晰，总结和表述能力不错的印象。

题3：手写一下如何创建一张表Products，该表有5个字段，产品id，供应商id，产品名称，产品价格，产品描述。

答：

CREATE TABLE Products
（
    prod_id INT PRIMARY KEY AUTO_INCREMENT COMMENT '产品id',
    vend_id INT  NOT NULL COMMENT '供应商id',
    prod_category VARCHAR(254) COMMENT '产品类别',
    prod_name VARCHAR(254) COMMENT '产品名称',
    prod_price  DECIMAL(8,2) COMMENT '产品价格',
    prod_desc  VARCHAR(1000) COMMENT '产品描述',
    create_time datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    update_time datetime COMMENT '更新时间'
）;

本题看似简单，却可考察出面试者的三个方面：

1.平时创建表时考虑是否周全，比如对各个字段该定义为什么类型，可否为空是否都有思量；

2.建表是否有新建主键意识；

3.如果最后能自己主动补上create_time和update_time两字段，可说明面试者的确是有实际工作经验的。

题4：给刚刚定义的表Products增加一列，表示供应商的手机号码。

答：

ALTER TABLE Products add vend_phone VARCHAR(20);

题5：把刚新加的列删除掉。

答：

ALTER TABLE Products DROP COLUMN vend_phone;

PS：诸如以上题4题5题6，面试官要么会直接问你怎么新加一个字段，怎么删除一个字段，这种只需你口头回答的题型，要么就会给个示例，让你实际书写一下。总之，无论会怎么面，面试者需要对基本的DDL语言十分熟悉这是肯定的，类似考题比如怎么给表重命名呀，怎么删除一张表呀，怎么给表插入数据呀，等等，这里就不一一列举了，如果真的忘了，可即刻上网百度复习一下。

题6：假设以上所建表中已插入实际数据，请你检索一下产品价格大于20，供应商id为28，产品名称里包含饼干的数据，结果按产品id排序，只需显示10行。

答：当我们看到这样一个典型的数据检索面试题时，首先我们将面试官所描述的所有内容快速记录下来，如果面试官没有给你准备面试草稿纸，那么，你最好事先自己准备几张，一个事前能够做足准备的面试者，给面试官的印象分只会增不会减。将内容记录下来以后，接下来，你先花点时间观察一下这些内容，迅速对应一下自己所学知识结构，想想，每一个具体的内容描述，面试官具体是想考察什么。以本题为例，首先说的是检索一下，那么也就是select，产品价格大于20，供应商id为28，显然这是限制条件，且它们之间是组合关系（且），产品名称里包含饼干，包含，什么意思呢，就是模糊查询的概念，那么自然想到like，结果排序，也就是order by，且并没有说按升序还是降序，那就只要使用默认排序即可，显示10行，limit。

SELECT *
FROM Products
WHERE prod_price > 20 and vend_id = '28'
      and prod_name like '%饼干%'
ORDER BY prod_id
LIMIT 10；

题7：以上明细结果基础上按产品类别分组，统计每组记录数，结果按照记录数进行降序。

答：

SELECT prod_category，count(*) as cnt
FROM Products
WHERE prod_price > 20 and vend_id = '28'
      and prod_name like '%饼干%'
GROUP BY prod_category
ORDER BY prod_category DESC;

题8：将产品名称和价格以空格分隔，合并为一个字段。

答：使用CONCAT函数。

CONCAT(prod_name,' ',prod_price)

题9：将产品价格按照1-10元，11-100元，100元以上分为三组，进行分组统计记录数。

答：

SELECT
case when prod_price between 1 and 10 then '1~10'
     when prod_price between 11 and 100 then '11~100'
     when prod_price > 100 then '大于100' end as price_group,
     count(*) as cnt
FROM Products
group by price_group;

PS：在题7考查了基本语法结构以后，会再进一步考查分组，汇总，使用函数，创建计算字段等更为常见使用更为频繁的SQL进阶知识点，如题8/9/10。其他常见的聚集函数，文本处理函数，日期、时间处理函数和数值处理函数等，考察情况类似，大家可自行前去总结了解一下，也可直接阅读本公众号SQL相关文章，里面都有罗列总结。

02SQL实操考察

SQL基础知识考察结束后，如果面试者整体表现尚佳，面试官就会加大考题难度，以实际工作中的SQL应用程度来考察大家，具体体现为对多张表之间的检索考察，相关知识点涉及：表联结、子查询、组合查询。

图3 实操考察

考题模拟

题10：什么叫左外连接、什么叫右外连接、什么叫内外连接 或 什么叫全外连接？

答：

内连接(INNER JOIN)，通常可以省略掉INNER不写，它的含义是左右两个集合相乘后，只保留满足ON后面关联条件的记录。所以，可以利用内连接计算两个集合的交集，只需要把集合元素的字段都写在ON后面的关联条件里即可。

左外连接(LEFT OUTER JOIN)，OUTER通常可以省略不写，它的含义是，左右两个集合相乘后，保留满足ON后面关联条件的记录加上左表中原有的但未关联成功的记录。因此，左外连接，可以用来计算集合的差集，只需要过滤掉关联成功的记录，留下左表中原有的但未关联成功的记录，就是我们要的差集。

右外连接(RIGHT OUTER JOIN)，与左外连接含义相同，只是方向不同而已，通常也是省略OUTER不写。

全外连接(FULL OUTER JOIN)，含义是，左右两个集合相乘后，保留满足ON后面关联条件的记录加上左表和右表中原有的但未关联成功的记录。

题11：假设除了以上Products表，还有一张存储销售产品供应商的表Vendors，表中字段信息如下，如何得到表Products和Vendors两表能关联上的部分中Products的数据。

列	说明
vend_id	唯一的供应商ID
vend_name	供应商名
vend_address	供应商的地址
vend_zip	供应商地址邮政编码
vend_city	供应商所在城市

答：

//方式一：
SELECT Products.*
FROM Vendors，Products
WHERE Vendors.vend_id = Products.vend_id;


//方式二：
SELECT Products.*
FROM Products
WHERE vend_id IN (SELECT DISTINCT vend_id
                   FROM Vendors);
                   
//方式三：
SELECT a.*
FROM Products a
(INNER) JOIN Vendors b
ON a.vend_id = b.vend_id;

 

题12：检索供应商id不在Products中的Vendors表中的数据。

答：

SELECT a.*
FROM Vendors a
LEFT JOIN Products b
ON a.vend_id = b.vend_id
WHERE b.vend_id is null

题13：组合查询有哪两种？组合查询有什么特点？

答：组合查询有两种，一种是相同记录去重组合查询，一种是相同记录不去重组合查询。对应语法是UNION和UNION ALL。

UNION使用注意事项：

（1）使用UNION必须有两条或者两条以上的SELECT语句组成，语句之间用UNION关键字分割；

（2）使用UNION关联的每个子查询必须包含相同数量的字段；

（3）列数据类型必须兼容;类型不必完全相同，但必须是DBMS可以隐含转换的类型(不同的数值类型或者不同的日期类型)；

（4）使用组合查询,当需要对结果进行排序时，只能指定一条order By语句，这条语句只能放在最后一条SELECT语句的后面。

以上所列是SQL中的UNION使用注意事项，HiveSql和SparkSql中的使用规则如何读者最好再自行总结对比一下。

PS：以上几个示例分别演示了表联结、子查询、组合查询的重要知识点，实际考题可能千变万化，本小结相当于给大家将相关知识点总结了一下，大家可多刷刷题加强认知和使用的熟练程度。

03SparkSql/HiveSql专业知识考查

实际面试中，前两节SQL知识点的面试范围和语法题型同样适用于SparkSql/HiveSql，至于SparkSql和HiveSql独有的特性考察，在面试一个初级数据分析师的时候，会体现在以下知识点。

图4 SparkSql专业考点

图5 HiveSql专业考点

考题模拟

题14：SparkSql支持读入的数据类型有哪些？

答：SparkSql支持读入的数据类型有parquet、csv、json、jdbc、table和text等。

题15：具体阐述一下SparkSql想做到跟SQL一样查看目标文件里的3条记录，需要怎么做？

答：

（1）构建入口import org.apache.spark.sql.SparkSession

val spark = SparkSession
  .builder()
  .appName("Spark SQL basic example")
  .config("spark.some.config.option", "some-value")
  .getOrCreate()
  
（2）创建DataFrame，例如从一个json文件创建一个DataFrame。val df = spark.read.json("examples/src/main/resources/people.json")

// 显示出DataFrame的内容
df.show()
// +----+-------+
// | age|   name|
// +----+-------+
// |null|Michael|
// |  30|   Andy|
// |  19| Justin|
// +----+-------

（3）执行SQL查询。// 将DataFrame注册成一个临时视图

df.createOrReplaceTempView("people")
val sqlDF = spark.sql("SELECT * FROM people LIMIT 3")
sqlDF.show()
// +----+-------+
// | age|   name|
// +----+-------+
// |null|Michael|
// |  30|   Andy|
// |  19| Justin|
// +----+-------+
其他使用语法示例。import spark.implicits._ //导入隐式转换的包

//打印schema
df.printSchema()
// root
// |-- age: long (nullable = true)
// |-- name: string (nullable = true)
//选择一列进行打印
df.select("name").show()

//选取年龄大于20的
df.filter($"age" > 20).show()

//聚合操作
df.groupBy("age").count().show

题16：SparkSql的数据保存语法是什么样的？

答：

//例如选取DataFrame中的两列保存到json文件中，可指定保存文件的格式
df.select("name", "favorite_color").write.format("json").save("namesAndFavColors.json")


//选取DataFrame中的两列保并追加到parquet文件中，可指定模式为追加，另还有覆盖模式
df.select("name", "favorite_color").write.mode(SaveMode.append)
.save("namesAndFavColors.parquet")
 

题17：谈谈Hive里分区的概念，它的作用是什么，如何添加和删除分区？

答：在Hive中，表中的一个分区（Partition）对应于表下的一个目录，分区的作用就是辅助查询，缩小查询范围，在HiveSql中限制分区条件可加快数据的检索速度。

//添加分区
ALTER TABLE day_table ADD PARTITION (dt='2008-08-08', hour='08') location '/path/pv1.txt' PARTITION (dt='2008-08-08', hour='09') location '/path/pv2.txt';


//删除分区
ALTER TABLE day_hour_table DROP PARTITION (dt='2008-08-08', hour='09');

题18：如何自定义一个函数供HiveSql使用，具体步骤分为哪几步？

答：Hive自定义临时通用函数的步骤如下，以一个简单示例我们一起来操作一下：

1. 在类中创建自定义函数。自定义UDF需要继承org.apache.hadoop.hive.ql.exec.UDF，实现evaluate函数，evaluate函数支持重载。

package com.yqz.udf;
 
import org.apache.hadoop.hive.ql.exec.UDF;
import org.apache.hadoop.io.Text;
 
public class ConcatString extends UDF {
    // string can not translation in hadoop
    public Text evaluate(Text a, Text b) {
        return new Text(a.toString() + "*******" + b.toString());
    }
}

查询执行过程中，查询中对应的每个应用到这个函数的地方都会对这个类进行实例化。对于每行输入都会调用evaluate()函数。

2.将该类所在的包导出成jar包，放入linux目录下。

//先删除旧包
delete jar /data/yqz/hive/contactString.jar;
//添加新的jar包
add jar /data/yqz/hive/contactString.jar;

需要注意的是，jar文件路径是不需要用引号括起来的。这个路径需要是当前文件系统的全路径。Hive不仅仅将这个jar文件放入到classpath中，同时还将其加入到分布式缓存中，这样，整个集群的机器都是可以获得该jar文件的。

3.创建临时函数，指向jar包中的类。

//语法：create temporary function <函数名> as 'java类名';
//示例
create temporary function myconcat as 'com.yqz.udf.ConcatString';

需要注意的是，create temporary function中的temporary 关键字表示的是当前会话中声明的函数只会在当前会话中有效。因此用户需要在每个会话中都添加jar，然后再创建函数。如果用户需要长期频繁的使用同一个jar和函数的话，可以将相关语句增加到$HOME/.hiverc文件中去。

4.使用临时函数.

//语法：
使用：select <函数名> (参数);
删除：drop temporary function if exists <函数名>;
//示例
select myconcat('HELLO','world');

删除自定义临时函数时，加上if exists，这样即使该函数不存在，也不会报错。

题19：SQL和HiveSql中都有窗口函数，能讲讲窗口函数的基本语法吗？

答：

<窗口函数>()
OVER
(
  [PARTITION BY <列清单>]
  [ORDER BY <排序用清单列>] [ASC/DESC]
  (ROWS | RANGE) <范围条件>
)

题20：HiveSql使用到的数据跟传统SQL数据库中的数据存储方式不同，你能讲解下HiveSql中使用到的数据源的数据组织方式吗？

答：

（1）Hive的数据组织包括数据库、表、视图、分区、分桶和表数据等。数据库，表，分区等等都对应 HDFS上的一个目录。分桶和表数据对应 HDFS 对应目录下的文件。

（2）Hive 中所有的数据都存储在 HDFS 中，没有专门的数据存储格式，因为 Hive 是读模式 （Schema On Read），可支持 TextFile，SequenceFile，RCFile 或者自定义格式等。

（3）只需要在创建表的时候告诉 Hive 数据中的列分隔符和行分隔符，Hive 就可以解析数据。

分隔符	描述
\n	对于文本文件来说，每行是一条记录，所以\n 来分割记录
^A (Ctrl+A)	分割字段，也可以用\001 来表示
^B (Ctrl+B)	用于分割 Arrary 或者 Struct 中的元素，或者用于 map 中键值之间的分割，也可以用\002 分割。
^C	用于 map 中键和值自己分割，也可以用\003 表示。

（4）Hive 中包含以下数据模型：

　　database：在 HDFS 中表现为${hive.metastore.warehouse.dir}目录下一个文件夹；

　　table：在 HDFS 中表现所属 database 目录下一个文件夹；

　　partition：在 HDFS 中表现为 table 目录下的子目录；

　　bucket：在 HDFS 中表现为同一个表目录或者分区目录下根据某个字段的值进行 hash 散列之后的多个文件；

　　view：与传统数据库类似，只读，基于基本表创建；

（5）Hive 的元数据存储在 RDBMS中，除元数据外的其它所有数据都基于 HDFS 存储。

PS：需要重点强调的是，虽然以上示例16-22中，窗口函数只是通过示例给出了语法结构，但在实际面试中窗口函数却是被考察最多的，其考点内容不一，没有统一的面试模板，都是面试官随机出题考察，通常会以实际案例形式给出。窗口函数在HiveSql的实际使用中能够实现各种复杂查询且语法结构简洁高效。所以强烈建议大家一定要多加练习，本公众号之前有一篇专门写Hive窗口函数的文章《Hive窗口函数使用指南》，讲解细致且示例很多，大家可自行查阅一下。

小结

本文将SQL/SparkSql/HiveSql放在一起梳理了一份常见题型的面试题库。每一小节前都给出了考点大纲，依据大纲，下面都会出几道模拟考题，每一个考题的讲解都尽量全面和详细，以带大家将相关知识点温习一遍，所选考题只是作为典型样例，其他类推知识考点或周边知识细节还需要大家再多加练习和总结，唯有充足的准备才能如愿找到理想的工作，希望本文对你有一定的帮助哦。

-end-