Updating a flag on On Premise table using aws glue job

Hello, Anjali!

Let's walk through a practical solution for updating a flag on an on-premises Oracle table using AWS Glue. This response includes specific details about handling .pem files and Python code snippets for better clarity.

---

Clarifying the Issue

You're working with an on-premises Oracle database, transferring data to S3 using MWAA and AWS Glue. Once the transfer is complete, you want to update specific records in the on-premises table. Your requirement excludes truncate/load scenarios, focusing on specific updates, and you're encountering issues with providing a .pem file for the database connection in a Python shell Glue job.

---

Key Terms

• AWS Glue: A serverless data integration service for preparing data for analytics and machine learning.
• On-Premises Oracle Table: A database hosted locally, requiring secure connections to external systems.
• MWAA (Managed Workflows for Apache Airflow): A managed orchestration service that simplifies workflow automation.
• .pem File: A privacy-enhanced mail file used for security certificates, typically required for encrypted communication.
• JDBC: Java Database Connectivity, a widely used API for connecting and executing queries on databases.

---

The Solution (Our Recipe)

Steps at a Glance:

1. Configure Oracle to accept connections via JDBC.
2. Use AWS Secrets Manager for secure credential storage.
3. Configure .pem file handling in the Glue job.
4. Write an SQL script to perform the update operation.
5. Implement and test the Python script for execution.

Step-by-Step Guide:

1. Configure Oracle to accept connections via JDBC.

   • Ensure the Oracle database is accessible over the network with a valid JDBC URL.
   • Install SSL certificates if needed and configure the listener on the database to accept encrypted connections.

2. Use AWS Secrets Manager for secure credential storage.

   • Store database credentials securely in AWS Secrets Manager. Include the user, password, and other connection details.
   • Grant AWS Glue permissions to access these secrets by updating the IAM role associated with the Glue job.

3. Configure .pem file handling in the Glue job.

   • Upload the .pem file to an S3 bucket and configure access permissions.
   • Modify the Glue job script to download the .pem file during execution:

     import boto3
     import os
     
     s3 = boto3.client('s3')
     bucket_name = 'your-bucket-name'
     pem_file_key = 'path/to/your-cert.pem'
     local_pem_file = '/tmp/cert.pem'
     
     # Download the .pem file
     s3.download_file(bucket_name, pem_file_key, local_pem_file)
     os.environ['SSL_CERT_FILE'] = local_pem_file

   • The SSL_CERT_FILE environment variable ensures the database driver can locate the .pem file for encrypted communication.

4. Write an SQL script to perform the update operation.

   • Embed the SQL command in the Glue job:

     UPDATE TABLE_NAME SET COL1='XYZ' WHERE COL2='ABC';

5. Implement and test the Python script for execution.

   • Use Python's jaydebeapi library to execute the SQL command. Here’s a sample script:

     import jaydebeapi
     
     # Connection parameters
     jdbc_url = "jdbc:oracle:thin:@dbhost.example.com:1521/orclpdb"
     driver_class = "oracle.jdbc.OracleDriver"
     driver_path = "/path/to/ojdbc8.jar"  # Ensure this jar file is available
     db_user = "your-username"
     db_password = "your-password"
     cert_path = "/tmp/cert.pem"
     
     # Set up the connection
     conn = jaydebeapi.connect(
         driver_class,
         jdbc_url,
         [db_user, db_password],
         driver_path,
         {"javax.net.ssl.trustStore": cert_path}
     )
     
     # Execute SQL update
     cursor = conn.cursor()
     sql_update = "UPDATE TABLE_NAME SET COL1='XYZ' WHERE COL2='ABC';"
     cursor.execute(sql_update)
     conn.commit()
     
     print("Update successful!")
     cursor.close()
     conn.close()

   • Ensure the JDBC driver file (ojdbc8.jar) is available in the Glue job’s dependencies.

---

Closing Thoughts

This improved solution addresses the .pem file handling challenge and includes Python code for JDBC connections and SQL execution. By leveraging Secrets Manager, S3 for .pem file storage, and robust JDBC practices, you can securely and efficiently update your on-premises Oracle table. Let us know how it goes, and feel free to ask for additional help!

---

Hope this solution works seamlessly for you, Anjali! Good luck with your Glue job adventures. 🚀😊

---

Cheers, Aaron 😊