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

Abstract

The invention discloses a data query method, an acceleration device, computing equipment and a storage medium, and relates to the technical field of databases. The method is applied to an acceleration device of computing equipment and comprises the steps that vectorization execution is conducted on a data query request, a vector dictionary and a vector index are generated based on a fact table, a dimension table and a connection key indicated by the data query request, and multi-table connection query is achieved. On the other hand, the whole process is executed by the hardware acceleration device, the computing power of a processor in the computing equipment is unloaded, and the overall query performance can be improved.

Description

Technical Field

[0001] This application relates to the field of database technologies, and particularly to a data query method, an acceleration device, a computing device, and a storage medium. Background Art

[0002] In the field of database technologies, storing data using a star or snowflake join architecture can effectively save the storage space of a database. That is, a large amount of factual data is stored in a fact table, and the dimensional information for the factual data is stored in a dimension table. An association relationship between the fact table and the dimension table is established through the foreign key of the fact table and the primary key of the dimension table. When performing business analysis, the dimension table and the fact table can be joined through a data query request, thereby aggregating the data in the dimension table and the fact table and providing corresponding information to the user. For example, a data query request instructs to aggregate the sales amount in the fact table according to the two-dimensional information of the year and the region where the customer is located.

[0003] In related technologies, under a star or snowflake join architecture, a data query request usually specifies a join key, and the join key is used to determine which row in the dimension table is aggregated with a specified row in the fact table. Currently, the Hash Join algorithm is usually used to execute such data query requests. For example, the smaller of the two tables in terms of data volume is selected, a hash table is constructed in memory based on the join key of this table, and then based on each hash value in the hash table, the other table is traversed to find the rows with the same join key value, and these rows are merged to obtain the data query result.

[0004] However, in the above method, constructing a hash table occupies a large amount of memory space, and moreover, the processor needs to continuously access the hash table in memory, resulting in low data query efficiency and affecting the overall performance of the database. Summary of the Invention

[0005] Embodiments of this application provide a data query method, an acceleration device, a computing device, and a storage medium, which can improve data query efficiency.

[0006] In a first aspect, this application provides a data query method, which is applied to an acceleration device of a computing device. The method includes:

[0007] Obtain a query vector of a data query request. The data query request instructs to query 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 the primary key of the at least one dimension table, and the at least one dimension table is used to store the dimensional information of the factual data in the fact table;

[0008] Generate a vector dictionary for each dimension table based on the query vector and the vectors corresponding to the at least one dimension table. The vector dictionary of the dimension table includes the join key values of the join keys in the dimension table that are related to the query vector;

[0009] Traverse the at least one dimension table based on 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, and generate a vector index, where the vector index includes the fact table identifier of the connection key value in the fact table;

[0010] Query the fact table based on the vector index to obtain a data query result.

[0011] Through the above method, vectorize the execution of the data query request, generate a vector dictionary and a vector index based on the fact table, dimension table, and connection key indicated by the data query request, and implement a multi-table join query. On the one hand, since the vector dictionary and vector index are small in size, they do not occupy too much storage space. On the other hand, the entire process is executed by a hardware acceleration device, offloading the computing power of the processor in the computing device, and can improve the overall query performance.

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

[0013] In some embodiments, the method further includes at least one of the following:

[0014] Store the vector dictionary of each dimension table in the acceleration device;

[0015] Store the vector index in the acceleration device.

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

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

[0018] Generate the vector dictionary of the first dimension table based on the first connection key value.

[0019] In some embodiments, the traversing the at least one dimension table based on 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:

[0020] Generate 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 the vector dictionary of each dimension table, where the connection table of the dimension table includes the connection key value in the dimension table and the fact table identifier of the connection key value in the fact table;

[0021] Generate the vector index based on the same fact table identifier in the connection table of each dimension table.

[0022] In some embodiments, generating 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 the vector dictionary of each dimension table includes:

[0023] 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 join key value in the first dimension table, where the first join key value is a join key value determined from the first dimension table based on the first dimension information indicated by the query vector;

[0024] Based on the first primary key value and the first foreign key of the fact table associated with the first primary key, determine the first fact table identifier of the first join key value in the fact table;

[0025] Generate the join table of 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.

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

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

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

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

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

