7.3 Graph & Analytical Querying with Amazon Neptune, OpenSearch & Athena

7.3 Graph & Analytical Querying with Amazon Neptune, OpenSearch & Athena

The Specialty Data Store Paradigm

In modern data engineering architectures, no single database engine satisfies all operational and analytical access patterns. The concept of Polyglot Persistence advocates leveraging purpose-built data stores engineered specifically for distinct data models:

• Relational / OLTP (RDS, Aurora): Acid compliance, complex table transactions.
• Key-Value / NoSQL (DynamoDB): Single-digit millisecond key lookups at scale.
• Data Warehousing / OLAP (Redshift): Columnar MPP aggregations over petabytes.
• Graph Analytics (Neptune): Traverse multi-hop entity relationships with graph-native operators; cost still grows with the paths explored.
• Search & Vector Search (OpenSearch): Unstructured text matching and high-dimensional vector embeddings.
• Serverless Data Lake Analytics (Athena): Ad-hoc ANSI SQL queries directly over Amazon S3 objects.

---

Amazon Neptune: Purpose-Built Graph Database Engine

Amazon Neptune is a high-performance, fully managed graph database engine optimized for storing and querying highly connected datasets.

Why Relational Databases Fail for Graph Workloads

In a relational database, representing graph structures (such as social networks, financial fraud trees, or supply chains) requires join tables (many-to-many junction tables). Executing a multi-hop query (e.g. "Find all friends of friends who purchased the same product as User X") requires cascading JOIN operations:

-- Relational Multi-Hop JOIN (Exponential Performance Degradation)
SELECT p2.product_name 
FROM users u
JOIN friendships f1 ON u.id = f1.user_id
JOIN friendships f2 ON f1.friend_id = f2.user_id
JOIN purchases p1 ON f2.friend_id = p1.user_id
JOIN products p2 ON p1.product_id = p2.id
WHERE u.id = 'USR-8841';

Relational multi-hop queries can require repeated joins and large intermediate results. Neptune provides graph-native storage, indexes, and traversal operators, but runtime still depends on graph degree, filters, path length, and the portion of the graph explored.

Neptune Graph Models & Query Languages

Neptune supports two distinct graph frameworks within the same engine:

Neptune Storage Architecture

Like Amazon Aurora, Neptune features a cloud-native decoupled storage engine. A Neptune storage volume automatically scales up to 128 TiB and replicates data 6 ways across 3 Availability Zones, supporting up to 15 low-latency read replicas.

---

Amazon OpenSearch Service: Search, Log Analytics & Vector Database

Amazon OpenSearch Service (successor to Amazon Elasticsearch Service) is a managed distributed search and analytics suite used for real-time log analytics, application search, monitoring, and generative AI vector retrieval.

Cluster Architecture & Node Roles

An OpenSearch cluster comprises specialized node types:

• Dedicated Manager Nodes: Manage cluster state, shard routing, and index creation.
• Data Nodes: Store indexed documents and execute search/aggregation queries.
• UltraWarm & Cold Storage: Decouples historical log storage by offloading cold indices to Amazon S3 while maintaining query access via OpenSearch Dashboards.

Full-Text Inverted Indexing vs. Vector Search

1. Full-Text Search: Uses inverted indexes (built via Apache Lucene) to analyze text tokens, compute TF-IDF / BM25 relevancy scores, and deliver instant search results.
2. Vector Database & k-NN: OpenSearch includes a native k-Nearest Neighbor (k-NN) plugin. High-dimensional vector embeddings generated by machine learning models (e.g. Amazon Bedrock, Titan) are stored in vector fields. OpenSearch executes similarity searches using HNSW (Hierarchical Navigable Small World) algorithms for Retrieval-Augmented Generation (RAG) pipelines.

---

Amazon Athena: Serverless Interactive Analytics on S3

Amazon Athena is an interactive, serverless query service that allows data engineers to analyze data directly in Amazon S3 using standard ANSI SQL. Athena requires zero ETL, zero infrastructure provisioning, and no cluster management.

Architectural Underpinnings

