Extracting value from data frame thorws error because of the . in the column name in spark

Question

This is my Existing data frame

+------------------+-------------------------+-----------+---------------+-------------------------+---------------------------+------------------------+--------------------------+---------------+-----------+----------------+-----------------+----------------------+--------------------------+-----------+--------------------+-----------+--------------------------------------------------------------------------------------------+-----------------------+------------------+-----------------------------+-----------------------+----------------------------------+
|DataPartition     |TimeStamp                |_lineItemId|_organizationId|fl:FinancialConceptGlobal|fl:FinancialConceptGlobalId|fl:FinancialConceptLocal|fl:FinancialConceptLocalId|fl:InstrumentId|fl:IsCredit|fl:IsDimensional|fl:IsRangeAllowed|fl:IsSegmentedByOrigin|fl:SegmentGroupDescription|fl:Segments|fl:StatementTypeCode|FFAction|!||LineItemName                                                                                |LineItemName.languageId|LocalLanguageLabel|LocalLanguageLabel.languageId|SegmentChildDescription|SegmentChildDescription.languageId|
+------------------+-------------------------+-----------+---------------+-------------------------+---------------------------+------------------------+--------------------------+---------------+-----------+----------------+-----------------+----------------------+--------------------------+-----------+--------------------+-----------+--------------------------------------------------------------------------------------------+-----------------------+------------------+-----------------------------+-----------------------+----------------------------------+
|SelfSourcedPrivate|2017-11-02T10:23:59+00:00|3          |4298009288     |XTOT                     |3016350                    |null                    |null                      |null           |true       |false           |false            |false                 |null                      |null       |BAL                 |I|!|       |Total Assets                                                                                |505074                 |null              |null                         |null                   |null                              |

And here is the schema of above data frame

root
 |-- DataPartition: string (nullable = true)
 |-- TimeStamp: string (nullable = true)
 |-- _lineItemId: long (nullable = true)
 |-- _organizationId: long (nullable = true)
 |-- fl:FinancialConceptGlobal: string (nullable = true)
 |-- fl:FinancialConceptGlobalId: long (nullable = true)
 |-- fl:FinancialConceptLocal: string (nullable = true)
 |-- fl:FinancialConceptLocalId: long (nullable = true)
 |-- fl:InstrumentId: long (nullable = true)
 |-- fl:IsCredit: boolean (nullable = true)
 |-- fl:IsDimensional: boolean (nullable = true)
 |-- fl:IsRangeAllowed: boolean (nullable = true)
 |-- fl:IsSegmentedByOrigin: boolean (nullable = true)
 |-- fl:SegmentGroupDescription: string (nullable = true)
 |-- fl:Segments: struct (nullable = true)
 |    |-- fl:SegmentSequence: struct (nullable = true)
 |    |    |-- _VALUE: long (nullable = true)
 |    |    |-- _segmentId: long (nullable = true)
 |-- fl:StatementTypeCode: string (nullable = true)
 |-- FFAction|!|: string (nullable = true)
 |-- LineItemName: string (nullable = true)
 |-- LineItemName.languageId: long (nullable = true)
 |-- LocalLanguageLabel: string (nullable = true)
 |-- LocalLanguageLabel.languageId: long (nullable = true)
 |-- SegmentChildDescription: string (nullable = true)
 |-- SegmentChildDescription.languageId: long (nullable = true)

I want to rename the header columns of the data frame using below code .

 val temp = dfTypeNew.select(dfTypeNew.columns.filter(x => !x.equals("fl:Segments")).map(x => col(x).as(x.replace("_", "LineItem_").replace("fl:", ""))): _*)

When I do that I get below error

Exception in thread "main" org.apache.spark.sql.AnalysisException: Can't extract value from LineItemName#368: need struct type but got string;

When I rename my columns without . I am able to extract

Answer 1

The error is there because (.)dot is used to access a struct field To read a field that has a column name use backticks as below

  val df = Seq(
    ("a","b","c"),
    ("a","b","c")
  ).toDF("x", "y", "z.z")

  df.select("x", "`z.z`").show(false)

Output

+---+---+
|a  |c.c|
+---+---+
|a  |c  |
|a  |c  |
+---+---+

Hope this helps!

Edited by Ramesh

@Anupam, all you had to do was use the above technique that Shankar suggested in your code as

val temp = dfTypeNew.select(dfTypeNew.columns.filter(x => !x.equals("fl:Segments")).map(x => col(s"`${x}`").as(x.replace("_", "LineItem_").replace("fl:", ""))): _*)

Thats all.