Question your Apache Hive metastore with AWS Lake Formation permissions

Apache Hive is a SQL-based information warehouse system for processing extremely distributed datasets on the Apache Hadoop platform. There are two key parts to Apache Hive: the Hive SQL question engine and the Hive metastore (HMS). The Hive metastore is a repository of metadata in regards to the SQL tables, akin to database names, desk names, schema, serialization and deserialization info, information location, and partition particulars of every desk. Apache Hive, Apache Spark, Presto, and Trino can all use a Hive Metastore to retrieve metadata to run queries. The Hive metastore may be hosted on an Apache Hadoop cluster or may be backed by a relational database that’s exterior to a Hadoop cluster. Though the Hive metastore shops the metadata of tables, the precise information of the desk could possibly be residing on Amazon Easy Storage Service (Amazon S3), the Hadoop Distributed File System (HDFS) of the Hadoop cluster, or some other Hive-supported information shops.

As a result of Apache Hive was constructed on high of Apache Hadoop, many organizations have been utilizing the software program from the time they’ve been utilizing Hadoop for large information processing. Additionally, Hive metastore gives versatile integration with many different open-source massive information software program like Apache HBase, Apache Spark, Presto, and Apache Impala. Due to this fact, organizations have come to host enormous volumes of metadata of their structured datasets within the Hive metastore. A metastore is a important a part of an information lake, and having this info out there, wherever it resides, is vital. Nonetheless, many AWS analytics providers don’t combine natively with the Hive metastore, and due to this fact, organizations have needed to migrate their information to the AWS Glue Knowledge Catalog to make use of these providers.

AWS Lake Formation has launched assist for managing consumer entry to Apache Hive metastores by a federated AWS Glue connection. Beforehand, you might use Lake Formation to handle consumer permissions on AWS Glue Knowledge Catalog sources solely. With the Hive metastore connection from AWS Glue, you’ll be able to connect with a database in a Hive metastore exterior to the Knowledge Catalog, map it to a federated database within the Knowledge Catalog, apply Lake Formation permissions on the Hive database and tables, share them with different AWS accounts, and question them utilizing providers akin to Amazon Athena, Amazon Redshift Spectrum, Amazon EMR, and AWS Glue ETL (extract, rework, and cargo). For added particulars on how the Hive metastore integration with Lake Formation works, check with Managing permissions on datasets that use exterior metastores.

Use instances for Hive metastore integration with the Knowledge Catalog embody the next:

• An exterior Apache Hive metastore used for legacy massive information workloads like on-premises Hadoop clusters with information in Amazon S3
• Transient Amazon EMR workloads with underlying information in Amazon S3 and the Hive metastore on Amazon Relational Database Service (Amazon RDS) clusters.

On this put up, we exhibit find out how to apply Lake Formation permissions on a Hive metastore database and tables and question them utilizing Athena. We illustrate a cross-account sharing use case, the place a Lake Formation steward in producer account A shares a federated Hive database and tables utilizing LF-Tags to shopper account B.

Resolution overview

Producer account A hosts an Apache Hive metastore in an EMR cluster, with underlying information in Amazon S3. We launch the AWS Glue Hive metastore connector from AWS Serverless Utility Repository in account A and create the Hive metastore connection in account A’s Knowledge Catalog. After we create the HMS connection, we create a database in account A’s Knowledge Catalog (known as the federated database) and map it to a database within the Hive metastore utilizing the connection. The tables from the Hive database are then accessible to the Lake Formation admin in account A, similar to some other tables within the Knowledge Catalog. The admin continues to arrange Lake Formation tag-based entry management (LF-TBAC) on the federated Hive database and share it to account B.

The info lake customers in account B will entry the Hive database and tables of account A, similar to querying some other shared Knowledge Catalog useful resource utilizing Lake Formation permissions.

The next diagram illustrates this structure.

The answer consists of steps in each accounts. In account A, carry out the next steps:

1. Create an S3 bucket to host the pattern information.
2. Launch an EMR 6.10 cluster with Hive. Obtain the pattern information to the S3 bucket. Create a database and exterior tables, pointing to the downloaded pattern information, in its Hive metastore.
3. Deploy the applying GlueDataCatalogFederation-HiveMetastore from AWS Serverless Utility Repository and configure it to make use of the Amazon EMR Hive metastore. This can create an AWS Glue connection to the Hive metastore that exhibits up on the Lake Formation console.
4. Utilizing the Hive metastore connection, create a federated database within the AWS Glue Knowledge Catalog.
5. Create LF-Tags and affiliate them to the federated database.
6. Grant permissions on the LF-Tags to account B. Grant database and desk permissions to account B utilizing LF-Tag expressions.

