Thomas Henson

4 Types of NoSQL Databases

by Leave a Comment

Early in my career as a Data Engineer I spent a majority of my time in ETL hell. If you aren’t familiar with ETL it stands for Extract, Transform, and Load. Basically it’s the process Data Engineers use to put structure to unstructured data. In a IoT workload, imagine pulling data from a sensor that shows time, temperature, location, wind, and etc. The data from our sensor would resemble a semi-structured log file, but to put this data into a traditional SQL database some ETL would have to happen. However now this type of workflow is old school and now with NoSQL databases we can pair down on the structure needed from semi-structured and unstructured data. In this blog post let’s go over the 4 types of NoSQL Databases every Data Engineer should understand.

types of nosql databases

What is NoSQL Databases

NoSQL is commonly referred to as not only SQL, non SQL, or non relational databases. These non SQL databases are built for extreme high throughput using key value pairs versus relational databases with relative dependence. Using loose dependences and quick indexes NoSQL databases are perfect for Streaming Analytics and IoT applications because data can quickly be stored and referenced.

SQLNoSQL
Relational dependencesLoose dependences
Updates to tables time consumingUpdates to tables on-demand
Performance dependent on queries & indexes
Performance depend on hardware & network
Rigid scaling
Elastic scaling
CostlyCost Efficient

Types of NoSQL Databases

Not all NoSQL Database are the same! Let’s explain the 4 types of NoSQL databases and their use cases.

Columnar Database

The first type of NoSQL database is the Columnar databases which is optimized for reading and writing columns of data as opposed to rows of data. Column-oriented storage for database tables is an help drive down the input/output requirements for database. Since the I/O profile is lowered, overall storage footprint is lowered. One main feature of Columnar Databases is their ability to compress data. Instead of data being written in traditional row orientation, Columnar databases use column orientation. Each column will be associated with column key. Checkout this example from my HBase Blog Post.

Row Oriented

Row Oriented
1
2
row1,2983212,124212,9863
row2,232,1232,2263

Column Oriented

1
2
3
4
row1, row2
2983212,232
124212,1232
9863,2263

See how everything is organized by columns? Sure makes for an adjustment for experienced SQL Data Engineers.

Document Database

Document databases store data as documents but for storing documents (I used to think this…). The design of the data in Document Databases takes on a semi-structured fashion like JSON or XML. The schema for Document Database is flexible which gives the developer the tools to scale the applications supported by the Database. The design and loose schema requirements allows for high throughput in Document Databases.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
{
    "glossary": {
        "title": "example glossary",
"GlossDiv": {
            "title": "S",
"GlossList": {
                "GlossEntry": {
                    "ID": "SGML",
"SortAs": "SGML",
"GlossTerm": "Standard Generalized Markup Language",
"Acronym": "SGML",
"Abbrev": "ISO 8879:1986",
"GlossDef": {
                        "para": "A meta-markup language, used to create markup languages such as DocBook.",
"GlossSeeAlso": ["GML", "XML"]
                    },
"GlossSee": "markup"
                }
            }
        }
    }
}
 
Example JSON from http://json.org/example.html

Graph Database

Graph database focuses on how data relates to other data points. Data from Graph databases store directed link between data sets called edges. These edges are displayed in Graphical representation of the data (hence the name). SQL or other NoSQL database can act as the base layer for Graph databases. For example, think of how an application recommends movie to watch next. After a user rates a couple movies relationships can be shown that if a person like Star Wars they might like Lost in Space as well.

Key-value Database

Key-value databases function in heavy read environments. Theses types databases rely on compute and memory to speed up reads for supporting applications.  Data stored in Key-Value databases follow a object oriented schema style vs. relational schema. Since the data is to be read very quickly the hardware is typically memory or SSD drives vs. traditional HDDs. Data in Key-value databases is held at a premium because storing the data tend to be higher cost vs. the other NoSQL databases.

Popular NoSQL Databases

Cassandra – One of the first Big Data coined NoSQL database. Right now they boast having over 10 Petabytes in production. Cassandra has support for Hadoop and Spark.

