Menu
My use case is I am having one table in hive which has one column as INT and one as Array data type. I want to display it horizontally..
449
A lateral view first applies the UDTF to each row of base table and then joins resulting output rows to the input rows to form a virtual table having the supplied table alias. Version. Prior to Hive 0.6. 0, lateral view did not support the predicate push-down optimization. In Hive 0.5.
To flatten a nested array's elements into a single array of values, use the flatten function. This query returns a row for each element in the array.
Apache Hive does not have direct standard UDF for transposing rows into columns. Transpose & Pivot in Hive Query can be achieved using multi-stage process. You can use collect_list() or collect_set() function and merge the multiple rows into columns and then get the result.
Explode function displays each element of collection data type as single row.
CREATE TABLE Products
(id INT, ProductName STRING, ProductColorOptions ARRAY<STRING>);
select * from products;
1 Watches [“Red”,”Green”]
2 Clothes [“Blue”,”Green”]
3 Books [“Blue”,”Green”,”Red”]
select explode(ProductColorOptions ) from products;
Red
Green
But I want to join with other columns like id but that leads to an error.
In that case, we need to use Lateral View.Lateral view creates a virtual table for exploded columns and make join with the base table.
We need not to worry about the virtual table as it is done by hive internally.
SELECT p.id,p.productname,colors.colorselection FROM default.products P
LATERAL VIEW EXPLODE(p.productcoloroptions) colors as colorselection;
1 Watches Red
1 Watches Green
2 Clothes Blue
199
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
