What to think about when migrating knowledge warehouse to Amazon Redshift

Clients are migrating knowledge warehouses to Amazon Redshift as a result of it’s quick, scalable, and cost-effective. Nevertheless, knowledge warehouse migration initiatives might be advanced and difficult. On this submit, I show you how to perceive the widespread drivers of knowledge warehouse migration, migration methods, and what instruments and companies can be found to help together with your migration venture.

Let’s first focus on the massive knowledge panorama, the that means of a contemporary knowledge structure, and what you want to think about in your knowledge warehouse migration venture when constructing a contemporary knowledge structure.

Enterprise alternatives

Information is altering the way in which we work, dwell, and play. All of this habits change and the motion to the cloud has resulted in a knowledge explosion over the previous 20 years. The proliferation of Web of Issues and sensible telephones have accelerated the quantity of the information that’s generated day by day. Enterprise fashions have shifted, and so have the wants of the individuals operating these companies. We now have moved from speaking about terabytes of knowledge just some years in the past to now petabytes and exabytes of knowledge. By placing knowledge to work effectively and constructing deep enterprise insights from the information collected, companies in numerous industries and of assorted sizes can obtain a variety of enterprise outcomes. These might be broadly categorized into the next core enterprise outcomes:

• Bettering operational effectivity – By making sense of the information collected from varied operational processes, companies can enhance buyer expertise, improve manufacturing effectivity, and improve gross sales and advertising agility
• Making extra knowledgeable choices – By growing extra significant insights by bringing collectively full image of knowledge throughout a corporation, companies could make extra knowledgeable choices
• Accelerating innovation – Combining inner and exterior knowledge sources allow quite a lot of AI and machine studying (ML) use circumstances that assist companies automate processes and unlock enterprise alternatives that had been both unimaginable to do or too troublesome to do earlier than

Enterprise challenges

Exponential knowledge development has additionally introduced enterprise challenges.

To start with, companies must entry all knowledge throughout the group, and knowledge could also be distributed in silos. It comes from quite a lot of sources, in a variety of knowledge sorts and in massive quantity and velocity. Some knowledge could also be saved as structured knowledge in relational databases. Different knowledge could also be saved as semi-structured knowledge in object shops, resembling media information and the clickstream knowledge that’s always streaming from cellular units.

Secondly, to construct insights from knowledge, companies must dive deep into the information by conducting analytics. These analytics actions usually contain dozens and a whole lot of knowledge analysts who must entry the system concurrently. Having a performant system that’s scalable to satisfy the question demand is commonly a problem. It will get extra advanced when companies must share the analyzed knowledge with their prospects.

Final however not least, companies want an economical answer to handle knowledge silos, efficiency, scalability, safety, and compliance challenges. Having the ability to visualize and predict value is important for a enterprise to measure the cost-effectiveness of its answer.

To resolve these challenges, companies want a future proof trendy knowledge structure and a sturdy, environment friendly analytics system.

Trendy knowledge structure

A contemporary knowledge structure permits organizations to retailer any quantity of knowledge in open codecs, break down disconnected knowledge silos, empower customers to run analytics or ML utilizing their most popular software or approach, and handle who has entry to particular items of knowledge with the correct safety and knowledge governance controls.

The AWS knowledge lake structure is a contemporary knowledge structure that lets you retailer knowledge in a knowledge lake and use a hoop of purpose-built knowledge companies across the lake, as proven within the following determine. This lets you make choices with pace and agility, at scale, and cost-effectively. For extra particulars, check with Trendy Information Structure on AWS.

Trendy knowledge warehouse

Amazon Redshift is a totally managed, scalable, trendy knowledge warehouse that accelerates time to insights with quick, straightforward, and safe analytics at scale. With Amazon Redshift, you’ll be able to analyze all of your knowledge and get efficiency at any scale with low and predictable prices.

Amazon Redshift presents the next advantages:

• Analyze all of your knowledge – With Amazon Redshift, you’ll be able to simply analyze all of your knowledge throughout your knowledge warehouse and knowledge lake with constant safety and governance insurance policies. We name this the trendy knowledge structure. With Amazon Redshift Spectrum, you’ll be able to question knowledge in your knowledge lake without having for loading or different knowledge preparation. And with knowledge lake export, it can save you the outcomes of an Amazon Redshift question again into the lake. This implies you’ll be able to reap the benefits of real-time analytics and ML/AI use circumstances with out re-architecture, as a result of Amazon Redshift is totally built-in together with your knowledge lake. With new capabilities like knowledge sharing, you’ll be able to simply share knowledge throughout Amazon Redshift clusters each internally and externally, so everybody has a dwell and constant view of the information. Amazon Redshift ML makes it straightforward to do extra together with your knowledge—you’ll be able to create, practice, and deploy ML fashions utilizing acquainted SQL instructions straight in Amazon Redshift knowledge warehouses.
• Quick efficiency at any scale – Amazon Redshift is a self-tuning and self-learning system that means that you can get the very best efficiency in your workloads with out the undifferentiated heavy lifting of tuning your knowledge warehouse with duties resembling defining type keys and distribution keys, and new capabilities like materialized views, auto-refresh, and auto-query rewrite. Amazon Redshift scales to ship persistently quick outcomes from gigabytes to petabytes of knowledge, and from just a few customers to 1000’s. As your person base scales to 1000’s of concurrent customers, the concurrency scaling functionality robotically deploys the required compute assets to handle the extra load. Amazon Redshift RA3 situations with managed storage separate compute and storage, so you’ll be able to scale every independently and solely pay for the storage you want. AQUA (Superior Question Accelerator) for Amazon Redshift is a brand new distributed and hardware-accelerated cache that robotically boosts sure forms of queries.
• Straightforward analytics for everybody – Amazon Redshift is a totally managed knowledge warehouse that abstracts away the burden of detailed infrastructure administration or efficiency optimization. You possibly can deal with attending to insights, reasonably than performing upkeep duties like provisioning infrastructure, creating backups, establishing the format of knowledge, and different duties. You possibly can function knowledge in open codecs, use acquainted SQL instructions, and reap the benefits of question visualizations obtainable by way of the brand new Question Editor v2. You can even entry knowledge from any utility by way of a safe knowledge API with out configuring software program drivers, managing database connections. Amazon Redshift is suitable with enterprise intelligence (BI) instruments, opening up the facility and integration of Amazon Redshift to enterprise customers who function from throughout the BI software.

A contemporary knowledge structure with a knowledge lake structure and trendy knowledge warehouse with Amazon Redshift helps companies in all totally different sizes tackle massive knowledge challenges, make sense of a considerable amount of knowledge, and drive enterprise outcomes. You can begin the journey of constructing a contemporary knowledge structure by migrating your knowledge warehouse to Amazon Redshift.

Migration issues

Information warehouse migration presents a problem when it comes to venture complexity and poses a danger when it comes to assets, time, and value. To cut back the complexity of knowledge warehouse migration, it’s important to decide on a proper migration technique primarily based in your present knowledge warehouse panorama and the quantity of transformation required emigrate to Amazon Redshift. The next are the important thing elements that may affect your migration technique determination:

• Measurement – The full dimension of the supply knowledge warehouse to be migrated is set by the objects, tables, and databases which are included within the migration. A superb understanding of the information sources and knowledge domains required for shifting to Amazon Redshift results in an optimum sizing of the migration venture.
• Information switch – Information warehouse migration includes knowledge switch between the supply knowledge warehouse servers and AWS. You possibly can both switch knowledge over a community interconnection between the supply location and AWS resembling AWS Direct Join or switch knowledge offline by way of the instruments or companies such because the AWS Snow Household.
• Information change charge – How typically do knowledge updates or adjustments happen in your knowledge warehouse? Your present knowledge warehouse knowledge change charge determines the replace intervals required to maintain the supply knowledge warehouse and the goal Amazon Redshift in sync. A supply knowledge warehouse with a excessive knowledge change charge requires the service switching from the supply to Amazon Redshift to finish inside an replace interval, which results in a shorter migration cutover window.
• Information transformation – Shifting your present knowledge warehouse to Amazon Redshift is a heterogenous migration involving knowledge transformation resembling knowledge mapping and schema change. The complexity of knowledge transformation determines the processing time required for an iteration of migration.
• Migration and ETL instruments – The choice of migration and extract, remodel, and cargo (ETL) instruments can affect the migration venture. For instance, the efforts required for deployment and setup of those instruments can range. We glance nearer at AWS instruments and companies shortly.