[0031] Generate a grouping code based on the grouping key, and map the grouping code to the subscript of the one-dimensional array corresponding to the grouping key in the data query result.

[0032] In some embodiments, the acceleration device 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 processor unit (DPU).

[0033] Second aspect, the present application provides an acceleration device 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 provided in the foregoing first aspect or any optional manner of the first aspect, and the storage unit is used to provide storage space for the acceleration device.

[0034] Third aspect, the present application provides a computing device. 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 provided in the foregoing first aspect or any optional manner of the first aspect.

[0035] Fourth aspect, the present application provides a computer-readable storage medium. The computer-readable storage medium is used to store at least one program code, and the at least one program code is used to execute the data query method provided in the foregoing first aspect or any optional manner of the first aspect. The storage medium includes but is not limited to volatile memories such as random access memories, and non-volatile memories such as flash memories, hard disk drives (HDDs), and solid state drives (SSDs).

[0036] Fifth aspect, the present application provides a computer program product. When the computer program product runs on the acceleration device of a computing device, it causes the acceleration device to execute the data query method provided in the foregoing first aspect or any optional manner of the first aspect. The computer program product can be a software installation package. In the case where it is necessary to implement the functions of the foregoing acceleration device, the computer program product can be downloaded and executed on the acceleration device. Description of the Drawings

[0037] Figure 1 is a schematic diagram of an implementation environment provided by an embodiment of the present application;

[0038] Figure 2 is a schematic diagram of the hardware structure of an acceleration device provided by an embodiment of the present application;

[0039] Figure 3 is a schematic diagram of the execution logic of an acceleration device provided by an embodiment of the present application;

[0040] Figure 4 is a flowchart of a data query method provided by an embodiment of the present application;

[0041] Figure 5 is a schematic diagram of generating a vector dictionary provided by an embodiment of the present application;

[0042] Figure 6 It is a schematic diagram of generating a vector index provided by an embodiment of the present application;

[0043] Figure 7 It is a schematic diagram of querying a fact table based on a vector index provided by an embodiment of the present application;

[0044] Figure 8 It is a schematic diagram of another data query method provided by an embodiment of the present application;

[0045] Figure 9 It is a schematic diagram of vectorized execution of a data query method provided by an embodiment of the present application;

[0046] Figure 10 It is a schematic diagram of comparing experimental effects provided by an embodiment of the present application;

[0047] Figure 11 It is a schematic diagram of another data query method provided by an embodiment of the present application. Detailed implementation manners

[0048] To make the objectives, technical solutions, and advantages of the present application clearer, the following will further describe the embodiments of the present application in detail 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 for analysis, stored data, displayed data, etc.), and signals involved in the present 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 relevant laws, regulations, and standards of relevant countries and regions. For example, the fact tables, dimension tables, etc. involved in the present application are obtained under full authorization.

[0049] For the convenience of understanding, the following first explains the key terms and key concepts involved in the present application.

[0050] A fact table is a table used to store business fact data. It usually includes numerical data related to business processes, such as sales amount, order quantity, inventory quantity, etc. Each row of the fact table represents a specified business fact, and each column is a metric or indicator related to that fact. The fact table usually contains one or more foreign keys for establishing an association relationship with the dimension table.

[0051] A dimension table is a table used to store context information describing facts. It usually includes dimension information related to the fact data in the fact table, such as time, location, product, customer, etc. Each row of the dimension table represents a dimension value, and each column is an attribute related to that dimension. The dimension table usually contains a primary key for establishing an association relationship with the fact table.

[0052] The star connection is a multi-dimensional data connection architecture, which consists of a fact table and at least one dimension table. The fact table is the core. Each dimension table has a dimension as the primary key, and the primary keys of all these dimensions are combined into the primary key of the fact table. After organizing the data in this way, the fact data in the fact table can be aggregated according to different dimensions (part or all of the primary key of the fact table), such as summation (summary), averaging (average), counting (count), percentage (percent), etc.

[0053] The snowflake connection is a multi-dimensional data connection architecture, which consists of a fact table and at least one dimension table. There is 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.

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

[0055] The following introduces the application scenarios and implementation environments involved in this application.

