Privacy-Preserving Federated Query Engine

Abstract

The federated query engine system and method for multiple datasets is enhanced with privacy preservation features. This can, for example, restrict the movement of data from one or more of the accessed datasets. It can use cryptographic long-term keys that allow for fuzzy table joins that do not require comparison of plain text column values. Query planning can leverage the specific infrastructure of the storage systems that house each of the datasets. The query engine receives standard SQL queries, translates the queries into logical plans for performing the queries across the multiple datasets, converts the logical plans into physical plans specific to the implementation architecture of the multiple datasets, and sends these physical plans to SQL workers located near the data warehouses that house each dataset.

Description

[Background technology]

[0001] Modern enterprises often have large datasets stored in data warehouses hosted on different infrastructures and / or cloud storage environments, yet they often want to collaborate with each other on these datasets.

[0002] One recent approach to solving this problem is the federated query engine. In a typical federated query engine, data is extracted from multiple distant sources, such as databases and data warehouses, whether found in local infrastructure or cloud storage. A Standard Query Language (SQL) script is written that fetches data across these sources and joins tables from the different data sets. Examples of commercial products include Amazon Redshift and Athena, used in AWS cloud environments, and Google BigQuery, used in Google cloud environments. These systems avoid the long and redundant extract, transform, and load (ETL) process required to gather data in a shared format to run queries, since the data across the different data sources remains where it is. A single SQL-like query can be created to pull the data across these disparate sources. Because the federated query system performs the necessary translations, users do not need to know the specific query or data language for each data set, and automated conversions allow anyone to run queries across all of the data sources. Trino, Spark, and Dremio are examples of platforms that offer federated query support.

[0003] A limitation of a federated query system is that it does not include a means to independently control privacy across the multiple datasets that are accessed. Nevertheless, individual datasets may have different privacy requirements. These privacy requirements may be determined by the organizations that store the data, by the nature of the data itself, or by the laws and regulations of the jurisdictions in which the data are collected and / or stored. Therefore, a system for providing privacy protection as part of a federated query system is desirable.

[0004] References mentioned in this Background section are not admitted to be prior art to the present invention. Summary of the Invention

[0005] The present invention is directed to a federated query engine system and method with privacy preserving features. In certain embodiments, the federated query engine may restrict the movement of data from one or more of the datasets accessed. For example, the owner of a particular dataset may specify that certain columns of data in the dataset should never leave its proprietary network. Also, in certain embodiments, privacy enhancing techniques may be inserted into the query plans developed by the federated query engine to enable privacy preserving table joins. For example, this may be implemented by using cryptographic long term keys, a Bloom filter based cryptographic approach, thereby enabling fuzzy table joins that do not require comparison of plain text column values. Also, in certain embodiments, the query plans developed by the federated query engine may leverage the specific infrastructure of the storage systems housing each of the datasets. For example, if there are multiple datasets stored in a Snowflake account, the plan may leverage native Snowflake capabilities to enable secure data sharing, while knowing that this will not be part of the plan for joins between Snowflake dataset environments and non-Snowflake dataset environments.

[0006] By being aware of both the data location (for federation) and privacy constraints of each of the underlying datasets, a federated query engine system according to an embodiment of the present invention can rewrite / generate optimal privacy-compliant execution plans. These and other features, objects, and advantages of the present invention will become better understood from a consideration of the following detailed description of the preferred embodiments and the appended claims, taken in conjunction with the drawings as described below. [Brief description of the drawings]

[0007] [Figure 1]FIG. 2 is an architecture diagram of a system according to an embodiment of the present invention. [Diagram 2] FIG. 2 is a process flow diagram for a federated SQL coordinator in accordance with an embodiment of the invention. DETAILED DESCRIPTION OF THE PREFERRED EMBODIMENTS

[0008] Before the present invention is described in greater detail, it is to be understood that the invention is not limited to the particular embodiments described, and that the terminology used in describing the particular embodiments is for the purpose of describing these particular embodiments only, and is not intended to be limiting, as the scope of the present invention will be limited only by the claims.

