How to Convert a Lateral View to a Table Reference

[BennyBoyW]

Hi All

I have a view creation script in DataBricks which uses a lateral view to access columns in a structure held within an array field. 

It is working fine but I have noted that the LATERAL VIEW is now depracated and that I should be using a TABLE REFERENCE instead. 

I just cannot get the command to work however, so my question is - how do I convert the following to a TABLE REFERENCE ?

CREATE OR REPLACE VIEW newview

  AS

    SELECT

      t1.field1

      , structureitem.field2

      , structureitem.field3

    FROM table1 AS t1, table2 AS t2

    LATERAL VIEW EXPLODE(t1.structure) AS structureitem

    where t1.id = t2.id;

[saurabh18cs]

Hi  try this  

CREATE OR REPLACE VIEW newview AS

SELECT

  t1.field1,

  structureitem.field2,

  structureitem.field3

FROM table1 AS t1

JOIN table2 AS t2

  ON t1.id = t2.id

JOIN LATERAL TABLE(explode(t1.structure)) AS structureitem

  ON TRUE;

[BennyBoyW]

Hi Again. Thank you for the extremely quick reply !

Unfortunately DataBricks doesn't seem to like the syntax above and returns the error :

[UNRESOLVABLE_TABLE_VALUED_FUNCTION] Could not resolve TABLE to a table-valued function. Please make sure that TABLE is defined as a table-valued function and that all required parameters are provided correctly. If TABLE is not defined, please create the table-valued function before using it.

[balajij8]

You can use

CREATE OR REPLACE VIEW newview

  AS

    SELECT

      t1.field1,

      item.field2,

      item.field3

    FROM table1 AS t1

    INNER JOIN table2 AS t2 ON t1.id = t2.id

    , LATERAL EXPLODE(t1.structure) AS structureitem(item)