[0056] The technical solution provided in the embodiments of this 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, and can also be applicable to scenarios involving vector retrieval operations such as storage, network, and cloud. It should be understood that computer storage is embodied as a pyramid shape, with a small top space but fast processing speed. The cache of the central processing unit (CPU) is the first place where the CPU gets data. Similarly, multi-core processing also accelerates the execution of programs. However, the data inconsistency problem brought 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 comprehensive performance of the database. Currently, in-memory databases are combined with algorithms of hardware settings, such as being divided into hardware-oblivious algorithms and hardware-conscious algorithms. The former is applied to, for example, hash join algorithms without partitioning, while the latter is for join algorithms with partitioning. Different algorithms are sensitive to the cache and the number of cores of the CPU to different degrees. Compared with the hash join algorithm, the vector join algorithm has improvements in both space and efficiency. Compared with the hash join, the vector join has great advantages in constructing vector arrays and join queries. Based on this, this application provides a hardware acceleration device that supports vector join, configured in a computing device, and can accelerate the execution process of multi-table join and multi-table aggregation operators in data analysis scenarios such as databases and big data.

[0057] The following refers to Figure 1 to introduce the implementation environment of this application.

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

[0059] The host 101 refers to a device used to run a database and can provide services such as data query and data analysis for users. In the embodiment of the present application, the host 101 can control the acceleration device 102 to execute operators such as multi-table join and multi-table aggregation for a data query request of the database. This process can also be understood as loading the data query task into the acceleration device 102 for operation. In addition, the number of hosts 101 can be one or more, and the present application does not make any limitation in this regard.

[0060] The acceleration device 102 is used to provide computing power for the database to accelerate the execution process of database operators, that is, to execute the data query method provided by the present 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 the present application does not make any limitation in this regard. Schematically, the acceleration device 102 queries corresponding data from a fact table and at least one dimension table according to a data query request sent by the host 101, obtains a 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 the present application does not make any limitation in this regard.

[0061] Schematically, the host 101 and the acceleration device 102 are communicatively connected through a peripheral component interconnect express (PCIe) link, and the host 101 exchanges data with the acceleration device 102 through the PCIe link. For example, the computing device 100 can be an independent physical server, or a server cluster or a distributed file system composed 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 network (CDN), and big data and artificial intelligence platforms.

[0062] Taking the computing device 100 as a cloud server as an example, the computing device can also be referred to as a cloud platform (i.e., the abbreviation of the cloud computing platform), which refers to a service based on hardware resources and software resources, providing computing, network, and storage capabilities. Through the network "cloud", huge data calculations are processed and analyzed remotely and then returned to the user, with characteristics such as large scale, distribution, virtualization, high availability, scalability, on-demand service, and security. The cloud platform can achieve the rapid allocation and release of configurable computing resources with a relatively small management cost or a relatively low interaction complexity between the user and the service provider.

[0063] In addition, the networks involved above include, but are not limited to, any combination of a data center network, a storage area network (SAN), a local area network (LAN), a metropolitan area network (MAN), a wide area network (WAN), a mobile, wired or wireless network, a private network or a virtual private network. In some implementations, technologies and / or formats such as hyper text markup language (HTML), extensible markup language (XML), etc. are used to represent the data exchanged through the network. In addition, conventional encryption technologies such as secure sockets layer (SSL), transport layer security (TLS), virtual private network (VPN), internet protocol security (IPsec), etc. can be used to encrypt all or part of the links. In other embodiments, customized and / or dedicated data communication technologies can also be used to replace or supplement the above data communication technologies.

[0064] Next, the structure of the above acceleration device 102 will be introduced.

[0065] Figure 2 It is a schematic diagram of the hardware structure of an acceleration device provided by an embodiment of the present application. As Figure 2 shown, the acceleration device 102 includes a communication interface 1021, a processing unit 1022, a storage unit 1023, and a bus 1024. Among them, the communication interface 1021, the processing unit 1022, and the storage unit 1023 are communicatively connected to each other through the bus 1024.

[0066] The communication interface 1021 is used to provide program instructions and / or data. The communication interface 1021 includes a PCIe communication interface, other general peripheral interfaces, etc., and the present application does not limit this. For example, when the acceleration device 102 is used as an acceleration card of the host 101, data exchange is achieved with the host 101 through the PCIe communication interface. Another example is that the acceleration device 102 realizes communication between the acceleration device 102 and other devices or communication networks through the peripheral interface.

