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

An evaluation of the performance of a NoSQL document database in a simulation of a large scale Electronic Health Record (EHR) system

Electronic Healthcare Record (EHR) systems can provide significant benefits by improving the effectiveness of healthcare systems. Research and industry projects focusing on storing healthcare information in NoSQL databases has been triggered by practical experience demonstrating that a relational database approach to managing healthcare records has become a bottleneck. Previous studies show that NoSQL databases based on consistency, availability and partition tolerance (CAP) theorem have significant advantages over relational databases such as easy and automatic scaling, better performance and high availability. However, there is limited empirical research that has evaluated the suitability of NoSQL databases for managing EHRs. This research addressed this identified research problem and gap in the literature by investigating the following general research: How can a simulation of a large EHR system be developed so that the performance of NoSQL document databases comparative to relational databases can be evaluated? Using a Design Science approach informed by a pragmatic worldview, a number of IT artefacts were developed to enable an evaluation of performance of a NoSQL document oriented database comparative to a relational database in a simulation of a large scale EHR system. These were healthcare data models (NoSQL document database, relational database) for the Australian Healthcare context, a random healthcare data generator and a prototype EHR system. The performance of a NoSQL document database (Couchbase) was evaluated comparative to a relational database (MySQL) in terms database operations (insert, update, delete of EHRs), scalability, EHR sharing and data analysis (complex querying) capabilities in a simulation of a large scale EHR system, constructed in the cloud environment of Amazon Web Services (AWS). Test scenarios consisted of a number of different configurations ranging from 1, 2, 4, 8 and 16 nodes for 1Million, 10 Million, 100 Million and 500 Million records to simulate database operations in a large scale and distributed EHR system environment. The Couchbase NoSQL document database was found to perform significantly better than the MySQL relational database in most of the test cases in terms of database operations -insert, update, delete of EHRs, scalability and EHR sharing. However, the MySQL relational database was found to perform significantly better than the Couchbase NoSQL document database for the complex query test that demonstrates basic analysis capabilities. Furthermore, the Couchbase NoSQL document database used significantly more disk space than the MySQL relational database to store the same number of EHRs. This research made a number of important contributions to knowledge, theory and practice. The main theoretical contribution to design theory was the design and evaluation of a prototype EHR system for simulating database management operations in a large scale EHR system environment. The prototype EHR system was underpinned by the development of two data models with data structures designed for a NoSQL document database and a relational database and a random healthcare data generator which were based on Australian Healthcare data characteristics and statistics. The design of a data model for EHRs for a NoSQL document database using an aggregated document modelling approach provided an important contribution to data modelling theory for NoSQL document databases using de-normalisation and document aggregation. The design of a random healthcare data generator was another important contribution to design theory and was based on a data distribution algorithm (multinomial distribution and probability theory) informed by National Health Data Dictionary and published Australian Healthcare statistics. The prototype EHR system allowed this study to demonstrate through a simulated performance evaluation that a NoSQL document database has significant and proven performance advantages over relational databases in most of the database management test cases. Hence this study demonstrated the utility and efficacy of a NoSQL document database in the simulation of a large scale EHR system. This research has made a number of important contributions to practice. Foremost is that the IT artefacts (namely, a data model for storing EHRs in a NoSQL document database, a random healthcare data generator and a prototype EHR system) developed and evaluated in this research can be readily adopted by practitioners. Another important practical contribution of this research is that it is based on the open source availability of NoSQL database and relational database alternatives. Hence, this research can provide a sound basis for lower-income countries as well higher-income countries to establish their own cost-effective national EHR systems without the restrictions, limitations, complexity or complications of similar proprietary relational database systems