Amazon Athena: Cost and Optimization Guide

Learn about Amazon Athena and how to optimize its costs.

1. Understanding Athena Costs

Billing

• Per byte scanned: $5 per TB
• Per query: Free
• Cost example:

# Example cost calculation
def calculate_athena_cost():
    # Cost per TB scanned
    cost_per_tb = 5  # USD
    
    # Example query
    query_size = {
        'taille_donnees': 100,  # GB
        'octets_scannes': 50,   # GB
        'coût': (50 / 1024) * cost_per_tb  # Conversion to TB
    }
    
    return query_size

Cost Factors

# Example cost factor analysis
def analyze_cost_factors():
    factors = {
        'taille_donnees': {
            'impact': 'Élevé',
            'optimisation': 'Partitionnement'
        },
        'format_donnees': {
            'impact': 'Élevé',
            'optimisation': 'Parquet/ORC'
        },
        'requetes': {
            'impact': 'Faible',
            'optimisation': 'Cache'
        }
    }
    
    return factors

2. Data Optimization

Partitioning

# Example of partitioning
def partition_data():
    athena = boto3.client('athena')
    
    # Creating a partitioned table
    query = """
    CREATE TABLE ma_table_partitionnee
    WITH (
        format = 'PARQUET',
        partitioned_by = ARRAY['annee', 'mois']
    )
    AS
    SELECT 
        *,
        year(date_col) as annee,
        month(date_col) as mois
    FROM ma_table;
    """
    
    response = athena.start_query_execution(
        QueryString=query,
        QueryExecutionContext={
            'Database': 'ma_base'
        },
        ResultConfiguration={
            'OutputLocation': 's3://mon-bucket/athena-results/'
        }
    )
    
    return response['QueryExecutionId']

Compression

# Example of compression
def compress_data():
    athena = boto3.client('athena')
    
    # Creating a compressed table
    query = """
    CREATE TABLE ma_table_compressee
    WITH (
        format = 'PARQUET',
        compression = 'SNAPPY'
    )
    AS
    SELECT * FROM ma_table;
    """
    
    response = athena.start_query_execution(
        QueryString=query,
        QueryExecutionContext={
            'Database': 'ma_base'
        },
        ResultConfiguration={
            'OutputLocation': 's3://mon-bucket/athena-results/'
        }
    )
    
    return response['QueryExecutionId']

3. Query Optimization

Partition Predicates

# Example of using partition predicates
def use_partition_predicates():
    athena = boto3.client('athena')
    
    # Optimized query with partition predicates
    query = """
    SELECT *
    FROM ma_table_partitionnee
    WHERE annee = 2024
    AND mois = 3;
    """
    
    response = athena.start_query_execution(
        QueryString=query,
        QueryExecutionContext={
            'Database': 'ma_base'
        },
        ResultConfiguration={
            'OutputLocation': 's3://mon-bucket/athena-results/'
        }
    )
    
    return response['QueryExecutionId']

Limiting Columns

# Example of column selection
def limit_columns():
    athena = boto3.client('athena')
    
    # Optimized query with column selection
    query = """
    SELECT 
        date_col,
        categorie,
        montant
    FROM ma_table
    WHERE date_col >= '2024-01-01';
    """
    
    response = athena.start_query_execution(
        QueryString=query,
        QueryExecutionContext={
            'Database': 'ma_base'
        },
        ResultConfiguration={
            'OutputLocation': 's3://mon-bucket/athena-results/'
        }
    )
    
    return response['QueryExecutionId']

4. Data Formats

Parquet vs CSV

# Example of format comparison
def compare_formats():
    formats = {
        'parquet': {
            'compression': 'Excellente',
            'performance': 'Élevée',
            'taille': 'Petite',
            'coût': 'Faible'
        },
        'csv': {
            'compression': 'Faible',
            'performance': 'Basse',
            'taille': 'Grande',
            'coût': 'Élevé'
        }
    }
    
    return formats

Format Conversion

# Example of format conversion
def convert_format():
    athena = boto3.client('athena')
    
    # Converting CSV to Parquet
    query = """
    CREATE TABLE ma_table_parquet
    WITH (
        format = 'PARQUET',
        compression = 'SNAPPY'
    )
    AS
    SELECT * FROM ma_table_csv;
    """
    
    response = athena.start_query_execution(
        QueryString=query,
        QueryExecutionContext={
            'Database': 'ma_base'
        },
        ResultConfiguration={
            'OutputLocation': 's3://mon-bucket/athena-results/'
        }
    )
    
    return response['QueryExecutionId']

5. Cost Monitoring

Cost Metrics

# Example of cost monitoring
def monitor_costs():
    cloudwatch = boto3.client('cloudwatch')
    
    # Cost metrics
    metrics = cloudwatch.get_metric_statistics(
        Namespace='AWS/Athena',
        MetricName='BytesScanned',
        Dimensions=[
            {
                'Name': 'QueryType',
                'Value': 'DML'
            }
        ],
        StartTime=datetime.now() - timedelta(days=7),
        EndTime=datetime.now(),
        Period=3600,
        Statistics=['Sum']
    )
    
    return metrics['Datapoints']

Cost Alerts

# Example of alert configuration
def setup_cost_alert():
    cloudwatch = boto3.client('cloudwatch')
    
    # Creating an alarm
    cloudwatch.put_metric_alarm(
        AlarmName='AthenaCostAlert',
        MetricName='BytesScanned',
        Namespace='AWS/Athena',
        Statistic='Sum',
        Period=3600,
        EvaluationPeriods=1,
        Threshold=1000000000000,  # 1 TB
        ComparisonOperator='GreaterThanThreshold',
        AlarmActions=['arn:aws:sns:region:account:mon-topic']
    )

6. Best Practices

Cost Preview

# Example of cost preview
def preview_costs():
    athena = boto3.client('athena')
    
    # Running an EXPLAIN query
    query = """
    EXPLAIN
    SELECT *
    FROM ma_table
    WHERE date_col >= '2024-01-01';
    """
    
    response = athena.start_query_execution(
        QueryString=query,
        QueryExecutionContext={
            'Database': 'ma_base'
        },
        ResultConfiguration={
            'OutputLocation': 's3://mon-bucket/athena-results/'
        }
    )
    
    return response['QueryExecutionId']

Result Caching

# Example of using the cache
def use_result_cache():
    athena = boto3.client('athena')
    
    # Cache configuration
    athena.update_work_group(
        WorkGroup='primary',
        ConfigurationUpdates={
            'ResultConfigurationUpdates': {
                'OutputLocation': 's3://mon-bucket/athena-results/',
                'EncryptionConfiguration': {
                    'EncryptionOption': 'SSE_S3'
                }
            },
            'RequesterPaysEnabled': False,
            'PublishCloudWatchMetricsEnabled': True,
            'BytesScannedCutoffPerQuery': 10000000000  # 10 GB
        }
    )

Conclusion

Key takeaways:

• Billing is per byte scanned
• Optimize through partitioning
• Use efficient formats
• Monitor costs
• Cache query results

Recommendations:

• Partition your data
• Use Parquet/ORC
• Limit the columns scanned
• Set up alerts
• Monitor costs regularly