Reply
New Contributor
Posts: 2
Registered: ‎03-07-2019

Lateral View Outer Explode: Results in Rows Multipliers

 

Hi

I am struggling with lateral view outer explode statement. This is the structure of my table:

TBL_CUSTOMER

CustId                                                                          string

Cust_Resident_Country                                               array

                Item                                                             string

Cust_TaxIdenitity                                                        array

                Item                                                           struct

                                Tin_issuing_country                    string

                                Tin                                             string

                               

CustId   Cust_Resident_Country                               Cust_TaxIdenitity

1              [AU:HK]                                                               [{AU:987678;HK:76556}{ AU:987678;HK:76556}]

 

When I use the lateral view outer explode for this object I get 4 rows for the above row:

1              HK                                                          AU                          987678

1              HK                                                          HK                          765556

1              AU                                                          AU                          987678

1              AU                                                          HK                          76556

The desired result is :

1              HK                                                          AU                          987678

1              AU                                                          HK                          76556

 

And there are around 4 more array columns that have to be exploded.How do I stop the mapping of this UDTF mapping to each element of the array resulting in multiplication in row output.

 

 

Thanks!

Samir

 

Cloudera Employee
Posts: 824
Registered: ‎03-23-2015

Re: Lateral View Outer Explode: Results in Rows Multipliers

Hi,

Can you please share your query that has the LATERAL VIEW? This can help to see if you have any issues on your query.
New Contributor
Posts: 2
Registered: ‎03-07-2019

Re: Lateral View Outer Explode: Results in Rows Multipliers

Hi

Thank you for your response.

I am running it for one account number

select distinct msg_bet_id
, atchd_doc_ref_id
, idv_cd as Resident_Country_Code
, idv_tin.tin as TIN
, idv_tin.tin_issg_cntry_cd as TIN_Country_Code
FROM tt17.crs_account_report a1
lateral view outer explode(hldr_idv_rsdnt_cntry_cds)exp1 as idv_cd
lateral view outer explode(hldr_idv_tins)exp2 as idv_tin

where accountnumber=123