Data query method, acceleration apparatus, computing device and storage medium

Abstract

The present application relates to the technical field of databases. Disclosed are a data query method, an acceleration apparatus, a computing device and a storage medium. The method is applied to the acceleration apparatus of the computing device. The method comprises: performing vectorized execution on a data query request, and generating vector dictionaries and vector indexes on the basis of a fact table, dimension tables and join keys indicated by the data query request, thus realizing multi-table join queries. The vector dictionaries and the vector indexes have small sizes, and therefore do not occupy excessive storage space. Moreover, the whole process is executed by the hardware acceleration apparatus, offloading the computing power of a processor in the computing device, thereby improving the overall query performance.

Description

Data query method, acceleration device, computing equipment and storage medium

[0001] This application claims priority to Chinese patent application number 202311680630.3, filed on December 7, 2023, entitled “Data query method, acceleration device, computing device and storage medium”, the entire contents of which are incorporated by reference into this application. Technical Field

[0002] The present application relates to the field of database technology, and in particular to a data query method, an acceleration device, a computing device, and a storage medium. Background Art

[0003] In the field of database technology, using a star or snowflake join architecture to store data can effectively save database storage space. Specifically, large amounts of fact data are stored in a fact table, and dimensional information related to the fact data is stored in a dimension table. The fact table and dimension tables are linked using the foreign keys of the fact table and the primary keys of the dimension tables. During business analysis, data queries can be used to join the dimension and fact tables, aggregating the data from the dimension and fact tables to provide relevant information to users. For example, a data query request might instruct the user to aggregate the sales figures in the fact table by the two dimensions of year and customer region.

[0004] In related technologies, in star or snowflake join architectures, data query requests typically specify a join key, which is used to determine which row in the dimension table is aggregated with a specified row in the fact table. Currently, hash join algorithms are commonly used to execute such data query requests. For example, the table with the smaller amount of data is selected, and a hash table is constructed in memory based on the join key of the table. Then, based on each hash value in the hash table, the other table is traversed to find rows with the same join key value. These rows are then merged to obtain the data query result.

[0005] However, in the above method, building a hash table will take up a lot of memory space. Moreover, the processor needs to continuously access the hash table in the memory, resulting in low data query efficiency and affecting the overall performance of the database.

[0006] Summary of the Invention

[0007] The embodiments of the present application provide a data query method, an acceleration device, a computing device, and a storage medium, which can improve data query efficiency.

[0008] In a first aspect, the present application provides a data query method, applied to an acceleration device of a computing device, the method comprising:

[0009] Obtaining a query vector of a data query request, wherein the data query request indicates querying data from a fact table and at least one dimension table based on a join key, wherein at least one foreign key of the fact table is associated with a primary key of the at least one dimension table, and the at least one dimension table is used to store dimension information of fact data in the fact table;

[0010] generating a vector dictionary for each dimension table based on the query vector and a vector corresponding to the at least one dimension table, wherein the vector dictionary for each dimension table includes a join key value of the join key in the dimension table that is related to the query vector;

[0011] Based on at least one foreign key of the fact table, the primary key of the at least one dimension table, and a vector dictionary of each dimension table, traverse the at least one dimension table to generate a vector index, where the vector index includes a fact table identifier of the join key value in the fact table;

[0012] The fact table is queried based on the vector index to obtain a data query result.

[0013] Through the above method, data query requests are vectorized and executed. Based on the fact table, dimension table, and join key indicated by the data query request, a vector dictionary and vector index are generated to implement multi-table join queries. On the one hand, due to the small size of the vector dictionary and vector index, they do not occupy too much storage space. On the other hand, the entire process is executed by a hardware acceleration device, which offloads the computing power of the processor in the computing device and can improve overall query performance.

[0014] In some embodiments, the at least one dimensional table is stored in the acceleration device.

[0015] In some embodiments, the method further comprises at least one of the following:

[0016] Storing the vector dictionary of each dimension table in the acceleration device;

[0017] The vector index is stored in the acceleration device.

[0018] In some embodiments, generating a vector dictionary for each dimension table based on the query vector and the vector corresponding to the at least one dimension table includes:

[0019] Based on the first dimension information indicated by the query vector, determining a first connection key value related to the first dimension information from a first dimension table where the first dimension information is located;

[0020] A vector dictionary of the first dimensional table is generated based on the first connection key value.

[0021] In some embodiments, traversing the at least one dimension table to generate a vector index based on the at least one foreign key of the fact table, the primary key of the at least one dimension table, and the vector dictionary of each dimension table includes:

[0022] Generate a join table for each dimension table based on at least one foreign key of the fact table, the primary key of the at least one dimension table, and a vector dictionary of each dimension table, wherein the join table for each dimension table includes the join key value in the dimension table and a fact table identifier of the join key value in the fact table;

[0023] The vector index is generated based on the same fact table identifier in the join table of each dimension table.

[0024] In some embodiments, generating a join table for each dimension table based on at least one foreign key of the fact table, a primary key of at least one dimension table, and a vector dictionary of each dimension table includes:

[0025] traversing the first dimensional table based on the vector dictionary of the first dimensional table and the first primary key of the first dimensional table to determine a first primary key value corresponding to a first join key value in the first dimensional table, where the first join key value is a join key value determined from the first dimensional table based on the first dimension information indicated by the query vector;

[0026] Determine a first fact table identifier of the fact table containing the first join key value based on the first primary key value and a first foreign key of the fact table associated with the first primary key;

[0027] A join table of the first dimension table is generated based on the first join key value and a first fact table identifier of the first join key value in the fact table.

