Nosql Specialist

// Flexible document modeling with validation

// User profile with embedded and referenced data
const userSchema = {
  validator: {
    $jsonSchema: {
      bsonType: "object",
      required: ["email", "profile", "createdAt"],
      properties: {
        _id: { bsonType: "objectId" },
        email: {
          bsonType: "string",
          pattern: "^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\\.[a-zA-Z]{2,}$"
        },
        profile: {
          bsonType: "object",
          required: ["firstName", "lastName"],
          properties: {
            firstName: { bsonType: "string", maxLength: 50 },
            lastName: { bsonType: "string", maxLength: 50 },
            avatar: { bsonType: "string" },
            bio: { bsonType: "string", maxLength: 500 },
            preferences: {
              bsonType: "object",
              properties: {
                theme: { enum: ["light", "dark", "auto"] },
                language: { bsonType: "string", maxLength: 5 },
                notifications: {
                  bsonType: "object",
                  properties: {
                    email: { bsonType: "bool" },
                    push: { bsonType: "bool" },
                    sms: { bsonType: "bool" }
                  }
                }
              }
            }
          }
        },
        // Embedded addresses for quick access
        addresses: {
          bsonType: "array",
          maxItems: 5,
          items: {
            bsonType: "object",
            required: ["type", "street", "city", "country"],
            properties: {
              type: { enum: ["home", "work", "billing", "shipping"] },
              street: { bsonType: "string" },
              city: { bsonType: "string" },
              state: { bsonType: "string" },
              postalCode: { bsonType: "string" },
              country: { bsonType: "string", maxLength: 2 },
              isDefault: { bsonType: "bool" }
            }
          }
        },
        // Reference to orders (avoid embedding large arrays)
        orderCount: { bsonType: "int", minimum: 0 },
        lastOrderDate: { bsonType: "date" },
        totalSpent: { bsonType: "decimal" },
        status: { enum: ["active", "inactive", "suspended"] },
        tags: {
          bsonType: "array",
          items: { bsonType: "string" }
        },
        createdAt: { bsonType: "date" },
        updatedAt: { bsonType: "date" }
      }
    }
  }
};

// Create collection with schema validation
db.createCollection("users", userSchema);

// Compound indexes for common query patterns
db.users.createIndex({ "email": 1 }, { unique: true });
db.users.createIndex({ "status": 1, "createdAt": -1 });
db.users.createIndex({ "profile.preferences.language": 1, "status": 1 });
db.users.createIndex({ "tags": 1, "totalSpent": -1 });

// Aggregation pipeline for complex analytics

const userAnalyticsPipeline = [
  // Match active users from last 6 months
  {
    $match: {
      status: "active",
      createdAt: { $gte: new Date(Date.now() - 6 * 30 * 24 * 60 * 60 * 1000) }
    }
  },
  
  // Add computed fields
  {
    $addFields: {
      registrationMonth: { $dateToString: { format: "%Y-%m", date: "$createdAt" } },
      hasMultipleAddresses: { $gt: [{ $size: "$addresses" }, 1] },
      isHighValueCustomer: { $gte: ["$totalSpent", 1000] }
    }
  },
  
  // Group by registration month
  {
    $group: {
      _id: "$registrationMonth",
      totalUsers: { $sum: 1 },
      highValueUsers: {
        $sum: { $cond: ["$isHighValueCustomer", 1, 0] }
      },
      avgSpent: { $avg: "$totalSpent" },
      usersWithMultipleAddresses: {
        $sum: { $cond: ["$hasMultipleAddresses", 1, 0] }
      },
      topSpenders: {
        $push: {
          $cond: [
            { $gte: ["$totalSpent", 500] },
            { userId: "$_id", spent: "$totalSpent", email: "$email" },
            "$$REMOVE"
          ]
        }
      }
    }
  },
  
  // Sort by registration month
  { $sort: { _id: 1 } },
  
  // Add percentage calculations
  {
    $addFields: {
      highValuePercentage: {
        $multiply: [{ $divide: ["$highValueUsers", "$totalUsers"] }, 100]
      },
      multiAddressPercentage: {
        $multiply: [{ $divide: ["$usersWithMultipleAddresses", "$totalUsers"] }, 100]
      }
    }
  }
];

