# Use an external Hive metastore You can use the Amazon Athena data connector for external Hive metastore to query data sets in Amazon S3 that use an Apache Hive metastore. No migration of metadata to the AWS Glue Data Catalog is necessary. In the Athena management console, you configure a Lambda function to communicate with the Hive metastore that is in your private VPC and then connect it to the metastore. The connection from Lambda to your Hive metastore is secured by a private Amazon VPC channel and does not use the public internet. You can provide your own Lambda function code, or you can use the default implementation of the Athena data connector for external Hive metastore. **Topics** + [ ## Overview of features ](#connect-to-a-data-source-hive-features) + [ ## Workflow ](#connect-to-data-source-hive-workflow) + [ ## Considerations and limitations ](#connect-to-a-data-source-hive-considerations) + [ # Connect Athena to an Apache Hive metastore ](connect-to-data-source-hive-connecting-athena-to-an-apache-hive-metastore.md) + [ # Use the AWS Serverless Application Repository to deploy a Hive data source connector ](connect-data-source-sar-hive.md) + [ # Connect Athena to a Hive metastore using an existing IAM execution role ](connect-data-source-hive-existing-iam-role.md) + [ # Configure Athena to use a deployed Hive metastore connector ](connect-data-source-hive-existing-lambda.md) + [ # Omit the catalog name in external Hive metastore queries ](datastores-hive-default-catalog.md) + [ # Work with Hive views ](hive-views.md) + [ # Use the AWS CLI with Hive metastores ](datastores-hive-cli.md) + [ # Modify the Athena external Hive metastore connector ](datastores-hive-reference-implementation.md) ## Overview of features With the Athena data connector for external Hive metastore, you can perform the following tasks: + Use the Athena console to register custom catalogs and run queries using them. + Define Lambda functions for different external Hive metastores and join them in Athena queries. + Use the AWS Glue Data Catalog and your external Hive metastores in the same Athena query. + Specify a catalog in the query execution context as the current default catalog. This removes the requirement to prefix catalog names to database names in your queries. Instead of using the syntax `catalog.database.table`, you can use `database.table`. + Use a variety of tools to run queries that reference external Hive metastores. You can use the Athena console, the AWS CLI, the AWS SDK, Athena APIs, and updated Athena JDBC and ODBC drivers. The updated drivers have support for custom catalogs. ### API support Athena Data Connector for External Hive Metastore includes support for catalog registration API operations and metadata API operations. + **Catalog registration** – Register custom catalogs for external Hive metastores and [federated data sources](federated-queries.md). + **Metadata** – Use metadata APIs to provide database and table information for AWS Glue and any catalog that you register with Athena. + **Athena JAVA SDK client** – Use catalog registration APIs, metadata APIs, and support for catalogs in the `StartQueryExecution` operation in the updated Athena Java SDK client. ### Reference implementation Athena provides a reference implementation for the Lambda function that connects to external Hive metastores. The reference implementation is provided on GitHub as an open source project at [Athena Hive metastore](https://github.com/awslabs/aws-athena-hive-metastore). The reference implementation is available as the following two AWS SAM applications in the AWS Serverless Application Repository (SAR). You can use either of these applications in the SAR to create your own Lambda functions. + `AthenaHiveMetastoreFunction` – Uber Lambda function `.jar` file. An "uber" JAR (also known as a fat JAR or JAR with dependencies) is a `.jar` file that contains both a Java program and its dependencies in a single file. + `AthenaHiveMetastoreFunctionWithLayer` – Lambda layer and thin Lambda function `.jar` file. ## Workflow The following diagram shows how Athena interacts with your external Hive metastore. ![\[How Athena interacts with your external Hive metastore.\]](http://docs.aws.amazon.com/athena/latest/ug/images/connect-to-data-source-hive-workflow.png) In this workflow, your database-connected Hive metastore is inside your VPC. You use Hive Server2 to manage your Hive metastore using the Hive CLI. The workflow for using external Hive metastores from Athena includes the following steps. 1. You create a Lambda function that connects Athena to the Hive metastore that is inside your VPC. 1. You register a unique catalog name for your Hive metastore and a corresponding function name in your account. 1. When you run an Athena DML or DDL query that uses the catalog name, the Athena query engine calls the Lambda function name that you associated with the catalog name. 1. Using AWS PrivateLink, the Lambda function communicates with the external Hive metastore in your VPC and receives responses to metadata requests. Athena uses the metadata from your external Hive metastore just like it uses the metadata from the default AWS Glue Data Catalog. ## Considerations and limitations When you use Athena Data Connector for External Hive Metastore, consider the following points: + You can use CTAS to create a table on an external Hive metastore. + You can use INSERT INTO to insert data into an external Hive metastore. + DDL support for external Hive metastore is limited to the following statements. + ALTER DATABASE SET DBPROPERTIES + ALTER TABLE ADD COLUMNS + ALTER TABLE ADD PARTITION + ALTER TABLE DROP PARTITION + ALTER TABLE RENAME PARTITION + ALTER TABLE REPLACE COLUMNS + ALTER TABLE SET LOCATION + ALTER TABLE SET TBLPROPERTIES + CREATE DATABASE + CREATE TABLE + CREATE TABLE AS + DESCRIBE TABLE + DROP DATABASE + DROP TABLE + SHOW COLUMNS + SHOW CREATE TABLE + SHOW PARTITIONS + SHOW SCHEMAS + SHOW TABLES + SHOW TBLPROPERTIES + The maximum number of registered catalogs that you can have is 1,000. + Kerberos authentication for Hive metastore is not supported. + To use the JDBC driver with an external Hive metastore or [federated queries](federated-queries.md), include `MetadataRetrievalMethod=ProxyAPI` in your JDBC connection string. For information about the JDBC driver, see [Connect to Amazon Athena with JDBC](connect-with-jdbc.md). + The Hive hidden columns `$path`, `$bucket`, `$file_size`, `$file_modified_time`, `$partition`, `$row_id` cannot be used for fine-grained access control filtering. + Hive hidden system tables like `example_table$partitions` or `example_table$properties` are not supported by fine-grained access control. ### Permissions Prebuilt and custom data connectors might require access to the following resources to function correctly. Check the information for the connector that you use to make sure that you have configured your VPC correctly. For information about required IAM permissions to run queries and create a data source connector in Athena, see [Allow access to the Athena Data Connector for External Hive Metastore](hive-metastore-iam-access.md) and [Allow Lambda function access to external Hive metastores](hive-metastore-iam-access-lambda.md). + **Amazon S3** – In addition to writing query results to the Athena query results location in Amazon S3, data connectors also write to a spill bucket in Amazon S3. Connectivity and permissions to this Amazon S3 location are required. For more information, see [Spill location in Amazon S3](#connect-to-data-source-hive-spill-location) later in this topic. + **Athena** – Access is required to check query status and prevent overscan. + **AWS Glue** – Access is required if your connector uses AWS Glue for supplemental or primary metadata. + **AWS Key Management Service** + **Policies** – Hive metastore, Athena Query Federation, and UDFs require policies in addition to the [AWS managed policy: AmazonAthenaFullAccess](security-iam-awsmanpol.md#amazonathenafullaccess-managed-policy). For more information, see [Identity and access management in Athena](security-iam-athena.md). ### Spill location in Amazon S3 Because of the [limit](https://docs.aws.amazon.com/lambda/latest/dg/limits.html) on Lambda function response sizes, responses larger than the threshold spill into an Amazon S3 location that you specify when you create your Lambda function. Athena reads these responses from Amazon S3 directly. **Note** Athena does not remove the response files on Amazon S3. We recommend that you set up a retention policy to delete response files automatically. # Connect Athena to an Apache Hive metastore To connect Athena to an Apache Hive metastore, you must create and configure a Lambda function. For a basic implementation, you can perform all required steps starting from the Athena management console. **Note** The following procedure requires that you have permission to create a custom IAM role for the Lambda function. If you do not have permission to create a custom role, you can use the Athena [reference implementation](connect-to-data-source-hive.md#connect-to-a-data-source-hive-features-reference-implementation) to create a Lambda function separately, and then use the AWS Lambda console to choose an existing IAM role for the function. For more information, see [Connect Athena to a Hive metastore using an existing IAM execution role](connect-data-source-hive-existing-iam-role.md). **To connect Athena to a Hive metastore** 1. Open the Athena console at [https://console.aws.amazon.com/athena/](https://console.aws.amazon.com/athena/home). 1. If the console navigation pane is not visible, choose the expansion menu on the left. ![\[Choose the expansion menu.\]](http://docs.aws.amazon.com/athena/latest/ug/images/nav-pane-expansion.png) 1. Choose **Data sources and catalogs**. 1. On the upper right of the console, choose **Create data source**. 1. On the **Choose a data source** page, for **Data sources**, choose **S3 - Apache Hive metastore**. 1. Choose **Next**. 1. In the **Data source details** section, for **Data source name**, enter the name that you want to use in your SQL statements when you query the data source from Athena. The name can be up to 127 characters and must be unique within your account. It cannot be changed after you create it. Valid characters are a-z, A-Z, 0-9, \$1 (underscore), @ (at sign) and - (hyphen). The names `awsdatacatalog`, `hive`, `jmx`, and `system` are reserved by Athena and cannot be used for data source names. 1. For **Lambda function**, choose **Create Lambda function**, and then choose **Create a new Lambda function in AWS Lambda** The **AthenaHiveMetastoreFunction** page opens in the AWS Lambda console. The page includes detailed information about the connector. ![\[The AthenaHiveMetastoreFunction page in the AWS Lambda console.\]](http://docs.aws.amazon.com/athena/latest/ug/images/connect-to-data-source-hive-4.png) 1. Under **Application settings**, enter the parameters for your Lambda function. + **LambdaFuncName** – Provide a name for the function. For example, **myHiveMetastore**. + **SpillLocation** – Specify an Amazon S3 location in this account to hold spillover metadata if the Lambda function response size exceeds 4 MB. + **HMSUris** – Enter the URI of your Hive metastore host that uses the Thrift protocol at port 9083. Use the syntax `thrift://:9083`. + **LambdaMemory** – Specify a value from 128 MB to 3008 MB. The Lambda function is allocated CPU cycles proportional to the amount of memory that you configure. The default is 1024. + **LambdaTimeout** – Specify the maximum permissible Lambda invocation run time in seconds from 1 to 900 (900 seconds is 15 minutes). The default is 300 seconds (5 minutes). + **VPCSecurityGroupIds** – Enter a comma-separated list of VPC security group IDs for the Hive metastore. + **VPCSubnetIds** – Enter a comma-separated list of VPC subnet IDs for the Hive metastore. 1. Select **I acknowledge that this app creates custom IAM roles**, and then choose **Deploy**. ![\[Deploying the Lambda function app from the AWS Lambda console.\]](http://docs.aws.amazon.com/athena/latest/ug/images/connect-to-data-source-hive-4a.png) When the deployment completes, your function appears in your list of Lambda applications. Now that the Hive metastore function has been deployed to your account, you can configure Athena to use it. 1. Return to the **Enter data source details** page of the Athena console. 1. In the **Lambda function** section, choose the refresh icon next to the Lambda function search box. Refreshing the list of available functions causes your newly created function to appear in the list. 1. Choose the name of the function that you just created in the Lambda console. The ARN of the Lambda function displays. 1. (Optional) For **Tags**, add key-value pairs to associate with this data source. For more information about tags, see [Tag Athena resources](tags.md). 1. Choose **Next**. 1. On the **Review and create** page, review the data source details, and then choose **Create data source**. 1. The **Data source details** section of the page for your data source shows information about your new connector. You can now use the **Data source name** that you specified to reference the Hive metastore in your SQL queries in Athena. In your SQL queries, use the following example syntax, replacing `hms-catalog-1` with the catalog name that you specified earlier. ``` SELECT * FROM hms-catalog-1.CustomerData.customers ``` 1. For information about viewing, editing, or deleting the data sources that you create, see [Manage your data sources](data-sources-managing.md). # Use the AWS Serverless Application Repository to deploy a Hive data source connector To deploy an Athena data source connector for Hive, you can use the [AWS Serverless Application Repository](https://aws.amazon.com/serverless/serverlessrepo/) instead of starting with the Athena console. Use the AWS Serverless Application Repository to find the connector that you want to use, provide the parameters that the connector requires, and then deploy the connector to your account. Then, after you deploy the connector, you use the Athena console to make the data source available to Athena. **To use the AWS Serverless Application Repository to deploy a data source connector for Hive to your account** 1. Sign in to the AWS Management Console and open the **Serverless App Repository**. 1. In the navigation pane, choose **Available applications**. 1. Select the option **Show apps that create custom IAM roles or resource policies**. 1. In the search box, enter **Hive**. The connectors that appear include the following two: + **AthenaHiveMetastoreFunction** – Uber Lambda function `.jar` file. + **AthenaHiveMetastoreFunctionWithLayer** – Lambda layer and thin Lambda function `.jar` file. The two applications have the same functionality and differ only in their implementation. You can use either one to create a Lambda function that connects Athena to your Hive metastore. 1. Choose the name of the connector that you want to use. This tutorial uses **AthenaHiveMetastoreFunction**. ![\[Choose the name of the Athena data source connector for Hive.\]](http://docs.aws.amazon.com/athena/latest/ug/images/connect-data-source-sar-hive-1.png) 1. Under **Application settings**, enter the parameters for your Lambda function. + **LambdaFuncName** – Provide a name for the function. For example, **myHiveMetastore**. + **SpillLocation** – Specify an Amazon S3 location in this account to hold spillover metadata if the Lambda function response size exceeds 4 MB. + **HMSUris** – Enter the URI of your Hive metastore host that uses the Thrift protocol at port 9083. Use the syntax `thrift://:9083`. + **LambdaMemory** – Specify a value from 128 MB to 3008 MB. The Lambda function is allocated CPU cycles proportional to the amount of memory that you configure. The default is 1024. + **LambdaTimeout** – Specify the maximum permissible Lambda invocation run time in seconds from 1 to 900 (900 seconds is 15 minutes). The default is 300 seconds (5 minutes). + **VPCSecurityGroupIds** – Enter a comma-separated list of VPC security group IDs for the Hive metastore. + **VPCSubnetIds** – Enter a comma-separated list of VPC subnet IDs for the Hive metastore. 1. On the bottom right of the **Application details** page, select **I acknowledge that this app creates custom IAM roles**, and then choose **Deploy**. At this point, you can configure Athena to use your Lambda function to connect to your Hive metastore. For steps, see [Configure Athena to use a deployed Hive metastore connector](connect-data-source-hive-existing-lambda.md). # Connect Athena to a Hive metastore using an existing IAM execution role To connect your external Hive metastore to Athena with a Lambda function that uses an existing IAM role, you can use Athena's reference implementation of the Athena connector for external Hive metastore. The three major steps are as follows: 1. **[Clone and build](#connect-data-source-hive-existing-iam-role-clone-and-build-the-lambda-function)** – Clone the Athena reference implementation and build the JAR file that contains the Lambda function code. 1. **[AWS Lambda console](#connect-data-source-hive-existing-iam-role-aws-lambda-console)** – In the AWS Lambda console, create a Lambda function, assign it an existing IAM execution role, and upload the function code that you generated. 1. **[Amazon Athena console](connect-data-source-hive-existing-lambda.md)** – In the Amazon Athena console, create a data source name that you can use to refer to your external Hive metastore in your Athena queries. If you already have permissions to create a custom IAM role, you can use a simpler workflow that uses the Athena console and the AWS Serverless Application Repository to create and configure a Lambda function. For more information, see [Connect Athena to an Apache Hive metastore](connect-to-data-source-hive-connecting-athena-to-an-apache-hive-metastore.md). ## Prerequisites + Git must be installed on your system. + You must have [Apache Maven](https://maven.apache.org/) installed. + You have an IAM execution role that you can assign to the Lambda function. For more information, see [Allow Lambda function access to external Hive metastores](hive-metastore-iam-access-lambda.md). ## Clone and build the Lambda function The function code for the Athena reference implementation is a Maven project located on GitHub at [awslabs/aws-athena-hive-metastore](https://github.com/awslabs/aws-athena-hive-metastore). For detailed information about the project, see the corresponding README file on GitHub or the [Modify the Athena external Hive metastore connector](datastores-hive-reference-implementation.md) topic in this documentation. **To clone and build the Lambda function code** 1. Enter the following command to clone the Athena reference implementation: ``` git clone https://github.com/awslabs/aws-athena-hive-metastore ``` 1. Run the following command to build the `.jar` file for the Lambda function: ``` mvn clean install ``` After the project builds successfully, the following `.jar` file is created in the target folder of your project: `hms-lambda-func-1.0-SNAPSHOT-withdep.jar` In the next section, you use the AWS Lambda console to upload this file to your Amazon Web Services account. ## Create and configure the Lambda function in the AWS Lambda console In this section, you use the AWS Lambda console to create a function that uses an existing IAM execution role. After you configure a VPC for the function, you upload the function code and configure the environment variables for the function. ### Create the Lambda function In this step, you create a function in the AWS Lambda console that uses an existing IAM role. **To create a Lambda function that uses an existing IAM role** 1. Sign in to the AWS Management Console and open the AWS Lambda console at [https://console.aws.amazon.com/lambda/](https://console.aws.amazon.com/lambda/). 1. In the navigation pane, choose **Functions**. 1. Choose **Create function**. 1. Choose **Author from scratch**. 1. For **Function name**, enter the name of your Lambda function (for example, **EHMSBasedLambda**). 1. For **Runtime**, choose **Java 8**. 1. Under **Permissions**, expand **Change default execution role**. 1. For **Execution role**, choose **Use an existing role**. 1. For **Existing role**, choose the IAM execution role that your Lambda function will use for Athena (this example uses a role called `AthenaLambdaExecutionRole`). 1. Expand **Advanced settings**. 1. Select **Enable Network**. 1. For **VPC**, choose the VPC that your function will have access to. 1. For **Subnets**, choose the VPC subnets for Lambda to use. 1. For **Security groups**, choose the VPC security groups for Lambda to use. 1. Choose **Create function**. The AWS Lambda console and opens the configuration page for your function and begins creating your function. ### Upload the code and configure the Lambda function When the console informs you that your function has been successfully created, you are ready to upload the function code and configure its environment variables. **To upload your Lambda function code and configure its environment variables** 1. In the Lambda console, make sure that you are on the **Code** tab of the page of the function that you specfied. 1. For **Code source**, choose **Upload from**, and then choose **.zip or .jar file**. 1. Upload the `hms-lambda-func-1.0-SNAPSHOT-withdep.jar` file that you generated previously. 1. On your Lambda function page, choose the **Configuration** tab. 1. From the pane on the left, choose **Environment variables**. 1. In the **Environment variables** section, choose **Edit**. ![\[Choose Edit to edit the environment variables for the Lambda function.\]](http://docs.aws.amazon.com/athena/latest/ug/images/connect-data-source-hive-existing-iam-role-5.png) 1. On the **Edit environment variables** page, use the **Add environment variable** option to add the following environment variable keys and values: + **HMS\$1URIS** – Use the following syntax to enter the URI of your Hive metastore host that uses the Thrift protocol at port 9083. ``` thrift://:9083 ``` + **SPILL\$1LOCATION** – Specify an Amazon S3 location in your Amazon Web Services account to hold spillover metadata if the Lambda function response size exceeds 4 MB. ![\[Specifying values for the Lambda function environment variables.\]](http://docs.aws.amazon.com/athena/latest/ug/images/connect-data-source-hive-existing-iam-role-6.png) 1. Choose **Save**. At this point, you are ready to configure Athena to use your Lambda function to connect to your Hive metastore. For steps, see [Configure Athena to use a deployed Hive metastore connector](connect-data-source-hive-existing-lambda.md). # Configure Athena to use a deployed Hive metastore connector After you have deployed a Lambda data source connector like `AthenaHiveMetastoreFunction` to your account, you can configure Athena to use it. To do so, you create a data source name that refers to your external Hive metastore to use in your Athena queries. **To connect Athena to your Hive metastore using an existing Lambda function** 1. Open the Athena console at [https://console.aws.amazon.com/athena/](https://console.aws.amazon.com/athena/home). 1. If the console navigation pane is not visible, choose the expansion menu on the left. ![\[Choose the expansion menu.\]](http://docs.aws.amazon.com/athena/latest/ug/images/nav-pane-expansion.png) 1. Choose **Data sources and catalogs**. 1. On the **Data sources and catalogs** page, choose **Create data source**. 1. On the **Choose a data source** page, for **Data sources**, choose **S3 - Apache Hive metastore**. 1. Choose **Next**. 1. In the **Data source details** section, for **Data source name**, enter the name that you want to use in your SQL statements when you query the data source from Athena (for example, `MyHiveMetastore`). The name can be up to 127 characters and must be unique within your account. It cannot be changed after you create it. Valid characters are a-z, A-Z, 0-9, \$1 (underscore), @ (at sign) and - (hyphen). The names `awsdatacatalog`, `hive`, `jmx`, and `system` are reserved by Athena and cannot be used for data source names. 1. In the **Connection details** section, use the **Select or enter a Lambda function** box to choose the name of the function that you just created. The ARN of the Lambda function displays. 1. (Optional) For **Tags**, add key-value pairs to associate with this data source. For more information about tags, see [Tag Athena resources](tags.md). 1. Choose **Next**. 1. On the **Review and create** page, review the data source details, and then choose **Create data source**. 1. The **Data source details** section of the page for your data source shows information about your new connector. You can now use the **Data source name** that you specified to reference the Hive metastore in your SQL queries in Athena. In your SQL queries, use the following example syntax, replacing `ehms-catalog` with the data source name that you specified earlier. ``` SELECT * FROM ehms-catalog.CustomerData.customers ``` 1. To view, edit, or delete the data sources that you create, see [Manage your data sources](data-sources-managing.md). # Omit the catalog name in external Hive metastore queries When you run DML and DDL queries on external Hive metastores, you can simplify your query syntax by omitting the catalog name if that name is selected in the query editor. Certain restrictions apply to this functionality. ## DML statements **To run queries with registered catalogs** 1. You can put the data source name before the database using the syntax `[[data_source_name].database_name].table_name`, as in the following example. ``` select * from "hms-catalog-1".hms_tpch.customer limit 10; ``` 1. When the data source that you want to use is already selected in the query editor, you can omit the name from the query, as in the following example. ``` select * from hms_tpch.customer limit 10: ``` ![\[A DML query using a default data source.\]](http://docs.aws.amazon.com/athena/latest/ug/images/datastores-hive-default-catalog-2.png) 1. When you use multiple data sources in a query, you can omit only the default data source name, and must specify the full name for any non-default data sources. For example, suppose `AwsDataCatalog` is selected as the default data source in the query editor. The `FROM` statement in the following query excerpt fully qualifies the first two data source names but omits the name for the third data source because it is in the AWS Glue data catalog. ``` ... FROM ehms01.hms_tpch.customer, "hms-catalog-1".hms_tpch.orders, hms_tpch.lineitem ... ``` ## DDL statements The following Athena DDL statements support catalog name prefixes. Catalog name prefixes in other DDL statements cause syntax errors. ``` SHOW TABLES [IN [catalog_name.]database_name] ['regular_expression'] SHOW TBLPROPERTIES [[catalog_name.]database_name.]table_name [('property_name')] SHOW COLUMNS IN [[catalog_name.]database_name.]table_name SHOW PARTITIONS [[catalog_name.]database_name.]table_name SHOW CREATE TABLE [[catalog_name.][database_name.]table_name DESCRIBE [EXTENDED | FORMATTED] [[catalog_name.][database_name.]table_name [PARTITION partition_spec] [col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )] ``` As with DML statements, you can omit the datasource and database prefixes from the query when the data source and database are selected in the query editor. In the following image, the `hms-catalog-1` data source and the `hms_tpch` database are selected in the query editor. The `show create table customer` statement succeeds even though the `hms-catalog-1` prefix and the `hms_tpch` database name are omitted from the query itself. ![\[A DDL statement using the default catalog.\]](http://docs.aws.amazon.com/athena/latest/ug/images/datastores-hive-default-catalog-4.png) ## Specifying a default data source in a JDBC connection string When you use the Athena JDBC Driver to connect Athena to an external Hive metastore, you can use the `Catalog` parameter to specify the default data source name in your connection string in a SQL editor like [SQL workbench](https://www.sql-workbench.eu/index.html). **Note** To download the latest Athena JDBC drivers, see [Using Athena with the JDBC driver](https://docs.aws.amazon.com/athena/latest/ug/connect-with-jdbc.html). The following connection string specifies the default data source *hms-catalog-name*. ``` jdbc:awsathena://AwsRegion=us-east-1;S3OutputLocation=s3://amzn-s3-demo-bucket/lambda/results/;Workgroup=AmazonAthenaPreviewFunctionality;Catalog=hms-catalog-name; ``` The following image shows a sample JDBC connection URL as configured in SQL Workbench. ![\[Configuring a JDBC connection URL in SQL Workbench.\]](http://docs.aws.amazon.com/athena/latest/ug/images/datastores-hive-default-catalog-jdbc-1.jpg) # Work with Hive views You can use Athena to query existing views in your external Apache Hive metastores. Athena translates your views for you on-the-fly at runtime without changing the original view or storing the translation. For example, suppose you have a Hive view like the following that uses a syntax not supported in Athena like `LATERAL VIEW explode()`: ``` CREATE VIEW team_view AS SELECT team, score FROM matches LATERAL VIEW explode(scores) m AS score ``` Athena translates the Hive view query string into a statement like the following that Athena can run: ``` SELECT team, score FROM matches CROSS JOIN UNNEST(scores) AS m (score) ``` For information about connecting an external Hive metastore to Athena, see [Use an external Hive metastore](connect-to-data-source-hive.md). ## Considerations and limitations When querying Hive views from Athena, consider the following points: + Athena does not support creating Hive views. You can create Hive views in your external Hive metastore, which you can then query from Athena. + Athena does not support custom UDFs for Hive views. + Due to a known issue in the Athena console, Hive views appear under the list of tables instead of the list of views. + Although the translation process is automatic, certain Hive functions are not supported for Hive views or require special handling. For more information, see the following section. ## Hive function support limitations This section highlights the Hive functions that Athena does not support for Hive views or that require special treatment. Currently, because Athena primarily supports functions from Hive 2.2.0, functions that are available only in higher versions (such as Hive 4.0.0) are not available. For a full list of Hive functions, see [Hive language manual UDF](https://cwiki.apache.org/confluence/display/hive/languagemanual+udf). ### Aggregate functions #### Aggregate functions that require special handling The following aggregate function for Hive views requires special handling. + **Avg** – Instead of `avg(INT i)`, use `avg(CAST(i AS DOUBLE))`. #### Aggregate functions not supported The following Hive aggregate functions are not supported in Athena for Hive views. ``` covar_pop histogram_numeric ntile percentile percentile_approx ``` Regression functions like `regr_count`, `regr_r2`, and `regr_sxx` are not supported in Athena for Hive views. ### Date functions not supported The following Hive date functions are not supported in Athena for Hive views. ``` date_format(date/timestamp/string ts, string fmt) day(string date) dayofmonth(date) extract(field FROM source) hour(string date) minute(string date) month(string date) quarter(date/timestamp/string) second(string date) weekofyear(string date) year(string date) ``` ### Masking functions not supported Hive masking functions like `mask()`, and `mask_first_n()` are not supported in Athena for Hive views. ### Miscellaneous functions #### Miscellaneous functions that require special handling The following miscellaneous functions for Hive views require special handling. + **md5** – Athena supports `md5(binary)` but not `md5(varchar)`. + **Explode** – Athena supports `explode` when it is used in the following syntax: ``` LATERAL VIEW [OUTER] EXPLODE() ``` + **Posexplode** – Athena supports `posexplode` when it is used in the following syntax: ``` LATERAL VIEW [OUTER] POSEXPLODE() ``` In the `(pos, val)` output, Athena treats the `pos` column as `BIGINT`. Because of this, you may need to cast the `pos` column to `BIGINT` to avoid a stale view. The following example illustrates this technique. ``` SELECT CAST(c AS BIGINT) AS c_bigint, d FROM table LATERAL VIEW POSEXPLODE() t AS c, d ``` #### Miscellaneous functions not supported The following Hive functions are not supported in Athena for Hive views. ``` aes_decrypt aes_encrypt current_database current_user inline java_method logged_in_user reflect sha/sha1/sha2 stack version ``` ### Operators #### Operators that require special handling The following operators for Hive views require special handling. + **Mod operator (%)** – Because the `DOUBLE` type implicitly casts to `DECIMAL(x,y)`, the following syntax can cause a View is stale error message: ``` a_double % 1.0 AS column ``` To work around this issue, use `CAST`, as in the following example. ``` CAST(a_double % 1.0 as DOUBLE) AS column ``` + **Division operator (/)** – In Hive, `int` divided by `int` produces a `double`. In Athena, the same operation produces a truncated `int`. #### Operators not supported Athena does not support the following operators for Hive views. **\$1A** – bitwise `NOT` **A ^ b** – bitwise `XOR` **A & b** – bitwise `AND` **A \$1 b** – bitwise `OR` **A <=> b** – Returns same result as the equals (`=`) operator for non-null operands. Returns `TRUE` if both are `NULL`, `FALSE` if one of them is `NULL`. ### String functions #### String functions that require special handling The following Hive string functions for Hive views require special handling. + **chr(bigint\$1double a)** – Hive allows negative arguments; Athena does not. + **instr(string str, string substr)** – Because the Athena mapping for the `instr` function returns `BIGINT` instead of `INT`, use the following syntax: ``` CAST(instr(string str, string substr) as INT) ``` Without this step, the view will be considered stale. + **length(string a)** – Because the Athena mapping for the `length` function returns `BIGINT` instead of `INT`, use the following syntax so that the view will not be considered stale: ``` CAST(length(string str) as INT) ``` #### String functions not supported The following Hive string functions are not supported in Athena for Hive views. ``` ascii(string str) character_length(string str) decode(binary bin, string charset) encode(string src, string charset) elt(N int,str1 string,str2 string,str3 string,...) field(val T,val1 T,val2 T,val3 T,...) find_in_set(string str, string strList) initcap(string A) levenshtein(string A, string B) locate(string substr, string str[, int pos]) octet_length(string str) parse_url(string urlString, string partToExtract [, string keyToExtract]) printf(String format, Obj... args) quote(String text) regexp_extract(string subject, string pattern, int index) repeat(string str, int n) sentences(string str, string lang, string locale) soundex(string A) space(int n) str_to_map(text[, delimiter1, delimiter2]) substring_index(string A, string delim, int count) ``` ### XPath functions not supported Hive XPath functions like `xpath`, `xpath_short`, and `xpath_int` are not supported in Athena for Hive views. ## Troubleshooting When you use Hive views in Athena, you may encounter the following issues: + **View ** is stale** – This message usually indicates a type mismatch between the view in Hive and Athena. If the same function in the [Hive LanguageManual UDF](https://cwiki.apache.org/confluence/display/hive/languagemanual+udf) and [Presto functions and operators](https://prestodb.io/docs/current/functions.html) documentation has different signatures, try casting the mismatched data type. + **Function not registered** – Athena does not currently support the function. For details, see the information earlier in this document. # Use the AWS CLI with Hive metastores You can use `aws athena` CLI commands to manage the Hive metastore data catalogs that you use with Athena. After you have defined one or more catalogs to use with Athena, you can reference those catalogs in your `aws athena` DDL and DML commands. ## Using the AWS CLI to manage Hive metastore catalogs ### Registering a catalog: Create-data-catalog To register a data catalog, you use the `create-data-catalog` command. Use the `name` parameter to specify the name that you want to use for the catalog. Pass the ARN of the Lambda function to the `metadata-function` option of the `parameters` argument. To create tags for the new catalog, use the `tags` parameter with one or more space-separated `Key=key,Value=value` argument pairs. The following example registers the Hive metastore catalog named `hms-catalog-1`. The command has been formatted for readability. ``` $ aws athena create-data-catalog --name "hms-catalog-1" --type "HIVE" --description "Hive Catalog 1" --parameters "metadata-function=arn:aws:lambda:us-east-1:111122223333:function:external-hms-service-v3,sdk-version=1.0" --tags Key=MyKey,Value=MyValue --region us-east-1 ``` ### Showing catalog details: Get-data-catalog To show the details of a catalog, pass the name of the catalog to the `get-data-catalog` command, as in the following example. ``` $ aws athena get-data-catalog --name "hms-catalog-1" --region us-east-1 ``` The following sample result is in JSON format. ``` { "DataCatalog": { "Name": "hms-catalog-1", "Description": "Hive Catalog 1", "Type": "HIVE", "Parameters": { "metadata-function": "arn:aws:lambda:us-east-1:111122223333:function:external-hms-service-v3", "sdk-version": "1.0" } } } ``` ### Listing registered catalogs: List-data-catalogs To list the registered catalogs, use the `list-data-catalogs` command and optionally specify a Region, as in the following example. The catalogs listed always include AWS Glue. ``` $ aws athena list-data-catalogs --region us-east-1 ``` The following sample result is in JSON format. ``` { "DataCatalogs": [ { "CatalogName": "AwsDataCatalog", "Type": "GLUE" }, { "CatalogName": "hms-catalog-1", "Type": "HIVE", "Parameters": { "metadata-function": "arn:aws:lambda:us-east-1:111122223333:function:external-hms-service-v3", "sdk-version": "1.0" } } ] } ``` ### Updating a catalog: Update-data-catalog To update a data catalog, use the `update-data-catalog` command, as in the following example. The command has been formatted for readability. ``` $ aws athena update-data-catalog --name "hms-catalog-1" --type "HIVE" --description "My New Hive Catalog Description" --parameters "metadata-function=arn:aws:lambda:us-east-1:111122223333:function:external-hms-service-new,sdk-version=1.0" --region us-east-1 ``` ### Deleting a catalog: Delete-data-catalog To delete a data catalog, use the `delete-data-catalog` command, as in the following example. ``` $ aws athena delete-data-catalog --name "hms-catalog-1" --region us-east-1 ``` ### Showing database details: Get-database To show the details of a database, pass the name of the catalog and the database to the `get-database` command, as in the following example. ``` $ aws athena get-database --catalog-name hms-catalog-1 --database-name mydb ``` The following sample result is in JSON format. ``` { "Database": { "Name": "mydb", "Description": "My database", "Parameters": { "CreatedBy": "Athena", "EXTERNAL": "TRUE" } } } ``` ### Listing databases in a catalog: List-databases To list the databases in a catalog, use the `list-databases` command and optionally specify a Region, as in the following example. ``` $ aws athena list-databases --catalog-name AwsDataCatalog --region us-west-2 ``` The following sample result is in JSON format. ``` { "DatabaseList": [ { "Name": "default" }, { "Name": "mycrawlerdatabase" }, { "Name": "mydatabase" }, { "Name": "sampledb", "Description": "Sample database", "Parameters": { "CreatedBy": "Athena", "EXTERNAL": "TRUE" } }, { "Name": "tpch100" } ] } ``` ### Showing table details: Get-table-metadata To show the metadata for a table, including column names and datatypes, pass the name of the catalog, database, and table name to the `get-table-metadata` command, as in the following example. ``` $ aws athena get-table-metadata --catalog-name AwsDataCatalog --database-name mydb --table-name cityuseragent ``` The following sample result is in JSON format. ``` { "TableMetadata": { "Name": "cityuseragent", "CreateTime": 1586451276.0, "LastAccessTime": 0.0, "TableType": "EXTERNAL_TABLE", "Columns": [ { "Name": "city", "Type": "string" }, { "Name": "useragent1", "Type": "string" } ], "PartitionKeys": [], "Parameters": { "COLUMN_STATS_ACCURATE": "false", "EXTERNAL": "TRUE", "inputformat": "org.apache.hadoop.mapred.TextInputFormat", "last_modified_by": "hadoop", "last_modified_time": "1586454879", "location": "s3://amzn-s3-demo-bucket/", "numFiles": "1", "numRows": "-1", "outputformat": "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat", "rawDataSize": "-1", "serde.param.serialization.format": "1", "serde.serialization.lib": "org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe", "totalSize": "61" } } } ``` ### Showing metadata for all tables in a database: List-table-metadata To show the metadata for all tables in a database, pass the name of the catalog and database name to the `list-table-metadata` command. The `list-table-metadata` command is similar to the `get-table-metadata` command, except that you do not specify a table name. To limit the number of results, you can use the `--max-results` option, as in the following example. ``` $ aws athena list-table-metadata --catalog-name AwsDataCatalog --database-name sampledb --region us-east-1 --max-results 2 ``` The following sample result is in JSON format. ``` { "TableMetadataList": [ { "Name": "cityuseragent", "CreateTime": 1586451276.0, "LastAccessTime": 0.0, "TableType": "EXTERNAL_TABLE", "Columns": [ { "Name": "city", "Type": "string" }, { "Name": "useragent1", "Type": "string" } ], "PartitionKeys": [], "Parameters": { "COLUMN_STATS_ACCURATE": "false", "EXTERNAL": "TRUE", "inputformat": "org.apache.hadoop.mapred.TextInputFormat", "last_modified_by": "hadoop", "last_modified_time": "1586454879", "location": "s3://amzn-s3-demo-bucket/", "numFiles": "1", "numRows": "-1", "outputformat": "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat", "rawDataSize": "-1", "serde.param.serialization.format": "1", "serde.serialization.lib": "org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe", "totalSize": "61" } }, { "Name": "clearinghouse_data", "CreateTime": 1589255544.0, "LastAccessTime": 0.0, "TableType": "EXTERNAL_TABLE", "Columns": [ { "Name": "location", "Type": "string" }, { "Name": "stock_count", "Type": "int" }, { "Name": "quantity_shipped", "Type": "int" } ], "PartitionKeys": [], "Parameters": { "EXTERNAL": "TRUE", "inputformat": "org.apache.hadoop.mapred.TextInputFormat", "location": "s3://amzn-s3-demo-bucket/", "outputformat": "org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat", "serde.param.serialization.format": "1", "serde.serialization.lib": "org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe", "transient_lastDdlTime": "1589255544" } } ], "NextToken": "eyJsYXN0RXZhbHVhdGVkS2V5Ijp7IkhBU0hfS0VZIjp7InMiOiJ0Ljk0YWZjYjk1MjJjNTQ1YmU4Y2I5OWE5NTg0MjFjYTYzIn0sIlJBTkdFX0tFWSI6eyJzIjoiY2xlYXJpbmdob3VzZV9kYXRhIn19LCJleHBpcmF0aW9uIjp7InNlY29uZHMiOjE1ODkzNDIwMjIsIm5hbm9zIjo2NTUwMDAwMDB9fQ==" } ``` ## Running DDL and DML statements When you use the AWS CLI to run DDL and DML statements, you can pass the name of the Hive metastore catalog in one of two ways: + Directly into the statements that support it. + To the `--query-execution-context` `Catalog` parameter. ### DDL statements The following example passes in the catalog name directly as part of the `show create table` DDL statement. The command has been formatted for readability. ``` $ aws athena start-query-execution --query-string "show create table hms-catalog-1.hms_tpch_partitioned.lineitem" --result-configuration "OutputLocation=s3://amzn-s3-demo-bucket/lambda/results" ``` The following example DDL `show create table` statement uses the `Catalog` parameter of `--query-execution-context` to pass the Hive metastore catalog name `hms-catalog-1`. The command has been formatted for readability. ``` $ aws athena start-query-execution --query-string "show create table lineitem" --query-execution-context "Catalog=hms-catalog-1,Database=hms_tpch_partitioned" --result-configuration "OutputLocation=s3://amzn-s3-demo-bucket/lambda/results" ``` ### DML statements The following example DML `select` statement passes the catalog name into the query directly. The command has been formatted for readability. ``` $ aws athena start-query-execution --query-string "select * from hms-catalog-1.hms_tpch_partitioned.customer limit 100" --result-configuration "OutputLocation=s3://amzn-s3-demo-bucket/lambda/results" ``` The following example DML `select` statement uses the `Catalog` parameter of `--query-execution-context` to pass in the Hive metastore catalog name `hms-catalog-1`. The command has been formatted for readability. ``` $ aws athena start-query-execution --query-string "select * from customer limit 100" --query-execution-context "Catalog=hms-catalog-1,Database=hms_tpch_partitioned" --result-configuration "OutputLocation=s3://amzn-s3-demo-bucket/lambda/results" ``` # Modify the Athena external Hive metastore connector If you have special requirements, you can modify the Athena connector for external Hive metastore for your own use. Athena provides a reference implementation of the connector on GitHub.com at [https://github.com/awslabs/aws-athena-hive-metastore](https://github.com/awslabs/aws-athena-hive-metastore). Most use cases do not require you to modify the reference implementation. However, if necessary, you can modify the source code and build the artifacts yourself. The reference implementation is an [Apache Maven](https://maven.apache.org/) project that has the following modules: + `hms-service-api` – Contains the API operations between the Lambda function and the Athena service clients. These API operations are defined in the `HiveMetaStoreService` interface. Because this is a service contract, you should not change anything in this module. + `hms-lambda-handler` – A set of default Lambda handlers that process all Hive metastore API calls. The class `MetadataHandler` is the dispatcher for all API calls. You do not need to change this package. + `hms-lambda-func` – An example Lambda function that has the following components. + `HiveMetaStoreLambdaFunc` – An example Lambda function that extends `MetadataHandler`. + `ThriftHiveMetaStoreClient` – A Thrift client that communicates with Hive metastore. This client is written for Hive 2.3.0. If you use a different Hive version, you might need to update this class to ensure that the response objects are compatible. + `ThriftHiveMetaStoreClientFactory` – Controls the behavior of the Lambda function. For example, you can provide your own set of handler providers by overriding the `getHandlerProvider()` method. + `hms.properties` – Configures the Lambda function. Most cases require updating the following two properties only. + `hive.metastore.uris` – the URI of the Hive metastore in the format `thrift://:9083`. + `hive.metastore.response.spill.location`: The Amazon S3 location to store response objects when their sizes exceed a given threshold (for example, 4 MB). The threshold is defined in the property `hive.metastore.response.spill.threshold`. Changing the default value is not recommended. **Note** These two properties can be overridden by the [Lambda environment variables](https://docs.aws.amazon.com/lambda/latest/dg/env_variables.html) `HMS_URIS` and `SPILL_LOCATION`. Use these variables instead of recompiling the source code for the Lambda function when you want to use the function with a different Hive metastore or spill location. + `hms-lambda-layer` – A Maven assembly project that puts `hms-service-api`, `hms-lambda-handler`, and their dependencies into a `.zip` file. The `.zip` file is registered as a Lambda layer for use by multiple Lambda functions. + `hms-lambda-rnp` – Records the responses from a Lambda function and then uses them to replay the response. You can use this model to simulate Lambda responses for testing. ## Building the artifacts yourself After you modify the source code, you can build the artifacts yourself and upload them to an Amazon S3 location. Before you build the artifacts, update the properties `hive.metastore.uris` and `hive.metastore.response.spill.location` in the `hms.properties` file in the `hms-lambda-func` module. To build the artifacts, you must have Apache Maven installed and run the command `mvn install`. This generates the layer `.zip` file in the output folder called `target` in the module `hms-lambda-layer` and the Lambda function `.jar` file in the module `hms-lambd-func`.