Configure cross-account entry of Amazon SageMaker Lakehouse multi-catalog tables utilizing AWS Glue 5.0 Spark

An IAM position, Glue-execution-role, within the client account, with the next insurance policies:

AWS managed insurance policies AWSGlueServiceRole and AmazonRedshiftDataFullAccess.

Create a brand new in-line coverage with the next permissions and fasten it:

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Sid": "LFandRSserverlessAccess",
            "Effect": "Allow",
            "Action": [
                "lakeformation:GetDataAccess",
                "redshift-serverless:GetCredentials"
            ],
            "Useful resource": "*"
        },
        {
            "Impact": "Permit",
            "Motion": "iam:PassRole",
            "Useful resource": "*",
            "Situation": {
                "StringEquals": {
                    "iam:PassedToService": "glue.amazonaws.com"
                }
            }
        }
    ]
}

Add the next belief coverage to Glue-execution-role, permitting AWS Glue to imagine this position:

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": [
                    "glue.amazonaws.com"
                ]
            },
            "Motion": "sts:AssumeRole"
        }
    ]
}

Steps for producer account setup

For the producer account setup, you’ll be able to both use your IAM administrator position added as Lake Formation administrator or use a Lake Formation administrator position with permissions added as mentioned within the conditions. For illustration functions, we use the IAM admin position Admin added as Lake Formation administrator.

Configure your catalog

Full the next steps to arrange your catalog:

Log in to AWS Administration Console as Admin.
On the Amazon Redshift console, observe the directions in Registering Amazon Redshift clusters and namespaces to the AWS Glue Knowledge Catalog.
After the registration is initiated, you will notice the invite from Amazon Redshift on the Lake Formation console.
Choose the pending catalog invitation and select Approve and create catalog.

On the Set catalog particulars web page, configure your catalog:
1. For Title, enter a reputation (for this put up, redshiftserverless1-uswest2).
2. Choose Entry this catalog from Apache Iceberg appropriate engines.
3. Select the IAM position you created for the information switch.
4. Select Subsequent.
On the Grant permissions – elective web page, select Add permissions.
1. Grant the Admin consumer Tremendous consumer permissions for Catalog permissions and Grantable permissions.
2. Select Add.
Confirm the granted permission on the subsequent web page and select Subsequent.
Evaluate the main points on the Evaluate and create web page and select Create catalog.

Wait just a few seconds for the catalog to indicate up.

Select Catalogs within the navigation pane and confirm that the redshiftserverless1-uswest2 catalog is created.
Discover the catalog element web page to confirm the ordersdb.public database.
On the database View dropdown menu, view the desk and confirm that the orderstbl desk exhibits up.

Because the Admin position, you too can question the orderstbl in Amazon Athena and ensure the information is offered.

Grant permissions on the tables from the producer account to the patron account

On this step, we share the Amazon Redshift federated catalog database redshiftserverless1-uswest2:ordersdb.public and desk orderstbl in addition to the Amazon S3 primarily based Iceberg desk returnstbl_iceberg and its database customerdb from the default catalog to the patron account. We are able to’t share your complete catalog to exterior accounts as a catalog-level permission; we simply share the database and desk.

On the Lake Formation console, select Knowledge permissions within the navigation pane.
Select Grant.
Underneath Principals, choose Exterior accounts.
Present the patron account ID.
Underneath LF-Tags or catalog assets, choose Named Knowledge Catalog assets.
For Catalogs, select the account ID that represents the default catalog.
For Databases, select customerdb.
Underneath Database permissions, choose Describe below Database permissions and Grantable permissions.
Select Grant.
Repeat these steps and grant table-level Choose and Describe permissions on returnstbl_iceberg.
Repeat these steps once more to grant database- and table-level permissions for the ordertbl desk of the federated catalog database redshiftserverless1-uswest2/ordersdb.

The next screenshots present the configuration for database-level permissions.

The next screenshots present the configuration for table-level permissions.

Select Knowledge permissions within the navigation pane and confirm that the patron account has been granted database- and table-level permissions for each orderstbl from the federated catalog and returnstbl_iceberg from the default catalog.

Register the Amazon S3 location of the returnstbl_iceberg with Lake Formation.

On this step, we register the Amazon S3 primarily based Iceberg desk returnstbl_iceberg knowledge location with Lake Formation to be managed by Lake Formation permissions. Full the next steps:

On the Lake Formation console, select Knowledge lake areas within the navigation pane.
Select Register location.
For Amazon S3 path, enter the trail in your S3 bucket that you simply supplied whereas creating the Iceberg desk returnstbl_iceberg.
For IAM position, present the user-defined position LakeFormationS3Registration_custom that you simply created as a prerequisite.
For Permission mode, choose Lake Formation.
Select Register location.
Select Knowledge lake areas within the navigation pane to confirm the Amazon S3 registration.

With this step, the producer account setup is full.

Steps for client account setup

For the patron account setup, we use the IAM admin position Admin, added as a Lake Formation administrator.

The steps within the client account are fairly concerned. Within the client account, a Lake Formation administrator will settle for the AWS Useful resource Entry Supervisor (AWS RAM) shares and create the required useful resource hyperlinks that time to the shared catalog, database, and tables. The Lake Formation admin verifies that the shared assets are accessible by working take a look at queries in Athena. The admin additional grants permissions to the position Glue-execution-role on the useful resource hyperlinks, database, and tables. The admin then runs a be part of question in AWS Glue 5.0 Spark utilizing Glue-execution-role.

Settle for and confirm the shared assets

Lake Formation makes use of AWS RAM shares to allow cross-account sharing with Knowledge Catalog useful resource insurance policies within the AWS RAM insurance policies. To view and confirm the shared assets from producer account, full the next steps:

Log in to the patron AWS console and set the AWS Area to match the producer’s shared useful resource Area. For this put up, we use us-west-2.
Open the Lake Formation console. You will note a message indicating there’s a pending invite and asking you settle for it on the AWS RAM console.
Observe the directions in Accepting a useful resource share invitation from AWS RAM to overview and settle for the pending invitations.
When the invite standing modifications to Accepted, select Shared assets below Shared with me within the navigation pane.
Confirm that the Redshift Serverless federated catalog redshiftserverless1-uswest2, the default catalog database customerdb, the desk returnstbl_iceberg, and the producer account ID below Proprietor ID column show accurately.
On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Databases.
Search by the producer account ID.
It is best to see the customerdb and public databases. You may additional choose every database and select View tables on the Actions dropdown menu and confirm the desk names

You’ll not see an AWS RAM share invite for the catalog degree on the Lake Formation console, as a result of catalog-level sharing isn’t attainable. You may overview the shared federated catalog and Amazon Redshift managed catalog names on the AWS RAM console, or utilizing the AWS Command Line Interface (AWS CLI) or SDK.

Create a catalog hyperlink container and useful resource hyperlinks

A catalog hyperlink container is a Knowledge Catalog object that references an area or cross-account federated database-level catalog from different AWS accounts. For extra particulars, confer with Accessing a shared federated catalog. Catalog hyperlink containers are primarily Lake Formation useful resource hyperlinks on the catalog degree that reference or level to a Redshift cluster federated catalog or Amazon Redshift managed catalog object from different accounts.

Within the following steps, we create a catalog hyperlink container that factors to the producer shared federated catalog redshiftserverless1-uswest2. Contained in the catalog hyperlink container, we create a database. Contained in the database, we create a useful resource hyperlink for the desk that factors to the shared federated catalog desk >:redshiftserverless1-uswest2/ordersdb.public.orderstbl.

On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Catalogs.
Select Create catalog.

Present the next particulars for the catalog:
1. For Title, enter a reputation for the catalog (for this put up, rl_link_container_ordersdb).
2. For Kind, select Catalog Hyperlink container.
3. For Supply, select Redshift.
4. For Goal Redshift Catalog, enter the Amazon Useful resource Title (ARN) of the producer federated catalog (arn:aws:glue:us-west-2:>:catalog/redshiftserverless1-uswest2/ordersdb).
5. Underneath Entry from engines, choose Entry this catalog from Apache Iceberg appropriate engines.
6. For IAM position, present the Redshift-S3 knowledge switch position that you simply had created within the conditions.
7. Select Subsequent.

On the Grant permissions – elective web page, select Add permissions.
1. Grant the Admin consumer Tremendous consumer permissions for Catalog permissions and Grantable permissions.
2. Select Add after which select Subsequent.

Evaluate the main points on the Evaluate and create web page and select Create catalog.

Wait just a few seconds for the catalog to indicate up.

Within the navigation pane, select Catalogs.
Confirm that rl_link_container_ordersdb is created.

Create a database below rl_link_container_ordersdb

Full the next steps:

On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Databases.
On the Select catalog dropdown menu, select rl_link_container_ordersdb.
Select Create database.