// Execute aggregation with explain for performance analysis
const results = db.users.aggregate(userAnalyticsPipeline).explain("executionStats");

// Transaction support for multi-document operations
const session = db.getMongo().startSession();

session.startTransaction();
try {
  // Update user profile
  db.users.updateOne(
    { _id: userId },
    { 
      $set: { "profile.lastName": "NewLastName", updatedAt: new Date() },
      $inc: { version: 1 }
    },
    { session: session }
  );
  
  // Create audit log entry
  db.auditLog.insertOne({
    userId: userId,
    action: "profile_update",
    changes: { lastName: "NewLastName" },
    timestamp: new Date(),
    sessionId: session.getSessionId()
  }, { session: session });
  
  session.commitTransaction();
} catch (error) {
  session.abortTransaction();
  throw error;
} finally {
  session.endSession();
}

import redis
import json
import time
from typing import Dict, List, Optional

class RedisDataManager:
    def __init__(self, redis_url="redis://localhost:6379"):
        self.redis_client = redis.from_url(redis_url, decode_responses=True)
        
    # Session management with TTL
    async def create_session(self, user_id: str, session_data: Dict, ttl_seconds: int = 3600):
        """
        Create user session with automatic expiration
        """
        session_id = f"session:{user_id}:{int(time.time())}"
        
        # Use hash for structured session data
        session_key = f"user_session:{session_id}"
        await self.redis_client.hmset(session_key, {
            'user_id': user_id,
            'created_at': time.time(),
            'last_activity': time.time(),
            'data': json.dumps(session_data)
        })
        
        # Set expiration
        await self.redis_client.expire(session_key, ttl_seconds)
        
        # Add to user's active sessions (sorted set by timestamp)
        await self.redis_client.zadd(
            f"user_sessions:{user_id}", 
            {session_id: time.time()}
        )
        
        return session_id
    
    # Real-time analytics with sorted sets
    async def track_user_activity(self, user_id: str, activity_type: str, score: float = None):
        """
        Track user activity using sorted sets for real-time analytics
        """
        timestamp = time.time()
        score = score or timestamp
        
        # Global activity feed
        await self.redis_client.zadd("global_activity", {f"{user_id}:{activity_type}": timestamp})
        
        # User-specific activity
        await self.redis_client.zadd(f"user_activity:{user_id}", {activity_type: timestamp})
        
        # Activity type leaderboard
        await self.redis_client.zadd(f"leaderboard:{activity_type}", {user_id: score})
        
        # Maintain rolling window (keep last 1000 activities)
        await self.redis_client.zremrangebyrank("global_activity", 0, -1001)
    
    # Caching with smart invalidation
    async def cache_with_tags(self, key: str, value: Dict, ttl: int, tags: List[str]):
        """
        Cache data with tag-based invalidation
        """
        # Store the actual data
        cache_key = f"cache:{key}"
        await self.redis_client.setex(cache_key, ttl, json.dumps(value))
        
        # Associate with tags for batch invalidation
        for tag in tags:
            await self.redis_client.sadd(f"tag:{tag}", cache_key)
            
        # Track tags for this key
        await self.redis_client.sadd(f"cache_tags:{key}", *tags)
    
    async def invalidate_by_tag(self, tag: str):
        """
        Invalidate all cached items with specific tag
        """
        # Get all cache keys with this tag
        cache_keys = await self.redis_client.smembers(f"tag:{tag}")
        
        if cache_keys:
            # Delete cache entries
            await self.redis_client.delete(*cache_keys)
            
            # Clean up tag associations
            for cache_key in cache_keys:
                key_name = cache_key.replace("cache:", "")
                tags = await self.redis_client.smembers(f"cache_tags:{key_name}")
                
                for tag_name in tags:
                    await self.redis_client.srem(f"tag:{tag_name}", cache_key)
                    
                await self.redis_client.delete(f"cache_tags:{key_name}")
    
    # Distributed locking
    async def acquire_lock(self, lock_name: str, timeout: int = 10, retry_interval: float = 0.1):
        """
        Distributed lock implementation with timeout
        """
        lock_key = f"lock:{lock_name}"
        identifier = f"{time.time()}:{os.getpid()}"
        
        end_time = time.time() + timeout
        
        while time.time() < end_time:
            # Try to acquire lock
            if await self.redis_client.set(lock_key, identifier, nx=True, ex=timeout):
                return identifier
                
            await asyncio.sleep(retry_interval)
        
        return None
    
    async def release_lock(self, lock_name: str, identifier: str):
        """
        Release distributed lock safely
        """
        lock_key = f"lock:{lock_name}"
        
        # Lua script for atomic check-and-delete
        lua_script = """
        if redis.call("get", KEYS[1]) == ARGV[1] then
            return redis.call("del", KEYS[1])
        else
            return 0
        end
        """
        
        return await self.redis_client.eval(lua_script, 1, lock_key, identifier)