In account B, carry out the next steps:

1. As an information lake admin, assessment and settle for the AWS Useful resource Entry Supervisor (AWS RAM) invitations for the shares from account A.
2. The info lake admin then sees the shared database and tables. The admin creates a useful resource hyperlink to the database and grants fine-grained permissions to an information analyst on this account.
3. Each the info lake admin and the info analyst question the Hive tables which are out there to them utilizing Athena.

Account A has the next personas:

• hmsblog-producersteward – Manages the info lake within the producer account A

Account B has the next personas:

• hmsblog-consumersteward – Manages the info lake within the shopper account B
• hmsblog-analyst – An information analyst who wants entry to chose Hive tables

Stipulations

To observe the tutorial on this put up, you want the next:

Lake Formation and AWS CloudFormation setup in account A

To maintain the setup easy, now we have an IAM admin registered as the info lake admin. Full the next steps:

1. Signal into the AWS Administration Console and select the us-west-2 Area.
2. On the Lake Formation console, below Permissions within the navigation pane, select Administrative roles and duties.
3. Select Handle Directors within the Knowledge lake directors part.
4. Beneath IAM customers and roles, select the IAM admin consumer that you’re logged in as and select Save.
5. Select Launch Stack to deploy the CloudFormation template:
   
6. Select Subsequent.
7. Present a reputation for the stack and select Subsequent.
8. On the following web page, select Subsequent.
9. Overview the small print on the ultimate web page and choose I acknowledge that AWS CloudFormation may create IAM sources.
10. Select Create.

Stack creation takes about 10 minutes. The stack establishes the producer account A setup as follows:

• Creates an S3 information lake bucket
• Registers the info lake bucket to Lake Formation with the Allow catalog federation flag
• Launches an EMR 6.10 cluster with Hive and runs two steps in Amazon EMR:
  • Downloads the pattern information from public S3 bucket to the newly created bucket
  • Creates a Hive database and 4 exterior tables for the info in Amazon S3, utilizing a HQL script
• Creates an IAM consumer (hmsblog-producersteward) and units this consumer as Lake Formation administrator
• Creates LF-Tags (LFHiveBlogCampaignRole = Admin, Analyst)

Overview CloudFormation stack output in account A

To assessment the output of your CloudFormation stack, full the next steps:

1. Log in to the console because the IAM admin consumer you used earlier to run the CloudFormation template.
2. Open the CloudFormation console in one other browser tab.
3. Overview and notice down the stack Outputs tab particulars.
   
4. Select the hyperlink below Worth for ProducerStewardCredentials.

This can open the AWS Secrets and techniques Supervisor console.

5. Select Retrieve worth and notice down the credentials of hmsblog-producersteward.
   
   

Arrange a federated AWS Glue connection in account A

To arrange a federated AWS Glue connection, full the next steps:

1. Open the AWS Serverless Utility Repository console in one other browser tab.
2. Within the navigation pane, select Accessible purposes.
3. Choose Present apps that create customized IAM roles or useful resource insurance policies.
4. Within the search bar, enter Glue.

This can listing numerous purposes.

5. Select the applying named GlueDataCatalogFederation-HiveMetastore.

This can open the AWS Lambda console configuration web page for a Lambda operate that runs the connector software code.

To configure the Lambda operate, you want particulars of the EMR cluster launched by the CloudFormation stack.

6. On one other tab of your browser, open the Amazon EMR console.
7. Navigate to the cluster launched for this put up and notice down the next particulars from the cluster particulars web page:
   1. Major node public DNS
   2. Subnet ID
   3. Safety group ID of the first node
      
      
8. Again on the Lambda configuration web page, below Overview, configure, and deploy, within the Utility settings part, present the next particulars. Depart the remainder because the default values.
   1. For GlueConnectionName, enter hive-metastore-connection.
   2. For HiveMetastoreURIs enter thrift://<Major-node-public-DNS-of your-EMR>:9083. For instance, thrift://ec2-54-70-203-146.us-west-2.compute.amazonaws.com:9083, the place 9083 is the Hive metastore port in EMR cluster.
   3. For VPCSecurityGroupIds, enter the safety group ID of the EMR major node.
   4. For VPCSubnetIds, enter the subnet ID of the EMR cluster.
