Use the following code for the table DDLs: In Amazon Redshift query editor v2 or a compatible SQL editor of your choice, create the tables arch_table_metadata and arch_job_log.
Create an Amazon Redshift provisioned cluster or Amazon Redshift serverless workgroup.Status of the stored procedure run: IN-PROGRESS, COMPLETED, or FAILED.įor this solution, complete the following prerequisites: Time in UTC when the stored procedure ended. Time in UTC when the stored procedure started.
Number of rows deleted by the purge operation. Number of rows in the table before purging. Id column value from table arch_table_metadata. ColumnNameĪssigns unique numeric value per stored procedure run. Records are added to this table by the stored procedure. The arch_job_log database table stores the run history of stored procedures. Number of days the data will be retained for the table. Name of the date column that is used to identify records to be archived and purged.Īmazon S3 location where the data will be archived. Name of the table to be archived and purged. Name of the database schema of the table. ColumnNameĭatabase-generated, automatically assigns a unique value to each record. The arch_table_metadata table contains the following columns.
Redshift create view archive#
You need to add rows into this table that you want to archive and purge. The arch_table_metadata database table stores the metadata for all the tables that need to be archived and purged. We use two database tables as part of this solution. The following diagram illustrates our solution architecture. Time series tables retain data for a certain period of time (days, months, quarters, or years) and need data to be purged regularly to maintain the rolling data to be analyzed by end-users.
Redshift create view how to#
This post walks you through the process of how to automate data archival and purging of Amazon Redshift time series tables. The purging process can be based on the data retention policy, which is defined by the data owner or business need. Archive data consists of older data that is still important to the organization and may be needed for future reference, as well as data that must be retained for regulatory compliance.ĭata purging is the process of freeing up space in the database or deleting obsolete data that isn’t required by the business.
The frequency of data archival depends on the relevance of the data with respect to your business or legal needs.ĭata archiving is the process of moving data that is no longer actively used in a data warehouse to a separate storage device for long-term retention. It’s crucial to define how long an organization needs to hold on to specific data, and if data that is no longer needed should be archived or deleted. It’s necessary to keep optimizing your data in data warehouses for consistent performance, reliability, and cost control. In a big data world, the size of data is consistently increasing, which directly affects the cost of storing the data in data stores. You can run and scale analytics in seconds on all your data without having to manage your data warehouse infrastructure.Ī data retention policy is part of an organization’s overall data management. Tens of thousands of customers today rely on Amazon Redshift to analyze exabytes of data and run complex analytical queries, making it the most widely used cloud data warehouse. Amazon Redshift is a fast, petabyte-scale cloud data warehouse that makes it simple and cost-effective to analyze all of your data using standard SQL.
0 kommentar(er)
