WHERE Clause Research Articles

HFD: Hierarchical feature decoupling for SQL generation from text

Text-to-SQL, a computational linguistics task, seeks to facilitate the conversion of natural language queries into SQL queries. Recent methodologies have leveraged the concept of slot-filling in conjunction with predetermined SQL templates to effectively bridge the semantic gap between natural language questions and structured database queries, achieving commendable performance by harnessing the power of multi-task learning. However, employing identical features across diverse tasks is an ill-suited practice, fraught with inherent drawbacks. Firstly, based on our observation, there are clear boundaries in the natural language corresponding to SELECT and WHERE clauses. Secondly, the exclusive features integral to each subtask are inadequately emphasized and underutilized, thereby hampering the acquisition of discriminative features for each specific subtask. In an endeavor to rectify these issues, the present work introduces an innovative approach: the hierarchical feature decoupling model for SQL query generation from natural language. This novel approach involves the deliberate separation of features pertaining to subtasks within both SELECT and WHERE clauses, further dissociating these features at the subtask level to foster better model performance. Empirical results derived from experiments conducted on the WikiSQL benchmark dataset reveal the superiority of the proposed approach over several state-of-the-art baseline methods in the context of text-to-SQL query generation.

Graph rewriting primitives for semantic graph databases sanitization

Due to the rapid proliferation of data online, an important quantity of private or sensitive informations is being stored as linked data in graph databases (e.g., represented as RDF). For such databases to be shared without jeopardizing privacy, they must first undergo a process known as database sanitization. During this process, databases are transformed following graph transformations that are usually described informally or through ad-hoc processes. However, a more thourough formalization of these transformations would aid in analysing the sanitization process, ensuring its correctness, and demonstrating the resulting privacy guarantees. This paper is an effort toward bridging the gap between the rigorous graph rewriting approaches and graph sanitization. We propose a graph transformation language to serve as a basis for constructing various sanitization mechanisms. This language relies on a set of elementary transformation operators formalized using a generic algebraic graph rewriting approach. Our language takes into account semantic and supports the equivalent of WHERE and EXCEPT clauses. As a proof of concept, we use these operators to implement two mechanisms from the literature, one generic (Local Differential Privacy) and one specifically introduced for semantic graph databases (sensitive attribute masking through anatomization). We propose an open-sourced tool implementing the elementary operators and the privacy mechanisms we derive from them relying on the Attributed Graph Grammar System (AGG) and its java API, providing a concrete tool implementing formal graph rewriting mechanisms to sanitize semantic graph databases. We present experimental results on this implementation regarding both proposed schemes and discuss its efficiency and scalability.

Improving Text-to-SQL with a Hybrid Decoding Method

Text-to-SQL is a task that converts natural language questions into SQL queries. Recent text-to-SQL models employ two decoding methods: sketch-based and generation-based, but each has its own shortcomings. The sketch-based method has limitations in performance as it does not reflect the relevance between SQL elements, while the generation-based method may increase inference time and cause syntactic errors. Therefore, we propose a novel decoding method, Hybrid decoder, which combines both methods. This reflects inter-SQL element information and defines elements that can be generated, enabling the generation of syntactically accurate SQL queries. Additionally, we introduce a Value prediction module for predicting values in the WHERE clause. It simplifies the decoding process and reduces the size of vocabulary by predicting values at once, regardless of the number of conditions. The results of evaluating the significance of Hybrid decoder indicate that it improves performance by effectively incorporating mutual information among SQL elements, compared to the sketch-based method. It also efficiently generates SQL queries by simplifying the decoding process in the generation-based method. In addition, we design a new evaluation measure to evaluate if it generates syntactically correct SQL queries. The result demonstrates that the proposed model generates syntactically accurate SQL queries.

Conditional Generative Model Based Predicate-Aware Query Approximation

The goal of Approximate Query Processing (AQP) is to provide very fast but "accurate enough" results for costly aggregate queries thereby improving user experience in interactive exploration of large datasets. Recently proposed Machine-Learning-based AQP techniques can provide very low latency as query execution only involves model inference as compared to traditional query processing on database clusters. However, with increase in the number of filtering predicates (WHERE clauses), the approximation error significantly increases for these methods. Analysts often use queries with a large number of predicates for insights discovery. Thus, maintaining low approximation error is important to prevent analysts from drawing misleading conclusions. In this paper, we propose ELECTRA, a predicate-aware AQP system that can answer analytics-style queries with a large number of predicates with much smaller approximation errors. ELECTRA uses a conditional generative model that learns the conditional distribution of the data and at run-time generates a small (≈ 1000 rows) but representative sample, on which the query is executed to compute the approximate result. Our evaluations with four different baselines on three real-world datasets show that ELECTRA provides lower AQP error for large number of predicates compared to baselines.

