[1,2,3],[4,5,6],[7,8,9]and turn it into a table with the following output:
1,4,7 2,5,8 3,6,91. To prepare the table, firstly need to create a dummy table with one record:
CREATE TABLE dummy (a int); INSERT INTO TABLE dummy VALUES (1);This is to allow us to insert array data into our testing table by SELECTing from our 1 row dummy table. 2. Create a table with array data type:
CREATE TABLE array_table (a arrayThis will get us ready with a table having the following data:, b array , c array ); INSERT INTO array_test SELECT array(1,2,3), array(3,4,5), array(6,7,8) FROM dummy;
+-------------+-------------+-------------+--+ | a | b | c | +-------------+-------------+-------------+--+ | [1,2,3] | [3,4,5] | [6,7,8] | +-------------+-------------+-------------+--+3. Now run the following query using LATERAL VIEW will give the output we want:
SELECT key1, key2, key3 FROM array_test LATERAL VIEW posexplode(a) t1 AS q1, key1 LATERAL VIEW posexplode(b) t2 AS q2, key2 LATERAL VIEW posexplode(c) t3 AS q3, key3 WHERE q1 = q2 AND q1 = q3; +-------+-------+-------+--+ | key1 | key2 | key3 | +-------+-------+-------+--+ | 1 | 3 | 6 | | 2 | 4 | 7 | | 3 | 5 | 8 | +-------+-------+-------+--+This might not be the best solution, but at least it works. However, how well the performance goes on real Big Data set, you have to test out and confirm yourself. Hope above helps.