Examples interact with an external Amazon Redshift database. Redshift table names used in the examples are placeholders and must exist in the user's Redshift environment. For internal SAS data creation, DATA steps are provided.
1 Code Block
CASLIB
Explanation : This example creates a CAS data library (caslib) named `redshift_lib_basic` that connects to an Amazon Redshift database. It uses `srctype` options to specify the source type, `server`, `username`, `password`, and `database` for connection information. Replace the option values with your own Redshift connection information.
Copied!
/* Establishes a simple connection to an Amazon Redshift database. */
caslib redshift_lib_basic desc='Basic Redshift Caslib'
dataSource=(srctype='redshift',
server='redshift-server.example.com', /* Replace with your Redshift server */
username='redshift_user',
password='your_redshift_password',
database='your_redshift_database');
/* Verify that the caslib is properly defined */
proc cas; table.caslibinfo caslib='redshift_lib_basic'; run; quit;
1
/* Establishes a simple connection to an Amazon Redshift database. */
Explanation : This example shows how to load an existing table from Amazon Redshift (`my_redshift_table`) to the CAS server using `PROC CASUTIL`. It also illustrates the ability to override connection options (like username and password) directly within the `dataSourceOptions` of the `LOAD` statement. The table is loaded into the `casuser` caslib under the name `my_cas_table_from_redshift`. `ephem=TRUE` indicates that the CAS table is ephemeral.
Copied!
/* Create a basic caslib if it doesn't exist */
caslib redshift_lib_load desc='Redshift Caslib for Loading'
dataSource=(srctype='redshift',
server='redshift-server.example.com', /* Replace with your Redshift server */
username='redshift_user_load',
password='your_redshift_password_load',
database='your_redshift_database_load');
/* Load an existing 'my_redshift_table' from Redshift into CAS */
/* Credentials can be overridden at the procedure level */
proc casutil;
list files incaslib="redshift_lib_load"; /* Lists available tables in the Redshift caslib */
load casdata="my_redshift_table" incaslib="redshift_lib_load"
outcaslib="casuser" casout="my_cas_table_from_redshift"
dataSourceOptions=(
username='user_override', /* Override the user */
password='password_override' /* Override the password */
) ephem=TRUE; /* ephem=TRUE for a temporary table */
list files incaslib="casuser"; /* Verifies the presence of the new CAS table */
contents casdata="my_cas_table_from_redshift" incaslib="casuser";
quit;
1
/* Create a basic caslib if it doesn't exist */
2
caslib redshift_lib_load desc='Redshift Caslib for Loading'
3
dataSource=(srctype='redshift',
4
server='redshift-server.example.com', /* Replace with your Redshift server */
5
username='redshift_user_load',
6
password='your_redshift_password_load',
7
database='your_redshift_database_load');
8
9
/* Load an existing 'my_redshift_table' from Redshift into CAS */
10
/* Credentials can be overridden at the procedure level */
11
PROC CASUTIL;
12
list files incaslib="redshift_lib_load"; /* Lists available tables in the Redshift caslib */
Explanation : This example illustrates bulk loading data from a CAS table (`casuser.sample_data`) to a new table in Amazon Redshift (`sample_redshift_table`).
1. A `sample_data` table is first created in CAS with example data.
2. A `redshift_lib_bulk` caslib is defined to connect to Redshift, specifying `bulkLoad=TRUE` options, `bucket` (for the intermediate S3 bucket), `region`, `deleteDataFiles=TRUE` to clean up S3 after the transfer, and `blFormat='PARQUET'` for the file format used for the transfer.
3. `PROC CASUTIL SAVE` is then used to write the CAS table to Redshift via the bulk load mechanism, which uses S3 as an intermediate step.
Copied!
/* 1. Create an in-memory SAS table (CAS) for loading */
data casuser.sample_data;
length id 8 name $20 score 8;
input id name $ score;
datalines;
1 John 85
2 Jane 92
3 Mike 78
4 Emily 95
;
run;
/* 2. Establish a connection to Redshift with bulk loading options */
caslib redshift_lib_bulk desc='Redshift Caslib for Bulk Load'
dataSource=(srctype='redshift',
server='redshift-server.example.com', /* Replace */
username='redshift_user_bulk',
password='your_redshift_password_bulk',
database='your_redshift_database_bulk',
bucket='your-s3-bucket-name', /* Replace with your S3 bucket */
region='us-east-1', /* Replace with your AWS region */
bulkLoad=TRUE, /* Enable bulk loading */
deleteDataFiles=TRUE, /* Deletes temporary files on S3 after loading */
blFormat='PARQUET' /* Uses Parquet format for S3 transfer */
);
/* 3. Load the CAS table into Amazon Redshift using bulk load */
proc casutil;
save casdata="sample_data" incaslib="casuser"
outcaslib="redshift_lib_bulk" casout="sample_redshift_table"
replace; /* replace=TRUE if the Redshift table already exists */
quit;
1
/* 1. Create an in-memory SAS table (CAS) for loading */
2
DATA casuser.sample_data;
3
LENGTH id 8 name $20 score 8;
4
INPUT id name $ score;
5
DATALINES;
6
1 John 85
7
2 Jane 92
8
3 Mike 78
9
4 Emily 95
10
;
11
RUN;
12
13
/* 2. Establish a connection to Redshift with bulk loading options */
14
caslib redshift_lib_bulk desc='Redshift Caslib for Bulk Load'
replace; /* replace=TRUE if the Redshift table already exists */
32
QUIT;
4 Code Block
CASLIB
Explanation : This example configures a caslib to connect to Amazon Redshift using Single Sign-On (SSO) with AWS IAM authentication. It uses `authMethod='IAM'` and includes the `clusterId`, `authenticationScope`, and `awsIAMassumeRoleARN` options which are necessary for this type of connection. The option values must be replaced with those from your AWS and Microsoft Entra ID (Azure Active Directory) configuration.
Copied!
/* Establishes an SSO connection to an Amazon Redshift database */
caslib redshift_lib_sso desc='Redshift Caslib with SSO'
dataSource=(srctype='redshift',
server='myserver.redshift.amazonaws.com', /* Replace */
username='myuserid', /* Replace with your user ID */
database='myuserdb', /* Replace with your Redshift database */
region='us-east-1', /* Replace with your AWS region */
authMethod='IAM', /* Uses IAM authentication for SSO */
clusterId='dbrs1', /* Replace with your Redshift cluster ID */
authenticationScope='api://a09827234-93eo-092fjas1-92038498273/awsassumerole', /* Replace */
awsIAMassumeRoleARN='arn:aws:iam::129347192347098273:role/myrolename' /* Replace */
);
/* Verify that the caslib is properly defined */
proc cas; table.caslibinfo caslib='redshift_lib_sso'; run; quit;
1
/* Establishes an SSO connection to an Amazon Redshift database */
2
caslib redshift_lib_sso desc='Redshift Caslib with SSO'
This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.
SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration. WeAreCAS is an independent community site and is not affiliated with SAS Institute Inc.
This site uses technical and analytical cookies to improve your experience.
Read more.
