hive-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Gunther Hagleitner (JIRA)" <>
Subject [jira] [Commented] (HIVE-1180) Support Common Table Expressions (CTEs) in Hive
Date Thu, 30 Jan 2014 20:16:11 GMT


Gunther Hagleitner commented on HIVE-1180:

I think it's reasonable to do non-recursive, non-materialized CTEs in a first step. It still
gives you some of the benefits of CTEs (temporary "view" without metastore, multiple refs
to the same subquery, simpler to write queries.) It will, however, still be more efficient
in some cases to create/drop table to avoid re-computation. We should probably document and
create follow up jira right away.

I've gone through the initial patch and it looks good so far. Some questions/suggestions for
test cases to add:

   * CTAS/create table like: Should also be supported, right? Currently you won't be able
to specify that.
   * Negative test: Multiple with statements in the same query
   * What about multiple CTEs, can they referencing one another? If not, is this caught?
   * Insert statement: From the grammar it looks like you might be able to specify the CTE
as the target table also (negative test), otherwise we should probably test that you can use
it as a source.
   * Order by, sort by: These don't really make sense in a CTE - should they be disallowed?
   * Top level union with CTE is probably worth a test.
   * CTE in view definition: Should be allowed too, right? Currently I don't think it is.

> Support Common Table Expressions (CTEs) in Hive
> -----------------------------------------------
>                 Key: HIVE-1180
>                 URL:
>             Project: Hive
>          Issue Type: Improvement
>          Components: Query Processor
>            Reporter: Jeff Hammerbacher
>            Assignee: Harish Butani
>         Attachments: HIVE-1180.1.patch
> I've seen some presentations from the PostgreSQL recently expounding the utility of CTEs
( 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

This message was sent by Atlassian JIRA

View raw message