数据与智能 该公众号专注于大数据和人工智能技术。 一批具有多年实践经验的技术极客参与运营管理，在大数据、数据分析、推荐系统、机器学习、人工智能等方向持续输出原创文章，输出至少10篇每周都有优质原创文章。 同时，我们将关注并分享大数据和人工智能行业趋势。 欢迎关注。
为什么要检查 SQL？
大数据分析工程师80%的时间都在和SQL打交道，利用SQL完成业务侧的各种临时需求分析和定期报表统计。 熟练的SQL技能可以大大提高工作效率。 本文将SQL/SparkSql/HiveQL放在一起，整理了一份常见题型的面试题库。
面试题库
01 SQL基础知识考察
对于初级数据分析师面试来说，SQL面试的重点会放在基础知识的考察上。 如果不能熟练回答最基本的基本概念和语法，那么通过面试的机会就会很低。 下面两张图是基本SQL概念和基本语法的试题大纲。 接下来我们根据图中提到的概念列出几个常见的面试问题。 图1 基本概念
图2 基础语法试题模拟
问题1：你认为SQL是一种什么样的语言？ 告诉我们您对此的了解。
答：SQL是结构化查询语言的缩写。 它是一种专门用于与数据库通信的语言。 与大多数编程语言不同，SQL 中只有几个关键字。 SQL 语言的设计目的只有一个——提供一种从数据库读取和写入数据的方法。 简单有效的方法。 SQL具有以下优点： (1)SQL不是特定数据库提供商专有的语言。 几乎所有重要的 DBMS 都支持 SQL，因此学习这种语言可以让您使用几乎任何数据库。 (2) SQL简单易学。 它的句子都是由描述性很强的英语单词组成的，而且这些单词的数量并不多。 (3)虽然SQL看起来很简单，但它实际上是一种功能强大的语言。 灵活地使用其语言元素，您可以执行非常复杂和高级的数据库操作。
问题2：如何理解数据库和表？
答：数据库是以某种有组织的方式存储的数据的集合。 将数据库视为文件柜。 该文件柜是数据的物理位置，无论数据是什么或如何组织。 数据库和数据库软件的概念通常很容易混淆。 数据库软件应该称为数据库管理系统（DBMS）。 数据库是通过 DBMS 创建和操作的容器。 然而，它到底是什么以及它采取什么形式对于不同的数据库来说是不同的。 当您将信息放入文件柜时，您不仅仅是将它们扔到某个抽屉中，仅此而已。 相反，您在文件柜中创建文件并将相关信息放入特定文件中。 在数据库世界中，此类文件称为表。 它是一种结构化文件，可用于存储特定类型的数据。 关于表的概念有以下几点说明： 数据库中的每个表都有一个名称来标识自己。 该名称是唯一的，即数据库中没有其他表具有相同的名称。 PS：每次回答面试官提出的概念性问题时，最好在回答完概念后总结相关笔记。 这可以很快表明你对这个概念的理解非常清晰。 并给面试官留下你思路清晰、总结和表达能力强的印象。 问题3：手写如何创建一个表Products。 该表有5个字段，产品id、供应商id、产品名称、产品价格、产品描述。
答案：创建表产品
（
prod_id INT 主键 AUTO_INCRMENT 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 产品添加 vend_phone VARCHAR(20);
问题5：删除新增的列。
答案：ALTER TABLE Products DROP COLUMN vend_phone;
PS：对于上面的问题4、5、6，面试官要么直接问你如何添加新字段，要么如何删除字段。 这类问题只需要你口头回答，或者他们会给你一个例子让你练习。 写一下它。 总之，无论面试如何，肯定的是面试官需要非常熟悉基本的DDL语言。 类似的测试题比如如何重命名表、如何删除表、如何向表中插入数据等，这里就不一一列举了。 如果实在忘记了，可以立即百度查看。 问题6：假设实际数据已插入到上面创建的表中。 请搜索产品价格大于20、供应商ID为28、产品名称包含饼干数据。 结果按照产品ID排序，只需要显示10行。
答：当我们看到这样一个典型的数据检索面试问题时，我们首先快速记录面试官所描述的一切。 如果面试官没有给你准备面试草稿，那么你最好提前自己准备一些。 张作为一个面试官，如果能提前做好充足的准备，只会增加他给面试官的印象。 记录完内容后，花一些时间观察内容，快速绘制出所学知识的结构，并思考每个具体的内容描述以及面试官到底想考察什么。 以这个问题为例。 首先要做的就是搜索，也就是选择。 产品价格大于20，供应商ID为28，显然这是一个限制，它们之间存在组合关系（与）。 产品名称包含cookie。 ，include，是什么意思，是模糊查询的概念，所以很自然地想到了like，结果排序，即order by，并没有说升序或者降序，那就用默认的排序吧，显示 10 行，限制。
选择 *
来自产品
其中 prod_price > 20 且 vend_id = '28'
和 prod_name 类似 '%cookie%'
按 prod_id 排序
限制 10；
问题七：根据上面的详细结果，按照产品类别进行分组，统计每组的记录数，并根据记录数对结果进行降序排序。
答案：SELECT prod_category, count(*) as cnt
来自产品
其中 prod_price > 20 且 vend_id = '28'
和 prod_name 类似 '%cookie%'
按产品类别分组
按产品类别 DESC 排序；
问题8：将产品名称和价格用空格分隔并合并到一个字段中。
答案：使用CONCAT函数。 CONCAT(产品名称,' ',产品价格)
问题9：将产品价格按照1-10元、11-100元、100元以上分为三组，进行分组统计并记录数量。
回答：
选择
当 prod_price 介于 1 和 10 之间时，则为 '1~10'
当 prod_price 介于 11 和 100 之间时，则 '11~100'
当 prod_price > 100 时，“大于 100”以 Price_group 结束，
计数(*)为cnt
来自产品
按价格组分组；
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表，存储产品供应商。 表中字段信息如下。 如何获取产品和供应商表中可以关联的部分中的产品数据。
列说明 vend_id 唯一供应商 ID vend_name 供应商名称 vend_address 供应商地址 vend_zip 供应商地址邮政编码 vend_city 供应商城市 答案：
//方法一：
选择产品。*
来自供应商、产品
WHERE Vendors.vend_id = Products.vend_id;
//方法2：
选择产品。*
来自产品
WHERE vend_id IN (选择不同的 vend_id
来自供应商）；
//方法3：
选择一个。*
来自产品 a
（内部）加入供应商 b
ON a.vend_id = b.vend_id;
问题12：检索Vendors 表中供应商ID 不在Products 中的数据。
回答：
选择一个。*
来自供应商
左连接 产品 b
ON a.vend_id = b.vend_id
其中 b.vend_id 为 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 基本示例)
.config(spark.some.config.option, 一些值)
.getOrCreate()
(2) 创建DataFrame，例如从json文件创建DataFrame。 val df = Spark.read.json(examples/src/main/resources/people.json)
//显示DataFrame的内容
df.show()
// +----+--------+
// | 年龄| 姓名|
// +----+--------+
// |null|迈克尔|
// | 30| 安迪|
// | 19| 贾斯汀|
// +----+--------
(3)执行SQL查询。 // 将 DataFrame 注册为临时视图
df.createOrReplaceTempView(人)
val sqlDF = Spark.sql(SELECT * FROM people LIMIT 3)
sqlDF.show()
// +----+--------+
// | 年龄| 姓名|
// +----+--------+
// |null|迈克尔|
// | 30| 安迪|
// | 19| 贾斯汀|
// +----+--------+
使用语法的其他示例。 import Spark.implicits._ //导入隐式转换包
//打印模式
df.printSchema()
// 根
// |-- 年龄：long (nullable = true)
// |-- 名称：字符串 (nullable = true)
//选择要打印的列
df.select(名称).show()
//选择20岁以上的
df.filter(#34;年龄 > 20).show()
//聚合操作
df.groupBy(年龄).count().show
问题16：SparkSql的数据保存语法是什么？
回答：
//例如，选择DataFrame中的两列并将其保存到json文件中。 您可以指定保存文件的格式。
df.select(name, favorite_color).write.format(json).save(namesAndFavColors.json)
//在DataFrame中选择两列并将它们附加到镶木地板文件中。 模式可以指定为append，也有overwrite模式。
df.select(name, favorite_color).write.mode(SaveMode.append)
.save(namesAndFavColors.镶木地板)
问题17：我们来说一下Hive中分区的概念，它的作用是什么，以及如何添加和删除分区？
答：在Hive中，表中的一个分区（Partition）对应于表下的一个目录。 分区的作用是辅助查询，缩小查询范围。 在HiveSql中限制分区条件可以加快数据检索速度。
//添加分区
更改表 day_table 添加分区（dt='2008-08-08'，小时='08'）位置 '/path/pv1.txt' 分区（dt='2008-08-08'，小时='09'）位置'/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函数。 评估函数支持重载。
包com.yqz.udf；
导入 org.apache.hadoop.hive.ql.exec.UDF;
导入 org.apache.hadoop.io.Text；
公共类 ConcatString 扩展 UDF {
// 字符串在hadoop中无法翻译
公共文本评估（文本a，文本b）{
返回新文本(a.toString() + ******* + b.toString());
在查询执行期间，该类将在查询中应用此函数的每个相应位置实例化。 每行输入都会调用evaluate() 函数。
2、将该类的包导出为jar包，放到linux目录下。 //先删除旧包
删除jar /data/yqz/hive/contactString.jar；
//添加新的jar包
添加jar /data/yqz/hive/contactString.jar； 需要注意的是，jar文件路径不需要用引号引起来。 该路径需要是当前文件系统的完整路径。 Hive不仅将jar文件放入classpath中，还将其添加到分布式缓存中，以便整个集群中的所有机器都可以获取该jar文件。
3、创建一个临时函数，指向jar包中的类。
//语法：创建临时函数作为'java类名';
//例子
创建临时函数 myconcat 作为 'com.yqz.udf.ConcatString'；
需要注意的是createtemporary函数中的temporary关键字表示在当前会话中声明的函数只在当前会话中有效。 所以用户需要在每个会话中添加jar，然后创建函数。 如果用户需要长期频繁使用同一个jar和函数，可以在$HOME/.hiverc文件中添加相关语句。
4.使用临时函数。//语法：
使用：选择（参数）；
删除：删除临时函数（如果存在）；
//例子
选择 myconcat('HELLO','world');
删除自定义临时函数时，添加ifexists，这样即使函数不存在也不会报错。 问题19：SQL和HiveSql中都有窗口函数。 你能告诉我窗口函数的基本语法吗？
回答：
()
超过
[分区依据]
[排序依据] [升序/降序]
（行 | 范围）
问题20：HiveSql使用的数据与传统SQL数据库中的数据存储方式不同。 您能解释一下HiveSql中使用的数据源的数据组织方法吗？
答：（1）Hive的数据组织包括数据库、表、视图、分区、桶和表数据等。数据库、表、分区等都对应HDFS上的一个目录。 Bucket和表数据对应HDFS相应目录下的文件。
(2) Hive中的所有数据都存储在HDFS中。 没有特殊的数据存储格式，因为 Hive 处于读取模式（Schema On Read），可以支持 TextFile、SequenceFile、RCFile 或自定义格式等。
(3)只需要在创建表时告诉Hive数据中的列分隔符和行分隔符，Hive就可以解析数据。
分隔符说明n对于文本文件，每一行都是一条记录，因此n用于分隔记录^A（Ctrl+A）用于分隔字段， 01也可以用于表示^B（Ctrl+B）用于分隔Arrary或Struct中的元素，或者用于在map中的键值之间分割，也可以用 02分割。 ^C用于将map中的键和值单独分隔开，也可以用 03表示。
(4) Hive包含以下数据模型： 数据库：在HDFS中表示为${hive.metastore.warehouse.dir}目录下的文件夹； table：在HDFS中表示为数据库目录下的文件夹； 分区：在HDFS中表示，在HDFS中，表现为表目录下的子目录； Bucket：在HDFS中，表现为同一个表目录或分区目录中根据某个字段的值进行哈希处理的多个文件； 视图：与传统数据库类似，只是基于基本表进行读取、创建；
（5）Hive的元数据存储在RDBMS中，除元数据外的所有其他数据均基于HDFS存储。 PS：需要强调的是，虽然在上面的例子16-22中，窗函数只是通过例子给出了语法结构，但在实际面试中，考察最多的是窗函数。 考点内容各异，没有统一的面试模板，都是面试官随机提出的问题，通常以实际案例的形式。 窗口函数可以实现HiveSql实际使用中的各种复杂查询，语法结构简单高效。 因此，强烈建议大家多练习。 在这个公众号之前，有一篇专门介绍Hive窗口函数的文章《Hive窗口函数使用指南》。 解释很详细，例子也很多。 你可以自己检查一下。
概括
本文将SQL/SparkSql/HiveSql放在一起，整理了一份常见题型的面试题库。 每个部分之前都给出了测试点的概述。 根据大纲，下面给出几道模拟试题。 每道试题的解释都尽可能全面、详细，帮助大家复习相关知识点。 所选试题仅作为典型示例。 例子、其他类比知识考点或者周边的知识细节还是需要更多的练习和总结。 只有做好充足的准备，才能找到理想的工作。 希望这篇文章能够对您有所帮助。
