Shantanu's Blog

Database Consultant

April 29, 2024

Partitioning and bucketing S3 data

Let's assume we need to optimize this query. The data is stored in S3 (around a few TB)

SELECT * FROM "bucketing_blog"."noaa_remote_original"
WHERE
report_type = 'CRN05'
AND ( station = '99999904237'
OR station = '99999953132'
OR station = '99999903061'
OR station = '99999963856'
OR station = '99999994644'
);

There are around 14325 unique stations and report_type column contain around 13 types.

1) The first thing that can be tried is to create a table with partitioning (using report_type)

CREATE TABLE "bucketing_blog"."athena_non_bucketed"
WITH (
external_location = 's3://<your-s3-location>/athena-non-bucketed/',
partitioned_by = ARRAY['report_type'],
format = 'PARQUET',
write_compression = 'SNAPPY'
)
AS
SELECT * FROM "bucketing_blog"."noaa_remote_original";

If you need faster and cheaper query, use buckets

CREATE TABLE "bucketing_blog"."athena_bucketed"
WITH (
external_location = 's3://<your-s3-location>/athena-bucketed/',
partitioned_by = ARRAY['report_type'],
bucketed_by = ARRAY['station'],
bucket_count = 16,
format = 'PARQUET',
write_compression = 'SNAPPY'
)
AS
SELECT * FROM "bucketing_blog"."noaa_remote_original"

I created 16 buckets because that is the maximum number of stations that may appear in the SQL query.

https://aws.amazon.com/blogs/big-data/optimize-data-layout-by-bucketing-with-amazon-athena-and-aws-glue-to-accelerate-downstream-queries/

Labels: athena, aws

posted by shantanu # Monday, April 29, 2024 0 comments

July 06, 2021

Quick test tables in Athena

If you quickly ant to test data using athena query, use with syntax as shown below:

WITH countries(country_code) AS (VALUES 'pol', 'CAN', 'USA')
SELECT upper(country_code) AS country_code
FROM countries

In this example aggregate expression uses "OVER" function.

WITH students_results(student_id, result) AS (VALUES
('student_1', 17),
('student_2', 16),
('student_3', 18),
('student_4', 18),
('student_5', 10),
('student_6', 20),
('student_7', 16))
SELECT
student_id,
result,
count(*) OVER (
ORDER BY result
) AS close_better_scores_count
FROM students_results

Labels: athena, aws

posted by shantanu # Tuesday, July 06, 2021 0 comments

May 28, 2021

Athena and Unicode text

Athena supports unicode characters very well. For e.g. if the datafile looks like this...

"Root_word";"Word";"Primary";"Type";"Code";"Position";"Rule"
"अँटिबायोटिक","अँटिबायोटिक","अँटिबायोटिक","Primary","","",""
"अँटिबायोटिक","अँटिबायोटिकअंती","अँटिबायोटिक","Suffix","A","7293","001: 0 अंती ."
"अँटिबायोटिक","अँटिबायोटिकअर्थी","अँटिबायोटिक","Suffix","A","7293","002: 0 अर्थी ."
"अँटिबायोटिक","अँटिबायोटिकआतून","अँटिबायोटिक","Suffix","A","7293","003: 0 आतून ."
"अँटिबायोटिक","अँटिबायोटिकआतूनचा","अँटिबायोटिक","Suffix","A","7293","004: 0 आतूनचा ."
"अँटिबायोटिक","अँटिबायोटिकआतूनची","अँटिबायोटिक","Suffix","A","7293","005: 0 आतूनची ."
"अँटिबायोटिक","अँटिबायोटिकआतूनचे","अँटिबायोटिक","Suffix","A","7293","006: 0 आतूनचे ."
"अँटिबायोटिक","अँटिबायोटिकआतूनच्या","अँटिबायोटिक","Suffix","A","7293","007: 0 आतूनच्या ."
"अँटिबायोटिक","अँटिबायोटिकआतूनला","अँटिबायोटिक","Suffix","A","7293","008: 0 आतूनला ."

This create table statement is all I need...

create external table myptg (
root_word varchar(255),
derived_word varchar(255),
stemmed_word varchar(255),
type varchar(255),
code varchar(255),
position varchar(255),
rule varchar(255)
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
'separatorChar' = '\;',
'quoteChar' = '\"',
'escapeChar' = '\\'
)
LOCATION 's3://ptg1/mc/'
TBLPROPERTIES ("skip.header.line.count"="1");

