Using an Athena View from Glue Catalog in AWS Glue ETL job

Hi lprevost. Thanks for sharing your question to re:Post!

AWS Glue ETL jobs cannot directly read standard Athena views using getCatalogSource().

Here's why:

1. Views don't have a classification: Views are virtual tables with no physical storage or data format. They don't have properties like classification=parquet or connection that Glue ETL expects.

2. Views don't have a storage location: Glue ETL's getCatalogSource() expects tables with an S3 location and SerDe information, which views don't have.

3. Different view types: The AWS documentation you referenced about "Data Catalog views" refers to PROTECTED MULTI DIALECT VIEWS (Lake Formation managed views), which are different from standard Athena views created with CREATE OR REPLACE VIEW.

Reference: Use Data Catalog views in Athena

---

Solutions

Solution 1: Materialize the View as a Table (Recommended)

Create a physical table from your view using CTAS (Create Table As Select):

CREATE TABLE nces_ccd.distinct_rurls_table
WITH (
  format='PARQUET',
  external_location='s3://your-bucket/path/to/distinct_rurls/'
) AS
SELECT rev_tld 
FROM "nces_ccd"."schools_and_districts" 
WHERE (rev_tld <> '') 
UNION 
SELECT rev_tld 
FROM "nces_ccd"."lea" 
WHERE (rev_tld <> '') 
ORDER BY rev_tld ASC

Then in your Glue ETL job:

distinct_rurls = glueContext.create_dynamic_frame.from_catalog(
    database="nces_ccd",
    table_name="distinct_rurls_table"
)

Pros:

• Works reliably with Glue ETL
• Better performance (no view resolution overhead)
• Can be refreshed on a schedule

Cons:

• Data is not real-time (needs periodic refresh)
• Requires additional storage

Reference: Create a table from query results (CTAS)

---

Solution 2: Replicate View Logic in Glue ETL

Perform the UNION operation directly in your Glue ETL job:

from awsglue.context import GlueContext
from pyspark.context import SparkContext

sc = SparkContext()
glueContext = GlueContext(sc)

# Read both tables
schools_and_districts = glueContext.create_dynamic_frame.from_catalog(
    database="nces_ccd",
    table_name="schools_and_districts"
)

lea = glueContext.create_dynamic_frame.from_catalog(
    database="nces_ccd",
    table_name="lea"
)

# Convert to Spark DataFrames
schools_df = schools_and_districts.toDF()
lea_df = lea.toDF()

# Apply filters and union
schools_filtered = schools_df.filter(schools_df.rev_tld != "").select("rev_tld")
lea_filtered = lea_df.filter(lea_df.rev_tld != "").select("rev_tld")

# Union and sort
distinct_rurls_df = schools_filtered.union(lea_filtered).distinct().orderBy("rev_tld")

# Convert back to DynamicFrame if needed
from awsglue.dynamicframe import DynamicFrame
distinct_rurls = DynamicFrame.fromDF(distinct_rurls_df, glueContext, "distinct_rurls")

Pros:

• Real-time data (no materialization needed)
• No additional storage required
• Full control over the transformation logic

Cons:

• More code to maintain
• Logic duplicated between Athena view and Glue job

---

Solution 3: Use Athena Query via boto3 (Workaround)

Query the view using Athena's API and write results to S3, then read in Glue:

import boto3
import time

athena_client = boto3.client('athena')

# Execute Athena query
response = athena_client.start_query_execution(
    QueryString='SELECT * FROM nces_ccd.distinct_rurls',
    QueryExecutionContext={'Database': 'nces_ccd'},
    ResultConfiguration={
        'OutputLocation': 's3://your-bucket/athena-results/'
    }
)

query_execution_id = response['QueryExecutionId']

# Wait for query to complete
while True:
    status = athena_client.get_query_execution(QueryExecutionId=query_execution_id)
    state = status['QueryExecution']['Status']['State']
    if state in ['SUCCEEDED', 'FAILED', 'CANCELLED']:
        break
    time.sleep(2)

if state == 'SUCCEEDED':
    # Read the results from S3
    result_location = status['QueryExecution']['ResultConfiguration']['OutputLocation']
    distinct_rurls = glueContext.create_dynamic_frame.from_options(
        connection_type="s3",
        connection_options={"paths": [result_location]},
        format="csv",
        format_options={"withHeader": True}
    )

Pros:

• Can use existing view definition
• Real-time data

Cons:

• More complex code
• Additional Athena query costs
• Slower execution (query + read)
• Requires managing Athena query lifecycle

Reference: AWS Athena boto3 documentation

---

Solution 4: Use Spark SQL with Glue Data Catalog (Worth Trying)

Enable Glue Data Catalog as Hive metastore and query the view using Spark SQL:

from awsglue.context import GlueContext
from pyspark.context import SparkContext
from pyspark.sql import SparkSession

# Create Spark session with Glue Catalog as metastore
spark = SparkSession.builder \
    .config("spark.sql.catalogImplementation", "hive") \
    .config("hive.metastore.client.factory.class", 
            "com.amazonaws.glue.catalog.metastore.AWSGlueDataCatalogHiveClientFactory") \
    .enableHiveSupport() \
    .getOrCreate()

glueContext = GlueContext(spark.sparkContext)

# Query the view using Spark SQL
distinct_rurls_df = spark.sql("SELECT * FROM nces_ccd.distinct_rurls")

# Convert to DynamicFrame if needed
from awsglue.dynamicframe import DynamicFrame
distinct_rurls = DynamicFrame.fromDF(distinct_rurls_df, glueContext, "distinct_rurls")

Important: This approach works if:

• The view definition uses standard SQL that Spark can understand
• Your view is simple (like your UNION query)
• The underlying tables are accessible via the Glue Catalog

It may fail if:

• The view uses Athena-specific functions not supported by Spark
• There are syntax differences between Athena (Trino/Presto) and Spark SQL

For your specific view (simple UNION with WHERE clauses), this should work.

Pros:

• Uses SQL syntax
• Leverages Glue Catalog
• Real-time data
• Simpler than replicating logic manually

Cons:

• Requires Hive support configuration
• May encounter SQL dialect incompatibilities with complex views

---

Why the Documentation is Confusing

The AWS documentation mentions views in different contexts:

1. Standard Athena Views (what you created): Virtual tables with CREATE OR REPLACE VIEW. These cannot be used directly in Glue ETL jobs.

2. Protected Multi Dialect Views (MDVs): Lake Formation managed views created with CREATE PROTECTED MULTI DIALECT VIEW. These are designed for cross-engine compatibility but still have limitations.

3. Glue Catalog Table Type: Views are stored in the Glue Catalog with TableType=VIRTUAL_VIEW, but Glue ETL's getCatalogSource() doesn't support reading them.

Reference: Creating tables in AWS Glue

---

Recommended Approach

For your use case, Solution 1 (Materialize as Table) or Solution 2 (Replicate Logic) are the best options:

• Use Solution 1 if your data doesn't change frequently and you can refresh the table periodically
• Use Solution 2 if you need real-time data and the view logic is simple

Both approaches are well-supported, reliable, and performant.

---

References

• Use Data Catalog views in Athena
• Creating tables in AWS Glue
• Create Table As Select (CTAS)
• AWS Glue PySpark Extensions