Getting started with Amazon Redshift Spectrum

Open the IAM console.

In the navigation pane, choose Roles.

Choose Create role.

Choose AWS service as the trusted entity, and then choose Redshift as the use case.

Under Use case for other AWS services, choose Redshift - Customizable and then choose Next.

The Add permissions policy page appears. Choose AmazonS3ReadOnlyAccess and AWSGlueConsoleFullAccess, if you're using the AWS Glue Data Catalog. Or choose AmazonAthenaFullAccess if you're using the Athena Data Catalog. Choose Next.

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:Get*",
                "s3:List*"
            ],
            "Resource": "arn:aws:s3:::redshift-downloads/*"
        }
    ]
}

For Role name, enter a name for your role, for example myspectrum_role.

Review the information, and then choose Create role.

In the navigation pane, choose Roles. Choose the name of your new role to view the summary, and then copy the Role ARN to your clipboard. This value is the Amazon Resource Name (ARN) for the role that you just created. You use that value when you create external tables to reference your data files on Amazon S3.

Open the IAM console at https://console.aws.amazon.com/iam/.

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 to create the policy on the JSON tab.

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": "*"
        }
    ]
}

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

On the Review policy page, for Name enter myspectrum_policy to name 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.

After you create a policy, you can provide access to your users.

Open the Lake Formation console at https://console.aws.amazon.com/lakeformation/.

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

Follow the instructions in Granting table permissions using the named resource method in the AWS Lake Formation Developer Guide. Provide the following information:

Choose Grant.

Sign in to the AWS Management Console and open the Amazon Redshift console at https://console.aws.amazon.com/redshiftv2/.

On the navigation menu, choose Clusters, then choose the name of the cluster that you want to update.

For Actions, choose Manage IAM roles. The IAM roles page appears.

Either choose Enter ARN and then enter an ARN or an IAM role, or choose an IAM role from the list. Then choose Add IAM role to add it to the list of Attached IAM roles.

Choose Done to associate the IAM role with the cluster. The cluster is modified to complete the change.

To create an external schema, replace the IAM role ARN in the following command with the role ARN you created in step 1. Then run the command in your SQL client.

create external schema myspectrum_schema 
from data catalog 
database 'myspectrum_db' 
iam_role 'arn:aws:iam::123456789012:role/myspectrum_role'
create external database if not exists;

To create an external table, run the following CREATE EXTERNAL TABLE command.

aws s3 cp s3://redshift-downloads/tickit/spectrum/sales/ s3://bucket-name/tickit/spectrum/sales/ --copy-props none --recursive

copy: s3://redshift-downloads/tickit/spectrum/sales/sales_ts.000 to s3://bucket-name/tickit/spectrum/sales/sales_ts.000

create external table myspectrum_schema.sales(
salesid integer,
listid integer,
sellerid integer,
buyerid integer,
eventid integer,
dateid smallint,
qtysold smallint,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp)
row format delimited
fields terminated by '\t'
stored as textfile
location 's3://redshift-downloads/tickit/spectrum/sales/'
table properties ('numRows'='172000');

Get the number of rows in the MYSPECTRUM_SCHEMA.SALES table.

select count(*) from myspectrum_schema.sales;

count 
------
172462

Keep your larger fact tables in Amazon S3 and your smaller dimension tables in Amazon Redshift, as a best practice. If you loaded the sample data in Load data, you have a table named EVENT in your database. If not, create the EVENT table by using the following command.

create table event(
eventid integer not null distkey,
venueid smallint not null,
catid smallint not null,
dateid smallint not null sortkey,
eventname varchar(200),
starttime timestamp);

Load the EVENT table by replacing the IAM role ARN in the following COPY command with the role ARN you created in Step 1. Create an IAM role for Amazon Redshift. You can optionally download and view the source data for the allevents_pipe.txt from an Amazon S3 bucket in AWS Region us-east-1.

