Amazon Redshift- write date and time

Question

Is there a Boto3 python script available that gives Date and Time, when was last time Table in Amazon Redshift was written (INSERT, UPDATE and DELETE), just need data and time, not the content which was written.

Answer

In order to extract this information you need to look into system tables. 
You can call [Redshift Data API](https://docs.aws.amazon.com/redshift/latest/mgmt/data-api.html) to run a query against system tables from boto3.

Create a query to extract time & query text from [STL_QUERY](https://docs.aws.amazon.com/redshift/latest/dg/r_STL_QUERY.html) + [STL_QUERYTEXT](https://docs.aws.amazon.com/redshift/latest/dg/r_STL_QUERYTEXT.html) (Provisioned) or  [SYS_QUERY_HISTORY](https://docs.aws.amazon.com/redshift/latest/dg/SYS_QUERY_HISTORY.html) (Serverless). 
Your query would look like `SELECT * FROM ... WHERE text LIKE '%INSERT%' AND text LIKE '%mytable%'`.

Note that in the example above, mytable may not be the target of INSERT so you need to check the extracted query text manually. 
If you need full automation, parse the query text in Python using SQL parser library like below. 
https://github.com/macbre/sql-metadata

Amazon Redshift- write date and time

Contenuto pertinente