如何为配置单元中的结构字段创建视图

gwbalxhn 于 2021-06-29 发布在 Hive

关注(0)|答案(3)|浏览(327)

步骤1：我已经编写了一个自定义项，它将形成两个或更多的结构列，比如cars、bikes、bus。此外，自定义项从另一个名为“详细信息”的视图中获取一些信息。

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>>

我正在创建一个视图'车辆'使用此自定义项如下

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;

第2步：我想将每个struct列分解成另一个视图。当我尝试下面的查询时，我得到的错误是“as子句中提供的别名数与udtf输出的列数不匹配”

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;

注意：如果我只有汽车结构自定义项，工作正常。仅当udf包含多个结构时才面临问题。
有什么帮助吗？

Hive hive-udf

来源：https://stackoverflow.com/questions/37282646/how-to-create-view-for-struct-fields-in-hive

3条答案

按热度按时间

izj3ouym1#

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;

赞(0）回复(0）举报 2021-06-29

au9on6nz2#

下面的回答部分不完整，因为您没有包含足够的信息，如自定义项名称，或您要查询的表的结构/名称，但应该足够让您继续。
可以创建使用“分解”的视图：

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

explode将获取一个数组并将其“分解”到一个表中。explode实际上是一个udtf，用户定义的表生成函数。看到了吗https://cwiki.apache.org/confluence/display/hive/languagemanual+udf#languagemanualudf-爆炸

赞(0）回复(0）举报 2021-06-29

xwbd5t1u3#

问题是你的观点

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;

您的数据类型是struct数组，因此您测试\u struct（d.data）的横向视图对于返回的struct列只需要一个别名。
例如

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

赞(0）回复(0）举报 2021-06-29

我来回答

如何为配置单元中的结构字段创建视图

3条答案

相关问题

热门标签

最新问答