将mysql查询的表数据插入到hive库

最新推荐文章于 2022-12-31 15:29:10 发布

原创最新推荐文章于 2022-12-31 15:29:10 发布 · 260 阅读

0 ·

CC 4.0 BY-SA版权

Spark 同时被 2 个专栏收录

4 篇文章

订阅专栏

数据开发

2 篇文章

订阅专栏

本文介绍了一种使用Apache Spark将MySQL数据库中的数据迁移到Hive数据仓库的方法。通过Java编程，利用SparkSession读取MySQL数据，并将其写入Hive表，实现数据的高效迁移。

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;
import java.util.Properties;

public class MysqlTohive {
    // TODO: 2020/9/16 将mysql查询的表数据插入到hive库

    public static void main(String[] args) {

        SparkSession spark = SparkSession.builder().appName("sqlToHive").getOrCreate();
        Properties dbConfPro = new Properties();
        dbConfPro.setProperty("user", "root");
        dbConfPro.setProperty("password", "root");

        String query="(select * from student) m";
        Dataset<Row> result = spark.read().jdbc("jdbc:mysql://192.168.8.103:3306/test?rewriteBatchedStatements=true", query, dbConfPro);

        //创建对应的视图表
        result.createOrReplaceTempView("student");
        spark.catalog().setCurrentDatabase("test");
        String query2="insert overwrite table student select * from student";
        spark.sql(query2);


    }
}

所需pom.xml

 <dependencies>
        <dependency>
            <groupId>org.apache.spark</groupId>
            <artifactId>spark-core_2.11</artifactId>
            <version>2.1.0</version>
        </dependency>
        <dependency>
            <groupId>org.apache.spark</groupId>
            <artifactId>spark-sql_2.11</artifactId>
            <version>2.1.0</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.spark/spark-hive -->
        <dependency>
            <groupId>org.apache.spark</groupId>
            <artifactId>spark-hive_2.11</artifactId>
            <version>2.1.0</version>
            <scope>provided</scope>
        </dependency>


        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.27</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/org.apache.logging.log4j/log4j-core -->
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.12.1</version>
        </dependency>

    </dependencies>