Linking AWS Glue Crawlers to JDBC sources for schema management

Question

Pulumi · Accepted Answer

AWS Glue is a fully managed extract, transform, and load (ETL) service that makes it easy for customers to prepare and load their data for analytics. Within AWS Glue, you can create Crawlers that automatically discover and classify data stored in your AWS environment. The summarized steps to use an AWS Glue Crawler with JDBC data stores for schema management are:

1. **Set up a JDBC connection**: Define a connection in AWS Glue to your JDBC data store by providing the necessary details such as connection string, database name, username, and password.
2. **Create a crawler**: Define an AWS Glue Crawler specifying the JDBC connection and the include path for the tables you want to crawl.
3. **Run the crawler**: Start the crawling process, which connects to the data store via JDBC, scans the specified tables, infers schemas, and creates metadata tables in your AWS Glue Data Catalog.
4. **Use the schema**: Once the metadata is in the AWS Glue Data Catalog, it can be used to define and execute ETL jobs in AWS Glue or queried directly with services such as Amazon Athena.

Below is a Pulumi program written in TypeScript that demonstrates these steps. You will be defining the necessary resources to set up the connection to your JDBC data source and create a crawler that uses this connection. Make sure that you have the necessary details for connecting to your JDBC data source before using this code.

```typescript
import * as pulumi from "@pulumi/pulumi";
import * as aws from "@pulumi/aws";

// Replace these variable values with your JDBC data source details
const jdbcConnectionName = "my-jdbc-connection";
const jdbcConnectionString = "jdbc:mysql://[db-instance-identifier].[region].rds.amazonaws.com:3306/database-name";
const username = "your_db_username";
const password = "your_db_password";
const jdbcIncludePath = "database-name/table-name"; // Replace with your database and table name

// Create a Glue Connection for a JDBC data source
const jdbcConnection = new aws.glue.Connection(jdbcConnectionName, {
    connectionType: "JDBC",
    connectionProperties: {
        USERNAME: username,
        PASSWORD: password,
        JDBC_CONNECTION_URL: jdbcConnectionString,
        // Additional properties like CUSTOM_JDBC_CERT, CUSTOM_JDBC_CERT_STRING, etc.
    },
});

// Create a Glue Crawler using the JDBC connection
const crawler = new aws.glue.Crawler("my-crawler", {
    jdbcTargets: [{
        connectionName: jdbcConnection.name,
        path: jdbcIncludePath,
    }],
    role: "arn:aws:iam::123456789012:role/service-role/AWSGlueServiceRole-my-crawler-role", // Use your AWS Glue Service Role ARN
    databaseName: "my_database", // The name of the database in which to store the crawler's output in the Data Catalog.
    description: "A crawler for JDBC data source",
});

// Export the names of the resources
export const jdbcConnectionArn = jdbcConnection.arn;
export const crawlerName = crawler.name;
```

In this script, be sure to replace the placeholders like `your_db_username`, `your_db_password`, `jdbc:mysql://[db-instance-identifier].[region].rds.amazonaws.com:3306/database-name`, and the `role` ARN with your actual JDBC credentials and the ARN of an IAM Role with the necessary permissions.

**Explanation**:

- **AWS Glue Connection (`aws.glue.Connection`)**: This resource defines the connection to your JDBC data store. It includes all the necessary properties like the connection string, username, and password. These sensitive details should be stored securely, for example using AWS Secrets Manager or Pulumi secret management.

- **AWS Glue Crawler (`aws.glue.Crawler`)**: This is the crawler that will scan your JDBC data source. It uses the connection defined above and specifies what data to crawl through the `jdbcTargets` property. The crawler outputs metadata tables to your AWS Glue Data Catalog which you can use in your ETL jobs or data queries.

- **Exports**: At the end of the script, the ARN of the JDBC connection and the name of the crawler are exported. These can be helpful for reference and could be used in other parts of a Pulumi program to link to these resources.