[0067] The processing unit 1022 is used to execute the data query method provided by this application. For example, it is a core of a central processing unit (CPU), or a control unit (CU). This application does not make any limitations in this regard. Schematically, the processing unit 1022 can also be understood as a vector control unit (also known as the 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 this processing unit 1022, the logical control of the real database service scenario is realized, and it can be conveniently docked into the database operator execution process, minimizing software transformation.

[0068] 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), or a double data rate (DDR) memory, a static random-access memory (SRAM), or other types of dynamic storage devices that can store information and instructions, or it may also include any other medium that can be used to carry or store the desired program code in the form of instructions or data structures and can be accessed 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. This application does not make any limitations in this regard.

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

[0070] It should be noted that the above Figure 2 only shows a hardware structure diagram of an acceleration device that can be configured as the above acceleration device 102. In some embodiments, the acceleration device 102 may further include other components to achieve more functions. This application is not limited thereto.

[0071] Next, refer to Figure 3 , and introduce the hardware execution logic of the acceleration device 102. Figure 3 It is a schematic diagram of the execution logic of an acceleration device provided by an embodiment of this application. As Figure 3As shown, the processing unit 1022 (i.e., the VAQ_CTRL unit) is used to provide logical control for interacting with the service flow, including sub-units such as DecoCtrl, DataReq, DataPack, and InstExec. Among them, the DateReq and DataPack sub-units are used to interact with the ASPM storage unit to implement data request and acquisition operations. The InstExec sub-unit interacts with the arithmetic logic unit ALU, sends the data that needs to be calculated to the ALU unit, and realizes 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 Taking the sum operation of finding idx (index) = 3 as an example for the content shown, the external input idx (i.e., read req to ASPM), constructs the bit 1001 that conforms to 3 from the index (CMPEQ in the figure is the SIMD comparison instruction), combines it with the Value table, and gets 32 (REDUCTION in the figure is the SIMD reduction instruction, and OPN is the data block instruction), and writes 32 into the ASPM (i.e., write req to ASPM). Among them, the Prev (previous) stage can be understood as the historical visibility determination stage.

[0072] The data query method provided by this application will be introduced below.

[0073] Based on the foregoing introduction, it can be known that the technical solution provided by the embodiments of this application can be applied to data analysis scenarios such as databases and big data. Taking the database service as an example, the user initiates a query through a structured query language (SQL). After the syntax and lexical analysis of the database generate an execution plan and optimize it through an optimizer, it will enter the execution stage. At this time, it will execute according to the optimized execution plan in the order of execution operators. The scenarios targeted by this application for acceleration are operators involving multi-table joins and result aggregations, such as aggregate aggregation operators and join join operators, etc.

[0074] Next, refer to Figure 4 to introduce the data query method provided by this application. Figure 4 is a flowchart of a data query method provided by an embodiment of this application. As Figure 4 shown, this method is applied to an acceleration device of a computing device and includes the following steps 401 to step 404.

[0075] 401. The acceleration device obtains a query vector of a data query request. 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 the primary key of at least one dimension table, and at least one dimension table is used to store dimension information of the fact data in the fact table.

[0076] In the embodiments of the present application, the acceleration device is communicatively connected to the host. In response to a data query request from a user, the host sends the data query request to the optimizer, and the optimizer maps the data query request into a query vector and sends the query vector to the acceleration device. In some embodiments, the acceleration device may also map the data query request into a query vector, and the present application does not limit this. Converting the data query request into vectorized execution can effectively improve the overall performance.

[0077] Based on the foregoing introduction to the fact table and dimension tables, it can be known that the fact table includes at least one foreign key, and each dimension table has a dimension as the primary key. These primary keys are combined into the primary key of the fact table, that is, they are associated with at least one foreign key of the fact table. In addition, the present application does not limit the number of dimension tables associated with the fact table.

[0078] The following is an example of the above data query request based on SSB (Star Schema Benchmark, an international standard star test set) with reference to the SQL shown below.

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

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

[0081] WHERE lo.custkey = c.custkey

[0082] AND lo.suppkey = s.suppkey

[0083] AND lo.orderdate = d.dateid

[0084] AND c.region = ’A’

