Phase 2: Core Cloud Services

Data lake architecture & query-in-place with Athena or BigQuery

Intermediate ~3 min read
Think of it this way A friendly analogy. Read this if the technical version feels dense. Show Hide

Imagine a super-duper enormous library storage room. Instead of sorting every single new book, magazine, or even a handwritten note into perfect shelves right away, you just put everything in this one giant room exactly as it arrives. It doesn't matter if it’s a finished novel, a half-scribbled poem, or just a drawing – it all goes in. You don't need to decide where it should go or how it should be categorized until much later, if ever. This "store everything first" approach is super handy because you never know what information might be important later, and you don't waste time sorting things you might not even use. This giant, flexible storage room, often existing on special cloud services like AWS S3 or Google Cloud Storage, is what we call a "data lake."

Now, imagine you need to find all the books written by authors whose names start with 'S', that have a dragon on the cover, and were published after 2010. Normally, you'd have to physically go through shelves, pull out books, and read summaries. But with a "query-in-place" tool, it's like having a magical, super-fast librarian who can instantly scan every single item in that giant storage room without moving a single book or sorting anything. You just tell them your question (your "query"), and they zoom through everything right where it sits, finding all the matches and telling you exactly what you asked for. They don't take the books out and put them on a special "dragon-book" shelf; they just give you the answer directly from the massive collection.

Tools like AWS Athena and Google BigQuery are these amazing super-librarians. They let you ask questions about the mountain of "books" in your data lake without ever having to move or re-organize your collection. This means if you’re building a new project or trying to figure something out, you don't have to wait for someone to meticulously sort and categorize every piece of information. You can just throw all your notes, drawings, stories, and other data into your data lake and then use these smart tools to instantly ask any question you want, whenever you want. This makes it incredibly flexible and fast for exploring new ideas!

A data lake is a centralized repository that allows you to store all your structured, semi-structured, and unstructured data at any scale. Unlike traditional data warehouses that require data to be cleaned and transformed into a specific schema before ingestion, a data lake stores data in its raw, native format. The foundation of a modern cloud data lake is highly scalable, durable, and cost-effective object storage services like AWS S3 or Google Cloud Storage (GCS). This "store everything first, decide schema later" approach provides immense flexibility, enabling diverse analytical workloads without upfront data modeling constraints.

The real power of a data lake, especially for exploration and ad-hoc analysis, comes from "query-in-place" capabilities. This means you can directly run SQL queries against the data stored in your object storage without needing to load it into a separate database or analytics engine. AWS Athena and Google BigQuery (with external tables) are prime examples of such services. Athena allows you to query S3 data using standard SQL, while BigQuery can query data directly from GCS. These tools don't physically move your data; instead, they act as query engines that dynamically read and process data residing in your cloud storage bucket, often leveraging metadata catalogs to understand the data's structure.

For a Cloud Architect, understanding this paradigm shift is crucial. It enables rapid prototyping, democratizes data access, and significantly reduces the initial ETL burden often associated with data projects. You define schemas on top of raw data using services like AWS Glue Data Catalog, which Athena then uses to interpret your S3 files. Similarly, BigQuery external tables serve this purpose for GCS. To optimize performance and cost, particularly for large datasets, architects should implement data partitioning (e.g., by date or region) and choose columnar storage formats like Parquet or ORC. This ensures queries only scan necessary data, keeping costs down and improving query speeds.

Key Takeaways

  • Data lakes centralize raw, multi-format data on highly scalable object storage (S3/GCS).
  • "Query-in-place" tools like Athena (for S3) and BigQuery (for GCS) allow direct SQL querying of data without movement.
  • This approach provides agility for data exploration and cost efficiency (pay-per-query, no upfront ETL).
  • Metadata catalogs (AWS Glue Data Catalog) or external table definitions are used to apply schemas to raw data.
  • Implementing data partitioning and using columnar formats (Parquet/ORC) are critical for optimizing query performance and cost.

Code Example

sql
CREATE EXTERNAL TABLE IF NOT EXISTS my_raw_web_logs (
  `ip_address` string,
  `timestamp` string,
  `request` string,
  `status_code` int,
  `bytes_sent` int,
  `user_agent` string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
  'separatorChar' = ',',
  'escapeChar' = '\\',
  'quoteChar' = '"'
)
LOCATION 's3://your-data-lake-bucket/raw/weblogs/yearly=2023/'
TBLPROPERTIES ('has_encrypted_data'='false', 'skip.header.line.count'='1');

How this code works

This SQL statement defines a virtual table, my_raw_web_logs, in Athena or BigQuery that allows querying raw web server logs stored in an S3 data lake. Instead of loading data into a database, this CREATE EXTERNAL TABLE command registers the location and structure of existing data files. It effectively overlays a database schema onto files already present in S3, enabling query-in-place capabilities. The initial block specifies the columns like ip_address and request, along with their data types, mapping them to the fields within the raw log files.

The ROW FORMAT SERDE clause is critical; it tells the query engine how to interpret the raw data by using the OpenCSVSerde to parse comma-separated values. WITH SERDEPROPERTIES then fine-tunes this parser, specifying the exact 'separatorChar', 'escapeChar', and 'quoteChar' used in the CSV files. Crucially, the LOCATION parameter points directly to the S3 path where the actual log files reside, in this case, a specific yearly partition. A subtle but important detail is skip.header.line.count='1' within TBLPROPERTIES; this instructs the engine to ignore the first row of each file, preventing the header from being mistakenly read as data. This setup allows SQL queries to run directly against the S3 data without moving or transforming it.

Related Articles

Hand-picked reads to go deeper on s3, cloud storage & data lakes.

Back to Cloud Architect Roadmap