[HIVE-18013] Hive incorrectly rewrites create statements for VIEW

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 2.1.0
• Component/s: Hive, SQL
• Labels: VIEW hive hql sql
• Assignee: Unassigned
• Reporter: Jaydene Green-Stevens
• Created: 08/Nov/17 11:38
• Updated: 08/Nov/17 11:41

Description

I am encountering errors when using VIEW s as, when Hive rewrites the create statements unrelated query fragments are inserted, resulting in a malformed view statement that cannot then be parsed. 

The create statements I am using for each VIEW is as follows;

CREATE VIEW bar.web_hits
AS
SELECT url
FROM  bar.web_hit_log
WHERE ip_address NOT IN (
     SELECT ip_address
     FROM  bar.crawler
     WHERE active = true
)
AND timestamp_gmt BETWEEN “a” AND “b”;
 
CREATE VIEW  bar.page_view_agg
AS
SELECT url AS page_url, COUNT(*) AS page_count
FROM  bar.web_hits a
GROUP BY url
ORDER BY page_count DESC;

I then have an INSERT statement which selects data from the previously created views and puts it into a table;

INSERT OVERWRITE TABLE  bar.view_result
SELECT page_url, page_count
FROM  bar.page_view_agg;

The following error is produced from running the above query;

FAILED: SemanticException line 1:52 missing EOF at '.' near 'crawler' in definition of VIEW page_view_agg [
SELECT `a`.`url` AS `page_url`, COUNT(*) AS`crawler`.`ip_address``page_count`
FROM ` bar`.`crawler` `a`
GROUP`crawler`.`active`BY `a`.`url`
ORDER BY page_count DESC
] used as page_view_agg at Line 3:5

Running describe extended page_view_agg produces the following output (edited for simplicity);

page_url     string
page_count   bigint
 
viewOriginalText:
SELECT url AS page_url, COUNT(*) AS page_count
FROM  bar.web_hits a
GROUP BY url
ORDER BY page_count DESC, 
 
viewExpandedText:
SELECT `a`.`url` AS `page_url`, COUNT(*) AS`crawler`.`ip_address``page_count`
FROM ` bar`.`crawler` `a`
GROUP`crawler`.`active`BY `a`.`url`
ORDER BY page_count DESC, 

 
As you can see, it looks like the statement has been corrupted by the insertion of the unnecessary string `crawler`.`ip_address` on line 1.