如何为配置单元中的struct字段创建视图 [英] How to create view for struct fields in hive

问题描述

第1步：
我写了一个UDF，它将形成2个或更多的结构列，如汽车，自行车，公共汽车。此外，UDF还从其他视图中获取了一些名为'details'的信息。

  cars结构形式为：ARRAY< STRUCT< name：string ，mfg：string，year：int>> 
 bikes结构形式为：ARRAY< STRUCT< name：string，mfg：string，year：int，price：double>> 
 bus结构形式为：ARRAY< STRUCT< name：string，mfg：string，year：int，price：double>> 
  

我正在使用此UDF创建视图'vehicles'，如下所示

 添加JAR s3：//test/StructFV-0.1.jar; 
 CREATE TEMPORARY FUNCTION TEST_STRUCT AScom.test.TestStruct; 
 
如果不存在，则创建数据库ranjith; 
 USE ranjith; 
降视图如果存在车辆; 
 CREATE VIEW车辆AS 
 SELECT t.cars，t.bikes，t.buses 
 FROM details d LATERAL VIEW TEST_STRUCT（d.data）t AS 
汽车，自行车，公共汽车; 
  

第2步：
我想将每个结构列分解到另一个视图。
当我尝试下面的查询时，出现如下错误：AS子句中提供的别名数与预期的UDTF输出的列数不匹配

  USE ranjith; 
下降查看如果存在汽车; 
 CREATE VIEW汽车AS 
 SELECT c.name作为名称，c.mfg作为mfg，c.year作为年份
 FROM车辆v侧视图EXPLODE（v.cars）exploded_table as c; 
  

注意：如果我只有汽车结构UDF，工作正常。仅当UDF包含多个STRUCT时才面对问题。

有什么帮助？

解决方案

问题是你的看法

  CREATE VIEW车辆AS 
 SELECT t.cars，t.bikes ，t.buses 
 FROM详情d LATERAL VIEW TEST_STRUCT（d.data）t AS 
汽车，自行车，公共汽车; 
  

您的数据类型是STRUCT的ARRAY，所以您的横向视图为
TEST_STRUCT（d.data ）只需要一个别名，即返回的STRUCT列。

eg

  CREATE VIEW车辆AS 
 SELECT columnAlias.cars，columnAlias.bikes，columnAlias.buses 
 FROM details d LATERAL VIEW TEST_STRUCT（d.data）tableAlias AS columnAlias; 
  

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?

解决方案

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;

这篇关于如何为配置单元中的struct字段创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！