MV referencing a formula column in an Incorta table throwing error.

I have a file-based table which brings in an ID:  "<ID> Total"    I use a formula column using a casecontains to drop the " Total" where they exist and return the ID where it does not.   All is well with that world.

However, when I try to query the formula column in an MV using Postgres SQL  ( I think Spark SQL limits you to non-formula columns? ) I get the following error:


INC_03070101: Failed to load data from [spark://seqsr-dev1:7077] with properties [[error, AnalysisException: Undefined function: 'casecontains'. This function is neither a registered temporary function nor a permanent function registered in the database 'default'.; line 1 pos 76]]


I find this confusing as I'd expect to query the parquet files and I'd expect physical schema formula column results to be stored w/in them -- obviously I'm wrong somewhere  ;-)      


Any ideas on how to work around the error?

8replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • What is your SQL statement?  Are you using casecontains in your SQL statement in your MV?

    • Dan Brock    

      The casecontains function is in the table 'Staffing' which is file-based, validates, loads, and displays the data correctly.


      Here's the SQL - ID_Calc is the formula column from 'Staffing.'  


      select PeriodWeek, level, ID_Calc AS ID, _Headcount_w__RGM___AM, Target_Headcount

      from QSR_Metrics.Staffing Stf

      WHERE 1 = 1 AND level = 'Store'


    • Dan Brock  I should add as well that this is 4.9.7

    • R. A. Dawson Sr Could you please file a Support ticket for this case? What's happening in the background is your formula column is getting translated to SQL to be calculated at query time because formula columns are not persisted to parquet. There seems to be a translation issue with the caseContains function. As a workaround, please use the case() and contains() functions. I verified those are working correctly.

      Instead of:


      Use this:

    • Dustin Basil  Will do - thx!    Is there a doc or reference differentiating what goes into parquet v non-parquet ( DDM, ZTI, other three letter combinations ;-)   )    

    • R. A. Dawson Sr there may be some details buried in various doc but ultimately I can say that all data extracted from the source system directly goes into parquet. Join columns and formula columns are persisted in DDM files in the snapshot folder.

    • R. A. Dawson Sr This video provides some more details about handling formula column in a physical table using spark:

      Like 1
    • Anurag Malik Thanks Anurag!

Like Follow
  • Status Answered
  • 6 mths agoLast active
  • 8Replies
  • 50Views
  • 4 Following

Product Announcement

A new community experience is coming! If you would like to have beta access to provide feedback, please contact us at community@incorta.com.