I can create a supporting table like this...

create external table gamabhana (derived_word varchar(255))
LOCATION 's3://ptg1/mc2/'
TBLPROPERTIES ("skip.header.line.count"="1");

A new table can be created using the syntax something like this...

create external table anoop (
serial_number int,
root_word varchar(255),
stem1_word varchar(255),
stem2_word varchar(255),
stem3_word varchar(255),
stem4_word varchar(255),
stem5_word varchar(255),
stem6_word varchar(255),
stem7_word varchar(255)
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'field.delim' = ',',
'escape.delim' = '\\',
'line.delim' = '\n'
)
LOCATION 's3://ptg1/mc3/'
TBLPROPERTIES ("skip.header.line.count"="1");

And then run a join statement like this...

create table gamabhana_match as
select a.derived_word, b.root_word, b.stemmed_word, b.type, b.code, b.position, b.rule, c.stem1_word, c.stem2_word, c.stem3_word, c.stem4_word, c.stem5_word, c.stem6_word, c.stem7_word
from gamabhana as a left join myptg as b
on b.derived_word = a.derived_word
left join anoop as c
on c.derived_word = a.derived_word

It will scan around 2 GB data (in this case) and the cost will be around 1 cent per query. This can also be done in MySQL. But importing data and building indexes is not easy. Unlike Athena, MySQL allows unlimited queries for free!

Athena is good for data that is important and accessed rarely.

Labels: athena, aws, usability

posted by shantanu # Friday, May 28, 2021 0 comments

April 17, 2021

crawl indian language websites

CommonCrawl builds and maintains an open repository of web crawl data that can be accessed and analyzed by anyone.

https://commoncrawl.org/

Here are 3 easy steps to download the data in any language, for e.g. Marathi or Gujarati

1) Clone the repo git clone https://github.com/qburst/common-crawl-malayalam.git

2) Change language code from mal to mar at the line -
AND content_languages LIKE 'mal%'"

The other codes are: hin (Hindi), mal (Malayalam), mar (Marathi), guj (Gujarati)

Run this shell script after replacing XXX with your AWS access and security key.

./extract_malayalam_warcs.sh XXX XXX 2021-10 s3://nlp-malayalam

3) Change unicode range from mal: range(3328, 3456) to your own desired language range, for e.g. devnagari: range(2304, 2432) Gujarati: range(2688, 2816)

Run this shell script after replacing XXX with your AWS access and security key.

./filter_malayalam.sh XXX XXX s3://nlp-malayalam/2021-10/warcs s3://nlp-malayalam/2021-10/output2

Filtered and unfiltered data will be available in output2 sub-folder of s3 bucket.

_____

Update:

The good French guys from Sorbonne University and inria have extracted sentences from common crawler for all languages and made them available at

https://oscar-corpus.com/

I have no words to thank them. The paper is available here...

http://corpora.ids-mannheim.de/CMLC7-final/CMLC-7_2019-Oritz_et_al.pdf

Without this data, it would have been very difficult to extract reliable data for languages like Marathi, Gujarati.

Labels: athena, aws, python, shell script

posted by shantanu # Saturday, April 17, 2021 0 comments

October 24, 2020

Using Bucketing in Amazon Athena

To reduce the data scan cost, AWS Athena provides an option to bucket your data. This optimization technique can perform wonders on reducing cost.

Like partitioning, columns that are frequently used to filter the data are good candidates for bucketing. However, unlike partitioning, with bucketing it’s better to use columns with high cardinality as a bucketing key. For example, Year and Month columns are good candidates for partition keys, whereas userID and sensorID are good examples of bucket keys. By doing this, you make sure that all buckets have a similar number of rows.

Bucketing is a technique that groups data based on specific columns together within a single partition. These columns are known as bucket keys. By grouping related data together into a single bucket (a file within a partition), you significantly reduce the amount of data scanned by Athena, thus improving query performance and reducing cost.

For example, imagine collecting and storing clickstream data. If you frequently filter or aggregate by Sensor ID, then within a single partition it’s better to store all rows for the same sensor together.

