Spark on Maxconpute 读取资源 SQL DataFrame Json 转换开发

大数据00

已于 2023-08-18 15:36:48 修改

阅读量818

点赞数 2

分类专栏：阿里云 Spark 文章标签： spark 大数据 scala

于 2022-10-20 17:55:15 首次发布

本文链接：https://blog.csdn.net/weixin_44996457/article/details/127431953

版权

阿里云同时被 2 个专栏收录

12 篇文章 0 订阅

订阅专栏

Spark

2 篇文章 0 订阅

订阅专栏

1、Utils类创建SparkSession 入口

package com.gwm.utils;

import org.apache.spark.SparkConf;

/**
 * @author yangyingchun
 * @version 1.0
 * @date 2022/10/17 14:32
 */
public class SparkConfUtil {
    static SparkConf sparkConf;
    public static SparkConf getloaclSparkConf(String appname,String parallelism){
        sparkConf = (new SparkConf())
                .setAppName(appname)
                .setMaster("local["+parallelism+"]");
        return  sparkConf;
    }
    public static SparkConf getSparkConf(String appname){
        sparkConf = (new SparkConf())
                .setAppName(appname)
                ;
        return  sparkConf;
    }
}

2、 config.properties 配置信息

config.properties
##ODPS相关
itsl.gwm_accessId=taUG******574jX
itsl.gwm_accessKey=QTiO*********pV8mqlr9wXFT
itsl.yyc_accessId=9y*********bAKn6PsIr
itsl.yyc_accessKey=yPW***********jTxhemqQzPj8YFMXQ
itsl_dev.gwm_odps_project=itsl_dev
itsl.gwm_odps_project=itsl
dwd.gwm_odps_project=dwd
gwm_odps_driver=com.aliyun.odps.jdbc.OdpsDriver
gwm_odps_url=jdbc:odps:http://******************************d01.odps.ops.cloud.gwm.cn/api?project=ITSL_dev&charset=UTF-8&interactiveMode=true
gwm_odps_endpoint=http://******************************d01.odps.ops.cloud.gwm.cn/api

3、Spark 编程

package com.gwm.driver

import com.gwm.utils.{ConfigPropUtils, SparkSessionUtil}
import org.apache.spark.sql.{Column, ColumnName, DataFrame, Dataset, Row, SaveMode, SparkSession}
import scala.collection.mutable
import scala.io.Source


/**
 * @author yangyingchun
 * @date 2022/10/18 13:32
 * @version 1.0
 */
object EventOrderSuccessScala {
  var accessId = ConfigPropUtils.get("itsl.yyc_accessId")
  var accessKey = ConfigPropUtils.get("itsl.yyc_accessKey")
  var endPoint = ConfigPropUtils.get("gwm_odps_endpoint")
  var project = ConfigPropUtils.get("itsl.gwm_odps_project")
  val appName = EventOrderSuccessScala.getClass.getSimpleName
  //map 读取配置信息
  //querySql:sql_info
  //inColumns:"","",""
  //outColumns:,
  //outputTable:${data_date}
  //accessId:
  //accessKey:
  //endPoint:
  //project:
  var map = new mutable.HashMap[String,String]()

  def main(args: Array[String]): Unit = {

    //通过上传文件的方式构建
    val targetFile = args(0)
    val data_date = args(1)
    val outputTable = args(2)
    val file = Source.fromFile(targetFile)
    var strings: Iterator[String] = file.getLines()
    for (line <- strings) {
        println(line)
        val strings1 = line.split("\\^")
        map.put(strings1(0),strings1(1))
    }
    file.close

    //1.todo.初始化编程入口

    if (map.contains("accessId"))
        accessId = map.get("accessId").get
    if (map.contains("accessKey"))
        accessKey = map.get("accessKey").get
    if (map.contains("endPoint"))
        endPoint = map.get("endPoint").get
    if (map.contains("project"))
        project = map.get("project").get

    println("accessId:"+accessId)
    println("accessKey:"+accessKey)
    println("endPoint:"+endPoint)
    println("project:"+project)
//    val spark = SparkSessionUtil.getLocalParameter(appName, "*", accessId, accessKey, endPoint, project)
    val spark = SparkSessionUtil.getParameter(appName,accessId,accessKey,endPoint,project)

    //2.todo.读取数据
    println(map.get("querySql").get.concat(data_date))
    var  sql = map.get("querySql").get.concat(data_date)
    var frame: DataFrame = spark.sql(sql)
    import spark.implicits._
    frame.show()
    //3.todo.数据转换为JSON格式
    println(map.get("inColumns").get)
    println(map.get("outColumns").get)
    var inColumns: Array[String] = map.get("inColumns").get.split(",")
    var outColumns: Array[String] = map.get("outColumns").get.split(",")

    import org.apache.spark.sql.functions.get_json_object
    import org.apache.spark.sql.functions.abs

    var result: DataFrame = frame.toDF(inColumns: _*)
                            .toJSON
                            .toDF(outColumns(0))
                            .withColumn(outColumns(1),get_json_object($"${outColumns(0)}","$."+outColumns(1))) //
                            .toDF(outColumns:_*)
    result.show()
    //4.todo.封装并执行SQL写入目标表
//    result.write.mode(SaveMode.Overwrite).partitionBy("data_date").option("encoding","utf-8").saveAsTable(outputTable)
    result.write.option("partitionOverwriteMode", "dynamic").mode("overwrite").insertInto(outputTable)

  }

}


写入方式
df1.write.mode("overwrite").partitionBy("year").saveAsTable(tableName)	所有分区被覆盖
df1.write.mode("overwrite").format("Hive").partitionBy("year").saveAsTable(tableName)	所有分区被覆盖
df1.write.option("partitionOverwriteMode", "dynamic").mode("append").insertInto(tableName)	动态分区，如果分区存在则追加
df1.write.option("partitionOverwriteMode", "dynamic").mode("overwrite").insertInto(tableName)	动态分区，如果分区存在则覆盖
INSERT OVERWRITE TABLE $tableName partition (year) SELECT * FROM tmp	动态分区，如果分区存在则覆盖
INSERT INTO TABLE $tableName partition (year) SELECT * FROM tmp	动态分区，如果分区存在则追加
df1.write.mode("overwrite").insertInto(tableName)	动态分区，如果分区存在则覆盖
df1.write.mode("append").insertInto(tableName)	动态分区，如果分区存在则追加
INSERT OVERWRITE TABLE $tableName partition (year=2024) SELECT * FROM tmp	针对指定分区覆盖写入
INSERT INTO TABLE $tableName partition (year=2024) SELECT * FROM tmp	针对指定分区追加数

4、文本参数

文本参数
querySql^select 'event_order_success' event_name,buyer_phone,order_sn,brand_name,car_model,pay_time,paid_amount,data_date from dwd.t_sl_so_oms_order_a_h where mobile is not null and add_time>='2022-03-01 20:00:00' and pay_status = 12 and pay_time IS NOT NULL and data_date=
inColumns^event_name,buyer_phone,order_sn,brand_name,car_model,pay_time,paid_amount,data_date
outColumns^json_message,data_date
accessId^9y*********n6PsIr
accessKey^yPW6***********xhemqQzPj8YFMXQ
endPoint^http://*******************d01.odps.ops.cloud.gwm.cn/api
project^itsl_dev

提交：