[0028] In some embodiments, querying the fact table based on the vector index to obtain a data query result includes:

[0029] Based on the vector index, the column where the join key is located in the fact table is queried to obtain the data query result.

[0030] In some embodiments, the data query request indicates querying data from a fact table and at least one dimension table based on a join key and a metric key; and querying the fact table based on the vector index to obtain a data query result includes:

[0031] Based on the vector index, the column where the join key and the column where the metric key are located in the fact table are queried to obtain the data query result.

[0032] In some embodiments, the data query request indicates querying data from a fact table and at least one dimension table based on a join key and a group key, and the method further includes:

[0033] A group code is generated based on the group key, and the group code is mapped to a subscript of a one-dimensional array corresponding to the group key in the data query result.

[0034] In some embodiments, the acceleration device is at least one of a system-on-chip (SOC), a field programmable gate array (FPGA), a graphics processor (GPU), an application-specific integrated circuit (ASIC), an artificial intelligence (AI) chip, or a data processor (DPU).

[0035] In a second aspect, the present application provides an acceleration device, which is configured on a computing device, and the acceleration device includes a processing unit and a storage unit, wherein the processing unit is used to execute the data query method provided in the aforementioned first aspect or any optional method of the first aspect, and the storage unit is used to provide storage space for the acceleration device.

[0036] In a third aspect, the present application provides a computing device, which includes a host and an acceleration device, wherein the host is used to send a data query request to the acceleration device, and the acceleration device is used to receive the data query request and implement the data query method provided in the first aspect or any optional method of the first aspect.

[0037] In a fourth aspect, the present application provides a computer-readable storage medium for storing at least one program code, wherein the at least one program code is used to execute the data query method provided in the first aspect or any optional embodiment of the first aspect. The storage medium includes, but is not limited to, volatile memory, such as random access memory, and non-volatile memory, such as flash memory, a hard disk drive (HDD), or a solid state drive (SSD).

[0038] In a fifth aspect, the present application provides a computer program product that, when executed on an acceleration device of a computing device, causes the acceleration device to execute the data query method provided in the first aspect or any optional embodiment of the first aspect. The computer program product may be a software installation package, which can be downloaded and executed on the acceleration device when the functions of the acceleration device are to be implemented. BRIEF DESCRIPTION OF THE DRAWINGS

[0039] FIG1 is a schematic diagram of an implementation environment provided by an embodiment of the present application;

[0040] FIG2 is a schematic diagram of the hardware structure of an acceleration device provided in an embodiment of the present application;

[0041] FIG3 is a schematic diagram of the execution logic of an acceleration device provided in an embodiment of the present application;

[0042] FIG4 is a flow chart of a data query method provided in an embodiment of the present application;

[0043] FIG5 is a schematic diagram of generating a vector dictionary provided in an embodiment of the present application;

[0044] FIG6 is a schematic diagram of generating a vector index provided in an embodiment of the present application;

[0045] FIG7 is a schematic diagram of a vector index-based fact table query provided in an embodiment of the present application;

[0046] FIG8 is a schematic diagram of another data query method provided in an embodiment of the present application;

[0047] FIG9 is a schematic diagram of a vectorized execution of a data query method provided in an embodiment of the present application;

[0048] FIG10 is a schematic diagram showing a comparison of experimental results provided in an embodiment of the present application;

[0049] FIG11 is a schematic diagram of another data query method provided in an embodiment of the present application. DETAILED DESCRIPTION

[0050] In order to make the purpose, technical solutions and advantages of this application clearer, the implementation methods of this application will be further described in detail below with reference to the accompanying drawings. It should be noted that the information (including but not limited to user device information, user personal information, etc.), data (including but not limited to data used for analysis, stored data, displayed data, etc.) and signals involved in this application are all authorized by the user or fully authorized by all parties, and the collection, use and processing of relevant data need to comply with the relevant laws, regulations and standards of the relevant countries and regions. For example, the fact tables, dimension tables, etc. involved in this application are all obtained with full authorization.

[0051] For ease of understanding, the key terms and key concepts involved in this application are explained below.

[0052] A fact table is a table used to store business fact data. It typically contains numerical data related to business processes, such as sales, order quantity, and inventory levels. Each row in a fact table represents a specific business fact, and each column is a metric or indicator related to that fact. Fact tables typically contain one or more foreign keys to establish relationships with dimension tables.

[0053] A dimension table is a table used to store contextual information describing facts. It typically includes dimensional information related to the fact data in the fact table, such as time, location, product, and customer. Each row in a dimension table represents a dimension value, and each column represents an attribute related to that dimension. A dimension table typically contains a primary key, which is used to establish a relationship with the fact table.

[0054] A star join is a multidimensional data connection architecture consisting of a fact table and at least one dimension table. The fact table is the core, and each dimension table has a dimension as its primary key. The primary keys of all these dimensions are combined to form the primary key of the fact table. After organizing data in this way, the fact data in the fact table can be aggregated according to different dimensions (partial or complete fact table primary keys) to perform operations such as summaries, averages, counts, and percentages.

[0055] Snowflake join is a multidimensional data connection architecture consisting of a fact table and at least one dimension table. There are one or more dimension tables that are not directly connected to the fact table but are connected to the fact table through other dimension tables.

[0056] An operator (OP) refers to a computing unit or computing function running on a computing device.

[0057] The following is an introduction to the application scenarios and implementation environment involved in this application.