Alternatively, you’ll be able to select the Create dropdown menu after which select Database.

Present particulars for the database:
1. For Title, enter a reputation (for this put up, public_db).
2. For Catalog, select rl_link_container_ordersdb.
3. Go away Location – elective as clean.
4. Underneath Default permissions for newly created tables, deselect Use solely IAM entry management for brand spanking new tables on this database.
5. Select Create database.

Select Catalogs within the navigation pane to confirm that public_db is created below rl_link_container_ordersdb.

Create a desk useful resource hyperlink for the shared federated catalog desk

A useful resource hyperlink to a shared federated catalog desk can reside solely contained in the database of a catalog hyperlink container. A useful resource hyperlink for such tables won’t work if created contained in the default catalog. For extra particulars on useful resource hyperlinks, confer with Making a useful resource hyperlink to a shared Knowledge Catalog desk.

Full the next steps to create a desk useful resource hyperlink:

On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Tables.
On the Create dropdown menu, select Useful resource hyperlink.

Present particulars for the desk useful resource hyperlink:
1. For Useful resource hyperlink title, enter a reputation (for this put up, rl_orderstbl).
2. For Vacation spot catalog, select rl_link_container_ordersdb.
3. For Database, select public_db.
4. For Shared desk’s area, select US West (Oregon).
5. For Shared desk, select orderstbl.
6. After the Shared desk is chosen, Shared desk’s database and Shared desk’s catalog ID ought to get routinely populated.
7. Select Create.

Within the navigation pane, select Databases to confirm that rl_orderstbl is created below public_db, inside rl_link_container_ordersdb.

Create a database useful resource hyperlink for the shared default catalog database.

Now we create a database useful resource hyperlink within the default catalog to question the Amazon S3 primarily based Iceberg desk shared from the producer. For particulars on database useful resource hyperlinks, refer Making a useful resource hyperlink to a shared Knowledge Catalog database.

Although we’re capable of see the shared database within the default catalog of the patron, a useful resource hyperlink is required to question from analytics engines, akin to Athena, Amazon EMR, and AWS Glue. When utilizing AWS Glue with Lake Formation tables, the useful resource hyperlink must be named identically to the supply account’s useful resource. For extra particulars on utilizing AWS Glue with Lake Formation, confer with Issues and limitations.

Full the next steps to create a database useful resource hyperlink:

On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Databases.
On the Select catalog dropdown menu, select the account ID to decide on the default catalog.
Seek for customerdb.

It is best to see the shared database title customerdb with the Proprietor account ID as that of your producer account ID.

Choose customerdb, and on the Create dropdown menu, select Useful resource hyperlink.
Present particulars for the useful resource hyperlink:
1. For Useful resource hyperlink title, enter a reputation (for this put up, customerdb).
2. The remainder of the fields needs to be already populated.
3. Select Create.
Within the navigation pane, select Databases and confirm that customerdb is created below the default catalog. Useful resource hyperlink names will present in italicized font.

Confirm entry as Admin utilizing Athena

Now you’ll be able to confirm your entry utilizing Athena. Full the next steps:

Open the Athena console.
Ensure an S3 bucket is supplied to retailer the Athena question outcomes. For particulars, confer with Specify a question consequence location utilizing the Athena console.
Within the navigation pane, confirm each the default catalog and federated catalog tables by previewing them.
You can even run a be part of question as follows. Take note of the three-point notation for referring to the tables from two completely different catalogs:

SELECT
returns_tb.market as Market,
sum(orders_tb.amount) as Total_Quantity
FROM rl_link_container_ordersdb.public_db.rl_orderstbl as orders_tb
JOIN awsdatacatalog.customerdb.returnstbl_iceberg as returns_tb
ON orders_tb.order_id = returns_tb.order_id
GROUP BY returns_tb.market;

This verifies the brand new functionality of SageMaker Lakehouse, which permits accessing Redshift cluster tables and Amazon S3 primarily based Iceberg tables in the identical question, throughout AWS accounts, by the Knowledge Catalog, utilizing Lake Formation permissions.

Grant permissions to Glue-execution-role

Now we are going to share the assets from the producer account with extra IAM principals within the client account. Often, the information lake admin grants permissions to knowledge analysts, knowledge scientists, and knowledge engineers within the client account to do their job features, akin to processing and analyzing the information.

We arrange Lake Formation permissions on the catalog hyperlink container, databases, tables, and useful resource hyperlinks to the AWS Glue job execution position Glue-execution-role that we created within the conditions.

Useful resource hyperlinks permit solely Describe and Drop permissions. You should use the Grant on track configuration to supply database Describe and desk Choose permissions.

Full the next steps:

On the Lake Formation console, select Knowledge permissions within the navigation pane.
Select Grant.
Underneath Principals, choose IAM customers and roles.
For IAM customers and roles, enter Glue-execution-role.
Underneath LF-Tags or catalog assets, choose Named Knowledge Catalog assets.
For Catalogs, select rl_link_container_ordersdb and the patron account ID, which signifies the default catalog.
Underneath Catalog permissions, choose Describe for Catalog permissions.
Select Grant.

Repeat these steps for the catalog rl_link_container_ordersdb:
1. On the Databases dropdown menu, select public_db.
2. Underneath Database permissions, choose Describe.
3. Select Grant.
Repeat these steps once more, however after selecting rl_link_container_ordersdb and public_db, on the Tables dropdown menu, select rl_orderstbl.
1. Underneath Useful resource hyperlink permissions, choose Describe.
2. Select Grant.
Repeat these steps to grant extra permissions to Glue-execution-role.
1. For this iteration, grant Describe permissions on the default catalog databases public and customerdb.
2. Grant Describe permission on the useful resource hyperlink customerdb.
3. Grant Choose permission on the tables returnstbl_iceberg and orderstbl.

The next screenshots present the configuration for database public and customerdb permissions.

The next screenshots present the configuration for useful resource hyperlink customerdb permissions.

The next screenshots present the configuration for desk returnstbl_iceberg permissions.

The next screenshots present the configuration for desk orderstbl permissions.

Within the navigation pane, select Knowledge permissions and confirm permissions on Glue-execution-role.

Run a PySpark job in AWS Glue 5.0

Obtain the PySpark script LakeHouseGlueSparkJob.py. This AWS Glue PySpark script runs Spark SQL by becoming a member of the producer shared federated orderstbl desk and Amazon S3 primarily based returns desk within the client account to investigate the information and establish the overall orders positioned per market.

Exchange > within the script together with your client account ID. Full the next steps to create and run an AWS Glue job:

On the AWS Glue console, within the navigation pane, select ETL jobs.
Select Create job, then select Script editor.

For Engine, select Spark.
For Choices, select Begin contemporary.
Select Add script.
Browse to the placement the place you downloaded and edited the script, choose the script, and select Open.
On the Job particulars tab, present the next info:
1. For Title, enter a reputation (for this put up, LakeHouseGlueSparkJob).
2. Underneath Primary properties, for IAM position, select Glue-execution-role.
3. For Glue model, choose Glue 5.0.
4. Underneath Superior properties, for Job parameters, select Add new parameter.
5. Add the parameters --datalake-formats = iceberg and --enable-lakeformation-fine-grained-access = true.
Save the job.
Select Run to execute the AWS Glue job, and anticipate the job to finish.
Evaluate the job run particulars from the Output logs

Clear up

To keep away from incurring prices in your AWS accounts, clear up the assets you created:

Delete the Lake Formation permissions, catalog hyperlink container, database, and tables within the client account.
Delete the AWS Glue job within the client account.
Delete the federated catalog, database, and desk assets within the producer account.
Delete the Redshift Serverless namespace within the producer account.
Delete the S3 buckets you created as a part of knowledge switch in each accounts and the Athena question outcomes bucket within the client account.
Clear up the IAM roles you created for the SageMaker Lakehouse setup as a part of the conditions.

Conclusion

On this put up, we illustrated tips on how to deliver your present Redshift tables to SageMaker Lakehouse and share them securely with exterior AWS accounts. We additionally confirmed tips on how to question the shared knowledge warehouse and knowledge lakehouse tables in the identical Spark session, from a recipient account, utilizing Spark in AWS Glue 5.0.

We hope you discover this convenient to combine your Redshift tables with an present knowledge mesh and entry the tables utilizing AWS Glue Spark. Check this answer in your accounts and share suggestions within the feedback part. Keep tuned for extra updates and be happy to discover the options of SageMaker Lakehouse and AWS Glue variations.

Appendix: Desk creation

Full the next steps to create a returns desk within the Amazon S3 primarily based default catalog and an orders desk in Amazon Redshift:

Obtain the CSV format datasets orders and returns.
Add them to your S3 bucket below the corresponding desk prefix path.
Use the next SQL statements in Athena. First-time customers of Athena ought to confer with Specify a question consequence location.

