Method and device for processing service data based on relational data table

Abstract

The embodiment of the invention provides a method for processing business data based on a relational data table, the relational data table comprises a first data column and a plurality of general data columns, a first text constraint is applied to the first data column, and a first data constraint is applied to the general data columns. The method comprises the steps that a first instruction is obtained and used for indicating and storing first data into a relational data table, the first data comprises a plurality of first data items belonging to a specific service attribute and a plurality of second data items belonging to a general service attribute, and each data item comprises a corresponding field name and a corresponding field value; and storing the plurality of first data items into a first data column based on the first text constraint. And based on a first data constraint, correspondingly storing the plurality of second data items into a general data column.

Description

Technical Field

[0001] One or more embodiments of the present specification relate to the field of database technology, and in particular, to a method and device for processing business data based on a relational data table. Background Art

[0002] Database technology, as a method of efficiently storing and managing business data in the form of data tables, has become the cornerstone of modern information systems.

[0003] In the database, business records are organized according to predetermined data dimensions to form structured data, which are stored in different data tables in the database for query and analysis. Common database-related technologies include: Oracle, DB2, SQL Server, MySQL, etc.

[0004] With the rapid development of Internet technology, the pace of digital transformation of enterprises has accelerated and the business areas have continued to expand. In an enterprise, there are usually multiple business departments, each responsible for a specific business line. These business departments accumulate professional business data with unique data structures based on the characteristics of their own business lines.

[0005] As a result, a large number of business data with different characteristics and data formats are formed within an enterprise. However, in the daily operation of an enterprise, cross-businesses are usually generated, especially platform-based businesses, whose required data spans multiple business departments and requires the preservation of data that meets the activity conditions in multiple business departments. At this time, the data with different data structures in various business departments are incompatible with each other, causing the data table of the platform-based business to have to be expanded to be compatible with business data in various formats, hindering the efficient circulation and sharing of data within the enterprise.

[0006] Therefore, we hope to have a solution that can dynamically process business data through technical means to achieve efficient data storage and access. Summary of the invention

[0007] One or more embodiments of the present specification describe a method and device for processing business data based on a relational data table. On the basis of retaining some relational data constraints of the business data, it can break through the data strong verification mechanism and integrate business data with different data structures into a relational data table to solve the above-mentioned technical problems.

[0008] According to a first aspect, a method for processing business data based on a relational data table is provided, wherein the relational data table includes a first data column and a plurality of common data columns, a first text constraint is applied to the first data column, and a first data constraint is applied to the common data column. The method comprises:

[0009] A first instruction is obtained, which is used to instruct to store first data into the relational data table, wherein the first data includes a plurality of first data items belonging to specific business attributes and a plurality of second data items belonging to general business attributes, and each data item includes its corresponding field name and field value.

[0010] Based on the first text constraint, the plurality of first data items are stored in a first data column.

[0011] Based on the first data constraint, the plurality of second data items are stored in corresponding general data columns.

[0012] According to an implementation, the first data constraint includes one or more of the following: a foreign key constraint, a unique constraint, a non-null constraint, and a range constraint.

[0013] According to one implementation, storing the plurality of first data items into a first data column includes:

[0014] For any first data item, a first key-value pair is determined according to its first field name and first field value.

[0015] The first key-value pairs corresponding to the first data items are concatenated based on the text constraint to obtain a first text.

[0016] The first text is stored in a first data column.

[0017] According to one implementation, the splicing operation includes:

[0018] The key-value pairs to be concatenated are marked with the first separator specified in the first text constraint.

[0019] According to an implementation manner, determining the first key-value pair includes:

[0020] Determine a first code according to the first field name; and use the first code as a key and the first field value to form the first key-value pair.

[0021] According to one implementation, the method further includes:

[0022] A second instruction is obtained, which is used to instruct to perform a second operation on the table data in the relational data table, wherein the second instruction includes query conditions of several data items related to the table data.

[0023] For any query data item in the query condition, the query field name is used as the key, and the query field value is used to form a query key-value pair.

[0024] Based on the query key-value pairs corresponding to the respective data items in the query condition, table data is selected in the relational data table, wherein the first data value corresponding to the first data column in the selected table data contains the query key-value pair.

[0025] The second operation is performed on the selected table data.

[0026] In one scenario of the above implementation, the second operation is a read operation, and performing the second operation on the selected table data includes:

[0027] The field values ​​of each common data column and the first data column included in the selected table data are read out.

[0028] In a scenario of the above implementation, the second operation is a delete operation, and performing the second operation on the selected table data includes:

[0029] The selected table data is deleted from the relational data table.

[0030] According to one implementation, the method further includes:

[0031] A third instruction is obtained, which is used to instruct that a third operation is performed on a third key-value pair in the table data of the relational data table, and the third instruction includes a third field name.

[0032] In the table data, based on the third field name, a corresponding third key-value pair is selected, and the key of the third key-value pair matches the third field name.

[0033] The third operation is performed on the selected third key-value pair.

[0034] In one scenario of the above implementation, the third operation is a read operation, and performing the third operation on the selected third key-value pair includes:

[0035] Read the value of the third key-value pair.

[0036] In one scenario of the above implementation, the third operation is an update operation, the third instruction further includes a third update value, and performing the third operation on the selected third key-value pair includes:

[0037] Update the value of the third key-value pair to the third updated value.

[0038] In a scenario of the above implementation, the third operation is a delete operation, and performing the third operation on the selected third key-value pair includes:

[0039] The third key-value pair is deleted from the table data.

[0040] According to a second aspect, a device for processing business data based on a relational data table is provided, wherein the relational data table includes a first data column and a plurality of general data columns, a first text constraint is applied to the first data column, and a first data constraint is applied to the general data column, the device comprising:

[0041] The acquisition module is configured to obtain a first instruction, which is used to instruct to store first data into the relational data table, wherein the first data includes several first data items belonging to specific business attributes and several second data items belonging to general business attributes, and each data item includes its corresponding field name and field value.

[0042] The first storage module is configured to store the plurality of first data items into a first data column based on the first text constraint.

[0043] The second storage module is configured to store the plurality of second data items into corresponding general data columns based on the first data constraint.

[0044] According to a third aspect, a computer program product is provided, comprising a computer program / instruction, which, when executed by a processor, implements the steps of the method described in the first aspect.

[0045] According to a fourth aspect, a computing device is provided, comprising a memory and a processor, wherein an executable code is stored in the memory, and when the processor executes the executable code, the method described in the first aspect is implemented.

[0046] In summary, by using the above method and device disclosed in the embodiments of this specification, the data items contained in the business data can be processed based on specific text constraints, and the data field values ​​originally scattered in multiple columns can be integrated into one column, thereby realizing the flattening of the business data. Storing business data in this way can break the data format barriers between business data, and achieve the purpose of storing business data with different data structures in the same relational data table. Moreover, the expansion of the business data format does not affect the storage and use of historical business data, realizing the decoupling of the storage and use of business data, and greatly improving the efficiency and flexibility of data storage. BRIEF DESCRIPTION OF THE DRAWINGS

[0047] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following briefly introduces the drawings required for use in the description of the embodiments. Obviously, the drawings described below are only some embodiments of the present invention, and for ordinary technicians in this field, other drawings can be obtained based on these drawings without creative work.

[0048] Figure 1 A relational data table disclosed in the embodiments of this specification;

[0049] Figure 2 A schematic diagram of a framework for processing business data based on a relational data table disclosed in an embodiment of this specification;

[0050] Figure 3 A flowchart of a method for processing business data based on a relational data table provided according to an embodiment of this specification;

[0051] Figure 4 A flow chart of a method for reading and deleting table data provided according to an embodiment of this specification;

[0052] Figure 5 A flow chart of a method for updating and deleting key-value pairs in table data provided according to an embodiment of this specification;

[0053] Figure 6 The present invention is a schematic diagram of a device for processing business data based on a relational data table according to an embodiment of the present specification. DETAILED DESCRIPTION

[0054] The solution provided by the embodiments of this specification is described below in conjunction with the accompanying drawings.

[0055] At present, in many enterprises, data analysis and business activities are carried out with database technology as their technical foundation. In particular, the use of relational database technologies such as SQL (Structured Query Language) has made it a key tool for data processing and query.

[0056] As mentioned above, with the continuous expansion of the business scale of enterprises and the gradual completion of information construction, different business departments will generate business data that conforms to the characteristics of their respective business lines, and these business data have different data structures. In relational databases, the differences in data structures can include, for example: different data items (i.e., data columns), different enumeration mappings (i.e., foreign key mappings), and so on.

[0057] As mentioned above, business data with different data structures will cause trouble for data processing in platform-type business departments. In platform-type system components that support multi-party businesses, relational databases play a core role, storing business data required by upstream and downstream businesses. Therefore, the database must be able to receive and process business data storage requirements from multiple upstream business departments, and also provide corresponding business data reading capabilities for downstream business departments. For example, transaction analysis components, coupon distribution components, shopping cart management components, etc., not only need to store various business data from upstream business ends, but also need to provide corresponding business data reading services for downstream business ends to realize personalized functions for each business line in the system. In order to meet data access requirements, the relational data tables used to store business data in platform-type system components need to be designed to be both flexible and scalable to meet the needs of accessing business data with different data structures.

[0058] For example, in an enterprise, the customer behavior data generated by the marketing department may have differences in data structure with the customer feedback data generated by the after-sales department. Given the strong type verification rules of relational databases, when these data are stored in a platform-type business database, they need to be manually cleaned according to the predetermined data column mapping rules, and the data format must be unified before being stored in the database. Take the user data table used to store user information as an example. In this data table, each user is represented by a business data containing several user data items. This data table that stores business master data is also called a fact table. Fact tables are usually designed around business processes and are used to record specific business events and business entities. They contain specific business elements (i.e., data items) for each business event and business entity, and are generally stored as measurable numerical data or comparable character data. Each row of table data in a fact table can represent a business event or business entity, such as an order event, a payment event, a user entity, a product entity, and so on.

[0059] Figure 1An exemplary relational data table is disclosed. Referring to the accompanying drawings, the header of the user data table represents the names of the various fields of the user data, and each row of business data represents each business entity (i.e., user) generated in the business activity. As shown in the figure, each piece of business data contains the field values ​​corresponding to the four fields of ID, name, permanent residence area, and age. In the accompanying drawings, the data table design of the user data table is also shown. Unlike the display of the data table, the data table design shown in the figure does not contain specific business data entries. In the data table design, each record in the right column represents each header (i.e., field name) of the data table, and the corresponding left column records the data constraints of the header, such as primary / foreign key information and data type definition. It should be understood that in the accompanying drawings, the data table and the corresponding data table design example only contain the data table design elements required to illustrate the present embodiment. In actual table design activities, the data table structure will contain more design elements, such as data indexes, etc.

