Shedding Light on Dark Data: Unlocking Insights with Amazon Redshift Spectrum

In the era of big data, companies are drowning in information. They‘re collecting and storing massive volumes of data from a myriad of sources, often in cheap, scalable object storage services like Amazon S3. This data piles up in vast "data lakes," growing murkier and harder to navigate over time.

As of 2020, S3 alone was storing over 100 trillion objects, with growth accelerating every year. According to Cisco, annual global data center IP traffic will reach a staggering 20.6 zettabytes (ZB) by 2021. For perspective, that‘s equivalent to over 26 trillion hours of 4K video streaming!

Clearly, enterprises understand the potential value of this data—why else would they bother keeping it around? Yet analysts estimate that 80% of this data is "dark data"—collected and stored but not being leveraged for analysis or decision-making.

Year	Global Data Volume (ZB)	Dark Data Volume (ZB)
2016	6.8	5.4
2017	8.9	7.1
2018	11.6	9.3
2019	15.0	12.0
2020	19.4	15.5
2021	24.8	19.8

Source: IDC

This dark data is difficult to access, often unstructured, and its sheer volume makes it impractical to process using traditional methods. It sits idle in cold storage, with its insights untapped and its value unrealized.

Imagine you‘re an explorer equipped with only a small flashlight in a vast, dark cave glittering with hidden treasures. Your light can only illuminate a tiny sliver of the cave at a time. You know there are valuable insights buried in there, but the thought of trying to explore the entire cavern is overwhelming.

But what if you could upgrade to a powerful set of floodlights that could illuminate the entire cave at once? You‘d be able to quickly spot patterns, identify the most promising areas to explore, and unearth treasures you never knew existed.

Enter Amazon Redshift Spectrum—a feature of Amazon Redshift that lets you query data directly in Amazon S3 data lakes using familiar SQL. With Spectrum, you can shine a light on exabytes of dark data, extracting insights without the cost and complexity of moving it to a data warehouse first.

Why Redshift Spectrum is a Game Changer

To understand what makes Redshift Spectrum so revolutionary, let‘s look at common approaches for analyzing data lakes today:

Amazon EMR (Elastic MapReduce)

Amazon EMR lets you process huge datasets using open source tools like Apache Spark, Hive, and Presto deployed on managed clusters of EC2 instances. You can run petabyte-scale ETL, analytics, and machine learning jobs.

However, you need specialized skills to configure and optimize these clusters and rewrite your queries for these processing frameworks. It‘s like building your own industrial-grade spelunking gear from scratch to explore a cave.

Amazon Athena

Amazon Athena is an interactive query service that makes it easy to analyze data directly in Amazon S3 using standard SQL. You can run ad-hoc queries on data in a variety of formats without managing infrastructure.

But Athena can‘t join that S3 data with data in your Amazon Redshift clusters. It‘s like having a flashlight that can explore certain sections of the cave but can‘t connect the findings to other areas you‘ve already mapped.

Loading Data into Redshift

You can also extract, transform, and load (ETL) data from S3 into Redshift tables for analysis with the rest of your structured data. But all that data movement takes time and effort. It‘s like hauling treasure out of the cave, processing it, and moving it to a separate storage room for analysis.

Plus, you need to store all that duplicate data in Redshift, which rapidly gets expensive. It‘s like paying to build an entirely new storage cave and filling it with copies of treasure from the original cave.

While these approaches work, they have key limitations:

• Significant learning curve and engineering effort
• Rigid, time-consuming ETL pipelines
• Expensive data storage and movement
• Disconnected data silos
• Infrastructure to deploy and manage

But what if you could leave the data in S3 and query it in-place using the same BI tools and SQL you already use with Redshift? What if you could join that dark data with your existing structured data in Redshift for end-to-end insights?

That would give you the power to fully explore your cave of dark data, automatically bring your most valuable treasures to light, and analyze them alongside your existing trove of insights.

How Redshift Spectrum Works

This is exactly what Amazon Redshift Spectrum makes possible. With Spectrum, you can run complex, analytical SQL queries on data stored in S3 without loading it into Redshift. It separates storage and compute, letting you scale them independently and leverage S3‘s cheap, durable, and endlessly scalable storage for your data lake.

Source: AWS Redshift Spectrum Documentation

Here‘s how it works:

1. First, you create external tables in your Redshift cluster that map to data stored in S3. You define the structure and location of the data and can optionally partition it and specify file formats. These external tables are stored in an external data catalog like the AWS Glue Data Catalog or an Apache Hive metastore.

2. When you run a query in Redshift that references an external table, Redshift‘s query optimizer routes the S3 portion of the query to Spectrum. Redshift scans the external table metadata and generates a query plan that pushes S3 data processing to Spectrum while handling any local data processing itself.

3. Spectrum spawns thousands of nodes that partition the S3 data and process it in parallel. Each node can process one or more data chunks, dramatically accelerating performance. The nodes securely access S3 using IAM roles and VPC endpoints.