[0009] With reference to FIG. 2, an overview of the flow for use of an embodiment of the present invention as a privacy-preserving federated SQL engine can work as follows: In SQL text step 10, the engine takes as input (a) a query statement, which may be written in SQL, (b) a set of privacy policies associated with the queried dataset, and (c) a schema for each dataset / table along with the dataset location (i.e., association with the underlying data warehouse). This step may be performed using a SQL parser / optimizer 11, such as Spark Catalyst. Within the private SQL engine 13 itself, the next step is for the engine to parse the SQL statement into a structured form in SQL AST step 12. An abstract syntax tree (AST) is a structured tree data structure that provides a machine-readable format for concrete syntax code. Specifically, for SQL, the AST may be in the form of a tree made up of nodes for joins, filters, projections, etc. Based on the input privacy policy and table locations, engine 13 then performs a structural transformation (i.e., rewriting) of the query's AST to satisfy the privacy constraints imposed on the system. This produces a "logical" plan 14 for the query on each of the separate datasets. From this logical plan 14, engine 13 then generates an optimized query execution, a physical plan 16 that takes into account both the privacy constraints and the dataset / table locations. The top-level physical plan 16 can be composed of multiple separate physical plans 16, one for each of the underlying datasets (whose tables are part of the input query).

[0010] It can be seen that the SQL Query Engine 13 uses the input query to define a logical plan 14 that defines what has to be done, rather than algorithmic level details of how it will be done. This logical plan 14 is used to create one or more physical plans 16 that define the specific algorithms that will be used to execute the query. The Optimizer 11 then tries to choose the best physical plan 16, and finally the query is executed according to this plan.

[0011] Once the physical plan 16 is produced, it is submitted to each of the data warehouse's SQL workers in an execution graph 18. In this process, the individual physical plans are translated into the native data warehouse 21 SQL (or non-SQL) dialect appropriate for each dataset. The SQL workers then execute the translated queries against the underlying datasets, fetch the results, and send the results back to the coordinator in job step 20. The coordinator performs a final aggregation from the multiple results, potentially applies additional privacy constraints on the aggregated results, and returns the results back from the SQL engine 13.

[0012] Now referring to FIG. 1, the structure of a system according to an embodiment of the present invention can be described. The SQL engine 13 includes an SQL coordinator 20, a query rewrite SQL planner 22, and an SQL aggregator 24. The SQL planner 22 utilizes an SQL parser and SQL query optimizer library, but performs custom rewrites / transformations and optimizations to generate multiple physical plans 16 for each of the data sets in the data warehouse 21. The SQL aggregator 24 performs on-the-fly aggregation and applies custom privacy restrictions to the query results. The SQL aggregator uses a standard local SQL database or data warehouse. SQL workers 26 are placed close to each data set. The SQL workers 26 perform translation of the query AST into the data warehouse dialect and rely on the existing data warehouse to execute the physical SQL (or non-SQL) queries.

[0013] The SQL Engine 13 relies on an existing cloud data warehouse (BigQuery, Snowflake, AWS Athena, etc.) for the actual execution of the underlying queries. The control plane of the SQL Engine 13 may be deployed in the service provider's cloud (e.g., Google Cloud), while the SQL Workers 26 may be deployed close to the customer's data warehouse / cloud 21.

[0014] The operation of an example system can now be explained. Suppose a query is run against two data sets, one representing customer relationship management (CRM) data from a sneaker manufacturer, while the other data set represents transactions from retailers selling the supplier's brand and other brands of sneakers. The query seeks to know how many customers have bought the supplier's sneaker brand from the retailer, and who is also a member of the supplier's online VIP customer program. In this example, the retailer data set is housed in the Snowflake cloud environment, while the retailer data set is in Google BigQuery. Thus, SQL workers 26 at each of these locations will translate the query into the local dialect for SQL operations on each of these two data sets. The required data, in this example, includes a STORE SALES fact table, which is combined with item and date information.

[0015] The privacy component of the system may be implemented in several ways. Assume that queries are made by suppliers. The retailer may be willing to grant the supplier access to transaction data, but does not want to reveal private customer data in order to protect privacy. Hence, the retailer does not want the supplier to be able to easily download its entire data set. Thus, in this example, the retailer sets a query threshold of 100, which means that all queries must be aggregate queries, and no groups with less than 100 customers will be returned.

[0016] Suppression of rows below the threshold is achieved by the SQL Planner's query rewrite module 22, which modifies the logical query plan to include counts of distinct customers that contributed to the values ​​in each aggregation group. As a result, counts are efficiently calculated in the source data warehouse 21. These counts are then included for each aggregated row of the results, and rows that do not meet the set threshold (100 in this example) are excluded by the aggregator 24 from the results returned to the user. In the presence of federation, this approach allows for the combination of data from multiple data sets, each with an independently set query threshold. Customer counts are calculated independently for each constituent data set, and then all policies are enforced by the aggregator after the data is combined.