[0060] exist Figure 1 In the table design of the user information table shown, there are the data table primary key (identified by PK in the figure) "ID", the character type (identified by nvarchar in the figure) field "name", the numeric type (identified by int in the figure) field "age", and the foreign key (identified by FK in the figure) field "permanent residence area". The foreign key field represents that it is just a field associated with the primary key of the corresponding dimension table and does not record specific dimension information. For example, in the user data table, for the permanent residence area of ​​a certain user, the area name is not directly stored, but the corresponding area ID of the area in the area dimension table is recorded. When the downstream business reads data, the corresponding area name (in this example, the city name) can be queried in the area dimension table according to the area ID. Therefore, it is not difficult to understand that when storing business data in the user data table, for the fields with foreign key associations, it is necessary to first find the corresponding foreign key value in the dimension table according to the corresponding business value in the business data, and then use it as the field value and store it in the corresponding field of the user data table.

[0061] Continue reading Figure 1In the scenario shown, for the user data table, there is a need to store two business data (business data A and business data B). Among them, business data A has data fields and field values ​​that correspond one-to-one with the structure of the user data table, and can be directly stored in the table, for example, by writing an insert statement directly into the table (as shown in the SQL statement associated with business data A in the figure). However, in specific practice, among the business data from different business departments connected to the platform database, there are very few business data such as business data A that are completely aligned with the user data table fields. More commonly, the business data to be stored is transmitted in the format of the business department to which it belongs. Although it contains the necessary field information required by the platform user data table, the format is not consistent. Taking the business data B in the attached figure as an example, it contains the "name", "permanent residence area", and "age" field information required by the user data table, but it is presented in different field forms: the name is split into two fields, the surname and the first name, the field value of the permanent residence area is not a standard enumeration value of the regional dimension table, and the age needs to be calculated based on the information of the year of birth. This common problem of mismatch between fields may be caused by the fact that the business data is manually entered by the user on the front-end page and cannot be standardized. It may also be caused by adding temporary field values ​​when the user data table is designed without fully considering the future data expansion requirements. It may also be caused by the presence of preset data access rules between upstream and downstream businesses that are different from those in the user data table, etc., which are not listed here one by one.

[0062] Therefore, for this type of business data, it is necessary to convert the field values ​​that do not meet the user data table field requirements before entering the table. Figure 1 The SQL sample statements associated with business data B in the example apply a customized field value conversion method to convert business data B into field values ​​that can be received by the user data table. For example, for the surname and first name field values ​​of business data B, the text concatenation conversion method is applied to convert them into names and insert them into the name field of the user data table; for the permanent residence area field value of business data B, the conditional judgment function (for example, CASE statement, IF statement) is applied to perform enumeration conversion, mapped to the standard enumeration value in the regional dimension table to query the corresponding regional ID, and inserted into the permanent residence area field of the user data table; for the year of birth of business data B, the numerical conversion method is applied to convert it into an age value and insert it into the age field of the user data table.

[0063] Through the above description of the method for storing different business data in the user data table, it can be seen that due to the strong type verification rules of the user data table built on the relational database, the field values ​​in the business data to be stored that do not conform to the field format of the user data table all need to be converted by customized execution before data storage can be executed. It should be understood that in the example shown in the accompanying drawings, the user data table is used for explanation, but it does not represent a limitation of the platform data table in practice. In specific practice, the platform data table can be a data table related to any business entity, which is not specifically limited here. In practice, such customized conversion of data as described in the above example is usually implemented by developers manually writing relevant SQL statements based on the understanding of the field value definition of the upstream business. With the increasing number of upstream business departments connected to the platform database, the custom field values ​​(non-standardized) of the upstream business have increased accordingly. The process of converting these custom field values ​​not only consumes a lot of manpower, but also causes the data entry code of the business data table to become more bloated, reducing the maintainability of the code. In addition, when downstream businesses read business data stored in the business data table, they also need to use the inverse operation of the above conversion method to restore the corresponding field values, which greatly increases the complexity of data reading.

[0064] In some scenarios, when the business department expands the business data fields to be stored, the business data table also needs to update its own table structure and add corresponding new fields to store the expanded field values. In a relational database, changes to the table structure not only involve the update of the target business data table, but also have many impacts. For example: rebuilding the index on the target business data table will temporarily reduce database performance; when the table structure of the target business data table is updated, the database may temporarily lock the table, which will affect the execution of other transactions attempting to access the table; some views, stored procedures, functions and other data objects that depend on the target business data table may need to be modified and compiled synchronously.

[0065] All the problems listed above indicate that, as a platform database, relational business data tables will not only incur high labor costs for script customization when faced with different business data access requirements of upstream and downstream businesses, but also lack flexibility in the data tables and are unable to cope with the dynamic updates of business data fields of business departments.