CREATE TABLE TargetTable
WITH (
format = 'PARQUET',
external_location = 's3://<s3_bucket_name>/curated/',
partitioned_by = ARRAY['dt'],
bucketed_by = ARRAY['sensorID'],
bucket_count = 3)
AS SELECT *
FROM SourceTable

You can run the select query like this:

select * from TargetTable where dt= '2020-08-04-21' and sensorID = '1096'

Labels: athena, aws

posted by shantanu # Saturday, October 24, 2020 0 comments

August 24, 2020

Manage Athena tables using python module wrangler

# create a boto session

import boto3
my_session = boto3.Session(
aws_access_key_id="xxx",
aws_secret_access_key="xxx",
region_name="us-east-1",
)

# use wrangler to upload parquet files to S3
# use the database created in glue

import awswrangler as wr
wr.s3.to_parquet(
df=df,
path="s3://test1623/noaa/",
dataset=True,
database="awswrangler_test",
table="noaa",
partition_cols=["year"],
boto3_session=my_session,
)

# Read Athena query into pandas dataframe
df = wr.athena.read_sql_query(
"select * from noaa limit 10", database="awswrangler_test", boto3_session=my_session
)

https://aws.amazon.com/blogs/big-data/optimize-python-etl-by-extending-pandas-with-aws-data-wrangler/

Labels: athena, aws

posted by shantanu # Monday, August 24, 2020 0 comments

July 31, 2020

Using Athena to preview large S3 files

Athena can also be used for quickly previewing the csv or text files saved in S3.

# vi symlink.txt

s3://some_bucket/abc_servers_backup/bulk_205_aws/db/xyz_no_details_202006.csv.gz

As you can see, I have saved the entire path in a symbolic links text file and uploading it to S3 using this command...

aws s3 cp symlink.txt s3://132cols/symlink/

I will now use the path of symbolic links file while creating the table as shown below.

CREATE EXTERNAL TABLE test_bulk (

dummy_col string)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '~'

ESCAPED BY '\\'

LINES TERMINATED BY '\n'

STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'

OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'

LOCATION 's3://132cols/symlink/'

TBLPROPERTIES ('has_encrypted_data'='false');

The important point to note is that I have changed the comma , in "FIELDS TERMINATED BY" parameter to tile ~ or else I would get only the first column. Due to this change, I can see the entire row in a single column called "dummy_col". This tip will be useful when you want to check what's in a large file without downloading it first. This method can also be used to re-check if the file that you have uploaded to S3 is valid and readable.

Labels: athena, aws

posted by shantanu # Friday, July 31, 2020 0 comments

April 28, 2020

More about Athena query

Here is how we can run a single query across multiple regions.

-- Create a table with a single id column
CREATE EXTERNAL TABLE ids(id bigint)
PARTITIONED BY (region string)
ROW FORMAT DELIMITED

-- Add two partitions, use two buckets from two regions
ALTER TABLE ids ADD PARTITION (region='us')
LOCATION 's3://my-us-bucket/ids'
ALTER TABLE ids ADD PARTITION (region='eu')
LOCATION 's3://my-eu-bucket/ids'

-- Count the distinct ids, in all regions
SELECT COUNT(DISTINCT id)
FROM ids

https://aws.amazon.com/blogs/apn/running-sql-on-amazon-athena-to-analyze-big-data-quickly-and-across-regions/

Labels: athena

posted by shantanu # Tuesday, April 28, 2020 0 comments

April 13, 2020

Redshift Spectrum

Athena and redshift both are great database. But there are times when we need a bridge to connect them. For e.g. when we need to join a redshift table with Athena. Redshift spectrum can be used in such cases.

1) Create an IAM role called "RedshiftCopyUnload" using the cloudformation template shown in this example:

https://stackoverflow.com/questions/58816446/template-to-create-iam-role-for-spectrum-s3-access

2) Create database using the Role created in the first step:

create external schema spectrum_schema from data catalog
database 'spectrum_db'
iam_role 'arn:aws:iam::XXX:role/RedshiftCopyUnload'
create external database if not exists;

3) Create external table:

create external table spectrum_schema.testme (number bigint)
row format delimited fields terminated by '|'
stored as textfile
location 's3://texport/c_pincode_data/';

4) Create internal table native to redshift using select query on external table:

create table mypincode as select * from spectrum_schema.testme limit 10;

Labels: athena, redshift

posted by shantanu # Monday, April 13, 2020 0 comments

