Extract of drug document oriented database 

Contexts in source publication

Context 1

... general, data integration and data exchange solutions adopt the certain answers semantics for query answering, i.e. results of a query expressed over the target contain the intersection of data retrieved from the sources. We believe that this pessismistic approach is too restrictive and as a consequence, many valid results may be missing from final results. At the other extreme of the query answering semantics spectrum, we find the possible answer semantics which provides as results over a target query the union of sources results. With this optimistic approach conflicting results may be proposed as a final result, leaving the end-users unsatisfied. In this work, we propose a trade-off between these two semantics which is based on a preference-based approach. Intuitively, preferences provided over target attributes define a partial order over mapped sources. Hence, for a given data object, conflicting information on the same attribute among different data sources can be handled efficiently and the final result will contain the preferred values. Example 4: Consider the queries over docDB and colDB asking for lab and price information for the drug identified by value 3295935. Given the information stored in both sources, respectively the column store ( Figure 2) and column family store (Figure 3), conflicts arise on the prices, resp. 1.88 and 2.05 euros, and pharmaceuticals, resp. Pfizer and Wyeth. When creating the mapping assertions, domain experts can express that drug prices are more accurate in the document store ( docDB ) and that information about pharmaceutical laboratory is more trustable in the column family ( colDB ). Hence the result of this query will contain a single tuple consisting of: { Advil, Wyeth, 1.88 } , i.e. mixing values retrieved the different sources. We now define the notion of preferences over mapping assertions. Definition 2: Consider a set of source databases { DB 1 , DB 2 , ..DB n } , a preference relation, denoted , is a relation ⊆ DB i × DB j , with i = j , that is defined on each non primary key attribute of target relations. A preference is total on an attribute A if for every pair DB i , DB j of sources that propose attribute A, either DB i ∗ DB j or DB j ∗ DB i with ∗ the transitive closure of . Example 5: Consider the drug relation in our running example target schema. Its definition according to the preferences proposed in Example 3 are the following: drug(drugId, drugName docDB colDB , lab colDB docDB , That is, for a given drug, in case of conflict, its docDB drugName attribute is preferred to the one proposed by colDB and the preferred value for the lab attribute is colDB over docDB . Note that since the composition attribute can only be retrieved from the colDB source, it is not necessary to define a preference order over this attribute. Once a target relation schema and a set of mapping assertions have been defined, end-users can expressed queries in SQL over the target database. Since that database is virtual, i.e. it does not contain any data, data needs to be retrieved from the sources and processed to provide a final result. The presence of NoSQL databases in the set of sources imposes to transform the former SQL query into a query specifically tailored to each NoSQL source. This transformation is based on the peculiarities of the source database, e.g. whether a declarative query language exists or only procedural approach enables to query that database, and the mapping assertions. Since most NoSQL stores support only a procedural query approach, we have decided to implement a query language to bridge the gap between SQL and some code in a programming language. This section presents the Bridge Query Language (henceforth BQL) which is used internally by our data integration system and the query processing semantics. The overall architecture of query processing within our data integration system is presented in Figure 4. First, an end-user writes an SQL query over the target schema. The expressivity of accepted SQL queries corresponds to Select Project Join (SPJ) conjunctive queries, e.g. GROUP BY clauses are not accepted but we are planning to introduce them in future extensions. Note that this limi- tation is due to a common abstraction of the NoSQL databases we are studying in this paper (column family and document). An end-user SQL query is then translated into the BQL internal query language of our data integration system. This transformation corresponds to a rewritting of the SQL into a BQL query using the mapping assertions. Note that this translation step is not needed for a RDBMS. Then for each BQL, a second transformation is performed, this time to generate a query tailoring the NoSQL database system. Thus, for each supported NoSQL implementation, a set of rules is defined for the translation of a BQL query. Most of the time, the BQL translation takes the form of a program and uses a specific API. In Section 6, we provide details on the translation from BQL to Java programs into MongoDB and Cassandra. The results obtained from each query is later processed within the data integration system. Intuitively, each result set takes the form of a list containing the awaited target columns. In order to detect data conflicts, we need to efficiently identify similar objects. This step is performed by incorporating into the result set values corresponding to primary keys of target relations of the SQL query. So, even if primary keys are not supposed to be displayed in the final query result, they are temporarily stored in the result set. Hence objects returned from the union of the result sets are easily and unambiguously identified. Similar objects can then be analyzed using the preference orders defined over target attributes. The query result contains values retrieved from the preferred source attributes. BQL is the internal query language that bridges the gap between the SQL language of the target model and the different and the heterogenous query languages of the sources. The syntax of the query language follows the EBNF proposed in the companion web site. This language contains a set of reserved words whose semantics is obvious for a programmer. For instance, the get instruction enables to define a set of filter operations and to define the distinguished variables of the query, i.e. the values needed in the result. The foreach in : instruction is frequently encountered in many programming languages and their semantics align. Intuitively, it supports an iteration over elements of a result set and the associated processing is performed after the ’:’ symbol. We have implemented an SQL to BQL translator which parses an SQL query and generates a set of BQL queries, one for each NoSQL database mapped to the relation of the target query. This translator takes into account the mapping assertions defined over the data integration system. Example 6: We now introduce a set of queries expressed over our running example. They correspond to different real case scenario and emphasize the different functionalities of our query language. For each target query (SQL), we present the BQL generated for both colDB and docDB ...

Context 2

... analysis. Like Hive, Pig 13 tries to raise the level of abstraction for processing large data sets with Hadoop’s MapReduce implementation. The Pig platform consists of a high level language called Pig Latin [6] for constructing data pipelines, where operations on an input relation are executed one after the other. These Pig Latin data pipelines are translated into a sequence of MapReduce jobs by a compiler, which is also included in the Pig framework. Cascading 14 is an API for data processing on Hadoop clusters. It is not a new text based query syntax like Pig or another complex system that must be installed and maintained like Hive. Cascading offers a collection of operations like functions, filters and aggregators, which can be wired together into complex, scale-free and fault tolerant data processing workflows as opposed to directly implementing MapReduce algorithms. In contrast to missing standards in a query language for NoSQL databases, standards for persisting java objects already exist. With the Java Persistence API (JPA 15 ) and Java Domain Objects (JDO 16 ) it is possible to map java objects into different databases. The Datanucleus implementation of these two standards provides a mapping layer on top of HBase, BigTable [1], Amazon S3 17 , MongoDB and Cassandra. Googles App Engine 18 uses this framework for persistence. A powerful data query and administration tool which is used extensively within the Oracle community is Quest Softwares Toad 19 . Since 2010, a prototype which also offers its support for column family stores is available. During the time of writing this paper, the beta version 1.2 can be connected to Azure Table Services 20 , Cassandra, SimpleDB 21 , HBase and every ODBC compliant relational database. Toad for Cloud consists of two components. The first is the Toad client, which can be installed on a Microsoft Windows computer. It can be used to access different databases, the Amazon EC2 console, and to write and execute SQL statements. The second component is the Data Hub. It translates SQL statements submitted through the Toad client into a language understood by all supported databases and returns results in the familiar tabular row and column format. In order to use SQL on column family stores like Cassandra and HBase, the column families, rows and columns have to be mapped into virtual tables. Afterwards, the user does have full MySQL support on these data, containing also inserts, updates and deletes. Furthermore, it is possible to do virtual data integration with different data sources. One reason why only column family stores are supported by Toad is their easy mapping to relational databases. To do the same with a document store containing objects with a deep nested structure or squeezing a graph into a relational schema is a much more complicated task. Even if a suitable solution was found, powerful and easy to use query languages, tools and interfaces like Traverser API for Neo4J would be missing in the SQL layer of Toad, which queries the mapped tables. Due to their different data models and their relatively young history, NoSQL databases still lack a common query language. However, Quest Software and Hadoop demonstrate that it is possible to use SQL (Toad) or a SQL like query language (Hive) on top of column family stores. A mapping to document stores and graph databases is still missing. We have seen that each category of NoSQL databases has its own data model. In this section, we present details concerning document oriented and column family categories. Document oriented databases correspond to an extension of the well-known key- value concept where in this case the value consists of a structured document. A document contains hierarchically organized data similar to XML and JSON. This permits to represent one-to-one as well as one-to-many relationships in a single document. Therefore a complex document can be retrieved or stored without using joins. Since document oriented databases are aware of stored data, it enables to define document field indexes as well as to propose advanced query features. The most popular document oriented databases are MongoDB (10gen) and CouchDB (Apache). Example 1: In the following a document oriented database stores drug information aimed at an application targeting the general public. According to features proposed by the application, two so-called collections are defined: drugD and therapD . drugD includes documents describing drug related information whereas therapD contains documents with information about therapeutic classes and drugs used for it. Each drugD document is identified by a drug identifier. In this example, its attributes are limited to the name of the product, its price, pharmaceutical lab and a list of therapeutic classes. The key for therapD documents is a string corresponding to the therapeutic class name. It contains a single attribute corresponding to the list of drug identifiers treating this therapeutic class. Figure 2 presents an extract of this database. Finally, in order to ensure an efficient search to patients an index on the attribute name of the drugD document is defined. Column family stores correspond to persistent, sparse, distributed multilevel hash maps. In column family stores, arbitrary keys (rows) are applied to arbitrary key value pairs (columns). These columns can be extended with further arbitrary key value pairs. Afterwards, these key value pair lists can be organized into column families and keyspaces. Finally, column-family stores can appear in a very similar shape to relational databases on the surface. The most popular systems are HBase and Cassandra. All of them are influenced by Googles Bigtable. Example 2: Figure 3 presents some column families defined in a medical application. Since Cassandra works best when its data model is denormalized, the data is divided on three column families: drugC , drugNameC and therapC . The columns drugName , contra , composition , lab are integrated into drugC and identified by row key drugId . drugNameC contains row key drugName and a drugId column in order to provide an efficient search for patients. Since end- users of this database need to search products by therapeutical classes, therapC contains therapName as row key and a column for each drugId with a timestamp as value. This section presents the syntax and semantics of our data integration framework. Moreover, it focuses on the mapping language which supports the definition of correspondences between sources and target entities. Our mapping language integrates some original aspects by considering (i) query processing performances of the sources via access paths and (ii) dealing with contradicting information found between sources using preferences. In the rest of this paper, we consider the following example. Example 3: A medical application needs to integrate drug data coming from two different NoSQL stores. The first database, corresponding to a document store, denoted docDB , and is used in a patient oriented application while the other database, a column family store, denoted colDB , contains information aimed at health care professionals. In this paper, we concentrate on some extracts of docDB and colDB which correspond to respectively Figures 2 and 3. The data stored in both databases present some overlapping as well as some discrepancies both at the tuple and schema level. For instance, at the schema level, both databases contain french drug identifiers, names, pharmaceutical companies and prices but only colDB proposes access to the composition of a drug product. Considering the tuple level, some drugs may be present in one database but not in the other. Moreover information concerning the same drug product (i.e. identified by the same identifier value) may contradict themselves in different sources. Given these source databases, the target schema is defined as follows. We consider that relation and attribute names are self-explanatory. Obviously, our next step it to define correspondances between the sources and the target. This is supporteed by mapping assertions which are currently being defined manually by domain experts. In the near future, we aim to discover some of them automatically by analyzing extensions and intensions of both sources and the target. Nevertheless, we do not believe that all mapping assertions can be discovered automatically due to the lack of semantics contained in both the target and the sources. Next, we present the mapping language enabling the definitions of mapping assertions. Our data integration system takes the form of a triple , , where is the target schema, S is the source schema and M is the mapping between T and S . In Section 1, we motivated the fact that the set S could correspond to both RDBMS and NoSQL stores and that T takes the form of a relational schema. We consider that the target schema is given, possibly defined by a team of domain experts or using schema matching techniques [7]. This mapping language adopts a GAV (Global As View) approach with sound sources [5]. The mapping assertions are thus of the following form: φ S φ T where φ S is a query over S and φ T is a relation of T . Our system must deal with the heterogeneity of the sources and the highly denormalized aspect of NoSQL database instances. In order to cope with this last aspect, our mapping language handles the different access paths proposed by a given source. This is due to the important performance differences one can observe between the processing of the same query through different access paths. For instance, in the context of colDB , retrieving the drug information from a drug name will be more effective using the drugCName column family rather than the drugC column family (which would require complete scan of all its tuples). We believe that a mapping assertion corresponds to the ideal place to store the preferred access paths possible for a target relation. Hence each ...