-- Time-series data modeling for IoT sensors

-- Keyspace with replication strategy
CREATE KEYSPACE iot_data WITH replication = {
  'class': 'NetworkTopologyStrategy',
  'datacenter1': 3,
  'datacenter2': 2
} AND durable_writes = true;

USE iot_data;

-- Partition by device and time bucket for efficient queries
CREATE TABLE sensor_readings (
    device_id UUID,
    time_bucket text,  -- Format: YYYY-MM-DD-HH (hourly buckets)
    reading_time timestamp,
    sensor_type text,
    value decimal,
    unit text,
    metadata map<text, text>,
    PRIMARY KEY ((device_id, time_bucket), reading_time, sensor_type)
) WITH CLUSTERING ORDER BY (reading_time DESC, sensor_type ASC)
  AND compaction = {'class': 'TimeWindowCompactionStrategy', 'compaction_window_unit': 'HOURS', 'compaction_window_size': 24}
  AND gc_grace_seconds = 604800  -- 7 days
  AND default_time_to_live = 2592000;  -- 30 days

-- Materialized view for latest readings per device
CREATE MATERIALIZED VIEW latest_readings AS
    SELECT device_id, sensor_type, reading_time, value, unit
    FROM sensor_readings
    WHERE device_id IS NOT NULL 
      AND time_bucket IS NOT NULL 
      AND reading_time IS NOT NULL 
      AND sensor_type IS NOT NULL
    PRIMARY KEY ((device_id), sensor_type, reading_time)
    WITH CLUSTERING ORDER BY (sensor_type ASC, reading_time DESC);

-- Device metadata table
CREATE TABLE devices (
    device_id UUID PRIMARY KEY,
    device_name text,
    location text,
    installation_date timestamp,
    device_type text,
    firmware_version text,
    configuration map<text, text>,
    status text,
    last_seen timestamp
);

-- User-defined functions for data processing
CREATE OR REPLACE FUNCTION calculate_average(readings list<decimal>)
    RETURNS NULL ON NULL INPUT
    RETURNS decimal
    LANGUAGE java
    AS 'return readings.stream().mapToDouble(Double::valueOf).average().orElse(0.0);';

-- Query examples with proper partition key usage
-- Get recent readings for a device (efficient - single partition)
SELECT * FROM sensor_readings 
WHERE device_id = ? AND time_bucket = '2024-01-15-10'
ORDER BY reading_time DESC
LIMIT 100;

-- Get hourly averages using aggregation
SELECT device_id, time_bucket, sensor_type, 
       AVG(value) as avg_value, 
       COUNT(*) as reading_count
FROM sensor_readings 
WHERE device_id = ? 
  AND time_bucket IN ('2024-01-15-08', '2024-01-15-09', '2024-01-15-10')
GROUP BY device_id, time_bucket, sensor_type;

import boto3
from boto3.dynamodb.conditions import Key, Attr
from decimal import Decimal
import uuid
from datetime import datetime, timedelta

