1

I have a table created using HIVE query in Cloudera VM, below is my DDL to create the table called 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/';

Upon executing this on the HUE HIVE Editor the table gets created successfully, Now the issue is I am not able to execute SELECT statement which throws the following error.

SELECT Statement 

 Select 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

Also, since HUE editor gives auto complete feature, below was the statement and the error it gave.

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]

What is correct SELECT statement ? Am I missing out any syntax?

leftjoin
  • 36,950
  • 8
  • 57
  • 116
ANmike
  • 267
  • 3
  • 13

1 Answers1

0

user_mentions is an array of struct. You can address inner struct element only by specifying array index:

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

Or use explode() + lateral view if you want to select all array elements:

select id, user_mention.name
from incremental_tweets 
     lateral view outer explode(entities.user_mentions) s as user_mention
;
leftjoin
  • 36,950
  • 8
  • 57
  • 116
  • Thanks for the response, I tried with `explode()` + `lateral view` but ended up with the same error. `Error while processing statement: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask` – ANmike May 06 '18 at 06:16
  • @ANmike You need to inspect job tracker logs to find the reason and real exception. This exception it prints on console is not informative – leftjoin May 06 '18 at 06:28
  • @ANmike The syntax is correct, I have checked it. Most probably the problem is in the data. Could you please provide data example and job tracker logs. – leftjoin May 07 '18 at 06:29