Hi all,
I’m having an issue when trying to insert data into a table from a view.
The issue is that Hive seems to rewrite the create statements for the view -
like renaming a column to table.col1, and a table to database.table - but when
it does this, unrelated query fragments are inserted, resulting in a malformed
view statement that cannot then be parsed.
This problem does not occur in Hive 1.2.1 and earlier, but it does occur in
Hive 2.1.0.
Below are the statements used to create the views;
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 created an external table, and then I try to run the following statement to
select from the view page_view_agg and received an error;
INSERT OVERWRITE TABLE bar.view_result
SELECT page_url, page_count
FROM bar.page_view_agg;
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
I ran the describe extended page_view_agg command and got the following output
(edited);
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.
I would be grateful to hear any suggestions as to what the problem is/ how to
fix it.
Thanks
Jay Green-Stevens
Software Development Intern
Hotels.com – an Expedia Inc Brand
Expedia.com Ltd – 407 St John St, London EC1V4EX
[ignature_1304619991]