[0085] AND s.region = ’A’

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

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

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

[0089] In the above query statement, lineorder is the merchandise order detail table, abbreviated as lo; customer is the customer information table, abbreviated as c; supplier is the supplier information table, abbreviated as s; date is the date table, abbreviated as d. The fact table is the merchandise order detail 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 are used to store the dimension information of the fact data in the fact table lo. The customer code custkey column, the supplier code suppkey column, and the date identifier dateid column are the join keys, the lo.revenue column is the measure key, and the c.nation column, the s.nation column, and the d.year column are the grouping keys. This data query request instructs to query the total revenue of orders that meet the specified conditions from the fact table lo and multiple dimension tables c, s, and d based on custkey, suppkey, and dateid. Among them, the specified conditions mean that in dimension table c, region = 'A', in dimension table s, region = 'A', and in dimension table d, the year is between 1992 and 1997. The query results are grouped according to the grouping keys, and the years are sorted in ascending order, and the revenues are sorted in descending order.

[0090] 402. The acceleration device generates a vector dictionary for each dimension table based on the query vector and the vector corresponding to at least one dimension table. The vector dictionary of the dimension table includes the join key values of the join keys related to the query vector in the dimension table.

[0091] In the 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 the data query efficiency. Of course, the dimension table can also be stored in the host memory and obtained by the acceleration device from the host to save the storage space of the acceleration device. Or, when the number of dimension tables is multiple, a part of the dimension tables can be stored in the acceleration device, and another part of the dimension tables can be stored in the host memory. The present application does not make any limitations in this regard.

[0092] 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 (which can also be simply referred to as the dimension vector). Taking the first dimension table as an example, this step includes the following steps A1 and A2:

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

[0094] Among them, the acceleration device traverses the first dimension table where the first dimension information is located based on the first dimension information and the join key column of the first dimension table, and determines the first join key value that meets the first dimension information.

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

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

[0097] Schematically, refer to Figure 5 , Figure 5 is a schematic diagram of generating a vector dictionary provided by an embodiment of the present application. Combining the SQL example in the foregoing step 401, taking the first dimension information 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 that satisfy c.region = 'A', that is, custkey "1" and custkey "3". Combining the OIDs of the dimension tables corresponding to these two first connection key values, a vector dictionary for the first dimension table is generated, that is, "Inter_C". 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. Among them, 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 elaborated here.

[0098] In some embodiments, the acceleration device stores the vector dictionary of each dimension table in the acceleration device, such as the storage unit ASPM of the acceleration device, for subsequent quick access to improve data query efficiency.

[0099] 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, and generates a vector index. The vector index includes the fact table identifier of the connection key value in the fact table.

[0100] In the embodiment of the present application, the fact table identifier is an object identifier OID used to uniquely identify a certain row in the fact table. Since the vector dictionary of each dimension table is obtained through the above step 402, in this step, the dimension table and the fact table can be connected according to the vector dictionary of each dimension table and the association relationship between the dimension table and the fact table to construct a vector index.

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

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

[0103] Step B1: Generate a join table for each 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. The join table of the dimension table includes the join key value in the dimension table and the fact table identifier of the fact table where the join key value is located.

[0104] Among them, for any dimension table (hereinafter referred to as the first dimension table), this step B1 includes the following steps:

[0105] Step B11: Traverse the first dimension table based on the vector dictionary of the first dimension table and the first primary key of the first dimension table to determine the first primary key value corresponding to the first join key value in the first dimension table. The first join key value is a join key value determined from the first dimension table based on the first dimension information indicated by the query vector.

[0106] Among them, the acceleration device traverses the first dimension table based on the first join 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 join key value from the primary key column of the first dimension table. The first join key value refers to the aforementioned step 402 and will not be elaborated here.

[0107] Step B12: Determine the 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.

[0108] Among them, the acceleration device obtains the corresponding data of the fact table based on the first primary key value and the first foreign key of the fact table, performs matching, and determines the first fact table identifier of the first join key value in the fact table.

[0109] 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.

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