[0058] The technical solutions provided by the embodiments of the present application can be applied to data analysis scenarios such as databases and big data, such as data query scenarios involving operators such as multi-table joins and multi-table aggregations. They can also be applied to scenarios involving vector retrieval operations such as storage, networks, and clouds. It should be understood that computer storage is embodied in a pyramid shape, with a small top-level space but fast processing speed. The cache of the central processing unit (CPU) is the first place the CPU gets data. Similarly, multi-core processing also accelerates program execution. However, the data inconsistency problem caused by multi-threading requires the introduction of a thread synchronization mechanism. In related technologies, adjusting software performance to the underlying hardware is a very effective method to improve the overall performance of the database. Currently, in-memory databases are combined with algorithms set by hardware. For example, they are divided into hardware-oblivious algorithms and hardware-conscious algorithms. The former is applied to, for example, non-partitioned hash join algorithms, while the latter is for partitioned join algorithms. Different algorithms have different sensitivities to the CPU cache and the number of cores. Compared with the hash join algorithm, the vector join algorithm has improvements in both space and efficiency. Compared with the hash join algorithm, the vector join algorithm has significant advantages in constructing vector arrays and join queries. Based on this, the present application provides a hardware acceleration device that supports vector connections, which is configured on a computing device and can accelerate the execution process of multi-table connections and multi-table aggregation operators in data analysis scenarios such as databases and big data.

[0059] The implementation environment of this application is introduced below with reference to FIG1 .

[0060] Figure 1 is a schematic diagram of an implementation environment provided by an embodiment of the present application. As shown in Figure 1, the implementation environment includes a computing device 100, which includes a host 101 and an acceleration device 102, and the host 101 and the acceleration device 102 are communicatively connected.

[0061] Host 101 is a device used to run a database and can provide users with services such as data query and data analysis. In the embodiment of the present application, host 101 can control acceleration device 102 to execute operators such as multi-table joins and multi-table aggregations in response to database data query requests. This process can also be understood as loading data query tasks into acceleration device 102 for execution. In addition, the number of hosts 101 can be one or more, and this application does not limit this.

[0062] The acceleration device 102 is used to provide computing power for the database to accelerate the execution process of the database operator, that is, to execute the data query method provided in this application. For example, the acceleration device 102 is at least one of a system on chip (SOC), a field-programmable gate array (FPGA), a graphics processing unit (GPU), an application-specific integrated circuit (ASIC), an artificial intelligence (AI) chip or a data processing unit (DPU), and this application does not limit this. Schematically, the acceleration device 102 queries the corresponding data from the fact table and at least one dimension table according to the data query request sent by the host 101, obtains the data query result, and returns the data query result to the host 101. In addition, the number of acceleration devices 102 can be one or more, and this application does not limit this.

[0063] Schematically, the host 101 and the acceleration device 102 are communicatively connected via a peripheral component interconnect express (PCIe) link, and the host 101 exchanges data with the acceleration device 102 via the PCIe link. For example, the computing device 100 can be an independent physical server, or a server cluster or distributed file system consisting of multiple physical servers, or a cloud server that provides basic cloud computing services such as cloud services, cloud databases, cloud computing, cloud functions, cloud storage, network services, cloud communications, middleware services, domain name services, security services, content delivery networks (CDNs), and big data and artificial intelligence platforms.

[0064] Taking computing device 100 as a cloud server as an example, the computing device can also be referred to as a cloud platform (short for cloud computing platform), which refers to services based on hardware and software resources, providing computing, networking, and storage capabilities. Through the network "cloud," massive amounts of data are processed and analyzed remotely before being returned to users. Cloud platforms offer advantages such as large-scale, distributed, virtualized, highly available, scalable, on-demand services, and security. Cloud platforms enable the rapid provisioning and release of configurable computing resources with minimal management overhead and interaction complexity between users and service providers.

[0065] In addition, the networks involved above include, but are not limited to, any combination of data center networks, storage area networks (SANs), local area networks (LANs), metropolitan area networks (MANs), wide area networks (WANs), mobile, wired or wireless networks, dedicated networks or virtual private networks. In some implementations, technologies and / or formats including hypertext markup language (HTML) and extensible markup language (XML) are used to represent data exchanged over the network. In addition, conventional encryption technologies such as secure sockets layer (SSL), transport layer security (TLS), virtual private networks (VPNs), and internet protocol security (IPsec) can be used to encrypt all or part of the links. In other embodiments, customized and / or dedicated data communication technologies can be used to replace or supplement the above data communication technologies.

[0066] The structure of the acceleration device 102 is introduced below.

[0067] Figure 2 is a schematic diagram of the hardware structure of an acceleration device provided in an embodiment of the present application. As shown in Figure 2, the acceleration device 102 includes a communication interface 1021, a processing unit 1022, a storage unit 1023, and a bus 1024. The communication interface 1021, the processing unit 1022, and the storage unit 1023 are connected to each other via the bus 1024.

[0068] The communication interface 1021 is used to provide program instructions and / or data. The communication interface 1021 includes a PCIe communication interface, other common peripheral interfaces, etc., which are not limited in this application. For example, when the acceleration device 102 is used as an accelerator card for the host 101, data exchange is achieved with the host 101 through the PCIe communication interface. For another example, the acceleration device 102 communicates with other devices or communication networks through the peripheral interface.

[0069] The processing unit 1022 is used to execute the data query method provided in this application, such as the core of a central processing unit (CPU), or a control unit (CU), which is not limited in this application. Schematically, the processing unit 1022 can also be understood as a vector control unit (also known as a VAQ_CTRL unit). In some embodiments, the processing unit 1022 includes an arithmetic logic unit (ALU) and a vector function unit (VFU), etc., for interacting with the storage unit 1023. By deploying the processing unit 1022, logical control for docking with real database business scenarios is achieved, and it can be easily docked into the database operator execution process, minimizing software modification.