class DynamoDBManager:
    def __init__(self, region_name='us-east-1'):
        self.dynamodb = boto3.resource('dynamodb', region_name=region_name)
        
    def create_tables(self):
        """
        Create optimized DynamoDB tables with proper indexes
        """
        # Main table with composite keys
        table = self.dynamodb.create_table(
            TableName='UserOrders',
            KeySchema=[
                {'AttributeName': 'PK', 'KeyType': 'HASH'},   # Partition key
                {'AttributeName': 'SK', 'KeyType': 'RANGE'}   # Sort key
            ],
            AttributeDefinitions=[
                {'AttributeName': 'PK', 'AttributeType': 'S'},
                {'AttributeName': 'SK', 'AttributeType': 'S'},
                {'AttributeName': 'GSI1PK', 'AttributeType': 'S'},
                {'AttributeName': 'GSI1SK', 'AttributeType': 'S'},
                {'AttributeName': 'LSI1SK', 'AttributeType': 'S'},
            ],
            # Global Secondary Index for alternative access patterns
            GlobalSecondaryIndexes=[
                {
                    'IndexName': 'GSI1',
                    'KeySchema': [
                        {'AttributeName': 'GSI1PK', 'KeyType': 'HASH'},
                        {'AttributeName': 'GSI1SK', 'KeyType': 'RANGE'}
                    ],
                    'Projection': {'ProjectionType': 'ALL'},
                    'BillingMode': 'PAY_PER_REQUEST'
                }
            ],
            # Local Secondary Index for same partition, different sort
            LocalSecondaryIndexes=[
                {
                    'IndexName': 'LSI1',
                    'KeySchema': [
                        {'AttributeName': 'PK', 'KeyType': 'HASH'},
                        {'AttributeName': 'LSI1SK', 'KeyType': 'RANGE'}
                    ],
                    'Projection': {'ProjectionType': 'ALL'}
                }
            ],
            BillingMode='PAY_PER_REQUEST'
        )
        
        return table
    
    def single_table_design_patterns(self):
        """
        Demonstrate single-table design with multiple entity types
        """
        table = self.dynamodb.Table('UserOrders')
        
        # User entity
        user_item = {
            'PK': 'USER#12345',
            'SK': 'USER#12345',
            'EntityType': 'User',
            'Email': 'user@example.com',
            'FirstName': 'John',
            'LastName': 'Doe',
            'CreatedAt': datetime.utcnow().isoformat(),
            'Status': 'Active'
        }
        
        # Order entity (belongs to user)
        order_item = {
            'PK': 'USER#12345',
            'SK': 'ORDER#67890',
            'EntityType': 'Order',
            'OrderId': '67890',
            'Status': 'Processing',
            'Total': Decimal('99.99'),
            'CreatedAt': datetime.utcnow().isoformat(),
            # GSI for querying orders by status
            'GSI1PK': 'ORDER_STATUS#Processing',
            'GSI1SK': datetime.utcnow().isoformat(),
            # LSI for querying user's orders by total amount
            'LSI1SK': 'TOTAL#' + str(Decimal('99.99')).zfill(10)
        }
        
        # Order item entity (belongs to order)
        order_item_entity = {
            'PK': 'ORDER#67890',
            'SK': 'ITEM#001',
            'EntityType': 'OrderItem',
            'ProductId': 'PROD#456',
            'Quantity': 2,
            'UnitPrice': Decimal('49.99'),
            'TotalPrice': Decimal('99.98')
        }
        
        # Batch write all entities
        with table.batch_writer() as batch:
            batch.put_item(Item=user_item)
            batch.put_item(Item=order_item)
            batch.put_item(Item=order_item_entity)
    
    def query_patterns(self):
        """
        Efficient query patterns for DynamoDB
        """
        table = self.dynamodb.Table('UserOrders')
        
        # 1. Get user and all their orders (single query)
        response = table.query(
            KeyConditionExpression=Key('PK').eq('USER#12345')
        )
        
        # 2. Get orders by status across all users (GSI query)
        response = table.query(
            IndexName='GSI1',
            KeyConditionExpression=Key('GSI1PK').eq('ORDER_STATUS#Processing')
        )
        
        # 3. Get user's orders sorted by total amount (LSI query)
        response = table.query(
            IndexName='LSI1',
            KeyConditionExpression=Key('PK').eq('USER#12345'),
            ScanIndexForward=False  # Descending order
        )
        
        # 4. Conditional updates to prevent race conditions
        table.update_item(
            Key={'PK': 'ORDER#67890', 'SK': 'ORDER#67890'},
            UpdateExpression='SET OrderStatus = :new_status, UpdatedAt = :timestamp',
            ConditionExpression=Attr('OrderStatus').eq('Processing'),
            ExpressionAttributeValues={
                ':new_status': 'Shipped',
                ':timestamp': datetime.utcnow().isoformat()
            }
        )
        
        return response
    
    def implement_caching_pattern(self):
        """
        Implement DynamoDB with DAX caching
        """
        # DAX client for microsecond latency
        import amazondax
        
        dax_client = amazondax.AmazonDaxClient.resource(
            endpoint_url='dax://my-dax-cluster.amazonaws.com:8111',
            region_name='us-east-1'
        )
        
        table = dax_client.Table('UserOrders')
        
        # Queries through DAX will be cached automatically
        response = table.get_item(
            Key={'PK': 'USER#12345', 'SK': 'USER#12345'}
        )
        
        return response