Hbase – Top NoSQL opensource on Hadoop choice. Facebook is both a heavy user and contributor to HBase. HBase is Columnar database.

MongoDB – One of the most popular name in NoSQL. MongoDB is a document db using JSON like document schema to store data in database.

BigTable – The brain child of Google. When Google released the whitepaper on BigTable HBase was developed out of the research. Now available through Google Cloud Platform. BigTable is a Columnar database.

DynamoDB –  Touted by AWS as the most popular Cloud NoSQL Database. DynamoDB is a Document and Key-value database (really cool stuff). Being out of AWS DynamoDB is an extremely customize able NoSQL database that can scale as the use case decides.

CouchDB – Another Big Data NoSQL Database. CouchDB is a Document database. Designed around ease and flexibility for developer to get running as quickly as possible.

CosmosDB– Azure’s offering for global distributed NoSQL database with scale.  CosmosDB was built off the success of DocumentDB.

Want More Data Engineering Tips?

Sign up for my newsletter to be sure and never miss a post or YouTube Episode of Big Data Big Question where I answer questions from the community about Data Engineering questions.

 

DynamoDB Create Table

by Leave a Comment

Maybe it’s my background as a Web Developer, but whenever learning a new development stack I want to build a CRUD(Create, Read, Update, Delete) application first thing. Step one in creating a CRUD application for a database is create a table. In this post let’s see how quickly we use DynamoDB Create table from both AWS console and CLI. 

DynamoDB Create Table

DynamoDB is a NoSQL database built by Amazon for both the AWS cloud and off premise. The non-relational database supports both Key-Value and Document models. In this walk through we will focus on the key-value model. If you are interested in learning more DynamoDB Commands checkout 11 DynamoDB CLI commands.

DynamoDB Create Table

Table Example

In this tutorial let’s create a table for global college team registry. We want to track name, shortname, mascot, colors, and location. For example the University of South California would contain the following:

  1. Name – University of South California
  2. ShortName – USC
  3. Mascot – Trojan
  4. Colors – Cardinal, Gold
  5. Location – Los Angeles, CA

Before loading our data into the table we are going create a table and assign data types to the items.

DynamoDB Create Table From Console

Let’s walk through the steps to creating a table in DynamoDB to track College Teams from the AWS Console. Make sure to log in and navigate to the DynamoDB service.

  1. Create Table – Our table name is ‘collegeteam‘. The table name must be unique per AWS region.
  2. Partition Key (Primary Key) – Primary Key is a combination of the Partition Key (that we are entering here) and Sort Key (if sort key is used). Since we are using both Parition Key and Sort Key DyanmoDB will hash the keys across the AWS region. For our college teams table our Partition Key for sorting our teams is ‘name’ (string).
  3. Add Sort Key -The sort key is additional key to enrich queries associated with the Primary Key. All queries will be associated with the Primary Key.  For the college teams table we have ‘name’ as our primary key, lets’ add the ‘location’ as our sort key.
  4. Default Settings/Options – Ready to create if not let’s discuss the advanced settings below
    1. Secondary Indexes – Remember we added additional sort keys tied to our Primary Key? We can use Secondary Indexes when we want to setup indexes independent of the Primary Key. For the college team tables let’s set a secondary index with ‘mascot’ (string) as the Partition Key (Primary Key) and ‘colors’ (string) as the Sort Key. The mascot secondary index will have separate read/write capacity units from the primary index.
    2. Provision Capacity – Default setting is for 5 RCUs (Read Capacity Units) and 5 WCUs (Write Capacity Units). To change provisioned capacity you must disable auto scaling. We will leave the provision capacity with the default setting.
    3. Auto Scaling – Enables DynamoDB Developer to configure how AWS will scale RCUs and WCUs to create elastic tables. We will leave the Auto Scaling enabled and keep the default settings.
    4. Create – Now we are ready to create our table.
    5. Items – After creating table we can navigate to Items and create items. Let’s create our first item for University of South California. Ensure to match the correct data types for each item.
      1. Name (string) – University of South California
      2. ShortName (string) – USC
      3. Mascot  (string)- Trojan
      4. Colors (string) – Cardinal, Gold
      5. Location (string) – Los Angeles, CA
    6. Save – Hit the Save button and now we have just created a college team DyanmoDB table.