[0070] The storage unit 1023 is used to provide storage space for the acceleration device 102. For example, the storage unit 1023 is an advanced scratch pad memory (ASPM), a double data rate memory (DDR), a static random-access memory (SRAM), or other types of dynamic storage devices capable of storing information and instructions, or includes any other medium capable of carrying or storing desired program code in the form of instructions or data structures and accessible by a computer, but is not limited thereto. For example, taking the storage unit 1023 as an ASPM, it can be set to a single port size of 16 KB and 8 channel banks, which is not limited in this application.

[0071] The bus 1024 may include a path for transmitting information between various components of the acceleration device 102 (eg, the communication interface 1021 , the processing unit 1022 , and the storage unit 1023 ).

[0072] It should be noted that the above FIG. 2 is only a hardware structure diagram provided by this application that can be configured as the above-mentioned acceleration device 102. In some embodiments, the acceleration device 102 may also include other components to achieve more functions, and this application is not limited to this.

[0073] The hardware execution logic of the acceleration device 102 is introduced below with reference to Figure 3. Figure 3 is a schematic diagram of the execution logic of an acceleration device provided by an embodiment of the present application. As shown in Figure 3, the processing unit 1022 (also known as the VAQ_CTRL unit) is used to provide logical control for interacting with the business flow, including sub-units such as DecoCtrl, DataReq, DataPack and InstExec, among which the DateReq and DataPack sub-units are used to interact with the ASPM storage unit to implement data request and acquisition operations, and the InstExec sub-unit interacts with the arithmetic logic unit ALU, sends the data to be calculated to the ALU unit, and performs the calculation through the ALU. The ALU is a combinational logic digital circuit that can perform arithmetic operations or bit operations on binary integers. Figure 3 shows a sum operation with idx (index) = 3 as an example. The external input idx (read request to ASPM) constructs the bit 1001 corresponding to 3 from the index (CMPEQ in the figure is a SIMD comparison instruction), combines it with the Value table, and obtains 32 (REDUCTION in the figure is a SIMD reduction instruction, and OPN is a data block instruction). 32 is then written to ASPM (write request to ASPM). The Prev (previous) stage can be understood as the historical visibility determination stage.

[0074] The following is an introduction to the data query method provided by this application.

[0075] Based on the foregoing introduction, it can be seen that the technical solutions provided by the embodiments of this application can be applied to data analysis scenarios such as databases and big data. Taking database services as an example, users initiate queries through structured query language (SQL). After the database's grammatical lexical analysis generates an execution plan and is optimized by the optimizer, it enters the execution phase. At this time, it will be executed according to the execution operator sequence according to the optimized execution plan. The scenarios targeted by this application for acceleration are operators involving multi-table joins and result aggregation, such as aggregate operators and join operators.

[0076] The data query method provided by this application is described below with reference to FIG4 . FIG4 is a flow chart of a data query method provided by an embodiment of this application. As shown in FIG4 , the method is applied to an acceleration device of a computing device and includes the following steps 401 to 404 .

[0077] 401. An acceleration device obtains a query vector of a data query request, where the data query request indicates querying data from a fact table and at least one dimension table based on a join key, where at least one foreign key of the fact table is associated with a primary key of at least one dimension table, and the at least one dimension table is used to store dimensional information of fact data in the fact table.

[0078] In an embodiment of the present application, the accelerator is in communication with the host. The host responds to a user's data query request by sending the data query request to the optimizer. The optimizer maps the data query request into a query vector and sends the query vector to the accelerator. In some embodiments, the accelerator may also map the data query request into a query vector, which is not limited in this application. By converting the data query request into vectorized execution, overall performance can be effectively improved.

[0079] Based on the previous description of fact tables and dimension tables, we can see that a fact table includes at least one foreign key, and each dimension table has a dimension that serves as a primary key. These primary keys combine to form the primary key of the fact table, which is associated with at least one foreign key in the fact table. Furthermore, this application does not impose a limit on the number of dimension tables associated with a fact table.

[0080] The following example illustrates the above data query request based on the Star Schema Benchmark (SSB), an international standard star schema test set, and with reference to the following SQL.

[0081] "SELECT c.nation, s.nation, d.year, sum(lo.revenue)as revenue

[0082] FROM customer AS c, lineorder AS lo, supplier AS s, dwdate AS d

[0083] WHERE lo.custkey=c.custkey

[0084] AND lo.suppkey=s.suppkey

[0085] AND lo.orderdate=d.dateid

[0086] AND c.region='A'

[0087] AND s.region = 'A'

[0088] AND d.year>=1992and d.year<=1997

[0089] GROUP BY c.nation,s.nation,d.year

[0090] ORDER BY d.year asc, revenue desc;”

[0091] In the above query, lineorder is the product order details table, abbreviated as lo; customer is the customer information table, abbreviated as c; supplier is the supplier information table, abbreviated as s; and date is the date table, abbreviated as d. The fact table is the product order details table lo, and the dimension tables include the customer information table c, the supplier information table s, and the date table d. These dimension tables store dimensional information for the fact data in fact table lo. The customer code custkey column, the supplier code suppkey column, and the date identifier dateid column serve as join keys, the lo.revenue column serves as the measure key, and the c.nation column, s.nation column, and d.year column serve as grouping keys. This data query request indicates to query the total order revenue that meets the specified conditions from the fact table lo and multiple dimension tables c, s, and d based on custkey, suppkey, and dateid, where the specified conditions are that the region region = 'A' in dimension table c, the region region = 'A' in dimension table s, and the year year in dimension table d is between 1992 and 1997. The query results are grouped by the grouping key, and the years are sorted in ascending order and the revenues are sorted in descending order.