• Distributed Engine: Built on open-source Presto and Trino query engines.
• Data Catalog Integration: Connects seamlessly with the AWS Glue Data Catalog to resolve table definitions, data types, and S3 partition structures.
• Pricing Model: Billed strictly per query based on total terabytes of data scanned (with Regional bytes-scanned rates or provisioned capacity). Minimizing data scanned via columnar file formats and partitioning directly translates to cost optimization.

Athena SQL Query ---> [ Presto/Trino Query Planner ] ---> Consults AWS Glue Data Catalog
                                                     ---> Scans Partitioned Parquet on S3

Athena Performance & Cost Optimization Techniques

1. Columnar File Formats (Apache Parquet / ORC): Convert raw JSON/CSV files to Parquet. Columnar storage permits Athena to scan only the specific columns requested in a SELECT statement, reducing data scanned by up to 90%.

2. Data Compression: Compress S3 files using Snappy or GZIP. Athena scans fewer bytes from disk, improving query speeds.

3. Partitioning & Partition Projection:

   • Standard Partitioning: S3 path prefixes like s3://my-bucket/logs/year=2026/month=08/day=13/ limit query scans.
   • The Glue Catalog Bottleneck: As partition counts grow into millions, running MSCK REPAIR TABLE or querying Glue Data Catalog APIs introduces severe latency and throttling.
   • Partition Projection: Configured directly in table DDL TBLPROPERTIES. Athena calculates partition locations dynamically using rules (e.g. date ranges, integer ranges) without calling the Glue Data Catalog API, drastically accelerating query performance.

4. Athena Federated Query: Extends Athena beyond S3. Using custom AWS Lambda connectors, data engineers can run unified ANSI SQL queries joining S3 Parquet tables directly with live data stored in DynamoDB, Amazon RDS, Redshift, or ElastiCache.

5. CTAS & Apache Iceberg Support:

   • CTAS (CREATE TABLE AS SELECT): Creates a new Athena table and writes transformed query results back to S3 as partitioned Parquet files in a single operation.
   • Apache Iceberg Format: Athena natively supports Apache Iceberg table format, bringing ACID transaction guarantees, time-travel point-in-time queries, and automated schema evolution to S3 data lakes.

---

Code Example: Neptune Gremlin Query, OpenSearch Vector Search, and Athena DDL with Partition Projection

-- Athena DDL Table Creation utilizing Partition Projection for fast date routing
CREATE EXTERNAL TABLE IF NOT EXISTS clickstream_logs (
    event_id STRING,
    user_id STRING,
    event_type STRING,
    page_url STRING,
    response_time_ms INT
)
PARTITIONED BY (
    event_date STRING
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe'
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION 's3://datacenter-analytics-lake/clickstream/'
TBLPROPERTIES (
    'projection.enabled' = 'true',
    'projection.event_date.type' = 'date',
    'projection.event_date.range' = '2025-01-01,NOW',
    'projection.event_date.format' = 'yyyy-MM-dd',
    'projection.event_date.interval' = '1',
    'projection.event_date.interval.unit' = 'DAYS',
    'storage.location.template' = 's3://datacenter-analytics-lake/clickstream/event_date=${event_date}/'
);

# Python Gremlin Query example for Amazon Neptune
from gremlin_python.driver.driver_remote_connection import DriverRemoteConnection
from gremlin_python.process.anonymous_traversal import traversal

# Connect to Neptune Cluster Endpoint inside Private Subnet
neptune_endpoint = 'wss://your-neptune-cluster.neptune.amazonaws.com:8182/gremlin'
g = traversal().withRemote(DriverRemoteConnection(neptune_endpoint, 'g'))

def find_suspicious_fraud_network(user_id: str):
    """
    Traverses Neptune Property Graph to find multi-hop shared credit card accounts.
    """
    try:
        # Gremlin query: User -> HasCard -> Card <- HasCard <- OtherUser
        shared_accounts = g.V().has('User', 'userId', user_id) \
                           .out('hasCreditCard') \
                           .in_('hasCreditCard') \
                           .hasLabel('User') \
                           .values('userId') \
                           .toList()
        print(f"Detected {len(shared_accounts)} accounts linked by shared credit card.")
        return shared_accounts
    except Exception as e:
        print(f"Neptune traversal error: {str(e)}")
        raise e