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

SNS message

How do I import a table/view into an SNS message?

10 Community Answers

Best Answer  >Matillion Agent  

Arawan Gajajiva —

Hi Maurizio -

An example of how you can use a Python Script to build your message would be to do as follows:

  • Create two Job Variables
    • curr_var1 — This stores the current value of some data from a table column.
    • agg_var1 — You will append to this variable as you iterate over your table. This variable represents what you want to include in your SNS message.
  • Setup a Table Iterator component to iterate over your audit table/view.
    • Map the value of a column to the “curr_var1” variable.
    • Connect a Python Script component to the Table Iterator
  • Configure your Python Script component to populate/append to the “agg_var1” variable.
    • Here is some simple example code that demonstrates how you can append values into the “agg_var1” variable:
new_agg_var1 = agg_var1+ ',' + curr_var1 
context.updateVariable('agg_var1', new_agg_var1)
print "current value of agg_var1: " + agg_var1

Hope this helps!
Arawan

Matillion Agent  

Arawan Gajajiva —

Hi Maurizio -

The SNS Message component allows you to specify a Subject and Message to publish to an SNS Topic. You can use Matillion Variables when defining the Subject or Message. Some things to note about the usage of SNS can be found in AWS’ SNS FAQ.

Note the following things that are mentioned:

Amazon SNS messages can contain up to 256 KB of text data, including XML, JSON and unformatted text.

The ”Email” transport is meant for end-users/consumers and notifications are regular, text-based messages which are easily readable.

Noting the above, I do not think that putting the contents of a table or view is the intended usage of SNS. Could you expand a little further on what you are trying to do? There may be a better way to achieve your end goal if you could provide some more details.

Best regards,
Arawan


Maurizio Heller —

I want to send the audit table with row counts from the staging tables into an email (SNS)


Matillion Agent  

Arawan Gajajiva —

Hi Maurizio -

If you can get the data you want into Matillion variables, you can put that into the SNS Message. However, you need to ensure that the entire message is 256KB or less.

Alternatively, you could use a Python Script to send an email from the Matillion server itself.

Arawan


Maurizio Heller —

But how can I put a view/table into a variable?


Matillion Agent  

Arawan Gajajiva —

Hi Maurizio -

You can use the Table Iterator component to iterate through every row in your audit table (or view). The component will allow you to map the value of a column in a row in the table to a variable in your Matillion job. This would allow you to send an SNS Message with the mapped values for every row in the table/view you are getting data from. If you do not want an SNS Message for every row in your table/view, you could use a Python script connected to the Table Iterator to dynamically build a message by appending the current value of a variable to another variable. Then, send the final result to an SNS Message.

Another solution would be to unload the contents of the audit table/view to a delimited file on S3. Then, you can use a Python script to send the email and include the file from S3 as an attachment.

Arawan


Maurizio Heller —

The table iterator method over the sns sends one email per row which results in too many emails. Can you tell me how to use python to load the table into a single email via sns?


Best Answer  Matillion Agent  

Arawan Gajajiva —

Hi Maurizio -

An example of how you can use a Python Script to build your message would be to do as follows:

  • Create two Job Variables
    • curr_var1 — This stores the current value of some data from a table column.
    • agg_var1 — You will append to this variable as you iterate over your table. This variable represents what you want to include in your SNS message.
  • Setup a Table Iterator component to iterate over your audit table/view.
    • Map the value of a column to the “curr_var1” variable.
    • Connect a Python Script component to the Table Iterator
  • Configure your Python Script component to populate/append to the “agg_var1” variable.
    • Here is some simple example code that demonstrates how you can append values into the “agg_var1” variable:
new_agg_var1 = agg_var1+ ',' + curr_var1 
context.updateVariable('agg_var1', new_agg_var1)
print "current value of agg_var1: " + agg_var1

Hope this helps!
Arawan


Maurizio Heller —

When I type ${agg_var1} into the SNS message, I only get one row?/


Maurizio Heller —

Its still not treating the ${agg_var1} as a table


Maurizio Heller —

Nevermind I got it to work

Post Your Community Answer

To add an answer please login