March 12, 2020

Analyze S3 storage usage using inventory

1) Enable inventory for the bucket
2) Create a table using athena to read the inventory data
3) Run a select query

1) Enable inventory using boto as shown in this stack thread:

https://stackoverflow.com/questions/60615911/use-boto-to-enable-inventory

2) Replace $InventoryBucket, $InventoryPrefix, $Bucket, and $InventoryName with the configuration.

CREATE EXTERNAL TABLE inventory(
bucket string,
key string,
size bigint,
last_modified_date timestamp,
e_tag string,
storage_class string,
is_multipart_uploaded boolean,
replication_status string,
encryption_status string
)
PARTITIONED BY (dt string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde'
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.SymlinkTextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.IgnoreKeyTextOutputFormat'
LOCATION 's3://$InventoryBucket/$InventoryPrefix/$Bucket/$InventoryName/hive';

The location URL will look something like this...

LOCATION 's3://testme16/invent/testme16/myinventory/hive';

Repair table to read partitions:

MSCK REPAIR TABLE inventory;

3) Replace $Yesterday with yesterday’s timestamp (e.g., 2020-03-03-00-00):

SELECT prefix, SUM(size)/1000/1000/1000 AS total_size FROM (
SELECT regexp_extract(i.key, '([^\/]*\/).*', 1) AS prefix, i.size
FROM inventory AS i WHERE i.dt = '$Yesterday'
) GROUP BY prefix ORDER BY total_size DESC;

https://cloudonaut.io/how-to-analyze-and-reduce-s3-storage-usage/

Labels: athena, aws

posted by shantanu # Thursday, March 12, 2020 0 comments

February 19, 2020

Manage athena tables using python

Here is 4 or 5 lines of code to read data from athena table into pandas dataframe.

from pyathena import connect

from pyathena.pandas.util import to_sql

bucket = ‘my_bucket_name’

conn = connect(

aws_access_key_id=access,

aws_secret_access_key=secret,

s3_staging_dir="s3://" + bucket + "/tutorial/staging/",

region_name="us-east-1",

)

ndf = pd.read_sql("SELECT * FROM sampledb.todel limit 100", conn)

# pandas dataframe to Athena table

to_sql(ndf, "sample_table", conn, "s3://" + bucket + "/tutorial/s3dir/",

schema="sampledb", index=False, if_exists="replace")

_____

The following script will display output of "show create table" command for all tables. It will also create a new excel file called "output.xlsx". 10 records from each table will be saved on separate sheets of the file. Running this script is recommended to learn more about the tables you have saved in Athena.

import pandas as pd

from pyathena import connect

from pyathena.pandas.util import to_sql

conn = connect(

aws_access_key_id="XXX", aws_secret_access_key="XXX",

s3_staging_dir="s3://as-athena-qquery-results-5134690XXXX-us-east-1/todel/",

region_name="us-east-1",

)

dbname = pd.read_sql("show databases", conn)

mydict = dict()

for db in dbname["database_name"]:

mydict[db] = pd.read_sql("show tables in {0}".format(db), conn)

newdict = dict()

for k in mydict.keys():

for i in mydict[k].values:

newdict["{0}.{1}".format(k, i[0])] = pd.read_sql("show create table {0}.{1}".format(k, i[0]), conn)

# print the create table output:

for i in newdict.keys():

for x in newdict[i].values:

print(x[0])

print("\n")

# select 10 records from each table and save as excel sheets:

datadict = dict()

for k in mydict.keys():

for i in mydict[k].values:

try:

datadict["{0}.{1}".format(k, i[0])] = pd.read_sql( "select * from {0}.{1} limit 10".format(k, i[0]), conn)

except:

pass

with pd.ExcelWriter("output.xlsx") as writer:

for k, v in datadict.items():

v.to_excel(writer, sheet_name=k)

Labels: athena, aws, pandas, python

posted by shantanu # Wednesday, February 19, 2020 0 comments

Import csv data and convert it to parquet in athena

# Let's assume we have a large file that we need to import in athena, here are the commands to be used.

# gunzip -c panindia_pincode.csv.gz | head
"1","110016","DELHI","DELHI","","","",""
"2","110027","DELHI","DELHI","","","",""
"3","110062","DELHI","DELHI","","","",""

# create a table in athena