[0066] In order to solve the above problems, the inventors proposed a method and device for processing business data based on a relational data table in the embodiments of this specification, and classified each data item (including field name and corresponding field value) in the business data, wherein the data items belonging to specific business attributes include business data unique to the upstream business, and the data items belonging to general business attributes include business data common to all upstream businesses (for example, creation date, business category, etc.), so that the corresponding storage strategy can be adopted according to the category to which the data item belongs: for the general business attribute data items that all upstream businesses have, direct table storage is performed; and for the specific business attribute data items that are likely to change frequently, text aggregation operations can be performed and stored in the business data table in text form. In this way, business data with different data items can be stored in the same business data table, improving data storage efficiency and flexibility. Figure 2 The schematic diagram of a framework for processing business data based on a relational data table is shown. The business data to be stored includes M specific data items belonging to specific business attributes and N general data items belonging to general business attributes. Each data item consists of a field name and a corresponding field value. Figure 2 In the process of entering the business data into the table, each general data item can be directly stored in each general data column of the business data table, and for M specific data items, these specific data items can be converted into aggregated texts through text aggregation operations based on specific text constraints, and stored as field values ​​in the first data column of the business data table. In this way, several specific data items contained in the business data to be stored with different data structures can be converted into a unified text format and stored in a relational business data table, so that the business data storage requirements transmitted by different upstream businesses can be met in one relational data table.

[0067] Following the above technical ideas, Figure 3 In FIG. 1 , a flowchart of a method for processing business data based on a relational data table according to an embodiment of the present specification is shown. It can be understood that the method can be executed by any device, equipment, platform, or device cluster with computing and processing capabilities. Figure 3In one embodiment, the relational data table includes a first data column and several general data columns, a first text constraint is applied to the first data column, and a first data constraint is applied to the general data column; the method includes at least the following steps. S301: Obtain a first instruction, which is used to instruct that first data is stored in the relational data table, the first data includes several first data items belonging to specific business attributes, and several second data items belonging to general business attributes, each data item includes its corresponding field name and field value. S303: Based on the first text constraint, store the several first data items in the first data column. S305: Based on the first data constraint, store the several second data items in the general data column accordingly.

[0068] The specific implementation methods of the above steps will be described in detail below with reference to the accompanying drawings.

[0069] In step S301, a first instruction is obtained, which is used to instruct to store first data into the relational data table.

[0070] Corresponding to the selection of the relational database to which the relational data table (i.e., the business data table mentioned above) belongs (for example, MySQL, Oracle DB, PostgreSQL, etc.), the first instruction is a corresponding command or statement that can be executed by the database query engine. Taking the SQL standard statements supported by common relational databases as an example, the first instruction may be an SQL statement instructing to store the first data into the relational data table, for example, an INSERT statement; the first instruction may also be a call instruction to the relational database storage interface, which contains the first data to be stored; the first instruction may also be an instruction instructing the relational database to load the data file containing the first data to store the data into the relational data table. In short, there are many ways to store data into a relational data table, and the embodiments of this specification do not specifically limit this.

[0071] As mentioned above, in a relational database, a data table is composed of several data rows (i.e., table data) and several data columns (i.e., fields), wherein each row of table data represents a piece of business data, and each column represents a field in the business data, and the intersection of rows and columns constitutes a data item, i.e., a field name and a corresponding field value. In the design of a data table, one or more fields are usually designated as a primary key (Primary Key, PK), and the uniqueness of the primary key ensures the uniqueness of each row of business data in the data table. In the embodiments of this specification, in order to simplify the expression, the primary key is not described too much, and it can be understood that when inserting table data into a data table, a self-incrementing positive integer is assigned to the newly added table data as the primary key. In addition, in order to maintain the association between multiple data tables, a foreign key (Foreign Key, FK) is also used to reference the primary keys in other tables (e.g., dimension tables), thereby establishing a connection between tables.

[0072] In order to ensure the integrity of each table data stored in the data table and compliance with business rules, data constraints can also be set on the data columns of the data table to verify the stored business data based on the data constraints to prevent the storage of invalid data.

[0073] In one embodiment, the relational data table includes a first data column and several general data columns, a first text constraint is applied to the first data column, and a first data constraint is applied to the general data column. The first text constraint can be used to stipulate that the text of the field value of each table data corresponding to the first data column is organized in a preset format. The first text constraint will be explained in detail below. The first data constraint may include one or more of the following: foreign key constraint, unique constraint, non-empty constraint, range constraint. Foreign key constraints are used to maintain the relationship between the relational data table and other tables. The field value corresponding to the field (or field combination) to which the foreign key constraint is applied will be taken from the primary key of another table, or be empty (if it can be empty). The unique constraint is used to ensure that the value of a field (or the combined value of multiple fields) in the relational data table is unique in the entire table, that is, duplicate values ​​are not allowed, typically, such as the primary key field. The non-null constraint requires that the field in the relational data table cannot have a null value (NULL), which means that when storing new data in the data table, it is necessary to ensure that the field has a field value, and when updating the table data, the field value of the field cannot be deleted. The range constraint can limit the field value in the field to remain within a range that has business significance. For example, for an integer type field, the value range can be set to 0 to 100.

[0074] The first data may be data from upstream business, which usually includes key information in the business process (e.g., customer information, order information, transaction information, etc.), and needs to be stored in the relational data table through the execution of the first instruction for downstream business to read, thereby forming a flow of business data. The first data includes several first data items belonging to specific business attributes and several second data items belonging to general business attributes, and each data item includes its corresponding field name and field value.

[0075] The specific business attributes may be unique business attributes in the business process that generates the first data, such as order priority attributes in the commodity sales business, customer loyalty scores in the customer management business, etc.; they may also be business attributes that may change at any time in the business process, such as new business attributes added to adapt to business development. The general business attributes may be common business attributes in the upstream business process, such as the creation date and business category of the first data. The definition of data items has been introduced in detail in the previous article and will not be repeated here.

