Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

adding additional columns to redshift table

Hi,

I am attempting to orchestrate a daily load of summarised marketing information of the previous days' data using the API component and RSDs.

I am passing to the RSD yesterday's date and I would like to add this date as a column on-load.

Is this possible ?

Many thanks,

Greg.

What is the best way to add extra columns

4 Community Answers

Matillion Agent  

Kalyan Arangam —

Hi Greg,

Usually, we would advise doing this in a transformation step after data is loaded into redshift.

Since you are already passing it into RSD, you may add the necessary column in the RSD.

Step 1
Create a column definition for the date column under </rsb:info> tag.

<attr   name="dt_yesterday"     xs:type="string" />

Step 2
Add the following line just above <rsb:push/>. Lets assume your data parameter is called p_yesterday and you have passed it via Connection Options.

<rsb:set  attr="dt_yesterday" value="[_connection.p_yesterday]"/>

Please use input.pyesterday] if you have passed you date using a WHERE clause or a filter.

Hope that helps.

Best
Kalyan


Greg Roper —

Awesome!
Thank you Kaylan.


Greg Roper —

Hi Kaylan,

I don't think this feature is working. I initially tried string and then date (with iso format) - neither values were passed to the table. I also ensured that the new columns were selected in the Data Selection properties of the API component. I've even tried hard coding date values.

Could you please describe how I would achieve this with a transformation job and components.

Many thanks,

Greg.


<rsb:info
<attr name="date_from" xs:type="date" />
<attr name="date_to" xs:type="date" />
</rsb:info>

<!-- <rsb:set attr="date_from" value="[_connection.yesterday_iso]"/> -->
<!-- <rsb:set attr="date_to" value="[_connection.yesterday_iso]"/> -->

<rsb:set attr="date_from" value="2018-20-20 00:00:00"/>
<rsb:set attr="date_to" value="2018-20-20 00:00:00"/>


Greg Roper —

Yes my dates are wonky there, pls ignore this:)

Post Your Community Answer

To add an answer please login