Can I force Redshift not to use Lake Formation permissions for a specific external schema?

user_90580 0
Viewed 820

I want to create an external schema like

create external schema ruben_external
from data catalog
database 'ruben_external'
region 'eu-north-1'
iam_role 'arn:aws:iam::xxxxxxxx:role/ruben_redshift_external'
create external database if not exists ;

create external table ruben_external.ruben_manifest_test
(
    customer_id bigint,
    external_cust_id varchar(30)

row format serde 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
with serdeproperties('serialization.format'='1')
stored as
inputformat 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
outputformat 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
location 's3://mybucket/folder1/LATEST_redshift_external_location_manifest.json'
;

In my case the IAM role ruben_redshift_external

  • has a policy attached with full access to S3, glue and lakeformation
  • is granted in Lake Formation permissions to create databases, select the table and access to the data lake location

but the fact that the role has direct access to S3 is ignored by Redshift, it seems to always use Lake Formation to get some temporary credentials to access S3.

I would like to disable the Lake Formation resolve step for this particular schema or redshift instance if it's possible mainly to help me debug. The external table I'm creating uses a manifest file as location (the manifest points to multiple parquet files), it seems that Lake Formation provides credentials that allow redshift to read the manifest file but not the files pointed by the manifest and I was hoping to confirm that. See related question

amazon-redshift-spectrum amazon-redshift
1 Answers

First of all, Lake Formation does NOT support Redshift queries using manifest as stated in AWS Service integrations with Lake Formation.

If the LOCATION of a Redshift External Table is an S3 path that is part of a AWS Lake Formation "Data lake location" then Redshift will always use the LakeFormation provided temporary credentials to access it.

So if you don't want to use Lake Formation for a specific external table, then you need to put the manifest and data files in a location not registered to Lake Formation there is NO way around it.

You can retrieve the currently registered locations (data lake locations) using the AWS Console > AWS Lake Formation > Register and ingest > Data lake locations or with the AWS CLI command aws lakeformation list-resources

Step by step instructions to create a Redshift external table that uses the IAM role rubelagu_redshift_test to access the files in S3 (as opposed to using the IAM role to get some other temporary credentials from Lake Formation):

  • Create a new S3 bucket (or reuse a S3 bucket/path not registered to AWS Lake Formation)
  • Upload the manifest and data files to it
    • s3://mynewbucket/rubelagu-redshift-test/folder1/_manifest.txt
      • example content {"entries": [{"url": "s3://mynewbucket/rubelagu-redshift-test/folder2/file1.parquet", "mandatory": true, "meta": {"content_length": 8066055}}]}
    • s3://mynewbucket/rubelagu-redshift-test/folder2/file1.parquet
  • in IAM create a new IAM role
  • In AWS Lake Formation you need to make this new IAM role a Database Creator because later we will run CREATE EXTERNAL SCHEMA ... FROM DATA CATALOG ... CREATE DATABASE so the role needs to be able to create a database in the data catalog.
    • AWS Lake Formation > Permissions > Administrative roles and tasks > Database Creators > Grant > rubelagu_redshift_test -> Create Database
  • In Redshift associate this IAM role with the cluster
    • Redshift > your cluster > Properties > Cluster permissions > Manage IAM roles > Associate IAM role and Save Changes
    • wait until it transition from Not Applied to adding and finally to in-sync
  • Create the external schema in Redshift (remember the IAM role need to be associated with the Redshift cluster and need to be setup as a Database Creator in Lake Formation for it to work)
create external schema rubelagu_redshift_test
from data catalog
database 'rubelagu_redshift_test'
region 'eu-north-1'
iam_role 'arn:aws:iam::xxxxxxxx:role/rubelagu_redshift_test'
create external database if not exists;
  • Create the external table in that schema with
create external table rubelagu_redshift_test.ruben_manifest_test
(
    customer_id bigint,
    external_cust_id varchar(30),
)
row format serde 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
with serdeproperties('serialization.format'='1')
stored as
inputformat 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
outputformat 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
location 's3://mynewbucket/rubelagu-redshift-test/folder1/_manifest.txt'
;
  • Now you can test with select count(*) from rubelagu_redshift_test.ruben_manifest_test;

If you follow those steps you should get a Redshift external table that uses the IAM role rubelagu_redshift_test to access the files in S3 (as opposed to using the IAM role to get some other temporary credentials from Lake Formation). Again, the key is that the external table location is in a S3 path that does NOT belong to any Lake Formation registered locations

user_905800
Related