Towards Automated Schema Optimization?

Abstract

Non-relational systems are essential to manage large amounts of semi- and/or unstructured data. To use the optimal data storage at a given time, it may be necessary to change the data model during the lifetime of an application. This paper o ers a visionary approach providing an automated schema migration and optimization between di erent NoSQL data stores. By means of data and query analyses, optimizations of all existing cardinalities can be achieved with respect to good query performances with minimal redundancy. First performance measurements prove the increase in performance.

Keywords

• Schema Migration
• Schema Optimization
• Data Migration

Introduction

Due to the steady development and appearance of new database technologies as well as the further development of existing applications and the consequential changed requirements to a database system used at a given time, it can be necessary to migrate data between di erent, heterogeneous database systems. If the data of the source system are migrated without optimizing the schema with respect to the target system, performance may be a ected.

For example, missing or restricted join operations in NoSQL systems lead to performance losses. Otherwise changes in the database schema (e.g. embedding) can help to improve the performance.

This paper introduces the concept of a exible migration architecture between di erent database systems with various data models. Our approach includes an automated optimization process based on data metrics and query analysis that allows the identi cation of the best way to embed or reference the data.

At rst a migration from relational to document stores is presented. This approach uses an automatic optimization process where the transformation rules can be modi ed and extended. The contributions of the paper are: { A comparison of existing approaches to migrate schema and data between di erent database models. { The proof of concept of a exible migration architecture with an automated optimization of the schema transformation. { First performance analyses to investigate the increase in performance through optimization.

This paper is organized as follows: Section 2 presents related work on migration and optimization processes between di erent database systems. Section 3 describes rst approaches to a concept on the way to an automated and exible migration process. Section 4 shows performance improvements based on rst measurements regarding a migration from the relational database system MariaDB to the document-oriented system of MongoDB. Finally, Section 5 provides the conclusions as well as an outlook on our further work. 2

Related Work

This chapter presents the state of the art on migration and optimization processes between di erent database systems. After a short introduction of the di erent approaches a summarizing comparison is given at the end of the chapter (cf. Table 1).

[ 10 ] describes an algorithm that outputs the order in which related tables can be aggregated into one large \NoSQL-table" with regard to the optimization of read queries in a migration from relational to document stores. [ 1 ] presents a process that takes conceptual UML class diagrams and transforms them into physical models of di erent NoSQL stores using transformation rules and a platform independent meta model. [ 2 ] describes a semi-autonomous rule-based process by which a conceptional UML class diagram can be transformed into various platform speci c models. The class diagram can be edited manually in order to divide it into multiple regions which describe several physically separated models. [ 6 ] shows a concept of data and schema migration from relational to document and column-oriented NoSQL stores that has exible optimization possibilities. The core is a platform independent meta model that additionally describes data and query characteristics of the source system. Di erent strategies are provided to optimize the model with respect to di erent target systems. [ 3 ] describes the migration of data from relational systems to the document-oriented model of MongoDB in detail. This includes a semi-autonomous optimization process against the target model. Note that many-to-many relationships are always referenced. In [ 8 ] an optimization process is described where several target models based on transformation heuristics are generated.

Finally, Table 1 summarizes the works considered and o ers a comparison between di erent properties regarding automated data and schema migration including optimization processes.

[ 3 ] is one of the few works that o ers a detailed description of the migration process including schema optimizations. However, they do not provide optimizations for many-to-many relationships. Therefore, our approach provides exible and extensible transformation rules regarding di erent target models. We perform an in-depth analysis of performance critical queries by using multiple quantitative metrics of existing data. One-into-many and many-into-many embeddings are also considered. 3

Concept

Our concept is based on the use of a platform independent meta model. We o er a exible and extensible process for an automated schema and data migration between all kinds of data stores. A further important goal is the automated optimization of the schema with respect to the target system using di erent strategy rules as presented in [ 6 ] as well as the previously collected data metrics and analysis of the queries in the form of a so-called dependency graph. Fig. 1 shows an overview of the design described.

At rst an automatic migration and schema optimization, from relational to document-oriented stores is considered. The optimization of the schema is achieved through de-normalization, i.e. embedding certain relationships. In order to determine the best direction of embedding among many-to-many relationships as well as the rule-based decision process whether to embed or reference, further data metrics must be considered, compared to [ 3 ]. Based on these metrics as well as the cardinalities of concerning relationships, the dependency graph is generated from the queries. This has the advantage that all previously analyzed queries can be involved in a later optimization step concerning target system speci c rules.

To provide a high degree of exibility and extensibility, the strategy rules can be described on two levels following the concept from [ 6 ]. On the one hand, those that are applicable to certain data models, such as the document model and { on the other hand rules { for speci c systems like MongoDB.

Schema extraction and meta model: The step of extracting the physical data structure from the source database to a platform independent meta model is an important base for the migration architecture [ 4, 5, 8 ]. The description or

Queries src DB

Strategy

Rules

Meta Model

(Schema)

Data

Metrics

Dependency

Graph

Optimized

Schema target DB

development of a suitable meta model would, however, go beyond the scope of this paper.

Data metrics and query analysis: An important point of the schema optimization are the metrics that are collected in the form of a data analysis, referred to the source database. The following metrics are used here: { avgEntitySize: Average size of entities within an entity type. { entityCount: Number of entities within an entity type. { rCountfMin,Max,Avgg: Concerning the relationships between two entity types, the minimal, maximal and average number of \connections". The goal of the analysis of queries is to generate the dependency graph to show the dependencies between the entity types involved in the queries. Algorithm 1 illustrates the process for generating this graph. Note that currently no queries can be optimized that have joins over more than two entity types while at the same time more than one entity type is a ected by lter operations.

Algorithm 1: Generation of the dependency graph.

Input : Set of queries Q; Data metrics M ; Schema S 0utput : Set of digraphs G; dependency graph DG Strategy rules: The structure of the physically target model is nally de ned by rules related to speci c target systems. These make use of the data metrics, user de ned thresholds of those metrics, the cardinalities of the relationships, and the dependency graph to describe the transformations of the schema for an optimal performance. In the following the early approaches of transformation rules for a migration to the document-oriented model of MongoDB are shown: { R1 (doc): 8 relationships r 2 S : r 2= DG ! REF { R2 (doc): 8 relationships r 2 S : r 2 DG ! EMBED { R3 (mongo): 8 edges 2 DG : direction is one-into-x ! EMBED { R4 (mongo): 8 edges 2 DG : direction is many-into-x ! if mCond = true EMBED else ! REF with mCond : (rCountMax(ej) < rCountMaxthold) ^ (rCountMax(ej) avgEntitySize(ej) < rCountMaxthold avgEntitySizethold) Note that the database system speci c rules R3 and R4 are preferred to the general rule R2. 4

First Evaluations

First measurements were performed to prove an increase in performance with the example of a migration from MariaDB to MongoDB. Therefore, a part of the data model of the multi-model benchmark UniBench [ 9 ] and the three provided data sets1 with di erent scale factors (SF1, SF10 and SF30 ) were used. As a rst example, optimizations have been made for following SQL query: SELECT c.firstName, c.lastName, f.feedback FROM customer c, feedback f, product p WHERE c.id = f.customerId AND f.asin = p.asin AND p.title = Katadyn TRK Drip Ceradyn... ; 1 https://github.com/HY-UDBMS/UniBench/releases 102 ) g o l, sm101 ( e m i

T100 a b c

SF1 a b c

SF10

Conclusion and Outlook

This paper presents a visionary approach to a exible and extensible concept towards schema and data migration between di erent heterogeneous database systems, including automated optimization processes. We rst describe strategy rules using an example for a migration from relational to document-oriented stores. In contrast to previous work like [ 3 ], the optimization process presented in this paper considers one-into-many and many-into-many embeddings.

The rst measurements taken show a performance improvement in comparison to a non-optimized migration.

Our future work aims at the extension and in-depth evaluation of our automated schema optimization approach to all NoSQL data models, including multi-model systems [ 7 ].

References

1. Abdelhedi , F. , et al.: UMLtoNoSQL: Automatic transformation of conceptual schema to NoSQL databases . In: AICCSA'17 . IEEE ( 2017 )

2. Daniel , G., Gomez , A. , Cabot , J.: UMLto[no] SQL: Mapping conceptual schemas to heterogeneous datastores . In: RCIS'19 . IEEE ( 2019 )

3. Jia , T. , Zhao , X. , Wang , Z. , Gong , D. , Ding , G.: Model Transformation and Data Migration from Relational Database to MongoDB . In: Big Data' 16 . IEEE ( 2016 )

4. Klettke , M. , Storl, U., Scherzinger , S. : Schema Extraction and Structural Outlier Detection for JSON-based NoSQL Data Stores . In: BTW' 15 . GI ( 2015 )

5. Klettke , M. , Storl, U., Shenavai , M. , Scherzinger , S.: NoSQL schema evolution and big data migration at scale . In: SCDM'16 . IEEE ( 2016 )

6. Liang , D. , Lin , Y. , Ding , G.: Mid-model Design Used in Model Transition and Data Migration between Relational Databases and NoSQL Databases . In: SmartCity' 15 . IEEE ( 2015 )

7. Lu , J. , Holubova , I. : Multi-model Databases: A New Journey to Handle the Variety of Data . ACM Comput. Surv . ( 2019 )

8. Mali , J. , Atigui , F. , Azough , A. , Travers , N.: ModelDrivenGuide: An Approach for Implementing NoSQL Schemas . In: DEXA'20 . Springer ( 2020 )

9. Zhang , C. , Lu , J.: Holistic evaluation in multi-model databases benchmarking . Distributed and Parallel Databases ( 2019 )

10. Zhao , G. , Lin , Q. , Li , L. , Li , Z. : Schema Conversion Model of SQL Database to NoSQL . In: P2P, Parallel, Grid, Cloud and Internet Computing' 14 . IEEE ( 2014 )