Accessing Hive Views

A view is a logical table that future queries can refer to. Views do not contain any data but it stores the query. The query that is stored by the view is run each time the view is referenced by another query.

Presto can access Hive views but these conditions apply:

  • Hive views are accessible in Presto but only with best effort delivery.
  • Presto can access a Hive view only when its statement contains ANSI SQL dialects that Presto supports.
  • Presto cannot access a Hive view when its statement contains non-ANSI SQL dialects.

Troubleshooting Hive Views Failures in Presto

The examples below are a few Hive view failures with workarounds:

  • A Hive view statement that contains functions undefined in Presto is inaccessible. For example, a Hive view with statement, SELECT QUOTE (MY_STRING_COLUMN) FROM MY_TABLE does not work in Presto as the QUOTE function is not defined. For such statements, add UDF to Presto, which defines QUOTE functions.

  • A Hive view statement that has strings quoted with ” (double quotes) does not work in Presto. For example, SELECT * FROM MY_TABLE WHERE MY_STRING_COLUMN = "EXPECTED_VALUE" does not work in Presto as the string value is in double quotes.

    To make this work in Presto, use ‘ (a single quote) to quote string values. For example, use SELECT * FROM MY_TABLE WHERE MY_STRING_COLUMN = 'EXPECTED_VALUE' (string value in single quotes) as the Hive view statement.

  • A Hive view statement with its syntax undefined in Presto does not work. For example, SELECT cast(MY_INT_COLUMN as String) FROM MY_TABLE does not work in Presto.

    To make this work in Presto, use SELECT cast(MY_INT_COLUMN as varchar) FROM MY_TABLE as the Hive view statement.

In general, when a Hive view with a long SQL statement does not work in Presto, it is not apparent (from reading the SQL statement) to decide on which part of the statement is non-ANSI complaint. In such Hive views, it is recommended to break the Hive view statement into smaller parts and try as individual Presto queries. This helps in tracing the non-ANSI compliant part of the statement. After tracing such part(s) in the Hive view statement, you need to:

  1. Convert the non-ANSI compliant parts of the Hive statement into ANSI compliant.
  2. Recreate the Hive view with a new statement to make it accessible to Presto.