DynamoDB MySQL Connection: 3 Easy Steps

on Database Management Systems, Database Schema Design, DynamoDB, MySQL, MySQL WorkBench, ODBC, ODBC Connector • April 8th, 2022 • Write for Hevo

dynamodb mysql -featured image

DynamoDB is a scalable database management system provided by Amazon that helps users store data as per their requirements. Amazon allows users to query DynamoDB data using DynamoDB API or Command Line Interface (CLI). But, CLI or API cannot handle all queries operations in DynamoDB like joins, aggregation functions (MAX, MIN, COUNT), and more. However, DynamoDB data can be queried using the MySQL Server to manage such operations. Amazon enables users to query DynamoDB data using MySQL with the Amazon DynamoDB ODBC (Open Database Connectivity) driver to connect DynamoDB MySQL. With the Amazon DynamoDB driver, DynamoDB can connect to any application having ODBC connectivity.

In this tutorial, you will learn to connect DynamoDB MySQL Server using the Amazon DynamoDB ODBC driver.

Table of Contents

Prerequisites

Basic understanding of types of databases

What is Amazon DynamoDB?

DynamoDB MySQL: DynamoDB logo
Image credit: aws

Developed in 2021, Amazon DynamoDB is a fully managed NoSQL database. It means that AWS will take care of all the administrative tasks, including hardware provisioning, installation setup, replications, and software patching, performed by the database admin to maintain the database.

Tables in DynamoDB are schema-less, meaning information does not have a fixed structure like relational database systems. As a result, it can handle semi-structured and unstructured data effectively. Despite the complexity of storing semi-structured and unstructured data, DynamoDB allows users to create databases that can store and retrieve any amount of data. With such extensive databases storage, users can provide services to more traffic.

The data entries in DynamoDB tables are called items. The items in the DynamoDB table should consist of at least one primary key or unique key that differentiates it from the other items in the DynamoDB table. And if you want to add data to an item, it should be a maximum of 400KB. However, if you want to add more than 400kb of data to the item, you need to use S3 buckets. 

Features of Amazon DynamoDB

  • Key-value and Document Data model: Amazon DynamoDB supports both key-value and document data models. The key-value pairs form a non-relational database that uses the simple keys to map values for storing and retrieving data. And the document data models are those in which data files internal structures are used to fetch information. Due to such data models, DynamoDB has a flexible schema that helps your databases adapt to the changing requirements.
  • Datatypes: Amazon DynamoDB supports different data types like Scalar, Multi-valued, and Document data types. While Scalar data types are Number, String, Boolean, and Null, which means they can have only one value, Multi-valued datatypes consist of String Set, Number Set, and Binary Set. Multi-valued data types are stored in sets, meaning the values in this data are unique. For example, the month’s attribute can have a String Set of the name of twelve months. Each value in this attribute is unique. And the Document data types are used for complex data structures in the form of Maps and Lists. 
  • The Data Model in Amazon DynamoDB: The Data Model in Amazon DynamoDB consists of tables, items, and attributes. Tables consist of items, and items consist of attributes. Attributes in DynamoDB are like key and value pairs inside tables. Items are like rows in tables with a unique primary key used for finding an exact item in the tables.
  • DynamoDB Streams: DynamoDB Streams are used to capture data modification events in DynamoDB tables. The data about such events appear in the stream in real-time. It is also stored in the order in which the events occurred. A stream record represents every event. When you enable a stream on the DynamDB table, DynamoDB Streams writes a record whenever any of the below event occurs.
    • A new item is added to the table.
    • An item is updated.
    • An item is deleted from the table.
  • Scalability: DynamoDB automatically spreads the data and traffic for tables over a sufficient number of servers to handle throughput and storage requirements. If your data grows, DynamoDB will add storage automatically. And if your data shrinks, it will remove storage automatically.
  • Data Partitions: DynamoDB stores data in partitions. Primary keys in the DynamoDB database are used to uniquely identify items and are also used to store data in partitions. The primary key consists of two components. i.e., Partition key or Hash Attribute. It is the key that is used to store data in partitions. 
  • Sort key (Range Attribute) is the second component. It is useful when the Partition key is not unique. Items with the same partition keys are stored in the same partitions. Sort keys are used to sort data on the same partitions. To insert the items with the primary key in DynamoDB, DynamoDB uses a hash function. For example, consider the below table:
DynamoDB MySQL: primary key in database
Image Source: Self
  • If you want to store the above data in DynamoDB, it is stored in partitions. Using a hash function, you can insert the data with primary key 100 to DynamoDB. This primary key value (100) will be fed to the hash function. The hash function will generate the partition number based on the primary key. And therefore, it will store the item in the data partition.
  • Secondary indexes: Secondary indexes are used for fast query performance in DynamoDB. They consist of partition keys and sort keys. The partition key and sort key make up the primary key. The data is stored on partitions based on these primary keys. There are two types of indexes. First is Global Secondary indexes (GSI), where the partition key and the sort keys are different from the base table. For Local Secondary Indexes (LSI), partition keys are the same as the base tables, but the sort keys are different. LSI does not get its own partitions, but they are stored along with the base tables. You can create a maximum of five GSI and LSI of the base tables. LSI needs to be added at the time of table creation only. They cannot be added after the table is created. Indexes are updated automatically when tables are modified. While creating an index, attributes to be copied has to be mentioned. If nothing is mentioned, DynamoDB will copy only the key attributes of the base tables. It means indexes can contain additional attributes from the base tables apart from the index keys.

Simplify Data Analysis with Hevo’s No-code Data Pipeline