4. Spectrum aggregates the intermediate results from each node and returns the final result to Redshift. Redshift combines this with results from any local processing and returns the complete result to the user or application.

Source: AWS Big Data Blog

This all happens transparently in seconds, without you having to provision or manage a single node. You get the power of Redshift with the near-infinite scale of S3.

It‘s like having an army of robot assistants that can instantly swarm all over the dark data cave, shine their lights everywhere at once, and report back on everything they found and how it connects to your existing knowledge.

When to Use Redshift Spectrum

So when does it make sense to use Spectrum versus the other approaches we mentioned earlier? Here are some prime use cases:

Querying Massive Datasets

Let‘s say you work for a global media conglomerate that has petabytes of dark data in S3—server logs, mobile app clickstreams, legacy archived data, etc. You want to correlate this unstructured behavioral data with your structured subscriber and content metadata in Redshift.

With Spectrum, you can run queries that scan hundreds of terabytes—even petabytes—of that S3 data at a time without needing to load it into Redshift. You get the insights without the ETL headaches.

Joining S3 Data with Redshift

Continuing the media example, suppose you want to analyze how the viewing history in your S3 logs influences subscriber churn. You have a subscribers dimension table in Redshift with core subscriber attributes and a churn fact table with daily churn status snapshots.

With Spectrum, you can join those Redshift tables with an external viewership table on S3 to connect the dots between viewing patterns and churn. You can even write the results of your queries back to S3 for further analysis in Redshift or other tools.

SELECT s.subscriber_id, s.account_type, v.genre, v.watch_hours, c.is_churned
FROM subscribers s
JOIN spectrum.viewership v ON s.subscriber_id = v.subscriber_id  
LEFT JOIN churn c ON s.subscriber_id = c.subscriber_id

Archiving Infrequently Accessed Data

Many companies keep several years of historical data in Redshift for trend analysis and regulatory compliance. But the older that data gets, the less frequently it‘s queried. Meanwhile, it takes up valuable space in Redshift‘s local SSD storage.

With Spectrum, you can offload that cold, historical data to S3 while still being able to query it seamlessly from Redshift when needed. This keeps your Redshift cluster lean and mean for critical workloads.

Enabling Self-Service BI

Picture a large retail chain with a central Redshift data warehouse used for core reporting and business intelligence. Regional store managers are constantly clamoring for access to more raw data to splice and dice in their own ways. But there are too many of them and too much data to provision Redshift clusters for each of them.

With Spectrum, you can ingest all that raw data from stores into S3 and let the regional managers go to town with their SQL queries and BI tools. They get the data access and agility they crave without disrupting the core EDW.

Migrating from On-Prem to Cloud

Many organizations today are migrating their on-premises data warehouses and Hadoop clusters to the cloud for scalability and cost efficiency. But moving petabytes of data is daunting.

With Spectrum, you can quickly lift-and-shift your on-prem data to S3 and start querying it with Redshift Spectrum right away as a first step in your migration journey. You can gradually load data from S3 into Redshift‘s local storage over time as you optimize your analytics.

Exploring Data Lakes

Finally, Spectrum is a great way to explore and experiment with new datasets you‘re considering ingesting into your data warehouse. You can land the raw data in S3 and query it with Spectrum to get a feel for its structure, quality, and value before investing in formal data modeling and ETL.

It‘s like doing a quick initial survey of a new section of the cave before deciding whether to commit to a full-scale mining operation.

Redshift Spectrum Best Practices

To get the most mileage out of Redshift Spectrum, consider these tips:

Partition and bucket your data

By partitioning your S3 data based on frequently filtered columns like date, region, or product category and using S3‘s best practices for directory structure and file sizing, you can dramatically improve query performance and reduce the amount of data scanned per query. This saves you time and money.

Compress and convert your data

Spectrum charges you by the amount of S3 data scanned per query. Converting your data from raw text formats to compressed, columnar formats like Apache Parquet can slash that data volume by 50-90%, making your queries cheaper and faster.

Optimize your file sizes

Avoid data in S3 that consists of tons of tiny files. Instead, aim for fewer, larger files in the 100MB – 1GB range. This reduces the overhead of opening and closing files, improving parallelism and throughput.

Tune your queries

Use Redshift‘s query monitoring rules to track Spectrum queries and set up alerts for excessive scan times or data volumes. Use techniques like columnar filtering, predicate pushdown, and aggregate projections to minimize the data processed per query. Consult the Redshift Spectrum best practices guide for more tips.

Conclusion

With Amazon Redshift Spectrum, you can extend your existing data warehouse to analyze exabytes of unstructured data in your S3 data lake without compromising on performance or flexibility. By shining a light on your dark data, you can extract hidden insights that illuminate new opportunities and transform your business.

Like an army of robot spelunkers, Redshift Spectrum can help you plumb the depths of your data cave, hauling treasure to the surface to be analyzed alongside your existing hoard of insights.

So saddle up your squadron of Spectrum robots and set forth into the darkness of your data lake. You never know what valuable discoveries await when you light up the shadows of your dark data.