Migrate DNS records in bulk to an Amazon Route 53 private hosted zone

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.

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.

 python3 --version

Install the pandas package.

 pip3 install pandas --user

Convert the Excel worksheet data to JSON.

Create a Python file that contains the following code to convert from Excel to JSON.

import pandas as pd
data=pd.read_excel('./Book1.xls')
data.to_json(path_or_buf='my.json',orient='records')

where Book1 is the name of the Excel worksheet and my.json is the name of the output JSON file.

Upload the JSON file to an S3 bucket.

Upload the my.json file to an S3 bucket. For more information, see Creating a bucket in the Amazon S3 documentation.

Create a private hosted zone.

Use the 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.

import boto3
import random
hostedZoneName ="xxx"
vpcRegion = "us-east-1"
vpcId="vpc-xxxx"
route53_client = boto3.client('route53')
response = route53_client.create_hosted_zone(
        Name= hostedZoneName,
        VPC={
            'VPCRegion: vpcRegion,
            'VPCId': vpcId
        },
        CallerReference=str(random.random()*100000),
        HostedZoneConfig={
            'Comment': "private hosted zone created by automation",
            'PrivateZone': True
        }
    )
 print(response)

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.

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. 

fileobj = s3_client.get_object(
        Bucket=bucket_name,
        Key='my.json'
        )
    filedata = fileobj['Body'].read()
    contents = filedata.decode('utf-8')
    json_content=json.loads(contents)
    print(json_content)

where json_content contains the Python dictionary.

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).

for item in json_content:
    fqn_name = unicodedata.normalize("NFKD",item["FqdnName"].replace("u'", "'").replace('\xa0', '').strip())
    rec_type = item["RecordType"].replace('\xa0', '').strip()
    res_rec = {
                 'Value': item["Value"].replace('\xa0', '').strip()
                }

Insert records.

Use the following code as part of the previous for loop.

change_response = route53_client.change_resource_record_sets(
            HostedZoneId="xxxxxxxx",
            ChangeBatch={
                'Comment': 'Created by automation',
                'Changes': [
                    {
                        'Action': 'UPSERT',
                        'ResourceRecordSet': {
                            'Name': fqn_name,
                            'Type': rec_type,
                            'TTL': item["TTL"],
                            'ResourceRecords': res_rec
                        }
                    }
                ]
            }
    )

Where xxxxxxx is the hosted zone ID from the first step of this epic.