CREATE DATABASE customerdb;
CREATE EXTERNAL TABLE customerdb.returnstbl_csv(
  `returned` string, 
  `order_id` string, 
  `market` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ';' 
LOCATION
  's3:////'
TBLPROPERTIES (
  'skip.header.line.rely'='1'
);

choose * from customerdb.returnstbl_csv restrict 10;

Create an Iceberg format desk within the default catalog and insert knowledge from the CSV format desk:

CREATE TABLE customerdb.returnstbl_iceberg(
  `returned` string, 
  `order_id` string, 
  `market` string)
LOCATION 's3:///returnstbl_iceberg/' 
TBLPROPERTIES (
  'table_type'='ICEBERG'
);

INSERT INTO customerdb.returnstbl_iceberg
SELECT *
FROM returnstbl_csv;  

SELECT * FROM customerdb.returnstbl_iceberg LIMIT 10;

To create the orders desk within the Redshift Serverless namespace, open the Question Editor v2 on the Amazon Redshift console.
Hook up with the default namespace utilizing your database admin consumer credentials.
Run the next instructions within the SQL editor to create the database ordersdb and desk orderstbl in it. Copy the information out of your S3 location of the orders knowledge to the orderstbl:

create database ordersdb;
use ordersdb;

create desk orderstbl(
  row_id int, 
  order_id VARCHAR, 
  order_date VARCHAR, 
  ship_date VARCHAR, 
  ship_mode VARCHAR, 
  customer_id VARCHAR, 
  customer_name VARCHAR, 
  phase VARCHAR, 
  metropolis VARCHAR, 
  state VARCHAR, 
  nation VARCHAR, 
  postal_code int, 
  market VARCHAR, 
  area VARCHAR, 
  product_id VARCHAR, 
  class VARCHAR, 
  sub_category VARCHAR, 
  product_name VARCHAR, 
  gross sales VARCHAR, 
  amount bigint, 
  low cost VARCHAR, 
  revenue VARCHAR, 
  shipping_cost VARCHAR, 
  order_priority VARCHAR
  );

copy orderstbl
from 's3:///ordersdatacsv/orders.csv' 
iam_role 'arn:aws:iam:::position/service-role/'
CSV 
DELIMITER ';'
IGNOREHEADER 1
;

choose * from ordersdb.orderstbl restrict 5;

In regards to the Authors

Aarthi Srinivasan is a Senior Massive Knowledge Architect with Amazon SageMaker Lakehouse. She collaborates with the service workforce to reinforce product options, works with AWS prospects and companions to architect lakehouse options, and establishes greatest practices for knowledge governance.

Subhasis Sarkar is a Senior Knowledge Engineer with Amazon. Subhasis thrives on fixing advanced technological challenges with modern options. He makes a speciality of AWS knowledge architectures, notably knowledge mesh implementations utilizing AWS CDK parts.

An IAM position, Glue-execution-role, within the client account, with the next insurance policies:

AWS managed insurance policies AWSGlueServiceRole and AmazonRedshiftDataFullAccess.

Create a brand new in-line coverage with the next permissions and fasten it:

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Sid": "LFandRSserverlessAccess",
            "Effect": "Allow",
            "Action": [
                "lakeformation:GetDataAccess",
                "redshift-serverless:GetCredentials"
            ],
            "Useful resource": "*"
        },
        {
            "Impact": "Permit",
            "Motion": "iam:PassRole",
            "Useful resource": "*",
            "Situation": {
                "StringEquals": {
                    "iam:PassedToService": "glue.amazonaws.com"
                }
            }
        }
    ]
}

Add the next belief coverage to Glue-execution-role, permitting AWS Glue to imagine this position:

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": [
                    "glue.amazonaws.com"
                ]
            },
            "Motion": "sts:AssumeRole"
        }
    ]
}

Steps for producer account setup

Configure your catalog

Full the next steps to arrange your catalog:

Log in to AWS Administration Console as Admin.
On the Amazon Redshift console, observe the directions in Registering Amazon Redshift clusters and namespaces to the AWS Glue Knowledge Catalog.
After the registration is initiated, you will notice the invite from Amazon Redshift on the Lake Formation console.
Choose the pending catalog invitation and select Approve and create catalog.

On the Set catalog particulars web page, configure your catalog:
1. For Title, enter a reputation (for this put up, redshiftserverless1-uswest2).
2. Choose Entry this catalog from Apache Iceberg appropriate engines.
3. Select the IAM position you created for the information switch.
4. Select Subsequent.
On the Grant permissions – elective web page, select Add permissions.
1. Grant the Admin consumer Tremendous consumer permissions for Catalog permissions and Grantable permissions.
2. Select Add.
Confirm the granted permission on the subsequent web page and select Subsequent.
Evaluate the main points on the Evaluate and create web page and select Create catalog.

Wait just a few seconds for the catalog to indicate up.

Select Catalogs within the navigation pane and confirm that the redshiftserverless1-uswest2 catalog is created.
Discover the catalog element web page to confirm the ordersdb.public database.
On the database View dropdown menu, view the desk and confirm that the orderstbl desk exhibits up.

Because the Admin position, you too can question the orderstbl in Amazon Athena and ensure the information is offered.

Grant permissions on the tables from the producer account to the patron account

On the Lake Formation console, select Knowledge permissions within the navigation pane.
Select Grant.
Underneath Principals, choose Exterior accounts.
Present the patron account ID.
Underneath LF-Tags or catalog assets, choose Named Knowledge Catalog assets.
For Catalogs, select the account ID that represents the default catalog.
For Databases, select customerdb.
Underneath Database permissions, choose Describe below Database permissions and Grantable permissions.
Select Grant.
Repeat these steps and grant table-level Choose and Describe permissions on returnstbl_iceberg.
Repeat these steps once more to grant database- and table-level permissions for the ordertbl desk of the federated catalog database redshiftserverless1-uswest2/ordersdb.

The next screenshots present the configuration for database-level permissions.

The next screenshots present the configuration for table-level permissions.

Select Knowledge permissions within the navigation pane and confirm that the patron account has been granted database- and table-level permissions for each orderstbl from the federated catalog and returnstbl_iceberg from the default catalog.

Register the Amazon S3 location of the returnstbl_iceberg with Lake Formation.

On this step, we register the Amazon S3 primarily based Iceberg desk returnstbl_iceberg knowledge location with Lake Formation to be managed by Lake Formation permissions. Full the next steps:

On the Lake Formation console, select Knowledge lake areas within the navigation pane.
Select Register location.
For Amazon S3 path, enter the trail in your S3 bucket that you simply supplied whereas creating the Iceberg desk returnstbl_iceberg.
For IAM position, present the user-defined position LakeFormationS3Registration_custom that you simply created as a prerequisite.
For Permission mode, choose Lake Formation.
Select Register location.
Select Knowledge lake areas within the navigation pane to confirm the Amazon S3 registration.

With this step, the producer account setup is full.

Steps for client account setup

For the patron account setup, we use the IAM admin position Admin, added as a Lake Formation administrator.

Settle for and confirm the shared assets

Log in to the patron AWS console and set the AWS Area to match the producer’s shared useful resource Area. For this put up, we use us-west-2.
Open the Lake Formation console. You will note a message indicating there’s a pending invite and asking you settle for it on the AWS RAM console.
Observe the directions in Accepting a useful resource share invitation from AWS RAM to overview and settle for the pending invitations.
When the invite standing modifications to Accepted, select Shared assets below Shared with me within the navigation pane.
Confirm that the Redshift Serverless federated catalog redshiftserverless1-uswest2, the default catalog database customerdb, the desk returnstbl_iceberg, and the producer account ID below Proprietor ID column show accurately.
On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Databases.
Search by the producer account ID.
It is best to see the customerdb and public databases. You may additional choose every database and select View tables on the Actions dropdown menu and confirm the desk names

Create a catalog hyperlink container and useful resource hyperlinks

On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Catalogs.
Select Create catalog.

Present the next particulars for the catalog:
1. For Title, enter a reputation for the catalog (for this put up, rl_link_container_ordersdb).
2. For Kind, select Catalog Hyperlink container.
3. For Supply, select Redshift.
4. For Goal Redshift Catalog, enter the Amazon Useful resource Title (ARN) of the producer federated catalog (arn:aws:glue:us-west-2:>:catalog/redshiftserverless1-uswest2/ordersdb).
5. Underneath Entry from engines, choose Entry this catalog from Apache Iceberg appropriate engines.
6. For IAM position, present the Redshift-S3 knowledge switch position that you simply had created within the conditions.
7. Select Subsequent.

On the Grant permissions – elective web page, select Add permissions.
1. Grant the Admin consumer Tremendous consumer permissions for Catalog permissions and Grantable permissions.
2. Select Add after which select Subsequent.

Evaluate the main points on the Evaluate and create web page and select Create catalog.

Wait just a few seconds for the catalog to indicate up.

