sparkSql连接Oracle

1、测试数据

2、代码

package com.cn.sparkSql

import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.types.Decimal


object SparkSqlConOracle {
  def main(args: Array[String]): Unit = {
    val spark = SparkSession
      .builder()
      .appName("SparkSqlConOracle")
      .master("local[*]")
      .getOrCreate()
    //设置输出日志级别
    spark.sparkContext.setLogLevel("WARN")
    val odf=spark.read
      .format("jdbc")
      .option("url", "jdbc:oracle:thin:@//127.0.0.1:1521/orcl")
      .option("dbtable", "student")
      .option("user", "system")
      .option("password", "123456")
      .load()
    //为了避免重复创建同一个视图，而报错，创建之前最好先判断是否存在
    val viewFlag = spark.catalog.tableExists("stu")
    if(!viewFlag){
      odf.createTempView("stu")
    }

    val df = spark.sql("select * from stu")
    //将name,sex转化为：key=name,value=sex的map
    val map = df.rdd.map(row=>(row.get(0),row.get(2))).collect().toMap
    for(key<-map.keys){
      val value=map.get(key)
      value match {
        case Some(v) =>println("key:"+key+ ", value:"+v)
      }
    }
    //取出姓名的第一列
    val name = df.select("name").rdd.first().toString()
        .replace("[","")
        .replace("]","")
    println(name)
  }
}

3、输出

key:lisi, value:男
key:wangli, value:女
lisi