Create AWS Glue Knowledge Catalog views utilizing cross-account definer roles

With AWS Glue Knowledge Catalog views you’ll be able to create a SQL view within the Knowledge Catalog that references a number of base tables. These multi-dialect views help numerous SQL question engines, offering constant entry throughout a number of Amazon Internet Companies (AWS) companies together with Amazon Athena, Amazon Redshift Spectrum, and Apache Spark in each Amazon EMR and AWS Glue 5.0.

Now you can create Knowledge Catalog views utilizing a cross-account AWS Id and Entry Administration (IAM) definer function. A definer function is an IAM function used to create the Knowledge Catalog view and has SELECT permissions on all columns of the underlying base tables. This definer function is assumed by AWS Glue and AWS Lake Formation service principals to vend credentials to the bottom tables’ knowledge every time the view is queried. The definer function permits the Knowledge Catalog view to be shared to principals or AWS accounts so that you could share a filtered subset of knowledge with out sharing the bottom tables.

Beforehand, Knowledge Catalog views required a definer function inside the identical AWS account as the bottom tables. The introduction of cross-account definer roles permits Knowledge Catalog view creation in enterprise knowledge mesh architectures. On this setup, database and desk metadata is centralized in a governance account, and particular person knowledge proprietor accounts keep management over desk creation and administration by their IAM roles. Knowledge proprietor accounts can now create and handle Knowledge Catalog views within the central governance accounts utilizing their current steady integration and steady supply (CI/CD) pipeline roles.

On this submit, we present you a cross-account state of affairs involving two AWS accounts: a central governance account containing the tables and internet hosting the views and an information proprietor (producer) account with the IAM function used to create and handle views. We offer implementation particulars for each SPARK dialect utilizing AWS SDK code samples and ATHENA dialect utilizing SQL instructions. Utilizing this method, you’ll be able to implement refined knowledge governance fashions at enterprise scale whereas sustaining operational effectivity throughout your AWS setting.

Key advantages

Key advantages for cross-account definer roles are as follows:

• Enhanced knowledge mesh help – Enterprises with multi-account knowledge lakehouse architectures can now keep their current operational mannequin the place knowledge proprietor accounts handle desk creation and updates utilizing their established IAM roles. These identical roles can now create and handle Knowledge Catalog views throughout account boundaries.
• Strengthened safety controls – By holding desk and examine administration inside knowledge proprietor account roles:
  • Safety posture is enhanced by correct separation of duties.
  • Audit trails turn out to be extra complete and significant.
  • Entry controls comply with the precept of least privilege.
• Elimination of knowledge duplication – Knowledge proprietor accounts can create views in central accounts that:
  • Present entry to particular knowledge subsets with out duplicating tables.
  • Scale back storage prices and administration overhead.
  • Keep a single supply of reality whereas enabling focused knowledge sharing.

Answer overview

An instance buyer has a database with two transaction tables of their central account, the place the catalog and permissions are maintained. With the database shared to the information proprietor (producer) account, we create a Knowledge Catalog view within the central account on these two tables, utilizing the producer’s definer function. The view from the central account might be shared to extra shopper accounts and queried. We illustrate creating the SPARK dialect utilizing create-table CLI, and add the ATHENA dialect for a similar view from the Athena console. We additionally present the AWS SDK pattern code for CreateTable() and UpdateTable(), with view definition and a pattern pySpark script to learn and confirm the view in AWS Glue.

The next diagram reveals the desk, view, and definer IAM function placements between a central governance account and knowledge producer account.

Stipulations

To carry out this answer, you have to have the next conditions:

1. Two AWS accounts with AWS Lake Formation arrange. For particulars, seek advice from Arrange AWS Lake Formation. The Lake Formation setup consists of registering your IAM admin function as Lake Formation administrator. Within the Knowledge Catalog settings, proven within the following screenshot, Default permissions for newly created databases and tables is ready to make use of Lake Formation permissions solely. Cross-account model settings is ready to Model 4.

2. Create an IAM function Knowledge-Analyst within the producer account. For the IAM permissions on this function, seek advice from Knowledge analyst permissions. This function will even be used because the view definer function. Add the permissions to this definer function from the Stipulations for creating views.

Create database and tables within the central account

On this step, you create two tables within the central governance account and populate them with few rows of knowledge:

1. Check in to the central account as admin consumer. Open the Athena console and arrange the Athena question outcomes bucket.
2. Run the next queries to create two pattern Iceberg tables, representing financial institution buyer transactions knowledge:

/* Verify if the Database exists, if not create new database. */
CREATE DATABASE IF NOT EXISTS bankdata_icebergdb;