CREATE EXTERNAL TABLE pandindia_pincode (
serial_number string,
pincode_number string,
client_city string,
client_state string,
dummy1 string,
dummy2 string,
dummy3 string,
dummy4 string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
'serialization.format' = ',',
'field.delim' = ',',
"quoteChar" = "\""
)
LOCATION 's3://datameetgeo/pincode/'
TBLPROPERTIES ('has_encrypted_data'='false');

## create parquet file format table

CREATE TABLE default.pandindia_pincode_parq
with (format='PARQUET', external_location='s3://datameetgeo/parquetpincode/'
) AS
SELECT * FROM default.pandindia_pincode

Labels: athena, aws, redshift

posted by shantanu # Wednesday, February 19, 2020 0 comments

February 16, 2020

Using partitions in Athena table

Here is a create table query that will create a partitioned table. The column "product_category" that is used for partitions, is not part of create table statement and still shows up in the select query.

CREATE EXTERNAL TABLE `reviews`(
`marketplace` varchar(10),
`customer_id` varchar(15),
`review_id` varchar(15),
`product_id` varchar(25),
`product_parent` varchar(15),
`product_title` varchar(50),
`star_rating` int,
`helpful_votes` int,
`total_votes` int,
`vine` varchar(5),
`verified_purchase` varchar(5),
`review_headline` varchar(25),
`review_body` varchar(1024),
`review_date` date,
`year` int)
PARTITIONED BY (
`product_category` varchar(25))
ROW FORMAT SERDE
'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
's3://amazon-reviews-pds/parquet'

In order to populate the table, we will need to use the alter table statement.

ALTER TABLE reviews ADD
partition(product_category='Apparel')
location 's3://amazon-reviews-pds/parquet/product_category=Apparel/'
partition(product_category='Automotive')
location 's3://amazon-reviews-pds/parquet/product_category=Automotive'
partition(product_category='Baby')
location 's3://amazon-reviews-pds/parquet/product_category=Baby'
partition(product_category='Beauty')
location 's3://amazon-reviews-pds/parquet/product_category=Beauty'
partition(product_category='Books')
location 's3://amazon-reviews-pds/parquet/product_category=Books'
partition(product_category='Camera')
location 's3://amazon-reviews-pds/parquet/product_category=Camera'
partition(product_category='Grocery')
location 's3://amazon-reviews-pds/parquet/product_category=Grocery'
partition(product_category='Furniture')
location 's3://amazon-reviews-pds/parquet/product_category=Furniture'
partition(product_category='Watches')
location 's3://amazon-reviews-pds/parquet/product_category=Watches'
partition(product_category='Lawn_and_Garden')
location 's3://amazon-reviews-pds/parquet/product_category=Lawn_and_Garden';

Around 1 TB data per category is already saved in the given S3 bucket in parquet format.

# aws s3 ls --human s3://amazon-reviews-pds/parquet/product_category=Apparel/
2018-04-09 06:35:35 115.0 MiB part-00000-495c48e6-96d6-4650-aa65-3c36a3516ddd.c000.snappy.parquet
2018-04-09 06:35:35 115.3 MiB part-00001-495c48e6-96d6-4650-aa65-3c36a3516ddd.c000.snappy.parquet
2018-04-09 06:35:36 114.9 MiB part-00002-495c48e6-96d6-4650-aa65-3c36a3516ddd.c000.snappy.parquet
2018-04-09 06:35:35 115.2 MiB part-00003-495c48e6-96d6-4650-aa65-3c36a3516ddd.c000.snappy.parquet
2018-04-09 06:35:35 115.3 MiB part-00004-495c48e6-96d6-4650-aa65-3c36a3516ddd.c000.snappy.parquet
2018-04-09 06:35:39 115.3 MiB part-00005-495c48e6-96d6-4650-aa65-3c36a3516ddd.c000.snappy.parquet
2018-04-09 06:35:39 115.4 MiB part-00006-495c48e6-96d6-4650-aa65-3c36a3516ddd.c000.snappy.parquet
2018-04-09 06:35:39 114.8 MiB part-00007-495c48e6-96d6-4650-aa65-3c36a3516ddd.c000.snappy.parquet
2018-04-09 06:35:39 115.3 MiB part-00008-495c48e6-96d6-4650-aa65-3c36a3516ddd.c000.snappy.parquet
2018-04-09 06:35:40 115.3 MiB part-00009-495c48e6-96d6-4650-aa65-3c36a3516ddd.c000.snappy.parquet