[0092] 402. The acceleration device generates a vector dictionary for each dimension table based on the query vector and a vector corresponding to at least one dimension table, where the vector dictionary for the dimension table includes a connection key value of a connection key in the dimension table that is related to the query vector.

[0093] In an embodiment of the present application, at least one dimension table can be stored in the acceleration device. In this way, the acceleration device does not need to obtain the dimension table from the host, thereby improving data query efficiency. Of course, the dimension table can also be stored in the host memory and obtained from the host by the acceleration device to save storage space of the acceleration device. Alternatively, when there are multiple dimension tables, some of the dimension tables can be stored in the acceleration device and other dimension tables can be stored in the host memory. This application does not limit this.

[0094] Schematically, in this step, for any dimension table (hereinafter referred to as the first dimension table), the acceleration device generates a vector dictionary for the dimension table based on the query vector and the vector corresponding to the dimension table (also referred to as the dimension vector). Taking the first dimension table as an example, this step includes the following steps A1 and A2:

[0095] Step A1: Based on the first dimension information indicated by the query vector, determine a first connection key value related to the first dimension information from a first dimension table where the first dimension information is located.

[0096] The acceleration device traverses the first dimension table where the first dimension information is located based on the first dimension information and the connection key column of the first dimension table, and determines a first connection key value that satisfies the first dimension information.

[0097] Step A2: Generate a vector dictionary of the first dimension table based on the first connection key value.

[0098] The acceleration device generates a vector dictionary of the first dimension table based on the first connection key value and a dimension table identifier of the first connection key value in the first dimension table, wherein the dimension table identifier is an object identifier (OID) used to uniquely identify a row in the dimension table.

[0099] Schematically, referring to Figure 5, Figure 5 is a schematic diagram of a vector dictionary generation provided by an embodiment of the present application. In conjunction with the SQL example in the aforementioned step 401, taking the first dimension information as c.region = 'A' as an example, the first dimension table is the customer information table c, and the connection key column corresponding to the first dimension table is the custkey column. Based on this, the acceleration device traverses the first dimension table and determines the first connection key values ​​in the first dimension table, namely custkey "1" and custkey "3", under the premise of satisfying c.region = 'A'. Combined with the dimension table OIDs corresponding to these two first connection key values, a vector dictionary of the first dimension table, namely "Inter_C", is generated. This process can also be understood as a process of compressing the first dimension table. In addition, Figure 5 also shows the process of generating vector dictionaries for the supplier information table s and the date table d. The principle is the same as that of the customer information table c, wherein the vector dictionary of the supplier information table s is "Inter_S" and the vector dictionary of the date table d is "Inter_D", which will not be repeated here.

[0100] In some embodiments, the acceleration device stores the vector dictionary of each dimension table in the acceleration device, such as a storage unit ASPM of the acceleration device, to facilitate subsequent fast access and improve data query efficiency.

[0101] 403. The acceleration device traverses at least one dimension table based on at least one foreign key of the fact table, the primary key of at least one dimension table, and the vector dictionary of each dimension table to generate a vector index, where the vector index includes a fact table identifier of the join key value in the fact table.

[0102] In this embodiment of the present application, the fact table identifier is an object identifier (OID) that uniquely identifies a row in the fact table. Since the vector dictionary for each dimension table is obtained in step 402, in this step, the dimension table and the fact table can be linked based on the vector dictionary of each dimension table and the relationship between the dimension table and the fact table to construct a vector index.

[0103] In some embodiments, the acceleration device stores the vector index in the acceleration device. Since the vector index is small in size, it does not take up too much storage space. Moreover, the subsequent acceleration device can query the fact table based on the vector index stored in the local acceleration device, which can effectively improve data query efficiency.

[0104] Schematically, this step includes the following steps B1 and B2:

[0105] Step B1: Generate a join table for each dimension table based on at least one foreign key of the fact table, at least one primary key of the dimension table, and a vector dictionary of each dimension table. The join table of the dimension table includes a join key value in the dimension table and a fact table identifier of the join key value in the fact table.

[0106] For any dimension table (hereinafter referred to as the first dimension table), step B1 includes the following steps:

[0107] Step B11: Based on the vector dictionary of the first dimension table and the first primary key of the first dimension table, traverse the first dimension table to determine the first primary key value corresponding to the first connection key value in the first dimension table, where the first connection key value is a connection key value determined from the first dimension table based on the first dimension information indicated by the query vector.

[0108] Among them, the acceleration device traverses the first dimension table based on the first connection key value in the vector dictionary of the first dimension table and the first primary key of the first dimension table, and determines the first primary key value corresponding to the first connection key value from the primary key column of the first dimension table, wherein the first connection key value refers to the aforementioned step 402 and will not be repeated here.

[0109] Step B12: Determine a first fact table identifier of the first join key value in the fact table based on the first primary key value and the first foreign key of the fact table associated with the first primary key.

[0110] The acceleration device obtains corresponding data of the fact table for matching based on the first primary key value and the first foreign key of the fact table, and determines the first fact table identifier of the first connection key value in the fact table.

[0111] Step B13: Generate a join table for the first dimension table based on the first join key value and the first fact table identifier of the first join key value in the fact table.