Within the navigation pane, select Catalogs.
Confirm that rl_link_container_ordersdb is created.

Create a database below rl_link_container_ordersdb

Full the next steps:

On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Databases.
On the Select catalog dropdown menu, select rl_link_container_ordersdb.
Select Create database.

Alternatively, you’ll be able to select the Create dropdown menu after which select Database.

Present particulars for the database:
1. For Title, enter a reputation (for this put up, public_db).
2. For Catalog, select rl_link_container_ordersdb.
3. Go away Location – elective as clean.
4. Underneath Default permissions for newly created tables, deselect Use solely IAM entry management for brand spanking new tables on this database.
5. Select Create database.

Select Catalogs within the navigation pane to confirm that public_db is created below rl_link_container_ordersdb.

Create a desk useful resource hyperlink for the shared federated catalog desk

Full the next steps to create a desk useful resource hyperlink:

On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Tables.
On the Create dropdown menu, select Useful resource hyperlink.

Present particulars for the desk useful resource hyperlink:
1. For Useful resource hyperlink title, enter a reputation (for this put up, rl_orderstbl).
2. For Vacation spot catalog, select rl_link_container_ordersdb.
3. For Database, select public_db.
4. For Shared desk’s area, select US West (Oregon).
5. For Shared desk, select orderstbl.
6. After the Shared desk is chosen, Shared desk’s database and Shared desk’s catalog ID ought to get routinely populated.
7. Select Create.

Within the navigation pane, select Databases to confirm that rl_orderstbl is created below public_db, inside rl_link_container_ordersdb.

Create a database useful resource hyperlink for the shared default catalog database.

Full the next steps to create a database useful resource hyperlink:

On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Databases.
On the Select catalog dropdown menu, select the account ID to decide on the default catalog.
Seek for customerdb.

It is best to see the shared database title customerdb with the Proprietor account ID as that of your producer account ID.

Choose customerdb, and on the Create dropdown menu, select Useful resource hyperlink.
Present particulars for the useful resource hyperlink:
1. For Useful resource hyperlink title, enter a reputation (for this put up, customerdb).
2. The remainder of the fields needs to be already populated.
3. Select Create.
Within the navigation pane, select Databases and confirm that customerdb is created below the default catalog. Useful resource hyperlink names will present in italicized font.

Confirm entry as Admin utilizing Athena

Now you’ll be able to confirm your entry utilizing Athena. Full the next steps:

Open the Athena console.
Ensure an S3 bucket is supplied to retailer the Athena question outcomes. For particulars, confer with Specify a question consequence location utilizing the Athena console.
Within the navigation pane, confirm each the default catalog and federated catalog tables by previewing them.
You can even run a be part of question as follows. Take note of the three-point notation for referring to the tables from two completely different catalogs:

SELECT
returns_tb.market as Market,
sum(orders_tb.amount) as Total_Quantity
FROM rl_link_container_ordersdb.public_db.rl_orderstbl as orders_tb
JOIN awsdatacatalog.customerdb.returnstbl_iceberg as returns_tb
ON orders_tb.order_id = returns_tb.order_id
GROUP BY returns_tb.market;

Grant permissions to Glue-execution-role

Useful resource hyperlinks permit solely Describe and Drop permissions. You should use the Grant on track configuration to supply database Describe and desk Choose permissions.

Full the next steps:

On the Lake Formation console, select Knowledge permissions within the navigation pane.
Select Grant.
Underneath Principals, choose IAM customers and roles.
For IAM customers and roles, enter Glue-execution-role.
Underneath LF-Tags or catalog assets, choose Named Knowledge Catalog assets.
For Catalogs, select rl_link_container_ordersdb and the patron account ID, which signifies the default catalog.
Underneath Catalog permissions, choose Describe for Catalog permissions.
Select Grant.

Repeat these steps for the catalog rl_link_container_ordersdb:
1. On the Databases dropdown menu, select public_db.
2. Underneath Database permissions, choose Describe.
3. Select Grant.
Repeat these steps once more, however after selecting rl_link_container_ordersdb and public_db, on the Tables dropdown menu, select rl_orderstbl.
1. Underneath Useful resource hyperlink permissions, choose Describe.
2. Select Grant.
Repeat these steps to grant extra permissions to Glue-execution-role.
1. For this iteration, grant Describe permissions on the default catalog databases public and customerdb.
2. Grant Describe permission on the useful resource hyperlink customerdb.
3. Grant Choose permission on the tables returnstbl_iceberg and orderstbl.

The next screenshots present the configuration for database public and customerdb permissions.

The next screenshots present the configuration for useful resource hyperlink customerdb permissions.

The next screenshots present the configuration for desk returnstbl_iceberg permissions.

The next screenshots present the configuration for desk orderstbl permissions.

Within the navigation pane, select Knowledge permissions and confirm permissions on Glue-execution-role.

Run a PySpark job in AWS Glue 5.0

Exchange > within the script together with your client account ID. Full the next steps to create and run an AWS Glue job:

On the AWS Glue console, within the navigation pane, select ETL jobs.
Select Create job, then select Script editor.

For Engine, select Spark.
For Choices, select Begin contemporary.
Select Add script.
Browse to the placement the place you downloaded and edited the script, choose the script, and select Open.
On the Job particulars tab, present the next info:
1. For Title, enter a reputation (for this put up, LakeHouseGlueSparkJob).
2. Underneath Primary properties, for IAM position, select Glue-execution-role.
3. For Glue model, choose Glue 5.0.
4. Underneath Superior properties, for Job parameters, select Add new parameter.
5. Add the parameters --datalake-formats = iceberg and --enable-lakeformation-fine-grained-access = true.
Save the job.
Select Run to execute the AWS Glue job, and anticipate the job to finish.
Evaluate the job run particulars from the Output logs

Clear up

To keep away from incurring prices in your AWS accounts, clear up the assets you created:

Delete the Lake Formation permissions, catalog hyperlink container, database, and tables within the client account.
Delete the AWS Glue job within the client account.
Delete the federated catalog, database, and desk assets within the producer account.
Delete the Redshift Serverless namespace within the producer account.
Delete the S3 buckets you created as a part of knowledge switch in each accounts and the Athena question outcomes bucket within the client account.
Clear up the IAM roles you created for the SageMaker Lakehouse setup as a part of the conditions.

Conclusion

Appendix: Desk creation

Full the next steps to create a returns desk within the Amazon S3 primarily based default catalog and an orders desk in Amazon Redshift:

Obtain the CSV format datasets orders and returns.
Add them to your S3 bucket below the corresponding desk prefix path.
Use the next SQL statements in Athena. First-time customers of Athena ought to confer with Specify a question consequence location.

CREATE DATABASE customerdb;
CREATE EXTERNAL TABLE customerdb.returnstbl_csv(
  `returned` string, 
  `order_id` string, 
  `market` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ';' 
LOCATION
  's3:////'
TBLPROPERTIES (
  'skip.header.line.rely'='1'
);

choose * from customerdb.returnstbl_csv restrict 10;

Create an Iceberg format desk within the default catalog and insert knowledge from the CSV format desk:

CREATE TABLE customerdb.returnstbl_iceberg(
  `returned` string, 
  `order_id` string, 
  `market` string)
LOCATION 's3:///returnstbl_iceberg/' 
TBLPROPERTIES (
  'table_type'='ICEBERG'
);

INSERT INTO customerdb.returnstbl_iceberg
SELECT *
FROM returnstbl_csv;  

SELECT * FROM customerdb.returnstbl_iceberg LIMIT 10;

To create the orders desk within the Redshift Serverless namespace, open the Question Editor v2 on the Amazon Redshift console.
Hook up with the default namespace utilizing your database admin consumer credentials.
Run the next instructions within the SQL editor to create the database ordersdb and desk orderstbl in it. Copy the information out of your S3 location of the orders knowledge to the orderstbl:

create database ordersdb;
use ordersdb;

create desk orderstbl(
  row_id int, 
  order_id VARCHAR, 
  order_date VARCHAR, 
  ship_date VARCHAR, 
  ship_mode VARCHAR, 
  customer_id VARCHAR, 
  customer_name VARCHAR, 
  phase VARCHAR, 
  metropolis VARCHAR, 
  state VARCHAR, 
  nation VARCHAR, 
  postal_code int, 
  market VARCHAR, 
  area VARCHAR, 
  product_id VARCHAR, 
  class VARCHAR, 
  sub_category VARCHAR, 
  product_name VARCHAR, 
  gross sales VARCHAR, 
  amount bigint, 
  low cost VARCHAR, 
  revenue VARCHAR, 
  shipping_cost VARCHAR, 
  order_priority VARCHAR
  );

copy orderstbl
from 's3:///ordersdatacsv/orders.csv' 
iam_role 'arn:aws:iam:::position/service-role/'
CSV 
DELIMITER ';'
IGNOREHEADER 1
;

choose * from ordersdb.orderstbl restrict 5;

In regards to the Authors

Scalable analytics and centralized governance for Apache Iceberg tables utilizing Amazon S3 Tables and Amazon Redshift

Writer Quicker, Smarter AI/BI Dashboards with Dynamic Calculations

NVIDIA Pronounces DGX Cloud Lepton for GPU Entry throughout Multi-Cloud Platforms

An IAM position, Glue-execution-role, within the client account, with the next insurance policies:

AWS managed insurance policies AWSGlueServiceRole and AmazonRedshiftDataFullAccess.

Create a brand new in-line coverage with the next permissions and fasten it:

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Sid": "LFandRSserverlessAccess",
            "Effect": "Allow",
            "Action": [
                "lakeformation:GetDataAccess",
                "redshift-serverless:GetCredentials"
            ],
            "Useful resource": "*"
        },
        {
            "Impact": "Permit",
            "Motion": "iam:PassRole",
            "Useful resource": "*",
            "Situation": {
                "StringEquals": {
                    "iam:PassedToService": "glue.amazonaws.com"
                }
            }
        }
    ]
}