[0017] To join data from a supplier's proprietary dataset with the retailer's dataset, some means of matching customer data is required. In one instance, a customer identifier, customer_id, may be used. In some cases, the retailer and its suppliers may have a common identifier for the customer, such as email. In other cases, the retailer and its suppliers may attach an identifier using a third-party identity resolution product, such as LiveRamp's RampID product.

[0018] Besides insights and measurement, another core supplier use case is to use this kind of data to generate new promotional campaigns. Suppose a supplier wants to run a campaign for VIP club members who bought shoes from another brand in a particular month. Due to the query threshold, the supplier does not know who are the unique customers in this group. However, by matching using a third-party system, direct selling to this kind of custom customer set may be enabled.

[0019] The systems and methods described herein may be implemented by any combination of hardware and software in various embodiments. For example, in one embodiment, the systems and methods may be implemented by a computer system or collection of computer systems, each of which includes one or more processors that execute program instructions stored on a computer-readable storage medium coupled to the processors. The program instructions may perform the functions described herein. The various systems and displays as shown in the figures and described herein represent example implementations. The order of any method may be changed and various elements may be added, modified, or omitted.

[0020] A computing system or computing device as described herein may implement the hardware portion of a cloud computing system or a non-cloud computing system as forming part of various implementations of the present invention. The computing system may be any of various types of devices including, but not limited to, a commodity server, a personal computer system, a desktop computer, a laptop or notebook computer, a mainframe computer system, a handheld computer, a workstation, a network computer, a consumer device, an application server, a storage device, a telephone, a mobile phone, or generally any type of computing node, computing node, computing device, and / or computing device. The computing system includes one or more processors (any of the processors may include multiple processing cores, and the processing cores may be single-threaded or multi-threaded) coupled to a system memory via an input / output (I / O) interface. The computing system may further include a network interface coupled to the I / O interface.

[0021] In various embodiments, the computer system may be a single processor system including one processor, or a multi-processor system including multiple processors. The processor may be any suitable processor capable of executing computing instructions. For example, in various embodiments, the processor may be a general purpose or embedded processor implementing any of a variety of instruction set architectures. In a multi-processor system, each of the processors may generally, but not necessarily, implement the same instruction set. The computer system also includes one or more network communication devices (e.g., network interfaces) for communicating with other systems and / or components over a communications network, such as a local area network, a wide area network, or the Internet. For example, a client application executing on a computing device may use the network interface to communicate with a server application executing on a single server or on a cluster of servers executing one or more of the components of the system described herein in a cloud computing environment or a non-cloud computing environment, such as those embodied in the various subsystems. In another example, an instance of a server application running on a computer system may use a network interface to communicate with other instances of the application, which may be running on other computer systems.

[0022] The computing device also includes one or more persistent storage devices and / or one or more I / O devices. In various embodiments, the persistent storage devices may correspond to disk drives, tape drives, solid state memory, other mass storage devices, or any other persistent storage devices. A computer system (or a distributed application or operating system running thereon) may store instructions and / or data in the persistent storage devices as desired and may retrieve the stored instructions and / or data as needed. For example, in some embodiments, a computer system may implement one or more nodes of a control plane or control system, and the persistent storage may include SSDs attached to the server nodes. Multiple computer systems may share the same persistent storage device or may share a pool of persistent storage devices, where the devices in the pool represent the same or different storage technologies.

[0023] A computer system includes one or more system memories that may store code / instructions and data accessible by a processor. The memory capabilities of a system may include multiple levels of memory and memory caches in the system, designed to swap information in memory based on, for example, access speed. Interleaving and swapping may be extended to persistent storage in virtual memory implementations. Technologies used to implement memory may include, by way of example, static random access memory (RAM), dynamic RAM, read-only memory (ROM), non-volatile memory, or flash type memory. As with persistent storage, multiple computer systems may share the same system memory, or may share a pool of system memory. The one or more system memories may include program instructions executable by the processor to perform the routines described herein. In various embodiments, the program instructions may be encoded in binary, assembly language, any interpreted language such as Java, compiled language such as C / C++, or any combination thereof, although the specific languages ​​shown herein are merely illustrative. In some embodiments, the program instructions may implement multiple separate client, server nodes, and / or other components.

