无法运行配置单元查询

lg40wkob  于 2021-06-26  发布在  Hive
关注(0)|答案(1)|浏览(381)

我有一个 table 在clouderavm中使用配置单元查询创建的,下面是我的 DDL 创建名为 incremental_tweets .

CREATE EXTERNAL TABLE incremental_tweets (
id BIGINT,
created_at STRING,
source STRING,
favorited BOOLEAN,
retweet_count INT,
retweeted_status STRUCT<
  text:STRING,
  user:STRUCT<screen_name:STRING,name:STRING>>,
entities STRUCT<
  urls:ARRAY<STRUCT<expanded_url:STRING>>,
  user_mentions:ARRAY<STRUCT<screen_name:STRING,name:STRING>>,
  hashtags:ARRAY<STRUCT<text:STRING>>>,
text STRING,
user STRUCT<
  screen_name:STRING,
  name:STRING,
  friends_count:INT,
  followers_count:INT,
  statuses_count:INT,
  verified:BOOLEAN,
  utc_offset:INT,
  time_zone:STRING>,
in_reply_to_screen_name STRING
)
ROW FORMAT SERDE 'com.cloudera.hive.serde.JSONSerDe'
LOCATION '/twitteranalytics/incremental/';

在执行此操作时 HUE HIVE Editor 表创建成功,现在的问题是我无法执行 SELECT 引发以下错误的语句。 SELECT StatementSelect id, entities.user_mentions.name FROM incremental_tweets;ERROR ```
Error while processing statement: FAILED: Execution Error, return code 2
from org.apache.hadoop.hive.ql.exec.mr.MapRedTask

还有,自从 `HUE editor` 提供自动完成功能,下面是它给出的语句和错误。 `Statement` ```
Select id, entities.`,user_mentions`.name FROM incremental_tweets;
``` `ERROR` ```
Error while compiling statement: FAILED: RuntimeException cannot find field 
,user_mentions(lowercase form: ,user_mentions) in [urls, user_mentions, 
hashtags]

什么是正确的 SELECT statement ? 我遗漏了什么语法吗?

tmb3ates

tmb3ates1#

user_mentions 是结构的数组。只能通过指定数组索引来寻址内部结构元素:

entities.user_mentions[0].name --get name from first array element

或使用 explode() + lateral view 如果要选择所有数组元素:

select id, user_mention.name
from incremental_tweets 
     lateral view outer explode(entities.user_mentions) s as user_mention
;

相关问题