Querying a data lake - Amazon Redshift
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Querying a data lake

You can query data in an Amazon S3 data lake. First, you create an external schema to reference the external database in the Amazon Glue Data Catalog. Then, you can query data in the Amazon S3 data lake.

Demo: Query a data lake

To learn how to query a data lake, watch the following video.


You create an IAM role to access an Amazon Glue Data Catalog enabled for Amazon Lake Formation.

  1. Open the IAM console at https://console.amazonaws.cn/iam/.

  2. In the navigation pane, choose Policies.

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

  3. Choose Create policy.

  4. Choose to create the policy on the JSON tab.

  5. Paste in the following JSON policy document, which grants access to the Data Catalog but denies the administrator permissions for Lake Formation.

    { "Version": "2012-10-17", "Statement": [ { "Sid": "RedshiftPolicyForLF", "Effect": "Allow", "Action": [ "glue:*", "lakeformation:GetDataAccess" ], "Resource": "*" } ] }
  6. When you are finished, choose Review to review the policy. The policy validator reports any syntax errors.

  7. On the Review policy page, for Name enter a name for the policy that you are creating, for example, mydatalake_policy. 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.

    After you create a policy, you can create a role and apply the policy.

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

  9. For Select type of trusted entity, choose Amazon service.

  10. Choose the Amazon Redshift service to assume this role.

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

  12. Choose the permissions policy that you created, mydatalake_policy, to attach to the role.

  13. Choose Next: Tagging.

  14. Choose Next: Review.

  15. For Role name, enter a name for the role, for example, mydatalake_role.

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

  17. Review the role, and then choose Create role.

You grant SELECT permissions on the table to query in the Lake Formation database.

  1. Open the Lake Formation console at https://console.amazonaws.cn/lakeformation/.

  2. In the navigation pane, choose Permissions, and then choose Grant.

  3. Provide the following information:

    • For IAM role, choose the IAM role you created, myspectrum_role. When you run the Amazon Redshift query editor, it uses this IAM role for permission to the data.


      To grant SELECT permission on the table in a Lake Formation–enabled Data Catalog to query, do the following:

      • Register the path for the data in Lake Formation.

      • Grant users permission to that path in Lake Formation.

      • Created tables can be found in the path registered in Lake Formation.

    • For Database, choose your Lake Formation database.

    • For Table, choose a table within the database to query.

    • For Columns, choose All Columns.

    • Choose the Select permission.

  4. Choose Save.


As a best practice, allow access only to the underlying Amazon S3 objects through Lake Formation permissions. To prevent unapproved access, remove any permission granted to Amazon S3 objects outside of Lake Formation. If you previously accessed Amazon S3 objects before setting up Lake Formation, remove any IAM policies or bucket permissions that previously were set up. For more information, see Upgrading Amazon Glue Data Permissions to the Amazon Lake Formation Model and Lake Formation Permissions.

Creating the external schema

To query data in an Amazon S3 data lake, you create an external schema. The external schema references the external database in the Amazon Glue Data Catalog.

  1. Choose 
                            CreateCreate, and then choose Schema.

  2. Enter a schema name.

  3. To grant ownership of the database to a user, choose Authorize user and choose a user.

  4. Choose External.

  5. Under Amazon Glue Data Catalog details, Region defaults to the Region where your Redshift database is located.

  6. Choose the Amazon Glue database that the external schema will map to.

  7. Choose an IAM role that has the required permissions to query data on Amazon S3.

  8. Choose Create schema.

    The schema appears in the database browser.

Querying data in your Amazon S3 data lake

You use the schema that you created in the previous procedure.

  1. In the database browser, choose the schema.

  2. To view a table definition, choose a table.

    The table columns and data types display.

  3. To query a table, choose the table and use the context menu (right-click) to choose Select table.