[0024] In some implementations, the program instructions may include instructions executable to implement an operating system (not shown), which may be any of a variety of operating systems, such as UNIX, LINUX, Solaris, MacOS, or Microsoft Windows. Any or all of the program instructions may be provided as a computer program product or software, which may include a non-transitory computer-readable storage medium having stored thereon instructions that may be used to program a computer system (or other electronic device) to perform a process according to various implementations. A non-transitory computer-readable storage medium may include any mechanism for storing information in a form (e.g., software, processing application) readable by a machine (e.g., a computer). Generally speaking, a non-transitory computer-accessible medium may include a computer-readable storage medium or memory medium, such as a magnetic or optical medium, e.g., a disk or DVD / CD-ROM, coupled to a computer system via an I / O interface. A non-transitory computer-readable storage medium may also include any volatile or non-volatile medium, such as RAM or ROM, that may be included in some embodiments of a computer system as a system memory or another type of memory. In other implementations, program instructions may be communicated using optical, acoustic, or other forms of propagated signals (e.g., carrier waves, infrared signals, digital signals, etc.) conveyed over a communication medium, such as a network and / or wired or wireless link, as may be implemented via a network interface. A network interface may be used to interface with other devices, which may include other computer systems or any type of external electronic device.Generally, system memory, persistent storage, and / or remote storage accessible on other devices over a network may store data blocks, replicas of data blocks, metadata associated with the data blocks and / or their state, database configuration information, and / or any other information usable in performing the routines described herein.

[0025] In certain implementations, the I / O interface may coordinate I / O traffic between the processor, the system memory, and any peripheral devices in the system, including through a network interface or other peripheral interface. In some embodiments, the I / O interface may perform any necessary protocol, timing, or other data conversion to convert data signals from one component (e.g., the system memory) into a format suitable for use by another component (e.g., the processor). In some embodiments, the I / O interface may include support for devices attached through various types of peripheral buses, such as, for example, variations of the Peripheral Component Interconnect (PCI) bus standard or the Universal Serial Bus (USB) standard. Also, in some embodiments, some or all of the functionality of the I / O interface, such as the interface to the system memory, may be incorporated directly into the processor.

[0026] The network interface may enable data to be exchanged between the computer system and other devices attached to the network, such as other computer systems (which may implement one or more storage system server nodes, primary nodes, read-only nodes, and / or clients of the database system described herein). Additionally, the I / O interface may enable communication between the computer system and various I / O devices and / or remote storage. In some embodiments, the input / output devices may include one or more display terminals, keyboards, keypads, touchpads, scanning devices, voice or optical recognition devices, or any other devices suitable for inputting or retrieving data by one or more computer systems. The input / output devices may be directly connected to a particular computer system or may be generally connected to multiple computer systems in a cloud or grid computing environment or other system involving multiple computer systems. Multiple input / output devices may be in communication with the computer system or may be distributed on various nodes of a distributed system that includes the computer system. The user interfaces described herein may be visible to a user using various types of display screens, which may include CRT displays, LCD displays, LED displays, and other display technologies. In some implementations, input may be received through a display using touch screen technology, while in other implementations, input may be received through a keyboard, mouse, touchpad, or other input technology, or any combination of these technologies.

[0027] In some embodiments, similar input / output devices may be separate from the computer system and may interact with one or more nodes of a distributed system including the computer system through a wired or wireless connection, such as via a network interface. The network interface may generally support one or more wireless networking protocols (e.g., Wi-Fi / IEEE 802.11, or another wireless networking standard). The network interface may support communication over any suitable wired or wireless general data network, such as, for example, other types of Ethernet networks. Additionally, the network interface may support communication over a telecommunications / telephony network, such as an analog voice network or a digital fiber communications network, over a storage area network, such as a Fibre Channel SAN, or over any other suitable type of network and / or protocol.

[0028] Any of the embodiments of the distributed system described herein, or any of its components, may be implemented as one or more network-based services in a cloud computing environment. For example, read-write and / or read-only nodes in a database hierarchy of a database system may present database services and / or other types of data storage services employing the distributed storage system described herein to clients as network-based services. In some embodiments, the network-based services may be implemented by software and / or hardware systems designed to support interoperable machine-to-machine interaction over a network. A web service may have an interface described in a machine-processable format, such as the Web Services Description Language (WSDL). Other systems may interact with the network-based service in a manner that is specified by the description of the network-based service's interface. For example, the network-based service may define various operations that other systems may invoke and may define specific application programming interfaces (APIs) that other systems may be expected to conform to when requesting the various operations.

[0029] In various embodiments, a network-based service may be requested or invoked through the use of a message that includes parameters and / or data associated with a network-based service request. Such a message may be formatted according to a particular markup language, such as Extensible Markup Language (XML), and / or may be encapsulated using a protocol, such as Simple Object Access Protocol (SOAP). To perform a network-based service request, a network-based service client may assemble a message that includes the request and communicate the message to an addressable endpoint (e.g., a Uniform Resource Locator (URL)) that corresponds to the web service using an Internet-based application-layer transport protocol, such as HyperText Transfer Protocol (HTTP). In some embodiments, a network-based service may be implemented using Representational State Transfer (REST) ​​techniques rather than message-based techniques. For example, a network-based service implemented according to REST techniques may be invoked through parameters included in an HTTP method, such as PUT, GET, or DELETE.

