SQL-based Query Research Articles

A workload-driven method for designing aggregate-oriented NoSQL databases

Due to the scalability and availability problems with traditional relational database systems, a variety of NoSQL stores have emerged over the last decade to deal with big data. How data are structured in a NoSQL store has a large impact on the query and update performance and the storage usage. Thus, different from the traditional database design, not only the data structure but also the data access patterns need to be considered in the design of NoSQL database schemas. In this paper, we present a general workload-driven method for designing key-value, wide-column, and document NoSQL database schemas. We first present a generic logical model Query Path Graph (QPG) that can represent the data structures of the UML class diagram. We also define mappings from the SQL-based query patterns to QPG and from QPG to aggregate-oriented NoSQL schemas. We use a cost model to measure the query and update performance and optimize the QPG schemas. We evaluate the proposed method with several typical case studies by simulating workloads on databases with different schema designs. The results demonstrate that our method preserves the generality and the quality of the design.

MULTI-JOIN-ORDERING QUERY OPTIMIZATION ALGORITHM FOR HIVE WAREHOUSE WITH MAPREDUCE

According to the Digital Report of July, 2021, Billions of users around the world uses Mobile Phones, Internet, social media every second. This huge range of heterogeneous digital data is called Big Data, and is measured in terms of terabytes or petabytes. It is difficult to the conventional relational databases to handle these heterogeneous data for data analytics, but is still in use significantly in the growth of Big Data. To handle SQL-based structured queries, Hadoop is one of the prominent and well-suited solution that allows Big Data to be stored and processed. Hive support SQL queries on Hadoop. Hive warehouse, is the oldest SQL-engine on the top of the Hadoop framework and to store the processed data, it uses HDFS (Hadoop Distributed File System). On the Hadoop, MapReduce is an execution engine that executes SQL-based queries. In the Query Optimization, join ordering always plays a significant role because when the order of tables in joining operation is changed, execution time of the query is reduced to a greater extent. The main problem of the Hive is that it does not enhance the order of the join for an SQL-query and also does not give assurance for an optimal execution plan. Its time complexity is measured in exponential (Shan, Y., & Chen, Y., 2015).The main focus of this paper is to discover the finest join ordering solution for a Hive query optimization problem through appropriate search algorithms and to improve SQL-based Hive queries performance with MapReduce–based system.

Developing a Data Model of Indoor Points of Interest to Support Location-Based Services

Focus on indoor spatial applications has been rising with the growing interest in indoor spaces. Along with the widespread use of mobile devices and the internet, it has increased demands for indoor location-based services (LBS), demanding more efficient representation and management of indoor spatial data. Indoor points of interest (Indoor POI) data, representing both spaces and facilities located indoors, provide the infrastructure for these services. These datasets are vital in delivering timely and accurate information to users, such as in cases of managing indoor facilities. However, even though there are studies that explore its use across applications and efforts exerted towards the standardization of the data model, most POI development studies have focused on the outdoors and remain underdeveloped in the indoors. In this paper, we propose a spatial-temporal Indoor POI data model to provide direction for the establishment of indoor POI data and to address limitations in currently available data specifications. By exploring how different Indoor POIs are from its outdoor counterparts, particularly on extending its outdoor counterparts’ functions on searching, sharing, and labeling, we describe the data model and its components using the Unified Modeling Language (UML). We perform an SQL-based query experiment to demonstrate the potential use of the data model using sample data.

The role of Learning Analytics in Educational Data Mining

EDM (Educational Data Mining) is a data mining algorithm that is used in the field of education. Data mining is a multidisciplinary field that combines artificial intelligence, machine learning, statistics, and database systems to produce results. Data mining may be used in a number of ways in the educational industry to improve student performance and educational institution reputation. Data gathering can indeed be applied to a broad variety of applications in the education system for the aim of boosting student performance and also the status of academic institutions, thanks to the emergence of new technologies that can harness data children s educational and the massive expansion in data sources.EDM refers to the procedures for analysing data from the learning environment in order to have a better understanding of students in their learning environment. The ability for teachers to gain real-time insight about the performance of students, including those who are at risk, can be extremely beneficial in arranging educational activities. It can be motivating and encouraging for students to get information about their performance in comparison to their peers or their progress toward their specific goals. Learning environment data has grown in size in recent years, necessitating the employment of Big Data technologies and methods to manage it. Databases that do not employ typical SQL-based queries are used to address this problem. Data is compressed at rest and in memory using compression algorithms. Before being aggregated, data is broken into smaller pieces and analysed by a huge number of devices spread around the network.

