Support Questions

Find answers, ask questions, and share your expertise

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;