Apache Drill
  1. Apache Drill
  2. DRILL-438

Add support for Views (CREATE, DROP and select)

    Details

    • Type: New Feature New Feature
    • Status: Resolved
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.4.0
    • Component/s: None
    • Labels:
      None
    1. DRILL-438-1.patch
      79 kB
      Venki Korukanti
    2. DRILL-438-2.patch
      83 kB
      Venki Korukanti

      Issue Links

        Activity

        Hide
        Julian Hyde added a comment -

        I think a view definition should capture its environment (current schema, and also path for resolving functions) so that that environment can be used when the view is expanded. That would require a couple of extra fields when storing the view definition in the repository.

        Show
        Julian Hyde added a comment - I think a view definition should capture its environment (current schema, and also path for resolving functions) so that that environment can be used when the view is expanded. That would require a couple of extra fields when storing the view definition in the repository.
        Hide
        George Chow added a comment -

        Thanks Venki. I can confirmed now that dfs.`default` works.

        Is the plan for 1.0 to support only views in dfs.default?

        And I'm probably jumping ahead but once persistence is finish, I should expect to see the view in INFORMATION_SCHEMA.TABLE with TABLE_TYPE=VIEW, right?

        Show
        George Chow added a comment - Thanks Venki. I can confirmed now that dfs.`default` works. Is the plan for 1.0 to support only views in dfs.default? And I'm probably jumping ahead but once persistence is finish, I should expect to see the view in INFORMATION_SCHEMA.TABLE with TABLE_TYPE=VIEW, right?
        Hide
        Venki Korukanti added a comment -

        I see the the issue. The problem is when you are using the 'dfs' as the current schema, view table is created as part of the "dfs" name space in ViewStore. But when searching for a table (or view) in "dfs" schema it looks at the tables in "default" schema which has the schema path "dfs.default". As there is no view table with "dfs.default" in ViewStore, it throws table not found error. When deleting we lookup the view in ViewStore by "dfs" path as that is the current schema. So it is able to find it.

        Workaround is set "Use dfs.`default`;" as the current schema, so that view are not created with "dfs" namespace. I will fix this issue and previous issue soon.

        Show
        Venki Korukanti added a comment - I see the the issue. The problem is when you are using the 'dfs' as the current schema, view table is created as part of the "dfs" name space in ViewStore. But when searching for a table (or view) in "dfs" schema it looks at the tables in "default" schema which has the schema path "dfs.default". As there is no view table with "dfs.default" in ViewStore, it throws table not found error. When deleting we lookup the view in ViewStore by "dfs" path as that is the current schema. So it is able to find it. Workaround is set "Use dfs.`default`;" as the current schema, so that view are not created with "dfs" namespace. I will fix this issue and previous issue soon.
        Hide
        George Chow added a comment -

        I am not creating any new session since I'm doing all this in one sqlline session.

        The odd thing is that DESCRIBE and DROP both work. And attempting to CREATE another view of the same name fails with error message about a pre-existing view. So it looks like the view is there. DESCRIBE is consistent with a direct reference to the file by returning an empty resultset (no columns or rows).

        But the critical part is that for whatever reason, queries fail.

        Show
        George Chow added a comment - I am not creating any new session since I'm doing all this in one sqlline session. The odd thing is that DESCRIBE and DROP both work. And attempting to CREATE another view of the same name fails with error message about a pre-existing view. So it looks like the view is there. DESCRIBE is consistent with a direct reference to the file by returning an empty resultset (no columns or rows). But the critical part is that for whatever reason, queries fail.
        Hide
        Venki Korukanti added a comment - - edited

        I tried the same steps and I don't see the issue. Are you creating any new session after the creating the view?

        0: jdbc:drill:zk=local> create view myView as select * from dfs.`/Users/hadoop/mapr-repos/parser-drill/sample-data/nation.parquet`;
        +------------+------------+
        |     ok     |  summary   |
        +------------+------------+
        | true       | View 'myView' created successfully in 'dfs.tmp' schema |
        +------------+------------+
        1 row selected (0.026 seconds)
        0: jdbc:drill:zk=local> select * from myView;
        +-------------+------------+-------------+------------+
        | N_NATIONKEY |   N_NAME   | N_REGIONKEY | N_COMMENT  |
        +-------------+------------+-------------+------------+
        | 0           | [B@6f213028 | 0           | [B@6be0cb17 |
        
        Show
        Venki Korukanti added a comment - - edited I tried the same steps and I don't see the issue. Are you creating any new session after the creating the view? 0: jdbc:drill:zk=local> create view myView as select * from dfs.`/Users/hadoop/mapr-repos/parser-drill/sample-data/nation.parquet`; +------------+------------+ | ok | summary | +------------+------------+ | true | View 'myView' created successfully in 'dfs.tmp' schema | +------------+------------+ 1 row selected (0.026 seconds) 0: jdbc:drill:zk=local> select * from myView; +-------------+------------+-------------+------------+ | N_NATIONKEY | N_NAME | N_REGIONKEY | N_COMMENT | +-------------+------------+-------------+------------+ | 0 | [B@6f213028 | 0 | [B@6be0cb17 |
        Hide
        George Chow added a comment - - edited

        Is this the right way to use view? Still no good.

        0: jdbc:drill:schema=hivestg> use dfs;
        +------------+------------+
        |     ok     |  summary   |
        +------------+------------+
        | true       | Default schema changed to 'dfs' |
        +------------+------------+
        1 row selected (0.098 seconds)
        0: jdbc:drill:schema=hivestg> select * from dfs.`/opt/drill/raw-files/testTypes.json`;
        +------------+------------+------------+------------+------------+-------------+
        |   first    |    last    |    age     |    sex     |   salary   | join_date   |
        +------------+------------+------------+------------+------------+-------------+
        | Jimmy      | James      | 29         | M          | 6300.1     | 2011-11-30  |
        | anderson   | carr       | null       | M          | 6300.1     | null        |
        | John       | James      | 29         | M          | 6300.1     | 2009-12-30  |
        +------------+------------+------------+------------+------------+-------------+
        3 rows selected (0.345 seconds)
        0: jdbc:drill:schema=hivestg> create view myView as select * from dfs.`/opt/drill/raw-files/testTypes.json`;
        +------------+------------+
        |     ok     |  summary   |
        +------------+------------+
        | true       | View 'myView' created successfully in 'dfs' schema |
        +------------+------------+
        1 row selected (0.128 seconds)
        0: jdbc:drill:schema=hivestg> select * from myView;
        Query failed: org.apache.drill.exec.rpc.RpcException: Remote failure while running query.[error_id: "45b96c5d-5e37-4d9d-96bf-4a7c88be859b"
        endpoint {
          address: "localhost"
          user_port: 31010
          control_port: 31011
          data_port: 31012
        }
        error_type: 0
        message: "Failure while parsing sql. < ValidationException:[ org.eigenbase.util.EigenbaseContextException: From line 1, column 15 to line 1, column 20 ] < EigenbaseContextException:[ From line 1, column 15 to line 1, column 20 ] < SqlValidatorException:[ Table \'myView\' not found ]"
        ]
        Error: exception while executing query (state=,code=0)
        
        Show
        George Chow added a comment - - edited Is this the right way to use view? Still no good. 0: jdbc:drill:schema=hivestg> use dfs; +------------+------------+ | ok | summary | +------------+------------+ | true | Default schema changed to 'dfs' | +------------+------------+ 1 row selected (0.098 seconds) 0: jdbc:drill:schema=hivestg> select * from dfs.`/opt/drill/raw-files/testTypes.json`; +------------+------------+------------+------------+------------+-------------+ | first | last | age | sex | salary | join_date | +------------+------------+------------+------------+------------+-------------+ | Jimmy | James | 29 | M | 6300.1 | 2011-11-30 | | anderson | carr | null | M | 6300.1 | null | | John | James | 29 | M | 6300.1 | 2009-12-30 | +------------+------------+------------+------------+------------+-------------+ 3 rows selected (0.345 seconds) 0: jdbc:drill:schema=hivestg> create view myView as select * from dfs.`/opt/drill/raw-files/testTypes.json`; +------------+------------+ | ok | summary | +------------+------------+ | true | View 'myView' created successfully in 'dfs' schema | +------------+------------+ 1 row selected (0.128 seconds) 0: jdbc:drill:schema=hivestg> select * from myView; Query failed: org.apache.drill.exec.rpc.RpcException: Remote failure while running query.[error_id: "45b96c5d-5e37-4d9d-96bf-4a7c88be859b" endpoint { address: "localhost" user_port: 31010 control_port: 31011 data_port: 31012 } error_type: 0 message: "Failure while parsing sql. < ValidationException:[ org.eigenbase.util.EigenbaseContextException: From line 1, column 15 to line 1, column 20 ] < EigenbaseContextException:[ From line 1, column 15 to line 1, column 20 ] < SqlValidatorException:[ Table \'myView\' not found ]" ] Error: exception while executing query (state=,code=0)
        Hide
        Venki Korukanti added a comment - - edited

        This is a bug. Current code is not considering the schema given in view identifier. It always creates the view in default schema and considers the first identifier in the compound identifier as the schema name. Log a separate JIRA. To unblock for now, create view as

         create view myView as select * from dfs.`/opt/drill/raw-files/testTypes.json`;

        And query

        select * from myView

        Make sure the current schema is one of the dfs schemas.

        Show
        Venki Korukanti added a comment - - edited This is a bug. Current code is not considering the schema given in view identifier. It always creates the view in default schema and considers the first identifier in the compound identifier as the schema name. Log a separate JIRA. To unblock for now, create view as create view myView as select * from dfs.`/opt/drill/raw-files/testTypes.json`; And query select * from myView Make sure the current schema is one of the dfs schemas.
        Hide
        George Chow added a comment -

        I pulled and did a build and observed this in sqlline:

        ===
        0: jdbc:drill:schema=hivestg> create view dfs.myView as select * from dfs.`/opt/drill/raw-files/testTypes.json`;
        ----------------------+

        ok summary

        ----------------------+

        true View 'dfs' created successfully in 'dfs' schema

        ----------------------+
        1 row selected (0.105 seconds)
        0: jdbc:drill:schema=hivestg> select * from dfs.myView;
        Query failed: org.apache.drill.exec.rpc.RpcException: Remote failure while running query.[error_id: "eaa86f75-0bfb-4d6e-bd25-43a0b5d60718"
        endpoint

        { address: "localhost" user_port: 31010 control_port: 31011 data_port: 31012 }

        error_type: 0
        message: "Failure while parsing sql. < ValidationException:[ org.eigenbase.util.EigenbaseContextException: From line 1, column 15 to line 1, column 24 ] < EigenbaseContextException:[ From line 1, column 15 to line 1, column 24 ] < SqlValidatorException:[ Table \'dfs.myView\' not found ]"
        ]
        Error: exception while executing query (state=,code=0)
        0: jdbc:drill:schema=hivestg> drop view dfs.myView;
        ----------------------+

        ok summary

        ----------------------+

        false Error: Schema 'dfs' has no view named 'dfs.myView'

        ----------------------+
        1 row selected (0.115 seconds)

        Show
        George Chow added a comment - I pulled and did a build and observed this in sqlline: === 0: jdbc:drill:schema=hivestg> create view dfs.myView as select * from dfs.`/opt/drill/raw-files/testTypes.json`; ----------- -----------+ ok summary ----------- -----------+ true View 'dfs' created successfully in 'dfs' schema ----------- -----------+ 1 row selected (0.105 seconds) 0: jdbc:drill:schema=hivestg> select * from dfs.myView; Query failed: org.apache.drill.exec.rpc.RpcException: Remote failure while running query.[error_id: "eaa86f75-0bfb-4d6e-bd25-43a0b5d60718" endpoint { address: "localhost" user_port: 31010 control_port: 31011 data_port: 31012 } error_type: 0 message: "Failure while parsing sql. < ValidationException:[ org.eigenbase.util.EigenbaseContextException: From line 1, column 15 to line 1, column 24 ] < EigenbaseContextException:[ From line 1, column 15 to line 1, column 24 ] < SqlValidatorException:[ Table \'dfs.myView\' not found ]" ] Error: exception while executing query (state=,code=0) 0: jdbc:drill:schema=hivestg> drop view dfs.myView; ----------- -----------+ ok summary ----------- -----------+ false Error: Schema 'dfs' has no view named 'dfs.myView' ----------- -----------+ 1 row selected (0.115 seconds)
        Hide
        Jacques Nadeau added a comment -

        added in b7bf00c

        Show
        Jacques Nadeau added a comment - added in b7bf00c
        Hide
        Venki Korukanti added a comment -

        Rebased on latest master.

        Show
        Venki Korukanti added a comment - Rebased on latest master.

          People

          • Assignee:
            Venki Korukanti
            Reporter:
            Jacques Nadeau
          • Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development