Toward real-time data query systems in HEP

Exploratory data analysis tools must respond quickly to a user’s questions, so that the answer to one question (e.g. a visualized histogram or fit) can influence the next. In some SQL-based query systems used in industry, even very large (petabyte) datasets can be summarized on a human timescale (seconds), employing techniques such as columnar data representation, caching, indexing, and code generation/JIT-compilation. This article describes progress toward realizing such a system for High Energy Physics (HEP), focusing on the intermediate problems of optimizing data access and calculations for “query sized” payloads, such as a single histogram or group of histograms, rather than large reconstruction or data-skimming jobs. These techniques include direct extraction of ROOT TBranches into Numpy arrays and compilation of Python analysis functions (rather than SQL) to be executed very quickly. We will also discuss the problem of caching and actively delivering jobs to worker nodes that have the necessary input data preloaded in cache. All of these pieces of the larger solution are available as standalone GitHub repositories, and could be used in current analyses.

Package queries: efficient and scalable computation of high-order constraints

Traditional database queries follow a simple model: they define constraints that each tuple in the result must satisfy. This model is computationally efficient, as the database system can evaluate the query conditions on each tuple individually. However, many practical, real-world problems require a collection of result tuples to satisfy constraints collectively, rather than individually. In this paper, we present package queries, a new query model that extends traditional database queries to handle complex constraints and preferences over answer sets. We develop a full-fledged package query system, implemented on top of a traditional database engine. Our work makes several contributions. (1) We design PaQL, a SQL-based query language that supports the declarative specification of package queries. We prove that PaQL is at least as expressive as integer linear programming, and therefore, evaluation of package queries is NP-hard. (2) We present a fundamental evaluation strategy that combines the capabilities of databases and constraint optimization solvers to derive solutions to package queries. The core of our approach is a set of translation rules that transform a package query to an integer linear program. (3) We introduce an offline data partitioning strategy allowing query evaluation to scale to large data sizes. (4) We introduce SketchRefine, a scalable algorithm for package evaluation, with strong approximation guarantees [ $$(1 \pm \varepsilon )$$ -factor approximation]. (5) We present a method for parallelizing the Refine phase of SketchRefine. (6) We present an empirical study of the efficiency gains of providing integer solvers with starting solutions. (7) We present extensive experiments over real-world and benchmark data. The results demonstrate that our methods are effective at deriving high-quality package results and achieve runtime performance that is an order of magnitude faster than directly using ILP solvers over large datasets.

A Scalable Execution Engine for Package Queries

Many modern applications and real-world problems involve the design of item collections, or packages: from planning your daily meals all the way to mapping the universe. Despite the pervasive need for packages, traditional data management does not offer support for their definition and computation. This is because traditional database queries follow a powerful, but very simple model: a query defines constraints that each tuple in the result must satisfy. However, a system tasked with the design of packages cannot consider items independently; rather, the system needs to determine if a set of items collectively satisfy given criteria. In this paper, we present package queries, a new query model that extends traditional database queries to handle complex constraints and preferences over answer sets. We develop a full-fledged package query system, implemented on top of a traditional database engine. Our work makes several contributions. First, we design PaQL, a SQL-based query language that supports the declarative specification of package queries. Second, we present a fundamental strategy for evaluating package queries that combines the capabilities of databases and constraint optimization solvers. The core of our approach is a set of translation rules that transform a package query to an integer linear program. Third, we introduce an offline data partitioning strategy allowing query evaluation to scale to large data sizes. Fourth, we introduce SKETCHREFINE, an efficient and scalable algorithm for package evaluation, which offers strong approximation guarantees. Finally, we present extensive experiments over real-world data. Our results demonstrate that SKETCHREFINE is effective at deriving high-quality package results, and achieves runtime performance that is an order of magnitude faster than directly using ILP solvers over large datasets.