If you are planning to use csv instead of parquet, then you will have to change the serialization, input / output formats in the create table statement.

Labels: athena, aws

posted by shantanu # Sunday, February 16, 2020 0 comments

February 15, 2020

Manage Athena tables using PyAthena

PyAthena is an indispensable toolfor Amazon Athena.

import pandas as pd
from pyathena import connect
from pyathena.pandas.util import to_sql

# create connection object
conn = connect(aws_access_key_id="xxx",
aws_secret_access_key="xxx",
s3_staging_dir="s3://testme162/tutorial/staging/",
region_name="us-east-1",
)

# You may have a very large dataframe instead of this...
df = pd.DataFrame({"a": [1, 2, 3, 4, 5, 6, 7, 8, 11, 21, 545]})

# use the helper function
to_sql(df, "todel", conn, "s3://testme162/tutorial/s3dir/",
schema="sampledb", index=False, if_exists="replace")

# read the athena data into a new dataframe
ndf = pd.read_sql("SELECT * FROM sampledb.todel limit 100", conn)

Labels: athena, aws, python

posted by shantanu # Saturday, February 15, 2020 0 comments

October 02, 2019

Redshift VPC with subnet issue

I was trying to launch an Amazon Redshift cluster in a specific VPC, but I could not select any VPC in the drop-down list. I checked that a VPC exist (though it's not default one!)
In order to resolve this, follow these 3 steps to create a cluster subnet group.

1) click "Security" from redshift console.
2) On the Subnet Groups tab, click Create Cluster Subnet Group.
3) Specify a Name, Description, VPC ID and Click add "all the subnets" link.

Labels: athena, aws, redshift

posted by shantanu # Wednesday, October 02, 2019 0 comments

October 01, 2019

Load parquet data into redshift table

Here is the copy command to load data from parquet file format to existing table in redshift.

copy favoritemovies from 'S3://some_bucket/parquet_file/'
iam_role 'arn:aws:iam::0123456789012:role/RedshiftCopyUnload'

The IAM role needs to be created using the steps mentioned below:

To create an IAM role to allow Amazon Redshift to access AWS services
1) Open the IAM Console.
2) In the navigation pane, choose Roles.
3) Choose Create role.
4) Choose AWS service, and then choose Redshift.
5) Under Select your use case, choose Redshift - Customizable and then choose Next: Permissions.
6) The Attach permissions policy page appears. choose
AmazonS3ReadOnlyAccess, AWSGlueConsoleFullAccess and AmazonAthenaFullAccess.
7) Choose Next: Tags.
8) Choose Next: Review.
9) For Role name, type a name for your role, for example RedshiftCopyUnload. Choose Create role.

Now you need to attach the role to the current cluster.
1) Sign in to the AWS Management Console and open the Amazon Redshift console
2) In the navigation pane, choose Clusters.
3) In the list, choose the cluster that you want to manage IAM role associations for. Choose Manage IAM Roles.
4) Select your IAM role from the Available roles list to associate or remove current one.
5) choose Apply Changes to update the IAM roles that are associated with the cluster.

1) https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html

2) https://docs.aws.amazon.com/redshift/latest/mgmt/copy-unload-iam-role.html#copy-unload-iam-role-associating-with-clusters

Labels: athena, aws, redshift

posted by shantanu # Tuesday, October 01, 2019 0 comments

Shantanu's Blog

April 29, 2024

Partitioning and bucketing S3 data

July 06, 2021

Quick test tables in Athena

May 28, 2021

Athena and Unicode text

April 17, 2021

crawl indian language websites

October 24, 2020

Using Bucketing in Amazon Athena

August 24, 2020

Manage Athena tables using python module wrangler

July 31, 2020

Using Athena to preview large S3 files

April 28, 2020

More about Athena query

April 13, 2020

Redshift Spectrum

March 12, 2020

Analyze S3 storage usage using inventory

February 19, 2020

Manage athena tables using python

Import csv data and convert it to parquet in athena

February 16, 2020

Using partitions in Athena table

February 15, 2020

Manage Athena tables using PyAthena

October 02, 2019

Redshift VPC with subnet issue

October 01, 2019

Load parquet data into redshift table

Archives