This submit is co-authored with Guillaume Saint-Martin at Solar King.
Solar King is the world’s main off-grid photo voltaic power firm, and is on a mission to energy entry to brighter lives via off-grid photo voltaic. Solar King designs, distributes, installs, and funds photo voltaic house power merchandise for folks presently dwelling with out dependable power entry. It serves over 100 million customers in 65 international locations the world over.
Over 26,000 brokers throughout Africa as we speak assist native households get entry to Solar King off-grid merchandise to have extra productive lives. These brokers are knowledgeable in near-real time to seek out the suitable geographical areas and households who wouldn’t have entry to low price energy. Solar King is pushed by knowledge for analyzing areas of progress throughout hundreds of miles utilizing a dashboards which are powered by Amazon Redshift.
On this submit, we share how Solar King makes use of Amazon Redshift and Redshift’s options like Information Sharing capabilities to enhance the efficiency of queries in Looker for over 1,000 of our workers.
Amazon Redshift is a completely managed, scalable cloud knowledge warehouse that accelerates your time to insights with quick, straightforward, and safe analytics at scale. Tens of hundreds of shoppers depend on Amazon Redshift to investigate exabytes of knowledge and run advanced analytical queries, making it a broadly used cloud knowledge warehouse. You may run and scale analytics in seconds on all of your knowledge with out having to handle your knowledge warehouse infrastructure.
Use case
Solar King makes use of a Redshift provisioned cluster to run its extract, rework and cargo (ETL) and analytics processes to supply and rework knowledge from varied sources. It then gives entry to this knowledge for enterprise customers via Looker. Amazon Redshift presently manages diverse consumption necessities for Looker customers throughout the globe
Amazon Redshift is used to wash and mixture knowledge into pre-processed tables, execute Solar King’s ETL pipelines, and course of Looker “persistent derived tables” (PDTs) scheduled at an hourly frequency or much less. These ETLs pipelines and PDTs have been competing workloads and generally bumped into learn/write conflicts.
As data-driven firm continues increasing, Solar King wanted an answer that does the next:
- Permits a whole lot of queries to run in parallel with desired question throughput.
- Optimize workload administration to allow ETL, enterprise intelligence (BI4) and Looker workloads to run concurrently with out impacting one another.
- Seamlessly scale capability with the rise in person base and preserve price effectivity.
Resolution overview
As the information volumes, question counts, and customers proceed to develop, Solar King determined to maneuver from a single cluster to a multi-cluster structure with knowledge sharing to reap the benefits of workload isolation and separate ETL and analytics workloads throughout completely different clusters whereas nonetheless utilizing a single copy of the information.
The answer at Solar King is comprised of a number of Redshift provisioned clusters and an Amazon Elastic Compute Cloud (EC2) Community Load Balancer, utilizing the information sharing functionality in Amazon Redshift.
Amazon Redshift Information Sharing permits knowledge entry throughout Redshift clusters with out having to repeat or transfer knowledge. Due to this fact, when a workload is moved from one Redshift cluster to a different, the workload can proceed to entry knowledge within the preliminary Redshift cluster. For extra info, discuss with Sharing Amazon Redshift knowledge securely throughout Amazon Redshift clusters for workload isolation.
The answer consists of the next key parts:
- Core ETL cluster: A core ETL producer cluster (8 ra3.xlplus nodes) with knowledge share.
- Looker cluster: A producer/shopper cluster (8 ra3.4xlarge nodes) with knowledge share to run the next:
- Giant ETL processes
- Looker initiated ETL processes (PDTs)
- Information group workloads
- BI clusters: This consists of 4 giant shopper clusters (6 ra3.4xlarge nodes every):
- Three clusters utilizing reserved situations (RIs) which are on 24/7
- One on-demand cluster turned on for six hours each weekday
- Community Load Balancer: The community load balancer distributes queries originating from Looker between the buyer clusters
- Concurrency scaling free tier: Every of the three clusters utilizing reserved situations (RIs) produces one hour of concurrency scaling credit per day, that are used on Mondays, whereas the on-demand cluster produces 4 hours of concurrency scaling credit protecting the concurrency scaling price underneath free tier.
The next diagram exhibits the answer and workflow steps
Outcomes
Solar King noticed the next enhancements with this answer:
- Efficiency – The advance in efficiency was drastic and quick after implementing the distributed producer/shopper structure. Most queries (95%) that used to take between 50-90 seconds to finish prior to now take at most 40 seconds, 75% of queries used to take as much as 5 seconds prior to now take lower than one second. Moreover, the variety of queries run (Amazon Redshift Adoption) elevated by 40%, pushed by a higher utilization of Looker following the structure change.
- Workload administration – After this architectural change, queries don’t spend a very long time queued anymore. The next chart illustrates queued vs operating queries on one of many clusters earlier than and after the modernization engagement.
- Scalability – With this Redshift knowledge share enabled structure, the Solar King knowledge group was in a position to deliver again a suitable efficiency to its customers, resulting in renewed engagement , measured with the doubling of the variety of month-to-month queries over the next few month, thus rising adoption of Amazon Redshift throughout the corporate.
Solar King prices are estimated to solely enhance by 35%, by reserving most situations used for 3 years (26 ra3.4xlarge and eight ra3.xlplus) and counting on the concurrency scaling free tier for a lift of efficiency on the day of highest utilization. That is in comparison with the smaller variety of reserved clusters (8 ra3.4xlarge) and a a lot bigger utilization of concurrency scaling (two concurrency scaling clusters, practically all the time on). This modernization elevated the productiveness of the brokers by offering them sooner and close to actual time entry to areas that want entry to low price energy.
Conclusion:
On this submit, we mentioned how Solar King used Amazon Redshift knowledge sharing capabilities to distribute workload and scale Amazon Redshift to handle end-user efficiency necessities from Looker and preserve management over the price of Amazon Redshift consumption. Attempt the approaches mentioned on this submit and tell us your suggestions within the feedback.
Concerning the authors
Guillaume Saint-Martin leads the Information and Analytics group at Solar King. With 10 years of expertise within the knowledge and growth sectors, he manages a group of over 30 analysts, knowledge engineers, and knowledge scientists to help Solar King long run modeling and development evaluation.
Aaber Jah is a Senior Analytics Specialist at AWS primarily based in Chicago, Illinois. He focuses on driving and sustaining AWS Information Analytics enterprise worth for patrons.
Rohit Vashishtha is a Senior Analytics Specialist Options Architect at AWS primarily based in Dallas, Texas. He has over 17 years of expertise architecting, constructing, main, and sustaining large knowledge platforms. Rohit helps prospects modernize their analytic workloads utilizing the breadth of AWS providers and ensures that prospects get one of the best worth/efficiency with utmost safety and knowledge governance.
