SparkSQL读取Excel数据源——spark-excel

spark
spark
发布日期:   2019-12-13

在Github上面看到一个针对SparkSQL加载Excel数据源的开源组件，拿过来测试了一下spark-excel

1.Spark Excel库

用于使用Apache Spark查询Excel文件的库，用于Spark SQL和DataFrames。

此包允许将Excel电子表格作为Spark DataFrames进行查询。

2.Spark版本要求

该库需要Spark 2.0+

3.重新编译

在github上面该项目代码是用scala2.11版本编译的，我本地测试环境是spark2.4 scala 2.12.8 所以下载源码在服务器上重新编译了一下。如果你使用的是scala2.11版本则可以跳过该步骤。

3.1.安装sbt

因为该项目使用sbt编译的，需要安装sbt 下载好sbt上传到服务器上之后配置环境变量即可。

3.2.下载源码

下载spark-excel源码，并上传服务器。

3.3.修改build.sbt配置

修改Spark 版本到2.4.0
修改scala 版本到2.12.8 如下图
修改sbt配置

3.4.打包

1
sbt assembly

打包

4.添加依赖

在pom.xml中添加依赖 或手动导入刚刚编译的包

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
<!-- https://mvnrepository.com/artifact/com.norbitltd/spoiwo -->
<dependency>
    <groupId>com.norbitltd</groupId>
    <artifactId>spoiwo_2.12</artifactId>
    <version>1.4.1</version>
</dependency>
 
<!-- https://mvnrepository.com/artifact/com.monitorjbl/xlsx-streamer -->
<dependency>
    <groupId>com.monitorjbl</groupId>
    <artifactId>xlsx-streamer</artifactId>
    <version>2.1.0</version>
</dependency>
 
<!-- https://mvnrepository.com/artifact/com.crealytics/spark-excel -->
<!--依赖的spark excel 刚刚手动编译导入，如果使用的是scala2.11版本可以直接用此依赖-->
<!-- 
<dependency>
     <groupId>com.crealytics</groupId>
     <artifactId>spark-excel_2.11</artifactId>
     <version>0.11.1</version>
</dependency>
-->

5.读取Excel文件

5.1 代码示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
/**
  * @author hnbian 2019/3/5 14:29
  */
object ExcelData {
  def main(args: Array[String]): Unit = {
    val spark = SparkUtils.getSparkSession("ExcelData", 4)
    val sqlContext = spark.sqlContext
    
    val filePath = "D:\\test.xlsx"
 
    //定义数据结构
    val schema = StructType(List(
      StructField("c1", StringType, nullable = false),
      StructField("c2", StringType, nullable = false),
      StructField("c3", StringType, nullable = false),
      StructField("c4", StringType, nullable = false),
      StructField("c5", StringType, nullable = false)))
 
    load(filePath,spark,schema)
  }
 
  /**
    * 加载Excel数据
    * @param filePath 文件路今天
    * @param spark SparkSession
    * @param schema 数据结构
    */
  def load(filePath:String,spark:SparkSession,schema:StructType): Unit ={
    val df = spark.read
      .format("com.crealytics.spark.excel")
      .option("dataAddress", "'Sheet2'!A1:G2") // 可选,设置选择数据区域 例如 A1:E2。
      .option("useHeader", "false") // 必须，是否使用表头，false的话自己命名表头（_c0）,true则第一行为表头
      .option("treatEmptyValuesAsNulls", "true") // 可选, 是否将空的单元格设置为null ,如果不设置为null 遇见空单元格会报错 默认t: true
      .option("inferSchema", "true") // 可选, default: false
      //.option("addColorColumns", "true") // 可选, default: false
      //.option("timestampFormat", "yyyy-mm-dd hh:mm:ss") // 可选, default: yyyy-mm-dd hh:mm:ss[.fffffffff]
      //.option("excerptSize", 6) // 可选, default: 10. If set and if schema inferred, number of rows to infer schema from
      //.option("workbookPassword", "pass") // 可选, default None. Requires unlimited strength JCE for older JVMs====
      //.option("maxRowsInMemory", 20) // 可选, default None. If set, uses a streaming reader which can help with big files====
      .schema(schema) // 可选, default: Either inferred schema, or all columns are Strings
      .load(filePath)
    df.show()
  }
}

5.2 表格样式

5.2.1 是否使用表头

  • excel 中测试数据
A B C D E
1 a1 a2 a3 a4 a5
2 xiaol xiaom xiaoh xiaoy xiaog

是否使用表头，不使用的话自己命名表头（_c0）, 如果使用则第一行为表头
参数名称： .option(“useHeader”, “false”) //参数为必须

  • 使用表头
1
2
3
.option("dataAddress", "'Sheet2'!A1:E2") 
.option("useHeader", "true")
df.show()
_c0 _c1 _c2 _c3 _c4
a1 a2 a3 a4 a5
xiaol xiaom xiaoh xiaoy xiaog
  • 不使用表头
1
2
3
.option("dataAddress", "'Sheet2'!A1:E2") 
.option("useHeader", "false")
df.show()
a1 a2 a3 a4 a5
xiaol xiaom xiaoh xiaoy xiaog

5.2.2 选择数据区域

  • 选择a1 c1的数据