Hadoop에서 SQL 기반 질의언어를 지원하는 공간 빅데이터 질의처리 시스템

본 논문에서는 Hadoop에 공간 데이터를 저장하고 SQL 기반 질의언어에 의하여 공간 데이터를 질의할 수 있는 공간 빅데이터 질의처리 시스템을 제시한다. 제안한 시스템은 대용량의 공간 빅데이터를 HDFS 기반의 저장 시스템에 저장하고 공간 데이터 처리기능이 추가확장된 SQL 기반 질의언어로 질의를 할 수 있도록 지원하며 OGC 심플 피처모델 기반의 공간 데이터 표준 데이터타입과 함수를 지원한다. 본 논문에서는 질의언어 파싱, 질의언어 검증, 질의계획 생성, 저장시스템 연동 등 질의처리의 주요 기능 개발을 제시하였다. 제안한 시스템의 성능을 기존 시스템과 비교하였으며, 실험에서는 Hadoop에 저장된 공간 데이터에 대한 영역질의의 질의실행시간에 있어서 비교 시스템 대비 약 58%의 성능향상을 나타냄을 보였다. In this paper we present a spatial big data query processing system that can store spatial data in Hadoop and query the data with SQL-based query language. The system stores large-scale spatial data in HDFS-based storage system, and supports spatial queries expressed in SQL-based query language extended for spatial data processing. It supports standard spatial data types and functions defined in OGC simple feature model in the query language. This paper presents the development of core functions of the system including query language parsing, query validation, query planning, and connection with storage system. We compares the performance of the suggested system with an existing system, and our experiments show that the system shows about 58% performance improvement of query execution time over the existing system when executing region query for spatial data stored in Hadoop.

Data Dissemination and Parallel Processing Techniques Research Based on Massively Parallel Processing

In the current distributed database system architecture enterprise-class, the massively parallel processing architecture is used frequently. This method can be used to carry out large-scale analysis of data through distributed across multiple nodes and storage and query process, from its scope of application produce simple reports to perform complex analytics workloads. However, due to the characteristics of shared-nothing MPP technology, to carry out large-scale data analysis query and maintain data consistency there are some difficulties. In this paper, a relational SQL-based query parsing distributed MPP data distribution and parallel processing technology, the goal is to maintain and improve the consistency of distributed data query speed. First SQL query analysis section, according to the syntax analysis, semantic analysis and sentence parsing steps such order; in the form of work distribution node/data node in the data distribution phase, all tasks emanating from the work of a distribution node, all need to treated results are returned to the node; when parallel processing, each node needs to store a copy of the lookup table, and on each node concurrent execution of SQL statements for each query. Experimental results show that the proposed MPP data distribution and parallel processing scheme can support large volume of data processing, ensuring data consistency in the premise of improving query processing speed.

INDREX: In-database relation extraction

The management of text data has a long-standing history in the human mankind. A particular common task is extracting relations from text. Typically, the user performs this task with two separate systems, a relation extraction system and an SQL-based query engine for analytical tasks. During this iterative analytical workflow, the user must frequently ship data between these systems. Worse, the user must learn to manage both systems. Therefore, end users often desire a single system for both analytical and relation extraction tasks.We propose INDREX, a system that provides a single and comprehensive view of the whole process combining both relation extraction and later exploitation with SQL. The system permits a data warehouse style extract-transform-load of generic relations extracted from text documents and can support additional text mining analysis libraries or systems. Once generic relations are loaded, the user can define SQL queries on the extracted relations to discover higher level semantics or to join them with other relational data.For executing this powerful task, our system extends the SQL-based analytical capabilities of a columnar-based massively parallel query processing engine with a broad set of user-defined functions and a data model that supports this task. Our white-box approach permits INDREX to benefit from built-in query optimization and indexing techniques of the underlaying query execution engine.Applications that support both text mining and analytical workflows leverage new analytical platforms based on the MapReduce framework and its open source Hadoop implementation. We compare our system against this base line. We measure execution times for common workflows and demonstrate orders of magnitude improvement in execution time using INDREX.

