handle hive view

[wyukawa]

Hi.

I try to use prestogres 0.4.6 and presto 0.72 and Cognos at Centos 6.5.

If there is a hive view and Cognos connects prestogres, the following error occurs.

pgdata/postgres/pg_log/postgresql-Fri.log

< 2014-06-27 14:23:58.178 JST >ERROR:  PrestoQueryException: Query 20140627_052358_00024_thk7q failed: Hive views are not supported: 'default.hvac_view'
< 2014-06-27 14:23:58.178 JST >CONTEXT:  Traceback (most recent call last):
          PL/Python function "run_system_catalog_as_temp_table", line 3, in <module>
            prestogres.run_system_catalog_as_temp_table(server, user, catalog, schema, table_name, query)
          PL/Python function "run_system_catalog_as_temp_table", line 250, in run_system_catalog_as_temp_table
          PL/Python function "run_system_catalog_as_temp_table", line 342, in run
          PL/Python function "run_system_catalog_as_temp_table", line 288, in columns
          PL/Python function "run_system_catalog_as_temp_table", line 330, in _raise_error
        PL/Python function "run_system_catalog_as_temp_table"
< 2014-06-27 14:23:58.178 JST >STATEMENT:  select prestogres_catalog.run_system_catalog_as_temp_table(E'localhost:8080', E'pg', E'hive', E'hive', E'presto_result', E'select relname, nspname, relkind from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relkind in (\'r\', \'v\') and nspname not in (\'pg_catalog\', \'information_schema\', \'pg_toast\', \'pg_temp_1\') and n.oid = relnamespace order by nspname, relname')
< 2014-06-27 14:23:58.178 JST >ERROR:  PrestoQueryException: Query 20140627_052358_00024_thk7q failed: Hive views are not supported: 'default.hvac_view'
< 2014-06-27 14:23:58.178 JST >STATEMENT:  do $$ begin raise exception '%', E'PrestoQueryException: Query 20140627_052358_00024_thk7q failed: Hive views are not supported: \'default.hvac_view\'' using errcode = E'XX000'; end $$ language plpgsql

[frsyuki]

I think same problem with #11. Could you try v0.4.7?

[electrum]

Presto does not support Hive views. However, you can now create views in Presto.

On Jun 27, 2014, at 12:41 AM, wyukawa notifications@github.com wrote:

Hi.

I try to use prestogres 0.4.6 and presto 0.72 and Cognos at Centos 6.5.

If there is a hive view and Cognos connects prestogres, the following error occurs.

pgdata/postgres/pg_log/postgresql-Fri.log

< 2014-06-27 14:23:58.178 JST >ERROR: PrestoQueryException: Query 20140627_052358_00024_thk7q failed: Hive views are not supported: 'default.hvac_view' < 2014-06-27 14:23:58.178 JST >CONTEXT: Traceback (most recent call last): PL/Python function "run_system_catalog_as_temp_table", line 3, in prestogres.run_system_catalog_as_temp_table(server, user, catalog, schema, table_name, query) PL/Python function "run_system_catalog_as_temp_table", line 250, in run_system_catalog_as_temp_table PL/Python function "run_system_catalog_as_temp_table", line 342, in run PL/Python function "run_system_catalog_as_temp_table", line 288, in columns PL/Python function "run_system_catalog_as_temp_table", line 330, in _raise_error PL/Python function "run_system_catalog_as_temp_table" < 2014-06-27 14:23:58.178 JST >STATEMENT: select prestogres_catalog.run_system_catalog_as_temp_table(E'localhost:8080', E'pg', E'hive', E'hive', E'presto_result', E'select relname, nspname, relkind from pg_catalog.pg_class c, pg_catalog.pg_namespace n where relkind in (\'r\', \'v\') and nspname not in (\'pg_catalog\', \'information_schema\', \'pg_toast\', \'pg_temp_1\') and n.oid = relnamespace order by nspname, relname') < 2014-06-27 14:23:58.178 JST >ERROR: PrestoQueryException: Query 20140627_052358_00024_thk7q failed: Hive views are not supported: 'default.hvac_view' < 2014-06-27 14:23:58.178 JST >STATEMENT: do $$ begin raise exception '%', E'PrestoQueryException: Query 20140627_052358_00024_thk7q failed: Hive views are not supported: \'default.hvac_view\'' using errcode = E'XX000'; end $$ language plpgsql — Reply to this email directly or view it on GitHub.

[frsyuki]

@electrum Thank you for the comment. Yes, you can create view in Presto using Presto's SQL syntax. Hive's VIEW is not compatible with Presto.

I found this issue related #11: https://groups.google.com/forum/#!topic/presto-users/u1k3RGPe3pc

[wyukawa]

Thank you for comment.

I understand that Presto does not support Hive views.

But, what I want to do is to access the hive table from Cognos.

Now, if hive view is included even one, Cognos can't access to all hive tables.

For example, there are hive table A, hive table B, hive view C.

In this case, Cognos can't access to A, B, C. I think that it's not good.

If Cognos can't access to C, but access to A, B, I think that it's good.

Anyway, it seems the topic of presto, rather than just about the prestogres.

I watch discussion https://groups.google.com/forum/#!topic/presto-users/u1k3RGPe3pc

[frsyuki]

@wyukawa I think this change fixes this issue: https://github.com/facebook/presto/pull/1441 Here is a built package with the pull-request. Could you try this?:

git clone https://gist.github.com/79c30f23ab0a037eec81.git
file presto-server-0.73-SNAPSHOT.tar.gz

[wyukawa]

It works!

Thanks!

Prestogres displays hive table lists which don't contain hive view.

--additinal informatin presto-server-0.73-SNAPSHOT doesn't contain commons-lang3-3.1.jar, so

java.lang.NoClassDefFoundError: org/apache/commons/lang3/ClassUtils

occurs.

Anyway, thanks!

[frsyuki]

Good to hear that! That's probably my packaging issue. Please use the next official release.

[qinzl1]

failed: Hive views are not supported: presto version 0.170