How to create view for struct fields in hive

Question

STEP 1: I have written an UDF which will form 2 or more Struct columns like cars, bikes, buses. Also the UDF takes some info from other view called 'details'.

cars struct form is: ARRAY<STRUCT<name:string, mfg:string, year:int>>
bikes struct form is: ARRAY<STRUCT<name: string, mfg:string, year: int, price: double>>
buses struct form is: ARRAY<STRUCT<name: string, mfg:string, year: int, price: double>>

I am creating a view 'vehicles' using this UDF as below

ADD JAR s3://test/StructFV-0.1.jar;
CREATE TEMPORARY FUNCTION TEST_STRUCT AS "com.test.TestStruct";

CREATE DATABASE IF NOT EXISTS ranjith;
USE ranjith;
DROP VIEW IF EXISTS vehicles;
CREATE VIEW vehicles AS 
SELECT t.cars, t.bikes, t.buses
FROM details d LATERAL VIEW TEST_STRUCT(d.data) t AS
cars, bikes, buses;

STEP 2: I want to explode each struct column into another view. When i try the below query, i am getting error like "The number of aliases supplied in the AS clause does not match the number of columns output by the UDTF expected"

USE ranjith;
DROP VIEW IF EXISTS cars;
CREATE VIEW cars AS 
SELECT c.name as name, c.mfg as mfg, c.year as year 
FROM vehicles v LATERAL VIEW EXPLODE (v.cars) exploded_table as c;

Note: If i have UDF with only cars struct, works fine. Facing issue only if the UDF contains more than one STRUCT.

Any help?

Unclench

pls. have look in to #11374043 – Tenuous

hi ram. i have updated the question. any help plz? – Unclench

Answer 1

Partially incomplete answer below because you did not include enough information, like the UDF name, or the structure/name of the table you're querying from, but it should be enough to get you going.

You can create a view that uses EXPLODE:

CREATE VIEW productview as 
  SELECT EXPLODE( myudf(.. ).product ) 
      FROM mytable

EXPLODE will take an array and 'explode' it into a table. EXPLODE is in fact a UDTF, User Define Table-generating Function. See https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-explode

Hindorff

Answer 2

USE ranjith;
DROP VIEW IF EXISTS cars;
CREATE VIEW cars AS 
SELECT c.name as name, c.mfg as mfg, c.year, b.name AS bike_name, bb.name AS             bus_name 
FROM vehicles v LATERAL VIEW EXPLODE (v.cars) exploded_table as c LATERAL VIEW     EXPLODE (v.bikes) exploded_table2 AS b LATERAL VIEW EXPLODE (v.buses) exploded_table3 AS bb;

Disagreement

Answer 3

The problem is your view

CREATE VIEW vehicles AS 
SELECT t.cars, t.bikes, t.buses
FROM details d LATERAL VIEW TEST_STRUCT(d.data) t AS
cars, bikes, buses;

Your datatypes are ARRAY of STRUCT, so your LATERAL VIEW to TEST_STRUCT(d.data) only needs one alias, for the STRUCT column that is returned.

e.g.

CREATE VIEW vehicles AS 
SELECT columnAlias.cars, columnAlias.bikes, columnAlias.buses
FROM details d LATERAL VIEW TEST_STRUCT(d.data) tableAlias AS columnAlias;

Czechoslovak