Wide table construction method, wide table construction system and related equipment

Abstract

The invention provides a wide table construction method, a wide table construction system and related equipment, and relates to the wide table construction method and the wide table construction system. A user with a wide table construction demand only needs to input a wide table construction instruction carrying wide table construction related information (such as an identifier of a fact table, an identifier of a dimension table, an identifier of a column of a to-be-added wide table in the fact table, an identifier of a column of the to-be-added wide table in the dimension table and the like) to a wide table construction system; in this way, the wide table construction system can automatically achieve wide table construction according to the wide table construction instruction input by the user and provide the wide table for the user, the whole wide table construction process is simple and rapid, and the construction efficiency is high.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of data processing, in particular to a wide table construction method, a wide table construction system and related equipment. BACKGROUND

[0002] A wide table is a data storage structure, which is characterized by containing a large number of columns in each row. The wide table is usually used to store data containing a large number of columns, such as financial transaction data, sensor data, enterprise employee information, enterprise financial data, etc. Since each row contains a large number of columns, the association (join) operation between multiple source tables integrated by the wide table in the database system can be reduced during query, the query efficiency is improved, and the wide table data is centralized, which is convenient for maintenance and management, so the wide table is widely used in the database system.

[0003] At present, the common way for enterprises to construct a wide table in a database system is that a special data table management personnel manually creates an empty wide table, then determines the columns in the fact table that need to be added to the wide table, manually copies these columns to the empty wide table, and then manually copies the columns in each dimension table that need to be added to the wide table to the wide table according to the correspondence between the foreign key of the wide table and the primary key of each dimension table, or even in some scenarios, some values are manually selected from the columns in each dimension table that need to be added to the wide table and copied to the wide table, so as to realize the construction of the wide table.

[0004] However, when the data volume of the fact table is large (such as tens, hundreds of columns, hundreds of millions of rows), the number of dimension tables is also huge (such as tens, hundreds of dimension tables), or the data volume of the dimension table is also large (such as tens, hundreds of columns, hundreds of millions of rows), the time consumed by the above-mentioned manual construction of the wide table is short for several hours and long for several days, and the efficiency is very low. SUMMARY

[0005] The present application provides a wide table construction method, a wide table construction system and related equipment, which can solve the problems of time-consuming, low efficiency and low efficiency in manual construction of a wide table.

[0006] In a first aspect, a wide table construction method is provided, which can include the following steps: receiving a wide table creation instruction sent by a user, and determining a plurality of columns of the wide table based on the wide table creation instruction, wherein the plurality of columns includes at least one first column and at least one second column, the at least one first column is from a fact table, and the at least one second column is from a dimension table, then creating a temporary table containing a primary key column, and establishing a wide table through the primary key column in the temporary table, and providing the wide table to the user, wherein the primary key column in the temporary table has a corresponding relationship with the at least one first column and the at least one second column.

[0007] In the above scheme, the user who has the wide table construction requirement only needs to input the wide table creation instruction, and the scheme can automatically determine the columns to be added to the wide table in the fact table and the dimension table based on the wide table creation instruction. After the columns contained in the wide table are determined, a temporary table containing the primary key column is created. Since the primary key column in the temporary table has a corresponding relationship with the columns to be added to the wide table in the fact table and the columns to be added to the wide table in the dimension table, the fact table and the dimension table can be associated according to the primary key column in the temporary table, the wide table is created and provided to the user. As can be seen, the entire wide table construction process is simple, fast and efficient. In some possible implementation manners, the temporary table containing the primary key column can be created and the wide table can be established through the primary key column in the temporary table by the following manner:

[0008] The first temporary table contains the corresponding relationship between the primary key column and at least one first column, and the second temporary table contains the corresponding relationship between the primary key column and at least one second column.

[0009] The wide table is obtained by associating the first temporary table and the second temporary table through the primary key column in the first temporary table and the primary key column in the second temporary table. In actual scenarios, the data volume of the columns to be added to the wide table in the fact table and the data volume of the columns to be added to the wide table in the dimension table are usually very large. For example, the fact table has several hundred or even several thousand columns to be added to the wide table, and these columns have hundreds of millions of rows of records. The dimension table has several tens or even several hundred columns to be added to the wide table, and these columns have hundreds of millions of rows of records. When creating the temporary table, if the columns to be added to the wide table in the fact table and the dimension table are all copied to the same temporary table, since the data volume to be copied is huge, and the data needs to be copied to the memory first and then processed to obtain the temporary table, the huge data volume will occupy a large amount of memory, and the large memory occupation will usually cause the performance of the scheme to decrease (for example, causing the execution speed to slow down). Therefore, in the above implementation manner, the columns to be added to the wide table in the fact table and the dimension table are copied to different temporary tables (i.e., the first temporary table and the second temporary table). In this way, different temporary tables can be processed separately, and only the data required for this processing needs to be copied to the memory, thereby reducing the data volume to be copied to the memory, reducing the memory pressure, and improving the performance of the scheme.

[0010] In some possible implementation manners, the number of the at least one second column is a plurality, and the dimension table includes a first dimension table and a second dimension table. The second temporary table can be created by the following steps: two second temporary tables are created, one of the two second temporary tables contains the corresponding relationship between the primary key column and the second column in the first dimension table, and the other of the two second temporary tables contains the corresponding relationship between the primary key column and the second column in the second dimension table.

[0011] In an actual scenario, the number of dimension tables participating in the construction of the wide table is usually multiple, and the data volume of the columns to be added to the wide table in each dimension table is usually very large. When creating the second temporary table, if the columns to be added to the wide table in multiple dimension tables are all copied to the same second temporary table, because the data volume to be copied is huge, and the data needs to be copied to the memory first and then processed to obtain the second temporary table, the huge data volume will occupy a large amount of memory, and the large memory occupation will usually cause the performance of the scheme to decrease (for example, causing the execution speed to decrease). Therefore, in the implementation manner described above, the columns to be added to the wide table in different dimension tables are copied to different second temporary tables, so that different second temporary tables can be processed separately, and when processed separately, only the data required for this processing needs to be copied to the memory, thereby reducing the data volume to be copied to the memory, reducing the memory pressure, and improving the performance of the scheme. In some possible implementation manners, before the wide table is obtained by associating the primary key column in the first temporary table and the primary key column in the second temporary table, the method further includes the following step: partitioning the first temporary table to obtain multiple partitions of the first temporary table.

[0012] In an actual scenario, because the first temporary table contains the primary key column and the columns to be added to the wide table in the fact table, the data volume of the first temporary table can be very large, for example, the first temporary table contains several hundred or even several thousand columns and hundreds of millions of rows of data. When the wide table is obtained by associating the first temporary table and the second temporary table, if the first temporary table is not partitioned, the first temporary table needs to be copied to the memory as a whole, and the wide table is obtained by associating the second temporary table. The huge data volume of the first temporary table as a whole will occupy a large amount of memory, and the large memory occupation will usually cause the performance of the scheme to decrease (for example, causing the execution speed to decrease). Therefore, in the implementation manner described above, the first temporary table is divided into multiple partitions, so that when the wide table is obtained by associating the first temporary table and the second temporary table, each partition of the first temporary table can be associated with the second temporary table separately. When each partition is associated with the second temporary table, only the data in the partition needs to be copied to the memory to be associated with the second temporary table, thereby reducing the data volume to be copied to the memory, reducing the memory pressure, and improving the performance of the scheme. In a second aspect, a wide table construction system is provided, and the system includes:

[0013] An instruction receiving unit is configured to receive a wide table creation instruction sent by a user.

[0014] A wide table construction unit is configured to determine multiple columns of the wide table based on the wide table creation instruction, wherein the multiple columns include at least one first column and at least one second column, the at least one first column is from a fact table, and the at least one second column is from a dimension table.

[0015] The wide table building unit is further configured to create a temporary table containing the primary key column, and build the wide table by the primary key column in the temporary table.

[0016] The wide table building unit is further configured to provide the wide table.

[0017] In some possible implementation manners, the wide table building unit is configured to create a first temporary table and a second temporary table, the first temporary table containing the correspondence between the primary key column and the at least one first column, and the second temporary table containing the correspondence between the primary key column and the at least one second column.

[0018] The wide table building unit is configured to associate the first temporary table and the second temporary table by the primary key column in the first temporary table and the primary key column in the second temporary table to obtain the wide table.

[0019] In some possible implementation manners, the wide table building unit is configured to create two second temporary tables, one of the two second temporary tables containing the correspondence between the primary key column and the second column in the first dimension table, and the other of the two second temporary tables containing the correspondence between the primary key column and the second column in the second dimension table.

[0020] In some possible implementation manners, before the wide table building unit associates the first temporary table and the second temporary table by the primary key column in the first temporary table and the primary key column in the second temporary table to obtain the wide table, the wide table building unit is further configured to partition the first temporary table to obtain a plurality of partitions of the first temporary table.

[0021] In a third aspect, a computing device cluster is provided, the computing device cluster comprising at least one computing device, each computing device of the at least one computing device comprising a processor and a memory, the processor of the at least one computing device being configured to execute instructions stored in the memory of the at least one computing device to cause the computing device cluster to implement the method described in the first aspect.

[0022] In a fourth aspect, a computer-readable storage medium is provided, the computer-readable storage medium storing instructions, the instructions being executed by a computing device or a computing device cluster to implement the method described in the first aspect.

[0023] In a fifth aspect, a computer program product containing instructions is provided, the computer program product comprising instructions, the instructions being executable on a computing device or being stored in any available medium or software or program product, the instructions causing the computing device or the computing device cluster to implement the method described in the first aspect when the computer program product is executed on the computing device or the computing device cluster. BRIEF DESCRIPTION OF DRAWINGS

[0024] Figure 1is an architecture diagram of a wide table construction system provided by the present application;

[0025] Figure 2 is an example diagram of a wide table construction system provided by the present application deployed in a cloud environment;

[0026] Figure 3 is a flow diagram of a wide table construction method provided by the present application;

[0027] Figure 4 is a detailed flow diagram of a wide table construction process provided by the present application;

[0028] Figure 5A is a diagram of a first temporary table partition provided by the present application;

[0029] Figure 5B is a diagram of a plurality of dimension tables in batches provided by the present application;

[0030] Figure 6A is a diagram of a second SQL statement associating a first temporary table and a plurality of dimension tables in batches provided by the present application;

[0031] Figure 6B is a diagram of another second SQL statement associating a first temporary table and a plurality of dimension tables in batches provided by the present application;

[0032] Figure 7A is an example diagram of a configuration interface in a wide table construction method provided by the present application;

[0033] Figure 7B is an example diagram of another configuration interface in a wide table construction method provided by the present application;

[0034] Figure 8 is a display interface diagram of a wide table provided by the present application;

[0035] Figure 9 is a structural diagram of a wide table construction system provided by the present application;

[0036] Figure 10 is a structural diagram of a computing device provided by the present application;

[0037] Figure 11 is a structural diagram of a computing device cluster provided by the present application;

[0038] Figure 12 is a structural diagram of another computing device cluster provided by the present application. DETAILED DESCRIPTION

[0039] The application scenarios involved in the present application are explained below.