[0111] Schematically, the above steps B1 and B2 refer to Figure 6 , Figure 6 is a schematic diagram of generating a vector index provided by an embodiment of the present application. As Figure 6As shown, in combination with the SQL example in the foregoing step 401, taking the first dimension table, i.e., 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", 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, 3 respectively. Then, based on the first primary key values and the first foreign key (i.e., the custkey column) of the fact table, the acceleration device determines the first fact table identifiers of the first connection key values in the fact table, which are 1, 2, 4, 6, 7 respectively. Based on the first connection key values (which are 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 connection table of the first dimension table, i.e., "JI_LC" (abbreviation of Join-lineorder-customer), is generated. Additionally, Figure 6 The process of generating a connection table for the supplier information table s and the date table d is also shown. The principle is the same as that of the customer information table c. Among them, the connection table of the supplier information table s is "JI_LS" (abbreviation of Join-lineorder-supplier), and the connection table of the date table d is "JI_LD" (abbreviation of Join-lineorder-date), which will not be elaborated here. "VOID" in the figure is the abbreviation of Virtual-OID and is used to represent the fact table identifier.

[0112] Continuing to refer to Figure 6 , after obtaining the connection tables of each dimension table, based on the same fact table identifiers in the connection tables of each dimension table, a vector index, i.e., "JI_LCSD" (abbreviation of Join-lineorder-customer-supplier-date), is generated. Since Figure 6 it is an example based on the star test set, the vector index generated here meets the star join condition. It should be understood that the data query method provided in this application is also applicable to other connection architectures, such as the snowflake connection architecture (which can be understood as a complex form of star connection architecture), etc. This application does not limit this.

[0113] Additionally, in some embodiments, when the data query request indicates querying data from the fact table and at least one dimension table based on the connection key and the 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 the subscript of a one-dimensional array corresponding to the grouping key. That is, referring to Figure 6 the vector index "JI_LCSD", in this vector index, the values 1 and 4 in the second column indicate that among the 1-7 rows of the fact table, the rows that meet the conditions are row 1 and row 4, and the values in the first column are the grouping codes. Row 1 is in the first group and row 4 is in the second group.

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

[0115] In the embodiment of the present application, the acceleration device obtains the fact data in the fact table from the host, and queries the column where the join key is located in the fact table based on the vector index to obtain the data query result. In some embodiments, when the data query request indicates to query data from the fact table and at least one dimension table based on the join key and the measure key, the acceleration device queries the column where the join key is located and the column where the measure key is located in the fact table based on the vector index to obtain the data query result.

[0116] Schematically, referring to Figure 7 , Figure 7 is a schematic diagram of querying the fact table based on the vector index provided by the embodiment of the present application. As Figure 7 shown, in combination with the SQL example in the foregoing step 401, the acceleration device queries the columns where the join keys custkey, suppkey, and orderdate are located in the fact table based on the vector index "JI_LCSD" to obtain the intermediate query results "Inter_C_F" (C_F is short for customer - fact table), "Inter_S_F" (S_F is short for supplier - fact table), "Inter_D_F" (D_F is short for date - fact table). Combining the intermediate query results with the vector dictionary of each dimension table, the output columns of the dimension table are taken out. For example, after obtaining the intermediate query result "Inter_C_F", the nation of the customer still needs to be queried. In addition, since in the SQL example of step 401, the data query request specifies the measure 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 values, and obtains the measure data "Inter_R". The output columns of each dimension table and the measure data extracted from the fact table measure column are combined to obtain the data query result.

[0117] Next, referring to Figure 8 , taking the interaction between the host and the acceleration device as an example, the data query method shown in the above steps 401 to 404 will be introduced. Figure 8 is a schematic diagram of another data query method provided by the embodiment of the present application. As Figure 8 shown, the left side is the host side and the right side is the acceleration device. Schematically, the data query method includes the following steps:

[0118] 1. The acceleration device obtains the query vector of the data query request, generates the vector dictionary of 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 the foregoing step 401), and stores it in the storage unit of the acceleration device.

[0119] 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.

[0120] 3. The acceleration device batch-transfers the fact table data from the host, performs fact table calculations based on the metric data, extracts the dimension table output columns from each dimension table, and then combines them with the output columns extracted from the fact table metric columns to obtain the data query result.

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

[0122] 4. The acceleration device returns the data query result to the host.