Add the next belief coverage to Glue-execution-role, permitting AWS Glue to imagine this position:

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": [
                    "glue.amazonaws.com"
                ]
            },
            "Motion": "sts:AssumeRole"
        }
    ]
}

Steps for producer account setup

Configure your catalog

Full the next steps to arrange your catalog:

Log in to AWS Administration Console as Admin.
On the Amazon Redshift console, observe the directions in Registering Amazon Redshift clusters and namespaces to the AWS Glue Knowledge Catalog.
After the registration is initiated, you will notice the invite from Amazon Redshift on the Lake Formation console.
Choose the pending catalog invitation and select Approve and create catalog.

On the Set catalog particulars web page, configure your catalog:
1. For Title, enter a reputation (for this put up, redshiftserverless1-uswest2).
2. Choose Entry this catalog from Apache Iceberg appropriate engines.
3. Select the IAM position you created for the information switch.
4. Select Subsequent.
On the Grant permissions – elective web page, select Add permissions.
1. Grant the Admin consumer Tremendous consumer permissions for Catalog permissions and Grantable permissions.
2. Select Add.
Confirm the granted permission on the subsequent web page and select Subsequent.
Evaluate the main points on the Evaluate and create web page and select Create catalog.

Wait just a few seconds for the catalog to indicate up.

Select Catalogs within the navigation pane and confirm that the redshiftserverless1-uswest2 catalog is created.
Discover the catalog element web page to confirm the ordersdb.public database.
On the database View dropdown menu, view the desk and confirm that the orderstbl desk exhibits up.

Because the Admin position, you too can question the orderstbl in Amazon Athena and ensure the information is offered.

Grant permissions on the tables from the producer account to the patron account

On the Lake Formation console, select Knowledge permissions within the navigation pane.
Select Grant.
Underneath Principals, choose Exterior accounts.
Present the patron account ID.
Underneath LF-Tags or catalog assets, choose Named Knowledge Catalog assets.
For Catalogs, select the account ID that represents the default catalog.
For Databases, select customerdb.
Underneath Database permissions, choose Describe below Database permissions and Grantable permissions.
Select Grant.
Repeat these steps and grant table-level Choose and Describe permissions on returnstbl_iceberg.
Repeat these steps once more to grant database- and table-level permissions for the ordertbl desk of the federated catalog database redshiftserverless1-uswest2/ordersdb.

The next screenshots present the configuration for database-level permissions.

The next screenshots present the configuration for table-level permissions.

Select Knowledge permissions within the navigation pane and confirm that the patron account has been granted database- and table-level permissions for each orderstbl from the federated catalog and returnstbl_iceberg from the default catalog.

Register the Amazon S3 location of the returnstbl_iceberg with Lake Formation.

On this step, we register the Amazon S3 primarily based Iceberg desk returnstbl_iceberg knowledge location with Lake Formation to be managed by Lake Formation permissions. Full the next steps:

On the Lake Formation console, select Knowledge lake areas within the navigation pane.
Select Register location.
For Amazon S3 path, enter the trail in your S3 bucket that you simply supplied whereas creating the Iceberg desk returnstbl_iceberg.
For IAM position, present the user-defined position LakeFormationS3Registration_custom that you simply created as a prerequisite.
For Permission mode, choose Lake Formation.
Select Register location.
Select Knowledge lake areas within the navigation pane to confirm the Amazon S3 registration.

With this step, the producer account setup is full.

Steps for client account setup

For the patron account setup, we use the IAM admin position Admin, added as a Lake Formation administrator.

Settle for and confirm the shared assets

Log in to the patron AWS console and set the AWS Area to match the producer’s shared useful resource Area. For this put up, we use us-west-2.
Open the Lake Formation console. You will note a message indicating there’s a pending invite and asking you settle for it on the AWS RAM console.
Observe the directions in Accepting a useful resource share invitation from AWS RAM to overview and settle for the pending invitations.
When the invite standing modifications to Accepted, select Shared assets below Shared with me within the navigation pane.
Confirm that the Redshift Serverless federated catalog redshiftserverless1-uswest2, the default catalog database customerdb, the desk returnstbl_iceberg, and the producer account ID below Proprietor ID column show accurately.
On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Databases.
Search by the producer account ID.
It is best to see the customerdb and public databases. You may additional choose every database and select View tables on the Actions dropdown menu and confirm the desk names

Create a catalog hyperlink container and useful resource hyperlinks

On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Catalogs.
Select Create catalog.

Present the next particulars for the catalog:
1. For Title, enter a reputation for the catalog (for this put up, rl_link_container_ordersdb).
2. For Kind, select Catalog Hyperlink container.
3. For Supply, select Redshift.
4. For Goal Redshift Catalog, enter the Amazon Useful resource Title (ARN) of the producer federated catalog (arn:aws:glue:us-west-2:>:catalog/redshiftserverless1-uswest2/ordersdb).
5. Underneath Entry from engines, choose Entry this catalog from Apache Iceberg appropriate engines.
6. For IAM position, present the Redshift-S3 knowledge switch position that you simply had created within the conditions.
7. Select Subsequent.

On the Grant permissions – elective web page, select Add permissions.
1. Grant the Admin consumer Tremendous consumer permissions for Catalog permissions and Grantable permissions.
2. Select Add after which select Subsequent.

Evaluate the main points on the Evaluate and create web page and select Create catalog.

Wait just a few seconds for the catalog to indicate up.

Within the navigation pane, select Catalogs.
Confirm that rl_link_container_ordersdb is created.

Create a database below rl_link_container_ordersdb

Full the next steps:

On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Databases.
On the Select catalog dropdown menu, select rl_link_container_ordersdb.
Select Create database.

Alternatively, you’ll be able to select the Create dropdown menu after which select Database.

Present particulars for the database:
1. For Title, enter a reputation (for this put up, public_db).
2. For Catalog, select rl_link_container_ordersdb.
3. Go away Location – elective as clean.
4. Underneath Default permissions for newly created tables, deselect Use solely IAM entry management for brand spanking new tables on this database.
5. Select Create database.

Select Catalogs within the navigation pane to confirm that public_db is created below rl_link_container_ordersdb.

Create a desk useful resource hyperlink for the shared federated catalog desk

Full the next steps to create a desk useful resource hyperlink:

On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Tables.
On the Create dropdown menu, select Useful resource hyperlink.

Present particulars for the desk useful resource hyperlink:
1. For Useful resource hyperlink title, enter a reputation (for this put up, rl_orderstbl).
2. For Vacation spot catalog, select rl_link_container_ordersdb.
3. For Database, select public_db.
4. For Shared desk’s area, select US West (Oregon).
5. For Shared desk, select orderstbl.
6. After the Shared desk is chosen, Shared desk’s database and Shared desk’s catalog ID ought to get routinely populated.
7. Select Create.

Within the navigation pane, select Databases to confirm that rl_orderstbl is created below public_db, inside rl_link_container_ordersdb.

Create a database useful resource hyperlink for the shared default catalog database.

Full the next steps to create a database useful resource hyperlink:

On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Databases.
On the Select catalog dropdown menu, select the account ID to decide on the default catalog.
Seek for customerdb.

It is best to see the shared database title customerdb with the Proprietor account ID as that of your producer account ID.

Choose customerdb, and on the Create dropdown menu, select Useful resource hyperlink.
Present particulars for the useful resource hyperlink:
1. For Useful resource hyperlink title, enter a reputation (for this put up, customerdb).
2. The remainder of the fields needs to be already populated.
3. Select Create.
Within the navigation pane, select Databases and confirm that customerdb is created below the default catalog. Useful resource hyperlink names will present in italicized font.

Confirm entry as Admin utilizing Athena

