CODEWITHSUNDEEP
scalaapache-sparkapache-spark-sql
Antony
Antony

Reputation: 1118

Unable to filter CSV columns stored in dataframe in spark 2.2.0

I am reading a CSV file from my local machine using spark and scala and storing into a dataframe (called df). I have to select only few selected columns with new aliasing names from the df and save to new dataframe newDf. I have tried to do the same but I am getting the error below.

main" org.apache.spark.sql.AnalysisException: cannot resolve '`history_temp.time`' given input columns: [history_temp.time, history_temp.poc]

Below is the code written to read the csv file from my local machine.

import org.apache.spark.sql.SparkSession

object DataLoadConversion {


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

    System.setProperty("spark.sql.warehouse.dir", "file:///C:/spark-warehouse")
    val spark = SparkSession.builder().master("local").appName("DataConversion").getOrCreate()

    val df = spark.read.format("com.databricks.spark.csv")
      .option("quote", "\"")
      .option("escape", "\"")
      .option("delimiter", ",")
      .option("header", "true")
      .option("mode", "FAILFAST")
      .option("inferSchema","true")
      .load("file:///C:/Users/an/Desktop/ct_temp.csv")

    df.show(5)   // Till this code is working fine

    val newDf = df.select("history_temp.time","history_temp.poc")

Below are the code which I tried but not working.

//  val newDf = df.select($"history_temp.time",$"history_temp.poc")
//  val newDf = df.select("history_temp.time","history_temp.poc")
//  val newDf = df.select( df("history_temp.time").as("TIME"))
//  val newDf = df.select(df.col("history_temp.time"))

//  df.select(df.col("*"))    // This is working

    newDf.show(10)
  }
}

Upvotes: 2

Views: 213

Answers (1)

Aaron
Aaron

Reputation: 686

from the looks of it. your column name format is the issue here. i am guessing they are just regular stringType but when you have something like history_temp.time spark thinks it as an arrayed column. which is not the case. I would rename all of the columns and replace "." to "". then you can run the same select and it should work. you can use foldleft to rplace all "." with "" like below.

val replacedDF = df.columns.foldleft(df){ (newdf, colname)=>
newdf.withColumnRenamed (colname, colname.replace(".","_"))
}

With that done you can select from replacedDF with below

val newDf= replacedDf.select("history_temp_time","history_temp_poc")

Let me know how it works out for you.

Upvotes: 2

Related Questions