An Efficient Distributed SPARQL Query Processing Scheme Considering Communication Costs in Spark Environments

Various distributed processing schemes were studied to efficiently utilize a large scale of RDF graph in semantic web services. This paper proposes a new distributed SPARQL query processing scheme considering communication costs in Spark environments to reduce I/O costs during SPARQL query processing. We divide a SPARQL query into several subqueries using a WHERE clause to process a query of an RDF graph stored in a distributed environment. The proposed scheme reduces data communication costs by grouping the divided subqueries in related nodes through the index and processing them, and the grouped subqueries calculate the cost of all possible query execution paths to select an efficient query execution path. The efficient query execution path is selected through the algorithm considering the data parsing cost of all possible query execution paths, amount of data communication, and queue time per node. It is shown through various performance evaluations that the proposed scheme outperforms the existing schemes.

CLASSIFICATION COMPLEX QUERY SQL FOR DATA LAKE MANAGEMENT USING MACHINE LEARNING

A query is a request for data or information from a database table or a combination of tables. It allows for a more accurate database search. SQL queries are divided into two types, namely, simple queries and complex queries. Complex SQL is the use of SQL queries that go beyond standard SQL by using the SELECT and WHERE commands. Complex SQL queries often involve the use of complex joins and subqueries, where the queries are nested in a WHERE clause. Complex SQL queries can be grouped into two types of queries, namely, Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP) queries. In the implementation of complex SQL queries in the NoSQL database, a classification process is needed due to the varying data formats, namely, structured, semi-structured, and unstructured data. The classification process aims to make it easier for the query data to be organized by type of query. The classification method used in this research is the Naive Bayes Classifier (NBC) which is generally often used in text data, and the Support Vector Machine (SVM), which is known to work very well on data with large dimensions. The two methods will be compared to determine the best classification result. The results showed that SVM was 84.61% accurate in terms of classification, and comparatively, NBC was at 76.92%.

A Novel Approach Using Non-Synonymous Materialized Queries for Data Warehousing

Data from multiple sources are loaded into the organization data warehouse for analysis. Since some OLAP queries are quite frequently fired on the warehouse data, their execution time is reduced by storing the queries and results in a relational database, referred as materialized query database (MQDB). If the tables, fields, functions, and criteria of input query and stored query are the same but the query criteria specified in WHERE or HAVING clause do not match, then they are considered non-synonymous to each other. In the present research, the results of non-synonymous queries are generated by reusing the existing stored results after applying UNION or MINUS operations on them. This will reduce the execution time of non-synonymous queries. For superset criteria values of input query, UNION operation is applied, and for subset values, MINUS operation is applied. Incremental result processing of existing stored results, if required, is performed using Data Marts.

Big Data Proprietary Platforms. The Case of Oracle Exadata

Abstract The most prominent Big Data solutions – such as NoSQL systems, Hadoop Frameworks, Spark, etc. – have been open-sourced. Nevertheless, commercial providers have targeted niches of this huge market with products more or less viable and affordable. This paper addresses the problem of benchmarking Big Data platforms with a focus on Oracle Exadata solution provided by one the most important data technologies vendor. Many classical benchmark approaches, such as TPC-H, are based on a predefined set of queries, and consequently they are not prone to predictive modeling. By contrast, for the TPC-H benchmark schema, we generate a set of 500 random queries containing not only tuple filters (WHERE), but also tuple grouping (GROUP BY) and group filters (HAVING), we collected results of the queries execution on four Oracle Exadata settings. Query duration was the outcome variable. Various query parameters, such as the number of joins, the number of attributes of different types within SELECT and WHERE clauses, and also some environment metrics served as predictors. Results were interpreted using exploratory data analysis and also Multivariate Adaptive Regression Splines (MARS) for both predicting the performance and explaining the main drivers of the system performance.

