Automate creating AWS Glue Information Catalog views with AWS SDK for knowledge mesh use case

AWS Glue Information Catalog view is a multi-dialect view that helps querying from a number of SQL question engines, similar to Amazon Athena, Amazon Redshift Spectrum, Apache Spark in Amazon EMR and AWS Glue. You possibly can create a Information Catalog view in a single account, utilizing an AWS Id and Entry Administration (IAM) definer function in the identical or completely different account and use AWS Lake Formation to share the view throughout a number of accounts. The definer function has the required full SELECT on the bottom tables to create the view and share it with different customers for querying. The Information Catalog assumes the definer function and manages entry of the bottom tables when the view is queried, thus permitting to share a subset of knowledge with out sharing the underlying base tables.

AWS Glue now provides AWS SDK assist for creating and updating the ATHENA dialect of Glue views. With this addition, now you can create ATHENA and SPARK dialects of Glue views concurrently, utilizing a cross account IAM definer function. This characteristic enhances the automation to create and replace Glue views, like that of Information Catalog tables. In our earlier weblog Create AWS Glue Information Catalog views utilizing cross-account definer roles, we had launched IAM definer roles in a cross-account use case to create Information Catalog views with SPARK dialects utilizing the APIs – CreateTable() and UpdateTable() – whereas creating and including ATHENA dialects utilizing Athena question editor. As a continuation to it, this submit exhibits you easy methods to use the Catalog objects API CreateTable() to programmatically create ATHENA and SPARK dialects utilizing cross-account IAM definer roles, and easy methods to add the ATHENA dialect programmatically for the views that had been created earlier with solely SPARK dialect.

Cross account definer roles allow enterprise knowledge mesh architectures the place a number of accounts are interconnected in a central governance and a number of producers and shoppers. The central governance account hosts the database, tables and permissions, whereas the producer accounts preserve CI/CD pipelines to create and handle these knowledge belongings. Having the definer function in producer accounts permits these CI/CD pipelines to be totally managed by IAM roles within the particular person accounts.

Key factors on creating multi-dialect views utilizing cross-account definer roles

• ATHENA dialects are validated and asynchronously created. Therefore, a cross-account Glue connection is required for validation for each producer account-central governance account pair. It is a one-time setup.
• SPARK dialects should not validated. Therefore SPARK dialect’s create syntax requires SubObjects listing of the bottom tables and StorageDescriptor fields for the columns of the view.
• Although queries on cross account views will be run utilizing database useful resource hyperlink names, the view definition SQL question for creating the view requires the unique database and base desk names from the central governance account.
• If a view has SPARK and ATHENA dialects accessible, we advocate updating each the dialects of the view concurrently utilizing update_table() API/SDK, for any modifications within the SQL definition of the view or the bottom desk. This can maintain each the dialects queryable.
• Creating and updating each SPARK and ATHENA dialects utilizing cross account definer function is supported utilizing AWS CloudFormation.
• The Information Catalog view that may be created utilizing cross account IAM definer roles can be found in SPARK and ATHENA dialects and at present not supported for Redshift Spectrum dialect.

Conditions

We use the identical setup utilized in Create AWS Glue Information Catalog views utilizing cross-account definer roles for the pattern database, tables, definer function, useful resource hyperlink, IAM and Lake Formation permissions on these assets and principals between the 2 AWS accounts. Summarizing the necessities as under.

• The setup features a central governance account with Information Catalog database bankdata_icebergdb and two tables transaction_table1 and transaction_table2, a producer account with a Information-Analyst function used as view definer function.
• Lake Formation permissions on the central account’s database and tables are granted to the producer account Information-Analyst function as per the sooner weblog. The definer function in producer account ought to have database DESCRIBE and CREATE_TABLE permissions, desk SELECT and DESCRIBE permission on all columns and rows of the bottom tables. The IAM permissions required on the definer function are detailed in Conditions for creating views. Equally, comply with the sooner weblog to create useful resource hyperlink for the shared database and grant Lake Formation permissions on the useful resource hyperlink to the Information-Analyst
• An Athena knowledge supply named centraladmin within the producer account, pointing to the Information Catalog of the central governance account.

Creating ATHENA and SPARK dialects on the identical time

