# Converting ETL processes to AWS Glue in AWS Schema Conversion Tool
<a name="CHAP-converting-aws-glue-api-process"></a>

In the following sections, you can find a description of a conversion that calls AWS Glue API operations in Python. For more information, see [Program AWS Glue ETL scripts in Python](https://docs.aws.amazon.com//glue/latest/dg/aws-glue-programming-python.html) in the* AWS Glue Developer Guide.*

**Topics**
+ [Step 1: Create a database](#CHAP-converting-aws-glue-step-api-create-db)
+ [Step 2: Create a connection](#CHAP-converting-aws-glue-step-api-connection)
+ [Step 3: Create an AWS Glue crawler](#CHAP-converting-aws-glue-step-api-crawler)

## Step 1: Create a database
<a name="CHAP-converting-aws-glue-step-api-create-db"></a>

The first step is to create a new database in an AWS Glue Data Catalog by using the [AWS SDK API](https://docs.aws.amazon.com/glue/latest/webapi/API_CreateDatabase.html). When you define a table in the Data Catalog, you add it to a database. A database is used to organize the tables in AWS Glue. 

The following example demonstrates the `create_database` method of the Python API for AWS Glue.

```
response = client.create_database(
    DatabaseInput={
        'Name': 'database_name’,
        'Description': 'description',
        'LocationUri': 'string',
        'Parameters': {         
            'parameter-name': 'parameter value'
        }
    }
)
```

If you are using Amazon Redshift, the database name is formed as follows.

```
{redshift_cluster_name}_{redshift_database_name}_{redshift_schema_name}
```

The full name of Amazon Redshift cluster for this example is as follows.

```
rsdbb03.apq1mpqso.us-west-2.redshift.amazonaws.com
```

The following shows an example of a well-formed database name. In this case `rsdbb03` is the name, which is the first part of the full name of the cluster endpoint. The database is named `dev` and the schema is `ora_glue`.

```
rsdbb03_dev_ora_glue
```

## Step 2: Create a connection
<a name="CHAP-converting-aws-glue-step-api-connection"></a>

Create a new connection in a Data Catalog by using the [AWS SDK API](https://docs.aws.amazon.com/glue/latest/webapi/API_CreateConnection.html).

The following example demonstrates using the [https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-catalog-connections.html](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-api-catalog-connections.html) method of the Python API for AWS Glue. 

 

```
response = client.create_connection(
    ConnectionInput={
        'Name': 'Redshift_abcde03.aabbcc112233.us-west-2.redshift.amazonaws.com_dev',
        'Description': 'Created from SCT',
        'ConnectionType': 'JDBC',
        'ConnectionProperties': {
            'JDBC_CONNECTION_URL': 'jdbc:redshift://aabbcc03.aabbcc112233.us-west-2.redshift.amazonaws.com:5439/dev',
            'USERNAME': 'user_name',
            'PASSWORD': 'password'
        },
        'PhysicalConnectionRequirements': {
            'AvailabilityZone': 'us-west-2c',
            'SubnetId': 'subnet-a1b23c45',
            'SecurityGroupIdList': [
                'sg-000a2b3c', 'sg-1a230b4c', 'sg-aba12c3d', 'sg-1abb2345'
            ]
        }
    }
)
```

The parameters used in `create_connection` are as follows:
+ `Name` (UTF-8 string) – required. For Amazon Redshift, the connection name is formed as follows: `Redshift_<Endpoint-name>_<redshift-database-name>`, for example:` Redshift_abcde03_dev`
+ `Description` (UTF-8 string) – Your description of the connection.
+ `ConnectionType` (UTF-8 string) – Required. The type of connection. Currently, only JDBC is supported; SFTP is not supported.
+ `ConnectionProperties` (dict) – Required. A list of key-value pairs used as parameters for this connection, including the JDBC connection URL, the user name, and the password.
+ `PhysicalConnectionRequirements` (dict) – Physical connection requirements, which include the following:
  + `SubnetId` (UTF-8 string) – The ID of the subnet used by the connection.
  + `SecurityGroupIdList` (list) – The security group ID list used by the connection.
  + `AvailabilityZone` (UTF-8 string) – Required. The Availability Zone that contains the endpoint. This parameter is deprecated.

## Step 3: Create an AWS Glue crawler
<a name="CHAP-converting-aws-glue-step-api-crawler"></a>

Next, you create an AWS Glue crawler to populate the AWS Glue catalog. For more information, see [Cataloging tables with a crawler](https://docs.aws.amazon.com/glue/latest/dg/add-crawler.html) in the *AWS Glue Developer Guide. *

The first step in adding a crawler is to create a new database in a Data Catalog by using the [AWS SDK API](https://docs.aws.amazon.com//glue/latest/webapi/API_CreateCrawler.html). Before you begin, make sure to first delete any previous version of it by using the `delete_crawler` operation.

When you create your crawler, a few considerations apply:
+ For the crawler name, use the format `<redshift_node_name>_<redshift_database_name>_<redshift_shema_name>`, for example: `abcde03_dev_ora_glue`
+ Use an IAM role that already exists. For more information on creating IAM roles, see [Creating IAM roles](https://docs.aws.amazon.com//IAM/latest/UserGuide/id_roles_create.html) in the *IAM User Guide.*
+ Use the name of the database that you created in the previous steps.
+ Use the `ConnectionName` parameter, which is required.
+ For the `path` parameter, use the path to the JDBC target, for example: `dev/ora_glue/%`

The following example deletes an existing crawler and then creates a new one by using the Python API for AWS Glue. 

```
response = client.delete_crawler(
    Name='crawler_name'
)

response = client.create_crawler(
    Name='crawler_name',
    Role= ‘IAM_role’,
    DatabaseName='database_name’,
    Description='string',
    Targets={
        'S3Targets': [
            {
                'Path': 'string',
                'Exclusions': [
                    'string',
                ]
            },
        ],
        'JdbcTargets': [
            {
                'ConnectionName': ‘ConnectionName’,
                'Path': ‘Include_path’,
                'Exclusions': [
                    'string',
                ]
            },
        ]
    },
    Schedule='string',
    Classifiers=[
        'string',
    ],
    TablePrefix='string',
    SchemaChangePolicy={
        'UpdateBehavior': 'LOG'|'UPDATE_IN_DATABASE',
        'DeleteBehavior': 'LOG'|'DELETE_FROM_DATABASE'|'DEPRECATE_IN_DATABASE'
    },
    Configuration='string'
)
```

Create and then run a crawler that connects to one or more data stores, determines the data structures, and writes tables into the Data Catalog. You can run your crawler on a schedule, as shown following.

```
response = client.start_crawler(
    Name='string'
)
```

This example uses Amazon Redshift as the target. Amazon Redshift data types map to AWS Glue data types in the following way after the crawler runs.


|  |  | 
| --- |--- |
| Amazon Redshift data type | AWS Glue data type | 
| smallint | smallint | 
| integer | int | 
| bigint | bigint | 
| decimal | decimal(18,0) | 
| decimal(p,s) | decimal(p,s) | 
| real | double | 
| double precision | double | 
| boolean | boolean | 
| char | string | 
| varchar | string | 
| varchar(n) | string | 
| date | date | 
| timestamp | timestamp | 
| timestamptz | timestamp |