Fetching AWS Glue Connection Details

There might be requirements where you have to fetch the details from the connection defined in AWS Glue Data Catalog and use them in your Glue job for connecting to the database (to store logs into database).

Below are the steps to achieve this:
1. Create a AWS Glue connection
• Refer Create AWS Glue Connection
2. Prepare a py.file (getConnection.py) to fetch the details
• This .py file uses a function called extract_jdbc_conf(connection_name, catalog_id = None)
• extract_jdbc_conf() returns a dictionary with keys user, password, vendor and url from connection object in the Data Catalog
• Arguments :
• connection_name – The name of the connection in the Data Catalog
• catalog_id – The catalog ID (account ID) of the Data Catalog being accessed. When None, the default account ID of the caller is used.
• Use the following code

class fetch_connection:
	@staticmethod
	def getConnectionDetails(glueContext,db,catalog_id):
		details = glueContext.extract_jdbc_conf(db, catalog_id = catalog_id)
		source = {}
		source['url'] = details['url']
		source['user'] = details['user']
		source['password'] = details['password']
		url = details['url']
		source['host'] = url[url.find("//")+2:-5]
		source['port'] = url[url.rfind(":")+1:]
		return source

3. Add this .py file in a s3 bucket



4. Provide the s3 Location of the .py file in the Security configuration, script libraries and job parameters in Python library path in Edit Job



5. Provide the following code in the job

	import getConnection as gc

CONNECTION = “<Glue Connection Name>”
conn = gc.fetch_connection.getConnectionDetails(glueContext,CONNECTION,None)

HOST_NAME = conn['host']
USERNAME = conn['user']
PASSWORD = conn['password']
PORT = conn['port']
DATABASE = “<Database name>”
URL = conn['url']+"/"+DATABASE
DRIVER = "org.postgresql.Driver"

Note- The above code is for Postgres

Use the below code for SQL Server
URL = conn['url']
URL = SOURCE_URL+";databaseName="+SOURCE_DB_NAME
USERNAME = conn['user']
PASSWORD = conn['password']
PORT="1433"
DRIVER = "com.microsoft.sqlserver.jdbc.SQLServerDriver"

6. Execute the job and now the job will fetch the connection details

In case if you have any queries please get us at support@helicaltech.com

Thank You
Sai Kavya Sathineni
BI Developer
Helical IT Solutions Pvt Ltd

Best Open Source Business Intelligence Software Helical Insight Here

A Business Intelligence Framework

Free
Download

Best Open Source Business Intelligence Software Helical Insight is Here

A Business Intelligence Framework

Free
Download