Step 12: Query the data in the data lake using Amazon Redshift Spectrum

Open the IAM console at https://console.amazonaws.cn/iam/. Sign in as the administrator user that you created in (user name Administrator) or as a user with the AdministratorAccess Amazon managed policy.

In the navigation pane, choose Policies.

If this is your first time choosing Policies, the Welcome to Managed Policies page appears. Choose Get Started.

Choose Create policy.

Choose the JSON tab.

Paste in the following JSON policy document.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "lakeformation:GetDataAccess",
                "glue:GetTable",
                "glue:GetTables",
                "glue:SearchTables",
                "glue:GetDatabase",
                "glue:GetDatabases",
                "glue:GetPartitions",
                "lakeformation:GetResourceLFTags",
                "lakeformation:ListLFTags",
                "lakeformation:GetLFTag",
                "lakeformation:SearchTablesByLFTags",
                "lakeformation:SearchDatabasesByLFTags"                
           ],
            "Resource": "*"
        }
    ]
}

When you are finished, choose Review to review the policy. The policy validator reports any syntax errors.

On the Review policy page, enter the Name as RedshiftLakeFormationPolicy for the policy that you are creating. Enter a Description (optional). Review the policy Summary to see the permissions that are granted by your policy. Then choose Create policy to save your work.

In the navigation pane of the IAM console, choose Roles, and then choose Create role.

For Select trusted entity, choose Amazon service.

Choose the Amazon Redshift service to assume this role.

Choose the Redshift Customizable use case for your service. Then choose Next: Permissions.

Search for the permissions policy that you created, RedshiftLakeFormationPolicy, and select the check box next to the policy name in the list.

Choose Next: Tags.

Choose Next: Review.

For Role name, enter the name RedshiftLakeFormationRole.

(Optional) For Role description, enter a description for the new role.

Review the role, and then choose Create role.

Open the Lake Formation console at https://console.amazonaws.cn/lakeformation/. Sign in as the data lake administrator.

In the navigation pane, under Permissions, choose Data lake permissions, and then choose Grant.

Provide the following information:

Choose Grant.

Open the Amazon Redshift console at https://console.amazonaws.cn/redshift. Sign in as the user Administrator.

Choose Create cluster.

On the Create cluster page, enter redshift-lakeformation-demo for the Cluster identifier.

For the Node type, select dc2.large.

Scroll down, and under Database configurations, enter or accept these parameters:

Expand Cluster permissions, and for Available IAM roles, choose RedshiftLakeFormationRole. Then choose Add IAM role.

If you must use a different port than the default value of 5439, next to Additional configurations, turn off the Use defaults option. Expand the section for Database configurations, and enter a new Database port number.

Choose Create cluster.

The Clusters page loads.

Wait until the cluster status becomes Available. Choose the refresh icon periodically.

Grant the data analyst permission to run queries against the cluster. To do so, complete the following steps.

1. Open the IAM console at https://console.amazonaws.cn/iam/, and sign in as the Administrator user.

2. In the navigation pane, choose Users, and attach the following managed policies to the user datalake_user.

   • AmazonRedshiftQueryEditor

   • AmazonRedshiftReadOnlyAccess

Sign out of the Amazon Redshift console and sign back in as user datalake_user.

In the left vertical toolbar, choose the EDITOR icon to open the query editor and connect to the cluster. If the Connect to database dialog box appears, choose the cluster name redshift-lakeformation-demo, and enter the database name dev, the user name awsuser, and the password that you created. Then choose Connect to database.

In the New Query 1 text box, enter and run the following statement to map the database lakeformation_tutorial in Lake Formation to the Amazon Redshift schema name redshift_jdbc:

create external schema if not exists redshift_jdbc from DATA CATALOG database 'lakeformation_tutorial' iam_role 'arn:aws:iam::<account-id>:role/RedshiftLakeFormationRole' region '<region>';

In the schema list under Select schema, choose redshift_jdbc.

The tables list populates. The query editor shows only the tables on which you were granted Lake Formation data lake permissions.

On the pop-up menu next to a table name, choose Preview data.

Amazon Redshift returns the first 10 rows.

You can now run queries against the tables and columns for which you have permissions.