Performance Analysis of Two Big Data Technologies on a Cloud Distributed Architecture. Results for Non-Aggregate Queries on Medium-Sized Data

Abstract Big Data systems manage and process huge volumes of data constantly generated by various technologies in a myriad of formats. Big Data advocates (and preachers) have claimed that, relative to classical, relational/SQL Data Base Management Systems, Big Data technologies such as NoSQL, Hadoop and in-memory data stores perform better. This paper compares data processing performance of two systems belonging to SQL (PostgreSQL/Postgres XL) and Big Data (Hadoop/Hive) camps on a distributed five-node cluster deployed in cloud. Unlike benchmarks in use (YCSB, TPC), a series of R modules were devised for generating random non-aggregate queries on different subschema (with increasing data size) of TPC-H database. Overall performance of the two systems was compared. Subsequently a number of models were developed for relating performance on the system and also on various query parameters such as the number of attributes in SELECT and WHERE clause, number of joins, number of processing rows etc.

Object Connection Hypergraphs-an Approach for Nested Object Query Optimization

In Object-Oriented Databases (OODB), nested object queries are used regularly. Nested structures are put in conditional expressions of the queries in two forms: nested sub-queries or path expression containing hidden joins – nested predicates in WHERE clauses. For nested queries, when analyzing the estimated cost of the nested algebraic expression, the expression evaluation result gives out an ineffective cost. Therefore, our method proposed in this paper will resolve the problems by leveling nested sub-queries in the nested queries. This method will increase the effectiveness of the query processing cost – We use object connection hypergraphs to present nested queries.

Динамическая компиляция выражений в SQL-запросах для СУБД PostgreSQL

In recent years, as performance and capacity of main and external memory grow, performance of database management systems (DBMSes) on certain kinds of queries is more determined by raw CPU speed. Currently, PostgreSQL uses the interpreter to execute SQL queries. This yields an overhead caused by indirect calls to handler functions and runtime checks, which could be avoided if the query were compiled into native code on-the-fly, i.e. just-in-time (JIT) compiled: at run time the specific table structure is known as well as data types and built-in functions used in the query as well as the query itself. This is especially important for complex queries, performance of which is CPU-bound. We’ve developed a PostgreSQL extension that implements SQL query JIT compilation using LLVM compiler infrastructure. In this paper we show how to implement JIT compilation to speed up sequential scan operator (SeqScan) as well as expressions in WHERE clauses. We describe some important optimizations that are possible only with dynamic compilation, such as precomputing tuple attributes offsets only for attributes used by the query. We also discuss the maintainability of our extension, i.e. the automation for translating PostgreSQL backend functions into LLVM IR, using the same source code both for our JIT compiler and the existing interpreter. Currently, with LLVM JIT we achieve up to 5x speedup on synthetic tests as compared to original PostgreSQL interpreter.

SVQL: A SQL Extended Query Language for Video Databases

With the rapid increasing of video data, video queries are becoming increasingly important. To better describe users’ video query requirements, developing a functional video query language has become a promising and interesting task. In this paper, we present a novel query language called SVQL for video databases, which is developed based on an extension of the traditional database query language SQL. In SVQL, we remain the clear and concise grammatical framework of SQL, thereby making SVQL easy to learn and use for traditional users, i.e., making SVQL with a user-friendly interface. Moreover, we extend the WHERE clause of SQL to introduce new conditional expressions, such as variable declaration, structure specification, feature specification and spatial-temporal specification, thereby, making SVQL with powerful expressiveness. In this paper, we first present the formal definitions of SVQL and illustrate its basic query capabilities using examples. Then, we discuss the SVQL query processing techniques. Finally, we evaluate SVQL through the comparison with other existing video query languages, and the evaluation results demonstrate the practicality and effectiveness of our proposed query language for video databases

A Comparative Study on the Performance of HZR+ Trees on Query Processing

