Amazon Aurora (MySQL)

Objective

Amazon Aurora is a MySQL or PostgreSQL-compatible relational database (RDS) built for the cloud.

This guide will help you quickly connect to Amazon Aurora and create various methods to pull the data. This page will demonstrate how easy it is to integrate an external database to ixHello.

You can get a brief introduction on Amazon Aurora here.

Pre-requisites

  • An active AWS Account

You should have an active AWS Account. If you do not have it, you can create an AWS account at https://portal.aws.amazon.com/billing/signup#/start

  • At least one Amazon Aurora Instance

If you do not have any Aurora DB instance, you can create one quickly.

  • Login to AWS Management Console at https://console.aws.amazon.com/

  • In the Search bar, enter Aurora.

  • Select Aurora and RDS

  • Scroll down and select Create Database

On clicking Create database, you will be redirected to the Engine options screen. Select Standard Create and Amazon Aurora (MySQL Compatible).

Fill in the Credentials Settings.

The Aurora Instance should support remote connection, which can be done by making it publicly accessible.

Once the instance is saved, The ixHello IP address should be whitelisted so that a connection can be established from ixHello to the Amazon Aurora RDS instance.

Depending upon your requirements, you may have to whitelist the external platform to be able to read/write to that instance. For ixHello, we are setting up for both Reader and Writer instance.

Click on the newly created DB instance and follow these steps:

Let’s first whitelist ixHello for Writer Instance. Click on the Writer Instance and click on the Connectivity and Security tab. Now, follow following steps to modify the inbound rules.

The screenshot below shows the “Connectivity and Security” settings explaining this:

You will be presented with the list of security groups.

Edit the inbound rules for the security group.

When you click on security group (as shown in above screenshot), you will be taken to the security group screen and you should see inbound rules as shown in below screenshot.

To whitelist ixHello IPs, click on Add Rule and fill up following details.

Custom IP for ixHello: 52.73.180.211

  • An ixHello Account

To create an ixHello account, follow the steps here

Steps

To proceed with these steps, you should have all the pre-requisites satisfied. If you are missing something, please go back and make sure you satisfy all of them.

Once done, you are ready to get started using ixHello with Amazon Aurora.

Step 1 Connect to Amazon Aurora

Login at https://bots.ixhello.com

To configure the Aurora database, browse to: Integration > Connect System and press Connections Gallery.

Scroll down to Amazon Aurora and press Connect.

You will be redirected to the configuration screen where you need to provide connection information as shown below.

If you do not have this information ready, please refer to the pre-requisites #3

The table below explains each of the inputs shown on this screen.

Input

Information

Service Instance Name

You can give any value of your choice.

Consumer Key

This is your AWS Access key ID

Consumer Secret

This is your AWS Secret access key

Region

AWS Region Name. Your DynamoDB table should be in this region.

Example: us-east-1, us-east-2, us-west-1, us-west-2

Fill the required information and click on “Validate and Save Settings” button, this will validate the details and if it is correct, integration will be saved, and you will be redirected to the integrations listing page as shown below.

Step 2 Create an App for Amazon Aurora

To create a new app, browse to Apps > Custom Apps and click on New Custom App.

You should see a small popup:

Field

Value

Name

You can give any value of your choice.

Custom App Type

Select Search

Category

Default

Click on Save and a new App will be created. It will be found under Apps > Custom Apps.

An App is successfully created, and you have various options like setup data sources. This is explained in next step.

Step 3 Create a Data Source

A Data Source connects the app with the DynamoDB integration we did in Step #1. Once you have an app created, you can go o Data Sources by either clicking on “Data Source” button or by clicking on “Data Sources” tab.

Now you should see screen as shown below.

To create a data source, click on ‘Create New Data Source’ button and you should see a popup screen as shown below

Give a name of your choice and make sure you select the DynamoDB integration that you created earlier and click Save. You should now see the new Data Source in the listing.

Once a data source is created, you are ready to start creating various methods.

Step 4 Create Methods to fetch the data from Amazon DynamoDB

To use this step, make sure you have completed previous step. If not go back and complete it.

Methods allows you to fetch data from the DynamoDB table. To access the methods, click on the “Methods” tab which is next to the “Data Sources” tab.

As of the publication date of this document, the VoiceWorx platform supports following DynamoDB methods,

  1. Get Tables

  2. Get All Items

  3. Get Item

  4. Get Items

  5. Put Item

  6. Post Item

  7. Delete Item

Depending on requirement, you will have to use an appropriate method, this document will explain each of these methods with an example.

Let’s start with a use case.

Scenario/ Use Case (Contact Finder):

You have a DynamoDB table which is used to store all the contacts in your organization. If you are using this for personal use, then consider that table contains contact details of your friends and family members.

You want to find email address of your friend Sam. For this, you can use GetItems method as explained below.

Method: GetItems

This method requires two parameters as explained below.

Parameters:

Parameter

Sample Value

Info

TableName

Contact

(Select from the drop-down list, you do not have to remember it)

DynamoDB table name. (Case Sensitive)

QueryParameters

Example: 1 {"name":"John"}

Example: 2 {"name":"Smith"}

Key Value pair in a valid json format.

Other than these two parameters, you need to provide the following standard inputs

Parameter

Info

Method Name

You can give any name. This name will be displayed in the methods list.

Request Type

