SparkSql将mysql数据接入到es

绝地灬酷狼 2022-12-29 09:39 108阅读 0赞

在es创建一个表

PUT /education4
    {  "settings": {                                                
        "number_of_shards": 1
      },
    "mappings": { 
         "_doc":{   
           "properties":{ 
             "work_experience":{ 
               "type":"keyword"
             },
             "avg_salary":{ 
               "type":"double"
             },
             "sum_salary":{ 
               "type":"keyword"
             },
             "p_num":{ 
               "type":"keyword"
             }
    }
    }
    }
    }

MySQL里面的数据  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70]

pom.xml

<dependency>
                <groupId>io.searchbox</groupId>
                <artifactId>jest</artifactId>
                <version>5.3.3</version>
                <exclusions>
                    <exclusion>
                        <groupId>org.slf4j</groupId>
                        <artifactId>slf4j-api</artifactId>
                    </exclusion>
                </exclusions>
            </dependency>
    
            <dependency>
                <groupId>net.java.dev.jna</groupId>
                <artifactId>jna</artifactId>
                <version>4.5.2</version>
            </dependency>
    
            <dependency>
                <groupId>org.codehaus.janino</groupId>
                <artifactId>commons-compiler</artifactId>
                <version>2.7.8</version>
            </dependency>
            
            <dependency>
                <groupId>org.elasticsearch</groupId>
                <artifactId>elasticsearch</artifactId>
                <version>2.4.6</version>
            </dependency>
    
            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.27</version>
            </dependency>
            <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-sql_2.11</artifactId>
                <version>2.1.1</version>
            </dependency>
            <dependency>
                <groupId>org.elasticsearch</groupId>
                <artifactId>elasticsearch-spark-20_2.11</artifactId>
                <version>6.6.0</version>
            </dependency>

代码实现

import org.apache.spark.SparkConf
    import org.apache.spark.sql.SparkSession
    import org.elasticsearch.spark.sql.EsSparkSQL
    
    object QianCheng { 
      def main(args: Array[String]): Unit = { 
        val sparkConf = new SparkConf().setAppName("es").setMaster("local[*]")
        sparkConf.set("es.nodes","192.168.121.140")
        sparkConf.set("es.port","9200")
        sparkConf.set("es.index.auto.create", "true")
        sparkConf.set("es.write.operation", "index")
        val spark = SparkSession.builder()
          .master("local[*]")
          .appName("JDBCRead")
          .config(sparkConf)
          .getOrCreate()
        import  spark.implicits._
    
        val url = "jdbc:mysql://hadoop102:3306/python"
        val user = "root"
        val pw = "yy8266603"
    
        val df = spark.read
          .option("url", url)
          .option("user", user)
          .option("password", pw)
          .option("dbtable", "experience")
          .format("jdbc").load()
    
    
        EsSparkSQL.saveToEs(df,"/education4/_doc")
    
        spark.close()
      }
    
    }

执行去mysql查看结果  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 1]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70]: /images/20221120/e09b9a7cfc634787a1641b879baa27ac.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQ2NTQ4ODU1_size_16_color_FFFFFF_t_70 1]: /images/20221120/0429df6cea4e4dbab78db9b8d2128f97.png