Correct way to use Crawler?

Hi johnkimm. I see you resolved your issue as you were looking at the wrong table.

Since this question has had a lot of views, I'm going to share some useful information for when others encounter similar issues with Glue Crawlers and CSV files.

Issue

After running an AWS Glue Crawler on a folder containing multiple CSV files with the same columns, the resulting table in Athena shows incorrect columns (loglevel, datetime, message) instead of the actual CSV column names, and all values appear empty.

Resolution: The user was querying the wrong table. However, this issue highlights something to pay attention to when using Glue Crawlers with CSV files.

---

Common Causes and Solutions

Cause 1: Crawler Detected Files as Log Files Instead of CSV

Problem: If your CSV files have names or content that resemble log files, the Glue Crawler may incorrectly classify them as logs, creating columns like loglevel, datetime, message.

Solution: Ensure proper CSV classification

1. Use clear CSV file naming:

   ✓ Good: data_2024-01-01.csv, batch_results.csv
   ✗ Bad: application.log.csv, cloudwatch.log
   

2. Ensure CSV files have headers: The first line of each CSV should contain column names:

   job_id,status,duration,timestamp
   12345,SUCCESS,120,2024-01-01T10:00:00Z

3. Configure crawler to use CSV classifier:

   • In the Crawler configuration, under "Add a classifier"
   • Create a custom CSV classifier if needed
   • Specify delimiter, quote character, and header settings

References:

• Adding classifiers to a crawler
• Built-in classifiers in AWS Glue

---

Cause 2: Multiple Table Schemas Created

Problem: The crawler may create multiple tables if it detects different schemas or file formats in subdirectories.

Solution: Check all tables created by the crawler

-- List all tables in your database
SHOW TABLES IN default;

-- Check table properties
SHOW CREATE TABLE default.logs_aws_athena_glue;

Common table naming patterns:

• logs_aws_athena_glue - Main table
• logs_aws_athena_glue_csv - CSV-specific table
• logs_aws_athena_glue_partition_0 - Partitioned table

The user's resolution: They were querying the wrong table name.

Reference: How crawlers work

---

Cause 3: CSV Files Don't Have Headers

Problem: If CSV files lack headers, the crawler cannot infer column names and may create generic columns.

Solution: Add headers to CSV files

When writing CSV files from your Lambda function:

import csv
import boto3

s3 = boto3.client('s3')

# Write CSV with headers
data = [
    ['job_id', 'status', 'duration', 'timestamp'],  # Header row
    ['12345', 'SUCCESS', '120', '2024-01-01T10:00:00Z'],
    ['12346', 'FAILED', '45', '2024-01-01T11:00:00Z']
]

csv_content = '\n'.join([','.join(row) for row in data])

s3.put_object(
    Bucket='your-bucket',
    Key='logs/data_2024-01-01.csv',
    Body=csv_content
)

Or use pandas:

import pandas as pd
import boto3

df = pd.DataFrame({
    'job_id': [12345, 12346],
    'status': ['SUCCESS', 'FAILED'],
    'duration': [120, 45],
    'timestamp': ['2024-01-01T10:00:00Z', '2024-01-01T11:00:00Z']
})

# Write to S3 with header
csv_buffer = df.to_csv(index=False)
s3 = boto3.client('s3')
s3.put_object(
    Bucket='your-bucket',
    Key='logs/data_2024-01-01.csv',
    Body=csv_buffer
)

Reference: Working with CSV files in Athena

---

Cause 4: Inconsistent CSV Format Across Files

Problem: If CSV files have different delimiters, quote characters, or structures, the crawler may create an incorrect schema.

Solution: Ensure consistency

All CSV files should have:

• Same delimiter (comma, tab, pipe, etc.)
• Same quote character
• Same number of columns
• Same column order
• Same header names (if using headers)

Check your files:

# Download and inspect a few files
aws s3 cp s3://your-bucket/logs/file1.csv - | head -5
aws s3 cp s3://your-bucket/logs/file2.csv - | head -5

Reference: Schema change policy in crawlers

