Migrating From SQL to NoSQL Database: Practices and Analysis

Most of the enterprises that are dealing with big data are moving towards using NoSQL data structures to represent data. Converting existing SQL structures to NoSQL structure is a very important task where we should guarantee both better Performance and accurate data. The main objective of this thesis is to highlight the most suitable NoSQL structure to migrate from relational Database in terms of high performance in reading data. Different combinations of NoSQL structures have been tested and compared with SQL structure to be able to conclude the best design to use.For SQL structure, we used the MySQL data that is stored in five tables with different types of relationships among them. For NoSQL, we implemented three different MongoDB structures. We considered combinations of different levels of embedding documents and reference relationships between documents. Our experiments showed that using a mix of one level embedded document with a reference relationship with another document is the best structure to choose. We have used a database that contains five tables with a variety of relationships many-to-one, and many-to-many. Also the huge amount of data stored in all the structures about 2 millions record/document. The research compares clearly between the performances of retrieving data from different MongDB representation of data and the result shows that in some cases using more than one collection to represent huge data with complex relationships is better than keeping all the data in one document

Extracting a Relational Database Schema from a Document Database

As NoSQL databases become increasingly used, more methodologies emerge for migrating from relational databases to NoSQL databases. Meanwhile, there is a lack of methodologies that assist in migration in the opposite direction, from NoSQL to relational. As software is being iterated upon, use cases may change. A system which was originally developed with a NoSQL database may accrue needs which require Atomic, Consistency, Isolation, and Durability (ACID) features that NoSQL systems lack, such as consistency across nodes or consistency across re-used domain objects. Shifting requirements could result in the system being changed to utilize a relational database. While there are some tools available to transfer data between an existing document database and existing relational database, there has been no work for automatically generating the relational database based upon the data already in the NoSQL system. Not taking the existing data into account can lead to inconsistencies during data migration. This thesis describes a methodology to automatically generate a relational database schema from the implicit schema of a document database. This thesis also includes details of how the methodology is implemented, and what could be enhanced in future works

Data schema does matter, even in NoSQL systems!

International audienceA Schema-less NoSQL system refers to solutions where users do not declare a database schema and, in fact, its management is moved to the application code. This paper presents a study that allows us to evaluate, to some extent, the data structuring impact. The decision of how to structure data in semi-structured databases has an enormous impact on data size, query performance and readability of the code, which influences software debugging and maintainability. This paper presents an experiment performed using MongoDB along with several alternatives of data structuring and a set of queries having increasing complexity. This paper introduces an analysis regarding the findings of such an experiment

Uma abordagem baseada em métricas para explorar alternativas de esquemas de dados no processo de conversão de RDB para NoSQL

