You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@hive.apache.org by "Jeff Hammerbacher (JIRA)" <ji...@apache.org> on 2010/02/18 20:30:27 UTC
[jira] Created: (HIVE-1180) Support Common Table Expressions (CTEs)
in Hive
Support Common Table Expressions (CTEs) in Hive
-----------------------------------------------
Key: HIVE-1180
URL: https://issues.apache.org/jira/browse/HIVE-1180
Project: Hadoop Hive
Issue Type: Improvement
Components: Query Processor
Reporter: Jeff Hammerbacher
I've seen some presentations from the PostgreSQL recently expounding the utility of CTEs (http://en.wikipedia.org/wiki/Common_table_expressions). Should we try to support these in Hive? I've never used them in practice, so curious to hear if the community would find them useful.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (HIVE-1180) Support Common Table Expressions
(CTEs) in Hive
Posted by "Ning Zhang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-1180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12835492#action_12835492 ]
Ning Zhang commented on HIVE-1180:
----------------------------------
The session-level temp table has not been implemented yet or in the near-future roadmap, but with the metastore's extension to keep the type of an object (whether it is a table or a view), it is relatively straightforward to add a new "temp table" type. The only thing is to change the DDL to support 'create temporary table ...), and drop the temp table once a session ended.
Agreed with your point about the recursive queries. It is easy for the user to write recursive queries, but hard for execution and bad for resource management.
> Support Common Table Expressions (CTEs) in Hive
> -----------------------------------------------
>
> Key: HIVE-1180
> URL: https://issues.apache.org/jira/browse/HIVE-1180
> Project: Hadoop Hive
> Issue Type: Improvement
> Components: Query Processor
> Reporter: Jeff Hammerbacher
>
> I've seen some presentations from the PostgreSQL recently expounding the utility of CTEs (http://en.wikipedia.org/wiki/Common_table_expressions). Should we try to support these in Hive? I've never used them in practice, so curious to hear if the community would find them useful.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (HIVE-1180) Support Common Table Expressions
(CTEs) in Hive
Posted by "Edward Capriolo (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-1180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12835466#action_12835466 ]
Edward Capriolo commented on HIVE-1180:
---------------------------------------
(2) is a good addition to Hive, given that we have views now and need to add the support of session-level temp tables.
Question, how are we going to support session level temp tables Files/tables in HDFS are going to be tied into SessionState or a session ID in someway?
(1) From my experience map/reduce does not lend itself well to recursive work. Currently if each recursion was a map/reduce job that would be a major strain on hadoop. (JobHistory would have explosive growth from a few queries) Maybe there is a simple way around this.
However it is possible/practical recursive processing does open up interesting queries on self joined tables.
id name parent
{noformat}
1 adam null
2 caan 1
3 able 1
{noformat}
Select "ALL DESCENDANTS OF ADAM"
> Support Common Table Expressions (CTEs) in Hive
> -----------------------------------------------
>
> Key: HIVE-1180
> URL: https://issues.apache.org/jira/browse/HIVE-1180
> Project: Hadoop Hive
> Issue Type: Improvement
> Components: Query Processor
> Reporter: Jeff Hammerbacher
>
> I've seen some presentations from the PostgreSQL recently expounding the utility of CTEs (http://en.wikipedia.org/wiki/Common_table_expressions). Should we try to support these in Hive? I've never used them in practice, so curious to hear if the community would find them useful.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (HIVE-1180) Support Common Table Expressions
(CTEs) in Hive
Posted by "Carl Steinbach (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-1180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12835361#action_12835361 ]
Carl Steinbach commented on HIVE-1180:
--------------------------------------
[PostgreSQL's CTE Readme | http://wiki.postgresql.org/wiki/CTEReadme]
> Support Common Table Expressions (CTEs) in Hive
> -----------------------------------------------
>
> Key: HIVE-1180
> URL: https://issues.apache.org/jira/browse/HIVE-1180
> Project: Hadoop Hive
> Issue Type: Improvement
> Components: Query Processor
> Reporter: Jeff Hammerbacher
>
> I've seen some presentations from the PostgreSQL recently expounding the utility of CTEs (http://en.wikipedia.org/wiki/Common_table_expressions). Should we try to support these in Hive? I've never used them in practice, so curious to hear if the community would find them useful.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (HIVE-1180) Support Common Table Expressions
(CTEs) in Hive
Posted by "Ning Zhang (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/HIVE-1180?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12835456#action_12835456 ]
Ning Zhang commented on HIVE-1180:
----------------------------------
It seems that he benefits of CTE are 1) its support of recursion and 2) query-level temp table materialization (if the optimizer chooses so).
I think (2) is a good addition to Hive, given that we have views now and need to add the support of session-level temp tables.
(1) is a major extension and it may be unsafe -- the query may never terminate (well UDFs are undeciable, but HiveQL itself is currently). I was wondering how many customers really need recursions given that we have UDF support.
> Support Common Table Expressions (CTEs) in Hive
> -----------------------------------------------
>
> Key: HIVE-1180
> URL: https://issues.apache.org/jira/browse/HIVE-1180
> Project: Hadoop Hive
> Issue Type: Improvement
> Components: Query Processor
> Reporter: Jeff Hammerbacher
>
> I've seen some presentations from the PostgreSQL recently expounding the utility of CTEs (http://en.wikipedia.org/wiki/Common_table_expressions). Should we try to support these in Hive? I've never used them in practice, so curious to hear if the community would find them useful.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.