[0112] Step B2: Generate a vector index based on the same fact table identifier in the join table of each dimension table.

[0113] Schematically, the above steps B1 and B2 refer to Figure 6, which is a schematic diagram of a vector index generation provided by an embodiment of the present application. As shown in Figure 6, in combination with the SQL example in the above step 401, taking the first dimension table as the customer information table c as an example, the connection key column corresponding to the first dimension table is the custkey column, the vector dictionary of the first dimension table is "Inter_C", and the primary key column of the first dimension table is the custkey column. The acceleration device traverses the first dimension table and determines that the first primary key values ​​corresponding to the first connection key values ​​custkey "1" and custkey "3" are 3, 3, 1, 1, and 3 respectively. Then, based on the first primary key value and the first foreign key of the fact table (i.e., the custkey column), the acceleration device determines the first fact table identifier of the first connection key value in the fact table, which are 1, 2, 4, 6, and 7 respectively. Based on the first join key values ​​(also the first primary key values ​​in this example) 3, 3, 1, 1, 3 and the first fact table identifiers 1, 2, 4, 6, 7, a join table for the first dimension table, "JI_LC" ​​(short for Join-lineorder-customer), is generated. Figure 6 also illustrates the process of generating join tables for the supplier information table s and the date table d. The principles are similar to those for the customer information table c. The join table for the supplier information table s is "JI_LS" (short for Join-lineorder-supplier), and the join table for the date table d is "JI_LD" (short for Join-lineorder-date). These details are omitted here. "VOID" in the figure stands for Virtual-OID, which represents the fact table identifier.

[0114] Continuing with Figure 6, after obtaining the join table for each dimension table, a vector index, "JI_LCSD" (short for Join-lineorder-customer-supplier-date), is generated based on the identical fact table identifier in the join table for each dimension table. Since Figure 6 is an example based on a star-shaped test set, the vector index generated here satisfies the star join condition. It should be understood that the data query method provided in this application is also applicable to other join architectures, such as the snowflake join architecture (which can be understood as a complex form of star join architecture), and this application does not limit this.

[0115] Furthermore, in some embodiments, when a data query request indicates a query for data from a fact table and at least one dimension table based on a join key and a grouping key, the acceleration device further performs the following steps: generating a grouping code based on the grouping key, and mapping the grouping code to a subscript of a one-dimensional array corresponding to the grouping key. Specifically, referring to the vector index "JI_LCSD" in FIG6 , the values ​​1 and 4 in the second column of this vector index indicate that, among rows 1-7 of the fact table, only rows 1 and 4 meet the condition, and the value in the first column is the grouping code, meaning that row 1 is in group 1 and row 4 is in group 2.

[0116] 404. The acceleration device queries the fact table based on the vector index to obtain a data query result.

[0117] In an embodiment of the present application, an acceleration device obtains fact data from a fact table from a host computer and queries the column containing the join key in the fact table based on a vector index to obtain a data query result. In some embodiments, when a data query request indicates that data should be queried from the fact table and at least one dimension table based on a join key and a metric key, the acceleration device queries the column containing the join key and the column containing the metric key in the fact table based on the vector index to obtain a data query result.

[0118] Schematically, referring to FIG7 , FIG7 is a schematic diagram of a vector index-based fact table query provided by an embodiment of the present application. As shown in FIG7 , in conjunction with the SQL example in the aforementioned step 401, the acceleration device queries the columns containing the join keys custkey, suppkey, and orderdate in the fact table based on the vector index "JI_LCSD", and obtains the intermediate query results "Inter_C_F" (C_F is the abbreviation for customer-fact table), "Inter_S_F" (S_F is the abbreviation for supplier-fact table), and "Inter_D_F" (D_F is the abbreviation for date-fact table). The intermediate query results are combined with the vector dictionary of each dimension table to extract the dimension table output columns. For example, after obtaining the intermediate query result "Inter_C_F", it is still necessary to query the customer's nation. In addition, since the data query request in the SQL example of step 401 specifies the metric key lo.revenue column, the acceleration device queries the lo.revenue column in the fact table based on the vector index, extracts the corresponding key value, and obtains the metric data "Inter_R". Merge the output columns of each dimension table and the measurement data extracted from the measurement column of the fact table to obtain the data query result.

[0119] Referring to FIG8 , the data query method shown in steps 401 to 404 above is described below, taking the interaction between the host and the acceleration device as an example. FIG8 is a schematic diagram of another data query method provided by an embodiment of the present application. As shown in FIG8 , the left side is the host side, and the right side is the acceleration device. Schematically, the data query method includes the following steps:

[0120] 1. The acceleration device obtains the query vector of the data query request, generates a vector dictionary for each dimension table based on the dimension table in the host-side memory, compresses the GROUP-BY and WHERE data (refer to the SQL shown in step 401 above), and stores it in the storage unit of the acceleration device.

[0121] 2. The acceleration device traverses each dimension table based on the query vector and the vector corresponding to each dimension table, and combines the association relationship between the dimension table and the fact table to obtain the corresponding fact data in the fact table for matching and generate a vector index.

[0122] 3. The accelerator batch-transfers fact table data from the host, performs fact table calculations based on the metric data, extracts dimension table output columns from each dimension table, and then merges the output columns extracted from the fact table metric columns into the data query results.

[0123] It should be noted that due to multi-table joins, if one of the conditions is not met, no output will be made. Therefore, data that has been accessed and does not meet the join conditions will not be accessed again.

[0124] 4. The acceleration device returns the data query results to the host.

