Amazon Redshift is a petabyte-scale data warehouse in the cloud that allows its users to create small or large databases without worrying about storage. Creating databases, and tables, and storing data has become much easier with Amazon Redshift. We create databases and start working on them without keeping a record of the table creation date, we might need a table creation date someday. The question arises here: Is there any way we can find the table creation date?
In this article, we will explore why we need to find table creation dates and what are the ways through which we can find table creation dates in Amazon Redshift.
Reasons to find table creation date:
There can be many reasons for knowing the table creation date. A few of them are stated below.
- The one main reason can be data auditing it helps to keep track of changes.
- It ensures data integrity.
- When you know the date old tables can be modified to keep the data up to date.
- Drop old useless tables, this will free storage space and improve performance.
- It helps you trace how the table has evolved.
Methods to access table creation date:
We can access the table creation date whether you created a column or not for storing the creation date. We will see how we can get the table creation date in Amazon Redshift with two methods.
- Access the table creation date from the table
- Audit logs
Access the table creation date from the table:
This allows us to access the creation date from the table that we stored in the table column while creating the table. Only a few people store the creation date of the table in their records if you have stored it in your table then you can access it by selecting the column name from the table. The result will display the created date of the table. This happens rarely most people don’t pay attention to the creation date of the table. If you did not store the creation date move to the next method.
Code:
# Accessing the table creation date from the table
SELECT created_at FROM e_desc;
Output:
Audit Log
We can get the creation date by going through the audits log. Audit logs are records of events that we have done in the database. It keeps a record of all the queries we execute and stores them. We can access these audits but we should have a cluster. Let’s see how we can see the creation date of the table from audits step-by-step
Step-1: Enable Audit logging
To enable audit logging for the cluster we have to go through certain steps.
1. Sign in to your AWS management console and open the Amazon Redshift console.
2. Choose Clusters on the navigation menu.
3. Choose the cluster you want to edit.
Note: If you don’t have any cluster first create a cluster then move to the next step.
4. Click Edit on the right of the cluster name
5. Scroll down and select Database Configuration.
6. Choose Edit, then Edit Audit logging.
7. Turn on configure audit logging, select S3 bucket or CloudWatch, and select the logs you want to export. Click save changes.
Step-2: Open where you stored logs S3 bucket or CloudWatch
Open logs and select the log. You will find all the logs here. You can search for create query to find the table date or write the table name it will display all the queries with timestamps.
Conclusion:
In conclusion, to find the table creation date we explained two methods. The first method is useful if you have a column for the creation date. If not, then the second method is best to use. Audit logging is also useful if you have a cluster created if not then you can create a cluster and follow all the steps. This way you can find the creation date of the table.