[0123] In some embodiments, for scenarios where the data query result obtained after joining needs 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 result, and return it to the host side. For example, in the figure, SPM refers to the sub-storage unit of the storage unit ASPM, and multiple vector indexes can be sequentially stored in order, thereby saving storage space. For example, after obtaining the actual data from the SPM and building the index, it is directly written. The key is stored in the SPM, and the value is stored in the bank.

[0124] In addition, the above process can also refer to Figure 9 , Figure 9 which is a schematic diagram of another data query method provided by the embodiments of the present application. As Figure 9As shown, continue to use the SQL in the foregoing step 401 as an example, and illustrate it in combination with sum(lo.revenue) as revenue. Among them, based on s_nation related to SQL, the fact table lineorder and the dimension table supplier are combined through the join key suppkey, and it is determined that the revenues corresponding to s_nation being 1, 0, 2, and 1 are 6, 0, 24, and 6 respectively; grouping is performed according to the grouping key s_nation to obtain the vector dictionary corresponding to the dimension table supplier. Similarly, based on c_nation related to SQL, the fact table lineorder and the dimension table customer are combined through the join key custkey, and it is determined that the revenues corresponding to c_nation being 0, 1, and 1 are 0, 6, and 6 respectively; grouping is performed according to the grouping key c_nation to obtain the vector dictionary corresponding to the dimension table customer. Then, the above two vector dictionaries are combined according to the join condition indicated by the query vector to obtain the grouping result as shown in the figure. The same operation as the above process is performed on the dimension table date table, and finally the three vector dictionaries involved in the SQL are combined, and the corresponding data is found from the fact table for summation. It should be understood that only examples are shown in the figure, and some data is not shown. The principle refers to the foregoing method embodiments and will not be elaborated here.

[0125] After the above Figures 4 to 9 introduction, it can be known that the embodiment of the present application provides a data query method, which is applied to an acceleration device of a computing device. The method includes: performing vectorized execution on a data query request, and generating a vector dictionary and a vector index based on the fact table, dimension table, and join key indicated by the data query request to implement multi-table join query. On the one hand, since the vector dictionary and the vector index are small in size and do not occupy too much storage space. On the other hand, the entire process is executed by a hardware acceleration device, unloading the computing power of the processor in the computing device, and capable of improving the overall query performance.

[0126] In addition, based on the foregoing introduction, it can be known that the technical solution provided by the present application is applicable to acceleration scenarios such as general databases and big data queries. Compared with the hash join algorithm, the data query method provided by the present application can effectively improve the data query efficiency. For example, refer to Figure 10 , Figure 10 is a schematic diagram of the experimental effect comparison provided by the embodiment of the present application. As Figure 10As shown, the Gauss column-store hash join algorithm is timed in three stages: build, probe, and get_batch. Correspondingly, in the method provided in this application, the time for vector join is divided into the build time for vector index generation and the time for vector join. It can be seen that the method provided in this application has an average improvement of 3.x in the star join statement of the SSB Benchmark, and the overall data processing throughput has also increased significantly. Among them, for the star join, by calculating the foreign key vector, directly access the vector array during the join to complete operations such as join filtering. For vector grouping, a grouping aggregation calculation method based on multi-dimensional arrays is used to map the multiple group encodings of the output records of the query star join process to the subscripts on each dimension of the corresponding multi-dimensional array, and convert them into one-dimensional array subscripts for aggregation calculation.

[0127] Moreover, the construction processes of the vector dictionary, vector index, etc. involved in this application are smaller and faster than the hash join. Moreover, the characteristic of the smaller vector index requires a lower requirement for the size of the CPU cache, which can avoid cache misses when running the vector index on certain specific CPUs (with small caches). For example, refer to Figure 11 , Figure 11 is a schematic diagram of another data query method provided by an embodiment of this application. Taking the query statement "select sum(R.payload + S.payload) from R, S where R.key = S.key" as an example, using the method provided in this application, a vector table of the same length as the R table is created, and the payload is mapped to a unique position in the vector index table using the vector index. When the S table comes to search, the corresponding value can be directly obtained for direct calculation. Compared with the hash join, the competition of parallel locks is minimized, and at the same time, the calculation of hash values and the query of hash tables are eliminated, and the CPU cycles in the probe stage are also saved.