Now you’ll be able to confirm your entry utilizing Athena. Full the next steps:

Open the Athena console.
Ensure an S3 bucket is supplied to retailer the Athena question outcomes. For particulars, confer with Specify a question consequence location utilizing the Athena console.
Within the navigation pane, confirm each the default catalog and federated catalog tables by previewing them.
You can even run a be part of question as follows. Take note of the three-point notation for referring to the tables from two completely different catalogs:

SELECT
returns_tb.market as Market,
sum(orders_tb.amount) as Total_Quantity
FROM rl_link_container_ordersdb.public_db.rl_orderstbl as orders_tb
JOIN awsdatacatalog.customerdb.returnstbl_iceberg as returns_tb
ON orders_tb.order_id = returns_tb.order_id
GROUP BY returns_tb.market;

Grant permissions to Glue-execution-role

Useful resource hyperlinks permit solely Describe and Drop permissions. You should use the Grant on track configuration to supply database Describe and desk Choose permissions.

Full the next steps:

On the Lake Formation console, select Knowledge permissions within the navigation pane.
Select Grant.
Underneath Principals, choose IAM customers and roles.
For IAM customers and roles, enter Glue-execution-role.
Underneath LF-Tags or catalog assets, choose Named Knowledge Catalog assets.
For Catalogs, select rl_link_container_ordersdb and the patron account ID, which signifies the default catalog.
Underneath Catalog permissions, choose Describe for Catalog permissions.
Select Grant.

Repeat these steps for the catalog rl_link_container_ordersdb:
1. On the Databases dropdown menu, select public_db.
2. Underneath Database permissions, choose Describe.
3. Select Grant.
Repeat these steps once more, however after selecting rl_link_container_ordersdb and public_db, on the Tables dropdown menu, select rl_orderstbl.
1. Underneath Useful resource hyperlink permissions, choose Describe.
2. Select Grant.
Repeat these steps to grant extra permissions to Glue-execution-role.
1. For this iteration, grant Describe permissions on the default catalog databases public and customerdb.
2. Grant Describe permission on the useful resource hyperlink customerdb.
3. Grant Choose permission on the tables returnstbl_iceberg and orderstbl.

The next screenshots present the configuration for database public and customerdb permissions.

The next screenshots present the configuration for useful resource hyperlink customerdb permissions.

The next screenshots present the configuration for desk returnstbl_iceberg permissions.

The next screenshots present the configuration for desk orderstbl permissions.

Within the navigation pane, select Knowledge permissions and confirm permissions on Glue-execution-role.

Run a PySpark job in AWS Glue 5.0

Exchange > within the script together with your client account ID. Full the next steps to create and run an AWS Glue job:

On the AWS Glue console, within the navigation pane, select ETL jobs.
Select Create job, then select Script editor.

For Engine, select Spark.
For Choices, select Begin contemporary.
Select Add script.
Browse to the placement the place you downloaded and edited the script, choose the script, and select Open.
On the Job particulars tab, present the next info:
1. For Title, enter a reputation (for this put up, LakeHouseGlueSparkJob).
2. Underneath Primary properties, for IAM position, select Glue-execution-role.
3. For Glue model, choose Glue 5.0.
4. Underneath Superior properties, for Job parameters, select Add new parameter.
5. Add the parameters --datalake-formats = iceberg and --enable-lakeformation-fine-grained-access = true.
Save the job.
Select Run to execute the AWS Glue job, and anticipate the job to finish.
Evaluate the job run particulars from the Output logs

Clear up

To keep away from incurring prices in your AWS accounts, clear up the assets you created:

Delete the Lake Formation permissions, catalog hyperlink container, database, and tables within the client account.
Delete the AWS Glue job within the client account.
Delete the federated catalog, database, and desk assets within the producer account.
Delete the Redshift Serverless namespace within the producer account.
Delete the S3 buckets you created as a part of knowledge switch in each accounts and the Athena question outcomes bucket within the client account.
Clear up the IAM roles you created for the SageMaker Lakehouse setup as a part of the conditions.

Conclusion

Appendix: Desk creation

Full the next steps to create a returns desk within the Amazon S3 primarily based default catalog and an orders desk in Amazon Redshift:

Obtain the CSV format datasets orders and returns.
Add them to your S3 bucket below the corresponding desk prefix path.
Use the next SQL statements in Athena. First-time customers of Athena ought to confer with Specify a question consequence location.

CREATE DATABASE customerdb;
CREATE EXTERNAL TABLE customerdb.returnstbl_csv(
  `returned` string, 
  `order_id` string, 
  `market` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ';' 
LOCATION
  's3:////'
TBLPROPERTIES (
  'skip.header.line.rely'='1'
);

choose * from customerdb.returnstbl_csv restrict 10;

Create an Iceberg format desk within the default catalog and insert knowledge from the CSV format desk:

CREATE TABLE customerdb.returnstbl_iceberg(
  `returned` string, 
  `order_id` string, 
  `market` string)
LOCATION 's3:///returnstbl_iceberg/' 
TBLPROPERTIES (
  'table_type'='ICEBERG'
);

INSERT INTO customerdb.returnstbl_iceberg
SELECT *
FROM returnstbl_csv;  

SELECT * FROM customerdb.returnstbl_iceberg LIMIT 10;

To create the orders desk within the Redshift Serverless namespace, open the Question Editor v2 on the Amazon Redshift console.
Hook up with the default namespace utilizing your database admin consumer credentials.
Run the next instructions within the SQL editor to create the database ordersdb and desk orderstbl in it. Copy the information out of your S3 location of the orders knowledge to the orderstbl:

create database ordersdb;
use ordersdb;

create desk orderstbl(
  row_id int, 
  order_id VARCHAR, 
  order_date VARCHAR, 
  ship_date VARCHAR, 
  ship_mode VARCHAR, 
  customer_id VARCHAR, 
  customer_name VARCHAR, 
  phase VARCHAR, 
  metropolis VARCHAR, 
  state VARCHAR, 
  nation VARCHAR, 
  postal_code int, 
  market VARCHAR, 
  area VARCHAR, 
  product_id VARCHAR, 
  class VARCHAR, 
  sub_category VARCHAR, 
  product_name VARCHAR, 
  gross sales VARCHAR, 
  amount bigint, 
  low cost VARCHAR, 
  revenue VARCHAR, 
  shipping_cost VARCHAR, 
  order_priority VARCHAR
  );

copy orderstbl
from 's3:///ordersdatacsv/orders.csv' 
iam_role 'arn:aws:iam:::position/service-role/'
CSV 
DELIMITER ';'
IGNOREHEADER 1
;

choose * from ordersdb.orderstbl restrict 5;

In regards to the Authors

An IAM position, Glue-execution-role, within the client account, with the next insurance policies:

AWS managed insurance policies AWSGlueServiceRole and AmazonRedshiftDataFullAccess.

Create a brand new in-line coverage with the next permissions and fasten it:

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Sid": "LFandRSserverlessAccess",
            "Effect": "Allow",
            "Action": [
                "lakeformation:GetDataAccess",
                "redshift-serverless:GetCredentials"
            ],
            "Useful resource": "*"
        },
        {
            "Impact": "Permit",
            "Motion": "iam:PassRole",
            "Useful resource": "*",
            "Situation": {
                "StringEquals": {
                    "iam:PassedToService": "glue.amazonaws.com"
                }
            }
        }
    ]
}

Add the next belief coverage to Glue-execution-role, permitting AWS Glue to imagine this position:

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": [
                    "glue.amazonaws.com"
                ]
            },
            "Motion": "sts:AssumeRole"
        }
    ]
}

Steps for producer account setup

Configure your catalog

Full the next steps to arrange your catalog:

Log in to AWS Administration Console as Admin.
On the Amazon Redshift console, observe the directions in Registering Amazon Redshift clusters and namespaces to the AWS Glue Knowledge Catalog.
After the registration is initiated, you will notice the invite from Amazon Redshift on the Lake Formation console.
Choose the pending catalog invitation and select Approve and create catalog.

On the Set catalog particulars web page, configure your catalog:
1. For Title, enter a reputation (for this put up, redshiftserverless1-uswest2).
2. Choose Entry this catalog from Apache Iceberg appropriate engines.
3. Select the IAM position you created for the information switch.
4. Select Subsequent.
On the Grant permissions – elective web page, select Add permissions.
1. Grant the Admin consumer Tremendous consumer permissions for Catalog permissions and Grantable permissions.
2. Select Add.
Confirm the granted permission on the subsequent web page and select Subsequent.
Evaluate the main points on the Evaluate and create web page and select Create catalog.

Wait just a few seconds for the catalog to indicate up.

Select Catalogs within the navigation pane and confirm that the redshiftserverless1-uswest2 catalog is created.
Discover the catalog element web page to confirm the ordersdb.public database.
On the database View dropdown menu, view the desk and confirm that the orderstbl desk exhibits up.