[0125] In some embodiments, for scenarios where the data query results obtained after connection need to be further aggregated (ADD / COUNT / AVG), the acceleration device can implement vector execution through the VADD instruction, accumulate the results into the storage space corresponding to the bank, obtain the further aggregated data query results, and return them to the host side. For example, the SPM in the figure refers to the sub-storage unit of the storage unit ASPM, which can store multiple vector indexes in sequence, thereby saving storage space. For example, the actual data is obtained from the SPM, and the index is built and written directly. The key is stored in the SPM, and the value is stored in the bank.

[0126] In addition, the above process can also refer to Figure 9, which is a schematic diagram of another data query method provided by an embodiment of the present application. As shown in Figure 9, the SQL in the aforementioned step 401 is continued as an example, and is explained in combination with sum(lo.revenue) as revenue. Among them, based on the s_nation related to SQL, the fact table lineorder and the dimension table supplier are combined through the connection key suppkey, and it is determined that the revenue corresponding to s_nation of 1, 0, 2, and 1 is 6, 0, 24, and 6 respectively; grouping is performed according to the grouping key s_nation, and the vector dictionary corresponding to the dimension table supplier is obtained. Similarly, based on the c_nation related to SQL, the fact table lineorder and the dimension table customer are combined through the connection key custkey, and it is determined that the revenue corresponding to c_nation of 0, 1, and 1 is 0, 6, and 6 respectively; grouping is performed according to the grouping key c_nation, and the vector dictionary corresponding to the dimension table customer is obtained. Then, the above two vector dictionaries are combined according to the connection condition indicated by the query vector to obtain the grouping result as shown in the figure. The same operations are performed on the date dimension table. Finally, the three vector dictionaries involved in the SQL statement are combined, and the corresponding data from the fact table is found and summed. It should be understood that the diagram is merely an example, and some data is not shown. The principles behind this method are similar to those in the previous method examples and will not be further elaborated here.

[0127] From the introduction of Figures 4 to 9 above, it can be seen that an embodiment of the present application provides a data query method, which is applied to an acceleration device of a computing device. The method includes: vectorizing the execution of a data query request, generating a vector dictionary and a vector index based on the fact table, dimension table, and connection key indicated by the data query request, and realizing a multi-table connection query. On the one hand, since the vector dictionary and vector index are small in size, they will not take up too much storage space. On the other hand, the entire process is executed by a hardware acceleration device, which offloads the computing power of the processor in the computing device and can improve the overall query performance.

[0128] In addition, based on the above introduction, it can be seen that the technical solution provided by this application is aimed at general database, big data query and other acceleration scenarios. Compared with the hash join algorithm, the data query method provided by this application can effectively improve the efficiency of data query. For example, refer to Figure 10, which is a schematic diagram of an experimental effect comparison provided by an embodiment of this application. As shown in Figure 10, the Gaussian column-stored hash join algorithm is divided into three phases of timing: build, probe and get_batch. Correspondingly, the time for vector connection in the method provided by this application is divided into vector index generation build time and vector connection time. It can be seen that the method provided by this application achieves an average improvement of 3.x on the star join statement of the SSB Benchmark, and the overall data processing throughput is also significantly improved. Among them, for star join Star Join, by calculating the foreign key vector, the vector array is directly accessed when joining, and operations such as join filtering are completed. For vector grouping Vector Group, a grouping aggregation calculation method based on a multidimensional array is used to map the multiple group codes of the output records of the query star join process to the subscripts on each dimension of the corresponding multidimensional array, and convert them into one-dimensional array subscripts for aggregation calculation.

[0129] Moreover, the construction process of the vector dictionary, vector index, etc. involved in this application is smaller and faster than hash join. Moreover, the smaller feature of the vector index requires a lower size of the CPU cache, which can avoid cache loss when running the vector index on certain specific CPUs (small cache). For example, referring to Figure 11, Figure 11 is a schematic diagram of another data query method provided by an embodiment of the present application. Taking the query statement "select sum(R.payload+S.payload)from R, S where R.key=S.key" as an example, the method provided by this application is used to create a vector table of the same length as the R table, and use the vector index to map the load to a unique position in the vector index table. When the S table is searched, the corresponding value can be directly obtained for direct calculation. Compared with hash join, the contention for parallel locks is minimized, while the calculation of hash values ​​and the query of hash tables are eliminated, and the CPU cycles in the detection phase are saved.

[0130] In this application, the terms "first", "second", etc. are used to distinguish between identical or similar items with substantially the same effects and functions. It should be understood that there is no logical or temporal dependency between "first", "second", and "nth", nor is there a limit on quantity and execution order. It should also be understood that although the following description uses the terms first, second, etc. to describe various elements, these elements should not be limited by the terms. These terms are only used to distinguish one element from another. For example, without departing from the scope of the various described examples, a first dimensional table can be referred to as a second dimensional table, and similarly, a second dimensional table can be referred to as a first dimensional table. Both the first dimensional table and the second dimensional table can be dimensional tables, and in some cases, can be separate and different dimensional tables.

[0131] In this application, the term "at least one" means one or more, and the term "plurality" means two or more. For example, a plurality of dimension tables refers to two or more dimension tables.

[0132] The above description is merely a specific embodiment of the present application, but the scope of protection of the present application is not limited thereto. Any person skilled in the art can easily conceive of various equivalent modifications or substitutions within the technical scope disclosed in this application, and such modifications or substitutions should be included in the scope of protection of the present application. Therefore, the scope of protection of the present application should be based on the scope of protection of the claims.