[0076] Next, in step S303, based on the first text constraint, the plurality of first data items are stored in a first data column.

[0077] As mentioned above, for each first data item belonging to a specific business attribute, it can be converted into an aggregate text and stored in the relational data table. Therefore, in this step, each first data item can be converted into data represented in text form, and the text data contains the field name and field value of the first data item to ensure that the converted text still completely retains various types of information of the first data item. This conversion of aggregate text can be standardized by setting specific text constraints, such as plain text conversion, markup language (e.g., Markdown) conversion, and so on.

[0078] According to an implementation manner, storing the plurality of first data items into a first data column may be achieved through the following steps.

[0079] For any first data item, a first key-value pair is determined according to its first field name and first field value.

[0080] In this step, each first data item can be converted into a text represented by a key-value pair, and the key-value pair can ensure that the converted text still completely retains the field name and field value of the first data item. Specifically, the field name of the first data item is used as the key, the corresponding field value is used as the value, and the key and the value are separated by a preset separator (for example, a comma, a colon, etc.), so as to construct a key-value pair corresponding to the data item.

[0081] In some specific scenarios, the business data to be stored may come from multiple different business departments, and there may be fields with the same name but different business meanings among these business data. When these business data are stored in the text form of key-value pairs, it is difficult to distinguish the business departments to which the keys with the same name belong, which will cause trouble for downstream business data reading. In other specific scenarios, there may be some special field names in the business data to be stored. For example, the field name is too long, the field name conflicts with the keywords of the relational database, etc. When such special field names are used as keys, when downstream businesses read them, it often causes the database query engine to fail to parse correctly and report errors.

[0082] In order to avoid the above problems, in one implementation, the first code can be determined according to the first field name; the first code is used as a key and the first field value forms the first key-value pair. In other words, the implementation method can convert the field name of the business data to be stored (i.e., the first data) into a first code through certain coding rules. The first code can be a globally unique identification code (for example, adding a UUID to the end of the field name), or a mixed code that includes specific business information (for example, adding the code of the business department to which the first data belongs as a prefix to the field name). In short, the first code can be obtained based on specific coding rules according to specific business needs. In one example, the initial consonants of the Chinese pinyin of the first field name text can be extracted as the first code, for example, the field name "department name" is encoded as "BMMC". In another example, the English text of the first field name can also be used as the first code, for example, the field name "department name" is encoded as "Department". In other examples, the encoding rules agreed upon by the upstream and downstream business departments can also be used to encode the field name of the first data to obtain a first code that is reducible. When the downstream business performs data query, the target query field name can be encoded according to the agreed encoding rules, and the query statement can be written using the encoding. After obtaining the query result, the first code in the query result is decoded based on the agreed encoding rules to restore the field name. It can be understood that there are many encoding rules, and in different business scenarios, they can be selected according to specific needs. The embodiments of this specification do not cite examples one by one.

[0083] Through the above steps, each first data item belonging to a specific business attribute in the first data can be converted into a first key-value pair. Next, a text aggregation operation can be performed on the first key-value pairs to store them in the first data column of the relational data table.

[0084] Therefore, the first key-value pairs corresponding to the first data items can be concatenated based on the first text constraint to obtain the first text.

[0085] In this step, the splicing operation can be a splicing operation based on relational query syntax, for example, through CONCAT, CONCAT_WS operations, several first key-value pairs are spliced ​​in text form. It can also be a splicing operation based on a programming language function, for example, using a string join function, or a string formatting method (such as sprintf in C language, format, f-string in Python), to combine and splice several first key-value pairs according to a certain template format. It can also be other text splicing methods, the selection of which depends on the specific application scenario and development habits, and the embodiments of this specification do not specifically limit this.

[0086] According to one implementation, the first separator specified in the first text constraint can be used to mark the key-value pairs to be spliced. That is, the first separator is inserted between every two first key-value pairs to mark the boundary between the key-value pairs. In the data processing process of the downstream business, the first text can be accurately divided into several original key-value pairs based on the first separator. This can not only enhance the readability of the first text, but also facilitate subsequent data query and analysis.

[0087] It is understandable that the setting of the first separator can be flexibly adjusted according to actual needs to avoid conflicts with characters that may appear in the first key-value pair. In a specific scenario, the first separator can include one or more of the following: brackets, caret (^), and line feed.

[0088] After determining the first text, an entry operation can be performed to store the first text in a first data column. It should be noted that in actual applications, there may be multiple first data columns in the relational data table, and each first data column may store a first text consisting of a number of first key-value pairs. The number of the multiple first data columns may be determined according to the text length limit of the relational data table for the first data column. For example, in order to avoid exceeding the length limit of the text stored in the first data column, multiple first data columns may be opened in the relational data table to disperse the storage of overlong texts, ensuring that the content of the first data column in each table data does not exceed the length limit of the database field.

[0089] While storing the first data item, in step S305, based on the first data constraint, the plurality of second data items are stored in corresponding general data columns.

[0090] As mentioned above, the second data item corresponds to a data item belonging to a general business attribute, and such data item has a corresponding data column (i.e., the general data column) in the relational data table. In the process of entering the first data into the table, for the second data item, the corresponding general data column can be found one by one, and the second data item can be stored in the corresponding general data column. It can be understood that when storing the second data item, according to the data constraints of the corresponding general data column, the field name and field value contained in the second data item can be stored, or only the field value or field name of the second data item can be stored, and no specific limitation is made here.

