When & How to Switch to NoSQL with FME

A lot of popular websites wouldn’t be able to function without a NoSQL database on the backend. I mean, using a relational database for something like a social network is like putting a Clydesdale in the Kentucky Derby. Don’t get me wrong — Clydesdales are an excellent breed and probably the best at hauling carts — but my money’s on Seabiscuit.

NoSQL databases have been gaining popularity since 2009. Facebook, Twitter, Buzzfeed, Expedia, eBay, craigslist, and Google all use them. (If Google gave its blessing, it must be good, right?)

Relational databases are designed for static schemas and consistent, reliable transactions. They are not designed to handle booming data volumes at top speed. A NoSQL approach is often preferred for:

✓ Real-time data collection.
✓ Big data storage. NoSQL database structures include key-value pairs (simple and fast), documents (store info as XML/JSON), columns (good for queries), and graphs (good for networks), among others.
✓ Agile schemas. You don’t need to define a structure and stick to it. This can change on the fly.
✓ Horizontal scaling. It’s easy to scale out your architecture when you need to.
✓ Access via APIs. NoSQL uses object-oriented programming as opposed to SQL queries.
✓ Searchable catalogues of information — text, metadata, GIS, or otherwise.
✓ Developing a mobile app or an Internet of Things device.
✓ More: Check out this guy’s list of things people use NoSQL for.

Hundreds of NoSQL DBMS options are available and it’s worth researching which data structures and requirements are best for your scenario. A few choices, in order of current popularity:

• MongoDB
• Cassandra
• Redis
• Elasticsearch
• Couchbase
• Amazon DynamoDB
• Microsoft Azure DocumentDB

Problems with going NoSQL

If you need to integrate a NoSQL database into your existing systems, there are some challenges involved. For example:

• Converting a relational database into document stores (or key-value pairs, columns, graphs, etc.)
• Shifting from unstructured tabular formats, like CSV, to JSON
• Loading existing JSON / XML into your NoSQL database
• Adding spatial capabilities, e.g. geospatial indexes, geometries via GeoJSON
• Connecting to the NoSQL system’s API
• Pulling source data from both cloud and on-premises systems
• Managing big data volumes
• Using NoSQL data in all your regular applications
• Transforming and restructuring NoSQL data

Using automation

The trick is to understand how NoSQL systems store data. Often, adopting NoSQL is a matter of converting your data to JSON and then loading it to the destination system.

Many NoSQL DBMSs have some level of built-in import/export tools. However, their format support is limited, and writing XML/JSON means coding.

Coding aside, the only way to switch to a NoSQL system is via software solutions. By using an automated process, you’re also enabling loading/updating at regular intervals (or in response to a trigger). This means your data store can be updated in real time. For example, you could automatically capture data from web and mobile sources and move it into your NoSQL system without touching a thing.

Webinar

We have a webinar on how to integrate a NoSQL database with your existing systems (without writing any code!). We go through real demos and show you how to automatically merge a wide variety of data, extract data from NoSQL, and work with raw JSON.

Watch: Tips for Adopting NoSQL Databases using FME

Watch: How FME helps you switch to NoSQL

FME is great at processing JSON and has specialized writers for many database systems. Building a workflow to synchronize data with your NoSQL system is a drag-and-drop process, one that you can automate in real time.

NoSQL Databases: Exploring What’s Possible with FME

How can you make the most our of NoSQL Databased with FME? There are a couple of ways FME can be used with NoSQL databases to take advantage of their capacity, performance and agility.

Example 1: Create a Data Warehouse

Goal: Aggregate data from different relational databases into NoSQL and create a central, searchable data warehouse for your organization.

Different departments within an organization often use relational databases that are finely tuned to the unique requirements of their operations. The problem: these independently functioning systems can result in productivity-squelching data silos.

The solution: aggregate all this data and use a NoSQL database as a central repository. Their capacity, search performance and unstructured nature provide a perfect environment for large volumes of structured data from disparate relational databases to coexist. To achieve this the data must be transformed into JSON and this is where FME is a big help.

Create an FME workspace that reads data from the enterprise relational database in question (Oracle, MS SQL Server, PostgreSQL, MySQL, etc) and loads it into your preferred NoSQL database (MongoDB, Elasticsearch, Azure DocumentDB, or Amazon DynamoDB). FME’s NoSQL writers will automatically transform the tabular data into JSON documents.

Add any of FME’s JSON transformers to the workspace to build more complex JSON documents. For example, make changes to contents, create JSON lists, and reorganize the way the JSON is structured to suit your application’s needs.

To ensure NoSQL databases are always up to date, automate your workflows so it runs at scheduled intervals or in response to triggers using FME Server or FME Cloud.

Tip: Create a searchable metadata catalogue by using FME to bring the metadata into a NoSQL database. Metadata can include information on the physical location of a file such as a URL or the owner’s email address.

Example 2: Create a Reservoir for IoT Data

Goal: Automate the collection of data from the Internet of Things into NoSQL databases.

Typically Internet of Things (IoT) data is collected in a time series from sensors on a continuous basis and is made available through web services as JSON. For the same reasons outlined in the first example (large-scale storage capacity, agile schemas) NoSQL databases make great reservoirs for IoT data. The challenge is loading this data as soon as it is collected.

Create an FME workspace that extracts the IoT JSON from a web service, transforms the JSON, and loads it into your NoSQL database. Deploy this workspace using FME Server or FME Cloud, and configure it to run whenever new data is available from a sensor.

*

I hope these examples have inspired you to explore ways to use FME to take advantage of NoSQL databases in your organization. We explain these examples in more detail with demos in our recent webinar, Tips for Adopting NoSQL Databases using FME.

Learn more about building your own NoSQL integration workflow, and try FME for free.