# Migrate DNS records in bulk to an Amazon Route 53 private hosted zone
<a name="migrate-dns-records-in-bulk-to-an-amazon-route-53-private-hosted-zone"></a>

*Ram Kandaswamy, Amazon Web Services*

## Summary
<a name="migrate-dns-records-in-bulk-to-an-amazon-route-53-private-hosted-zone-summary"></a>

Network engineers and cloud administrators need an efficient and simple way to add Domain Name System (DNS) records to private hosted zones in Amazon Route 53. Using a manual approach to copy entries from a Microsoft Excel worksheet to appropriate locations in the Route 53 console is tedious and error prone. This pattern describes an automated approach that reduces the time and effort required to add multiple records. It also provides a repeatable set of steps for multiple hosted zone creation.

This pattern uses Amazon Simple Storage Service (Amazon S3) to store records. To work with data efficiently, the pattern uses the JSON format because of its simplicity and its ability to support a Python dictionary (`dict` data type).

**Note**  
If you can generate a zone file from your system, consider using the [Route 53 import feature](https://docs.aws.amazon.com/Route53/latest/DeveloperGuide/resource-record-sets-creating-import.html) instead.

## Prerequisites and limitations
<a name="migrate-dns-records-in-bulk-to-an-amazon-route-53-private-hosted-zone-prereqs"></a>

**Prerequisites **
+ An Excel worksheet that contains private hosted zone records
+ Familiarity with different types of DNS records such as A record, Name Authority Pointer (NAPTR) record, and SRV record (see [Supported DNS record types](https://docs.aws.amazon.com/Route53/latest/DeveloperGuide/ResourceRecordTypes.html))
+ Familiarity with the Python language and its libraries

**Limitations**
+ The pattern doesn’t provide extensive coverage for all use case scenarios. For example, the [change\$1resource\$1record\$1sets](https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/route53.html#Route53.Client.change_resource_record_sets) call doesn’t use all the available properties of the API.
+ In the Excel worksheet, the value in each row is assumed to be unique. Multiple values for each fully qualified domain name (FQDN) are expected to appear in the same row. If that is not true, you should modify the code provided in this pattern to perform the necessary concatenation.
+ The pattern uses the AWS SDK for Python (Boto3) to call the Route 53 service directly. You can enhance the code to use an AWS CloudFormation wrapper for the `create_stack` and `update_stack` commands, and use the JSON values to populate template resources.

## Architecture
<a name="migrate-dns-records-in-bulk-to-an-amazon-route-53-private-hosted-zone-architecture"></a>

**Technology stack**
+ Route 53 private hosted zones for routing traffic
+ Amazon S3 for storing the output JSON file

![\[Workflow for migrating DNS records in bulk to a Route 53 private hosted zone.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/a81c29ea-f0c5-4d4a-ba87-93111a0f1ee9/images/2ada844b-4147-4f9f-8883-d22605aa42d8.png)


The workflow consists of these steps, as illustrated in the previous diagram and discussed in the *Epics* section:

1. Upload an Excel worksheet that has the record set information to an S3 bucket.

1. Create and run a Python script that converts the Excel data to JSON format.

1. Read the records from the S3 bucket and clean the data.

1. Create record sets in your private hosted zone.

## Tools
<a name="migrate-dns-records-in-bulk-to-an-amazon-route-53-private-hosted-zone-tools"></a>
+ [Route 53](https://docs.aws.amazon.com/Route53/latest/DeveloperGuide/Welcome.html) –  Amazon Route 53 is a highly available and scalable DNS web service that handles domain registration, DNS routing, and health checking.
+ [Amazon S3](https://docs.aws.amazon.com/AmazonS3/latest/userguide/Welcome.html) – Amazon Simple Storage Service (Amazon S3) is an object storage service. You can use Amazon S3 to store and retrieve any amount of data at any time, from anywhere on the web.

## Epics
<a name="migrate-dns-records-in-bulk-to-an-amazon-route-53-private-hosted-zone-epics"></a>

### Prepare data for automation
<a name="prepare-data-for-automation"></a>


| Task | Description | Skills required | 
| --- | --- | --- | 
| Create an Excel file for your records. | Use the records you exported from your current system to create an Excel worksheet that has the required columns for a record, such as fully qualified domain name (FQDN), record type, Time to Live (TTL), and value. For NAPTR and SRV records, the value is a combination of multiple properties, so use Excel's `concat` method to combine these properties.[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/migrate-dns-records-in-bulk-to-an-amazon-route-53-private-hosted-zone.html) | Data engineer, Excel skills | 
| Verify the working environment. | In your IDE, create a Python file to convert the Excel input worksheet to JSON format. (Instead of an IDE, you can also use an Amazon SageMaker notebook to work with Python code.)Verify that the Python version you’re using is version 3.7 or later.<pre> python3 --version</pre>Install the **pandas** package.<pre> pip3 install pandas --user</pre> | General AWS | 
| Convert the Excel worksheet data to JSON. | Create a Python file that contains the following code to convert from Excel to JSON.<pre>import pandas as pd<br />data=pd.read_excel('./Book1.xls')<br />data.to_json(path_or_buf='my.json',orient='records')</pre>where `Book1` is the name of the Excel worksheet and `my.json` is the name of the output JSON file. | Data engineer, Python skills | 
| Upload the JSON file to an S3 bucket. | Upload the `my.json` file to an S3 bucket. For more information, see [Creating a bucket](https://docs.aws.amazon.com/AmazonS3/latest/userguide/create-bucket-overview.html) in the Amazon S3 documentation. | App developer | 
| FqdnName | RecordType | Value | TTL | 
| something.example.org | A | 1.1.1.1 | 900 | 

### Insert records
<a name="insert-records"></a>


| Task | Description | Skills required | 
| --- | --- | --- | 
| Create a private hosted zone. | Use the [create\$1hosted\$1zone](https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/route53.html#Route53.Client.create_hosted_zone) API and the following Python sample code to create a private hosted zone. Replace the parameters `hostedZoneName`, `vpcRegion`, and `vpcId` with your own values.<pre>import boto3<br />import random<br />hostedZoneName ="xxx"<br />vpcRegion = "us-east-1"<br />vpcId="vpc-xxxx"<br />route53_client = boto3.client('route53')<br />response = route53_client.create_hosted_zone(<br />        Name= hostedZoneName,<br />        VPC={<br />            'VPCRegion: vpcRegion,<br />            'VPCId': vpcId<br />        },<br />        CallerReference=str(random.random()*100000),<br />        HostedZoneConfig={<br />            'Comment': "private hosted zone created by automation",<br />            'PrivateZone': True<br />        }<br />    )<br /> print(response)</pre>You can also use an infrastructure as code (IaC) tool such as AWS CloudFormation to replace these steps with a template that creates a stack with the appropriate resources and properties. | Cloud architect, Network administrator, Python skills | 
| Retrieve details as a dictionary from Amazon S3. | Use the following code to read from the S3 bucket and to get the JSON values as a Python dictionary. <pre>fileobj = s3_client.get_object(<br />        Bucket=bucket_name,<br />        Key='my.json'<br />        )<br />    filedata = fileobj['Body'].read()<br />    contents = filedata.decode('utf-8')<br />    json_content=json.loads(contents)<br />    print(json_content)</pre>where `json_content` contains the Python dictionary. | App developer, Python skills | 
| Clean data values for spaces and Unicode characters. | As a safety measure to ensure the correctness of data, use the following code to perform a strip operation on the values in `json_content`. This code removes the space characters at the front and end of each string. It also uses the `replace` method to remove hard (non-breaking) spaces (the `\xa0` characters).<pre>for item in json_content:<br />    fqn_name = unicodedata.normalize("NFKD",item["FqdnName"].replace("u'", "'").replace('\xa0', '').strip())<br />    rec_type = item["RecordType"].replace('\xa0', '').strip()<br />    res_rec = {<br />                 'Value': item["Value"].replace('\xa0', '').strip()<br />                }</pre> | App developer, Python skills | 
| Insert records. | Use the following code as part of the previous `for` loop.<pre>change_response = route53_client.change_resource_record_sets(<br />            HostedZoneId="xxxxxxxx",<br />            ChangeBatch={<br />                'Comment': 'Created by automation',<br />                'Changes': [<br />                    {<br />                        'Action': 'UPSERT',<br />                        'ResourceRecordSet': {<br />                            'Name': fqn_name,<br />                            'Type': rec_type,<br />                            'TTL': item["TTL"],<br />                            'ResourceRecords': res_rec<br />                        }<br />                    }<br />                ]<br />            }<br />    )</pre>Where `xxxxxxx` is the hosted zone ID from the first step of this epic. | App developer, Python skills | 

## Related resources
<a name="migrate-dns-records-in-bulk-to-an-amazon-route-53-private-hosted-zone-resources"></a>

**References**
+ [Creating records by importing a zone file](https://docs.aws.amazon.com/Route53/latest/DeveloperGuide/resource-record-sets-creating-import.html) (Amazon Route 53 documentation)
+ [create\$1hosted\$1zone method](https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/route53.html#Route53.Client.create_hosted_zone) (Boto3 documentation)
+ [change\$1resource\$1record\$1sets method](https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/route53.html#Route53.Client.change_resource_record_sets) (Boto3 documentation)

**Tutorials and videos **
+ [The Python Tutorial](https://docs.python.org/3/tutorial/) (Python documentation)
+ [DNS design using Amazon Route 53](https://www.youtube.com/watch?v=2y_RBjDkRgY) (YouTube video, *AWS Online Tech Talks*)