[0091] The above is an introduction to a storage process for processing business data based on a relational data table provided in an embodiment of this specification. In some embodiments of this specification, a related method process for reading and modifying table data based on the relational data table is also provided to ensure that each downstream task can correctly obtain and process the table data in the relational data table.

[0092] Figure 4 A flowchart of a method for reading and deleting table data based on a relational data table provided according to an embodiment of this specification is shown. The method includes at least the following steps:

[0093] Step S401: Obtain a second instruction, which is used to instruct to perform a second operation on the table data in the relational data table, wherein the second instruction includes query conditions of several data items related to the table data.

[0094] Similar to the first instruction described above, the second instruction may be a command or statement written in a query language supported by the database to which the relational data table belongs. In the query clause of the second instruction, there is a query condition corresponding to the query target (i.e., the table data). The query condition is used to constrain the value of the query target on any data column, including but not limited to the limitation of the value content, the limitation of the value range, and the limitation of the value attribute (e.g., text length, value type, etc.). From the aspect of the target column to which the query condition is applied, the query condition may be set for a general data column, which has the inherent data constraint characteristics of a relational database. The constraint setting for the general data column in the query condition may be written in accordance with the data constraint of the target data column and the corresponding grammatical provisions in the query language. In the execution phase of the query statement, the database query engine may also refer to the query condition for the general data column according to the inherent query execution method to implement the screening of the table data in the relational data table. This embodiment of the specification will not be elaborated on this. In addition, the query condition can also be set for the first data column, the first data column has a first text constraint, and in the first data column, a number of data items (key-value pairs) of specific business attributes of each business data are stored in text form. In the query condition applied to the first data column, constraints on several data items of the query target can be included, for example, it can be a constraint on the number of key-value pairs contained in the first data column, it can be a constraint on the field name or field value content of any data item, and so on. It can be understood that the two applications of the query conditions exemplified above can also be used in combination, that is, in the query clause of the second instruction, both query conditions applied to any general data column and query conditions applied to the first data column are included. The execution method of the query condition applied to the first data column will be described in detail below.

[0095] As mentioned above, the query condition applied to the first data column includes constraints on several data items of the query target. Therefore, in step S403: for any query data item in the query condition, its query field name is used as a key and the query field value is used to form a query key-value pair.

[0096] According to the different types of constraints on data items in the query conditions, corresponding rules can be applied to form query key-value pairs. The following will describe the classification.

[0097] In one example, the query condition may include a single constraint on the field name of any data item. For example, the field name is constrained using the LIKE keyword. The query clause may be, column_name LIKE'%name%', where column_name is the query keyword corresponding to the field name, the LIKE keyword indicates that in the query clause, a text fuzzy query is applied, and % is a text wildcard. The query clause of this example may indicate that all records containing the word "name" in the field name are queried. Correspondingly, when combining query key-value pairs, the field values ​​that are not constrained in the clause may be replaced with wildcards, indicating that any value is acceptable. The query key-value pairs may be (taking angle brackets as the packaging symbol of the key-value pair and colons as the key-value separators as an example): <'%name%':'%'>.

[0098] In another example, the query condition may also include a single constraint on the field value of any data item, for example, using text wildcards to perform fuzzy matching on field values. Similar to the above example, the corresponding query key-value pair may be constructed as: <'%':'% points%'>, which means querying all records whose field values ​​contain the word "points".

[0099] In other examples, the query condition may also be a combination of the above two examples, that is, it may also include constraints on the field name and field value of any data item, which will not be elaborated in the embodiments of this specification.

[0100] After the query key-value pair is determined based on the query condition, in step S405: based on the query key-value pairs corresponding to the several data items in the query condition, in the relational data table, table data is selected, and the first data value corresponding to the first data column in the selected table data contains the query key-value pair.

[0101] As mentioned above, the data stored in the first data column is of text type, so during the query execution process, the table data in the relational data table can be screened based on the query key-value pair using a text comparison method. For example, the INSTR function is used to determine whether the first data column content of each piece of table data contains the query key-value pair specified in the query condition.

[0102] According to the result of the text comparison, a plurality of table data that meet the query condition in the second instruction are screened from the relational data table as query results.

[0103] Next, based on the query results obtained in the above steps, in step S407: the second operation is performed on the selected table data.

[0104] The second operation may be a read operation (eg, a SELECT statement), a delete operation (eg, a DELETE statement), or various operations supported by a relational database for processing table data, which are not specifically limited here.

[0105] In a specific example, the second operation is a read operation, in which the field values ​​of the general data columns and the first data column contained in the selected table data can be read out. In the reading process, for each selected table data in the query result, the value corresponding to the general data column can be output according to the order set in the read operation clause (for example, SELECT A, B...; the set order is column A, column B), or when there is no set order in the read operation clause (for example, SELECT*...), it can be output according to the design order of each general data column in the table design of the relational data table, which is not specifically limited here. For the field value of the first data column, it can be directly output as a single text value; or it can be performed according to the first text constraint followed when storing data, and the inverse operation is performed to restore the field value of the first data column to a plurality of first key-value pairs for output. In some practices, it is also possible to further perform a restore operation on the first key-value pair (if there is an encoding operation, the corresponding decoding operation can be performed) to obtain the data items in the original business data and output them.