[0040] In the database system of each enterprise, a wide table can accommodate various types of data (such as text, numbers, dates, images, and various forms of data), can store data in the same table related to a certain business (such as product sales business, logistics business, medical care business, advertising business, live broadcast business, etc.) and multiple dimension tables, can accommodate a large number of columns (also known as fields), thereby reducing the association operation between the fact table and multiple dimension tables when querying, simplifying the query statement, improving the query performance, supporting complex data analysis (such as aggregation, filtering, sorting, etc.) and report generation, simplifying data management and maintenance, etc. Advantages are widely used. Among them, the fact table is a table used to store measurement data (also known as measurement value), that is, transactional data, each row of the fact table usually represents a fact event, for example, the sales of a day, the order quantity of a customer, etc. The dimension table is a table used to store dimension data, for example, the customer dimension table usually stores customer ID, customer name, customer address, customer industry, etc. For example, the product dimension table usually stores product ID, product name, product category, product brand, etc. For example, the date dimension table usually stores sales date, year, month, quarter, day of the week, etc.

[0041] The following will illustrate the fact table, the dimension table and the wide table by combining two specific business scenarios.

[0042] Scenario one, product sales scenario of an enterprise

[0043] The fact table can be a product sales fact table, which can include measurement data of the product sales business, such as sales, sales quantity, sales cost, net profit, etc. Each row of record also includes columns such as sales date, product ID, customer ID, etc.

[0044] The dimension table can be the product dimension table, the customer dimension table and the date dimension table of the enterprise. The product dimension table can include product ID, product name, product category, product brand, product specification, etc. The customer dimension table can include customer ID, customer name, customer address, customer industry, etc. The date dimension table can include sales date, year, month, quarter, day of the week, etc.

[0045] The wide table can be a table that integrates the sales, sales quantity, sales cost, net profit, sales date, product ID, customer ID, etc. in the product sales fact table, integrates the product name, product category, product brand, product specification, etc. (i.e. dimension) in the product dimension table, integrates the customer name, customer address, customer industry, etc. in the customer dimension table, and also integrates the sales date, year, month, quarter, day of the week, etc. in the date dimension table.

[0046] Scenario two, medical care business scenario

[0047] The fact table can be a medical care fact table, and can include measurement data of medical care business, such as the number of visits, the amount of drug consumption, the cost of examination, etc., and each record further includes columns such as the date of visit, the doctor ID, the patient ID, etc.

[0048] The dimension table can be a doctor dimension table and a patient dimension table, wherein the doctor dimension table can include dimensions such as the doctor name, the doctor title, the doctor department, etc., and the patient dimension table can include dimensions such as the patient name, the patient gender, the patient age, etc.

[0049] The wide table can be a table that integrates the columns such as the number of visits, the amount of drug consumption, the cost of examination, the date of visit, the doctor ID, the patient ID in the medical care fact table, integrates the columns such as the doctor name, the doctor title, the doctor department in the doctor dimension table, and further integrates the columns such as the patient name, the patient gender, the patient age in the patient dimension table.

[0050] At present, the way for enterprises to build a wide table in a database system is usually as follows: a data table manager manually creates an empty wide table, then determines the columns in the fact table that need to be added to the wide table, manually copies these columns to the empty wide table, and then according to the correspondence between the foreign key of the wide table and the primary key of each dimension table, copies the columns in each dimension table that need to be added to the wide table to the wide table, or even in some scenarios, some values are manually selected from the columns in each dimension table that need to be added to the wide table and copied to the wide table, so as to realize the construction of the wide table.

[0051] However, when the data volume of the fact table is large (such as tens, hundreds of columns, hundreds of millions of rows), the number of dimension tables is also large (such as tens, hundreds), or the data volume of the dimension table is also large (such as tens, hundreds of columns, hundreds of millions of rows), the efficiency of building a wide table by the above-mentioned manual method is very low.

[0052] To solve the problem of low efficiency in building a wide table by manual method, the present application provides a wide table construction method and a wide table construction system. When a user needs to build a wide table, the user only needs to input a wide table construction instruction carrying wide table construction related information (such as the identifier of the fact table, the identifier of the dimension table, the identifier of the column in the fact table to be added to the wide table, the identifier of the column in the dimension table to be added to the wide table, etc.) to the wide table construction system, and the wide table construction system can automatically build a wide table according to the wide table construction instruction input by the user, and provide the wide table to the user. The whole process of building a wide table is simple and fast, and the construction efficiency is high.

[0053] Next, the wide table construction system and the wide table construction method provided by the present application will be introduced in detail with reference to the corresponding drawings.

[0054] Please refer to Figure 1 , Figure 1 is a schematic diagram of the architecture of a wide table construction system provided by the present application, as shown inFigure 1 As shown, the architecture includes a client 100, a wide table construction system 200, and a database system 300.

[0055] The wide table construction system 200 can be deployed on a computing device, or on a computing device cluster composed of multiple computing devices. The computing device can be a bare metal server (BMS), a virtual machine, or a container. The BMS refers to a general-purpose physical server, such as an ARM server or an X86 server. The virtual machine refers to a complete computer system that is simulated by software and runs in a completely isolated environment, and has complete hardware system functions. The work that can be completed in a physical computer can also be implemented in a virtual machine. When creating a virtual machine in a computing device, part of the hard disk and memory capacity of the physical machine needs to be used as the hard disk and memory capacity of the virtual machine. Each virtual machine has an independent basic input / output system (BIOS), hard disk, and operating system, and can be operated like a physical machine. The container is a portable software unit that can combine an application and all its dependencies into a software package that is not limited by the underlying host operating system, so that a complex environment no longer needs to be built, and the application development and deployment process is simplified. In a specific implementation, the computing device cluster can be a cloud data center, or an enterprise private cluster, or a hybrid cloud environment, that is, a deployment mode in which a public cloud and a private cloud are used at the same time, and the present application does not make specific limitations.

[0056] The database system 300 can be deployed in a computing device or a cluster of computing devices, and can also be deployed in a storage device or a storage array. The database system 300 is configured to support querying and storing data, and provide query and storage related services for the wide table construction system 200 to support the wide table construction system 200 to complete the wide table construction business. The computing device and the cluster of computing devices can be described as above, and thus will not be described here. The storage device can be a hard disk drive (HDD), a solid state disk (SSD), a mechanical hard disk (HDD), a universal serial bus (USB), a flash memory, a secure digital memory card (SD card), a memory stick, etc., which will not be specifically limited herein. The storage array can be a redundant array of independent disks (RAID), a network attached storage (NAS), a storage area network (SAN), etc., which will not be specifically limited herein.

[0057] The client 100 is deployed in a terminal device or a computing device, and is configured to implement human-computer interaction. The terminal device can include a personal computer, a smart phone, a wearable device, a palm-held processing device, a tablet computer, a mobile notebook, an augmented reality (AR) device, a virtual reality (VR) device, a smart conference device, etc., which will not be specifically limited herein. The computing device can be described as above, and thus will not be described here.

[0058] In specific implementations, the client 100 can be a software or an application running on a terminal device or a computing device controlled by a user, such as a personal computer (PC) client, a world wide web (web) client based on a browser, or an application (APP) client running on a mobile terminal, which will not be specifically limited herein. The user holding the client 100 can be an individual user or an enterprise user having a wide table construction demand, which will not be specifically limited herein.

[0059] Optionally, the client 100 can be a separate client dedicated to implementing the wide table construction function, such as a wide table construction tool, a wide table construction application, etc. Alternatively, the client 100 can also be a wide table construction function module or plug-in within an integrated software, which is not limited in the present application.

[0060] Optionally, the client 100 can also be a client of a cloud platform, such as a console of the cloud platform, which can be a web-based console or an application programming interface (API) based console, which is not limited in the present application. The console can provide a wide table construction cloud service to a user, and the user can obtain the use right of the wide table construction system 200 provided by the present application by purchasing the cloud service. Alternatively, the wide table construction method provided by the present application can be a sub-service of the comprehensive cloud service provided to the user by the console, which is not limited in the present application.

[0061] In a specific implementation, the client 100, the wide table construction system 200 and the database system 300 are respectively deployed on different computing devices or computing device clusters, or at least two of the client 100, the wide table construction system 200 and the database system 300 can be deployed on the same computing device or the same computing device cluster, which is not limited in the present application.

[0062] The client 100, the wide table construction system 200 and the database system 300 establish a communication connection through a network, which can be a wired connection or a wireless connection, and the network can be a public internet, an internal local area network (LAN), a virtual private network (VPN), a dedicated line such as a fiber line, a copper line, a satellite connection, etc., or a wireless network such as a wireless fidelity (Wi-Fi) network, a cellular network, etc., which is not limited in the present application. The number of clients 100 that establish a communication connection with the wide table construction system 200 can be one or more, and the number of database systems 300 that establish a communication connection with the wide table construction system 200 can be one or more, which is not limited in the present application. Figure 1 Taking two clients 100 and one database system 300 establishing a connection with the wide table construction system 200 as an example.

[0063] The possible deployment manners of the client 100, the wide table construction system 200 and the database system 300 are described in detail above, and in actual deployment, the client 100, the wide table construction system 200 and the database system 300 can be flexibly deployed in combination with specific application scenarios and business requirements. The actual deployment manners of the client 100, the wide table construction system 200 and the database system 300 are exemplarily described below in combination with specific application scenarios.

[0064] In an application scenario, the client 100, the wide table construction system 200 and the database system 300 can be deployed on office equipment in an enterprise, for example, the wide table construction system 200 is deployed on a server or a server cluster purchased by the enterprise, the client 100 is deployed on an office computer of the enterprise, and the database system 300 is deployed on a database server of the enterprise. A user can use the office computer to run the client 100, access the wide table construction system 200 through the client 100 to perform wide table construction, and store the wide table in the database system 300 after the wide table construction system 200 constructs the wide table, so as to enable the user to view the wide table.

[0065] In another application scenario, the wide table construction system 200 and the database system 300 are provided by a cloud service provider, and an enterprise can purchase related cloud services of the wide table construction system 200 and the database system 300 from the cloud service provider. Then, the wide table construction system 200 and the database system 300 are deployed on instances in a cloud data center of the cloud service provider, and the client 100 is a control console of a cloud platform. For example, Figure 2 is an example diagram of the wide table construction system 200 deployed in a cloud environment provided by the present application, as shown in Figure 2 A user can initiate a purchase request of a cloud service through the client 100. After the client 100 sends the purchase request to the cloud platform, the cloud platform can provide the client 100 with a cloud service use right of the wide table construction system 200, so that the user can access the wide table construction system 200 through the client 100 to perform wide table construction. After the wide table construction system 200 constructs a wide table, the wide table is stored in the database system 300, so as to enable the user to view the wide table.

[0066] It should be understood that the above application scenarios are used for exemplification, and the client 100, the wide table construction system 200 and the database system 300 can be flexibly deployed according to actual business requirements, which are not exemplarily described herein.

[0067] In order to more clearly understand the specific process of wide table construction performed by the architecture shown in Figure 1 the flowchart of the wide table construction method provided by the present application is shown in Figure 3 as shown in Figure 3 may include the following steps:

[0068] S301: The client receives a wide table creation instruction input by a user.

[0069] The description of the client can refer to the related content of the embodiments, which will not be repeated here. Figure 1 The related content of the embodiments will not be repeated here.

[0070] The user can be an individual user or an enterprise user who has a wide table construction requirement, and the present application does not make specific limitations.

[0071] The wide table creation instruction is used to instruct to create a wide table, specifically, to instruct to create a wide table according to a fact table and a dimension table, so that the subsequent wide table construction system creates a wide table according to the wide table creation instruction after receiving the wide table creation instruction. For detailed definitions of the fact table and the dimension table, please refer to the related description of the fact table and the dimension table above, which will not be repeated here. For the process of the wide table construction system creating a wide table according to the wide table construction instruction, please refer to the related description in S303 and S304, which will not be expanded here.

[0072] The wide table creation instruction can carry related information required for wide table creation, such as the identifier (such as the table name or access path, etc.) of the fact table, the identifier (such as the table name or access path, etc.) of at least one dimension table, the identifier (such as the column name or the serial number of the column, etc.) of at least one first column, the column name (such as the column name or the serial number of the column, etc.) of at least one second column, the association condition between the fact table and the dimension table, etc.

[0073] (2) The identifier of the dimension table indicates the dimension table, which can be used to realize the positioning of the dimension table.

[0074] It should be noted that since the number of dimension tables participating in wide table construction in actual scenarios is usually multiple, in the following embodiments, the wide table creation instruction carrying the identifiers of multiple dimension tables is taken as an example for description.

[0075] (3) The at least one first column is the column in the fact table to be added to the wide table, which can be part or all of the columns in the fact table, and the identifier of the at least one first column indicates the at least one first column, which can be used to realize the positioning of the at least one first column in the fact table.

[0076] It should be noted that since the number of columns in the fact table to be added to the wide table in actual scenarios is usually multiple, in the following embodiments, the at least one first column is taken as multiple first columns as an example for description.

[0077] (4) The at least one second column is the column in the dimension table to be added to the wide table, which can be part or all of the columns in the dimension table, and the identifier of the at least one second column indicates the at least one second column, which can be used to realize the positioning of the at least one second column in the dimension table.

[0078] It should be noted that since the columns to be added to the wide table in the dimension table in the actual scene are usually multiple, in the following embodiments, at least one second column is taken as an example of multiple second columns.

[0079] (5) The association condition between the fact table and the dimension table refers to a condition for associating the fact table and the dimension table to obtain a wide table. The association condition between the fact table and the dimension table can be divided into a simple association condition and a complex association condition:

[0080] (5.1) The simple association condition refers to a primary-foreign key correspondence relationship between the fact table and the dimension table, that is, when the primary key value corresponding to the record of the second column included in the dimension table satisfies the primary-foreign key correspondence relationship between the fact table and the dimension table, it can be determined that the record satisfies the association condition, and the record is copied to the wide table. Among them, the primary key value corresponding to the record of the second column included in the dimension table satisfies the primary-foreign key correspondence relationship between the fact table and the dimension table, which means that the foreign key column of the fact table has the same value as the primary key value corresponding to the record of the second column.

[0081] (5.2) The complex association condition refers to the primary-foreign key correspondence relationship between the fact table and the dimension table and the filtering condition of the second column included in the dimension table, that is, when the primary key value corresponding to the record of the second column included in the dimension table satisfies the primary-foreign key correspondence relationship between the fact table and the dimension table, and satisfies the filtering condition corresponding to the second column, it can be determined that the record satisfies the association condition, and the record is copied to the wide table.

[0082] The following illustrates the simple association condition and the complex association condition.

[0083] Taking the product sales fact table and the product dimension table in the above scenario one (i.e., the product sales scenario of an enterprise) as an example, the foreign key "product ID" of the product sales fact table corresponds to the primary key "product ID" of the product dimension table, the second column is the product type of the product dimension table (assuming that it includes A, B, C, and D four types), and the simple association condition is that the correspondence relationship between the foreign key "product ID" of the fact table and the primary key "product ID" of the product dimension table. For example, when the product sales fact table and the product dimension table are associated based on the simple association condition, only the "product ID" value in the row where the record of the product type of the product dimension table is located is the same as a certain "product ID" value of the fact table, the record of the product type is copied to the wide table.

[0084] Again taking the product sales fact table and product dimension table in the above-mentioned one (i.e., the product sales scenario of an enterprise) as an example, the foreign key "product ID" of the fact table corresponds to the primary key "product ID" of the product dimension table, the second column is the product type (assuming including A, B, C, and D four types) of the product dimension table, the complex association condition is the corresponding relationship between the foreign key "product ID" of the product sales fact table and the primary key "product ID" of the product dimension table and the filtering condition (product type is A and B), when associating the product sales fact table and the product dimension table based on the complex association condition, the "product ID" value in the row where the record of the product type of the product dimension table is located needs to be the same as a certain "product ID" value of the fact table, and the "product type" value in the row where the record of the product type of the product dimension table is located needs to be "A" or "B", and only in this case, the record of the product type is copied to the wide table.

[0085] In a specific implementation, the way in which the client receives the wide table creation instruction input by the user includes but is not limited to the following several ways:

[0086] (1) The user can input the wide table creation instruction through a command-line interface (CLI) provided by the client.

[0087] (2) The user can input the wide table creation instruction through an application programming interface (API) provided by the client.

[0088] (3) The client provides a GUI to the user, the GUI can display related information upload interfaces and a submission control required for wide table creation, such as a fact table identifier upload interface and a dimension table identifier upload interface, the user can upload the related information required for wide table creation through these interfaces, and then click the submission control, so as to realize the input of the wide table creation instruction.

[0089] S302: The client sends a wide table creation instruction to the wide table construction system, and correspondingly, the wide table construction system receives the wide table creation instruction sent by the client.

[0090] The client can send the wide table creation instruction to the wide table construction system at a fixed time, or can send it to the wide table construction system immediately after obtaining the wide table creation instruction input by the user, and the application does not make specific limitations.

[0091] S303: The wide table construction system determines a plurality of columns of the wide table based on the wide table creation instruction, the plurality of columns including a plurality of first columns and a plurality of second columns.

[0092] For the plurality of first columns and the plurality of second columns, please refer to the related description in S301, which will not be expanded here.

[0093] As can be known from S301, the wide table creation instruction can carry different information required for wide table creation. In the following, the process of determining the first columns and the second columns in the multiple columns of the wide table based on the wide table creation instruction is described in detail in combination with several wide table creation instructions carrying different information.

[0094] The wide table creation instruction 1 carries the identification of the fact table and the identification of the multiple dimension tables, and does not carry the identification of the first columns and the identification of the second columns.

[0095] The wide table construction system locates the fact table based on the identification of the fact table carried in the wide table creation instruction 1, and determines all the columns in the fact table as the first columns to be added to the wide table, and locates the multiple dimension tables based on the identification of the multiple dimension tables carried in the wide table creation instruction 1, and determines all the columns in the multiple dimension tables as the second columns to be added to the wide table.

[0096] The wide table creation instruction 2 carries the identification of the fact table, the identification of the multiple dimension tables, and the identification of the first columns, and does not carry the identification of the second columns.

[0097] The wide table construction system locates the fact table based on the identification of the fact table carried in the wide table creation instruction 2, and determines the first columns to be added to the wide table in the fact table based on the identification of the first columns carried in the wide table creation instruction 2, and locates the multiple dimension tables based on the identification of the multiple dimension tables carried in the wide table creation instruction 2, and determines all the columns in the multiple dimension tables as the second columns to be added to the wide table.

[0098] The wide table creation instruction 3 carries the identification of the fact table, the identification of the multiple dimension tables, the identification of the first columns, and the identification of the second columns.

[0099] The wide table construction system locates the fact table based on the identification of the fact table carried in the wide table creation instruction 3, and determines the first columns to be added to the wide table in the fact table based on the identification of the first columns carried in the wide table creation instruction 3, and locates the multiple dimension tables based on the identification of the multiple dimension tables carried in the wide table creation instruction 3, and determines the second columns to be added to the wide table in the multiple dimension tables based on the identification of the second columns carried in the wide table creation instruction 3.

[0100] It should be understood that the above wide table creation instructions 1 to 3 are only examples of the wide table creation instructions and should not be considered as specific limitations.

[0101] S304: The wide table construction system creates a temporary table containing the first primary key column, and establishes the wide table through the first primary key column in the temporary table, wherein the first primary key column in the temporary table has a corresponding relationship with the first columns and the second columns.

[0102] For details of the temporary table, the first primary key column, and the correspondence between the first primary key column and the plurality of first columns and the plurality of second columns, please refer to Figure 4 The related description is as follows.

[0103] Specifically, S304 can be implemented by Figure 4 as shown in steps S3041-S3043:

[0104] S3041: The wide table construction system creates a first temporary table, wherein the first temporary table contains the correspondence between the first primary key column and the plurality of first columns.

[0105] The first temporary table contains the correspondence between the first primary key column and the plurality of first columns, which means that the first temporary table contains the first primary key column and the plurality of first columns, and each value of the first primary key column can uniquely identify a record of the plurality of first columns.

[0106] The first primary key column can be a universally unique identifier (UUID), a globally unique identifier (GUID), an auto increment integer, etc., and the present application does not make specific limitations. Specifically, when the first primary key column is a UUID, the wide table construction system can generate the value of the first primary key column in the first temporary table through a UUID generation function; when the first primary key column is a GUID, the value of the first primary key column in the first temporary table can be generated through a GUID generation function; when the first primary key is an auto increment integer, the wide table construction system can automatically assign a unique integer value to each new record inserted into the first temporary table as the value of the first primary key column. In a specific implementation, the generation mode of the first primary key column can be carried by the wide table construction instruction input by the user to the client and sent to the wide table construction system by the client, or it can be the default of the wide table construction system, so that the wide table construction system can generate the first primary key column in the first temporary table based on the generation mode of the first primary key column input by the user / system default, and the present application does not make specific limitations. Specifically, the wide table construction system can generate a unique first primary key value for each copied record when copying each record of the plurality of first columns in the fact table to the first temporary table.

[0107] Regarding the first temporary table, the storage form of the first temporary table after being created by the wide table construction system can be the following two forms:

[0108] Form 1: The first temporary table is not partitioned and is stored in the form of a complete table.

[0109] Form 2: The first temporary table is partitioned and stored in the form of a partition.