After you’ve got factored in all these issues, you’ll be able to choose a migration technique choice in your Amazon Redshift migration venture.

Migration methods

You possibly can select from three migration methods: one-step migration, two-step migration, or wave-based migration.

One-step migration is an effective choice for databases that don’t require steady operation resembling steady replication to maintain ongoing knowledge adjustments in sync between the supply and vacation spot. You possibly can extract present databases as comma separated worth (CSV) information, or columnar format like Parquet, then use AWS Snow Household companies resembling AWS Snowball to ship datasets to Amazon Easy Storage Service (Amazon S3) for loading into Amazon Redshift. You then take a look at the vacation spot Amazon Redshift database for knowledge consistency with the supply. In any case validations have handed, the database is converted to AWS.

Two-step migration is often used for databases of any dimension that require steady operation, resembling the continual replication. In the course of the migration, the supply databases have ongoing knowledge adjustments, and steady replication retains knowledge adjustments in sync between the supply and Amazon Redshift. The breakdown of the two-step migration technique is as follows:

• Preliminary knowledge migration – The information is extracted from the supply database, ideally throughout non-peak utilization to attenuate the affect. The information is then migrated to Amazon Redshift by following the one-step migration strategy described beforehand.
• Modified knowledge migration – Information that modified within the supply database after the preliminary knowledge migration is propagated to the vacation spot earlier than switchover. This step synchronizes the supply and vacation spot databases. After all of the modified knowledge is migrated, you’ll be able to validate the information within the vacation spot database and carry out crucial exams. If all exams are handed, you then swap over to the Amazon Redshift knowledge warehouse.

Wave-based migration is appropriate for large-scale knowledge warehouse migration initiatives. The precept of wave-based migration is taking precautions to divide a posh migration venture into a number of logical and systematic waves. This technique can considerably scale back the complexity and danger. You begin from a workload that covers a superb variety of knowledge sources and topic areas with medium complexity, then add extra knowledge sources and topic areas in every subsequent wave. With this technique, you run each the supply knowledge warehouse and Amazon Redshift manufacturing environments in parallel for a sure period of time earlier than you’ll be able to totally retire the supply knowledge warehouse. See Develop an utility migration methodology to modernize your knowledge warehouse with Amazon Redshift for particulars on tips on how to establish and group knowledge sources and analytics purposes emigrate from the supply knowledge warehouse to Amazon Redshift utilizing the wave-based migration strategy.

To information your migration technique determination, check with the next desk to map the consideration elements with a most popular migration technique.

Migration course of

On this part, we evaluate the three high-level steps of the migration course of. The 2-step migration technique and wave-based migration technique contain all three migration steps. Nevertheless, the wave-based migration technique consists of quite a few iterations. As a result of solely databases that don’t require steady operations are good suits for one-step migration, solely Steps 1 and a couple of within the migration course of are required.

Step 1: Convert schema and topic space

On this step, you make the supply knowledge warehouse schema suitable with the Amazon Redshift schema by changing the supply knowledge warehouse schema utilizing schema conversion instruments resembling AWS Schema Conversion Instrument (AWS SCT) and the opposite instruments from AWS companions. In some conditions, you may additionally be required to make use of customized code to conduct advanced schema conversions. We dive deeper into AWS SCT and migration greatest practices in a later part.

Step 2: Preliminary knowledge extraction and cargo