[0030] Unless otherwise stated, all technical and scientific terms used herein have the same meaning as commonly understood by one of ordinary skill in the art to which this invention belongs. Although any methods and materials similar or equivalent to those described herein can also be used in the practice or testing of the present invention, a limited number of exemplary methods and materials are described herein. It will be apparent to those skilled in the art that many more modifications are possible without departing from the inventive concepts herein.

[0031] All terms used herein should be interpreted in the broadest possible manner consistent with the context. When groupings are used herein, it is intended that all individual members of the group, and all possible combinations and subcombinations of the group, are included individually. When ranges are stated herein, it is intended that the ranges include all subranges and individual points within the range. All references cited herein are hereby incorporated by reference unless inconsistent with the present disclosure of this specification.

[0032] The present invention has been described with reference to certain preferred and alternative embodiments that are intended to be illustrative only and not limiting of the full scope of the invention as set forth in the appended claims.

Claims

1. 1. A federated query method comprising: receiving a query statement, a schema for each of a plurality of datasets, and at least one privacy policy in a query engine; Parsing the query sentence into a structured form; performing a structural transformation of the structured form of the query sentence based on the privacy policy to conform to a set of privacy requirements in the privacy policy to generate a logical query plan; generating at least one physical plan for a query in each of the plurality of datasets based on the schema and the logical plan for each of the plurality of datasets; translating the physical plan into a query dialect appropriate for the data set in the data warehouse to which each of the plurality of worker nodes is proximate to generate a translated query at each of a plurality of worker nodes proximate to one of a plurality of data warehouses each of which contains one of the plurality of data sets; 23. A method of federated query comprising the steps of:

2. 2. The federated query method of claim 1 , wherein parsing the query statement into a structured form comprises parsing the query statement into a tree structure.

3. 3. The federated query method of claim 2, wherein parsing the query statement into a tree structure comprises parsing the query statement into an abstract syntax tree.

4. 10. The federated query method of claim 1, further comprising receiving a location for each of the plurality of datasets.

5. 2. The federated query method of claim 1 , wherein the at least one physical plan comprises a plurality of physical plans.

6. 6. The federated query method of claim 5, wherein each of the plurality of physical plans is applied to one of the plurality of data sets.

7. 6. The federated query method of claim 5, further comprising: selecting a best physical plan from the plurality of physical plans.

8. 2. The federated query method of claim 1 , further comprising: executing each of the translated queries against one of the plurality of datasets.

9. 10. The federated query method of claim 8, further comprising fetching and returning to the query engine results of executing each of the translated queries against each of the plurality of datasets.

10. 10. The federated query method of claim 9, wherein the query engine comprises a coordinator, and further comprising fetching and returning results of executing each of the translated queries against each of the plurality of datasets to the coordinator.

11. 11. The federated query method of claim 10, further comprising aggregating at the coordinator results of executing each of the translated queries against each of the plurality of datasets.

12. 12. The federated query method of claim 11, further comprising applying a set of additional privacy constraints to the aggregated results.

13. 2. The federated query method of claim 1 , wherein the privacy policy includes a first privacy restriction for a first dataset in the plurality of datasets and a second privacy restriction for a second dataset in the plurality of datasets, the first privacy restriction and the second privacy restriction being not the same.

14. 14. The federated query method of claim 13, wherein the privacy policy includes a query threshold.

15. 14. The federated query method of claim 13, wherein the privacy policy includes restrictions on data movement of the portion of the data in at least one of the multiple datasets.

16. 16. The federated query method of claim 15, wherein the portion of the data in at least one of the multiple datasets is a column in at least one of the multiple datasets.

17. 14. The federated query method of claim 13, wherein the privacy policy comprises application of a cryptographic long-term key to a binding between at least two of the plurality of data sets.

18. 14. The federated query method of claim 13, wherein the privacy policy comprises application of data warehouse native privacy-enhancing features between joins of at least two of the multiple datasets.

19. 2. The federated query method of claim 1 , wherein performing a structural transformation of the structured form of the query statements to comply with a set of privacy requirements in the privacy policy to generate a logical query plan comprises suppressing rows in the multiple datasets that are below a threshold.

20. 20. The federated query method of claim 19, wherein the threshold varies between at least two of the datasets in the plurality of datasets.

Images