How to create a custom connector by using the CData JDBC driver for Salesforce in AWS Glue

Using the CData JDBC Driver for Salesforce in AWS Glue, you can easily create ETL jobs for Salesforce data, whether writing the data to an S3 bucket or loading it into any other AWS data store.  

In this article, we walk through uploading the CData JDBC Driver for Salesforce into an Amazon S3 bucket, the necessary IAM Role permissions, storing the connection properties in AWS Secrets Manager, creating a custom connector and creating a connection for that connector. 

Step 1: Upload the CData JDBC Driver for Salesforce to an Amazon S3 Bucket. 

To work with the CData JDBC Driver for Salesforce in AWS Glue, follow these steps to upload it (and any relevant license files) to an Amazon S3 bucket: 

1. Open the Amazon S3 Console. 

2. Select an existing bucket (or create a new one). 

3. Click Upload 

4. Select the JAR file (cdata.jdbc.salesforce .jar) found in the lib directory in the installation location for the driver. 

Step 2: IAM Role 

You should create a new role or update permissions to an existing role. The permissions that should be granted are: 

1. AWSGlueServiceRole (For accessing Glue Studio and Glue Jobs) 

2. AmazonEC2ContainerRegistryReadOnly (For accessing the CData AWS Glue Connector for SAP Concur) 

3. AmazonS3FullAccess (For reading from and writing to Amazon S3) 

4. AWS Secrets Manager 

Step 3: Store Salesforce Connection Properties Credentials in AWS Secrets Manager 

To safely store and use your connection properties, you can save them in AWS Secrets Manager. 

1. Navigate to Secrets Manager. 

2. Click Store a new secret. 

3. On the Store a new secret page, choose Other type of secret. This option means you must supply the structure and details of your secret. 

4. You can read more about the required properties to connect to Salesforce in our documentation. Once you know which properties you wish to store, create a key-value pair for each property. For example in this case we are using basic authentication :  

1. User 

2. Password 

3. SecurityToken 

4. RTK 

5. Click Next and set an unique name for you SecretsManager (e.g. SalesforceSecret). 

6. Click Next then Store and the new secrets manager should be created. 

Step 4: Create the custom connector. 

Now, let's create the custom connector that uses the uploaded driver: 

1. Navigate to AWS Glue Studio. 

2. Click Connections. 

3. Click Create custom connector. 

4. Make the following configurations: 

1. Connector S3 URL: fill in or browse to the S3 bucket where you uploaded the JAR file. Be sure to include the name of the JAR file itself in the path, i.e.: s3://testbucket/testfolder/cdata.jdbc.salesforce.jar 

2. Name: Fill in an unique name for the job, for example: SalesforceCustomConnector. 

3. Connector type: Select JDBC. 

4. Class name: Provide the driver class: cdata.jdbc.salesforce.SalesforceDriver. 

5. JDBC URL base: Enter the JDBC base URL: “jdbc:salesforce:”.  

6. URL parameter delimiter: Enter a character used to separate the parameters in your connection string: “;”. 

Step 5: Create the connection. 

To set up the connection for use in your AWS Glue Jobs, follow these steps: 

1. Navigate to AWS Glue Studio. 

2. Click Connections. 

3. Click on the custom connector that you created earlier. 

4. Make the following configurations: 

1. Name: Enter a unique name for your connection. 

2. Connection credential type: default. 

3. AWS Secret: The name of the Secret Manager you created earlier (e.g. SalesforceSecret). 

4. Additional URL parameter: Add these new parameters: 

1. AuthScheme: Basic 

2. User: ${User} 

3. Password: ${Password} 

4. SecurityToken: ${SecurityToken} 

5. RTK: ${RTK} 

Setting these parameters as variables make them retrieve the values from the Secrets manager. 

5. Click Create connection. 

Step 6: Create and run the Glue Job. 

Create a job by selecting the connector and the connection that you created. You can easily create use the custom connector in your ETL jobs to load Salesforce data into an S3 bucket or any other destination. You can also use it to add, update, or delete Salesforce data in your Glue Jobs.