Orientadora: Profa. Dra. Leticia Mara PeresCoorientador: Prof. Dr. Marcos Didonet Del FabroTese (doutorado) - Universidade Federal do Paraná, Setor de Ciências Exatas, Programa de Pós-Graduação em Informática. Defesa : Curitiba, 21/10/2020Inclui referências: p. 102-105Área de concentração: Ciência da ComputaçãoResumo: Com o surgimento de novas aplicações surgiram também novos requisitos sobre os sistemas de armazenamento. Cenários envolvendo dados estruturados, semiestruturados e não-estruturados são cada vez mais comuns. Os bancos de dados relacionais (RDB, do inglês Relational Database), amplamente usados para armazenar dados de diversas aplicações, já não atendem de forma adequada todas as questões impostas pelos diferentes cenários. Como alternativa surgiram os bancos de dados NoSQL (do inglês, Not only SQL), flexíveis em relação ao modelo de dados e projetados para fornecer alta escalabilidade e disponibilidade. Bancos de dados relacionais e bancos de dados NoSQL coexistirão por longo período de tempo e, como consequência, novas abordagens para converter o modelo relacional para modelos de dados NoSQL foram propostas. No entanto, a maioria dessas abordagens se destina a conversão de dados relacionais para um modelo de dados NoSQL específico e fornecem pouco suporte para customizações do processo de conversão, como seleção de campos, tabelas, instâncias e outros aspectos relativos à customização do esquema de dados produzido. Além disso, há diversas formas de estruturar os dados (ou definir esquemas de dados) ao converter RDB para NoSQL. A escolha do esquema de dados adequado não é trivial e envolve vários aspectos, como o padrão de acesso aos dados, o nível de redundância de dados desejado, o tamanho do banco de dados NoSQL resultante, o esforço de manutenção da aplicação, dentre outros. Nesta tese é definida uma abordagem para converter e migrar dados relacionais para bases NoSQL orientadas a documentos e família de colunas, composta por uma etapa de avaliação de esquemas NoSQL candidatos. A abordagem usa grafos acíclicos direcionados (DAG, do inglês Directed Acyclic Graph) para especificar a estrutura das entidades que serão migradas para o modelo de dados NoSQL e, também, para representar o padrão de acesso da aplicação (consultas). Para avaliar a abordagem foram realizados experimentos envolvendo cenários de conversão de RDB para NoSQL compostos por diferentes esquemas NoSQL candidatos. Os resultados dos experimentos mostraram que a abordagem é eficaz para identificar cenários em que há maior esforço de implementação das consultas, auxiliando o usuário no processo de seleção de esquemas NoSQL, antes de migrar de dados. Palavras-chave: Transformação de dados. Bancos de dados relacionais. Bancos de dados NoSQL. Conversão de bancos de dados. Métricas. Avaliação.Abstract: With the emergence of new applications, new requirements on storage systems have also emerged. Scenarios involving structured, semi-structured and unstructured data are increasingly common. Relational databases, widely used to store data from different applications, no longer adequately address all issues imposed by different scenarios. As an alternative, NoSQL databases have emerged, which are flexible in relation to the data model and designed to provide high scalability and availability. Relational databases and NoSQL databases will coexist for a long period of time and, as a consequence, new approaches to converting the relational model to NoSQL data models have been proposed. However, most of these approaches are aimed at converting relational data to a specific NoSQL data model and provide little support for customizing the conversion process, such as selection of fields, tables, instances, and other aspects related to the customization of the data schema produced. In addition, there are several ways to structure the data (or ways to define data schemas) when converting RDB to NoSQL. The choice of the appropriate data schema is not trivial and involves several aspects, such as the data access pattern, the desired level of data redundancy, the size of the resulting NoSQL database, the application maintenance effort, among others. This thesis defines an approach to convert and migrate relational data to document-oriented and column family NoSQL models, composed of an evaluation step of candidate NoSQL schemas. The approach uses directed acyclic graphs (DAG) to specify the structure of the entities that will be migrated to the NoSQL data model and also to represent the application's access pattern (queries). To evaluate candidate schemas, a set of metrics and scores was defined, which aims to measure the coverage of the NoSQL schema in relation to the set of queries. As NoSQL schema and query are defined through DAGs, it is possible to perform evaluations and comparisons objectively. To evaluate the approach, we performed experiments involving RDB to NoSQL conversion scenarios composed by different candidate NoSQL schemas. The results of the experiments showed that the approach is effective to identify scenarios in which there is a greater effort to implement the queries, assisting the user in the process of selecting NoSQL schemas, before executing the data migration. Keywords: Data transformation. Relational databases. NoSQL databases. Database conversion. Metrics. Evaluation

Physical Design for Non-relational Data Systems

Decades of research have gone into the optimization of physical designs, query execution, and related tools for relational databases. These techniques and tools make it possible for non-expert users to make effective use of relational database management systems. However, the drive for flexible data models and increased scalability has spawned a new generation of data management systems which largely eschew the relational model. These include systems such as NoSQL databases and distributed analytics frameworks such as Apache Spark which make use of a diverse set of data models. Optimization techniques and tools developed for relational data do not directly apply in this setting. This leaves developers making use of these systems with the need to become intimately familiar with system details to obtain good performance. We present techniques and tools for physical design for non-relational data systems. We explore two settings: NoSQL database systems and distributed analytics frameworks. While NoSQL databases often avoid explicit schema definitions, many choices on how to structure data remain. These choices can have a significant impact on application performance. The data structuring process normally requires expert knowledge of the underlying database. We present the NoSQL Schema Evaluator (NoSE). Given a target workload, NoSE provides an optimized physical design for NoSQL database applications which compares favourably to schemas designed by expert users. To enable existing applications to benefit from conceptual modeling, we also present an algorithm to recover a logical model from a denormalized database instance. Our second setting is distributed analytics frameworks such as Apache Spark. As is the case for NoSQL databases, expert knowledge of Spark is often required to construct efficient data pipelines. In NoSQL systems, a key challenge is how to structure stored data, while in Spark, a key challenge is how to cache intermediate results. We examine a particularly common scenario in Spark which involves performing iterative analysis on an input dataset. We show that jobs written in an intuitive manner using existing Spark APIs can have poor performance. We propose ReSpark, which automates caching decisions for iterative Spark analyses. Like NoSE, ReSpark makes it possible for non-expert users to obtain good performance from a non-relational data system

De l'importance d'un schéma de données dans les systèmes NoSQL(Data schema does matter, even in NoSQL Systems!)

International audienceArticle accepté et présenté à la conférence internationale RCIS, co-localisée avec INFORSID 2016 à Grenoble. La version longue de l'article, en anglais, est disponible dans les actes de RCIS. RESUME. La plupart de systèmes NoSQL n'utilisent pas de schéma de bases de données. Ces systèmes n’offrent donc pas de fonctions de gestion de schéma et la definition et la gestion des structures de données utilisées reviennent à l'application. Les données peuvent être semi-structurées et l'utilisateur a de nombreuses possibilités de structuration