Using ADF Data Flow Derived Column transform against nested Delta structures

• cubemaster

  Old Hand

  Points: 330

  More actions

  October 29, 2020 at 2:26 pm

  #3802462

  I'm trying (without success) to use a derived column transform within an ADF Data Flow where I've ingested a Delta table with nested structures. I'm struggling with the syntax needed to flatten out these structures and no column info is displayed despite me being able to preview the data.

  I thought I'd be able to use something like Name.FirstName to reference the structured column but it's giving me an error "Expression type could not be evaluated, correct the expression" in the expression editor

  Such a structure would be:

  {
  "ContactId":"1002657",
    "Name":{
    "FirstName":"Donna",
    "FullName":"Donna Brittain",
    "LastName":"Brittain"
    }
  }

  Data Preview working OK:

  

  The structure of my Delta table:

  

  The error I'm getting trying to reference a nested column:

  

  How can I reference a nested column such as Name.FirstName to flatten it out to FirstName and why is it not showing up in any of the mappings?

  It's also not an option to copy this data to another structure such as csv to flatten out as I have over 1 billion rows in this table

  • This topic was modified 4 years, 1 month ago by  cubemaster.

• Site Owners

  SSC Guru

  Points: 80325

  More actions

  October 30, 2020 at 3:10 pm

  #3802853

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.