Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Hive stored View definition incomplete

avatar
Explorer

When working with Hive in HDP 2.5 I noticed that the stored View definition sometimes is incomplete. For example I create the following view:

CREATE VIEW news_split AS
SELECT company, nof_lines, title,
  author, nof_words, keydate,
  publisher, w.word
FROM news
 LATERAL VIEW EXPLODE(words) W AS WORD;

If I then query the view ddl using "show create table news_split;" I get:

CREATE VIEW `news_split` AS
SELECT `news`.`company`, `news`.`nof_lines`, `news`.`title`,
  `news`.`author`, `news`.`nof_words`, `news`.`keydate`,
  `news`.`publisher`, 
FROM `default`.`news`
  LATERAL VIEW EXPLODE (`news`.`words`) `W` AS `word`

Can anyone explain why this is and how I can fix it? Maybe even how I get rid of the `news`. before every field?

1 ACCEPTED SOLUTION

avatar
Expert Contributor

Please try to create view with database name parameter as below, instead of default database.

    CREATE VIEW database.news_split AS
    SELECT company, nof_lines, title,
      author, nof_words, keydate,
      publisher, w.word
    FROM database.news
     LATERAL VIEW EXPLODE(words) W AS WORD;

View solution in original post

1 REPLY 1

avatar
Expert Contributor

Please try to create view with database name parameter as below, instead of default database.

    CREATE VIEW database.news_split AS
    SELECT company, nof_lines, title,
      author, nof_words, keydate,
      publisher, w.word
    FROM database.news
     LATERAL VIEW EXPLODE(words) W AS WORD;