[0106] In another specific example, the second operation is a delete operation. In this step, the selected table data can be deleted from the relational data table. Different delete operations can be performed accordingly according to different delete commands in the second operation. For example, for DML operations such as DELETE, a transaction-based marked deletion can be performed to mark the selected table data for deletion, but the table space of the relational data table will not be released immediately. If necessary, the table data can be retrieved by rolling back the transaction; and for DDL operations such as TRUNCATE and DROP, the selected table data will be destroyed and the table space corresponding to the relational data table will be released immediately.

[0107] In other embodiments of the present specification, a related method flow for updating and deleting key-value pairs in table data based on the relational data table is also provided. Figure 5 A flowchart of an exemplary method provided by an embodiment is shown, which at least includes the following steps:

[0108] Step S501: Obtain a third instruction, which is used to instruct to perform a third operation on a third key-value pair in the table data of the relational data table, and the third instruction includes a third field name.

[0109] Similarly, the third instruction may be a command or statement written in a query language supported by the database to which the relational data table belongs. The table data is the table data stored in the relational data table, and may be the table data contained in the query result obtained by the query operation performed in the above example, or may be all the table data in the relational data table, which is not specifically limited here.

[0110] The third instruction may be an instruction for processing the target key-value pair, which includes the field name of the target key-value pair, namely the third field name. It is understood that the third field name used as a query condition may be a field name text for precise matching, such as: department name; or an encoded text of a field name, such as: BMMC; or a text containing a wildcard for fuzzy matching, such as: %name% or %MC%.

[0111] Next, in step S503: in the table data, based on the third field name, a corresponding third key-value pair is selected, and the key of the third key-value pair matches the third field name.

[0112] As mentioned above, in the table data of the relational data table, each key-value pair is stored in the form of text, so during the query execution process, a text comparison method can be used to retrieve the corresponding third key-value pair in the table data based on the third field name, and the key of the key-value pair matches the third field name. In other words, the key of the third key-value pair exactly matches the third field name, or meets the fuzzy matching rule specified by the third field name.

[0113] It can be understood that any table data may contain multiple key-value pairs, each of which can match the third field name. In this case, the third key-value pairs obtained by the query may be multiple.

[0114] Based on the query results obtained in the above steps, in step S505: the third operation is performed on the selected third key-value pair.

[0115] The third operation may be a read operation (eg, a SELECT statement), an update operation (eg, an UPDATE statement), a delete operation (eg, a DELETE statement), or any operation supported by a relational database for processing table data, which is not specifically limited here.

[0116] In a specific example, the third operation is a read operation, in which the value of the third key-value pair can be read and returned as a result. When there are multiple third key-value pairs, the value of each key-value pair can be returned as an independent data record, that is, in the returned read result, each data record contains only the value of one key-value pair; the read result can also be divided into rows according to the table data to which the key-value pair belongs, that is, in the returned read result, the values ​​of several key-value pairs belonging to the same table data are displayed in the same data record.

[0117] In another specific example, the third operation is an update operation, and the third instruction also includes a third update value. In this step, the value of the third key-value pair can be updated to the third update value. It can be understood that the update of the value in the key-value pair can be an update operation (for example, an UPDATE statement), that is, the value of the third key-value pair is overwritten with the third update value; or it can be a replacement operation (for example, a REPLACE statement), that is, in the value of the third key-value pair, according to the matching condition given by the replacement operation, the text substring that meets the matching condition is replaced with the third update value.

[0118] In another specific example, the third operation is a delete operation, and in this step, the third key-value pair can be deleted from the table data. In some practices, in the process of deleting the third key-value pair from the table data, the first delimiter specified in the first text constraint can also be deleted synchronously to ensure that the content of the first data column of the table data after the delete operation is executed still complies with the first text constraint.

[0119] The above is an introduction to a method for processing business data based on a relational data table provided in an embodiment of this specification. Although the above embodiment mainly uses SQL statements as an example to illustrate the method flow, the technical concept embodied therein can also be applied to the processing of other relational databases and the query languages ​​they support.

[0120] According to one or more embodiments, a method for processing business data based on a relational data table is described in detail above. By adopting the above method provided in the embodiments of this specification, a unified key-value pair format can be used to standardize the expression of data items in the business data, and the key-value pairs can be spliced ​​to integrate the data items originally scattered in multiple columns into a single data column, thereby realizing the flattening of business data, unifying the storage methods of business data with different data structures, and achieving the purpose of automatically processing the access of multi-party business data in the relational data table.

[0121] In this specification, the word "first" in terms such as the first instruction and the first data item, and the corresponding "second", "third" (if any), etc. in the text are merely for the convenience of distinction and description and do not have any limiting meaning.

[0122] The foregoing describes certain embodiments of the present specification, and other embodiments are within the scope of the appended claims. In some cases, the actions or steps described in the claims may be performed in an order different from that in the embodiments, and the desired results may still be achieved. In addition, the processes depicted in the accompanying drawings do not necessarily have to be performed in the specific order or sequential order shown to achieve the desired results. In some embodiments, multitasking and parallel processing are also possible or may be advantageous.

[0123] Figure 6 Schematic diagram of a device for processing business data based on a relational data table according to an embodiment of this specification. The device 600 is deployed in a computing device, which can be implemented by any device, equipment, platform, device cluster, etc. with computing and processing capabilities. Figure 3 The relational data table includes a first data column and a plurality of general data columns, a first text constraint is applied to the first data column, and a first data constraint is applied to the general data column, and the device 600 includes:

[0124] The acquisition module 601 is configured to obtain a first instruction, which is used to instruct to store first data into the relational data table, wherein the first data includes several first data items belonging to specific business attributes and several second data items belonging to general business attributes, and each data item includes its corresponding field name and field value.

[0125] The first storage module 602 is configured to store the plurality of first data items into a first data column based on the first text constraint.

[0126] The second storage module 603 is configured to store the plurality of second data items into corresponding general data columns based on the first data constraint.

[0127] According to another embodiment of the present specification, there is also provided a computer program product, including a computer program / instruction, which is executed by a processor to implement the aforementioned combination Figure 3 The steps of the method.

[0128] According to another embodiment, the present specification further provides a computing device, including a memory and a processor, wherein the memory stores executable code, and when the processor executes the executable code, the aforementioned combination is implemented. Figure 3The steps of the method.

[0129] Those skilled in the art should be aware that in one or more of the above examples, the functions described in the embodiments of the present invention may be implemented using hardware, software, firmware, or any combination thereof. When implemented using software, these functions may be stored in a computer-readable medium or transmitted as one or more instructions or codes on a computer-readable medium.

[0130] The specific implementation methods described above further describe the purpose, technical solutions and beneficial effects of the embodiments of the present invention in detail. It should be understood that the above description is only a specific implementation method of the embodiments of the present invention and is not intended to limit the scope of protection of the present invention. Any modification, equivalent replacement, improvement, etc. made on the basis of the technical solution of the present invention shall be included in the scope of protection of the present invention.

Claims

1. A method for processing business data based on a relational data table, wherein the relational data table comprises a first data column and a plurality of general data columns, a first text constraint is applied to the first data column, and a first data constraint is applied to the general data column; the method comprises: Obtain a first instruction, which is used to instruct to store first data into the relational data table, wherein the first data includes a plurality of first data items belonging to specific business attributes and a plurality of second data items belonging to general business attributes, and each data item includes a corresponding field name and field value; Based on the first text constraint, storing the plurality of first data items into a first data column; Based on the first data constraint, the plurality of second data items are stored in corresponding general data columns.

2. The method according to claim 1, wherein: The first data constraint includes one or more of the following: a foreign key constraint, a unique constraint, a non-null constraint, and a range constraint.

3. The method according to claim 1, wherein: The storing the plurality of first data items into a first data column comprises: For any first data item, determine a first key-value pair according to its first field name and first field value; Based on the text constraint, concatenate the first key-value pairs corresponding to the plurality of first data items to obtain a first text; The first text is stored in a first data column.

4. The method according to claim 3, wherein: The splicing operation comprises: The key-value pairs to be concatenated are marked with the first separator specified in the first text constraint.

5. The method according to claim 3, wherein: The determining of the first key-value pair includes: Determine a first code according to the first field name; The first key-value pair is composed of the first code as the key and the first field value.

6. The method according to claim 3, wherein: The method further comprises: Obtaining a second instruction, which is used to instruct to perform a second operation on the table data in the relational data table, wherein the second instruction includes query conditions of several data items related to the table data; For any query data item in the query condition, use its query field name as a key and the query field value to form a query key-value pair; Based on the query key-value pairs corresponding to the plurality of data items in the query condition, in the relational data table, table data is selected, wherein the first data value corresponding to the first data column in the selected table data contains the query key-value pair; The second operation is performed on the selected table data.

7. The method according to claim 6, wherein: The second operation is a read operation, and the second operation is performed on the selected table data, including: The field values ​​of each common data column and the first data column included in the selected table data are read out.

8. The method according to claim 6, wherein: The second operation is a delete operation, and performing the second operation on the selected table data includes: The selected table data is deleted from the relational data table.

9. The method according to claim 3, wherein: The method further comprises: Obtaining a third instruction, which is used to instruct, in the table data of the relational data table, to perform a third operation on a third key-value pair, wherein the third instruction includes a third field name; In the table data, based on the third field name, a corresponding third key-value pair is selected, wherein the key of the third key-value pair matches the third field name; The third operation is performed on the selected third key-value pair.

10. The method according to claim 9, wherein: The third operation is a read operation, and the third operation is performed on the selected third key-value pair, including: Read the value of the third key-value pair.

11. The method according to claim 9, wherein: The third operation is an update operation, the third instruction further includes a third update value, and performing the third operation on the selected third key-value pair includes: Update the value of the third key-value pair to the third updated value.

12. The method according to claim 9, wherein: The third operation is a deletion operation, and performing the third operation on the selected third key-value pair includes: The third key-value pair is deleted from the table data.

13. A device for processing business data based on a relational data table, wherein the relational data table comprises a first data column and a plurality of general data columns, a first text constraint is applied to the first data column, and a first data constraint is applied to the general data column; the device comprises: The acquisition module is configured to acquire a first instruction for instructing to store first data into the relational data table, wherein the first data includes a plurality of first data items belonging to specific business attributes and a plurality of second data items belonging to general business attributes, and each data item includes a corresponding field name and field value; A first storage module is configured to store the plurality of first data items into a first data column based on the first text constraint; The second storage module is configured to store the plurality of second data items into corresponding general data columns based on the first data constraint.

14. A computer program product, comprising a computer program / instruction, which, when executed by a processor, implements the steps of the method according to any one of claims 1 to 12.

15. A computing device comprising a memory and a processor, characterized in that: The memory stores executable codes, and when the processor executes the executable codes, the method according to any one of claims 1 to 12 is implemented.

Images