day15-数据源

最新推荐文章于 2024-07-28 16:45:11 发布

shenjinrui

最新推荐文章于 2024-07-28 16:45:11 发布

阅读量108

点赞数

分类专栏： bigdata 文章标签：大数据 spark

本文链接：https://blog.csdn.net/shenjinrui/article/details/103911616

版权

bigdata 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

前言

day14，我们学习了Spark SQL的DataFrame。今天开始进入Spark SQL的数据源的学习。

Spark数据源介绍

在Spark-sql中可以使用各种各样的数据源来创建DataFrame或者DataSet，spark-sql对数据源兼容性比较好，并且提供了load 方法来加载数据，save方法保存数据源。load、save的时候默认都是以parquert格式处理。

parquet数据源

spark官方也提供了一些parquet数据源Demo，存放在Spark安装主目录的examples/src/main/resources/下面，下面使用官方提供的parquet数据源进行一些演示。

#读取parquert数据
scala> var df2 = spark.read.load("/opt/module/spark-2.1.0-bin-hadoop2.7/examples/src/main/resources/users.parquet")
df2: org.apache.spark.sql.DataFrame = [name: string, favorite_color: string ... 1 more field]
#查看数据
scala> df2.show
+------+--------------+----------------+
|  name|favorite_color|favorite_numbers|
+------+--------------+----------------+
|Alyssa|          null|  [3, 9, 15, 20]|
|   Ben|           red|              []|
+------+--------------+----------------+

#把查询结果保存起来
scala> df2.select($"name",$"favorite_color").write.mode("overwrite").save("/root/parquet")
#把上面保存的结果重新load进来
scala> var df3 = spark.read.load("/root/parquet")
df3: org.apache.spark.sql.DataFrame = [name: string, favorite_color: string]
#显示数据
scala> df3.show
+------+--------------+
|  name|favorite_color|
+------+--------------+
|Alyssa|          null|
|   Ben|           red|
+------+--------------+
scala>

使用JDBC

通过JDBC连接MySQL关系型数据库，将数据加载到Spark-SQL中处理。
使用JDBC，需要驱动，在启动Spark-shell的时候需要添加添加相关jar包。

#启动spark shell,需要指定mysql连接的驱动
[root@bigdata121 bin]# ./spark-shell --master spark://bigdata121:7077 --jars /root/mysql-connector-java-5.1.27.jar --driver-class-path /root/mysql-connector-java-5.1.27.jar
Using Spark's default log4j profile: org/apache/spark/log4j-defaults.properties
Setting default log level to "WARN".
To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).
20/01/09 18:06:44 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
20/01/09 18:06:50 WARN ObjectStore: Failed to get database global_temp, returning NoSuchObjectException
Spark context Web UI available at http://192.168.80.121:4040
Spark context available as 'sc' (master = spark://bigdata121:7077, app id = app-20200109180645-0001).
Spark session available as 'spark'.
Welcome to
      ____              __
     / __/__  ___ _____/ /__
    _\ \/ _ \/ _ `/ __/  '_/
   /___/ .__/\_,_/_/ /_/\_\   version 2.1.0
      /_/
         
Using Scala version 2.11.8 (Java HotSpot(TM) 64-Bit Server VM, Java 1.8.0_121)
Type in expressions to have them evaluated.
Type :help for more information.
#连接Mysql
scala> var df1 = spark.read.format("jdbc").option("url","jdbc:mysql://192.168.80.10:3306/jinrui?nserverTimezone=UTC&characterEncoding=utf-8").option("user","root").option("password","123456").option("driver","com.mysql.jdbc.Driver").option("dbtable","emp").load
df1: org.apache.spark.sql.DataFrame = [empno: int, ename: string ... 6 more fields]
#查看查询到的数据
scala> df1.show
+-----+-----+------+----+----------+----+----+------+
|empno|ename|   job| mgr|  hiredate| sal|comm|deptno|
+-----+-----+------+----+----------+----+----+------+
| 7369|smith| CLERK|7902|1980-12-17| 800|null|    20|
| 7499|aller|SALESN|7698|1990-04-10|1600| 300|    30|
| 7521| ward|SALESN|7698|1981-11-17|1250| 500|    20|
| 7788|scott| ANALY|7566|1986-05-27|3000|null|    20|
+-----+-----+------+----+----------+----+----+------+
scala>