1
2
3
.option("dataAddress", "'Sheet2'!A1:C1") 
.option("useHeader", "false")
df.show()
_c0 _c1 _c2
a1 a2 a3

5.2.3 修改空单元格

如果表格中有空数据但不做处理的话会抛出异常
可以使用 .option(“treatEmptyValuesAsNulls”, “true”) // 可选, 默认: true
参数将空的单元格设置为null

  • excel 中测试数据
A B C D E
1 a1 a2 a3 a4 a5
2 xiaol xiaom xiaoh xiaog

如上面表格中D2单元格为空的
设置替换空数值之后表格内容如下：

1
2
3
.option("useHeader", "true") 
.option("treatEmptyValuesAsNulls", "true")
df.show()
_c0 _c1 _c2 _c3 _c4
a1 a2 a3 a4 a5
xiaol xiaom xiaoh null xiaog

5.2.4 设置schema

设置schema 时需要注意在表格中String 类型数据的单元格不能有为空否则会报空指针。
如果设置范围的列数多余schame的列数那么多余的数据不会读取出来。

参数名称：**.schema(schema) **// 可选

  • excel 中测试数据
A B C D E
1 a1 a2 a3 a4 a5
2 xiaol xiaom xiaoh xiaoy xiaog
  • 设置schema
1
2
3
4
5
6
7
8
9
10
11
12
//定义数据结构
val schema = StructType(List(
  StructField("c1", StringType, nullable = false),
  StructField("c2", StringType, nullable = false),
  StructField("c3", StringType, nullable = false),
  StructField("c4", StringType, nullable = false),
  StructField("c5", StringType, nullable = false)))
  
.option("useHeader", "false")
.schema(schema)
df.show()

c1 c2 c3 c4 c5
a1 a2 a3 a4 a5
xiaol xiaom xiaoh xiaoy xiaog

6.保存数据到Excel文件中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
import hnbian.spark.utils.SparkUtils
import org.apache.spark.sql.{DataFrame, SparkSession}
import org.apache.spark.sql.types._
 
/**
  * @author hnbian 2019/3/5 14:29
  */
object ExcelData {
  def main(args: Array[String]): Unit = {
    val spark = SparkUtils.getSparkSession("ExcelData", 4)
    val sqlContext = spark.sqlContext
 
    val filePath = "D:\\test.xlsx"
    val fileSavePath = "D:\\testWrite.xlsx"
 
    //定义数据结构
    val schema = StructType(List(
      StructField("c1", StringType, nullable = false),
      StructField("c2", StringType, nullable = false),
      StructField("c3", StringType, nullable = false),
      StructField("c4", StringType, nullable = false),
      StructField("c5", StringType, nullable = false),
      StructField("c6", DateType, nullable = false)))
 
    val df = load(filePath,spark,schema)//读取Excel 文件
    save(fileSavePath,df) //把刚刚都出来的内容写到另外一个文件中（复制上一个文件）
  }
 
  /**
    * 将数据保存到Excel文件中
    * @param filePath 保存路径
    * @param df 数据集
    */
  def save(filePath:String,df:DataFrame): Unit ={
    df.write
      .format("com.crealytics.spark.excel")
      .option("dataAddress", "'Sheet'!A1:E2")
      .option("useHeader", "true")
      //.option("dateFormat", "yy-mmm-d") // Optional, default: yy-m-d h:mm
      //.option("timestampFormat", "mm-dd-yyyy hh:mm:ss") // Optional, default: yyyy-mm-dd hh:mm:ss.000
      .mode("append") // Optional, default: overwrite.
      .save(filePath)
  }
 
  /**
    * 加载Excel数据
    * @param filePath 文件路径
    * @param spark SparkSession
    * @param schema 数据结构
    */
  def load(filePath:String,spark:SparkSession,schema:StructType): DataFrame ={
    val df = spark.read
      .format("com.crealytics.spark.excel")
      .option("dataAddress", "'Sheet2'!A1:E2") // 可选,设置选择数据区域 例如 A1:E2。
      .option("useHeader", "false") // 必须，是否使用表头，false的话自己命名表头（_c0）,true则第一行为表头
      .option("treatEmptyValuesAsNulls", "true") // 可选, 是否将空的单元格设置为null ,如果不设置为null 遇见空单元格会报错 默认t: true
      .option("inferSchema", "true") // 可选, default: false
      //.option("addColorColumns", "true") // 可选, default: false
      //.option("timestampFormat", "yyyy-mm-dd hh:mm:ss") // 可选, default: yyyy-mm-dd hh:mm:ss[.fffffffff]
      //.option("excerptSize", 6) // 可选, default: 10. If set and if schema inferred, number of rows to infer schema from
      //.option("workbookPassword", "pass") // 可选, default None. Requires unlimited strength JCE for older JVMs====
      //.option("maxRowsInMemory", 20) // 可选, default None. If set, uses a streaming reader which can help with big files====
      .schema(schema) // 可选, default: Either inferred schema, or all columns are Strings
      .load(filePath)
    df.show()
    df
  }
}
文章作者: hnbian
文章链接: https://www.hnbian.cn/posts/4dd7e505.html
版权声明: 本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源 hnbian !
spark
评论
  目录