On this step, you full the preliminary knowledge extraction and cargo the supply knowledge into Amazon Redshift for the primary time. You should use AWS SCT knowledge extractors to extract knowledge from the supply knowledge warehouse and cargo knowledge to Amazon S3 in case your knowledge dimension and knowledge switch necessities let you switch knowledge over the interconnected community. Alternatively, if there are limitations resembling community capability restrict, you’ll be able to load knowledge to Snowball and from there knowledge will get loaded to Amazon S3. When the information within the supply knowledge warehouse is on the market on Amazon S3, it’s loaded to Amazon Redshift. In conditions when the supply knowledge warehouse native instruments do a greater knowledge unload and cargo job than AWS SCT knowledge extractors, it’s possible you’ll select to make use of the native instruments to finish this step.

Step 3: Delta and incremental load

On this step, you employ AWS SCT and typically supply knowledge warehouse native instruments to seize and cargo delta or incremental adjustments from sources to Amazon Redshift. That is typically referred to vary knowledge seize (CDC). CDC is a course of that captures adjustments made in a database, and ensures that these adjustments are replicated to a vacation spot resembling a knowledge warehouse.

It is best to now have sufficient data to start out growing a migration plan in your knowledge warehouse. Within the following part, I dive deeper into the AWS companies that may show you how to migrate your knowledge warehouse to Amazon Redshift, and the very best practices of utilizing these companies to speed up a profitable supply of your knowledge warehouse migration venture.

Information warehouse migration companies

Information warehouse migration includes a set of companies and instruments to help the migration course of. You start with making a database migration evaluation report after which changing the supply knowledge schema to be suitable with Amazon Redshift by utilizing AWS SCT. To maneuver knowledge, you need to use the AWS SCT knowledge extraction software, which has integration with AWS Information Migration Service (AWS DMS) to create and handle AWS DMS duties and orchestrate knowledge migration.

To switch supply knowledge over the interconnected community between the supply and AWS, you need to use AWS Storage Gateway, Amazon Kinesis Information Firehose, Direct Join, AWS Switch Household companies, Amazon S3 Switch Acceleration, and AWS DataSync. For knowledge warehouse migration involving a big quantity of knowledge, or if there are constraints with the interconnected community capability, you’ll be able to switch knowledge utilizing the AWS Snow Household of companies. With this strategy, you’ll be able to copy the information to the machine, ship it again to AWS, and have the information copied to Amazon Redshift by way of Amazon S3.

AWS SCT is an important service to speed up your knowledge warehouse migration to Amazon Redshift. Let’s dive deeper into it.

Migrating utilizing AWS SCT

AWS SCT automates a lot of the method of changing your knowledge warehouse schema to an Amazon Redshift database schema. As a result of the supply and goal database engines can have many various options and capabilities, AWS SCT makes an attempt to create an equal schema in your goal database wherever attainable. If no direct conversion is feasible, AWS SCT creates a database migration evaluation report that can assist you convert your schema. The database migration evaluation report gives vital details about the conversion of the schema out of your supply database to your goal database. The report summarizes all of the schema conversion duties and particulars the motion gadgets for schema objects that may’t be transformed to the DB engine of your goal database. The report additionally consists of estimates of the quantity of effort that it’ll take to jot down the equal code in your goal database that may’t be transformed robotically.

Storage optimization is the center of a knowledge warehouse conversion. When utilizing your Amazon Redshift database as a supply and a take a look at Amazon Redshift database because the goal, AWS SCT recommends type keys and distribution keys to optimize your database.

With AWS SCT, you’ll be able to convert the next knowledge warehouse schemas to Amazon Redshift:

• Amazon Redshift
• Azure Synapse Analytics (model 10)
• Greenplum Database (model 4.3 and later)
• Microsoft SQL Server (model 2008 and later)
• Netezza (model 7.0.3 and later)
• Oracle (model 10.2 and later)
• Snowflake (model 3)
• Teradata (model 13 and later)
• Vertica (model 7.2 and later)

