How to use multiple record types to provide SQL access to NoSQL type data

Introduction

With the development of Web Access, mobile devices, reports and analytics packages, and other applications, the database market is rapidly innovating. However, a large number of systems are not fully involved in these innovations because they do not organize their own data in a relational way. Because relational APIs such as SQL, Odbs, JDBC, PHP, and ADO are not available. NET, etc.) to access data efficiently, many systems cannot effectively apply these innovations.

This article will provide a solution for a very specific challenge: mix a different record structure in the same file and use a field to tell the application how to interpret each record. If you have the skills associated with a modern relational database, you may find it strange to hear that. You learn to follow a strict pattern and normalize the data by separating the data into different tables. However, business software is not always written in this way. In the near past, the total number of file handles was limited at the operating system level. For example, in the DOS OS era, the number of file handles plus stdin, stdout, stderr, and so on is 20.

Although a mixed-record structure is appropriate for non-relational or NoSQL APIs, because there is no pattern required, developers often face situations where a technique is needed to use a pattern. A consistent pattern definition for some software using SQL (or ODBC, etc.) is required, such as a large number of third-party reporting and analysis tools, including Crystal Reports and Excel, as well as many web and mobile device applications.

When a business needs to use SQL to access that type of data, they typically convert the data into a relational format by using techniques such as running a batch program to extract the data, and then loading them into relational database tables. These batch programs may have to run all night, so the data being imported is rarely kept up to date. Another option is to add a SQL API that requires decomposing each record structure into its own separate physical file, which can lead to risky changes to the existing code for the application. Another workaround is to rewrite the entire application. Most of the time, these codes have been working for years or even decades without any changes, so the smaller the impact of code changes, the better.

To avoid these problems and allow developers to map existing, faircom data into relational data, the C-treeace database of the company introduces support for multiple record types (MRT) tables. The MRT table supports a method of processing data that cannot be integrated into a single SQL schema. The MRT table is used in conjunction with existing data that is stored in a non relational table for the first time. Although it is doubtful if any of the developers will now create a new application based on the MRT table, any developer who deals with the traditional applications mentioned above will find this very valuable. If you envision a previously mentioned batch program as some type of ETL (extract, transform, load), you can envision the MRT table as a type of "virtual" ETL (extract, transform, and create a set of virtual tables that can be accessed in real time through SQL). The magic of FairCom's technology is that the conversion is instantaneous, rather than copying the data to an external table, so the data is always up to date.

This article shows in one example how to use the MRT table to provide NOSQL and SQL concurrent access for C-tree data in real time. The goal is for these application developers and application administrators, who are looking for a simple mechanism for c-treeace data, or providing SQL access to applications that can easily migrate to c-treeace.

C-treeace

Before delving into the details, let's briefly introduce the faircom c-treeace database, C-treeace is a high-performance nosql and SQL database engine that provides many traditional database concepts at the NoSQL API level, For example, all ACID properties provided by transaction processing, roll forward, rollback, record and key-level locks, deadlock detection, hot backup, automatic disaster recovery, and so on. Because the basic concepts of these data are implemented at the NoSQL level, C-treeace enables concurrent access to the same data file from a wide variety of nosql-oriented API and relational APIs.

The following illustration shows the APIs and frameworks supported by C-treeace:

The Figure 2-nosql API includes the ISAM API, a local Java implementation named Jtdb on ISAM, and a named C-treedb for. NET implementation of the. NET on ISAM and several others. The SQL API includes SQL, JDBC, ODBC, PHP, and ADO. NET and so on.

As we'll explore below, the ability to access data from NoSQL and SQL provides benefits for access to data that is not stored in relational formats. In this article, we will use standard SQL commands to access data and demonstrate how to use SQL to access non relational data. In practice, we can access this data with any application that uses industrial standard SQL. This also provides an opportunity to optimize data access to improve performance relative to the data engine that supports only SQL, but this is the subject of another article.

The sample code provided in this article uses the NoSQL c-treedb C API, the API provides a higher level on top of the ISAM API, saving programmers time and effort by providing a routine for Buffering management, session creation, combining application data and index files into a single database. After more than more than 30 years of effort, FairCom has provided such low-level database technologies for vertical market applications, enterprise-wide applications, and global-wide embedded processing. C-treeace is an engine-level solution for high-speed data indexing and extraction. The technology is used in a variety of things from the harsh Visa credit card transaction processing to NASA's Outer space Applications.