DynamoDB Create Table
Source: Getting Started with Amazon Dynamo DB (Jim Scharf) – AWS DB Day

Creating DynamoDB Table From CLI

In the first example we used the AWS console to create our college team table, but now let’s use the same example to create a DyanmoDB table from AWS CLI. Before creating the table make sure we have the AWS CLI configured.

  1. DyanmoDB Create Table – All of the different options for the college team table are passed into the ‘aws dynamodb create-table’ command.

Create Table
1
2
3
4
$ aws dynamodb create-table --table-name college-team
--attribute-definitions AttributeName=name,AttributeType=S AttributeName=location,AttributeType=S  
--key-schema AttributeName=name,KeyType=HASH AttributeName=location,KeyType=RANGE
--provisioned-throughput ReadCapacityUnits=5,WriteCapacityUnits=5

2. Add Items – Now that our table is created we can use the ‘put-item’ command to populate our DynamoDB table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
$ <span class="crayon-e">aws </span><span class="crayon-e">dynamodb </span><span class="crayon-v">put</span><span class="crayon-o">-</span><span class="crayon-i">item</span> –<span class="crayon-o">-</span><span class="crayon-v">table</span><span class="crayon-o">-</span><span class="crayon-e">name </span><span class="crayon-v">college</span><span class="crayon-o">-</span><span class="crayon-i">teams</span> –<span class="crayon-o">-</span><span class="crayon-e">item </span><span class="crayon-v">file</span><span class="crayon-c">//put-item.json</span>
 
//put-item.json
{
    "college-teams": [
        {  
              "PutRequest" :{  
              "Item": {
                    "name": {"S": "University of South California"},
                    "shortname": {"S": "USC"},
                    "location": {"S": "Los Angeles, CA"},
                    "mascot": {"S": "Trojan"},
                    "colors": {"S": "Cardinal, Gold"}
                }
            }
        }
    ]
}
//end source

Want More Data Engineering Tips?

Sign up for my newsletter to be sure and never miss a post or YouTube Episode of Big Data Big Question where I answer questions from the community about Data Engineering questions.

11 Fundamental DynamoDB CLI Commands

by 5 Comments

Do you know how to create a DyanmoDB table from the CLI?

What are the fundamental DynamoDB CLI Commands every AWS Data Engineer should know?

DynamoDB CLI Commands

What is DynamoDB?

DynamoDB fully managed cloud NoSQL database that supports both document and key value store models. Amazon touts DynamoDB as the most popular Cloud based NOSQL database. DynamoDB is NoSQL database not a traditional relational database thus does not support Joins. Below are a few more key characteristics of DyanmoDB:

  • DynamoDB Characteristics
  • Fully Managed NoSQL Database
  • Key-value pair NoDSQL Database
  • Durable across 3 availability zones
  • Hardware is a performance play with dedicated SSD
  • Scales up or down without any down time
  • Heavy support for JSON

DynamoDB CLI Commands

Ready to Use the DyanmoDB CLI Commands?

All the commands below are executed using the AWS CLI (Link to AWS CLI) with permission to DynamoDB. Once the AWS CLI is installed make sure to configure the AWS CLI to the DyanmoDB region. For the examples below I will be working on a DynamoDB table for college teams with table name of college-teams. The table will have 5 items (college-id, colors, location, name, mascot). The college-id item will the primary partition key and data type of number. The remaining items will all be classified as data type string.

DynamoDB CLI Commands

DynamoDB CLI Commands

Scan

aws dynamodb scan – Returns one or more items from DynamoDB table. Different from query because scan is a brute force query. Since scan will walk the entire table make sure to use sparingly.

1
$ aws dynamodb scan --table-name college-teams

Create Table

aws dynamodb create-table – Creates new unique table in Dynamodb. Ensure when creating table to follow AWS best practices with table name being unique in each region. Accepts parameters for table name, local secondary index, global secondary index, key schema, and other. Using –cli-input allows to pass in table configuration via JSON.

1
2
3
4
$ aws dynamodb create-table --table-name Aircraft
 