9. Select Deploy.
   

Anticipate the Create Accomplished standing of the Lambda software. You may assessment the small print of the Lambda software on the Lambda console.

10. Open Lake Formation console and within the navigation pane, select Knowledge sharing.

It’s best to see hive-metastore-connection below Connections.

11. Select it and assessment the small print.
    
12. Within the navigation pane, below Administrative roles and duties, select LF-Tags.

It’s best to see the created LF-tag LFHiveBlogCampaignRole with two values: Analyst and Admin.

13. Select LF-Tag permissions and select Grant.
14. Select IAM customers and roles and enter hmsblog-producersteward.
15. Beneath LF-Tags, select Add LF-Tag.
16. Enter LFHiveBlogCampaignRole for Key and enter Analyst and Admin for Values.
17. Beneath Permissions, choose Describe and Affiliate for LF-Tag permissions and Grantable permissions.
18. Select Grant.
    

This provides LF-Tags permissions for the producer steward.

19. Log off because the IAM administrator consumer.

Grant Lake Formation permissions as producer steward

Full the next steps:

1. Check in to the console as hmsblog-producersteward, utilizing the credentials from the CloudFormation stack Output tab that you just famous down earlier.
2. On the Lake Formation console, within the navigation pane, select Administrative roles and duties.
3. Beneath Database creators, select Grant.
   
4. Add hmsblog-producersteward as a database creator.
   
5. Within the navigation pane, select Knowledge sharing.
6. Beneath Connections, select the hive-metastore-connection hyperlink.
   
7. On the Connection particulars web page, select Create database.
8. For Database identify, enter federated_emrhivedb.

That is the federated database within the native AWS Glue Knowledge Catalog that may level to a Hive metastore database. This can be a one-to-one mapping of a database within the Knowledge Catalog to a database within the exterior Hive metastore.

9. For Database identifier, enter the identify of the database within the EMR Hive metastore that was created by the Hive SQL script. For this put up, we use emrhms_salesdb.
   
10. As soon as created, choose federated_emrhivedb and select View tables.

This can fetch the database and desk metadata from the Hive metastore on the EMR cluster and show the tables created by the Hive script.

Now you affiliate the LF-Tags created by the CloudFormation script on this federated database and share it to the patron account B utilizing LF-Tag expressions.

11. Within the navigation pane, select Databases.
12. Choose federated_emrhivedb and on the Actions menu, select Edit LF-Tags.
13. Select Assign new LF-Tag.
14. Enter LFHiveBlogCampaignRole for Assigned keys and Admin for Values, then select Save.
    
15. Within the navigation pane, select Knowledge lake permissions.
16. Select Grant.
17. Choose Exterior accounts and enter the patron account B quantity.
18. Beneath LF-Tags or catalog sources, select Useful resource matched by LF-Tags.
19. Select Add LF-Tag.
20. Enter LFHiveBlogCampaignRole for Key and Admin for Values.
    
21. Within the Database permissions part, choose Describe for Database permissions and Grantable permissions.
22. Within the Desk permissions part, choose Choose and Describe for Desk permissions and Grantable permissions.
23. Select Grant.
    
24. Within the navigation pane, below Administrative roles and duties, select LF-Tag permissions.
25. Select Grant.
26. Choose Exterior accounts and enter the account ID of shopper account B.
27. Beneath LF-Tags, enter LFHiveBlogCampaignRole for Key and enter Analyst and Admin for Values.
28. Beneath Permissions, choose Describe and Affiliate below LF-Tag permissions and Grantable permissions.
29. Select Grant and confirm that the granted LF-Tag permissions show appropriately.
    
30. Within the navigation pane, select Knowledge lake permissions.

You may assessment and confirm the permissions granted to account B.

31. Within the navigation pane, below Administrative roles and duties, select LF-Tag permissions.

You may assessment and confirm the permissions granted to account B.

32. Log off of account A.

Lake Formation and AWS CloudFormation setup in account B

To maintain the setup easy, we use an IAM admin registered as the info lake admin.