/*Create transaction_table1*/ Substitute the bucket identify
CREATE TABLE bankdata_icebergdb.transaction_table1 (
  transaction_id string,
  transaction_type string,
  transaction_amount double)
LOCATION 's3:///bankdata_icebergdb/transaction-table1'
TBLPROPERTIES (
  'table_type'='iceberg',
  'write_compression'='zstd'
);

/*Create transaction_table2*/
CREATE TABLE bankdata_icebergdb.transaction_table2 (
  transaction_id string,
  transaction_location string,
  transaction_date date)
LOCATION 's3:///bankdata_icebergdb/transaction-table2'
TBLPROPERTIES (
  'table_type'='iceberg',
  'write_compression'='zstd'
);


INSERT INTO bankdata_icebergdb.transaction_table1 (transaction_id, transaction_type, transaction_amount)
VALUES
  ('T001', 'buy', 50.0),
  ('T002', 'buy', 120.0),
  ('T003', 'refund', 200.5),
  ('T004', 'buy', 80.0),
  ('T005', 'withdrawal', 500.0),
  ('T006', 'buy', 300.0),
  ('T007', 'deposit', 1000.0),
  ('T008', 'refund', 20.0),
  ('T009', 'buy', 150.0),
  ('T010', 'withdrawal', 75.0);


INSERT INTO bankdata_icebergdb.transaction_table2 (transaction_id, transaction_location, transaction_date)
VALUES
  ('T001', 'Charlotte', DATE '2024-10-01'),
  ('T002', 'Seattle', DATE '2024-10-02'),
  ('T003', 'Chicago', DATE '2024-10-03'),
  ('T004', 'Miami', DATE '2024-10-04'),
  ('T005', 'New York', DATE '2024-10-05'),
  ('T006', 'Austin', DATE '2024-10-06'),
  ('T007', 'Denver', DATE '2024-10-07'),
  ('T008', 'Boston', DATE '2024-10-08'),
  ('T009', 'San Jose', DATE '2024-10-09'),
  ('T010', 'Phoenix', DATE '2024-10-10');

3. Confirm the created tables in Athena question editor by working a preview.

Share the database and tables from central to producer account

Within the central governance account, you share the database and the 2 tables to the producer account and the Knowledge-Analyst function in producer.

1. Check in to the Lake Formation console because the Lake Formation admin function.
2. Within the navigation pane, select Knowledge permissions.
3. Select Grant and supply the next info:
   1. For Principals, choose Exterior accounts and enter the producer account ID, as proven within the following screenshot.
      
      
   2. For Named Knowledge Catalog Sources, choose the default catalog and database bankdata_icebergdb, as proven within the following screenshot.
      
      
   3. Below Database permissions, choose Describe. For Grantable permissions, choose Describe.
      
      
   4. Select Grant.
   5. Repeat the previous steps to grant entry to the producer account definer function Knowledge-Analyst on the database bankdata_icebergdb and the 2 tables transaction_table1 and transaction_table2 as follows.
   6. Below Database permissions, grant Create desk and Describe permissions.
      
      
   7. Below Desk permissions, grant Choose and Describe on all columns.
      
      

With these steps, the central governance account knowledge admin steward has shared the database and tables to the producer account definer function.

Steps for producer account

Observe these steps for the producer account:

1. Check in to the Lake Formation console on the producer account because the Lake Formation administrator.
2. Within the left navigation pane, select Databases. A blue banner will seem on the console, exhibiting pending invites from AWS Useful resource Entry Supervisor (AWS RAM).
3. Open the AWS RAM console and evaluation the AWS RAM shares underneath Shared with me. You will notice the AWS RAM shares in pending state. Choose the pending AWS RAM share from central account and select Settle for useful resource share. After the useful resource share request is accepted, the shared database reveals up within the producer account.
4. On the Lake Formation console, choose the database. On the Create dropdown record, select Useful resource hyperlink. Present a reputation rl_bank_iceberg and select Create.
5. Let’s grant Describe permission on the useful resource hyperlink to the Knowledge-Analyst function within the producer account within the following steps.
   1. Within the left navigation pane, select Knowledge permissions. Select the Knowledge-Analyst function. Choose the useful resource hyperlink rl_bank_iceberg for the database as proven within the following screenshot.
      
      
   2. Grant Describe permission on the useful resource hyperlink.
      
      

Be aware: Cross-account Knowledge Catalog views can’t be created utilizing a useful resource hyperlink, though a useful resource hyperlink is required for the SDK use of SPARK dialect.

Subsequent, add the central account Knowledge Catalog as a Knowledge Supply in Athena from producer account:

1. Open the Athena console.
2. On the left navigation pane, select Knowledge sources and catalogs. Select Create knowledge supply.
   1. Choose S3-AWS Glue Knowledge Catalog.
   2. Select AWS – Glue Knowledge Catalog in one other account and identify the information supply as centraladmin.
   3. Select Subsequent after which create knowledge supply.

After the information supply is created, navigate to the Question editor and confirm the Knowledge supply centraladmin seems, as proven within the following screenshot.

The definer function also can now entry and question the central catalog database.

Create SPARK dialect view

On this step, you create a view with SPARK dialect, utilizing AWS Glue CLI command create-table:

1. Check in to the AWS console within the producer account as Knowledge-Analyst function. Enter the next command in your CLI setting, equivalent to AWS CloudShell, to create a SPARK DIALECT:

aws glue create-table --cli-input-json '{
   "DatabaseName": "rl_bank_iceberg",
   "TableInput": {
     "Title": "mdv_transaction1",
     "StorageDescriptor": {
       "Columns": [
         {
           "Name": "transaction_id",
           "Type": "string"
         },
         {
           "Name": "transaction_type",
           "Type": "string"
         },
         {
           "Name": "transaction_amount",
           "Type": "float"
         },
         {
           "Name": "transaction_location",
           "Type": "string"
         },
         {
           "Name": "transaction_date",
           "Type": "date"
         }
       ],
       "SerdeInfo": {}
     },
     "ViewDefinition": {
       "SubObjects": [
         "arn:aws:glue:::table/bankdata_icebergdb/transaction_table1",
         "arn:aws:glue:::table/bankdata_icebergdb/transaction_table2"
        ],
       "IsProtected": true,
       "Representations": [
         {
           "Dialect": "SPARK",
           "DialectVersion": "1.0",
           "ViewOriginalText": "SELECT t1.transaction_id, t1.transaction_type, t1.transaction_amount, t2.transaction_location, t2.transaction_date FROM transaction_table1 t1 JOIN transaction_table2 t2 ON t1.transaction_id = t2.transaction_id WHERE t1.transaction_amount > 100;",
           "ViewExpandedText": "SELECT t1.transaction_id, t1.transaction_type, t1.transaction_amount, t2.transaction_location, t2.transaction_date FROM transaction_table1 t1 JOIN transaction_table2 t2 ON t1.transaction_id = t2.transaction_id WHERE t1.transaction_amount > 100;"
         }
       ]
     }
   }
 }'

2. Open the Lake Formation console and confirm if the view is created. Confirm the dialect of the view on the SQL definitions tab for the view particulars.

Add ATHENA dialect

So as to add ATHENA dialect, comply with these steps:

1. On the Athena console, choose centraladmin from the Knowledge supply.
2. Enter the next SQL script to create the ATHENA dialect for a similar view:

ALTER VIEW mdv_transaction1 FORCE ADD DIALECT
AS
SELECT t1.transaction_id, t1.transaction_type, t1.transaction_amount, t2.transaction_location, t2.transaction_date FROM transaction_table1 t1 JOIN transaction_table2 t2 ON t1.transaction_id = t2.transaction_id WHERE t1.transaction_amount > 100

We will’t use the useful resource hyperlink rl_bank_iceberg within the Athena question editor to create or alter a view within the central account.

3. Confirm the added dialect by working a preview in Athena. For working the question, you should utilize both the useful resource hyperlink rl_bank_iceberg from the producer account catalog or use the centraladmin catalog.

The next screenshot reveals querying utilizing the useful resource hyperlink of the database within the producer account catalog.

The next screenshot reveals querying the view from the producer utilizing the related catalog centraladmin as the information supply.

4. Confirm the dialects on the view by inspecting the desk within the Lake Formation console.

Now you can question the view because the Knowledge-Analyst function within the producer account, utilizing each Athena and Spark. The view will even present within the central account as proven within the following code instance, with entry to the Lake Formation admin.

You may as well create the view with ATHENA dialect and add the SPARK dialect. The SQL syntax to create the view in ATHENA dialect is proven within the following instance:

create protected multi dialect view mdv_transaction1
safety definer
as
SELECT t1.transaction_id, t1.transaction_type, t1.transaction_amount, t2.transaction_location, t2.transaction_date FROM transaction_table1 t1 
JOIN transaction_table2 t2 
ON t1.transaction_id = t2.transaction_id 
WHERE t1.transaction_amount > 100;

The update-table CLI so as to add the corresponding SPARK dialect is proven within the following instance:

aws glue update-table --cli-input-json '{
    "DatabaseName": "rl_bankdatadb",
    "ViewUpdateAction": "ADD",
    "Pressure": true,
    "TableInput": {
        "Title": " mdv_transaction1",
        "StorageDescriptor": {
            "Columns": [
                {
                  "Name": "transaction_id",
                  "Type": "string"
                },
                {
                  "Name": "transaction_type",
                  "Type": "string"
                },
                {
                  "Name": "transaction_amount",
                  "Type": "float"
                },
                {
                  "Name": "transaction_location",
                  "Type": "string"
                },
                {
                  "Name": "transaction_date",
                  "Type": "date"
                }
             ],
             "SerdeInfo": {}
         },
         "ViewDefinition": {
         "SubObjects": [
               " "arn:aws:glue:::table/bankdata_icebergdb/transaction_table1",
           "arn:aws:glue:::table/bankdata_icebergdb/transaction_table2" 
],
         "IsProtected": true,
         "Representations": [
             {
                 "Dialect": "SPARK",
                 "DialectVersion": "1.0",
                 "ViewOriginalText": " SELECT t1.transaction_id, t1.transaction_type, t1.transaction_amount, t2.transaction_location, t2.transaction_date FROM transaction_table1 t1 JOIN transaction_table2 t2 ON t1.transaction_id = t2.transaction_id WHERE t1.transaction_amount > 100",
                 "ViewExpandedText": " SELECT t1.transaction_id, t1.transaction_type, t1.transaction_amount, t2.transaction_location, t2.transaction_date FROM transaction_table1 t1 JOIN transaction_table2 t2 ON t1.transaction_id = t2.transaction_id WHERE t1.transaction_amount > 100"
              }
           ]
        }
    }
}'

The next is a pattern Python script to create a SPARK dialect view: glueview-createtable.py.

The next code block is a pattern AWS Glue extract, switch, and cargo (ETL) script to entry the Spark dialect of the view from AWS Glue 5.0 from the central account. The AWS Glue job execution function ought to have Lake Formation SELECT permission on the AWS Glue view:

from pyspark.context import SparkContext
from pyspark.sql import SparkSession

aws_region = ""
aws_account_id = ""
local_catalogname = "spark_catalog"
warehouse_path = "s3:///bankdata_icebergdb/transaction-table1"

spark = SparkSession.builder.appName('query_glue_view') 
    .config('spark.sql.extensions','org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions') 
    .config(f'spark.sql.catalog.{local_catalogname}', 'org.apache.iceberg.spark.SparkSessionCatalog') 
    .config(f'spark.sql.catalog.{local_catalogname}.catalog-impl', 'org.apache.iceberg.aws.glue.GlueCatalog') 
    .config(f'spark.sql.catalog.{local_catalogname}.shopper.area', aws_region) 
    .config(f'spark.sql.catalog.{local_catalogname}.glue.account-id', aws_account_id) 
    .config(f'spark.sql.catalog.{local_catalogname}.io-impl', 'org.apache.iceberg.aws.s3.S3FileIO') 
    .config(f'spark.sql.catalog.{local_catalogname}.warehouse',warehouse_path) 
    .getOrCreate()
spark.sql(f"present databases").present()
spark.sql(f"SHOW TABLES IN {local_catalogname}.bankdata_icebergdb").present()
spark.sql(f"SELECT * FROM {local_catalogname}.bankdata_icebergdb. mdv_transaction1").present()

Within the AWS Glue job-details, for Lake Formation managed tables and for Iceberg tables, set extra parameters respectively as follows:

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

Cleanup

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

1. Revoke the Lake Formation permissions granted to the Knowledge-Analyst function and Producer account
2. Drop the Athena tables
3. Delete the Athena question outcomes out of your Amazon Easy Storage Service (Amazon S3) bucket
4. Delete the Knowledge-Analyst function from IAM

Conclusion

On this submit, we demonstrated how you can use cross-account IAM definer roles with AWS Glue Knowledge Catalog views. We confirmed how knowledge proprietor accounts can create and handle views in a central governance account whereas sustaining safety and management over their knowledge property. This characteristic permits enterprises to implement refined knowledge mesh architectures with out compromising on safety or requiring knowledge duplication.

The flexibility to make use of cross-account definer roles with Knowledge Catalog views offers a number of key benefits:

• Streamlines view administration in multi-account environments
• Maintains current CI/CD workflows and automation
• Enhances safety by centralized governance
• Reduces operational overhead by eliminating the necessity for knowledge duplication

As organizations proceed to construct and scale their knowledge lakehouse architectures throughout a number of AWS accounts, cross-account definer roles for Knowledge Catalog views present an important functionality for implementing environment friendly, safe, and well-governed knowledge sharing patterns.

Concerning the authors