Hevo Data, a Fully-managed Data Pipeline platform, can help you automate, simplify & enrich your data replication process in a few clicks. With Hevo’s wide variety of connectors and blazing-fast Data Pipelines, you can extract & load data from 100+ data sources straight into your Data Warehouse or any Databases. To further streamline and prepare your data for analysis, you can process and enrich raw granular data using Hevo’s robust & built-in Transformation Layer without writing a single line of code!

GET STARTED WITH HEVO FOR FREE[/hevoButton]

Hevo is the fastest, easiest, and most reliable data replication platform that will save your engineering bandwidth and time multifold. Try our 14-day full access free trial today to experience an entirely automated hassle-free Data Replication!

What is MySQL?

DynamoDB MySQL: mysql logo
Image credit: mysql

MySQL is a relational database management system that organizes and stores data into rows and columns. Users can define, update, add, and query data using Structured Query Language (SQL). SQL is a specialized language for communicating between databases. 

MySQL is an open-sourced database owned by Oracle Corporation. MySQL Server works as a client-server model that supports a variety of backends and different Application Programming Interfaces (APIs). MySQL is used for data warehousing, e-commerce, business applications, and more. Database-driven web applications like WordPress, Joomla, phpBB, and more use MySQL.

Connecting DynamoDB MySQL

Users can use the SQL Gateway from the ODBC driver for Amazon DynamoDB data to query Amazon DynamoDB data through the MySQL interface for connecting DynamoDB MySQL.

You can download the Amazon DynamoDB ODBC driver. The Amazon DynamoDB ODBC Driver enables you to connect with live data from Amazon DynamoDB from any application supporting ODBC connectivity like MySQL. Hence it is possible to connect DynamoDB MySQL.

Follow the below procedure to start the connection of DynamoDB MySQL, using the MySQL remoting service of the SQL Gateway, and work with live Amazon DynamoDB data in MySQL Workbench. SQL Gateway is a software that allows users to gain access to ODBC (Open Database Connectivity) data sources as virtual SQL Server or MySQL Server remotely. 

Step 1: Connecting to Amazon DynamoDB data

You can connect to Amazon DynamoDB MySQL using Accesskey, SecretKey, and Region. You can obtain your SecetKey and AccessKey using the security credentials of your Amazon Web Services account. And when you log in to DynamoDB, your Region will be displayed in the upper left-hand corner.

Step 2: Configure the SQL Gateway

Follow the SQL Gateway Overview to connect Amazon DynamoDB as a virtual MySQL database. You can configure a MySQL remoting service that listens for MySQL requests from clients.

What makes Hevo’s ETL Process Best-In-Class

Providing a high-quality ETL solution can be a difficult task if you have a large volume of data. Hevo’s automated, No-code platform empowers you with everything you need to have for a smooth data replication experience.

Check out what makes Hevo amazing:

  • Fully Managed: Hevo requires no management and maintenance as it is a fully automated platform.
  • Data Transformation: Hevo provides a simple interface to perfect, modify, and enrich the data you want to transfer.
  • Faster Insight Generation: Hevo offers near real-time data replication so you have access to real-time insight generation and faster decision making. 
  • Schema Management: Hevo can automatically detect the schema of the incoming data and map it to the destination schema.
  • Scalable Infrastructure: Hevo has in-built integrations for 100+ sources (with 40+ free sources) that can help you scale your data infrastructure as required.
  • Live Support: Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support calls.
Sign up here for a 14-day free trial!

Step 3: Query Amazon DynamoDB from MySQL Workbench

Follow the below steps to connect Amazon DynamoDB MySQL together

  • Click to add a new MySQL connection in the MySQL Workbench.
  • Name the connection.
  • Set the Hostname, Port, and Username parameters to connect to the SQL Gateway.
  • Click on the ‘Store in Vault’ tab to set and store the password.
  • Click on the ‘Test Connection’ tab to ensure the connection is configured correctly. Click on OK, as shown below.
DynamoDB MySQL: setup new connection
Image credit: cdata

Follow the below steps to query the Amazon DynamoDB data inDynamoDB MySQL Connection

  • Open the connection for DynamoDB MySQL you created using the CData SQL Gateway for Amazon DynamoDB.
  • Click on the File → New Query tab.
  • Write a SQL query to retrieve Amazon DynamoDB data using the below command and verify the DynamoDB MySQL connection.
SELECT * FROM `CData AmazonDynamoDB Sys`.Lead;
DynamoDB MySQL: MySQL Workbench
Image credit: cdata

Using CData SQL Gateway, you have queried Amazon DynamoDB data from MySQL Workbench and connected DynamoDB MySQL.

Querying DynamoDB MySQL client has the below advantages.

  • Users can query by any field like the primary key, sort key, or index.
  • Users can use aggregate functions like AVG, COUNT, MAX, MIN, and more.
  • Users can delete and update multiple records (items) by any filter.
  • Users can also add multiple rows or copy tables in one command.

Conclusion

In this tutorial, you have learned to connect DynamoDB MySQL. While Amazon DynamoDB ODBC driver was used to connect with the data from DynamoDB, CData SQL Gateway was used to provide MySQL access to the DynamoDB data. You can also connect DynamoDB with external data sources like Amazon Redshift and Amazon EMR. 

There are various trusted sources that companies use as it provides many benefits but transferring data from it into a data warehouse is a hectic task. The Automated data pipeline helps in solving this issue and this is where Hevo comes into the picture. Hevo Data is a No-code Data Pipeline and has awesome 100+ pre-built Integrations that you can choose from.

visit our website to explore hevo

Hevo can help you Integrate your data from numerous sources and load them into a destination to Analyze real-time data. It will make your life easier and data migration hassle-free. It is user-friendly, reliable, and secure.

SIGN UP for a 14-day free trial and see the difference!

Share your experience of learning about DynamoDB MySQL connection in the comments section below.

No-code Data Pipeline For your Data Warehouse