1. Signal into the AWS Administration Console of account B and choose the us-west-2 Area.
2. On the Lake Formation console, below Permissions within the navigation pane, select Administrative roles and duties.
3. Select Handle Directors within the Knowledge lake directors part.
4. Beneath IAM customers and roles, select the IAM admin consumer that you’re logged in as and select Save.
5. Select Launch Stack to deploy the CloudFormation template:
   
6. Select Subsequent.
7. Present a reputation for the stack and select Subsequent.
8. On the following web page, select Subsequent.
9. Overview the small print on the ultimate web page and choose I acknowledge that AWS CloudFormation may create IAM sources.
10. Select Create.

Stack creation ought to take about 5 minutes. The stack establishes the producer account B setup as follows:

• Creates an IAM consumer hmsblog-consumersteward and units this consumer as Lake Formation administrator
• Creates one other IAM consumer hmsblog-analyst
• Creates an S3 information lake bucket to retailer Athena question outcomes, with ListBucket and write object permissions to each hmsblog-consumersteward and hmsblog-analyst

Notice down the stack output particulars.

Settle for useful resource shares in account B

Check in to the console as hmsblog-consumersteward and full the next steps:

1. On the AWS CloudFormation console, navigate to the stack Outputs tab.
2. Select the hyperlink for ConsumerStewardCredentials to be redirected to the Secrets and techniques Supervisor console.
3. On the Secrets and techniques Supervisor console, select Retrieve secret worth and replica the password for the patron steward consumer.
4. Use the ConsoleIAMLoginURL worth from the CloudFormation template Output to log in to account B with the patron steward consumer identify hmsblog-consumersteward and the password you copied from Secrets and techniques Supervisor.
5. Open the AWS RAM console in one other browser tab.
6. Within the navigation pane, below Shared with me, select Useful resource shares to view the pending invites.

It’s best to see two useful resource share invites from producer account A: one for a database-level share and one for a table-level share.

7. Select every useful resource share hyperlink, assessment the small print, and select Settle for.
   

After you settle for the invites, the standing of the useful resource shares adjustments from Pending to Lively.

8. Open the Lake Formation console in one other browser tab.
9. Within the navigation pane, select Databases.

It’s best to see the shared database federated_emrhivedb from producer account A.

10. Select the database and select View tables to assessment the listing of tables shared below that database.
    

It’s best to see the 4 tables of the Hive database that’s hosted on the EMR cluster within the producer account.

Grant permissions in account B

To grant permissions in account B, full the next steps as hmsblog-consumersteward:

1. On the Lake Formation console, within the navigation pane, select Administrative roles and duties.
   
2. Beneath Database creators, select Grant.
3. For IAM customers and roles, enter hmsblog-consumersteward.
4. For Catalog permissions, choose Create database.
5. Select Grant.
   

This enables hmsblog-consumersteward to create a database useful resource hyperlink.

6. Within the navigation pane, select Databases.
7. Choose federated_emrhivedb and on the Actions menu, select Create useful resource hyperlink.
   
8. Enter rl_federatedhivedb for Useful resource hyperlink identify and select Create.
   
9. Select Databases within the navigation pane.
10. Choose the useful resource hyperlink rl_federatedhivedb and on the Actions menu, select Grant.
11. Select hmsblog-analyst for IAM customers and roles.
    
12. Beneath Useful resource hyperlink permissions, choose Describe, then select Grant.
    
13. Choose Databases within the navigation pane.
14. Choose the useful resource hyperlink rl_federatedhivedb and on the Actions menu, select Grant on the right track.
15. Select hmsblog-analyst for IAM customers and roles.
16. Select hms_productcategory and hms_supplier for Tables.
    
17. For Desk permissions, choose Choose and Describe, then select Grant.
    
18. Within the navigation pane, select Knowledge lake permissions and assessment the permissions granted to hms-analyst.
    

Question the Apache Hive database of the producer from the patron Athena

Full the next steps:

1. On the Athena console, navigate to the question editor.
2. Select Edit settings to configure the Athena question outcomes bucked.
   
3. Browse and select the S3 bucket hmsblog-athenaresults-<your-account-B>-us-west-2 that the CloudFormation template created.
4. Select Save.
   

hmsblog-consumersteward has entry to all 4 tables below federated_emrhivedb from the producer account.

5. Within the Athena question editor, select the database rl_federatedhivedb and run a question on any of the tables.
   

