Correlation-driven query optimization for cloud-based stores

Abstract

A method and apparatus for optimizing a query in a relational database management system (RDBMS) when a predicate on a data column in the query has a correlation to a partitioning attribute of a partitioning column in data retrieved from a cloud-based store, wherein the optimizing uses the correlation between the data column in the query to the partitioning column in the data retrieved from the cloud-based store for data elimination when processing the query. The correlation is defined in a formula or lookup data structure that maps or range-maps from the data column to the partitioning column.

What is claimed is: 
     
         1 . A computer-implemented method, comprising:
 executing a relational database management system (RDBMS) in a computer system, wherein the RDBMS retrieves data from a cloud-based store connected to the computer system, the data in the cloud-based store is a set of files comprised of data columns organized into one or more levels of a directory hierarchy representing one or more partitioning columns for the set of files, each of the levels of the directory hierarchy corresponding to a different one of the partitioning columns, and the data retrieved by the RDBMS from the cloud-based store is stored in a relational database managed by the RDBMS for use by the RDBMS in processing queries;   optimizing a query in the RDBMS when a predicate on a non-partitioning column of the data columns in the query has a semantic correlation to a partitioning attribute of one of the partitioning columns in the data retrieved by the RDBMS from the cloud-based store, wherein the semantic correlation is defined in a formula or lookup data structure for mapping from the predicate on the non-partitioning column of the data columns in the query to the partitioning attribute of the one of the partitioning columns, the semantic correlation maps or range-maps from the predicate on the non-partitioning column of the data columns to the partitioning attribute of the one of the partitioning columns, and the semantic correlation includes an exception list comprised of exception values in the data columns where the mapping does not match the formula or lookup data structure; and   accessing only the data in the levels of the directory hierarchy corresponding to the partitioning attribute of the one of the partitioning columns when the data is retrieved by the RDBMS from the cloud-based store for data elimination when processing the query.   
     
     
         2 . The method of  claim 1 , wherein the semantic correlation is defined manually by providing information that includes:
 the non-partitioning column of the data columns to map from,   the one of the partitioning columns to map to,   a mapping comprising either the formula or lookup data structure, and   an output granularity from the mapping.   
     
     
         3 . The method of  claim 1 , wherein the exception list can be disabled, the exception list prevents optimization, or the exception list is a lookup data structure. 
     
     
         4 . The method of  claim 2 , wherein the output granularity comprises:
 a single value, wherein a single value in the non-partitioning column of the data columns maps to a single value in the one of the partitioning columns;   range boundaries, wherein a single value in the non-partitioning column of the data columns maps to a range of values in the one of the partitioning columns; or   a list, wherein a single value in the non-partitioning column of the data columns maps to a list of values in the one of the partitioning columns.   
     
     
         5 . The method of  claim 1 , wherein the semantic correlation is defined autonomously or semi-autonomously, by identifying the non-partitioning column of the data columns and the one of the partitioning columns, and scanning the dataset to construct the formula or lookup data structure for performing the mapping. 
     
     
         6 . The method of  claim 5 , wherein a flag indicates whether the mapping requires maintenance as data changes, or whether the mapping is static as the data changes and can be performed once. 
     
     
         7 . The method of  claim 5 , wherein the formula or lookup data structure is inclusive in that the formula or lookup data structure captures exceptions by having separate entries for the exceptions. 
     
     
         8 . The method of  claim 1 , wherein the semantic correlation is a single cardinality that comprises a 1-to-1 correlation. 
     
     
         9 . The method of  claim 1 , wherein the semantic correlation is a higher cardinality that comprises a 1-to-many or many-to-1 correlation. 
     
     
         10 . The method of  claim 9 , wherein the semantic correlation that is the higher cardinality maps to one or more levels in a directory hierarchy. 
     
     
         11 . The method of  claim 9 , wherein the semantic correlation that is the higher cardinality uses a comparison operator other than equality. 
     
     
         12 . The method of  claim 1 , wherein the data elimination comprises file-level elimination. 
     
     
         13 . The method of  claim 1 , wherein the data elimination comprises sub-file-level elimination. 
     
     
         14 . The method of  claim 1 , wherein the cloud-based store is a distributed object store. 
     
     
         15 . The method of  claim 1 , wherein the cloud-based store is a distributed file system. 
     
     
         16 . A computer-implemented apparatus, comprising:
 a relational database management system (RDBMS) executing in a computer system, wherein the RDBMS retrieves data from a cloud-based store connected to the computer system, the data in the cloud-based store is a set of files comprised of data columns organized into one or more levels of a directory hierarchy representing one or more partitioning columns for the set of files, each of the levels of the directory hierarchy corresponding to a different one of the partitioning columns, and the data retrieved by the RDBMS from the cloud-based store is stored in a relational database managed by the RDBMS for use by the RDBMS in processing queries;   the RDBMS optimizing a query when a predicate on a non-partitioning column of the data columns in the query has a semantic correlation to a partitioning attribute of one of the partitioning columns in the data retrieved by the RDBMS from the cloud-based store, wherein the semantic correlation is defined in a formula or lookup data structure for mapping from the predicate on the non-partitioning column of the data columns in the query to the partitioning attribute of the one of the partitioning columns, the semantic correlation maps or range-maps from the predicate on the non-partitioning column of the data columns to the partitioning attribute of the one of the partitioning columns, and the semantic correlation includes an exception list comprised of exception values in the data columns where the mapping does not match the formula or lookup data structure; and   the RDBMS accessing only the data in the levels of the directory hierarchy corresponding to the partitioning attribute of the one of the partitioning columns when the data is retrieved by the RDBMS from the cloud-based store for data elimination when processing the query.   
     
     
         17 . A computer program product, the computer program product comprising a computer readable storage medium having program instructions tangibly embodied therewith, the program instructions executable by a computer system to cause the computer system to perform a method, comprising:
 executing a relational database management system (RDBMS) in a computer system, wherein the RDBMS retrieves data from a cloud-based store connected to the computer system, the data in the cloud-based store is a set of files comprised of data columns organized into one or more levels of a directory hierarchy representing one or more partitioning columns for the set of files, each of the levels of the directory hierarchy corresponding to a different one of the partitioning columns, and the data retrieved by the RDBMS from the cloud-based store is stored in a relational database managed by the RDBMS for use by the RDBMS in processing queries; and   optimizing a query in the RDBMS when a predicate on a non-partitioning column of the data columns in the query has a semantic correlation to a partitioning attribute of one of the partitioning columns in the data retrieved by the RDBMS from the cloud-based store, wherein the semantic correlation is defined in a formula or lookup data structure for mapping from the predicate on the non-partitioning column of the data columns in the query to the partitioning attribute of the one of the partitioning columns, the semantic correlation maps or range-maps from the predicate on the non-partitioning column of the data columns to the partitioning attribute of the one of the partitioning columns, and the semantic correlation includes an exception list comprised of exception values in the data columns where the mapping does not match the formula or lookup data structure; and   accessing only the data in the levels of the directory hierarchy corresponding to the partitioning attribute of the one of the partitioning columns when the data is retrieved by the RDBMS from the cloud-based store for data elimination when processing the query.