How to connect from Glue ETL job to Oracle Autonomous Database?

The error "Connection reset by peer" typically indicates a network-related issue when establishing the connection to the Oracle Autonomous Database. Here are a few things you can try to address this problem:

Verify the connection details:
    Double-check the db_url and ensure that the host, port, and service name are correct. You may want to try connecting to the database using a tool like SQL Developer or SQLPlus to verify the connection details.
    Ensure that the user and password are correct and have the necessary permissions to access the docrow table.

Check the JDBC driver version and compatibility:
    Ensure that the JDBC driver version (ojdbc8-21.7.0.0.jar) is compatible with the Oracle Autonomous Database version you are using.
    You can try downloading the latest JDBC driver version from the [Oracle JDBC Drivers download page](https://www.oracle.com/database/technologies/appdev/jdbc-downloads.html) and updating the customJdbcDriverS3Path in your code.

Verify network connectivity:
    Ensure that your Glue job can access the Oracle Autonomous Database endpoint. You can try pinging the endpoint or using a tool like telnet to test the connectivity.
    Check if there are any firewall rules or network configurations that might be blocking the connection.

Enable TLS/SSL connection:
    By default, Oracle Autonomous Database requires a secure TLS/SSL connection. You can try enabling TLS/SSL in your connection by modifying the JDBC URL and adding the ?sslmode=require parameter:

    db_url="jdbc:oracle:thin:@//adb.eu-frankfurt-1.oraclecloud.com:1521/ga8a15e2c641e7b_bfrceddyo0fopful_low.adb.oraclecloud.com?sslmode=require"

    Additionally, you may need to provide the necessary SSL/TLS certificates to your Glue job. You can do this by uploading the certificates to an S3 bucket and specifying the path in the connection options.

Check IAM permissions:
    Ensure that the IAM role associated with your Glue job has the necessary permissions to access the Oracle Autonomous Database.

Increase connection timeout:
    If the connection takes a long time to establish, you can try increasing the connection timeout value in the connection options:

    connection_oracle19c_options = {
        "url": db_url,
        "dbtable": "docrow",
        "user": "atpc_user",
        "password": "*******",
        "customJdbcDriverS3Path": "s3://abernads-oracle-jdbc-drivers/ojdbc8-21.7.0.0.jar",
        "customJdbcDriverClassName": "oracle.jdbc.OracleDriver",
        "connectTimeout": "120" # Increase the timeout value (in seconds)
    }

Start with the above steps and see if they help resolve the "Connection reset by peer" error. If the issue persists, you may need to further investigate the network configuration and security settings to ensure a successful connection from your Glue ETL job.

Really nice that you know how to use ChatGPT, but the right answer is the following: Replace connection_type=“oracle” to connection_type=“custom.jdbc” and than it works like a charm.

sc = SparkContext.getOrCreate() glueContext = GlueContext(sc) spark = glueContext.spark_session job = Job(glueContext) db_url=“jdbc:oracle:thin:@(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1521)(host=adb.eu-frankfurt-1.oraclecloud.com))(connect_data=(service_name=ga8a15e2c641e7b_bfrceddyo0fopful_high.adb.oraclecloud.com))(security=(ssl_server_dn_match=yes)))” connection_oracle19c_options = { “url”: db_url, “dbtable”: “docrow”, “user”: “atpc_user”, “password”: “*******, “customJdbcDriverS3Path”: “s3://abernads-oracle-jdbc-drivers/ojdbc8-21.7.0.0.jar”, “customJdbcDriverClassName”: “oracle.jdbc.OracleDriver”, “className”:“oracle.jdbc.OracleDriver”, “query”:“select * from docrow”} df_oracle19 = glueContext.create_dynamic_frame.from_options(connection_type=“custom.jdbc”,connection_options=connection_oracle19c_options) df_oracle19.show(10)