SQL-Based Fuzzy Query Mechanism Over Encrypted Database

With the development of cloud computing and big data, data privacy protection has become an urgent problem to solve. Data encryption is the most effective way to protect privacy; however, it will change the data format and result in: 1. database structure and application software will be changed; 2. structured query language (SQL) operations cannot work properly, especially in SQL-based fuzzy query. As a result, it is necessary to provide an SQL-based fuzzy query mechanism over encrypted databases, including traditional databases and cloud outsourced databases. This paper establishes a secure database system using format-preserving encryption (FPE) as the underlying primitive to protect the data privacy while not change the database structure. It further proposes a new SQL-based fuzzy query mechanism supporting directly query over encrypted data, which is constructed by FPE and universal hash function (UHF). The security of the proposed mechanism is analyzed as well. In the end, it makes extensive experiments on the system to demonstrate its practical performance.

L-EncDB: A lightweight framework for privacy-preserving data queries in cloud computing

With the advent of cloud computing, individuals and organizations have become interested in moving their databases from local to remote cloud servers. However, data owners and cloud service providers are not in the same trusted domain in practice. For the protection of data privacy, sensitive data usually have to be encrypted before outsourcing, which makes effective database utilization a very challenging task. To address this challenge, in this paper, we propose L-EncDB, a novel lightweight encryption mechanism for database, which (i) keeps the database structure and (ii) supports efficient SQL-based queries. To achieve this goal, a new format-preserving encryption (FPE) scheme is constructed in this paper, which can be used to encrypt all types of character strings stored in database. Extensive analysis demonstrates that the proposed L-EncDB scheme is highly efficient and provably secure under existing security model.

Using Page Size for Controlling Duplicate Query Results in Semantic Web

Semantic web is a web of future. TheResource Description Framework ( RDF) is a language to represent resources in the World Wide Web. When these resources are queried the problem of duplicate query results occurs. The present techniques used hash index comparison to remove duplicate query results. The major drawback of using the hash index to remove duplicate query results is that, if there is a slight change in formatting or word order, then hash i ndex is changed and query results are no more considered as duplicate even though they have same contents. We presented an algorithm for detection and elimination of duplicate query results from semantic web using hash index and page size comparisons. Experimental results showed that the proposed technique removed duplicate query results from semantic web efficiently, solved the problems of using hash index for duplicate handling and could be embedded in existing SQL-Based query system for semantic web. Res earch could be carried out for certain flexibilities in existing SQL-Based query system of semantic web to accommodate other duplicate detection techniques as well.

The Building and Optimization of Quantum Database

We have defined a scheme that takes a relational database and transform it in a quantum DB allowing an optimized query processing of SQL-based queries. Our approach has been applied to a simple example to show how our scheme performs. This work addresses the realization of more complex queries and builds an appropriate environment for quantum transaction execution.

Managing context data for diverse operating spaces

Context-aware computing is an exciting paradigm in which applications perceive and react to changing environments in an unattended manner. To enable behavioral adaptation, a context-aware application must dynamically acquire context data from different operating spaces in the real world, such as homes, shops and persons. Motivated by the sheer number and diversity of operating spaces, we propose a scalable context data management system in this paper to facilitate data acquisition from these spaces. In our system, we design a gateway framework for all operating spaces and develop matching algorithms to integrate the local context schemas of operating spaces into a global set of domain schemas upon which SQL-based context queries can be issued from applications. The system organizes the operating space gateways as peers in semantic overlay networks and employs distributed query processing techniques over these overlays. Evaluation results on a prototype implementation demonstrate the effectiveness of our system design.

GEOmetadb: powerful alternative search engine for the Gene Expression Omnibus