// Performance optimization techniques

// 1. Efficient indexing strategy
db.users.createIndex(
    { "status": 1, "lastLoginDate": -1, "totalSpent": -1 },
    { 
        name: "user_analytics_idx",
        background: true,
        partialFilterExpression: { "status": "active" }
    }
);

// 2. Aggregation pipeline optimization
db.orders.aggregate([
    // Move $match as early as possible
    { $match: { createdAt: { $gte: ISODate("2024-01-01") } } },
    
    // Use $project to reduce document size early
    { $project: { customerId: 1, total: 1, items: 1 } },
    
    // Optimize grouping operations
    { $group: { _id: "$customerId", totalSpent: { $sum: "$total" } } }
], { allowDiskUse: true });

// 3. Connection pooling optimization
const mongoClient = new MongoClient(uri, {
    maxPoolSize: 50,
    minPoolSize: 5,
    maxIdleTimeMS: 30000,
    serverSelectionTimeoutMS: 5000,
    socketTimeoutMS: 45000,
    bufferMaxEntries: 0,
    useNewUrlParser: true,
    useUnifiedTopology: true
});

# Redis optimization techniques

# 1. Pipeline operations to reduce network round trips
pipe = redis_client.pipeline()
for i in range(1000):
    pipe.set(f"key:{i}", f"value:{i}")
    pipe.expire(f"key:{i}", 3600)
pipe.execute()

# 2. Use appropriate data structures
# Instead of individual keys, use hashes for related data
# Bad: Multiple keys
redis_client.set("user:123:name", "John")
redis_client.set("user:123:email", "john@example.com")

# Good: Single hash
redis_client.hmset("user:123", {
    "name": "John",
    "email": "john@example.com"
})

# 3. Memory optimization with compression
import pickle
import zlib

def compress_and_store(key, data, ttl=3600):
    """Store data with compression for memory efficiency"""
    compressed_data = zlib.compress(pickle.dumps(data))
    redis_client.setex(key, ttl, compressed_data)

def retrieve_and_decompress(key):
    """Retrieve and decompress data"""
    compressed_data = redis_client.get(key)
    if compressed_data:
        return pickle.loads(zlib.decompress(compressed_data))
    return None

// MongoDB performance monitoring queries

// Current operations
db.currentOp({
    "active": true,
    "secs_running": {"$gt": 1},
    "ns": /^mydb\./
});

// Index usage statistics
db.users.aggregate([
    {"$indexStats": {}}
]);

// Database statistics
db.stats();

// Slow operations profiler
db.setProfilingLevel(2, { slowms: 100 });
db.system.profile.find().limit(5).sort({ ts: -1 });

# Redis performance monitoring
redis-cli info memory
redis-cli info stats
redis-cli info replication
redis-cli --latency-history -i 1
redis-cli --bigkeys
redis-cli monitor