[0110] In Form 2, the first temporary table is partitioned, which means that the records in the first temporary table are stored in different partitions according to different partitioning manners. The advantage of this is that the records in the first temporary table are evenly distributed to different partitions, and then when the first temporary table is actually operated (such as associating the first temporary table with other tables), partitioned execution can be performed, that is, only the data in the corresponding partition is loaded into the memory for operation each time, without loading the data in the entire table into the memory for operation, thereby reducing the memory occupation. For details, see Figure 5A In Form 2, the first temporary table is partitioned, which means that the records in the first temporary table are stored in different partitions according to different partitioning manners. The advantage of this is that the records in the first temporary table are evenly distributed to different partitions, and then when the first temporary table is actually operated (such as associating the first temporary table with other tables), partitioned execution can be performed, that is, only the data in the corresponding partition is loaded into the memory for operation each time, without loading the data in the entire table into the memory for operation, thereby reducing the memory occupation. For details, see Figure 5A In Form 2, the first temporary table is partitioned, which means that the records in the first temporary table are stored in different partitions according to different partitioning manners. The advantage of this is that the records in the first temporary table are evenly distributed to different partitions, and then when the first temporary table is actually operated (such as associating the first temporary table with other tables), partitioned execution can be performed, that is, only the data in the corresponding partition is loaded into the memory for operation each time, without loading the data in the entire table into the memory for operation, thereby reducing the memory occupation. For details, see Figure 5A Only as an example, in a specific implementation, the first temporary table can be divided into fewer or more partitions.

[0111] The partitioning manner of the first temporary table can be range partitioning, list partitioning, hash partitioning, round-robin partitioning, etc., which are not limited in the present application. Among them, range partitioning means that the data rows in the data table are distributed to different partitions according to different ranges of the partition key (which refers to the key field or field combination used for partitioning the data table (such as the first temporary table)), for example, when the partition key is the date column in the data table, the data rows can be distributed to different time partitions according to the date range; list partitioning means that the data rows in the data table are distributed to different partitions according to the discrete values of the partition key, for example, when the partition key is the geographic location column in the data table, the data rows can be distributed to different regional partitions according to the geographic location; hash partitioning means that the data rows in the data table are distributed to different partitions according to the hash values of the partition key, and hash partitioning is usually used for evenly distributing data rows; round-robin partitioning means that the data rows in the data table are distributed to different partitions in a round-robin manner.

[0112] In a specific implementation, the partition key, partitioning manner and number of partitions of the first temporary table can be carried by the wide table construction instruction input by the user to the client and sent to the wide table construction system by the client, or be defaulted by the wide table construction system, so that the wide table construction system can create the first temporary table in the corresponding form based on the partition key, partitioning manner and number of partitions input by the user / system default, which are not limited in the present application.

[0113] In a particular implementation, the wide table construction system can generate a first SQL statement for creating the first temporary table, and execute the first SQL statement to realize the creation of the first temporary table. The process of generating the first SQL statement for creating the first temporary table by the wide table construction system and the process of executing the first SQL statement are described in detail below.

[0114] (1) Process of generating the first SQL statement by the wide table construction system

[0115] It can be understood that the first SQL statement generated by the wide table construction system for creating the first temporary table will be different in the case of different storage forms of the first temporary table. For the two different storage forms of the first temporary table described above, the first SQL statement can have the following two cases:

[0116] For the first temporary table of form 1, the first SQL statement is case one: the first SQL statement is used to create an unpartitioned first temporary table.

[0117] For the first temporary table of form 2, the first SQL statement is case two: the first SQL statement is used to create a partitioned first temporary table.

[0118] It can be understood that the first SQL statement will be different in the case of different uses of the first SQL statement. The first SQL statement is different, and the process of generating different first SQL statements by the wide table construction system is different. The process of generating the first SQL statement by the wide table construction system is described below for the first SQL statement of the two cases.

[0119] For case one, the wide table construction system can generate a first SQL statement for creating an unpartitioned first temporary table according to the table name of the fact table and the column names of the plurality of first columns, and a first statement generation template of the first kind.

[0120] An exemplary first statement generation template of the first kind is given below, see template one as follows:

[0121] CREATE TABLE F_TMP (multiple first column names, seq_id varchar2(200)); / / F_TMP is the table name of the first temporary table, "seq_id" is the column name of the first primary key column, and "varchar2(200)" is the data type of the first primary key column "seq_id".

[0122] INSERT INTO F_TMP SELECT multiple first column names, uuid_generate_v1()::text as seq_id / / "uuid_generate_v1()::text as seq_id" means generating the value of the first primary key column "seq_id" through the uuid_generate_v1()::text function

[0123] FROM the table name of the fact table;

[0124] It should be noted that the content after " / / " is an introduction to the content of template one, and does not belong to template one.

[0125] In template one, the positions of Chinese characters (for example, "multiple first column names" and "the table name of the fact table") are fillable fields, which are used to fill in the corresponding content of Chinese characters, and the rest are fixed fields. The "CREATE TABLE" in the fixed field is a keyword of the creation statement, the "INSERT INTO" is a keyword of the insertion statement, and the "SELECT FROM" is a keyword of the query statement, which will not be expanded.

[0126] It should be understood that the above template one is only an example of the first first statement generation template, and the present application is not specifically limited, for example, the table name of the first temporary table can be other, each segment of code can be replaced by code with the same meaning, and so on.

[0127] In summary, the wide table construction system fills the table name of the fact table and the column names of the multiple first columns into the corresponding fillable fields in the first first statement generation template, and obtains the first SQL statement for creating the first temporary table without partitioning.

[0128] For case two, the wide table construction system can generate the first SQL statement for creating the first temporary table with partitioning according to the table name of the fact table, the column names of the multiple first columns, the partition key of the first temporary table, the partitioning mode and the partition number, and the second first statement generation template. In order to facilitate description, in the following embodiments, the partition key of the first temporary table is hash_id, the partitioning mode is list partitioning, and the partition number is 4.

[0129] An exemplary second first statement generation template is given below, see template two as follows:

[0130] CREATE TABLE F_TMP (column name of multiple first columns, seq_id varchar2(200), hash_id int) PARTITION BY range(hash_id) / / "hash_id" is the name of the partition key, "int" is the data type of the partition key, "PARTITION BY LIST(hash_id)" indicates that the data rows are partitioned in list partitioning manner according to the partition key hash_id (

[0132] PARTITION p000 VALUES(1), / / The PARTITION statement indicates that the data rows with the partition key value equal to 1 in the first temporary table are allocated to the partition p000

[0133] PARTITION p001 VALUES(2), / / The PARTITION statement indicates that the data rows with the partition key value equal to 2 in the first temporary table are allocated to the partition p001

[0134] PARTITION p002 VALUES(3), / / The PARTITION statement indicates that the data rows with the partition key value equal to 3 in the first temporary table are allocated to the partition p002

[0135] PARTITION p003 VALUES(4), / / The PARTITION statement indicates that the data rows with the partition key value equal to 4 in the first temporary table are allocated to the partition p003 );

[0137] INSERT INTO F_TMP SELECT multiple first column names, uuid_generate_v1()::text as seq_id, abs(hashtext(uuid_generate_v1()::text) % 4) as hash_id

[0138] FROM table name of fact table; / / "abs(hashtext(uuid_generate_v1()::text) % 4) as hash_id" is the value of the partition key "hash_id" generated by the abs(hashtext(uuid_generate_v1()::text) % 4) function

[0139] It should be noted that the content after " / / " is an explanation of the content of template two before " / / ", which does not belong to template two.

[0140] As can be seen from the template one, the difference between the template two and the template one is that the CREATE statement has "hash_id int", the INSERT statement has "abs(hashtext(uuid_generate_v1()::text)%4)as hash_id", and the code between "hash_id int" and INSERT. The meaning of the difference code has been introduced in the template two, see the content after " / / ". The same code of the template two and the template one has been introduced above, and will not be expanded.

[0141] It should be understood that the above template two is only an example of the second first statement generation template, and the present application is not limited thereto. For example, the table name of the first temporary table can be other, each piece of code can be replaced by the code with the same meaning, and the like.

[0142] In summary, according to the table name of the fact table, the column names of the plurality of first columns, the partition key of the first temporary table, the partition mode, the partition quantity, and the second first statement generation template, the wide table construction system can obtain the first SQL statement for creating the partitioned first temporary table.

[0143] (2) Process of running the first SQL statement by the wide table construction system

[0144] In a possible embodiment, in the case that the wide table construction system is integrated into the database system, the wide table construction system can directly run the first SQL statement to obtain and store the first temporary table.

[0145] In another possible embodiment, in the case that the wide table construction system is independent of the database system, the wide table construction system can send the first SQL statement to the database system, and the database system runs the first SQL statement to obtain and store the first temporary table.

[0146] S3042: The wide table construction system creates a second temporary table, wherein the second temporary table contains the correspondence between the first primary key column and the plurality of second columns.

[0147] The second temporary table contains the correspondence between the first primary key column and the plurality of second columns, which means that the second temporary table contains the first primary key column and the plurality of second columns, and each value of the first primary key column can uniquely identify a record of the plurality of second columns.

[0148] Specifically, the wide table construction system can associate the first temporary table and the dimension table according to the association condition between the fact table and the dimension table to obtain a second temporary table. Further, the wide table construction system can generate a second SQL statement for implementing association of the first temporary table and the dimension table according to the association condition between the fact table and the dimension table to obtain the second temporary table, and run the second SQL statement to implement creation of the second temporary table. The process of generating the second SQL statement by the wide table construction system and the process of running the second SQL statement are introduced in detail below.

[0149] (1) Process of generating the second SQL statement by the wide table construction system

[0150] In actual scenarios, the number of dimension tables associated with the fact table to implement creation of the wide table is usually multiple. Therefore, next, the process of generating the second SQL statement by the wide table construction system is introduced taking the number of dimension tables as multiple.

[0151] Specifically, the wide table construction system can generate multiple second SQL statements, the multiple second SQL statements and the multiple batches of dimension tables divided from the multiple dimension tables are in one-to-one correspondence, each second SQL statement is used for associating the first temporary table and each batch of dimension tables corresponding to each second SQL statement according to the association condition between the fact table and each batch of dimension tables corresponding to each second SQL statement to obtain a second temporary table, and the second temporary table includes the first primary key column and the second column in each batch of dimension tables corresponding to each second SQL statement.

[0152] The multiple dimension tables are divided into multiple batches of dimension tables, in the case that the number of the multiple dimension tables can be evenly divided into multiple batches, the number of the multiple dimension tables can be evenly divided to obtain, for example, Figure 5BAs shown, taking 24 dimension tables as an example, the 24 dimension tables can be divided into 3 batches of dimension tables, each batch including 8 dimension tables, and for example, when the plurality of dimension tables is 49, the 49 dimension tables can be divided into 7 batches of dimension tables, each batch including 7 dimension tables, or the plurality of dimension tables can be randomly divided, for example, when the plurality of dimension tables is 24, the 24 dimension tables can be divided into 4 batches of dimension tables, the first 2 batches each include 7 dimension tables, and the last 2 batches each include 5 dimension tables, for example, when the plurality of dimension tables is 49, the 49 dimension tables can be divided into 8 batches of dimension tables, the first 7 batches each include 6 dimension tables, and the 8th batch includes 7 dimension tables; when the number of the plurality of dimension tables cannot be evenly divided into multiple batches, the plurality of dimension tables can be randomly divided, for example, when the plurality of dimension tables is 23, the 23 dimension tables can be divided into 3 batches, the first 2 batches each include 8 dimension tables, and the 3rd batch includes the remaining 7 dimension tables, for example, when the plurality of dimension tables is 37, the 37 dimension tables can be divided into 5 batches, the first 4 batches each include 8 dimension tables, and the 5th batch includes 5 dimension tables, and the present application does not specifically limit the manner in which the plurality of dimension tables is divided into multiple batches of dimension tables. It should be understood that Figure 5B For example only, in a specific implementation, the 24 dimension tables can be divided into fewer or more batches of dimension tables.