[0128] In this application, terms such as "first" and "second" are used to distinguish between identical or similar items with basically the same functions. It should be understood that there is no logical or temporal dependency between "first", "second", and "nth", nor are the quantity and execution order limited. It should also be understood that although the following description uses terms such as first and second 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 various examples, the first dimension table can be called the second dimension table, and similarly, the second dimension table can be called the first dimension table. The first dimension table and the second dimension table can both be dimension tables, and in some cases, they can be separate and different dimension tables.

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

[0130] The above description is only a specific implementation manner of this application, but the protection scope of this application is not limited thereto. Any person skilled in the art within the technical scope disclosed in this application can easily think of various equivalent modifications or substitutions, and these modifications or substitutions should be covered within the protection scope of this application. Therefore, the protection scope of this application shall be subject to the protection scope of the claims.

[0131] In the above embodiments, it can be implemented in whole or in part by software, hardware, firmware, or any combination thereof. When implemented by software, it can be implemented in whole or in part in the form of program structure information. This program structure information includes one or more program instructions. When the program instructions are loaded and executed on a computing device, the processes or functions in the embodiments of this application are generated in whole or in part.

[0132] Those of ordinary skill in the art can understand that all or part of the steps to implement the above embodiments can be completed by hardware, or can be completed by a program instructing relevant hardware. This program can be stored in a computer-readable storage medium, and the above-mentioned storage medium can be a read-only memory, a magnetic disk, an optical disk, etc.

[0133] As mentioned above, the above embodiments are only used to illustrate the technical solutions of this application, rather than to limit it; although this application has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that they can still modify the technical solutions recorded in the foregoing embodiments, or perform equivalent substitutions on some of the technical features; and these modifications or substitutions do not make the essence of the corresponding technical solutions deviate from the scope of the technical solutions of the embodiments of this application.

Claims

1. A data query method, characterized in that, applied to an acceleration device of a computing device, the method comprising: obtaining a query vector of a data query request, the data query request indicating to query 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 being associated with a primary key of the at least one dimension table, the at least one dimension table being used to store dimension information of fact data in the fact table; generating a vector dictionary for each dimension table based on the query vector and vectors corresponding to the at least one dimension table, the vector dictionary of the dimension table including join key values of the join keys in the dimension table that are related to the query vector; 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, and generating a vector index, the vector index including fact table identifiers of the fact table where the join key values are located; querying the fact table 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 dimension 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; storing the vector index in the acceleration device.

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

5. The method according to any one of claims 1 to 4, characterized in that, the 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, and generating a vector index includes: generating a join table for each 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, the join table of the dimension table including the join key values in the dimension table and fact table identifiers of the fact table where the join key values are located; generating the vector index based on the same fact table identifiers in the join table of each dimension table.

6. The method according to claim 5, characterized in that, the generating a join table for each 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 includes: traversing the first dimension table based on the vector dictionary of the first dimension table and the first primary key of the first dimension table, and determining a first primary key value corresponding to a first join key value in the first dimension table, the first join key value being a join key value determined from the first dimension table based on the first dimension information indicated by the query vector; Determine the 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; 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.

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

8. The method according to any one of claims 1 to 6, wherein, The data query request instructs to query data from the 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: Querying the column where the join key is located and the column where the measure key is located in the fact table based on the vector index to obtain the data query result.

9. The method according to any one of claims 1 to 8, wherein, The data query request instructs to query data from the fact table and at least one dimension table based on a join key and a grouping key, and the method further includes: Generating a grouping code based on the grouping key, and mapping the grouping code to the subscript of a one-dimensional array corresponding to the grouping key in the data query result.

10. The method according to any one of claims 1 to 9, wherein, The acceleration device 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 processor unit (DPU).

11. An acceleration device, wherein, 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 according to any one of the foregoing claims 1 to 10, and the storage unit is used to provide storage space for the acceleration device.

12. A computing device, wherein, 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 according to any one of the foregoing claims 1 to 10.

13. A computer-readable storage medium, wherein, The computer-readable storage medium is used to store at least one segment of program code, and the at least one segment of program code is used to execute the data query method according to any one of the foregoing claims 1 to 10.

14. A computer program product, wherein, When the computer program product runs on the acceleration device of a computing device, the acceleration device is caused to execute the data query method according to any one of the foregoing claims 1 to 10.

Images