Merging the comfortable familiarity and efficiency of SQL with the flexibility and scalability of a non-relational systems is no easy task. CrateDB, offered by Crate.io, is a “distributed SQL database that is built on top of a NoSQL foundation.” It allows users who are comfortable using and SQL database to save and query machine data, as well as handle the diversity, velocity, and volume of machine and log data. This is especially useful to manufacturers installing an Industrial Internet of Things (IoT) sensor system in their factories. The sensors monitor and control the production equipment. A database such as CrateDB is needed to process the huge volumes of data flowing through the system.

In an interview, Crate.io’s Chief Product & Marketing Officer Andy Ellicott, having previously worked for IBM, Cloudant, VoltDB, and Vertica, described himself as “a guy who has spent his career launching databases.” Ellicott joined Crate two years ago:

“I looked at CrateDB and realized they were a SQL alternative to Splunk, which at the time had the machine data space all to itself. In a really crowded database market, there was this big empty patch—machine data—that had very little competition, and with IoT on the horizon that would not last long. That intrigued me.”

Crate.io offers services and products based on CrateDB (which is Open Source and free, per Apache 2.0’s Open Source license), and it has become involved with the Smart Systems markets: Smart Factories, Smart Cities, Smart Vehicles, as well as cybersecurity. The CrateDB project was initiated by Jodok Batlogg, an Open Source contributor and developer. It was started in 2013, with the goal of helping mainstream SQL developers use machine data, quickly and simply.

The Next Step for ALPLA

ALPLA was one of CrateDB’s first customers, and is a world leader in packaging solutions. They produce high-quality packaging for Coca-Cola, Unilever, and several other companies in the drinks, food, cosmetics, and cleaning industries. ALPLA has 18,300 employees working at 172 locations, in 45 different countries.

Prior to 2016, people monitored ALPLA’s manufacturing equipment, hoping to solve problems before they happened, and to streamline the manufacturing process. In 2016, in an effort to improve efficiency, ALPLA began integrating data from sensors on the factory equipment into real-time dashboards monitored by operations. Each factory had 900 different types of embedded sensors. Data from these sensors was used to control the machines in real-time, reject defective products, and inform staff when it was time to adjust and tune the equipment. The big picture goal was to integrate the sensor data coming from multiple factories and send it to a centralized “mission control.” Mission control would be staffed by experts who would monitor production from a distance, using interactive dashboards.

However, ALPLA chose SQL Server to act as the as data storage for the sensors, and it couldn’t keep up with data it was receiving. The system required 900 tables, with one table assigned to each of the different sensors, and with each sensor receiving hundreds of readings per second. Charts in mission control dashboards took three to five minutes per each update, which was too slow for their purposes. CrateDB turned out to be their solution. Describing the situation, Ellicott stated:

“Switching to CrateDB was easy for them, because their system was already set up for SQL data and SQL code. The benefits of using CrateDB, because of its NoSQL foundation, include storing lots of different data structures in the same table, which makes things easier and faster for database administrators.”

Insight to Action Using Tablets and HoloLenses

CrateDB dashboards are located in mission control centers, where experts remotely monitor, troubleshoot, and enhance equipment efficiency. The experts use virtual reality equipment (HoloLenses/Augmented VR) to connect with the factory floor, and operating adjustments can be communicated to factory workers by way of tablets. By collecting and analyzing data from all of the sensor systems, mission control directs people on the floor to the “hot spots,” minimizing waste and improving efficiency. This technology is transforming ALPLA into an even more modern, efficient industrial IoT organization.

CrateDB’s Core Strengths

CrateDB can be used to store and query machine data. The database makes it easy to handle the diversity, velocity, and volume of log and machine data. These abilities are supported by CrateDB’s core strengths:

• Scaling: CrateDB offers simple scaling and automatic data re-balancing (scaling simply requires adding new machines).
• Distributed SQL Queries: Features columnar field caches and a more modern query planner.
• Highly Available: Includes rolling software updates and automatic replication of the data through a cluster. This helps to assure hardware failures do not interrupt access. Additionally, CrateDB clusters are self-healing.
• Real-time Data Processing: Supports 40,000+ inserts each second, per node.
• BLOB Storage: Stores and retrieves Binary Large Objects (BLOBs), such as video, pictures, or large unstructured files.
• Time Series Analysis: Used to identify trends and anomalies.
• Geospatial Queries: Stores and queries geographical information with the geo-point and geo-shape types.
• Dynamic Schemas: Unlike many other SQL databases, CrateDB schemas are totally flexible.
• Snapshots: Saves incremental snapshots of the database and sends them to storage.

The CrateDB Cloud

When Crate.io began, its mission was to provide fast, easy-to-use Analytics for massive amounts of machine-generated data, while operating in “containerized, Cloud-native environments.” While that mission has not changed, it has expanded with the release an “Enterprise Edition.” Describing their Cloud services, Andy Ellicott said:

“This strategy promotes maximum uptime and efficiency. Customers can run the managed service on both public and private cloud platforms.”

CrateDB can be used with the Cloud services of:

• Amazon Web Services (AWS)
• Amazon Machine Images (AMIs)
• Microsoft Azure
• Softlayer
• Packet

Crate.io provides a “free” hosted CrateDB Cloud work space for 30 days. The program is designed to provide experience and spark interest in the CrateDB Enterprise Edition. Their program is hosted by Microsoft Azure, and is managed 24 hours per day, seven days a week by the expert advisors at Crate.

Smart Lighting

Smart lighting is designed for energy efficiency. It may include fixtures designed for high efficiency or automated controls that adjust to varying conditions such as the amount of sunlight available or whether there are people in the room. The technology supporting smart lighting can communicate with its environment, dramatically improving electrical efficiency.

Crate.io has worked with Tridonic, a provider of lighting solutions such as smart lighting. They have supplied smart lighting solutions at to retail chains and landmark hotel and office buildings.

Smart lighting has also become instrumental in the development of Smart Cities, which also use sensors. Smart lighting allows cities to manipulate the lighting for unusual circumstances. For example, emergencies and dangerous situations at night can be “brightened” (or even dimmed) quickly and easily, by a smart lighting system that communicates with its individual components.

Smart Cities

CrateDB can process massive amounts of data sent to it by a manufacturing environment. But their database design is not limited to manufacturing; Smart cities can also use their software. Sensors installed at appropriate locations inside a city provide data useful for making intelligent decisions. Data generated by thousands of sensors is analyzed in real time, monitoring and controlling lighting, traffic signals, and city equipment. CrateDB offers the following abilities to Smart Cities:

• Process thousands of data points per second
• Query data in real-time
• Process a variety of IoT data types
• Handle data at the edge of and within the Cloud

Smart Cities provide their citizens with a high quality of life, while using a minimal consumption of energy. This is done by using intelligent interconnections built into the infrastructure (heat, transportation, electricity, communications, and Smart Buildings) that share information. In Smart Cities, technological advances such as Cloud storage and the IoT, combined with sensors, allow huge amounts of information to be shared. This information ranges from air pollution to traffic to gunshots. Also, smartphone apps generally allow citizens and city workers to note problems and send observations to city hall. Ellicott commented,

“In the future, database management systems are going to get a lot smarter, and actually do a lot more, and share a lot more of the application workload.”

Photo Credit: Rachael Arnott/Shutterstock.com