[0153] It can be understood that, since the plurality of second SQL statements and the plurality of batches of dimension tables are in a one-to-one correspondence, taking the i-th second SQL statement (hereinafter referred to as second SQL statement i) as an example, the second SQL statement i is used to associate the first temporary table and the i-th batch of dimension tables according to the association condition between the fact table and the dimension table corresponding to the second SQL statement i (i.e., the i-th batch of dimension tables) to obtain a second temporary table Ti.

[0154] The second SQL statement i is used to obtain the second temporary table Ti, and the second temporary table Ti includes the first primary key column and the second column in the i-th batch of dimension tables. It can be seen that, once the second SQL statement i is executed, the second temporary table Ti can be obtained, and the obtained second temporary table Ti is a data table that has copied the first primary key column in the first temporary table and the second column in each i-th batch of dimension tables, and each value of the first primary key column copied by the second temporary table Ti can uniquely identify the data row in the second temporary table Ti.

[0155] The plurality of second SQL statements will be introduced below taking the second SQL statement i as an example.

[0156] The second SQL statement i can have the following two cases:

[0157] Case 1: For the unpartitioned first temporary table in form 1 in S3041, the second SQL statement i is used to associate the unpartitioned first temporary table and the i-th batch of dimension tables to obtain a second temporary table.

[0158] The three-batch dimension table shown in FIG. 6 is taken as an example. The second SQL statement 1 is used to associate the unpartitioned first temporary table and the first batch dimension table. As shown in FIG. 7, the second SQL statement 1 is used to associate the whole first temporary table with the first batch dimension table. Figure 5B Figure 6A The second SQL statement i is used to associate the unpartitioned first temporary table and the i-th batch dimension table. It is indicated that the second SQL statement i is used to copy the data of the whole first temporary table from the disk where the first temporary table is located to the memory, and then perform the association operation with the i-th batch dimension table.

[0159] The second SQL statement i is used to associate the unpartitioned first temporary table and the i-th batch dimension table. It is indicated that the second SQL statement i is used to copy the data of the whole first temporary table from the disk where the first temporary table is located to the memory, and then perform the association operation with the i-th batch dimension table.

[0160] Case 2: For the partitioned first temporary table of Form 2 in S3041, the second SQL statement i is used to associate the partitioned first temporary table and the i-th batch dimension table to obtain the second temporary table.

[0161] Specifically, the second SQL statement i includes a plurality of sub-statements, and the plurality of sub-statements are in a one-to-one correspondence with the plurality of partitions of the first temporary table. Each sub-statement is used to associate the corresponding partition of the first temporary table and the i-th batch dimension table to obtain an association result, and write the association result into the second temporary table. Taking i as 1, the plurality of partitions of the first temporary table are the four partitions shown in FIG. 8, and the multi-batch dimension table is the three-batch dimension table shown in FIG. 6. The second SQL statement 1 is used to associate the partitioned first temporary table and the first batch dimension table. As shown in FIG. 9, the second SQL statement 1 is used to respectively associate each partition of the first temporary table with the first batch dimension table. Figure 5A Figure 5B The second SQL statement i is used to associate the unpartitioned first temporary table and the i-th batch dimension table. It is indicated that the second SQL statement i is used to copy the data of the whole first temporary table from the disk where the first temporary table is located to the memory, and then perform the association operation with the i-th batch dimension table. Figure 6B

[0162] Each sub-statement is used to associate the corresponding partition of the first temporary table and the i-th batch dimension table. It is indicated that each sub-statement is used to copy the part of the data of the first temporary table included in the corresponding partition of the first temporary table from the corresponding partition of the first temporary table to the memory, and then perform the association operation with the i-th batch dimension table.

[0163] ​​​In case 2, the second SQL statement i is executed by multiple sub-statements respectively on the partitions of the corresponding first temporary table and the association operation of the i-th batch of dimension tables, compared with the second SQL statement i in case 1 directly executing the association operation of the i-th batch of dimension tables on the whole first temporary table. Since the second SQL statement in case 1 needs to copy the data in the memory to the whole data of the first temporary table during runtime, when the data amount of the whole first temporary table is much larger than the memory limit, the second SQL statement in case 1 during runtime will cause the data in the memory to be written to the disk, and the data writing to the disk will cause the subsequent association operation to read data from the disk, which will cause the association operation time to be greatly lengthened. However, each sub-statement in case 2 during runtime needs to copy the data in the memory to only part of the data of the first temporary table, and the occupied memory will be less, so as to reduce or even avoid data writing to the disk, and the subsequent association operation can read data from the memory, which can reduce the association operation time and improve the association operation efficiency, thereby improving the wide table construction efficiency.

[0164] It can be understood that the second SQL statement i will be different in different use cases of the second SQL statement i. When the second SQL statement i is different, the process of the wide table construction system generating different second SQL statements i is different. The following will introduce the process of the wide table construction system generating the second SQL statement i respectively for the two cases.

[0165] For case 1, the wide table construction system can generate the second SQL statement i according to the table name of the first temporary table, the column name of the first primary key column, the table name of the i-th batch of dimension tables, the column name of the second column in the i-th batch of dimension tables, the association condition between the fact table and the i-th batch of dimension tables, and the first second statement generation template.

[0166] The following gives an exemplary first second statement generation template, see the following template 1:

[0167] INSERT INTO F_TMP_A SELECT F.seq-id, alias of dimension table Tii. column name of the first second column in dimension table Tii, alias of dimension table Tii. column name of the second second column in dimension table Tii, …, alias of dimension table Tii. column name of the last second column in dimension table Tii, alias of dimension table Ti2. column name of the first second column in dimension table Ti2, alias of dimension table Ti2. column name of the second second column in dimension table Ti2, …, alias of dimension table Ti2. column name of the last second column in dimension table Ti2, … / / “F_TMP_A” is the table name of the second temporary table Ti, “F” in “F.seq-id” is the alias of the first temporary table F_TMP, “seq-id” in “F.seq-id” is the column name of the first primary key, dimension table Tii is the first dimension table in the ith batch of dimension tables, dimension table Ti2 is the second dimension table in the ith batch of dimension tables, and “…” means that the above is extrapolated

[0168] FROM F_TMP F

[0169] LEFT JOIN table name of dimension table Tii alias of dimension table Tii

[0170] ON foreign key name corresponding to the primary key of dimension table Tii in F.F_TMP = primary key name of dimension table Tii

[0171] AND filter condition corresponding to the second column in dimension table Tii / / The LEFT JOIN ON AND statement is used to fill in the association condition between F_TMP and dimension table Tii, i.e. the association condition between the fact table and dimension table Tii. It should be noted that when the association condition between the fact table and dimension table Tii is a simple association condition, the simple association condition (i.e. the primary-foreign key correspondence between the fact table and dimension table Tii) is filled into the “ON” statement, and the “AND” statement is removed. When the association condition between the fact table and dimension table Tii is a complex association condition, the primary-foreign key correspondence between the fact table and dimension table Tii is filled into the “ON” statement, and the filter condition corresponding to the second column in dimension table Tii is filled into the “AND” statement

[0172] LEFT JOIN table name of dimension table Ti2 alias of dimension table Ti2

[0173] ON foreign key name corresponding to the primary key of dimension table Ti2 in F.F_TMP = primary key name of dimension table Ti2

[0174] The second column in the AND dimension table Ti2 corresponds to the filter condition / / The LEFT JOIN ON AND statement is used to fill in the association condition between F_TMP and the dimension table Ti2, that is, the association condition between the fact table and the dimension table Ti2, which is filled in the same way as the association condition between the fact table and the dimension table Ti1 described above, and will not be expanded

[0175] … / / The ellipsis indicates that in the case of including more dimension tables in the ith batch of dimension tables, the above LEFT JOIN ON AND statement in the template 1 is used for analogy filling, and will not be expanded

[0176] It should be noted that the content after " / / " is an introduction to the content of the template 1 before " / / ", which does not belong to the template 1.

[0177] In the template 1, the positions of Chinese characters (such as "table name of the dimension table Ti1", "column name of the first second column in the dimension table Ti1", etc.) are fillable fields, which are used to fill in the corresponding content of the Chinese characters, and the rest are fixed fields.

[0178] It should be understood that the above template 1 is only an example of the first second statement generation template, and the present application is not limited to specific examples, for example, the table name of the second temporary table can be other, each segment of code can be replaced by code with the same meaning, etc.

[0179] In summary, the wide table construction system can obtain the second SQL statement i according to the table name of the first temporary table, the column name of the first primary key column, the table name of the ith batch of dimension tables, the column name of the second column in the ith batch of dimension tables, the association condition between the fact table and the ith batch of dimension tables, and the first second statement generation template.

[0180] It should be noted that in specific implementations, the table names of the multiple second temporary tables obtained by the multiple second SQL statements are different, for example, the table names of the multiple second temporary tables can correspond to F_TMP_A, F_TMP_B, F_TMP_C…, can also be TMP_A, TMP_B, TMP_C…, or other, and the present application is not limited to specific examples.

[0181] For case 2, the wide table construction system can generate a sub-statement corresponding to the jth partition of the first temporary table (hereinafter referred to as sub-statement Zj) according to the table name of the first temporary table, the column name of the first primary key column, the table name of the ith batch of dimension tables, the column name of the second column in the ith batch of dimension tables, the association condition between the fact table and the ith batch of dimension tables, the partition value corresponding to the jth partition of the first temporary table, and the second second statement generation template. After obtaining the sub-statements corresponding to all partitions of the first temporary table, the combination of the sub-statements corresponding to all partitions of the first temporary table is the second SQL statement i.

[0182] The partition value corresponding to the jth partition of the first temporary table refers to the value referenced by the first SQL statement when assigning data rows in the first temporary table to the jth partition. For example, in the "PARTITION p000 VALUES(1)" in the above template two, the partition value referenced by the first SQL statement when assigning data rows in the first temporary table to the partition p000 is the partition key "hash_id" value equal to 1. For example, in the "PARTITION p000 VALUES(2)" in the above template two, the partition value referenced by the first SQL statement when assigning data rows in the first temporary table to the partition p001 is the partition key "hash_id" value equal to 2.

[0183] An exemplary second second statement generation template is given below, see the following template two:

[0184] INSERT INTO F_TMP_A SELECT F.seq-id, alias of dimension table T11. column name of the first second column in the dimension table T11, alias of dimension table T11. column name of the second second column in the dimension table T11, …, alias of dimension table T11. column name of the last second column in the dimension table T11, alias of dimension table T12. column name of the first second column in the dimension table T12, alias of dimension table T12. column name of the second second column in the dimension table T12, …, alias of dimension table T12. column name of the last second column in the dimension table T12, …

[0185] FROM F_TMP F

[0186] LEFT JOIN table name of dimension table T11 alias of dimension table T11