The NCBI Gene Expression Omnibus (GEO) represents the largest public repository of microarray data. However, finding data in GEO can be challenging. We have developed GEOmetadb in an attempt to make querying the GEO metadata both easier and more powerful. All GEO metadata records as well as the relationships between them are parsed and stored in a local MySQL database. A powerful, flexible web search interface with several convenient utilities provides query capabilities not available via NCBI tools. In addition, a Bioconductor package, GEOmetadb that utilizes a SQLite export of the entire GEOmetadb database is also available, rendering the entire GEO database accessible with full power of SQL-based queries from within R.Availability: The web interface and SQLite databases available at http://gbnci.abcc.ncifcrf.gov/geo/. The Bioconductor package is available via the Bioconductor project. The corresponding MATLAB implementation is also available at the same website.Contact: yidong@mail.nih.gov

Development of Query Strategies to Identify a Histologic Lymphoma Subtype in a Large Linked Database System

Background Large linked databases (LLDB) represent a novel resource for cancer outcomes research. However, accurate means of identifying a patient population of interest within these LLDBs can be challenging. Our research group developed a fully integrated platform that provides a means of combining independent legacy databases into a single cancer-focused LLDB system. We compared the sensitivity and specificity of several SQL-based query strategies for identifying a histologic lymphoma subtype in this LLDB to determine the most accurate legacy data source for identifying a specific cancer patient population. Methods Query strategies were developed to identify patients with follicular lymphoma from a LLDB of cancer registry data, electronic medical records (EMR), laboratory, administrative, pharmacy, and other clinical data. Queries were performed using common diagnostic codes (ICD-9), cancer registry histology codes (ICD-O), and text searches of EMRs. We reviewed medical records and pathology reports to confirm each diagnosis and calculated the sensitivity and specificity for each query strategy. Results Together the queries identified 1538 potential cases of follicular lymphoma. Review of pathology and other medical reports confirmed 415 cases of follicular lymphoma, 300 pathology-verified and 115 verified from other medical reports. The query using ICD-O codes was highly specific (96%). Queries using text strings varied in sensitivity (range 7–92%) and specificity (range 86–99%). Queries using ICD-9 codes were both less sensitive (34–44%) and specific (35–87%). Conclusions Queries of linked-cancer databases that include cancer registry data should utilize ICD-O codes or employ structured free-text searches to identify patient populations with a precise histologic diagnosis.

The advantage of using relational databases for large corpora

Relational databases can be used to create large corpora that provide both very good search performance and a wide range of queries. This paper outlines how this approach has been used to create theCorpus del Español, which contains 100 million words of text in Spanish texts from the 1200s-1900s. The main databases are composed of n-grams tables (all unique 1, 2, 3, and 4 word sequences) and the associated frequency of all n-grams in each century (historical Spanish) and register (Modern Spanish). These tables are then joined to other tables containing part of speech, lemma, synonyms, and user-defined lists of words and lemma. There is essentially no limit to the amount of annotation that can be added in additional tables (with little or no impact on performance), and the SQL-based queries allow a wide range of searches that are not available with traditional corpora.

A relational debugging engine for the graphics pipeline

We present a new, unified approach to debugging graphics software. We propose a representation of all graphics state over the course of program execution as a relational database, and produce a query-based framework for extracting, manipulating, and visualizing data from all stages of the graphics pipeline. Using an SQL-based query language, the programmer can establish functional relationships among all the data, linking OpenGL state to primitives to vertices to fragments to pixels. Based on the Chromium library, our approach requires no modification to or recompilation of the program to be debugged, and forms a superset of many existing techniques for debugging graphics software.

An SQL-based query language for networks of relations

A set of relations can be modeled as a network through the use of image attributes , which are attributes defined on the domain of relation names. Such networks of relations can effectively meet many of the modeling requirements of advanced database applications such as engineering design and knowledge base systems. In this paper, we describe the features of ESQL, a novel query language that is an extension of SQL [Cham74] to exploit the added semantics of image attributes. Details of ESQL and its underlying principles can be found in [Ahad88] and [Ahad89].