copy event from 's3://redshift-downloads/tickit/allevents_pipe.txt' 
iam_role 'arn:aws:iam::123456789012:role/myspectrum_role'
delimiter '|' timeformat 'YYYY-MM-DD HH:MI:SS' region 'us-east-1';

The following example joins the external Amazon S3 table MYSPECTRUM_SCHEMA.SALES with the local Amazon Redshift table EVENT to find the total sales for the top 10 events.

select top 10 myspectrum_schema.sales.eventid, sum(myspectrum_schema.sales.pricepaid) from myspectrum_schema.sales, event
where myspectrum_schema.sales.eventid = event.eventid
and myspectrum_schema.sales.pricepaid > 30
group by myspectrum_schema.sales.eventid
order by 2 desc;

eventid | sum     
--------+---------
    289 | 51846.00
   7895 | 51049.00
   1602 | 50301.00
    851 | 49956.00
   7315 | 49823.00
   6471 | 47997.00
   2118 | 47863.00
    984 | 46780.00
   7851 | 46661.00
   5638 | 46280.00

View the query plan for the previous query. Notice the S3 Seq Scan, S3 HashAggregate, and S3 Query Scan steps that were run against the data on Amazon S3.

explain
select top 10 myspectrum_schema.sales.eventid, sum(myspectrum_schema.sales.pricepaid) 
from myspectrum_schema.sales, event
where myspectrum_schema.sales.eventid = event.eventid
and myspectrum_schema.sales.pricepaid > 30
group by myspectrum_schema.sales.eventid
order by 2 desc;

QUERY PLAN                                                                                                                                                                                
-----------------------------------------------------------------------------
XN Limit  (cost=1001055770628.63..1001055770628.65 rows=10 width=31)                                                                                                                      
  ->  XN Merge  (cost=1001055770628.63..1001055770629.13 rows=200 width=31)                                                                                                               
        Merge Key: sum(sales.derived_col2)                                                                                                                                                
        ->  XN Network  (cost=1001055770628.63..1001055770629.13 rows=200 width=31)                                                                                                       
              Send to leader                                                                                                                                                              
              ->  XN Sort  (cost=1001055770628.63..1001055770629.13 rows=200 width=31)                                                                                                    
                    Sort Key: sum(sales.derived_col2)                                                                                                                                     
                    ->  XN HashAggregate  (cost=1055770620.49..1055770620.99 rows=200 width=31)                                                                                           
                          ->  XN Hash Join DS_BCAST_INNER  (cost=3119.97..1055769620.49 rows=200000 width=31)                                                                             
                                Hash Cond: ("outer".derived_col1 = "inner".eventid)                                                                                                       
                                ->  XN S3 Query Scan sales  (cost=3010.00..5010.50 rows=200000 width=31)                                                                                  
                                      ->  S3 HashAggregate  (cost=3010.00..3010.50 rows=200000 width=16)                                                                                  
                                            ->  S3 Seq Scan myspectrum_schema.sales location:"s3://redshift-downloads/tickit/spectrum/sales" format:TEXT  (cost=0.00..2150.00 rows=172000 width=16)
                                                  Filter: (pricepaid > 30.00)                                                                                                             
                                ->  XN Hash  (cost=87.98..87.98 rows=8798 width=4)                                                                                                        
                                      ->  XN Seq Scan on event  (cost=0.00..87.98 rows=8798 width=4)

Choose Launch CFN stack. The CloudFormation console opens with the DataLake.yml template selected.

You can also download and customize the Redshift Spectrum Getting Started DataLake CloudFormation CFN template, then open CloudFormation console (https://console.aws.amazon.com/cloudformation) and create a stack with the customized template.

Choose Next.

Under Parameters, enter the Amazon Redshift cluster name, database name, and your database user name.

Choose Next.

The stack options appear.

Choose Next to accept the default settings.

Review the information and under Capabilities, and choose I acknowledge that AWS CloudFormation might create IAM resources.

Choose Create stack.