hive-issues mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Antoine CARME (JIRA)" <j...@apache.org>
Subject [jira] [Comment Edited] (HIVE-16725) Support recursive CTEs
Date Wed, 21 Nov 2018 23:28:00 GMT

    [ https://issues.apache.org/jira/browse/HIVE-16725?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16695355#comment-16695355
] 

Antoine CARME edited comment on HIVE-16725 at 11/21/18 11:27 PM:
-----------------------------------------------------------------

Would be nice to have this feature in Hive and Impala. Recursive CTEs are useful to translate
recurrent neural networks into SQL.

Some succesful usage of recursive CTEs is available here :(

[https://github.com/antoinecarme/keras2sql/issues/2]

For a lot of databases, it is OK. Hive/Impala and MonetDB are missing.

I know this is not a very standard usage, but it does the job in a very elegant way.

 

Some code for postgresql :

[https://github.com/antoinecarme/keras2sql/blob/master/demo/KerasClassifier_SimpleRNN/iris/pgsql/demo3_keras_KerasClassifier_SimpleRNN_pgsql.sql]

The same for SQLite :

[https://github.com/antoinecarme/keras2sql/blob/master/demo/KerasClassifier_SimpleRNN/iris/sqlite/demo3_keras_KerasClassifier_SimpleRNN_sqlite.sql]

etc ...

 

 

 


was (Author: antoinecarme):
Would be nice to have this feature in Hive and Impala. Recursive CTEs are useful to translate
recurrent neural networks into SQL.

Some succesful usage of recursive CTEs is available here :(

[https://github.com/antoinecarme/keras2sql/issues/2]

For a lot of databases, it is OK. Hive/Impala and MonetDB are missing.

I know this is not a very standard usage, but it does the job in a very elegant way.

> Support recursive CTEs
> ----------------------
>
>                 Key: HIVE-16725
>                 URL: https://issues.apache.org/jira/browse/HIVE-16725
>             Project: Hive
>          Issue Type: Sub-task
>          Components: SQL
>            Reporter: Carter Shanklin
>            Priority: Major
>
> Hive introduced non-recursive CTEs in HIVE-1180.
> Recursive CTEs are commonly used to navigate hierarchies stored in relational tables
where a parent ID column "foreign key" refers to another "primary key" field within the same
table. In this context recursive CTEs are used to traverse hierarchies, determine parents
/ children, measure depths, build paths and so on.
> Recursive CTEs are constructed similarly to basic CTEs but include 2 queries at a minimum:
first a root query which is combined via UNION / UNION ALL to additional queries that can
refer to the CTE's table name.
> Support should include:
> * Basic recursive CTE support: i.e. allow the CTE's table name to be referred in the
table subquery after a UNION or UNION ALL.
> * Recursive CTEs should be supported as basic queries, in views, or in subqueries.
> * Loop detection is highly desirable. If a loop is detected the query should fail at
runtime. Hive is commonly used in shared clusters where it is difficult to track down rogue
queries.
> * To ease portability, suggest  to not require the recursive keyword. It could be made
optional.
> * To ease portability, "with column list", i.e. with t(col1, col2) as ( ... ) should
be supported.
> Example (Postgres compatible):
> {code}
> create table hierarchy (id integer, parent integer);
> insert into hierarchy values (1, null), (2, 1), (3, 2);
> with recursive t(id, parent) as (
>   select id, parent from hierarchy where parent is null
>   union all select hierarchy.id, hierarchy.parent from hierarchy, t where t.id = hierarchy.parent
> ) select * from t;
>  id | parent
> ----+--------
>   1 |
>   2 |      1
>   3 |      2
> (3 rows)
> update hierarchy set parent = 3 where id = 1;
> with recursive t(id, parent) as (
>   select id, parent from hierarchy where parent = 1
>   union all select hierarchy.id, hierarchy.parent from hierarchy, t where t.id = hierarchy.parent
> ) select * from t;
> [ Query runs forever ]
> {code}
> Implementation Notes:
> The SQL standard requires use of the "recursive" keyword for recursive CTEs. However,
major commercial databases including Oracle, SQL Server and DB2 do not require, or in some
cases, don't even allow the "recursive" keyword. Postgres requires the "recursive" keyword.
> If Oracle detects a loop it fails with this message: ORA-32044: cycle detected while
executing recursive WITH query
> If Postgres encounters a loop in a recursive CTE, the query runs forever and must be
killed.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

Mime
View raw message