topic Re: Redshift and SAS in SAS Data Management

Redshift and SAS

test12342341 — Tue, 21 Nov 2017 21:11:48 GMT

Hello,

I am trying to upload a large table from SAS to Amazon RedShift using the bulkload option(http://documentation.sas.com/?docsetId=acreldb&docsetTarget=n1getgqem5whhmn1bxx7ct75ns82.htm&docsetVersion=9.4&locale=en).

It requires the BL_Bucket option (http://documentation.sas.com/?docsetId=acreldb&docsetTarget=n1n1jcqd49bk0kn1r66oe3o3dt47.htm&docsetVersion=9.4&locale=ja)

I have the bucket name, access and secret keys, but I don't know how they should be entered into the data set option. Could someone post an example of proc append with the bl_bucket option?

Thank you!

Re: Redshift and SAS

SASKiwi — Tue, 21 Nov 2017 21:22:00 GMT

Here is how I expect it to work. I note you need to be using SAS 9.4M4 for this option to be available.

proc datasets library = REDSHIFT;
  append base =  BaseTable
               data = AppendTable (bl_bucket = MyBucketName)
         ;
  run;
quit;

Re: Redshift and SAS

test12342341 — Wed, 22 Nov 2017 14:24:02 GMT

Thanks for your reply.

The data set option should go on the base table since that is the destination.

The bulkload=yes option should be used as well.

Here is what I tried and it is not working. Gives me an error saying 'Unknown region'. There doesn't seem to be a way to supply region.

proc append base=rds_lib.base_table( bulkload=yes BL_BUCKET=bucket_name)
data=data_table;
run;

Re: Redshift and SAS

test12342341 — Wed, 22 Nov 2017 15:25:52 GMT

I found some documentation on it. There are additional options that should be supplied. The example is here: https://communities.sas.com/t5/SAS-Communities-Library/Redshift-and-SAS/ta-p/361738

Re: Redshift and SAS

Patrick — Mon, 27 Nov 2017 20:33:05 GMT

@test12342341

The bulkload options need to be on source table (data=) as this is the one you want to load in bulk.

...I stay corrected: The bulkload option needs to be on the target table.

Re: Redshift and SAS

test12342341 — Mon, 27 Nov 2017 15:26:20 GMT

Not according to the example in this documentation https://communities.sas.com/t5/SAS-Communities-Library/Redshift-and-SAS/ta-p/361738

Re: Redshift and SAS

Patrick — Mon, 27 Nov 2017 20:35:19 GMT

@test12342341

Given that the link you've posted provides sample code and demonstrates how to use some of the bulkload options: What's still not working for you?