This page contains information about connecting Looker to Apache Hive 2.3+ and Apache Hive 3.1.2+.

Note the following about Looker support for the different versions of Apache Hive:

- Looker supports connections to Apache Hive 2.3+ and Apache Hive 3.1.2+:
  - For Apache Hive 2.3+, Looker has [integration-level support](https://docs.cloud.google.com/looker/docs/dialects.md.txt#support_levels_and_jdbc_installation).
  - For Apache Hive 3.1.2+, Looker can fully integrate with Apache Hive 3 databases only on versions later than 3.1.2. This is because of a [query parsing issue](https://issues.apache.org/jira/browse/HIVE-18624) from Hive versions 2.4.0 - 3.1.2 that resulted in extremely long parsing times for Looker-generated SQL.
- Looker does not support connections to Apache Hive 2. Queries on connections to Apache Hive 2 will return an error.

## Encrypting network traffic

It is a best practice to encrypt network traffic between the Looker application and your database. Consider one of the options that are described on the [Enabling secure database access](https://docs.cloud.google.com/looker/docs/enabling-secure-db-access.md.txt) documentation page.

## Introduction

Looker is architected to connect to a database server using JDBC. In the case of Hive, this is the thrift server (HiveServer2). See the [Apache documentation](https://cwiki.apache.org/confluence/display/Hive/Setting+Up+HiveServer2) for more information.

By default, this server will listen on port 10000.

Looker is an interactive querying tool, so it expects to work with an interactive SQL engine. If Hive is running on MapReduce --- `hive.execution.engine` is set to `mr` --- then Hive will return query results too slowly to be practical.

Looker was tested with Hive on Tez (`hive.execution.engine=tez`), although it is also possible to run Looker against Hive on Spark. Spark support was added in Hive version 1.1. (Looker supports Hive 1.2.1+.)

## Persistent derived tables (PDTs)

To enable [persistent derived tables (PDTs)](https://docs.cloud.google.com/looker/docs/derived-tables.md.txt#persistent_derived_table) in Looker using a Hive connection, create a scratch schema for Looker to use. The following is an example of a command you can use to create a `looker_scratch` schema:

     CREATE SCHEMA looker_scratch;

The user account that Looker uses to connect to Hive (which can be anonymous if no authentication is used) must have the following abilities in the scratch schema:

- Create tables
- Alter tables
- Drop tables

Test this with a JDBC client before attempting to create PDTs with Hive.

## Queues

If you want queries from Looker to go into a specific queue, enter the queue name parameter in the **Additional JDBC parameters** field on the [**Connection Settings**](https://docs.cloud.google.com/looker/docs/connecting-to-your-db.md.txt#additional_params) page:

    ?tez.queue.name=the_bi_queue

Other Hive parameters can be set this way in the **Additional JDBC parameters** field on the [**Connection Settings**](https://docs.cloud.google.com/looker/docs/connecting-to-your-db.md.txt#additional_params) page.

Using [user attributes](https://docs.cloud.google.com/looker/docs/admin-panel-users-user-attributes.md.txt), it is possible for queries from different users or different groups of users to go into different queues. To do this, create a user attribute named something like `queue_name`; then, in the **Additional JDBC parameters** field, add the following:

    ?tez.queue.name={{ _user_attributes['queue_name'] }}

You can use this to customize other `hive-site.xml` parameters on a per-user or per-group basis as well.

## Creating the Looker connection to your database

Follow these steps to create the connection from Looker to your database:

1. In the **Admin** section of Looker, select **Connections** , and then click **Add Connection**.
2. Select **Apache Hive 2.3+** or **Apache Hive 3.1.2+** from the **Dialect** drop-down menu.

   > [!NOTE]
   > **Note:** If you are on a [Looker (Google Cloud core)](https://docs.cloud.google.com/looker/docs/looker-core-overview.md.txt) instance and you don't see your dialect listed in the **Dialect** drop-down menu, see the [Looker (Google Cloud core) documentation](https://docs.cloud.google.com/looker/docs/looker-core-dialects.md.txt#supported_dialects_for) to verify that the dialect is supported for Looker (Google Cloud core) instances.

3. Fill out the connection details. The majority of the settings are common to most database dialects. See the [Connecting Looker to your database](https://docs.cloud.google.com/looker/docs/connecting-to-your-db.md.txt) documentation page for information. Some of the settings are described next:

   - **Name**: Specify the name of the connection. This is how you will refer to the connection in LookML projects.
   - **Host**: Specify the hostname.
   - **Port**: Specify the database port.
   - **Database**: Specify the database name.
   - **Username**: Specify the database username.
   - **Password**: Specify the database user password.
   - **Enable PDTs** : Use this toggle to enable [persistent derived tables](https://docs.cloud.google.com/looker/docs/derived-tables.md.txt#persistent-derived-tables). When PDTs are enabled, the **Connection** window reveals additional PDT settings and the [**PDT Overrides**](https://docs.cloud.google.com/looker/docs/connecting-to-your-db.md.txt#pdt-overrides) section.
   - **Temp Database** : Specify the name of the scratch schema created in the [Persistent derived tables (PDTs)](https://docs.cloud.google.com/looker/docs/db-config-apache-hive.md.txt#pdts) section of this documentation page.
   - **Max number of PDT builder connections** : Specify the number of possible concurrent PDT builds on this connection. Setting this value too high could negatively impact query times. For more information, see the [Connecting Looker to your database](https://docs.cloud.google.com/looker/docs/connecting-to-your-db.md.txt#max_pdt_builder_connections) documentation page.
   - **Additional JDBC parameters** : Add any additional JDBC parameters. See the [Supported JDBC parameters](https://docs.cloud.google.com/looker/docs/db-config-apache-hive.md.txt#jdbc-supported) section on this page for a list of supported parameters.
   - **Maintenance Schedule** : Specify a [`cron`](https://en.wikipedia.org/wiki/Cron#CRON_expression) expression that indicates when Looker should check [datagroups](https://docs.cloud.google.com/looker/docs/caching-and-datagroups.md.txt#specifying_caching_policies_with_datagroup_parameters) and persistent derived tables. Read more about this setting in the [Maintenance Schedule](https://docs.cloud.google.com/looker/docs/connecting-to-your-db.md.txt#pdt_maintenance_schedule) documentation.
   - **SSL**: Check to use SSL connections.
   - **Verify SSL**: Check for hostname verification.
   - **Max connections per node** : This setting can be left at the default value initially. See the [Connecting Looker to your database](https://docs.cloud.google.com/looker/docs/connecting-to-your-db.md.txt#max_connections) documentation page for more information.
   - **Connection Pool Timeout** : This setting can be left at the default value initially. Read more about this setting in the [Connection Pool Timeout](https://docs.cloud.google.com/looker/docs/connecting-to-your-db.md.txt#connection-pool-timeout) section of the **Connecting Looker to your database** documentation page.
   - **SQL Runner Precache** : To cause SQL Runner not to preload table information and to load table information only when a table is selected, clear this option. Read more about this setting in the [SQL Runner Precache](https://docs.cloud.google.com/looker/docs/connecting-to-your-db.md.txt#sql_runner_precache) section of the **Connecting Looker to your database** documentation page.
   - **Database Time Zone** : Specify the time zone used in the database. Leave this field blank if you don't want time zone conversion. See the [Using time zone settings](https://docs.cloud.google.com/looker/docs/using-time-zone-settings.md.txt) documentation page for more information.
4. To verify that the connection is successful, click the **Test Connection** button in the **Status details** section at the bottom of the **Connections Settings** page.

5. If Looker displays **Can Connect** , press **Connect** to create the connection.

Your database connection is then added to the list on the Looker [**Connections**](https://docs.cloud.google.com/looker/docs/admin-panel-database-connections.md.txt) **Admin** page.

## Supported JDBC parameters

For Apache Hive, Looker supports the following JDBC parameters in the [**Additional JDBC parameters**](https://docs.cloud.google.com/looker/docs/connecting-to-your-db.md.txt#additional_jdbc_parameters) field for the connection. See the documentation for your database for information on these parameters.

- `fetchSize`
- `httpPath`
- `keyStorePassword`
- `password`
- `principal`
- `serviceDiscoveryMode`
- `ssl`
- `tez.queue.name`
- `transportMode`
- `twoWay`
- `user`
- `zooKeeperNamespace`

## Feature support

For Looker to support some features, your database dialect must also support them.

### Apache Hive 2.3+

> [!NOTE]
> **Note:** Looker supports connections to Apache Hive 2.3 and later. Looker does not support connections to Apache Hive 2. Queries on connections to Apache Hive 2 will return an error.

Apache Hive 2.3+ supports the following features as of Looker 26.22:

| Feature | Supported? |
|---|---|
| Looker (Google Cloud core) | No |
| Symmetric aggregates | No |
| Derived tables | Yes |
| Persistent SQL derived tables | Yes |
| Persistent native derived tables | Yes |
| Stable views | Yes |
| Query killing | Yes |
| SQL-based pivots | No |
| Time zones | Yes |
| SSL | Yes |
| Subtotals | Yes |
| JDBC additional params | Yes |
| Case-sensitive | Yes |
| Location type | Yes |
| List type | Yes |
| Percentile | Yes |
| Distinct percentile | No |
| SQL Runner Show Processes | No |
| SQL Runner Describe Table | Yes |
| SQL Runner Show Indexes | Yes |
| SQL Runner Select 10 | Yes |
| SQL Runner Count | Yes |
| SQL Explain | Yes |
| OAuth 2.0 credentials | No |
| Context comments | Yes |
| Connection pooling | No |
| HLL sketches | No |
| Aggregate awareness | No |
| Incremental PDTs | No |
| Milliseconds | Yes |
| Microseconds | Yes |
| Materialized views | No |
| Period-over-period measures | No |
| Approximate count distinct | No |
| In-database analytic models | No |
| Custom calendars | Yes |

### Apache Hive 3.1.2+

Apache Hive 3.1.2+ supports the following features as of Looker 26.22:

| Feature | Supported? |
|---|---|
| Looker (Google Cloud core) | Yes |
| Symmetric aggregates | No |
| Derived tables | Yes |
| Persistent SQL derived tables | Yes |
| Persistent native derived tables | Yes |
| Stable views | Yes |
| Query killing | Yes |
| SQL-based pivots | No |
| Time zones | Yes |
| SSL | Yes |
| Subtotals | Yes |
| JDBC additional params | Yes |
| Case-sensitive | Yes |
| Location type | Yes |
| List type | Yes |
| Percentile | Yes |
| Distinct percentile | No |
| SQL Runner Show Processes | No |
| SQL Runner Describe Table | Yes |
| SQL Runner Show Indexes | No |
| SQL Runner Select 10 | Yes |
| SQL Runner Count | Yes |
| SQL Explain | Yes |
| OAuth 2.0 credentials | No |
| Context comments | Yes |
| Connection pooling | No |
| HLL sketches | No |
| Aggregate awareness | No |
| Incremental PDTs | No |
| Milliseconds | Yes |
| Microseconds | Yes |
| Materialized views | No |
| Period-over-period measures | No |
| Approximate count distinct | No |
| In-database analytic models | No |
| Custom calendars | Yes |

## Next steps

After you have connected your database to Looker, [configure sign-in options for your users](https://docs.cloud.google.com/looker/docs/getting-started-with-users.md.txt).