---

Cause 5: Crawler Configuration Issues

Problem: Crawler settings may not be optimized for your CSV structure.

Solution: Configure crawler properly

1. Create a custom CSV classifier:

   Go to AWS Glue Console → Classifiers → Add classifier

   Classifier name: my-csv-classifier
   Classifier type: CSV
   Delimiter: , (comma)
   Quote symbol: " (double quote)
   Contains header: Column names in first row
   Allow single column: No
   

2. Assign classifier to crawler:

   • Edit your crawler
   • Under "Add a classifier", select your custom classifier
   • Run the crawler again

Reference: Writing custom classifiers

---

Cause 6: Schema Evolution Issues

Problem: If you manually edited the table schema in Glue, the crawler may not update it correctly on subsequent runs.

Solution: Configure crawler schema update behavior

In Crawler configuration:

• Configuration options → Schema updates in the data catalog
• Choose: "Update the table definition in the Data Catalog"
• Object deletion in the data catalog: Choose appropriate option

Or delete the table and re-run the crawler:

DROP TABLE default.logs_aws_athena_glue;

Then run the crawler again to recreate the table with the correct schema.

Reference: How to prevent or allow schema changes

---

Best Practices for CSV Files with Glue Crawlers

1. Always include headers in the first row of CSV files
2. Use consistent naming for CSV files (avoid .log extensions)
3. Maintain consistent structure across all CSV files
4. Use custom classifiers for non-standard CSV formats
5. Partition your data by date for better query performance:

   s3://bucket/logs/year=2024/month=01/day=01/data.csv
   s3://bucket/logs/year=2024/month=01/day=02/data.csv
   

6. Test with a small sample before processing large datasets
7. Check crawler logs in CloudWatch for classification details

References:

• Best practices for crawlers
• Partitioning data in Athena

---

Debugging Steps

1. Check what tables were created:

   SHOW TABLES IN default;

2. Inspect table schema:

   DESCRIBE default.logs_aws_athena_glue;

3. Check table properties in Glue Console:

   • Go to AWS Glue → Tables
   • Select your table
   • View "Table details" and "Schema"
   • Check "Classification" field (should be "csv")

4. Review crawler logs:

   • Go to AWS Glue → Crawlers
   • Select your crawler
   • View "Logs" to see what was detected

5. Sample your S3 files:

   aws s3 cp s3://your-bucket/logs/sample.csv - | head -10

6. Query with explicit column selection:

   -- Instead of SELECT *
   SELECT job_id, status, duration FROM default.logs_aws_athena_glue LIMIT 10;

Reference: Viewing crawler history

---

Example: Complete Setup

1. Lambda function writing CSV with headers:

import boto3
import csv
from io import StringIO
from datetime import datetime

def lambda_handler(event, context):
    s3 = boto3.client('s3')
    
    # Your data collection logic here
    data = [
        {'job_id': '12345', 'status': 'SUCCESS', 'duration': 120},
        {'job_id': '12346', 'status': 'FAILED', 'duration': 45}
    ]
    
    # Write CSV with headers
    csv_buffer = StringIO()
    if data:
        writer = csv.DictWriter(csv_buffer, fieldnames=data[0].keys())
        writer.writeheader()
        writer.writerows(data)
    
    # Upload to S3 with date partition
    date_str = datetime.now().strftime('%Y-%m-%d')
    s3.put_object(
        Bucket='your-bucket',
        Key=f'logs/date={date_str}/data.csv',
        Body=csv_buffer.getvalue()
    )

2. Crawler configuration:

• Data source: s3://your-bucket/logs/
• Crawl all subfolders: Yes
• Custom classifier: CSV with headers
• Run frequency: Daily after Lambda execution

3. Query in Athena:

SELECT * FROM default.logs LIMIT 10;

References:

• Tutorial: Using an AWS Glue crawler
• Querying CSV data in Athena

---

References

• Adding classifiers to a crawler
• Writing custom classifiers
• Populating the Data Catalog using crawlers
• Working with CSV files in Athena