topic Re: lateral view explode in databricks - need help in Data Engineering

lateral view explode in databricks - need help

koantek_user — Tue, 13 Aug 2024 05:33:30 GMT

We are working on snowflake to databricks migration and we encountered the lateral flatten function of snowflake which we tried to convert to lateral view explode in databricks- but its output is a subset of lateral flatten

-----------------------

https://docs.snowflake.com/en/sql-reference/functions/flatten

The output of this flatten contains a result containing 6 elements as shown on this page with (index,value) being major in some of the queries

In case of tables with multiple array columns - these index values are used to combine data stored in 2 different array columns by performing joins

e.g.

col1 <1,2,3>, col2 <abc,def,ghi>

indices of 1 and abc, 2 and def, 3 and ghi are same and they are related data values stored in 2 different columns in a table

--------------------------------------

However,in Databricks - its equivalent is LATERAL VIEW [OUTER] EXPLODE - which just returns the exploded values and not their indices.

this doesn't allow us to combine such data items as mentioned above

https://docs.databricks.com/en/sql/language-manual/sql-ref-syntax-qry-select-lateral-view.html

---------------------------

Any suggestions would be helpful

Re: lateral view explode in databricks - need help

Brahmareddy — Wed, 14 Aug 2024 03:48:47 GMT

Hi AzureSnowflake,

I see you're migrating from Snowflake to Databricks and running into some issues with the LATERAL FLATTEN function in Snowflake. Specifically, you're finding that the LATERAL VIEW EXPLODE in Databricks isn't providing the full output, particularly the indices, that you need to join related data from multiple array columns.

To work around this in Databricks, you can manually generate indices for each element in the arrays using functions like posexplode, which provides both the index and value.

Please find the example below

SELECT
t.*,
p1.pos AS index,
p1.col1 AS value1,
p2.col2 AS value2
FROM
my_table t
LATERAL VIEW POSEXPLODE(t.col1) p1 AS index, col1
LATERAL VIEW POSEXPLODE(t.col2) p2 AS index, col2
WHERE
p1.index = p2.index

Just a thought, give a try and do let me know if it works!

Have a good day.

Re: lateral view explode in databricks - need help

Retired_mod — Wed, 14 Aug 2024 07:59:13 GMT

Hi @koantek_user, Thanks for reaching out! Please review the response and let us know if it answers your question. Your feedback is valuable to us and the community.

If the response resolves your issue, kindly mark it as the accepted solution. This will help close the thread and assist others with similar queries.

We appreciate your participation and are here if you need further assistance!