Recommendations on exporting to Redshift

#1

I’m looking for a way to export data into Redshift. After looking for examples, I’m still not sure how I can do this but it seems that:

  • For the metric data, will probably use MetricResultExportJob.

  • For non-timeseries data I’m currently considering the following two steps procedure:
    2.1. Use the Export or Extract (not sure what is the difference between the two) API to move data from C3 to S3;
    2.2. Use the exportDataForRedShift from Persistable to push the data into Redshift.

Is this the right way to do it? are there an alternative way that is recommended?
Is there a way to put data straight to Redshift without an intermediate step?

1 Like

#2

Hi,
Regarding the second point, I do not know a direct way to put data straight to Redshift. I think it is in two steps.

  1. Please use Export to export data from C3 to S3.
  2. Please use the following command in RedShift
    copy <table_name> from ‘s3://<bucket_name>/<object_prefix>’

More Info could be found at:
https://docs.aws.amazon.com/redshift/latest/dg/t_loading-tables-from-s3.html

1 Like

#5

Thanks @jiayang, I tried to use exportDataForRedShift on TenantConfig (which is mixes Persistable) as follows

var filterStr = Filter.eq(1, 1)
var spec = {
  s3Bucket: S3.bucketName(),
  filePath: "sample_filter",
  include: "id, name",
  filter: filterStr
};
TenantConfig.exportDataForRedShift(spec);

But I was hitting by the following error in the response:

{
  "id" : "4234.904096",
  "key" : "c3.love.exceptions.C3RuntimeException_wrapIt",
  "cause" : { },
  "template" : "{}",
  "parameters" : [ "wrapped NullPointerException", "null", "java.lang.NullPointerException" ],
  "codes" : [ "NotClassified" ]
}
0 Likes

#6

Bachir, did you ever get a followup here? CAT would like to try out this functionality.

0 Likes

#7

Hi Udit, no I’m having this error on 7.2 and on 7.6 so I ended up making a mapreduce that does this seem thing:

map:

  1. call evalMetrics with a collection of ids and expressions
  2. save the metric evaluation result to S3

reduce:

  1. connect to redshift
  2. execute copy command with the paths from map phase

With snowflake the reduce phase is even easier as they provide a REST api for uploading files.

0 Likes

#8

I keep getting NPEs, can someone else help? how do you call this correctly or where is there a good example?

1 Like

#9

Did you try the export API, here is an example:

var typeName = 'Facility';
var count = (c3Type(typeName).fetchCount()/1000) + 1
var prefix = ["migration", DateTime.now().toString("yyyyMMddhhmmss"), typeName, "part"].join("/");
return BatchExportSpec.make({
  numFiles       : count,
  numObjPerFile  : 1000,
  deleteExisting : true,
  limit          : -1,
  contentType    : "application/json",
  targetType     : {typeName: typeName},
  targetPath     : prefix
});
var job = Export.startExport(spec);
0 Likes