Data is being produced in new forms and unimaginable quantities. Researches and other scientific and commercial applications are engrossing the scientific community for their size and need of faster accessibility. The conventional access methods previously available in multidimensional databases are no longer suitable for the new form of data produced. In traditional databases, multicolumn index is created using B-tree [5]. This indexing cannot slide over columns, so the primary index column must be in the WHERE clause filters of the query. The R-tree [3], an extension of the B-tree, is a hierarchical, height balanced multidimensional indexing structure that guarantees space utilization above a certain threshold. But the data produced in most of the cases are not spatial in nature. Therefore, the data should be restructured in order to map the non-spatial data to geometric space. Thus, the multidimensional accessibility of spatial access methods, experimented on non spatial data for the first time and the analysis of which has produced interesting results forms the major contributions of this paper. The sequence of procedures followed to arrive at the analytical study is as follows: 1. The packing of non spatial data converts the data into a form that paves the way for multidimensional access, similar to using spatial access methods for spatial data. 2. The proposal of reduction of overlap of data using Hilbert curves for ordering the data before insertion into the proposed indexing structure 3. The proposal of a new index structure, Hilbert ZR+ Tree [HZR+ Tree]. 4. A collection of experiments and analysis which validates and proves the efficiency of the proposed data model.

SnipSuggest

In this paper, we present SnipSuggest, a system that provides on-the-go, context-aware assistance in the SQL composition process. SnipSuggest aims to help the increasing population of non-expert database users, who need to perform complex analysis on their large-scale datasets, but have difficulty writing SQL queries. As a user types a query, SnipSuggest recommends possible additions to various clauses in the query using relevant snippets collected from a log of past queries. SnipSuggest's current capabilities include suggesting tables, views, and table-valued functions in the FROM clause, columns in the SELECT clause, predicates in the WHERE clause, columns in the GROUP BY clause, aggregates, and some support for sub-queries. SnipSuggest adjusts its recommendations according to the context: as the user writes more of the query, it is able to provide more accurate suggestions. We evaluate SnipSuggest over two query logs: one from an undergraduate database class and another from the Sloan Digital Sky Survey database. We show that SnipSuggest is able to recommend useful snippets with up to 93.7% average precision, at interactive speed. We also show that SnipSuggest outperforms naïve approaches, such as recommending popular snippets.

Increasing Database Performance through Optimizing Structure Query Language Join Statement

Problem statement: A join statement is a select statement with more th an table in the FROM clause. A join predicate is a predicate in the WHERE clause that combines the columns of two of the tables in the join. Any database gives you t he ability to join various tables together through different types of joins, resulting large number of rows to process. Query language can be used to join these tables and as it is well known query lan guage should be declarative, so we can write alternative formulas to perform join statements. Di fferent formulas provide variation in performance. Approach: This research presented a transparent middle layer between application interface front end and database back end. Results: The responsibilities of this layer were catching t he SQL commands sent by application before reaching the database then examining these commands to see if they join more than one table, after that re writing the SQL command taking into consideration the order of executing join predicates and none joi n predicates. This research focused on rewriting th e SQL commands without application modification. Conclusion: Rewriting stage is the most complex stage because the system will restructure the SQL c ommand with new syntax taking two things in its consideration, the first one was rewriting the comm and with better performance syntax after getting th e help from recommendation dictionary, the second one was resulting the same data (output) as previous old command.

Design of a multi-dimensional query expression for document warehouses

During the past decade, data warehousing has been widely adopted in the business community. It provides multi-dimensional analyses on cumulated historical business data for helping contemporary administrative decision-makings. However, many data warehousing query language in present only provides on-line analytical processing (OLAP) for numeric data. For example, MDX (Multi-Dimensional eXpressions) has been proposed as a query language to allow describing multi-dimensional queries over databases with OLAP capabilities. Nevertheless, it is believed there is only about 20% information can be extracted from data warehouses concerning numeric data only, the other 80% information is hidden in non-numeric data or even in documents. Therefore, many researchers now advocate it is time to conduct research works on document warehousing to capture complete business intelligence. Document warehouses, unlike traditional document management systems, include extensive semantic information about documents, cross-document feature relations, and document grouping or clustering to provide a more accurate and more efficient access to text-oriented business intelligence. In this paper, we extend the structure of MDX into a new one containing complete constructs for querying document warehouses. The traditional MDX only contains SELECT, FROM, and WHERE clauses, which is not rich enough for document warehousing. In this paper, we present how to extend the language constructs to include GROUP BY, HAVING, and ORDER BY to design an SQL-like query language for document warehousing. The work is essential for establishing an infrastructure to help combining text processing with numeric OLAP processing technologies. Hopefully, the combination of data warehousing and document warehousing will be one of the most important kernels of knowledge management and customer relationship management applications.