Creating each ATHENA and SPARK dialects of a Glue catalog view concurrently is now supported by the AWS SDK. Within the producer account, create a brand new Glue connection, required for the Athena dialect validation. It is a prerequisite for creating the ATHENA dialect of the Glue catalog view utilizing cross account definer function. Then we create a Glue view with each dialects.

1. Check in to the producer account because the Lake Formation admin function, or any function with permission to create AWS Glue connections.
2. Utilizing an AWS Command Line Interface (AWS CLI) surroundings, similar to AWS CloudShell, create an AWS Glue connection as follows.

   aws glue create-connection --cli-input-json file://athena-validation-connection.json

   The content material of athena-validation-connection.json is as follows.

   {
       "CatalogId": "",
       "ConnectionInput": {
           "Title": "glue-view-validation-connection",
           "Description": "Glue view Athena cross-account validation connection",
           "ConnectionType": "VIEW_VALIDATION_ATHENA",
           "ConnectionProperties": {
               "WORKGROUP_NAME": "major",
               "DATA_SOURCE": "centraladmin"
           }
       }
   }

   Be aware: If you’re utilizing Athena for the primary time in your account or utilizing Major workgroup, setup the question outcomes location bucket utilizing Specify a question end result location.

3. Signal out because the Lake Formation admin and signal again in to the producer account because the definer IAM function, Information-Analyst.
4. Create an AWS Glue view utilizing the create-table CLI command and JSON file, or utilizing the AWS SDK for Python (Boto3) script.

   aws glue create-table --cli-input-json file://create_multipledialects.json

   The content material of create_multipledialects.json is as follows.

    {
      "DatabaseName": "rl_bank_iceberg",
      "TableInput": {
        "Title": "view_2dialects_2basetables_fromcli",
        "StorageDescriptor": {
          "Columns": [
            {
              "Name": "transaction_id",
              "Type": "string"
            },
            {
              "Name": "transaction_type",
              "Type": "string"
            },
            {
              "Name": "transaction_amount",
              "Type": "double"
            },
            {
              "Name": "transaction_location",
              "Type": "string"
            },
            {
              "Name": "transaction_date",
              "Type": "date"
            }
        },
        "ViewDefinition": {
          "SubObjects": [
            "arn:aws:glue:us-west-2::table/bankdata_icebergdb/transaction_table1",
            "arn:aws:glue:us-west-2::table/bankdata_icebergdb/transaction_table2"
           ],
          "IsProtected": true,
          "Representations": [
            {
              "Dialect": "SPARK",
              "DialectVersion": "1.0",
              "ViewOriginalText": "SELECT a.transaction_id, a.transaction_type, a.transaction_amount, b.transaction_location, b.transaction_date FROM bankdata_icebergdb.transaction_table1 a RIGHT JOIN bankdata_icebergdb.transaction_table2 b ON a.transaction_id = b.transaction_id",
              "ViewExpandedText": "SELECT a.transaction_id, a.transaction_type, a.transaction_amount, b.transaction_location, b.transaction_date FROM bankdata_icebergdb.transaction_table1 a RIGHT JOIN bankdata_icebergdb.transaction_table2 b ON a.transaction_id = b.transaction_id"
            },
            {
               "Dialect": "ATHENA",
               "DialectVersion": "3",
               "ViewOriginalText": "SELECT a.transaction_id, a.transaction_type, a.transaction_amount, b.transaction_location, b.transaction_date FROM bankdata_icebergdb.transaction_table1 a RIGHT JOIN bankdata_icebergdb.transaction_table2 b ON a.transaction_id = b.transaction_id",
               "ValidationConnection": "glue-view-validation-connection"
            }
          ]
        }
      }
   }

   Notes about fields within the above CLI enter JSON (applies to all SDK):

   • The definer is by default the API caller, however a Definer area will be set to explicitly specify a special IAM function.
   • Within the ViewDefinition, database qualifiers are required for SPARK dialect. That’s, the SQL definition supplied for ViewOriginalText and ViewExpandedText needs to be in . format.
5. After the view is created, you possibly can examine the main points on the Lake Formation console. The SQL definitions present each ATHENA and SPARK as proven within the following screenshot.

In case your view creation fails for any of the dialects, you need to use the AWS Glue get-table CLI command with --include-status-details to see what the error is and rectify it.

aws glue get-table --database-name  --name  --include-status-details

Glue PySpark script

The PySpark script for making a view with ATHENA and SPARK dialects are supplied under. Obtain and edit the Pyspark script together with your bucket title, producer and central account ids, area and related Glue useful resource names: bdb_5773_createview_bothdialects.py

Present the next settings to run the script in your Glue Studio. For particulars on operating a Spark job in Glue, refer Working with Spark jobs in AWS Glue.

• Select Information-Analyst because the job execution IAM function.
• Select Glue 5.1 for Glue model.
• For the Requested variety of employees, present >=4. That is an FGAC Spark driver requirement, which is required for Glue catalog views. Under screenshot exhibits these settings.
  
  
• Add the next 2 properties as extra job parameters. A screenshot is proven for reference.

  --datalake-formats = iceberg
  --enable-lakeformation-fine-grained-access=true

  

• Save and run the Glue job. Test the stdout logs to evaluation the question on the newly created view.

A pattern update_table script can be supplied under, for instance altering the view definition with extra columns. Be aware the REPLACE key phrase:

bdb_5773_updateview_bothdialects.py

Including ATHENA dialect utilizing SDK to an current AWS Glue view

You possibly can replace an current AWS Glue view that was created with the SPARK dialect and add the ATHENA dialect utilizing the SDK. The next instance makes use of the update-table CLI command.

aws glue update-table --cli-input-json file://add-athena-dialect.json

The content material of add-athena-dialect.json is as follows.

{
    "DatabaseName": "rl_bank_iceberg",
    "ViewUpdateAction": "ADD",
    "TableInput": {
        "Title": "view_sparkfirst_athenanext",
        "ViewDefinition": {
            "Representations": [
                {
                    "Dialect": "ATHENA",
                    "DialectVersion": "3",
                    "ViewOriginalText": "SELECT a.transaction_id, a.transaction_type, a.transaction_amount, b.transaction_location, b.transaction_date FROM bankdata_icebergdb.transaction_table1 a RIGHT JOIN bankdata_icebergdb.transaction_table2 b ON a.transaction_id = b.transaction_id",
                    "ValidationConnection": "glue-view-validation-connection"
                }
            ]
        }
    }
}

Confirm the added dialect on the view by reviewing the SQL definitions of the view in Lake Formation console or utilizing GetTable(). If you wish to edit the SQL definition or change the bottom tables of an current view that has each SPARK and ATHENA dialects, you are able to do so utilizing the update_table API (utilizing SDK or CLI), with "ViewUpdateAction": “REPLACE” and supply each the dialect definition below ViewDefinition.

You possibly can run queries on the view from the producer account as Information-Analyst. The view will be shared utilizing Lake Formation Tags or named methodology, identical to sharing tables, to extra client accounts from the central governance account. The buyer accounts will create a useful resource hyperlink and question the views.

Cleanup

To keep away from incurring ongoing prices, clear up the assets you used for this submit:

1. Revoke the Lake Formation permissions granted to the Information-Analyst function and the producer account from the central governance account.
2. Drop the Information Catalog tables, views, and the database.
3. Delete the Athena question outcomes out of your Amazon Easy Storage Service (Amazon S3) bucket.
4. Delete the Information-Analyst function from IAM.
5. Delete the AWS Glue connection and the Athena knowledge supply.
6. Delete the AWS Glue job, if you happen to tried the Python script as an AWS Glue job.

Conclusion

On this submit, I demonstrated easy methods to use cross-account IAM definer roles with AWS Glue Information Catalog views, easy methods to create and replace ATHENA and SPARK dialects utilizing the Information Catalog CreateTable() and UpdateTable() APIs. The multi-dialect Information Catalog views enable sharing a subset of knowledge from completely different tables utilizing Lake Formation permissions, together with LF-Tags primarily based entry management. The cross-account definer roles assist multi-account knowledge mesh architectures in order that the producer IAM roles can run the CI/CD pipelines in its account. We encourage you to attempt the characteristic and share your suggestions within the feedback.

Acknowledgements: I wish to thank all of the workforce members who labored so as to add AWS SDK assist for creating ATHENA and SPARK dialects collectively for AWS Glue views – Daniil Arushanov, Wyatt Hawes, Yuxi Wu, Santhosh Padmanabhan and Karthik Devaraj.

In regards to the writer