Jaydene Green-Stevens created HIVE-18013:
--------------------------------------------
Summary: Hive incorrectly rewrites create statements for VIEW
Key: HIVE-18013
URL: https://issues.apache.org/jira/browse/HIVE-18013
Project: Hive
Issue Type: Bug
Components: Hive, SQL
Affects Versions: 2.1.0
Reporter: Jaydene Green-Stevens
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;
{code:sql}
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;
{code}
I then have an {{INSERT}} statement which selects data from the previously
created views and puts it into a table;
{code:sql}
INSERT OVERWRITE TABLE bar.view_result
SELECT page_url, page_count
FROM bar.page_view_agg;
{code}
The following error is produced from running the above query;
{code}
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
{code}
Running {{describe extended page_view_agg}} produces the following output
(edited for simplicity);
{code}
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,
{code}
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.
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