GetItems

This is the method that you are using

Parameters

As explained in the previous table

Result Type

ListOfRecords (Auto Selected)

It indicates that the method can return one or more records

Record Limit

Numeric value (e.g. 10)

If Method is returning larger number of records, then you can limit this result by providing this value.

Record JsonPath

Each method returns data in json format and to pick any specific information from the json, we need to specify the Record JsonPath so the platform will pick that data in consideration while displaying results.

Example: $.[*]

This selects all the records that is returned.

Record Template

Let’s assume that the following json represents one record.

{

"Id": "1",

"Name": "kiran",

"PhoneNumber": "123-456-1234"

}

Now, you want to select name, then you can provide Record Template as

Phone number of {{Name}} is {{ PhoneNumber}}

Note:

Anything written in between {{ }} is represented as a json field and it’s case-sensitive.

Result Template

This indicates how method results should be represented.

Here you go, {{Records}}

Here, {{Records}} represents one or more records separated by a separator.

Sample Response:

[ { "id": 2, "Name": "Mike", "PhoneNumber": "123-456-1234" } ]

Method: GetTables

Create new method

Parameter

Info

Method Name

GetTables

Request Type

GetTables

This is the method that you are using

Parameters

As explained in the previous table

Result Type

ListOfRecords (Auto Selected)

It indicates that the method can return one or more records

Record Limit

Numeric value (e.g. 10)

If Method is returning larger number of records, then you can limit this result by providing this value.

Record JsonPath

Each method returns data in json format and to pick any specific information from the json, we need to specify the Record JsonPath so the platform will pick that data in consideration while displaying results.

Example: $.[*]

This selects all the records that is returned.

Record Template

{{TableName}}

Note:

Anything written in between {{ }} is represented as a json field and it’s case-sensitive.

Result Template

Here are the tables. {{Records}} How else can I help you?

Here, {{Records}} represents one or more records separated by a separator.

Parameters: Not required

Sample Response:

[ { "TableName": "Contacts" } ]

Method: GetAllItems

Parameters:

Parameter

Sample Value

Info

TableName

Contact

DynamoDB table name.

Parameter

Info

Method Name

GetAllItems

Request Type

GetAllItems

This is the method that you are using

Parameters

TableName: Contacts

Result Type

ListOfRecords (Auto Selected)

It indicates that the method can return one or more records

Record Limit

Numeric value (e.g. 10)

If Method is returning larger number of records, then you can limit this result by providing this value.

Record JsonPath

Each method returns data in json format and to pick any specific information from the json, we need to specify the Record JsonPath so the platform will pick that data in consideration while displaying results.

Example: $.[*]

This selects all the records that is returned.

Record Template

Name:{{Name}}, PhoneNumber: {{PhoneNumber}}

Note:

Anything written in between {{ }} is represented as a json field and it’s case-sensitive.

Result Template

Here are the contacts available, {{Records}} How else can I help you?

Here, {{Records}} represents one or more records separated by a separator.

Sample Response:

[ { "id": "3", "PhoneNumber": "(785) 241-6200", "Name": "Andy Young" }, { "id": "2", "PhoneNumber": "(512) 757-6000", "Name": "Sean" }, { "id": "9", "PhoneNumber": "(512) 757-6000", "Name": "Rose Gonzalez" } ]

Method: GetItem

Parameters:

Parameter

Sample Value

Info

TableName

Contact

DynamoDB table name. (Case Sensitive)

PartitionKeyName

id

Name of the Partition Key for the provided table(Case Sensitive)

PartitionKeyValue

1

Value for the Partition Key

Sample Response:

{ "id": "1", "PhoneNumber": "(336)222-7000", "Name": "Jack Rogers" }

Method: PutItem

Parameters:

Parameter

Sample Value

Info

TableName

Contact

DynamoDB table name. (Case Sensitive)

PartitionKeyName

id

Name of the Partition Key (Case Sensitive)

PartitionKeyValue

5

Value for the Partition Key

ItemData

{"PhoneNumber": "(503) 421-7800","Name": "Josh Davis"}

Item data in json format For example

Sample Response:

{ "id": "5", "PhoneNumber": "(503) 421-7800", "Name": "Josh Davis" }

Method: PostItem

Parameters:

Parameter

Sample Value

Info

TableName

Contact

DynamoDB table name. (Case Sensitive)

PartitionKeyName

id

Name of the Partition Key (Case Sensitive)

PartitionKeyValue

5

Value for the Partition Key

ItemData

{"PhoneNumber": "(503) 421-7800","Name": "Josh Davis"}

Item data in json format For example

Sample Response:

{ "id": "5", "PhoneNumber": "(503) 421-7800", "Name": "Josh Davis" }

Method: DeleteItem

Parameters:

Parameter

Sample Value

Info

TableName

Contact

DynamoDB table name. (Case Sensitive)

PartitionKeyName

id

Name of the Partition Key (Case Sensitive)

PartitionKeyValue

5

Value for the Partition Key

Sample Response:

{"response": "Delete Successful"}

From here, you can now proceed to create intents, slots, inputs and then finally publish to supported publish channels. You can refer to this document for further assistance to build and publish App. You can test your app in Alexa developer console and the expected result shall be like this

Congratulations you’ve successfully integrated Amazon DynamoDB to ixHello.

Last updated

Was this helpful?