You have been in a position to question an exterior Apache Hive metastore database of the producer account by the AWS Glue Knowledge Catalog and Lake Formation permissions utilizing Athena from the recipient shopper account.

6. Signal out of the console as hmsblog-consumersteward and signal again in as hmsblog-analyst.
7. Use the identical methodology as defined earlier to get the login credentials from the CloudFormation stack Outputs tab.

hmsblog-analyst has Describe permissions on the useful resource hyperlink and entry to 2 of the 4 Hive tables. You may confirm that you just see them on the Databases and Tables pages on the Lake Formation console.

On the Athena console, you now configure the Athena question outcomes bucket, just like the way you configured it as hmsblog-consumersteward.

8. Within the question editor, select Edit settings.
9. Browse and select the S3 bucket hmsblog-athenaresults-<your-account-B>-us-west-2 that the CloudFormation template created.
10. Select Save.
11. Within the Athena question editor, select the database rl_federatedhivedb and run a question on the 2 tables.
    
12. Signal out of the console as hmsblog-analyst.

You have been in a position to limit sharing the exterior Apache Hive metastore tables utilizing Lake Formation permissions from one account to a different and question them utilizing Athena. You too can question the Hive tables utilizing Redshift Spectrum, Amazon EMR, and AWS Glue ETL from the patron account.

Clear up

To keep away from incurring prices on the AWS sources created on this put up, you’ll be able to carry out the next steps.

Clear up sources in account A

There are two CloudFormation stacks related to producer account A. It is advisable delete the dependencies and the 2 stacks within the appropriate order.

1. Log in because the admin consumer to producer account B.
2. On the Lake Formation console, select Knowledge lake permissions within the navigation pane.
3. Select Grant.
4. Grant Drop permissions to your function or consumer on federated_emrhivedb.
   
5. Within the navigation pane, select Databases.
6. Choose federated_emrhivedb and on the Actions menu, select Delete to delete the federated database that’s related to the Hive metastore connection.

This makes the AWS Glue connection’s CloudFormation stack able to be deleted.

7. Within the navigation pane, select Administrative roles and duties.
8. Beneath Database creators, choose Revoke and take away hmsblog-producersteward permissions.
9. On the CloudFormation console, delete the stack named serverlessrepo-GlueDataCatalogFederation-HiveMetastore first.

That is the one created by your AWS SAM software for the Hive metastore connection. Anticipate it to finish deletion.

10. Delete the CloudFormation stack that you just created for the producer account arrange.

This deletes the S3 buckets, EMR cluster, customized IAM roles and insurance policies, and the LF-Tags, database, tables, and permissions.

Clear up sources in account B

Full the next steps in account B:

1. Revoke permission to hmsblog-consumersteward as database creator, just like the steps within the earlier part.
2. Delete the CloudFormation stack that you just created for the patron account setup.

This deletes the IAM customers, S3 bucket, and all of the permissions from Lake Formation.

If there are any useful resource hyperlinks and permissions left, delete them manually in Lake Formation from each accounts.

Conclusion

On this put up, we confirmed you find out how to launch the AWS Glue Hive metastore federation software from AWS Serverless Utility Repository, configure it with a Hive metastore operating on an EMR cluster, create a federated database within the AWS Glue Knowledge Catalog, and map it to a Hive metastore database on the EMR cluster. We illustrated find out how to share and entry the Hive database tables for a cross-account situation and the advantages of utilizing Lake Formation to limit permissions.

All Lake Formation options akin to sharing to IAM principals inside similar account, sharing to exterior accounts, sharing to exterior account IAM principals, limiting column entry, and setting information filters work on federated Hive database and tables. You need to use any of the AWS analytics providers which are built-in with Lake Formation, akin to Athena, Redshift Spectrum, AWS Glue ETL, and Amazon EMR to question the federated Hive database and tables.

We encourage you to take a look at the options of the AWS Glue Hive metastore federation connector and discover Lake Formation permissions in your Hive database and tables. Please touch upon this put up or discuss to your AWS Account Crew to share suggestions on this function.

For extra particulars, see Managing permissions on datasets that use exterior metastores.

Concerning the authors

Aarthi Srinivasan is a Senior Large Knowledge Architect with AWS Lake Formation. She likes constructing information lake options for AWS prospects and companions. When not on the keyboard, she explores the most recent science and know-how tendencies and spends time together with her household.