Advertisement

How to Create and Query a NoSQL Table

By on

Click to learn more about author Alex Williams

NoSQL databases are incredibly versatile and flexible, and while it would be great if there was a general approach to creating and querying a NoSQL database, as there are dozens of NoSQL databases, that’s not the case.

Instead, what we’ll do here today is give you an idea of how to create and query on two of the most popular databases around, Amazon DB and Oracle NoSQL.

Amazon DB

If you aren’t familiar with Amazon DB, it’s a key-value store database created by Amazon. It’s mostly built for incredibly high performance, delivering millisecond results, and can handle millions of queries per second. This makes sense considering Amazon is one of the biggest online marketplaces on the planet.

Creating a NoSQL Table in Amazon DB

Once you’ve opened the DynamoDB console, you’ll need to click create table. You can use any library you want, but for this hypothetical, we’ll just use films.

1. Write Films in the Table Name box.

2. Type Studio in the Partition Key box. Partition keys are used to spread data across several partitions. This helps with scalability down the line, so it’s important to choose something with a wide range of values.

3. Since studios tend to have several films under their belts, you might want to enable sorting with a sort key. Click on the Add Sort Key checkbox and then type filmName in the box.

4. Uncheck the Use Default Settings box. This will give you the options for auto-scaling, which allows the read/write capacity of the table to change based on the number of requests.

5. While we won’t be altering the defaults in certain settings, be aware of Secondary indexes, Provisioned capacity, and Auto Scaling areas. Once you scroll past those, you’ll see a “create” button, which you’ll want to click.

Once you’ve clicked on create, you’re done! You’ll find it in the table list with a checkbox next to it.

Querying Table in Amazon DB

Since Amazon DB is a key-value store, it uses keys to fulfill queries. This makes it very efficient and relatively straightforward to use.

By the way, don’t forget to actually add data to the table you created.

In the console, go to the items tab, and in the drop-down list, change it to query. After that, just input your query criteria and click on start search.

See, pretty easy!

Oracle NoSQL Database

Much like Amazon DB, Oracle NoSQL is made for high-performance and high-traffic applications. It’s integrated into a variety of Oracle products like Big Data and Fusion Middleware, and they even have a cloud service if you’d like to go that route.

Creating a NoSQL Table in Oracle NoSQL Database

Creating a database here is a little bit more involved, if only because it doesn’t have as clean and easy-to-use of an interface as AmazonDB.

To create a table, you will need to use data definition language (DDL), and to do so, you’ll need to use the NoSQLClient#tableDDL method. Here are a couple of examples:

Keep in mind that this is asynchronous and delivers a Promise of TableResult (which tells you the current state of the table). Generally speaking, NoSQLClient#tableDDL doesn’t wait for the underlying DDL operation to complete, and the result of TableResult will likely have one of several intermediary tables: TableState.DROPPING, TableState.CREATING, or TableState.UPDATING.

You can of course go straight to the operation completion if you aren’t interested in any of the intermediate steps:

When you get a return of the above, the result will show the final state of the operation.

Querying Table in Oracle NoSQL Database

Oracle NoSQL Database actually has its own complex query language, but we won’t get into that complexity right now.

To create a query, you’ll need to use the NoSQLClient#query method:

This returns Promise of QueryResult, which is a Javascript object that contains an array.

Much like other databases, such as MongoDB, the results are limited by default. You can get additional results by setting continuationKey in the opt argument with your next call:

Also, you can prepare queries if you’re going to do them often by using the NoSQLClient#prepare method. If you want to run a prepared query, pass the PreparedStatement you get from the prepare method to the NoSQLClient#query method instead of the statement.

Conclusion

As you can see, each database has its own way of approaching table query and creation. In fact, most databases tend to have their own rich query language, which you can look up and even possibly master. Querying is an incredibly powerful tool, especially when used by data scientists and data analysts.

Leave a Reply