spark3.0教程-SparkSQL：连接MySQL，读取数据

作者：马育民 • • 阅读：10067

说明

SparkSQL 可以通过 JDBC 连接 MySQL 数据，读取数据，并创建 DataFrame

关键代码

// 指定用户名、密码
val properties = new Properties()
properties.setProperty("user","root")
properties.setProperty("password","root")

val df: DataFrame = spark.read.jdbc("url","表名",properties)

案例

MySQL

创建 spark 数据库：

CREATE DATABASE `spark` /*!40100 COLLATE 'utf8mb4_unicode_ci' */

创建 cdz 表：

CREATE TABLE `cdz` (
    `id` INT(11) NOT NULL,
    `ele_num` INT(11) NULL DEFAULT NULL,
    `time` INT(11) NULL DEFAULT NULL,
    `start_time` DATETIME NULL DEFAULT NULL,
    `end_time` DATETIME NULL DEFAULT NULL
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

插入数据：

INSERT INTO `cdz` (`id`, `ele_num`, `time`, `start_time`, `end_time`) VALUES (1, 100, 10, '2020-01-01 09:30:50', '2020-01-01 09:40:50');
INSERT INTO `cdz` (`id`, `ele_num`, `time`, `start_time`, `end_time`) VALUES (2, 200, 25, '2020-01-11 10:40:50', '2020-01-11 11:05:50');
INSERT INTO `cdz` (`id`, `ele_num`, `time`, `start_time`, `end_time`) VALUES (3, 150, 12, '2020-01-01 11:20:50', '2020-01-01 11:32:50');
INSERT INTO `cdz` (`id`, `ele_num`, `time`, `start_time`, `end_time`) VALUES (1, 300, 50, '2020-01-03 14:30:50', '2020-01-03 15:20:10');
INSERT INTO `cdz` (`id`, `ele_num`, `time`, `start_time`, `end_time`) VALUES (1, 50, 5, '2020-02-02 12:30:50', '2020-02-02 12:35:50');
INSERT INTO `cdz` (`id`, `ele_num`, `time`, `start_time`, `end_time`) VALUES (2, 500, 50, '2020-02-02 15:30:30', '2020-02-02 17:20:30');
INSERT INTO `cdz` (`id`, `ele_num`, `time`, `start_time`, `end_time`) VALUES (3, 150, 12, '2020-02-01 11:20:50', '2020-02-01 11:32:50');
INSERT INTO `cdz` (`id`, `ele_num`, `time`, `start_time`, `end_time`) VALUES (3, 150, 12, '2020-02-03 11:20:50', '2020-02-03 11:32:50');
INSERT INTO `cdz` (`id`, `ele_num`, `time`, `start_time`, `end_time`) VALUES (1, 100, 10, '2020-03-01 09:30:50', '2020-03-01 09:40:50');
INSERT INTO `cdz` (`id`, `ele_num`, `time`, `start_time`, `end_time`) VALUES (1, 100, 10, '2020-03-05 09:30:50', '2020-03-05 09:40:50');
INSERT INTO `cdz` (`id`, `ele_num`, `time`, `start_time`, `end_time`) VALUES (2, 500, 120, '2020-03-12 15:30:30', '2020-03-12 17:30:30');
INSERT INTO `cdz` (`id`, `ele_num`, `time`, `start_time`, `end_time`) VALUES (2, 500, 120, '2020-03-22 15:30:30', '2020-03-22 17:30:30');
INSERT INTO `cdz` (`id`, `ele_num`, `time`, `start_time`, `end_time`) VALUES (3, 400, 50, '2020-03-02 17:30:20', '2020-03-02 18:20:20');
INSERT INTO `cdz` (`id`, `ele_num`, `time`, `start_time`, `end_time`) VALUES (4, 100, 5, '2020-04-01 11:20:50', '2020-04-01 11:25:50');
INSERT INTO `cdz` (`id`, `ele_num`, `time`, `start_time`, `end_time`) VALUES (4, 50, 8, '2020-04-21 11:20:50', '2020-04-21 11:28:50');
INSERT INTO `cdz` (`id`, `ele_num`, `time`, `start_time`, `end_time`) VALUES (5, 150, 12, '2020-04-11 11:20:50', '2020-04-11 11:32:50');
INSERT INTO `cdz` (`id`, `ele_num`, `time`, `start_time`, `end_time`) VALUES (6, 150, 20, '2020-04-15 11:20:50', '2020-04-15 11:40:50');

pom.xml

添加下面依赖

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.47</version>
</dependency>

全部依赖

<dependencies>
    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-core_2.12</artifactId>
        <version>3.0.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-yarn_2.12</artifactId>
        <version>3.0.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-sql_2.12</artifactId>
        <version>3.0.0</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.47</version>
    </dependency>
</dependencies>

<build>
    <sourceDirectory>src/main/scala</sourceDirectory>
    <plugins>
        <plugin>
            <groupId>net.alchim31.maven</groupId>
            <artifactId>scala-maven-plugin</artifactId>
            <version>3.3.2</version>
            <executions>
                <execution>
                    <goals>
                        <goal>compile</goal>
                        <goal>testCompile</goal>
                    </goals>
                </execution>
            </executions>
        </plugin>
    </plugins>
</build>

java

package read

import java.util.Properties

import org.apache.spark.SparkConf
import org.apache.spark.sql.{DataFrame, SparkSession}

object Std_read_jdbc {
  def main(args: Array[String]): Unit = {
    val sc = new SparkConf().setMaster("local[*]").setAppName("std")
    val spark = SparkSession.builder().config(sc).getOrCreate()

    // 指定用户名、密码
    val properties = new Properties()
    properties.setProperty("user","root")
    properties.setProperty("password","root")

    val df: DataFrame = spark.read
        .jdbc("jdbc:mysql://localhost:3306/spark","cdz",properties)

    df.printSchema()
    //显示所有数据
    df.show()
    spark.close()
  }
}

执行结果

root
 |-- id: integer (nullable = true)
 |-- ele_num: integer (nullable = true)
 |-- time: integer (nullable = true)
 |-- start_time: timestamp (nullable = true)
 |-- end_time: timestamp (nullable = true)


+---+-------+----+-------------------+-------------------+
| id|ele_num|time|         start_time|           end_time|
+---+-------+----+-------------------+-------------------+
|  1|    100|  10|2020-01-01 09:30:50|2020-01-01 09:40:50|
|  2|    200|  25|2020-01-11 10:40:50|2020-01-11 11:05:50|
|  3|    150|  12|2020-01-01 11:20:50|2020-01-01 11:32:50|
|  1|    300|  50|2020-01-03 14:30:50|2020-01-03 15:20:10|
|  1|     50|   5|2020-02-02 12:30:50|2020-02-02 12:35:50|
|  2|    500|  50|2020-02-02 15:30:30|2020-02-02 17:20:30|
|  3|    150|  12|2020-02-01 11:20:50|2020-02-01 11:32:50|
|  3|    150|  12|2020-02-03 11:20:50|2020-02-03 11:32:50|
|  1|    100|  10|2020-03-01 09:30:50|2020-03-01 09:40:50|
|  1|    100|  10|2020-03-05 09:30:50|2020-03-05 09:40:50|
|  2|    500| 120|2020-03-12 15:30:30|2020-03-12 17:30:30|
|  2|    500| 120|2020-03-22 15:30:30|2020-03-22 17:30:30|
|  3|    400|  50|2020-03-02 17:30:20|2020-03-02 18:20:20|
|  4|    100|   5|2020-04-01 11:20:50|2020-04-01 11:25:50|
|  4|     50|   8|2020-04-21 11:20:50|2020-04-21 11:28:50|
|  5|    150|  12|2020-04-11 11:20:50|2020-04-11 11:32:50|
|  6|    150|  20|2020-04-15 11:20:50|2020-04-15 11:40:50|
+---+-------+----+-------------------+-------------------+

原文出处：/show_1IX4KLRThFTX.html

吉ICP备15003043号