Access table in python script using where clause

Matillion ETL
1

Hi,

I have a requirement where I need to send data like total rows inserted for each table based on a condition from audit tables in an email, I am using cursor.context() to query the table, when I am running the query (select count(*) from emp) the script is working fine, but if include where clause it is failing. Can you please let me know?

2

Hi @RK​, could you tell us if you are using Matillion for Matillion, GCP, Synaps, Snowflake, etc.? When you say you are wanting to get total rows inserted for each table from audit tables, are you referring to the Matillion audit tables or tables in your own database?

I would suggest using a component like query result to grid or scalar to collect information about counts. You can then use the results in Python to create an email. What you are trying to do sounds very similar to what we do, where we get the job information and get rows inserted from the logs and then use that information to send email through AWS Simple Email Service using Python. Please let us know if there is more detail you could provide as it will likely help getting you a better answer. Thanks for posting!

3

We are using Matillion for snowflake, The audit tables are created by us. We are trying to get information from this audit table based on a condition, but in python script is not allowing to include where clause . It is throwing ERROR invalid identifier 'emp' [SQLCode: 904], [SQLState: 42000] in <script> at line number 2

 

cursor.execute('select * from audit where table_name="emp" ')

 

4

Hi @RK​, can you just query the content you need using a SQL Script component? Or is there some reason why you have to do it in Jython. In my opinion querying Snowflake via Jython is sort of a last resort. I understand there are reasons you might want to use Jython to accomplish this but it also comes with caveats and pitfalls.

Let's step back for a second and describe what you are trying to do as a whole rather than focusing on the querying of Snowflake with Jython. There could be other ways to get you to your end goal that don't require you to use Jython to make that happen. Please let us know what your goal is and I am sure a few of us will be glad to jump in and help.

5

Thanks Bryan,

 

Our original requirement is, the audit tables contain some metrics across multiple business units and this table might have 50 to 100 rows. Once our load process is done business team wants these metrics to be sent to them in a email. Ideally we would like to generate the report in csv format and attach to an email and send it.

6

We use azure cloud.

7

Ahhh... Well, I have a little experience with Azure but not nearly the experience as I do with AWS. So, I this may or may not be super helpful. We are using the Matillion for Snowflake on AWS product, which likely has different components that the Synapse product. Either way, based on your description this is the high level approach I would start with. The rule of thumb should be to leverage Snowflake to the best of it's abilities because what it does do, it does extremely well. In Snowflake, you have the ability to "unload" data using out of the box functionality.

 

So, these would be the high level steps:

  1. Unload data to a file in Azure (https://docs.snowflake.com/en/user-guide/data-unload-azure.html)
  2. Follow that up with a Python script if your Matillion version doesn't have a component for Azure SendGrid.
    1. If Python, then use the Azure Python Module to create the email and attach the file from step 1 and then send it.

 

I think this should get you much further. Please let us know if this approach doesn't work for you or if you got it to work using a different method. Hopefully this help more.

8

Will try this and see

9

Makes sense. What were you going to use to send the email? Where you going to send the email via AWS Simple Email Service using boto3?