Glue Custom Visual Script Running indefinitely

Question

I am trying to create a Custom Visual Transform but unfortunately facing some issues.

Here my motive is to Truncate a MySQL Table before loading the data into it and I want to do it with the help of Visual Tranforms not by changing the auto generated script.

My job is running continueously with the same log:

23/05/14 04:25:00 INFO MultipartUploadOutputStream: close closed:false s3://aws-glue-assets-849950158560-ap-south-1/sparkHistoryLogs/spark-application-1684037765713.inprogress

However removing all the code except this code is working:

from awsglue import DynamicFrame

def truncate_mysql_table(self, database_name, table_name, connection_name):
    return self.filter(lambda row: row['age'] == '21')

DynamicFrame.truncate_mysql_table = truncate_mysql_table

This the code I am using:

import pymysql
import boto3
import json

from awsglue import DynamicFrame

def truncate_mysql_table(self, database_name, table_name, connection_name):
    client = boto3.client('glue')
    response = client.get_connection(Name=connection_name, HidePassword=False)
    connection_props = response.get("Connection").get("ConnectionProperties")
    host_name = connection_props.get("JDBC_CONNECTION_URL").rsplit(":", 1)[0].split("//")[1]
    port = int(connection_props.get("JDBC_CONNECTION_URL").rsplit(":", 1)[1].split("/", 1)[0])
    secret_id = connection_props.get("SECRET_ID")

    client = boto3.client('secretsmanager')
    response = client.get_secret_value(SecretId=secret_id)

    secret_data = json.loads(response.get("SecretString"))
    username = secret_data.get("username")
    password = secret_data.get("password")

    con = pymysql.connect(host=host_name,
                          user=username,
                          passwd=password,
                          db=database_name,
                          port=port,
                          connect_timeout=60)

    with con.cursor() as cur:
        cur.execute(f"TRUNCATE TABLE {database_name.strip()}.{table_name.strip()}")
        con.commit()
        con.close()

    # print("Table Truncated")
    return self

DynamicFrame.truncate_mysql_table = truncate_mysql_table

My Glue Connection and MySQL RDS is in the same VPC also I am having VPC endpoints for s3 and secret manager. This shouldn't be a problem becuase after changing (or simplifying) the code it is giving the expected output.

Answer 1

I got this script working after this change:

In my case both Lambda Function and RDS Instance is present in the private subnet of a VPC and my VPC don't have any Glue service endpoint.

My Lambda Function is trying to make a request using boto3 library to Glue to get the connection information, but it wasn't able to communicate.

I added a Glue service endpoint to my VPC and voila!!! it works like a charm.

Finally I can Truncate a MySQL Table before loading data into it.