[0133] In the above embodiments, all or part of the embodiments may be implemented using software, hardware, firmware, or any combination thereof. When implemented using software, all or part of the embodiments may be implemented in the form of program structure information. The program structure information includes one or more program instructions. When the program instructions are loaded and executed on a computing device, all or part of the processes or functions described in the embodiments of the present application are generated.

[0134] Those skilled in the art will understand that all or part of the steps to implement the above embodiments may be accomplished by hardware, or may be accomplished by a program to instruct the relevant hardware, and the program may be stored in a computer-readable storage medium, and the above-mentioned storage medium may be a read-only memory, a disk or an optical disk, etc.

[0135] As described above, the above embodiments are only used to illustrate the technical solutions of the present application, rather than to limit them. Although the present application has been described in detail with reference to the above embodiments, those skilled in the art should understand that they can still modify the technical solutions described in the above embodiments, or make equivalent replacements for some of the technical features therein. However, these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the scope of the technical solutions of the embodiments of the present application.

Claims

1. A data query method, characterized in that: An acceleration device applied to a computing device, the method comprising: Acquire a query vector of a data query request, wherein the data query request indicates querying data from a fact table and at least one dimension table based on a join key, at least one foreign key of the fact table is associated with a primary key of the at least one dimension table, and the at least one dimension table is used to store dimension information of fact data in the fact table; Generate a vector dictionary for each dimension table based on the query vector and the vector corresponding to the at least one dimension table, wherein the vector dictionary for the dimension table includes a connection key value of the connection key in the dimension table related to the query vector; Based on at least one foreign key of the fact table, the primary key of the at least one dimension table and a vector dictionary of each dimension table, traverse the at least one dimension table to generate a vector index, wherein the vector index includes a fact table identifier of the join key value in the fact table; The fact table is queried based on the vector index to obtain a data query result.

2. The method according to claim 1, characterized in that The at least one dimensional table is stored in the acceleration device.

3. The method according to claim 1 or 2, characterized in that: The method further comprises at least one of the following: storing the vector dictionary of each dimension table in the acceleration device; The vector index is stored in the acceleration device.

4. The method according to any one of claims 1 to 3, characterized in that The step of generating a vector dictionary for each dimension table based on the query vector and the vector corresponding to the at least one dimension table includes: Based on the first dimension information indicated by the query vector, determining a first connection key value related to the first dimension information from a first dimension table where the first dimension information is located; Based on the first connection key value, a vector dictionary of the first dimensional table is generated.

5. The method according to any one of claims 1 to 4, characterized in that The step of traversing the at least one dimension table based on the at least one foreign key of the fact table, the primary key of the at least one dimension table, and the vector dictionary of each dimension table to generate a vector index includes: Based on at least one foreign key of the fact table, the primary key of at least one dimension table and the vector dictionary of each dimension table, a connection table of each dimension table is generated, wherein the connection table of each dimension table includes the connection key value in the dimension table and a fact table identifier of the connection key value in the fact table; The vector index is generated based on the same fact table identifier in the join table of each dimension table.

6. The method according to claim 5, characterized in that The step of generating a connection table for each dimension table based on at least one foreign key of the fact table, the primary key of the at least one dimension table, and a vector dictionary of each dimension table comprises: Based on the vector dictionary of the first dimensional table and the first primary key of the first dimensional table, traverse the first dimensional table to determine a first primary key value corresponding to a first connection key value in the first dimensional table, where the first connection key value is a connection key value determined from the first dimensional table based on the first dimension information indicated by the query vector; Determine a first fact table identifier of the first join key value in the fact table based on the first primary key value and a first foreign key of the fact table associated with the first primary key; A join table of the first dimension table is generated based on the first join key value and a first fact table identifier of the first join key value in the fact table.

7. The method according to any one of claims 1 to 6, characterized in that The querying the fact table based on the vector index to obtain a data query result includes: Based on the vector index, the column where the join key is located in the fact table is queried to obtain the data query result.

8. The method according to any one of claims 1 to 6, characterized in that The data query request indicates querying data from a fact table and at least one dimension table based on a join key and a measure key; the querying the fact table based on the vector index to obtain a data query result includes: Based on the vector index, the column where the join key and the column where the metric key are located in the fact table are queried to obtain the data query result.

9. The method according to any one of claims 1 to 8, characterized in that The data query request indicates querying data from a fact table and at least one dimension table based on a join key and a group key, and the method further includes: A group code is generated based on the group key, and the group code is mapped to a subscript of a one-dimensional array corresponding to the group key in the data query result.

10. The method according to any one of claims 1 to 9, characterized in that The acceleration device is at least one of a system-on-chip SOC, a field programmable gate array FPGA, a graphics processor GPU, an application-specific integrated circuit ASIC, an artificial intelligence AI chip or a data processor DPU.

11. An acceleration device, characterized in that: Configured in a computing device, the acceleration device includes a processing unit and a storage unit, wherein the processing unit is used to execute the data query method as described in any one of claims 1 to 10, and the storage unit is used to provide storage space for the acceleration device.

12. A computing device, characterized in that: The computing device includes a host and an acceleration device, the host is used to send a data query request to the acceleration device, and the acceleration device is used to receive the data query request and implement the data query method as described in any one of claims 1 to 10.

13. A computer-readable storage medium, characterized in that: The computer-readable storage medium is used to store at least one section of program code, and the at least one section of program code is used to execute the data query method as described in any one of claims 1 to 10 above.

14. A computer program product, characterized in that When the computer program product is run on an acceleration device of a computing apparatus, the acceleration device is enabled to execute the data query method as claimed in any one of claims 1 to 10.

Images