Create Table - Complex (passing in JSON file)
aws dynamodb create-table --table-name

List Tables

aws dynamodb list-tables – List all tables in region.

1
$ aws dynamodb list-tables

Put Item

aws dynamodb put-item – Add new items or update existing item to DynamoDB table. Using JSON helps add better structure for parameters and allows developer/administrators to not have type all on one line.

1
$ aws dynamodb put-item –-table-name college-teams –-item file//

Describe Table

aws dynamodb describe-table –  Explains from an AWS perspective the table structure.

1
$ aws dynamodb describe-table -–table-name college-teams

Get Item

aws dynamodb get-item – Return item with the passed in key value from DynamoDB table. To pass in the values to return it is best to pass in through JSON file. In the example below we want to return the value with a college-id = 1. The “N” specifies the data type which in this example is a number.

1
2
3
4
5
6
7
$ aws dynamodb get-item –-table-name college-teams –-key file://get.json
 
//source of get.json
{
  "college-id": {"N": "1"}
}
//end of source

Query

aws dynamodb query – Query returns one or more items based of primary keys. Supports any tables or secondary index from composite key. Use JSON to ease query input.

1
2
3
4
5
6
7
8
$ aws dynamodb query –-table-name college-teams --index-name college-id
--expression-attribute-value file://query.json
 
//source of query.json
{
  "college-id": {"N": "1"}
}
//end of source

Batch Get Item

aws dynamodb batch-get-item – Query multiple items from DynamoDB Table. They key to using the command is to pass the request to use keys in the JSON file. Used primary for large queries with upper limitation of 100. DynamoDB places an upper limit of 100 on batch-get-item. Make sure to take in to account the capacity read units for each query.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
$ aws dynamodb batch-get-item --request-items file://request-college.json
 
// source of request-college.json
  {
    "college-teams": {
        "Keys": [
            {
                "college-id": {"N": "5"},
                "mascot": {"S": "ugga"}
            },
            {
                "college-id": {"N": "6"},
                "mascot": {"S": "trojan"}
            },
            {
                "college-id": {"N": "7"},
                "mascot": {"S": "wolverine"}
            }
        ],
        "ProjectionExpression":"college"
    }
}
//end of source

Batch Write Item

aws dynamodb batch-write-item – Allows for administrators/developers to add or delete multiple items in a DynamoDB table. Just like the Batch Get Item the request is passed in the JSON file. For adding items we will use the “PutRequest” and for deleting items use the “Delete-Item”.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
$aws dynamodb batch-write-item --request-items file://write-college.json
 
//source for write-college.json
{
      "college-teams": [
        {  
              "PutRequest" :{  
              "Item": {
                    "college-id": {"N": "5"},
                    "colors": {"S": "red, black"},
                    "location": {"S": "georgia"},
                    "mascot": {"S": "ugga"},
                    "name": {"S": "georgia"}
                }
            }
        },
        {
            "PutRequest": {
                "Item": {
                    "college-id": {"N": "6"},
                    "colors": {"S": "red, gold"},
                    "location": {"S": "california"},
                    "mascot": {"S": "trojan"},
                    "name": {"S": "south california"}
                }
            }
        },
        {
            "PutRequest": {
                "Item": {
                    "college-id": {"N": "7"},
                    "colors": {"S": "blue,maze"},
                    "location": {"S": "michigan"},
                    "mascot": {"S": "wolverine"},
                    "name": {"S": "michigan"}
                }
            }
        }
    ]
}
//end of source

Delete Item

aws dynamodb delete-item – Deletes item based off key value from DynamoDB table.

1
2
3
4
5
6
7
$ aws dynamodb query delete-item --table-name college-teams --key file//d-item.json
 
//Source d-item.json
{
   "college-id": {"N": "1"}
}
// end of source

Delete Table

aws dynamodb delete-table – Deletes table and all configurations associated including indexes.

1
$ aws dynamodb query delete-table --table-name college-teams

Want More Data Engineering Tips?

Sign up for my newsletter to be sure and never miss a post or YouTube Episode of Big Data Big Question where I answer questions from the community about Data Engineering questions.