# spark-sql

**Repository Path**: Gcx/spark-sql

## Basic Information

- **Project Name**: spark-sql
- **Description**: No description available
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2022-05-08
- **Last Updated**: 2022-08-21

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

## 作业一：为 Spark SQL 添加一条自定义命令
SHOW VERSION；

显示当前 Spark 版本和 Java 版本

1. SqlBase.g4添加语法规则

   1.1 statement

   | SHOW VERSION #showVersion


   1.2 ansiNonReserved

   | VERSION


   1.3 nonReserved

   | VERSION

   1.4 //--SPARK-KEYWORD-LIST-START

   VERSION: 'VERSION' | 'V';

2. Maven antlr4 插件编译规则

![输入图片说明](src/main/resources/img/antlr4.png)


3. 增加 ShowVersionCommand 类
   ![输入图片说明](src/main/resources/img/commands.png)

4. 修在 SparkSqlParser.scala 中增加 visitShowVersion 方法
   ![输入图片说明](src/main/resources/img/sparksqlparser.png)

5. 编译
   build/mvn clean package -DskipTests
   ![输入图片说明](src/main/resources/img/showVersion.png)


## 作业二：构建 SQL 满足如下要求
通过 set spark.sql.planChangeLog.level=WARN，查看：
1. 构建一条 SQL，同时 apply 下面三条优化规则：
   CombineFilters、CollapseProject、BooleanSimplification

sql语句:

   CREATE TABLE t1(a1 INT, a2 INT) USING parquet;
   SELECT a11, (a2 + 1) AS a21
   FROM (
   SELECT (a1 + 1) AS a11, a2 FROM t1 WHERE a1 > 10
   ) WHERE a11 > 1 AND 1 = 1;

截图：

![输入图片说明](src/main/resources/img/sparksql1.png)



2. 构建一条 SQL，同时 apply 下面五条优化规则：
   ConstantFolding、PushDownPredicates、ReplaceDistinctWithAggregate、
   ReplaceExceptWithAntiJoin、FoldablePropagation


CREATE TABLE t1(a1 INT, a2 INT) USING parquet;


CREATE TABLE t2(b1 INT, b2 INT) USING parquet;


SELECT DISTINCT a1, a2, 'custom' a3
FROM (
SELECT * FROM t1 WHERE a2 = 10 AND 1 = 1
) WHERE a1 > 5 AND 1 = 1
EXCEPT SELECT b1, b2, 1.0 b3 FROM t2 WHERE b2 = 10 ;

截图：

![输入图片说明](src/main/resources/img/sparksql2.png)

## 作业三：实现自定义优化规则（静默规则）

第一步：实现自定义规则 (静默规则，通过 set spark.sql.planChangeLog.level=WARN，确认执行到就行)

第二步：创建自己的 Extension 并注入

![输入图片说明](src/main/resources/img/Silence-rule.png)

![输入图片说明](src/main/resources/img/Silence-rule-result.png)