spark操纵 mysql

最新推荐文章于 2024-12-05 17:02:22 发布

原创最新推荐文章于 2024-12-05 17:02:22 发布 · 110 阅读

0 ·

CC 4.0 BY-SA版权

hadoop 专栏收录该内容

25 篇文章

订阅专栏

博客提及了pom、Java和SQL相关内容，这些均为信息技术领域常见元素，pom常用于Java项目管理，Java是广泛使用的编程语言，SQL用于数据库操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

pom：

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0
         http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.songlk</groupId>
    <artifactId>testspark</artifactId>
    <version>1.0-SNAPSHOT</version>

    <build>
        <sourceDirectory>src/main/java</sourceDirectory>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>1.8</source>
                    <target>1.8</target>
                </configuration>
            </plugin>
            <plugin>
                <groupId>net.alchim31.maven</groupId>
                <artifactId>scala-maven-plugin</artifactId>
                <version>3.2.2</version>
                <configuration>
                    <recompileMode>incremental</recompileMode>
                </configuration>
                <executions>
                    <execution>
                        <goals>
                            <goal>compile</goal>
                            <goal>testCompile</goal>
                        </goals>
                    </execution>
                </executions>
            </plugin>
        </plugins>
    </build>

<dependencies>
    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-core_2.11</artifactId>
       <version>2.1.1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-mllib_2.11</artifactId>
        <version>2.1.1</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>5.1.17</version>
    </dependency>
    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-sql_2.11</artifactId>
        <version>2.1.1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-jdbc</artifactId>
        <version>2.1.0</version>
    </dependency>
    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-streaming_2.11</artifactId>
        <version>2.1.1</version>
    </dependency>
    <dependency>
        <groupId>org.apache.spark</groupId>
        <artifactId>spark-streaming-kafka-0-10_2.11</artifactId>
        <version>2.1.1</version>
    </dependency>
</dependencies>

</project>

java：

package com.it18zhang.spark.java;

import org.apache.spark.SparkConf;
import org.apache.spark.sql.*;

import java.util.Properties;

/**
 * Created by Administrator on 2017/4/3.
 */
public class SQLJDBCJava {
    public static void main(String[] args) {
        SparkConf conf = new SparkConf();
        conf.setMaster("local") ;
        conf.setAppName("SQLJava");
        SparkSession sess = SparkSession.builder()
                            .appName("SQLJava")
                            .config("spark.master","local")
                            .getOrCreate();
        String url = "jdbc:mysql://localhost:3306/mybatis" ;
        String table = "users" ;

        //查询数据库
        Dataset<Row> df = sess.read()
                .format("jdbc")
                .option("url", url)
                .option("dbtable", table)
                .option("user", "root")
                .option("password", "passwd")
                .option("driver", "com.mysql.jdbc.Driver")
                .load();
        df.show();

        //投影查询
        Dataset<Row> df2 = df.select(new Column("id"),new Column("name"));
        //过滤
        df2 = df2.where("name like 'je%'");
        //去重
        df2 = df2.distinct();


        Properties prop = new Properties();
        prop.put("user", "root");
        prop.put("password", "passwd");
        prop.put("driver", "com.mysql.jdbc.Driver");

        //写入
        df2.write().jdbc(url,"subusers",prop);
        df2.show();

    }
}

sql：

CREATE TABLE `items` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`itemname` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

`oid` int(11) NULL DEFAULT NULL,

PRIMARY KEY (`id`) 

)

ENGINE=InnoDB

DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

AUTO_INCREMENT=9
;



CREATE TABLE `orders` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`orderno` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

`uid` int(11) NULL DEFAULT NULL,

PRIMARY KEY (`id`) 

)

ENGINE=InnoDB

DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

AUTO_INCREMENT=5
;



CREATE TABLE `subusers` (

`id` int(11) NOT NULL,

`name` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL

)

ENGINE=InnoDB

DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
;



CREATE TABLE `users` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,

`age` int(11) NULL DEFAULT NULL,

PRIMARY KEY (`id`) 

)

ENGINE=InnoDB

DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci

AUTO_INCREMENT=6
;