[jira] [Created] (PHOENIX-1719) CREATE VIEW ... AS SELECT DDL should allow to specify aliases for the columns.

["Serhiy Bilousov (JIRA)" <ji...@apache.org>]

Serhiy Bilousov created PHOENIX-1719:
----------------------------------------

             Summary: CREATE VIEW ... AS SELECT DDL should allow to specify aliases for the columns.
                 Key: PHOENIX-1719
                 URL: https://issues.apache.org/jira/browse/PHOENIX-1719
             Project: Phoenix
          Issue Type: Improvement
            Reporter: Serhiy Bilousov
            Priority: Critical


It would be very helpful to be able to specify aliases for the columns when creating VIEW. It also would be beneficiary to have consistent GRAMMAR for select statement in the select, create view (including as select), derived tables, sub-queries  etc.

This would not only bring PHOENIX SQL one little step closer to the ANSI SQL standart but would also allow to bring well named column names to BI tools and still stay with HBase best practices regarding minimal length of the CQ/CD names.

It should also allow to hide quoted CQ/CC behind the VIEW so user would not need to think about what CC should be quoted and what should not.

Here is how it looks in different RDMS
{code:title=MS SQL|borderStyle=solid}
// Some comments here
CREATE VIEW [ schema_name . ] view_name [ (column [ ,...n ] ) ] 
[ WITH <view_attribute> [ ,...n ] ] 
AS select_statement 
[ WITH CHECK OPTION ] 
[ ; ]

<view_attribute> ::= 
{
    [ ENCRYPTION ]
    [ SCHEMABINDING ]
    [ VIEW_METADATA ]     
} 
{code}

{code:title=Postgrsql|borderStyle=solid}
[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] ( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] )
    [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    [ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

and with_query is:

    with_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert | update | delete )

TABLE [ ONLY ] table_name [ * ]
{code}





--
This message was sent by Atlassian JIRA
(v6.3.4#6332)