SQL/SDA: a query language for supporting spatial data analysis and its Web-based implementation

An important trend of current GIS development is to provide easy and effective access to spatial analysis functionalities for supporting decision making based on geo-referenced data. Within the framework of the ongoing SQL standards for spatial extensions, a spatial query language, called SQV/SDA, has been designed to meet such a requirement. Since the language needs to incorporate the important derivation functions (e.g., map-overlay and feature-fusion) as well as the spatial relationship and metric functions, the functionality of the FROM clause in SQL is developed in addition to the SELECT and WHERE clauses. By restructuring the FROM clause via a subquery, SQL/SDA is well-adapted to the general spatial analysis procedures using current GIS packages. Such an extended SQL, therefore, stretches the capabilities of previous ones. The implementation of SQL/SDA on the Internet adopts a hybrid model, which takes advantage of the Web GIS design methods in both the client side and server side. The client side of SQL/SDA, programmed in the Java language, provides a query interface by introducing visual constructs such as icons, listboxes, and comboboxes to assist in the composition of queries, thereby enhancing the usability of the language. The server side of SQL/SDA, which is composed of a query processor and Spatial Database Engine (SDE), carries out query processing on spatial databases after receiving user requests.

Restructuring the SQL Framework for Spatial Queries

This paper presents an approach to designing a spatial query language, called GeoSQL, in terms of the conventional spatial query and implementation process. A critical factor to the design is how to accommodate spatial operators in an appropriate form, while being compatible with the Structured Query Language (SQL) standard. To achieve this, the FROM clause of SQL is restructured to contain spatial operators via a subquery so that the results of spatial operations can be easily fed into both the SELECT and WHERE clauses. The subquery in the FROM clause creates an intermediate relation, on which the selection in terms of certain criteria is conducted. This is a distinct characteristic of GeoSQL. The syntax and semantics of GeoSQL are described, and a set of examples for testing the expressiveness of the language is given. The interface of the language is also designed with the introduction of visual constructs (e.g., icons and ListBoxes) to aid the entry of query text. This distinguishes GeoSQL's interface from the previous extended SQLs, which only employ pure text for constructing a query. After this, an implementation of GeoSQL is discussed. This paper finally suggests further extending GeoSQL for temporal and fuzzy queries.

Applying object-oriented technologies in modeling and querying temporally oriented clinical databases dealing with temporal granularity and indeterminacy.

The need of managing temporal information given at different levels of granularity or with indeterminacy is common to many application areas. Among them, we focus on clinical data management. Different time granularities and indeterminacy are also needed in querying temporal databases. In this paper, we describe GCH-OSQL (Granular Clinical History-Object Structured Query Language), an object-oriented temporally-oriented extension of SQL. GCH-OSQL is based on an object-oriented temporal data model, GCH-OODM. GCH-OODM allows storage of clinical information at different and mixed granularities or with temporal indeterminacy. GCH-OSQL deals with the valid time of clinical information. The temporal extension of the SELECT construct includes the addition of the TIME-SLICE and MOVING WINDOW clauses, and the capability to reference the temporal dimension of objects in the WHERE and SELECT clauses. Using object-oriented technologies, a system prototype for GCH-OSQL and GCH-OODM has been implemented and applied to data management of follow-up patients after coronary angioplasty intervention.

An Analysis of Semantic Overload in Database Access Systems Using Multi-table Query Formulation

Theory suggests that semantic overload in database representations and query languages may be a significant contributing factor to difficulties in end-user access to corporate databases. With respect to join queries, two aspects of SQL-based relational database management systems are overloaded: (1) foreign key columns are overloaded to represent both attributes and relationships and (2) the WHERE clause in SQL is overloaded to include both selection criteria and join criteria. We experimentally test the semantic overload theory by comparing end user performance using data access systems (DASs) that address these aspects individually and in combination. Eliminating the overload in foreign key columns by adding an explicit relationship construct to the standard tabular relational database representation significantly improved end user performance. Eliminating the overload in the SQL WHERE clause by adding an explicit join construct, similar to that included in SQL2, reduced certain types of errors but did not significantly improve performance. We conclude that for organizations seeking to improve end user performance in multi-table database queries it is more effective simply to use an explicit relationship representation in documenting the database structure than to invest in a database query language, such as SQL2, that has an explicit join syntax.Request access from your librarian to read this article's full text.