Because the Admin position, you too can question the orderstbl in Amazon Athena and ensure the information is offered.

Grant permissions on the tables from the producer account to the patron account

On the Lake Formation console, select Knowledge permissions within the navigation pane.
Select Grant.
Underneath Principals, choose Exterior accounts.
Present the patron account ID.
Underneath LF-Tags or catalog assets, choose Named Knowledge Catalog assets.
For Catalogs, select the account ID that represents the default catalog.
For Databases, select customerdb.
Underneath Database permissions, choose Describe below Database permissions and Grantable permissions.
Select Grant.
Repeat these steps and grant table-level Choose and Describe permissions on returnstbl_iceberg.
Repeat these steps once more to grant database- and table-level permissions for the ordertbl desk of the federated catalog database redshiftserverless1-uswest2/ordersdb.

The next screenshots present the configuration for database-level permissions.

The next screenshots present the configuration for table-level permissions.

Select Knowledge permissions within the navigation pane and confirm that the patron account has been granted database- and table-level permissions for each orderstbl from the federated catalog and returnstbl_iceberg from the default catalog.

Register the Amazon S3 location of the returnstbl_iceberg with Lake Formation.

On this step, we register the Amazon S3 primarily based Iceberg desk returnstbl_iceberg knowledge location with Lake Formation to be managed by Lake Formation permissions. Full the next steps:

On the Lake Formation console, select Knowledge lake areas within the navigation pane.
Select Register location.
For Amazon S3 path, enter the trail in your S3 bucket that you simply supplied whereas creating the Iceberg desk returnstbl_iceberg.
For IAM position, present the user-defined position LakeFormationS3Registration_custom that you simply created as a prerequisite.
For Permission mode, choose Lake Formation.
Select Register location.
Select Knowledge lake areas within the navigation pane to confirm the Amazon S3 registration.

With this step, the producer account setup is full.

Steps for client account setup

For the patron account setup, we use the IAM admin position Admin, added as a Lake Formation administrator.

Settle for and confirm the shared assets

Log in to the patron AWS console and set the AWS Area to match the producer’s shared useful resource Area. For this put up, we use us-west-2.
Open the Lake Formation console. You will note a message indicating there’s a pending invite and asking you settle for it on the AWS RAM console.
Observe the directions in Accepting a useful resource share invitation from AWS RAM to overview and settle for the pending invitations.
When the invite standing modifications to Accepted, select Shared assets below Shared with me within the navigation pane.
Confirm that the Redshift Serverless federated catalog redshiftserverless1-uswest2, the default catalog database customerdb, the desk returnstbl_iceberg, and the producer account ID below Proprietor ID column show accurately.
On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Databases.
Search by the producer account ID.
It is best to see the customerdb and public databases. You may additional choose every database and select View tables on the Actions dropdown menu and confirm the desk names

Create a catalog hyperlink container and useful resource hyperlinks

On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Catalogs.
Select Create catalog.

Present the next particulars for the catalog:
1. For Title, enter a reputation for the catalog (for this put up, rl_link_container_ordersdb).
2. For Kind, select Catalog Hyperlink container.
3. For Supply, select Redshift.
4. For Goal Redshift Catalog, enter the Amazon Useful resource Title (ARN) of the producer federated catalog (arn:aws:glue:us-west-2:>:catalog/redshiftserverless1-uswest2/ordersdb).
5. Underneath Entry from engines, choose Entry this catalog from Apache Iceberg appropriate engines.
6. For IAM position, present the Redshift-S3 knowledge switch position that you simply had created within the conditions.
7. Select Subsequent.

On the Grant permissions – elective web page, select Add permissions.
1. Grant the Admin consumer Tremendous consumer permissions for Catalog permissions and Grantable permissions.
2. Select Add after which select Subsequent.

Evaluate the main points on the Evaluate and create web page and select Create catalog.

Wait just a few seconds for the catalog to indicate up.

Within the navigation pane, select Catalogs.
Confirm that rl_link_container_ordersdb is created.

Create a database below rl_link_container_ordersdb

Full the next steps:

On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Databases.
On the Select catalog dropdown menu, select rl_link_container_ordersdb.
Select Create database.

Alternatively, you’ll be able to select the Create dropdown menu after which select Database.

Present particulars for the database:
1. For Title, enter a reputation (for this put up, public_db).
2. For Catalog, select rl_link_container_ordersdb.
3. Go away Location – elective as clean.
4. Underneath Default permissions for newly created tables, deselect Use solely IAM entry management for brand spanking new tables on this database.
5. Select Create database.

Select Catalogs within the navigation pane to confirm that public_db is created below rl_link_container_ordersdb.

Create a desk useful resource hyperlink for the shared federated catalog desk

Full the next steps to create a desk useful resource hyperlink:

On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Tables.
On the Create dropdown menu, select Useful resource hyperlink.

Present particulars for the desk useful resource hyperlink:
1. For Useful resource hyperlink title, enter a reputation (for this put up, rl_orderstbl).
2. For Vacation spot catalog, select rl_link_container_ordersdb.
3. For Database, select public_db.
4. For Shared desk’s area, select US West (Oregon).
5. For Shared desk, select orderstbl.
6. After the Shared desk is chosen, Shared desk’s database and Shared desk’s catalog ID ought to get routinely populated.
7. Select Create.

Within the navigation pane, select Databases to confirm that rl_orderstbl is created below public_db, inside rl_link_container_ordersdb.

Create a database useful resource hyperlink for the shared default catalog database.

Full the next steps to create a database useful resource hyperlink:

On the Lake Formation console, below Knowledge Catalog within the navigation pane, select Databases.
On the Select catalog dropdown menu, select the account ID to decide on the default catalog.
Seek for customerdb.

It is best to see the shared database title customerdb with the Proprietor account ID as that of your producer account ID.

Choose customerdb, and on the Create dropdown menu, select Useful resource hyperlink.
Present particulars for the useful resource hyperlink:
1. For Useful resource hyperlink title, enter a reputation (for this put up, customerdb).
2. The remainder of the fields needs to be already populated.
3. Select Create.
Within the navigation pane, select Databases and confirm that customerdb is created below the default catalog. Useful resource hyperlink names will present in italicized font.

Confirm entry as Admin utilizing Athena

Now you’ll be able to confirm your entry utilizing Athena. Full the next steps:

Open the Athena console.
Ensure an S3 bucket is supplied to retailer the Athena question outcomes. For particulars, confer with Specify a question consequence location utilizing the Athena console.
Within the navigation pane, confirm each the default catalog and federated catalog tables by previewing them.
You can even run a be part of question as follows. Take note of the three-point notation for referring to the tables from two completely different catalogs:

SELECT
returns_tb.market as Market,
sum(orders_tb.amount) as Total_Quantity
FROM rl_link_container_ordersdb.public_db.rl_orderstbl as orders_tb
JOIN awsdatacatalog.customerdb.returnstbl_iceberg as returns_tb
ON orders_tb.order_id = returns_tb.order_id
GROUP BY returns_tb.market;

Grant permissions to Glue-execution-role

Useful resource hyperlinks permit solely Describe and Drop permissions. You should use the Grant on track configuration to supply database Describe and desk Choose permissions.

Full the next steps:

On the Lake Formation console, select Knowledge permissions within the navigation pane.
Select Grant.
Underneath Principals, choose IAM customers and roles.
For IAM customers and roles, enter Glue-execution-role.
Underneath LF-Tags or catalog assets, choose Named Knowledge Catalog assets.
For Catalogs, select rl_link_container_ordersdb and the patron account ID, which signifies the default catalog.
Underneath Catalog permissions, choose Describe for Catalog permissions.
Select Grant.

Repeat these steps for the catalog rl_link_container_ordersdb:
1. On the Databases dropdown menu, select public_db.
2. Underneath Database permissions, choose Describe.
3. Select Grant.
Repeat these steps once more, however after selecting rl_link_container_ordersdb and public_db, on the Tables dropdown menu, select rl_orderstbl.
1. Underneath Useful resource hyperlink permissions, choose Describe.
2. Select Grant.
Repeat these steps to grant extra permissions to Glue-execution-role.
1. For this iteration, grant Describe permissions on the default catalog databases public and customerdb.
2. Grant Describe permission on the useful resource hyperlink customerdb.
3. Grant Choose permission on the tables returnstbl_iceberg and orderstbl.

The next screenshots present the configuration for database public and customerdb permissions.

The next screenshots present the configuration for useful resource hyperlink customerdb permissions.

The next screenshots present the configuration for desk returnstbl_iceberg permissions.

The next screenshots present the configuration for desk orderstbl permissions.

Within the navigation pane, select Knowledge permissions and confirm permissions on Glue-execution-role.

Run a PySpark job in AWS Glue 5.0

Exchange > within the script together with your client account ID. Full the next steps to create and run an AWS Glue job:

On the AWS Glue console, within the navigation pane, select ETL jobs.
Select Create job, then select Script editor.

For Engine, select Spark.
For Choices, select Begin contemporary.
Select Add script.
Browse to the placement the place you downloaded and edited the script, choose the script, and select Open.
On the Job particulars tab, present the next info:
1. For Title, enter a reputation (for this put up, LakeHouseGlueSparkJob).
2. Underneath Primary properties, for IAM position, select Glue-execution-role.
3. For Glue model, choose Glue 5.0.
4. Underneath Superior properties, for Job parameters, select Add new parameter.
5. Add the parameters --datalake-formats = iceberg and --enable-lakeformation-fine-grained-access = true.
Save the job.
Select Run to execute the AWS Glue job, and anticipate the job to finish.
Evaluate the job run particulars from the Output logs

Clear up

To keep away from incurring prices in your AWS accounts, clear up the assets you created:

Delete the Lake Formation permissions, catalog hyperlink container, database, and tables within the client account.
Delete the AWS Glue job within the client account.
Delete the federated catalog, database, and desk assets within the producer account.
Delete the Redshift Serverless namespace within the producer account.
Delete the S3 buckets you created as a part of knowledge switch in each accounts and the Athena question outcomes bucket within the client account.
Clear up the IAM roles you created for the SageMaker Lakehouse setup as a part of the conditions.

Conclusion

Appendix: Desk creation

Full the next steps to create a returns desk within the Amazon S3 primarily based default catalog and an orders desk in Amazon Redshift:

Obtain the CSV format datasets orders and returns.
Add them to your S3 bucket below the corresponding desk prefix path.
Use the next SQL statements in Athena. First-time customers of Athena ought to confer with Specify a question consequence location.

CREATE DATABASE customerdb;
CREATE EXTERNAL TABLE customerdb.returnstbl_csv(
  `returned` string, 
  `order_id` string, 
  `market` string)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY ';' 
LOCATION
  's3:////'
TBLPROPERTIES (
  'skip.header.line.rely'='1'
);

choose * from customerdb.returnstbl_csv restrict 10;

Create an Iceberg format desk within the default catalog and insert knowledge from the CSV format desk:

CREATE TABLE customerdb.returnstbl_iceberg(
  `returned` string, 
  `order_id` string, 
  `market` string)
LOCATION 's3:///returnstbl_iceberg/' 
TBLPROPERTIES (
  'table_type'='ICEBERG'
);

INSERT INTO customerdb.returnstbl_iceberg
SELECT *
FROM returnstbl_csv;  

SELECT * FROM customerdb.returnstbl_iceberg LIMIT 10;

To create the orders desk within the Redshift Serverless namespace, open the Question Editor v2 on the Amazon Redshift console.
Hook up with the default namespace utilizing your database admin consumer credentials.
Run the next instructions within the SQL editor to create the database ordersdb and desk orderstbl in it. Copy the information out of your S3 location of the orders knowledge to the orderstbl:

create database ordersdb;
use ordersdb;

create desk orderstbl(
  row_id int, 
  order_id VARCHAR, 
  order_date VARCHAR, 
  ship_date VARCHAR, 
  ship_mode VARCHAR, 
  customer_id VARCHAR, 
  customer_name VARCHAR, 
  phase VARCHAR, 
  metropolis VARCHAR, 
  state VARCHAR, 
  nation VARCHAR, 
  postal_code int, 
  market VARCHAR, 
  area VARCHAR, 
  product_id VARCHAR, 
  class VARCHAR, 
  sub_category VARCHAR, 
  product_name VARCHAR, 
  gross sales VARCHAR, 
  amount bigint, 
  low cost VARCHAR, 
  revenue VARCHAR, 
  shipping_cost VARCHAR, 
  order_priority VARCHAR
  );

copy orderstbl
from 's3:///ordersdatacsv/orders.csv' 
iam_role 'arn:aws:iam:::position/service-role/'
CSV 
DELIMITER ';'
IGNOREHEADER 1
;

choose * from ordersdb.orderstbl restrict 5;

In regards to the Authors

Configure cross-account entry of Amazon SageMaker Lakehouse multi-catalog tables utilizing AWS Glue 5.0 Spark

Scalable analytics and centralized governance for Apache Iceberg tables utilizing Amazon S3 Tables and Amazon Redshift

Writer Quicker, Smarter AI/BI Dashboards with Dynamic Calculations

NVIDIA Pronounces DGX Cloud Lepton for GPU Entry throughout Multi-Cloud Platforms

swissnewspaper

Related Posts

Scalable analytics and centralized governance for Apache Iceberg tables utilizing Amazon S3 Tables and Amazon Redshift

Writer Quicker, Smarter AI/BI Dashboards with Dynamic Calculations

NVIDIA Pronounces DGX Cloud Lepton for GPU Entry throughout Multi-Cloud Platforms

AI Improves Integrity in Company Accounting

Democracy.exe: When Exponential Tech Crashes the Human Thoughts

Saying new fine-tuning fashions and strategies in Azure AI Foundry

Amazon And Goodreads: The Hidden Monopoly Shaping Ebook Gross sales

Earth911 Inspiration: Change At The Velocity Of Belief

Recommended Stories

Espresso Break: Armed Madhouse – Missile Misfit

Retail Buying and selling Development in Perspective

How I Arrange Advertising and marketing for an eCommerce Delivery and Achievement Firm as Their Fractional CMO

Popular Stories

Eat Clear Assessment: Is This Meal Supply Service Value It?

RBI panel suggests extending name cash market timings to 7 p.m.

Working from home is the new normal as we combat the Covid-19

Dataiku Brings AI Agent Creation to AI Platform

The Significance of Using Instruments like AI-Primarily based Analytic Options

About Us

Categories

Recent News

Are you sure want to unlock this post?

Are you sure want to cancel subscription?

Configure cross-account entry of Amazon SageMaker Lakehouse multi-catalog tables utilizing AWS Glue 5.0 Spark

Steps for producer account setup

Configure your catalog

Grant permissions on the tables from the producer account to the patron account

Register the Amazon S3 location of the returnstbl_iceberg with Lake Formation.

Steps for client account setup

Settle for and confirm the shared assets

Create a catalog hyperlink container and useful resource hyperlinks

Create a database below rl_link_container_ordersdb

Create a desk useful resource hyperlink for the shared federated catalog desk

Create a database useful resource hyperlink for the shared default catalog database.

Confirm entry as Admin utilizing Athena

Grant permissions to Glue-execution-role

Run a PySpark job in AWS Glue 5.0

Clear up

Conclusion

Appendix: Desk creation

In regards to the Authors

Steps for producer account setup

Configure your catalog

Grant permissions on the tables from the producer account to the patron account

Register the Amazon S3 location of the returnstbl_iceberg with Lake Formation.

Steps for client account setup

Settle for and confirm the shared assets

Create a catalog hyperlink container and useful resource hyperlinks

Create a database below rl_link_container_ordersdb

Create a desk useful resource hyperlink for the shared federated catalog desk

Create a database useful resource hyperlink for the shared default catalog database.

Confirm entry as Admin utilizing Athena

Grant permissions to Glue-execution-role

Run a PySpark job in AWS Glue 5.0

Clear up

Conclusion

Appendix: Desk creation

In regards to the Authors

RELATED POSTS

Steps for producer account setup

Configure your catalog

Grant permissions on the tables from the producer account to the patron account

Register the Amazon S3 location of the returnstbl_iceberg with Lake Formation.

Steps for client account setup

Settle for and confirm the shared assets

Create a catalog hyperlink container and useful resource hyperlinks

Create a database below rl_link_container_ordersdb

Create a desk useful resource hyperlink for the shared federated catalog desk

Create a database useful resource hyperlink for the shared default catalog database.

Confirm entry as Admin utilizing Athena

Grant permissions to Glue-execution-role

Run a PySpark job in AWS Glue 5.0

Clear up

Conclusion

Appendix: Desk creation

In regards to the Authors

Steps for producer account setup

Configure your catalog

Grant permissions on the tables from the producer account to the patron account

Register the Amazon S3 location of the returnstbl_iceberg with Lake Formation.

Steps for client account setup

Settle for and confirm the shared assets

Create a catalog hyperlink container and useful resource hyperlinks

Create a database below rl_link_container_ordersdb

Create a desk useful resource hyperlink for the shared federated catalog desk

Create a database useful resource hyperlink for the shared default catalog database.

Confirm entry as Admin utilizing Athena

Grant permissions to Glue-execution-role

Run a PySpark job in AWS Glue 5.0

Clear up

Conclusion

Appendix: Desk creation

In regards to the Authors

Related Posts

Recommended Stories

Popular Stories

About Us

Categories

Recent News

Are you sure want to unlock this post?

Are you sure want to cancel subscription?