[0187] ON foreign key name corresponding to the primary key of the dimension table T11 in F.F_TMP = primary key name of the dimension table T11 in the alias of the dimension table T11

[0188] AND the second column in the dimension table T11 corresponds to the filtering condition

[0189] LEFT JOIN table name of dimension table T12 alias of dimension table T12

[0190] ON foreign key name corresponding to the primary key of the dimension table T12 in F.F_TMP = primary key name of the dimension table T12 in the alias of the dimension table T12

[0191] AND the second column in the dimension table T12 corresponds to the filtering condition

[0192] …

[0193] WHERE F.hash_id=F_TMP; / / this WHERE statement is used to fill in the partition value corresponding to the jth partition of F_TMP, which indicates that the jth partition of F_TMP is associated with the ith batch of dimension tables

[0194] It should be noted that the content after " / / " is an introduction to the content of template 2 before " / / ", which does not belong to template 2.

[0195] As can be seen from template 1, the difference between template 2 and template 1 is only that template 2 has a last WHERE statement. The meaning of the WHERE statement has been introduced in template 2. For the same code of template 2 as template 1, please refer to the relevant introduction of template 1 above, which will not be expanded.

[0196] It should be understood that the above template 2 is only an example of the second second statement generation template, and the present application is not limited to this. For example, the table name of the first temporary table can be other, and each segment of code can be replaced by code with the same meaning.

[0197] In summary, according to the table name of the first temporary table, the column name of the first primary key column, the table name of the ith batch of dimension tables, the column name of the second column in the ith batch of dimension tables, the association condition between the fact table and the ith batch of dimension tables, the partition value corresponding to the jth partition of the first temporary table, and the second second statement generation template, the sub-statement Zj can be obtained. The generation mode of the sub-statement corresponding to the remaining partitions of the first temporary table is the same as that of the sub-statement Zj, which will not be expanded. After obtaining the sub-statements corresponding to all partitions of the first temporary table, the combination of the sub-statements corresponding to all partitions of the first temporary table is the second SQL statement i.

[0198] In a possible embodiment, for case 2, in the case of a large number of partitions of the first temporary table, the wide table construction system can also divide the multiple partitions of the first temporary table into multiple batch partitions. For example, assuming that the first temporary table has 16 partitions, it can be divided into 4 batches, each including 4 partitions. In specific implementation, the partition division can be uniform division or random division, which is not limited in the present application. After dividing the multiple partitions of the first temporary table into multiple batch partitions, the wide table construction system can generate the sub-statement corresponding to the tth batch partition according to the table name of the first temporary table, the column name of the first primary key column, the table name of the ith batch of dimension tables, the column name of the second column in the ith batch of dimension tables, the association condition between the fact table and the ith batch of dimension tables, the partition value corresponding to the tth batch partition, and the third second statement generation template. After obtaining the sub-statements corresponding to all batch partitions of the first temporary table, the combination of the sub-statements corresponding to all batch partitions of the first temporary table is the second SQL statement i.

[0199] The third second statement generation template is similar to the second second statement generation template. Taking the second second statement generation template corresponding to the template 2 as an example, the difference between the third second statement generation template and the template 2 is that the last WHERE statement of the third second statement generation template is used to fill in the partition value range corresponding to the t-th batch partition of the first temporary table F_TMP:

[0200] WHERE F.hash_id=(F_TMP's t-th batch partition corresponding partition value range); / / The WHERE statement indicates the association of the first temporary table F_TMP's t-th batch partition and the i-th batch dimension table

[0201] In summary, the wide table construction system can obtain the t-th sub-statement according to the table name of the first temporary table, the column name of the first primary key column, the table name of the i-th batch dimension table, the column name of the second column in the i-th batch dimension table, the association condition between the fact table and the i-th batch dimension table, the partition value corresponding to the t-th batch partition of the first temporary table, and the third second statement generation template. The generation mode of the sub-statement corresponding to the remaining batch partitions of the first temporary table is the same as that of the t-th sub-statement, which will not be described in detail. After obtaining the sub-statements corresponding to all batch partitions of the first temporary table, the combination of the sub-statements corresponding to all batch partitions of the first temporary table is the second SQL statement i.

[0202] The wide table construction system generates the remaining second SQL statements in the plurality of second SQL statements in the same way as the generation of the second SQL statement i. For the sake of brevity of the description, the generation of the remaining second SQL statements will not be described in detail.

[0203] (2) Process of running the second SQL statement by the wide table construction system

[0204] In a possible embodiment, in the case where the wide table construction system is integrated into the database system, the wide table construction system can directly run the plurality of second SQL statements to obtain and store the plurality of second temporary tables.

[0205] In another possible embodiment, in the case where the wide table construction system is independent of the database system, the wide table construction system can send the plurality of second SQL statements to the database system, and the database system runs the plurality of second SQL statements to obtain and store the plurality of second temporary tables.

[0206] S3043: The wide table construction system associates the first temporary table and the second temporary table to obtain the wide table through the first primary key column in the first temporary table and the first primary key column in the second temporary table.

[0207] Since the first temporary table contains multiple first columns and the multiple second temporary tables contain multiple second columns, the wide table obtained by joining the first temporary table and the multiple second temporary tables will contain multiple first columns from the first temporary table and multiple second columns from the multiple second temporary tables. Since the multiple first columns from the first temporary table are copied from the fact table and the multiple second columns from the multiple second temporary tables are copied from the multiple dimension tables, the columns to be added to the wide table from the fact table (i.e., multiple first columns) and the columns to be added to the wide table from the multiple dimension tables (i.e., multiple second columns) are thus added to the wide table.

[0208] In its implementation, the wide table construction system can generate a third SQL statement. This third SQL statement is used to join the first and second temporary tables using the first primary key column of the first temporary table to obtain the wide table, and then executes the third SQL statement to retrieve the wide table. The following sections detail the process of generating and executing the third SQL statement.

[0209] (1) The process of the wide table building system generating third-party SQL statements

[0210] Specifically, the wide table construction system can generate a third SQL statement based on the table name of the first temporary table, the column name of the first primary key column, the table names of multiple second temporary tables, the column names of multiple first columns included in the first temporary table, the column names of multiple second columns included in the multiple second temporary tables, and the third statement generation template.

[0211] Below is an example template for generating a third statement, see template M below:

[0212] INSERT INTO TARGET_TABLE SELECT F.F_TMP (first column name), F.F_TMP (second column name), ..., F.F_TMP (last column name), A.F_TMP_A (first and second columns), A.F_TMP_A (second and second columns), ..., A.F_TMP_A (last and second columns), ... / / TARGET_TABLE is the name of the wide table. "F.F_TMP" represents the alias of the first temporary table. "A.F_TMP_A" represents the alias of the first and second temporary tables. "B.F_TMP_B" represents the alias of the second and second temporary tables. "..." indicates that the results are deduced from the previous results.

[0213] FROM F_TMP F

[0214] LEFT JOIN F_TMP_AA ON F.seq_id = A.seq_id / / seq_id is the column name of the first primary key column. This LEFT JOIN ON statement means joining the first temporary table F_TMP and the second temporary table F_TMP_A using the same first primary key column seq_id.

[0215] LEFT JOIN F_TMP_B B ON F.seq_id=B.seq_id / / This LEFT JOIN ON statement joins the first temporary table F_TMP and the second temporary table F_TMP_B using the same first primary key column seq_id.

[0216] LEFT JOIN F_TMP_C C ON F.seq_id=C.seq_id; / / This LEFT JOIN ON statement joins the first temporary table F_TMP and the second temporary table F_TMP_C using the same first primary key column seq_id.

[0217] … / / The ellipsis indicates that if there are more second temporary tables, the filling will be performed similarly based on the LEFT JOIN statement in template M, which will not be elaborated further.

[0218] It should be noted that the content after " / / " is a description of the content of template M preceding " / / ", and is not part of template M.

[0219] In template M, the position of the Chinese character (such as "the first column name", "the first column name") is a fillable field, which is used to fill in the content corresponding to the Chinese character, and the rest are fixed fields.

[0220] It should be understood that the above template M is merely an example of a template for generating the third statement, and this application does not impose any specific limitations. For example, the names of the first temporary table and each of the second temporary tables can be other, and each code segment can be replaced with code that has the same meaning, etc.

[0221] In summary, the wide table construction system can generate the third SQL statement based on the table name of the first temporary table, the column name of the first primary key column, the table names of multiple second temporary tables, the column names of multiple first columns included in the first temporary table, the column names of multiple second columns included in the multiple second temporary tables, and the template for the third statement.

[0222] (2) The process of the wide table construction system running a third SQL statement

[0223] In one possible implementation, in the case that the wide table construction system is integrated in the database system, the wide table construction system can directly run the third SQL statement to obtain the wide table and store it.

[0224] In another possible implementation, in the case that the wide table construction system is independent of the database system, the wide table construction system can send the third SQL statement to the database system, and the database system runs the third SQL statement to obtain the wide table and store it.

[0225] It should be noted that, Figure 4 The specific implementation steps of S304 shown in the figure are only examples and should not be considered as a limitation of the specific implementation process of S304. For example, in a specific implementation, the specific implementation process of S304 can be that the wide table construction system can first create two first temporary tables, wherein the first first temporary table contains the correspondence between the first primary key column and part of the columns in the plurality of first columns, and the second first temporary table contains the correspondence between the first primary key column and the remaining columns in the plurality of first columns, then the wide table construction system executes S3042 to obtain the second temporary table, and finally the wide table construction system associates the two first temporary tables and the second temporary table through the first primary key column in the two first temporary tables and the first primary key column in the second temporary table to obtain the wide table. For another example, in a specific implementation, the specific implementation process of S304 can be that the wide table construction system can first divide the plurality of first columns into three batches, and create three first temporary tables based on the three batches of columns, wherein the first first temporary table contains the correspondence between the first primary key column and the first batch of columns, the second first temporary table contains the correspondence between the first primary key column and the second batch of columns, and the third first temporary table contains the correspondence between the first primary key column and the third batch of columns, then the wide table construction system executes S3042 to obtain the second temporary table, and finally the wide table construction system associates the two first temporary tables and the second temporary table through the first primary key column in the three first temporary tables and the first primary key column in the second temporary table to obtain the wide table. The process of the wide table construction system creating two / three first temporary tables is similar to the process of the wide table construction system creating the first temporary table described in S3041. For brevity of the specification, please refer to the relevant description in S3041, which will not be described here. The process of the wide table construction system associating the two / three first temporary tables and the second temporary table through the first primary key column in the two / three first temporary tables and the first primary key column in the second temporary table to obtain the wide table is similar to the process of the wide table construction system associating the first temporary table and the second temporary table through the first primary key column in the first temporary table and the first primary key column in the second temporary table to obtain the wide table described in S3043. For brevity of the specification, please refer to the relevant description in S3043, which will not be described here.

[0226] S305: The wide table construction system sends the wide table to the client, and correspondingly, the client receives the wide table sent by the wide table construction system.

[0227] In a particular implementation, the wide table construction system can send the wide table to the client at a regular time, can send the wide table to the client immediately after obtaining the wide table, or can send the wide table to the client after receiving a wide table query request sent by the client, and the present application does not make a specific limitation.

[0228] S306: The client displays the wide table.

[0229] Optionally, the client can display the fact table and the dimension table together when displaying the wide table, so as to facilitate the user to view and compare.

[0230] It can be seen that in the wide table construction method provided by the present application, the user only needs to input a wide table construction instruction carrying wide table construction related information (such as the identifier of the fact table, the identifier of the dimension table, the identifier of the column in the fact table to be added to the wide table, the identifier of the column in the dimension table to be added to the wide table, etc.) to the wide table construction system, and then waits for the wide table construction system to complete the wide table construction. The whole wide table construction process is simple, fast and efficient.

[0231] In order to better understand the technical effects of the present application, the interface diagrams that can appear in the wide table construction method provided by the present application are exemplarily illustrated below in combination with Figure 7A , Figure 7B and Figure 8 .

[0232] Figure 7A is an example diagram of a configuration interface in a wide table construction method provided by the present application. The interface is an exemplary display, and the present application does not make a specific limitation. As shown in Figure 7A , the configuration interface includes a fact table configuration area 710, a dimension table configuration area 720, a dimension table configuration area 730, a dimension table adding control 740, and a wide table construction control 750.

[0233] The fact table configuration area 710 includes a fact table table name input box and a column name input box, which are used for the user to input the fact table table name and the column name in the fact table to be added to the wide table.

[0234] The dimension table configuration area 720 includes a dimension table table name input box and a column name input box, which are used for the user to input a dimension table table name and a column name in the dimension table to be added to the wide table.

[0235] The dimension table configuration area 730 includes a dimension table table name input box and a column name input box, which are used for the user to input another dimension table table name and a column name in the dimension table to be added to the wide table.

[0236] The dimension table adding control 740 is used for the user to click, and after clicking, the configuration interface shown in Figure 7B is displayed. The user can add a dimension table in the Figure 7BIn the dimension table configuration area 760 shown in the configuration interface, you can continue to enter the name of another dimension table and the column name to be added to the wide table in this dimension table.

[0237] The wide table control 750 is designed for user clicks. After clicking, the wide table building system begins to build the wide table based on the information entered by the user in the dimension table configuration areas such as the fact table configuration area 710, the dimension table configuration area 720, and the dimension table configuration area 730.

[0238] Specifically, if the user enters the corresponding information in the dimension table configuration areas such as the fact table configuration area 710, dimension table configuration area 720, and dimension table configuration area 730, and clicks the build wide table control 750, the client executes... Figure 3 As shown in S301 to S302, the wide table construction system executes S303 to S304 to obtain the wide table.

[0239] For example, Figure 8 This is a display interface diagram of a wide table provided in this application. This interface is an exemplary demonstration, and this application does not impose any specific limitations on it. Figure 8 As shown, the display interface includes a wide table display area 810 and a download control 820.

[0240] The wide table display area 810 is used to display the constructed wide table to the user.

[0241] The download control 820 is used by users to click and download wide tables.

[0242] It should be noted that the above Figure 7A , Figure 7B , Figure 8 Examples are provided for illustration only and are not intended to be specific. For instance, in a specific implementation, Figure 7A The configuration interface can also display the associated condition configuration area and the partition information configuration area. Figure 7A (Not shown), the association condition configuration area is used by the user to input the association conditions between the fact table and multiple dimension tables, and the partition information configuration area is used by the user to input the partitioning method and number of partitions for the first temporary table, etc. For example, Figure 7A The configuration interface can also include fact table and dimension table upload interfaces, allowing users to upload fact tables and multiple dimension tables. After uploading the fact table and multiple dimension tables, users can open the fact table and multiple dimension tables in the configuration interface, select the columns to be added to the wide table, and click submit. The wide table building system can then obtain the table name and the column name to be added to the wide table based on the table opened by the user in the configuration interface and the selected columns to be added to the wide table.

[0243] It should be understood that the size of the serial number of each step in the above embodiments does not mean the order of execution, and the execution order of each process should be determined according to its function and inherent logic, and should not constitute any limitation on the implementation process of the embodiments of the present application.

[0244] The architecture of the wide table construction system and the wide table construction method provided by the present application are described in detail above. The following describes the wide table construction system provided by the present application in combination with Figure 9 The unit modules in the wide table construction system provided by the present application are explained and described.

[0245] Figure 9 FIG. 1 is a structural schematic diagram of a wide table construction system 200 provided by the present application. The system 200 can be a wide table construction system provided by the present application. Figures 1-8 The wide table construction system described in the embodiments, such as the wide table construction system 200 shown in FIG. 1, can include an instruction receiving unit 211 and a wide table construction unit 212. The functions of each unit module of the wide table construction system 200 are exemplarily introduced below. Figure 9 The instruction receiving unit 211 is configured to receive a wide table creation instruction sent by a user. The wide table construction instruction can carry an identifier of a fact table, an identifier of a dimension table, an identifier of at least one first column, and an identifier of at least one second column, etc.

[0246] The wide table construction unit 212 is configured to determine a plurality of columns of a wide table based on the wide table creation instruction, wherein the plurality of columns include at least one first column and at least one second column, the at least one first column is from the fact table, and the at least one second column is from the dimension table. The number of dimension tables can be one or more.

[0247] The wide table construction unit 212 is further configured to create a temporary table containing a first primary key column, and establish the wide table through the first primary key column in the temporary table, wherein the first primary key column has a corresponding relationship with the at least one first column, and the first primary key column also has a corresponding relationship with the at least one second column.

[0248] The wide table construction unit 212 is further configured to provide the wide table.

[0249] In some possible embodiments, the wide table construction unit 212 is configured to create a first temporary table and a second temporary table, the first temporary table contains a corresponding relationship between the first primary key column and the at least one first column, and the second temporary table contains a corresponding relationship between the first primary key column and the at least one second column; the wide table construction unit 212 is configured to associate the first temporary table and the second temporary table through the first primary key column in the first temporary table and the first primary key column in the second temporary table to obtain the wide table.

[0250]

[0251] ​In some possible embodiments, the wide table building unit 212 is configured to create two second temporary tables, one of the two second temporary tables containing the correspondence between the first primary key column and the second column in the first dimension table, and the other of the two second temporary tables containing the correspondence between the first primary key column and the second column in the second dimension table. Here, the number of the first dimension tables can be one or more, and the number of the second dimension tables can be one or more.

[0252] In some possible embodiments, the wide table building unit 212 is further configured to partition the first temporary table to obtain a plurality of partitions of the first temporary table, before associating the first temporary table and the second temporary table to obtain the wide table through the first primary key column in the first temporary table and the first primary key column in the second temporary table.

[0253] In specific implementations, the instruction receiving unit 211 and the wide table building unit 212 can be implemented by software or by hardware. For example, the implementation of the wide table building unit 212 is described below. Similarly, the implementation of the instruction receiving unit 211 can refer to the implementation of the wide table building unit 212.

[0254] In an example in which the units are implemented by software, the wide table building unit 212 can include code running on a computing instance. Here, the computing instance can include at least one of a physical host (computing device), a virtual machine, and a container. Further, the computing instance can be one or more. For example, the wide table building unit 212 can include code running on multiple hosts / virtual machines / containers. It should be noted that the multiple hosts / virtual machines / containers used to run the code can be distributed in the same region, or can be distributed in different regions. Further, the multiple hosts / virtual machines / containers used to run the code can be distributed in the same availability zone (AZ), or can be distributed in different AZs, each of which includes one data center or multiple data centers in a similar geographical location. Here, generally, one region can include multiple AZs.

[0255] Similarly, the multiple hosts / virtual machines / containers used to run the code can be distributed in the same virtual private cloud (VPC), or can be distributed in multiple VPCs. Here, generally, one VPC is set in one region, and a communication gateway needs to be set in each VPC for cross-region communication between two VPCs in the same region and between VPCs in different regions, so as to realize the interconnection between the VPCs through the communication gateway.

[0256] As an example of implementation of the unit by hardware, the wide table building unit 212 can include at least one computing device, such as a server or the like. Alternatively, the wide table building unit 212 can also be implemented by using a CPU, an application-specific integrated circuit (ASIC), a programmable logic device (PLD), a complex programmable logical device (CPLD), a field-programmable gate array (FPGA), a generic array logic (GAL), a data processing unit (DPU), a neural network processing unit (NPU), a system on chip (SoC), an offload card, an acceleration card, or any combination thereof.

[0257] When the wide table building unit 212 includes a plurality of computing devices, the plurality of computing devices included in the wide table building unit 212 can be distributed in the same region or in different regions. The plurality of computing devices included in the wide table building unit 212 can be distributed in the same AZ or in different AZs. Similarly, the plurality of computing devices included in the wide table building unit 212 can be distributed in the same VPC or in multiple VPCs. The plurality of computing devices can be any combination of servers, ASICs, PLDs, CPLDs, FPGAs, GALs, DPUs, NPUs, SoCs, offload cards, acceleration cards, and the like.

[0258] It should be noted that in other embodiments, the wide table building unit 212 can be configured to perform any step of the wide table building method provided in the present application, and the instruction receiving unit 211 can be configured to perform any step of the wide table building method provided in the present application, Figure 9 The steps implemented by each unit in the above description can be specified as needed, and the units cooperate to implement different steps of the wide table building method provided in the present application to achieve the overall function of the wide table building system 200.

[0259] It should be understood that the functions of the various unit modules described above are only the functions that the wide table building system 200 can have in some embodiments of the present application, and the present application does not limit the functions of the various unit modules.

[0260] It should also be understood that Figure 9The wide table construction system 200 is an example of a division manner, and the wide table construction system 200 can further include more or fewer unit modules. The division manner of the unit modules in the wide table construction system 200 can be flexibly adjusted based on actual business scenarios, and the present application does not make specific limitations.

[0261] The present application also provides a computing device 1000, which can deploy the foregoing wide table construction system. Each unit module in the computing device 1000 respectively implements a corresponding step in the wide table construction method provided by the present application.

[0262] As shown in Figure 10 The computing device 1000 includes a processor 1010, a memory 1020, and a communication interface 1030, wherein the processor 1010, the memory 1020, and the communication interface 1030 can be connected to each other through a bus 1040.

[0263] The processor 1010 can read the program code (including instructions) stored in the memory 1020, execute the program code stored in the memory 1020, so that the computing device 1000 executes the wide table construction method provided by the present application, or so that the computing device 1000 deploys the wide table construction system 200.

[0264] The processor 1010 can include any one or more of the computing devices such as a CPU, a graphics processing unit (GPU), a microprocessor (MP), or a digital signal processor (DSP), an ASIC, an FPGA, a CPLD, an NPU, a SoC, an offload card, an acceleration card, and the like.

[0265] The processor 1010 executes various types of digital storage instructions, such as software or firmware programs stored in the memory 1020, which can enable the computing device 1000 to provide a wide variety of services.

[0266] In a specific implementation, as an example, the processor 1010 includes one or more CPUs.

[0267] In a specific implementation, as an example, the computing device 1000 also includes multiple processors, each of which can be a single-CPU or a multi-CPU. The processor herein refers to one or more devices, circuits, and / or processing cores for processing data (such as computer program instructions).

[0268] The memory 1020 is configured to store program codes, and the processor 1010 is configured to control execution of the program codes to implement the wide table construction method provided in the present application. The program codes can include one or more software modules, which can be Figure 9 The software modules provided in the embodiments, such as the instruction receiving unit 211 and the wide table construction unit 212.

[0269] The memory 1020 can include a volatile memory (volatile memory), such as a random access memory (random access memory, RAM); the memory 1020 can also include a non-volatile memory (non-volatile memory), such as a read-only memory (read-only memory, ROM), a flash memory (flash memory), a hard disk drive (hard disk drive, HDD) or a solid-state drive (solid-state drive, SSD); the memory 1020 can also include a combination of the above types.

[0270] The communication interface 1030 can be a wired interface (such as an Ethernet interface, a fiber interface, other types of interfaces (such as an infiniBand interface)) or a wireless interface (such as a cellular network interface or a wireless local area network interface), used for communication with other computing devices or apparatuses. The communication interface 1030 can use a protocol family above the transmission control protocol / internet protocol (transmission control protocol / internet protocol, TCP / IP), such as a remote function call (remote function call, RFC) protocol, a simple object access protocol (simple object access protocol, SOAP) protocol, a simple network management protocol (simple network management protocol, SNMP) protocol, a common object request broker architecture (common object request broker architecture, CORBA) protocol, and a distributed protocol, etc.

[0271] The bus 1040 can be a peripheral component interconnect express (PCIe) bus, an extended industry standard architecture (EISA) bus, a unified bus (Ubus or UB), a compute express link (CXL), a cache coherent interconnect for accelerators (CCIX), or the like. The bus 1040 can be divided into an address bus, a data bus, a control bus, and the like.

[0272] The bus 1040 can include a power bus, a control bus, a status signal bus, and the like, in addition to the data bus. However, for the sake of clarity, all buses are marked as bus 1040 in the figure. For ease of representation, Figure 10 Only one thick line is used in the figure to represent the bus 1040, but it does not mean that there is only one bus or only one type of bus.

[0273] As a possible implementation, the computing device 1000 can also include a chip system including the processor 1010 and a power supply circuit for performing power supply to the processor 1010, and the processor 1010 is configured to perform the operation steps corresponding to the wide table construction method. For the sake of brevity, details are not repeated here. The processor 1010 can be implemented by a CPU, or by a GPU, DPU, NPU, XPU, SoC, offload card, acceleration card, or other computing device or AI chip.

[0274] As a possible implementation, the computing device 1000 can include multiple types of processors 1010, i.e., the computing device 1000 is a heterogeneous device, for example, the computing device 1000 includes a CPU and a GPU, and the operation steps corresponding to the wide table construction method can be performed by at least one of the processors. For the sake of brevity, details are not repeated here.

[0275] The computing device 1000 described above is configured to perform the wide table construction method provided in the present application, and the specific implementation process is described in the above method embodiments, which will not be repeated here.

[0276] It should be understood that the computing device 1000 is only an example provided by the embodiments of the present application, and the computing device 1000 can have more or fewer components than those shown, can combine two or more components, or can have a different configuration of components. For what is not shown or described in the embodiments of the present application, please refer to the foregoing Figure 10 Figures 1-9 ​The related descriptions in the embodiments are not repeated here.

[0277] The application also provides a computing device cluster 1100, which can deploy the wide table construction system described above. Each unit module in the computing device cluster 1100 respectively implements a corresponding step in the wide table construction method provided by the application.

[0278] As shown in Figure 11 The computing device cluster 1100 includes at least one computing device 1000. The memory 1020 in one or more computing devices 1000 in the computing device cluster can store the same instructions for executing the wide table construction method provided by the application. The computing device 1000 can be a server, such as a central server, an edge server, or a local server in a local data center. In some embodiments, the computing device 1000 can also be a terminal device such as a desktop computer, a notebook computer, or a smart phone.

[0279] In some possible implementations, the memory 1020 of one or more computing devices 1000 in the computing device cluster 1100 can also respectively store instructions for executing the wide table construction method provided by the application. In other words, the combination of one or more computing devices 1000 can collectively execute the instructions for executing the wide table construction method provided by the application.

[0280] It should be noted that the memories 1020 in different computing devices 1000 in the computing device cluster 1100 can store different instructions, respectively used to execute part of the functions of the wide table construction system 200. That is, the instructions stored in the memories 1020 in different computing devices 1000 can implement the functions of one or more of the instruction receiving unit 211 and the wide table construction unit 212.

[0281] In some possible implementations, one or more computing devices 1000 in the computing device cluster 1100 can be connected through a network. The network can be a wide area network or a local area network, etc. Figure 12 A possible implementation is shown, as Figure 12 It is shown that two computing devices 1000A and 1000B are connected through a network. Specifically, the communication interface in each computing device is connected to the network. In this type of possible implementation, the memory 1020 in the computing device 1000A stores instructions for executing the functions of the instruction receiving unit 211. At the same time, the memory 1020 in the computing device 1000B stores instructions for executing the functions of the wide table construction unit 212.

[0282] Figure 12The connection between the computing device cluster 1100 shown can be that the wide table construction method provided in the present application needs to perform wide table construction for multiple users in high concurrency, and therefore the functions implemented by the wide table construction unit 212 are executed by the computing device 1000B.

[0283] It should be understood that Figure 12 The functions of the computing device 1000A shown in the figure can also be completed by multiple computing devices 1000. Similarly, the functions of the computing device 1000B can also be completed by multiple computing devices 1000.

[0284] The present application also provides a computer program product containing instructions, which can be a software or program product containing instructions that can run on a computing device or be stored in any available medium. When the computer program product runs on at least one computing device, it makes the at least one computing device execute the wide table construction method provided in the present application.

[0285] The present application also provides a computer readable storage medium, which can be any available medium that a computing device can store or a data storage device such as a data center containing one or more available media. The available medium can be a magnetic medium (for example, a floppy disk, a hard disk, a magnetic tape), an optical medium (for example, a digital video disc (digital video disc, DVD), or a semiconductor medium (for example, a solid state disk) and the like. The computer readable storage medium includes instructions that instruct the computing device to execute the wide table construction method provided in the present application.

[0286] In the above embodiments, the description of each embodiment has its own focus, and the parts not described in detail in a certain embodiment can be referred to the related description of other embodiments.

[0287] In the above embodiments, all or part of the embodiments can be implemented by software, hardware or any combination thereof. When implemented by software, all or part of the embodiments can be implemented in the form of a computer program product. The computer program product includes one or more computer instructions. When the computer program instructions are loaded and executed on a computer, all or part of the processes or functions described in the embodiments of the present application are generated. The computer can be a general purpose computer, a special purpose computer, a computer network, or other programmable apparatus. The computer instructions can be stored in a computer readable storage medium or transmitted from one computer readable storage medium to another computer readable storage medium, for example, the computer instructions can be transmitted from one website, computer, server or data center to another website, computer, server or data center through wired (such as coaxial cable, optical fiber, digital subscriber line) or wireless (such as infrared, wireless, microwave, etc.). The computer readable storage medium can be any available medium accessible by a computer or a data storage device such as a server, data center, etc. integrated with one or more available media. The available media can be magnetic media (such as floppy disk, hard disk, magnetic tape), optical media, or semiconductor media, etc.

[0288] The above is only a specific embodiment of the present application. Based on the specific embodiments provided by the present application, those skilled in the art can think of changes or replacements, which should be covered within the protection scope of the present application.

Claims

1. A method of wide table construction, characterized by, The method comprises: receiving a wide table creation instruction sent by a user; determining a plurality of columns of the wide table based on the wide table creation instruction, wherein the plurality of columns comprises at least one first column and at least one second column, the at least one first column is from a fact table, and the at least one second column is from a dimension table; creating a temporary table containing a primary key column and establishing the wide table through the primary key column in the temporary table, wherein the primary key column has a corresponding relationship with the at least one first column, and the primary key column also has a corresponding relationship with the at least one second column; providing the wide table.

2. The method of claim 1, wherein, The creating of the temporary table containing the primary key column and the establishing of the wide table through the primary key column in the temporary table comprises: creating a first temporary table and a second temporary table, the first temporary table containing a corresponding relationship between the primary key column and the at least one first column, and the second temporary table containing a corresponding relationship between the primary key column and the at least one second column; associating the first temporary table and the second temporary table through the primary key column in the first temporary table and the primary key column in the second temporary table to obtain the wide table.

3. The method of claim 2, wherein, The column number of the at least one second column is a plurality, the dimension table comprises a first dimension table and a second dimension table, and the creating of the second temporary table comprises: creating two second temporary tables, one of the two second temporary tables containing a corresponding relationship between the primary key column and a second column in the first dimension table, and the other of the two second temporary tables containing a corresponding relationship between the primary key column and a second column in the second dimension table.

4. The method according to claim 2 or 3, characterized in that, Before the associating of the first temporary table and the second temporary table through the primary key column in the first temporary table and the primary key column in the second temporary table to obtain the wide table, the method further comprises: partitioning the first temporary table to obtain a plurality of partitions of the first temporary table.

5. A wide table building system characterized by, The system comprises: an instruction receiving unit configured to receive a wide table creation instruction sent by a user; a wide table construction unit configured to determine a plurality of columns of the wide table based on the wide table creation instruction, wherein the plurality of columns comprises at least one first column and at least one second column, the at least one first column is from a fact table, and the at least one second column is from a dimension table; the wide table construction unit is further configured to create a temporary table containing a primary key column and establish the wide table through the primary key column in the temporary table, wherein the primary key column has a corresponding relationship with the at least one first column, and the primary key column also has a corresponding relationship with the at least one second column; the wide table construction unit is further configured to provide the wide table.

6. The system according to claim 5, wherein: the wide table construction unit is configured to create a first temporary table and a second temporary table, the first temporary table containing a corresponding relationship between the primary key column and the at least one first column, and the second temporary table containing a corresponding relationship between the primary key column and the at least one second column; The wide table construction unit is configured to associate the first temporary table and the second temporary table by the primary key column in the first temporary table and the primary key column in the second temporary table to obtain the wide table.

7. The system of claim 6, wherein, The wide table construction unit is configured to create two second temporary tables, one of which contains a correspondence between the primary key column and a second column in the first dimension table, and the other of which contains a correspondence between the primary key column and a second column in the second dimension table.

8. A cluster of computing devices, characterized in that, The at least one computing device includes a processor and a memory; The processor of the at least one computing device is configured to execute instructions stored in the memory of the at least one computing device to cause the cluster of computing devices to perform the method of any one of claims 1 to 4.

9. A computer-readable storage medium, characterized in that, The computer readable storage medium has instructions stored therein, which when executed by a cluster of computing devices, cause the cluster of computing devices to perform the method of any one of claims 1 to 4.

10. A computer program product comprising instructions, characterized in that, The instructions, when executed by the cluster of computing devices, cause the cluster of computing devices to perform the operational steps of the method of any one of claims 1 to 4.

Images