Nowadays data using by organizations in different business areas are very heterogeneous. This raises the issue of data integration. Two main classes of data models used for data representation can be distinguished: relational (SQL) and non-relational (NoSQL) data models. Data models of the classes differ a lot, for instance, relational models are applied for structured data, and NoSQL models mostly applied for semi-structured data. For the issues of data integration it is required to find a model that can unify relational and NoSQL models. A candidate for such unifying data model is Language Integrated Query - LINQ. The aim of this work is to validate that LINQ can successfully serve as unifying data model in data integration systems intended to integrate both relational and NoSQL data sources.
Currently, there is an exponential increase of the volume of experimentally obtained data in science and industry. The data can be obtained from various sources. For example, researchers can get scientific data from sensors during the experiments. Credit banks capture transactions each borrower and generate a credit history, that can be used in advance. The list of users and personal information, feedbacks and stories in social networks are also a data that continuously increase volume. The number of organizations that get the emerged data in different areas is also large. Data that is stored in various sources like web log files, web pages, documents, etc. possess different levels of structurization: structured, semi-structured, and unstructured data. Data in sources can be presented in various data models. Two large classes of data models can be distinguished: relational (mostly SQL) and NoSQL data models. NoSQL is a group of models with flexible schemas that can be further classified in four main categories: keyvalue, column-oriented, document and graph data models. These models are intended to represent semi-structured data or even schema-less data. NoSQL models are implemented in respective database management systems. To deal with heterogeneity of data the respective data integration methods and tools are required.
Frequently, the data sources are scattered and getting them directly from sources and
processing is quite problematic. That data can be localized in data lakes [
Even if the problem of localization of the initial data is solved, the problem of data integration from different sources remains urgent. Data integration requires the following conceptual specifications: a global schema and mappings binding the global schema and source schemas. The global schema is the integrated schema serving as a unified representation of schemas of participating data sources. Data integration methods are implemented within data integration systems (DIS).
Two kinds of data integration can be distinguished: materialized and virtual
integration. Materialized data integration usually proceeds within data warehouses [
Virtual DISs developed for concrete subject areas are called subject mediators
(mediators, in short). To have access to “fresh” information, a virtual integration system is
preferred to a warehouse since it avoids having to propagate updates of the data source
to the warehouse. The process of answering user queries in virtual DIS is performed as
follows [
• each subquery is passed to a specific wrapper of the relevant data source to be executed there;
• answers returned from the wrappers are collected, combined and returned to the user.
Three main techniques for definition of conceptual mappings between global and
local schemas for virtual integration are known: Global-as-View (GAV) [
The canonical model plays a role of a unifying model, in which the source data models can be represented without loss of information [25].
Various kinds of data models with different semantics are used as canonical models:
relational models [
To achieve the goal, the following problems have to be solved: (i) selection of concrete data models to be unified in LINQ; (ii) implementation of DIS prototype based on the Global-As-View approach and LINQ as the canonical data model, and (iii) evaluation of DIS via use case in some subject area. In this paper the related work is shortly overviewed and the current progress of DIS prototype implementation is reported and illustrated by a use case.
The remainder of this paper is structured as follows. In Section 2 related work is discussed. The phases of the DIS prototype development and description of its components are shown in Section 3. The initial steps on evaluation of the DIS prototype by a use case are described in Section 4. Finally, Section 5 draws conclusions and points out future work. 2
During recent years quite a number of different systems have been developed to implement virtual data integration. Various canonical models and conceptual specification approaches were applied.
In 2000 the Agora [
In [
A virtual data integration approach is also used in [
In [
In [
In [
In fact, none of known existing DISs support the integration of the whole range of
the NoSQL resources. However, with Mejer's research in [
Validation that LINQ can be used as a canonical data model consists of three steps:
For validation LINQ as a canonical data model there were chosen five heterogeneous
data models: SQLite as a relational model and four NoSQL data models. It is worth to
admit that from a set of various NoSQL model classes the four main classes with
different storage structure and data manipulation facilities were chosen. These classes are
key-value, column-oriented, document and graph data models:
• Key-Value Model. The simplest model in representation is a key-value model. The
representation consists of composition of a key and a value. A key-value NoSQL
implemented systems allow either simple data types (e.g., numerals and strings) or
the use of lists and sets of values of simple types. The databases based on this model
do not support complex queries to be performed on the data stored in database, but
only the search keys. The relationships in terms of reference keys and provides no
referential integrity constraint are not supported in the key-value model [
The following DBMSs are chosen to be integrated in this work: SQLite1 database based on a relational model, Accumulo2 as a key-value store, HBase3 as a column store, MongoDB4 database based on document model and Neo4j5 graph database.
Mentioned databases are planned to be integrated within a DIS prototype. The prototype should contain three main components: query rewriting component, DBMS wrappers supporting selected data models, and data merging component. The whole architecture that is planned to be realized is shown in Fig. 1.
Query rewriting is the first phase of DIS prototype operation. A query posed by a
user should be rewritten into five subqueries [
Application and evaluation of LINQ as a canonical data model is illustrated in this section with a use case and consist of the following steps: • a use case subject domain selection; • definition of the global schema and a set of analytical queries that should illustrate the main LINQ constructs; • selection of data sources to be deployed into source DBMSs; • definition of conceptual mappings (based on the Global-as-View approach), that link the local sources’ schemas and the global schema; • illustration of query rewriting and data merging processes.
As the domain for the use case the urban statistics were chosen including transports, demography, environment, immigration, etc. The examples of the subject domain entities and their attributes for transports and demography are illustrated in Table 1.
Various regional agencies or ministries can use the regional urban statistics to analyze the indicators and metrics. The indicators can be used for creating the development programs to improve the life quality in the region.
After selection of the subject domain, the global schema for the domain should be defined. The global schema is used for aggregating all data obtained from heterogeneous data sources.
The global schema should be defined using the canonical model. Since the LINQ is a constituent of the C# standard, the entities (classes) and attributes should be defined using C#. An example for the definition of the classes births and deaths is shown in Table 2.
Demography births deaths population unemployment immigration_by_nationality most_frequent_names most_frequent_baby_names Transports accidents bus_stops transports year, district_code, district_name, neighborhood_code, neighborhood_name, gender, number year, district_code, district_name, neighborhood_code, neighborhood_name, age, number year, district_code, district_name, neighborhood_code, neighborhood_name, gender, age, number year, month, gender, demand_occupation, number year, district_code, nationality, number order, name, gender, decade, frequency order, name, gender, year, frequency id, district_name, neighborhood_name, street, weekday, month, day, hour, part_of_the_day, mild_injuries, serious_injuries, victims, vehicles_involved, longitude, latitude code, transport, longitude, latitude, bus_stop, district_name, neighborhood_name code, transport, longitude, latitude, station, district_name, neighborhood_name
In order to validate that LINQ can be used as a canonical data model, it is necessary to define a certain set of queries that covers the capabilities of the LINQ language. As an example, consider a LINQ query that contains filter (where clause), join operation, aggregation functions sum, sub-queries and mathematical operation in select clause: var demogr = from b in births join d in deaths on b.year equals d.year where b.year<=2017 select new {y = b.year, brt = b.sum(n=>n.number), dth = d.sum(n=>n.number), diff = b.sum(n=>n.number) - d.sum(n=>n.number), city = b.city};
This query should return the annual number of births and deaths from two separated classes (both classes should be joined) and the difference between births and deaths (rate of natural increase). The example uses births and deaths classes as data sources.
This paper considers only a fragment of a global schema that should be used in analytical queries. A definition of the whole global schema is a future work.
The raw data were extracted from the open data banks or previously developed databases. Data on events and statistics in Barcelona8 are stored in SQLite relational database. In Neo4j (graph DBMS) a database with the events and statistics in Madrid9 was created, the data on Bilbao10 is going to be created in MongoDB (document DBMS). Data and statistics on Malaga11 are going to be presented in HBase (column store), data on Seville12 are going to be stored in Accumulo (key-value store).
As the query from Section 4.2 requires births and deaths entities, the parts of diagrams from relational and graph databases are illustrated in Fig. 2. The postfix _rel means that the entity is from relational database. Postfix _gr denotes entities from graph database.
On the left side of the Fig. 2 a part of the relational schema is presented: births_rel and deaths_rel entities. Each entity has a list of attributes that have explicit conducted data types. On the right side a visualization of the graph database is presented. The blue nodes are marked with births_gr label and orange nodes are marked with deaths_gr label. Since Neo4j does not show all labels of the nodes, there were shown only one label for each node. Other stored data of each label are hidden. In blue nodes the gender labels are shown, and in orange nodes the age category of deaths is pointed (the age label). Blue and orange nodes are linked by brt_dth relationships if year, district_name and neighborhood_name attribute values of births_gr and deaths_gr entities are equal.
9 https://datos.madrid.es/portal/site/egob/ 10 https://www.bilbao.eus/opendata/es/formatos 11 https://datosabiertos.malaga.eu/dataset 12 http://sevilla-idesevilla.opendata.arcgis.com/datasets The local schema definitions for document, column and key-value models are planned for the future work.
To define the conceptual mappings, the correspondences of entities and attributes
between local and global schemas should be established first. The examples of
correspondences between local (relational) and global schema elements are illustrated in
Fig. 3.
According to GAV approach [
For example, the conceptual mappings between births and deaths entities in global schema and births_rel and deaths_rel entities in local schema (SQL model) can be defined in the following way: var births = from b in births_rel select new {year = b.Year, district_name = b.District_Name, neighborhood_name = b.Neighborhood_Name, gender = b.Gender, num = b.Number, city = “Barcelona”}; var deaths = from d in deaths_rel select new {year = d.Year, district_name = d.District_Name, neighborhood_name = d.Neighborhood_Name, age = d.Age, num = d.Number, city = “Barcelona”};
City is not the attribute of any local source and its value is generated within views. The value of city attribute is a constant that identifies the data source – the city to which the extracted data refer. As it is described earlier, the database based on relational model stores data on Barcelona.
Conceptual mappings for the graph database look almost the same, the only difference is that births_gr and deaths_gr entities are used instead of births_rel and deaths_rel.
Conceptual mappings can be constructed manually by an expert or in semi-automated way. This paper discusses a simple example in which conflict situations do not arise during construction of conceptual mappings. In general, various types of conflicts such as data type mismatch or structural conflicts requiring combination of several attributes of a local schemas into an attribute of the global schema can occur. Dealing with conceptual mappings in case of conflicts is a future work.
Query Rewriting. First, the query from Section 4.2 should be rewritten. The query
rewriting is processed using the views from Section 4.4 on the basis the GAV approach
[
The rewritten query for the graph database looks almost the same. The only difference is that in the rewritten query for the graph database that the value of the city attribute is “Madrid” instead of “Barcelona”.
Query Transformation. To extract the data from local sources rewritten queries should be transformed into the source query languages. For instance, the rewritten LINQ query should be transformed in relational data model wrapper as follows: SELECT b.year as y, sum(b.num) as brt, sum(d.num) as dth,
sum(b.num) - sum(d.num) as diff, b.city FROM
(SELECT b.Year as year, b.District_Name as district_name, b.Neighborhood_Name as neighborhood_name, b.Gender as gender, b.Number as num, “Barcelona” as city FROM births_rel b) b
INNER JOIN (SELECT d.Year as year, d.District_Name as district_name, d.Neighborhood_Name as neighborhood_name, d.Age as age, d.Number as num, “Barcelona” as city FROM deaths_rel d) d ON b.year = d.year WHERE b.year <=2017 GROUP BY b.year, b.city
Considering that the attribute value city = “Madrid” in the graph data source, the rewritten LINQ query is transformed into Cypher graph query language (supported by Neo4j) as follows:
MATCH (b:births_gr)
WITH [{year: b.Year, district_name: b.District_Name, neighborhood_name: b.Neighborhood_Name, gender: b.Gender, num: b.Number, city: "Madrid"}] as b UNWIND b as b MATCH (d:deaths_gr)
WITH [{year: d.Year, district_name: d.District_Name, neighborhood_name: d.Neighborhood_Name, age: d.Age, num: d.Number, city: "Madrid"}] as d UNWIND d as d MATCH (b)--(d)
RETURN b.year as y, sum(b.num) as brt, sum(d.num) as dth, sum(b.num)sum(d.num) as diff, b.city Data Merging. Finally, the results extracted from sources should be merged in data merging component. For instance, the query to the relational database returns the result that is shown on Fig. 4(a), the query to the graph database returns the result that is shown on Fig. 4 (b). The result presented to a user in this case is just a merge of 4(a) and 4(b) results. a) 5 b)
This paper presents an approach for validation that Language Integrated Query (LINQ) is able to serve as a canonical data model for virtual data integration in the world of SQL and NoSQL databases. The architecture of DIS to validate LINQ as a canonical model is presented. A use case intended to illustrate key steps of data integration is provided. The subject domain of the use case is defined, conceptual mappings between source schemas and the global schema are illustrated as well as query rewriting and transformation for the relational and Neo4j data models.
Future work includes development of wrappers for chosen NoSQL models as well as query rewriting and data merging components of the prototype DIS. Complete use case description including global and local schemas and their conceptual mappings accompanied by a set of comprehensive analytical queries and their results is also a future work.
This work is supervised by Sergey Stupnikov, lead research scientist at the Federal Research Center “Computer Science and Control” of Russian Academy of Sciences. system. In: Chen, Q., Huan, J., Xu, Y., Zhang, T., Wang, L. (eds.) 5th International Conference on Biomedical Engineering and Informatics (BMEI 2012), pp. 1270–1274. IEEE, Chongqing, China (2012). 25. Zakharov, V.N., Kalinichenko, L.A., Sokolov, I.A., and Stupnikov, S.A.: Development of canonical information models for integrated information systems. Informatics and Its Applications 1 (2), 15–38 (2007). 26. Zhang, Y., Wang, X., Lai, S., He, S., Liu, K., Zhao, J., and Lv, X.: Ontology matching with word embeddings. In: Chinese Computational Linguistics and Natural Language Processing Based on Naturally Annotated Big Data, 34–45 (2014).