At AWS, we proceed to launch new options and enhancements to enhance our product. For the most recent supported conversions, go to the AWS SCT Person Information.

Migrating knowledge utilizing AWS SCT knowledge extraction software

You should use an AWS SCT knowledge extraction software to extract knowledge out of your on-premises knowledge warehouse and migrate it to Amazon Redshift. The agent extracts your knowledge and uploads the information to both Amazon S3 or, for large-scale migrations, an AWS Snowball Household service. You possibly can then use AWS SCT to repeat the information to Amazon Redshift. Amazon S3 is a storage and retrieval service. To retailer an object in Amazon S3, you add the file you need to retailer to an S3 bucket. Once you add a file, you’ll be able to set permissions on the article and likewise on any metadata.

In large-scale migrations involving knowledge add to a AWS Snowball Household service, you need to use wizard-based workflows in AWS SCT to automate the method by which the information extraction software orchestrates AWS DMS to carry out the precise migration.

Concerns for Amazon Redshift migration instruments

To enhance and speed up knowledge warehouse migration to Amazon Redshift, think about the next suggestions and greatest practices. Tthis checklist just isn’t exhaustive. Be sure you have a superb understanding of your knowledge warehouse profile and decide which greatest practices you need to use in your migration venture.

• Use AWS SCT to create a migration evaluation report and scope migration effort.
• Automate migration with AWS SCT the place attainable. The expertise from our prospects exhibits that AWS SCT can robotically create nearly all of DDL and SQL scripts.
• When automated schema conversion just isn’t attainable, use customized scripting for the code conversion.
• Set up AWS SCT knowledge extractor brokers as shut as attainable to the information supply to enhance knowledge migration efficiency and reliability.
• To enhance knowledge migration efficiency, correctly dimension your Amazon Elastic Compute Cloud (Amazon EC2) occasion and its equal digital machines that the information extractor brokers are put in on.
• Configure a number of knowledge extractor brokers to run a number of duties in parallel to enhance knowledge migration efficiency by maximizing the utilization of the allotted community bandwidth.
• Regulate AWS SCT reminiscence configuration to enhance schema conversion efficiency.
• Use Amazon S3 to retailer the massive objects resembling pictures, PDFs, and different binary knowledge out of your present knowledge warehouse.
• Emigrate massive tables, use digital partitioning and create sub-tasks to enhance knowledge migration efficiency.
• Perceive the use circumstances of AWS companies resembling Direct Join, the AWS Switch Household, and the AWS Snow Household. Choose the precise service or software to satisfy your knowledge migration necessities.
• Perceive AWS service quotas and make knowledgeable migration design choices.

Abstract

Information is rising in quantity and complexity sooner than ever. Nevertheless, solely a fraction of this invaluable asset is on the market for evaluation. Conventional on-premises knowledge warehouses have inflexible architectures that don’t scale for contemporary massive knowledge analytics use circumstances. These conventional knowledge warehouses are costly to arrange and function, and require massive upfront investments in each software program and {hardware}.

On this submit, we mentioned Amazon Redshift as a totally managed, scalable, trendy knowledge warehouse that may show you how to analyze all of your knowledge, and obtain efficiency at any scale with low and predictable value. Emigrate your knowledge warehouse to Amazon Redshift, you want to think about a variety of things, resembling the full dimension of the information warehouse, knowledge change charge, and knowledge transformation complexity, earlier than choosing an appropriate migration technique and course of to scale back the complexity and value of your knowledge warehouse migration venture. With AWS companies such AWS SCT and AWS DMS, and by adopting the information and the very best practices of those companies, you’ll be able to automate migration duties, scale migration, speed up the supply of your knowledge warehouse migration venture, and delight your prospects.

Concerning the Creator

Lewis Tang is a Senior Options Architect at Amazon Net Providers primarily based in Sydney, Australia. Lewis gives companions steerage to a broad vary of AWS companies and assist companions to speed up AWS follow development.