CN119938765A

CN119938765A - A method and device for processing business data based on relational data table

Info

Publication number: CN119938765A
Application number: CN202510121114.XA
Authority: CN
Inventors: 张鑫鑫
Original assignee: Alipay Hangzhou Information Technology Co Ltd
Current assignee: Alipay Hangzhou Information Technology Co Ltd
Priority date: 2025-01-24
Filing date: 2025-01-24
Publication date: 2025-05-06

Abstract

The embodiment of the specification provides 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 the steps of obtaining a first instruction, which is used for indicating to store first data into a relational data table, wherein the first data comprises a plurality of first data items belonging to specific service attributes and a plurality of second data items belonging to general service attributes, and each data item comprises a field name and a field value corresponding to the first data item. And storing the first data items into a first data column based on the first text constraint. And correspondingly storing the plurality of second data items into a common data column based on the first data constraint.

Description

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

Technical Field

One or more embodiments of the present disclosure relate to the field of database technologies, and in particular, to a method and apparatus for processing service data based on a relational data table.

Background

Database technology, a method of efficiently storing and managing business data in the form of a data table, has become a cornerstone of modern information systems.

In the database, the business records are organized according to predetermined data dimensions to form a structured strip of data that is stored in different data tables of the database for query and analysis. Common database-related technologies include Oracle, DB2, SQL SERVER, mySQL, and the like.

With the rapid development of internet technology, the digitized transformation of enterprises is accelerated, the business field is continuously expanded, and in an enterprise, a plurality of sub-business departments, each of which is responsible for a specific business line, usually exist. The business departments deposit professional business data with unique data structures according to the characteristics of the business lines.

Thus, within an enterprise, a plurality of business data with different characteristic data formats are formed. However, in the daily operation process of an enterprise, a cross-type service, especially a platform-type service, is usually generated, the required data spans across multiple service departments, and the data meeting the activity condition in the multiple service departments needs to be stored, and at this time, the data forms of the various service departments with different data structures are incompatible with each other, so that the data table of the platform-type service has to be expanded, so that the data table of the platform-type service can be compatible with service data in various formats, and efficient circulation and sharing of the data in the enterprise are hindered.

Therefore, a scheme is hoped to be available, service data can be dynamically processed through a technical means, and efficient access of the data is achieved.

Disclosure of Invention

One or more embodiments of the present disclosure describe a method and an apparatus for processing service data based on a relational data table, which can break through a data strong verification mechanism on the basis of maintaining a partial relational data constraint of service data, integrate service data with different data structures into one relational data table, and solve the above technical problems.

According to a first aspect, a method of processing business data based on a relational data table is provided, the relational data table comprising a first data column and a number of generic data columns, a first text constraint being applied on the first data column and a first data constraint being applied on the generic data column. The method comprises the following steps:

and acquiring a first instruction, which is used for indicating to store first data into the relational data table, wherein the first data comprises a plurality of first data items belonging to specific service attributes and a plurality of second data items belonging to general service attributes, and each data item comprises a field name and a field value corresponding to the first data item.

And storing the first data items into a first data column based on the first text constraint.

And correspondingly storing the plurality of second data items into a common data column based on the first data constraint.

According to one implementation, the first data constraint includes one or more of a foreign key constraint, a unique constraint, a non-null constraint, a range constraint.

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

for any first data item, a first key value pair is determined according to the first field name and the first field value.

And performing splicing operation on the first key value pairs corresponding to the first data items based on the text constraint to obtain a first text.

The first text is stored in a first data column.

According to one implementation, the performing a splicing operation includes:

Between pairs of key values that are to be stitched, a first separator specified in the first text constraint is used for marking.

According to one implementation, the determining the first key-value pair includes:

And forming the first key value pair by taking the first code as a key and a first field value.

According to one implementation, the method further comprises:

And obtaining a second instruction, which is used for indicating that the table data in the relational data table is subjected to a second operation, wherein the second instruction comprises query conditions of a plurality of data items related to the table data.

And aiming at any query data item in the query conditions, the query field name is used as a key, and the query field value form a query key value pair.

And selecting table data in the relational data table based on query key value pairs corresponding to a plurality of data items in the query condition, wherein a first data value corresponding to a first data column in the selected table data comprises the query key value pairs.

And carrying out the second operation on the selected table data.

In one scenario of the above implementation, the second operation is a read operation, and the performing the second operation with respect to the selected table data includes:

and reading out the field values of each general data column and the first data column contained in the selected table data.

In one scenario of the above implementation, the second operation is a delete operation, and the performing the second operation with respect to the selected table data includes:

And deleting the selected table data from the relational data table.

According to one implementation, the method further comprises:

A third instruction is obtained for indicating that a third operation is performed on a third key value pair in table data of the relational data table, the third instruction including a third field name.

And selecting a corresponding third key value pair based on the third field name in the table data, wherein the key of the third key value pair is matched with the third field name.

And performing the third operation for the selected third key value pair.

In one scenario of the above implementation, the third operation is a read operation, and the performing the third operation for the selected third key value pair includes:

reading the value of the third key value pair.

In one scenario of the foregoing implementation, the third operation is an update operation, the third instruction further includes a third update value, and the performing the third operation for the selected third key value pair includes:

and updating the value of the third key value pair to the third updated value.

In one scenario of the above implementation, the third operation is a delete operation, and the performing the third operation for the selected third key value pair includes:

the third key value pair is deleted from the table data.

According to a second aspect, there is provided an apparatus for processing business data based on a relational data table, the relational data table comprising a first data column and a plurality of generic data columns, a first text constraint being applied on the first data column and a first data constraint being applied on the generic data column, the apparatus comprising:

The acquisition module is configured to acquire a first instruction, which is used for indicating to store first data into the relational data table, wherein the first data comprises a plurality of first data items belonging to specific service attributes and a plurality of second data items belonging to general service attributes, and each data item comprises a field name and a field value corresponding to the first data item.

And a first storage module configured to store the number of first data items in a first data column based on the first text constraint.

And the second storage module is configured to store the plurality of second data items into the universal data column correspondingly based on the first data constraint.

According to a third aspect, there is provided a computer program product comprising computer programs/instructions which, when executed by a processor, implement the steps of the method of the first aspect.

According to a fourth aspect, there is provided a computing device comprising a memory and a processor, characterized in that the memory has stored therein executable code, which when executed by the processor, implements the method of the first aspect.

In summary, by adopting the method and the device disclosed in the embodiments of the present disclosure, the data field values originally scattered in multiple columns may be integrated into one column by processing the data items included in the service data based on the specific text constraint, so as to implement flattening processing on the service data. By adopting the mode for storing the service data, the data format barriers among the service data can be broken, the purpose of storing the service data with different data structures in the same relational data table is achieved, moreover, the storage and the use of the historical service data are not affected by the expansion of the service data format, the decoupling of the storage and the use of the service data is realized, and the data storage efficiency and the flexibility are greatly improved.

Drawings

In order to more clearly illustrate the technical solution of the embodiments of the present invention, the drawings that are required to be used in the description of the embodiments will be briefly described below. It is evident that the drawings in the following description are only some embodiments of the present invention and that other drawings may be obtained from these drawings without inventive effort for a person of ordinary skill in the art.

FIG. 1 is a table of relational data disclosed in embodiments of the present disclosure;

FIG. 2 is a schematic diagram of a framework for processing business data based on a relational data table according to an embodiment of the present disclosure;

FIG. 3 is a flowchart of a method for processing business data based on a relational data table according to an embodiment of the present disclosure;

FIG. 4 is a flow chart of a method for reading and deleting table data according to an embodiment of the present disclosure;

FIG. 5 is a flowchart of a method for updating and deleting key pairs in table data according to an embodiment of the present disclosure;

fig. 6 is a schematic diagram of an apparatus for processing service data based on a relational data table according to an embodiment of the present disclosure.

Detailed Description

The embodiments of the present invention will be described below with reference to the accompanying drawings.

At present, in a plurality of enterprises, database technology is not used as the technical basis for data analysis and business activities. In particular, the use of relational database technology such as SQL (Structured Query Language ) has made it a key tool for data processing and querying.

As described above, with the continuous expansion of business scale of enterprises and the gradual completion of informatization construction, different business departments will generate business data according with the characteristics of respective business lines, and these business data have different data structures. In a relational database, differences in data structure may include, for example, different data items (i.e., data columns), different enumeration mappings (i.e., foreign key mappings), and so forth.

Business data having different data structures as described above will cause trouble to the data processing of the platform business sector. In a platform-type system component supporting a multiparty service, a relational database plays a core role, storing service data required for upstream and downstream services, and therefore, the database must be capable of receiving and processing service data storage requirements from a plurality of upstream service departments, while also providing corresponding service data reading capabilities for downstream service departments. For example, the transaction analysis component, the coupon distribution component, the shopping cart management component, etc. need to store various business data from the upstream business end, and also need to provide corresponding business data reading service for the downstream business end, so as to realize the function of individuation of each business strip line in the system. In order to meet the data access requirements, the relational data tables used to store the business data in the platform-type system components need to be designed to be flexible and expandable to accommodate the need to access the business data of different data structures.

For example, in an enterprise, customer behavior data generated by a marketing department may be different from customer feedback data generated by an after-sales department in terms of data structure, and in view of strong type verification rules of a relational database, when the data is stored in a platform-type service database, it is required to perform manual data cleaning according to predetermined data column mapping rules, and store the data in the database after unifying data formats. Taking as an example a user data table for storing user information, in which each user is represented by a piece of service data comprising a number of user data items, such a data table storing service master data is also referred to as a fact table. Fact tables are typically designed around business processes for recording specific business events, business entities, wherein specific business elements (i.e., data items) of each business event, business entity are contained, typically stored as measurable numerical data or comparable character-type data. Each row of the fact table data may represent a business event, business entity, such as an order event, a payment event, a user entity, a merchandise entity, and so forth.

FIG. 1 discloses an exemplary relational data table. Referring to the drawings, the header of the user data table represents the names of various fields of the user data, and various business data represent various business entities (i.e., users) generated in a business activity, respectively. As shown in the figure, each business data includes a field value corresponding to an ID, a name, a living area, an age, and four fields. In the figures, the data table design of the user data table is also shown, which, unlike the presentation of the data table, does not contain specific business data entries. In the data table design, each record in the right column represents a respective header (i.e., field name) of the data table, and in the corresponding left column, the data constraint of the header, for example, the primary/foreign key information, and the data type definition, is recorded. It should be appreciated that in the figure, the data table and the corresponding data table design examples only include the data table design elements required to illustrate the present embodiment, and in the actual table design activity, the data table structure may include more design elements, such as a data index.

In the table design of the user information table shown in fig. 1, there are a data table primary key (identified by PK in the figure), "ID", a character type (identified by nvarchar in the figure), "name", a numeric type (identified by int in the figure), "age", and an external key (identified by FK in the figure), "usual region", the external key field representing only one field associated with the corresponding dimension table primary key, and specific dimension information is not recorded. For example, in the user data table, the area name is not directly stored for the resident area of a certain user, but the area ID corresponding to the area in the area dimension table is recorded, and when the downstream service performs data reading, the corresponding area name (in this example, the city name) may be queried in the area dimension table according to the area ID. Therefore, it is easy to understand that when storing service data in the user data table, for the field with foreign key association, the corresponding foreign key value needs to be found in the dimension table according to the corresponding service value in the service data, and then is stored in the corresponding field of the user data table as the field value.

With continued reference to fig. 1, in the illustrated scenario, there is a need to store two pieces of traffic data (traffic data a and traffic data B) for a user data table. Wherein the service data A has data fields and field values corresponding to the user data table structure one by one, and can be directly stored in the table, for example, by writing an insert sentence of the direct access table (as shown in SQL sentence associated with the service data A in the figure). However, in specific practice, among the service data from different service departments, such as the service data a, which are butted by the platform-type database and are completely aligned to the fields of the user data table, less service data are usually required, more commonly, the service data to be stored are transferred according to the format of the service department to which each service data belongs, and although the necessary field information required by the platform-type user data table is included, the formats are not consistent. Taking service data B as an example in the drawing, wherein the service data B contains the field information of 'name', 'usual region', 'age' required by a user data table, but the service data B is presented in different field forms, wherein the name is split into two fields of last name and first name, the field value of the usual region is not the standard enumeration value of a region dimension table, and the age is calculated according to the information of birth year. The common field mismatch problem may be caused by that service data cannot be standardized for a user to manually input in a front-end page, may be caused by that at the beginning of design of a user data table, temporary field values are added without fully considering the requirement of data expansion in the future, may be caused by that preset data access rules different from the user data table exist between upstream and downstream services, and the like, and are not listed here one by one.

Therefore, for such service data, it is necessary to convert each field value that does not meet the field requirements of the user data table before entering the table when storing. Continuing with the SQL example statement associated with business data B in FIG. 1, a customized field value conversion method is applied to convert business data B into field values receivable by the user data table. For example, the last name and first name section values of the service data B are converted into names by a text splicing conversion method and are inserted into name fields of the user data table, the ordinary region field values of the service data B are enumerated and converted by a condition judgment function (for example, CASE statement and IF statement), the standard enumeration values in the region dimension table are mapped to query corresponding region IDs and are inserted into ordinary region fields of the user data table, and the birth year of the service data B is converted into age values and are inserted into age fields of the user data table by a numerical conversion method.

By describing the method for storing different service data into the user data table, it can be seen that, due to the strong type verification rule of the user data table constructed in the relational database, the field values which do not conform to the field format of the user data table in the service data to be stored all need to be converted by performing customization, and the data storage can be performed. It should be understood that in the example shown in the drawings, the user data table is set forth, but is not meant to be a limitation on the platform-type data table in practice, and in particular practice, the platform-type data table may be a data table related to any business entity, which is not specifically limited herein. In practice, however, such data-customized transformations as set forth in the examples above are typically implemented by a developer manually writing the relevant SQL statement, with understanding of the field value definitions of the upstream services. Along with the continuous increase of upstream business departments of platform database butt joint, the custom field values (non-standardized) of upstream business are increased, and the process of converting the custom field values not only needs to consume a great deal of manpower, but also can lead to the data entry codes of the business data table to be more and more bloated, and the maintainability of the codes is reduced. In addition, when the downstream service reads the service data stored in the service data table, the reverse operation of the conversion method is also needed to restore the corresponding field value, so that the complexity of data reading is greatly increased.

In some scenarios, when the service department expands the service data field to be stored, the service data table also needs to update its own table structure, and adds a corresponding new field to store the expanded field value. In the relational database, the change of the table structure not only relates to the update of the target service data table, but also has a plurality of influences. For example, the reconstruction of an index on the target service data table may temporarily reduce the performance of the database, the database may temporarily lock the table when the target service data table updates the table structure, which may affect the execution of other transactions attempting to access the table, and some data objects depending on the view (view), storage process (StoredProcedure), function, etc. of the target service data table may require synchronized modification and compilation.

All the above listed problems show that, as a platform database, when the relational service data surface is required for accessing service data with different upstream and downstream services, not only higher labor cost is generated to customize the script, but also the flexibility of the data table is insufficient, and the dynamic update of the service data field of the service department cannot be dealt with.

In order to solve the above-mentioned problems, the present inventors propose a method and apparatus for processing service data based on a relational data table in the embodiments of the present disclosure, wherein each data item (including a field name and a corresponding field value) in service data is classified, wherein a data item belonging to a specific service attribute includes service data unique to an upstream service, and a data item belonging to a general service attribute includes service data (e.g., creation date, service category, etc.) common to all upstream services, so that a corresponding storage policy may be adopted according to the category of the data item, i.e., direct access table storage may be performed for the general service attribute data items included in all upstream services, and a text aggregation operation may be performed for the specific service attribute data item having a possibility of frequent change, and the service data item may be stored in a text form. Therefore, the service data with different data items can be stored in the same service data table, and the data storage efficiency and flexibility are improved. Fig. 2 shows a schematic diagram of a framework for processing service data based on a relational data table, in the service data to be stored, M specific data items belonging to specific service attributes and N general data items belonging to general service attributes, each data item being composed of a field name and a corresponding field value. Referring to fig. 2, in the business data entry process, each general data item may be directly stored in each general data column of the business data table in a corresponding manner, and for M specific data items, the specific data items may be converted into an aggregate text through a text aggregation operation based on specific text constraints, and stored as field values in the first data column of the business data table. In this way, a plurality of 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 into the relational business data table, so that the business data storage requirements of different upstream businesses are met in one relational data table.

Following the above technical concept, in fig. 3, a flowchart of a method for processing service data based on a relational data table according to an embodiment of the present specification is shown. It is understood that the method may be performed by any apparatus, device, platform, cluster of devices having computing, processing capabilities. Referring to fig. 3, in one embodiment, the relational data table includes a first data column and a plurality of general data columns, wherein 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 method at least includes the following steps. S301, a first instruction is acquired and used for indicating that first data are stored in the relational data table, wherein the first data comprise a plurality of first data items belonging to specific service attributes and a plurality of second data items belonging to general service attributes, and each data item comprises a field name and a field value corresponding to the first data item. And S303, storing the plurality of first data items into a first data column based on the first text constraint. And S305, based on the first data constraint, correspondingly storing the plurality of second data items into a common data column.

Specific implementations of the above steps will be described in detail below with reference to the accompanying drawings.

In step S301, a first instruction is acquired, which is used to instruct that first data be stored in the relational data table.

The first instruction is a corresponding command or statement executable by a database query engine, corresponding to the type of relational database (e.g., mySQL, oracle DB, postgreSQL, etc.) to which the relational data table (i.e., the aforementioned business data table) belongs. Taking a typical SQL standard statement supported by a relational database as an example, the first instruction may be an SQL statement, for example, an INSERT statement, which indicates that first data is stored in the relational database, or a call instruction to a relational database storage interface, where the call instruction includes first data to be stored, or an instruction that instructs the relational database to load a data file including the first data, so as to store the data in the relational database.

As described above, in the relational database, the data table is composed of a plurality of data rows (i.e., table data) and a plurality of data columns (i.e., fields), wherein each row of table data represents a piece of service data, each column represents a field in the service data, and the rows and columns intersect to form data items, i.e., field names and corresponding field values. In the design of a data table, one or more fields are typically designated as primary keys (PRIMARY KEY, PK), the uniqueness of which ensures uniqueness of each row of business data in the data table. In the embodiment of the present specification, in order to simplify the description, the primary key is not described excessively, and it is understood that when table data is inserted into a data table, a self-increasing positive integer is assigned to new table data as the primary key. In addition, to maintain associations between multiple data tables, foreign Keys (FK) are also used to reference primary keys in other tables (e.g., dimension tables), thereby establishing table-to-table associations.

In order to ensure the integrity of each piece of data stored in the data table and the compliance of service rules, data constraint can be set on the data columns of the data table, the stored service data is verified based on the data constraint, and the storage of invalid data is prevented.

In one embodiment, the relational data table comprises a first data column with a first text constraint applied thereto and a plurality of generic data columns with a first data constraint applied thereto. Wherein the first text constraint may be used to specify that the text of the respective table data corresponding to the field value of the first data column is organized in a preset format, as will be explained in detail below with respect to the first text constraint. The first data constraint may include one or more of a foreign key constraint, a unique constraint, a non-null constraint, a range constraint. The foreign key constraint is used for maintaining the relation between the relational data table and other tables, and the field value corresponding to the field (or the field combination) to which the foreign key constraint is applied is to be valued at the primary key of another table or is null (if the primary key can be null). Unique constraints are used to ensure that the value of a field (or the combined value of multiple fields) in the relational data table is unique across the table, i.e., that duplicate values are not allowed, typically, e.g., primary key fields. The non-NULL constraint requires that a field in the relational data table cannot have a NULL value (NULL), which means that when new data is stored in the data table, it is ensured that the field has a field value, and when the table data is updated, the field value of the field cannot be deleted. The range constraint may limit the field values in the fields to stay within a range of business significance, e.g., for an integer type of field, the range of values may be set to 0 to 100.

The first data may be data from an upstream service, which generally includes key information (e.g., customer information, order information, transaction information, etc.) in a service flow, and needs to be stored in the relational data table for a downstream service to read through execution of a first instruction, so as to form a circulation of service data. The first data comprises a plurality of first data items belonging to specific service attributes and a plurality of second data items belonging to general service attributes, and each data item comprises a field name and a field value corresponding to the data item.

The specific business attribute may be a specific business attribute in a business process for generating the first data, for example, an order priority attribute in a commodity selling business, a customer loyalty score in a customer management business, etc., or may be a business attribute which may change at any time in the business process, for example, a business attribute which is newly added to adapt to business development. The general service attribute may be a common service attribute provided in an upstream service flow, for example, a creation date of the first data, a service class, and the like. The definition of the data item is described in detail in the foregoing, and will not be repeated here.

Next, in step S303, the number of first data items is stored in a first data column based on the first text constraint.

As stated above, for each first data item belonging to a specific service attribute, it may be converted into an aggregate text and stored in the relational data table. Thus, in this step, each first data item may be converted into data represented in a text manner, the text data including the field name and the field value of the first data item, so as to ensure that the converted text still fully retains various types of information of the first data item. Such an aggregate Text conversion may be specified by setting specific Text constraints, such as Plain Text (Plain Text) conversion, markup language (e.g., markdown) conversion, and so forth.

According to one embodiment, storing the number of first data items in the first data column may be achieved by the following steps.

In this step, each first data item may be converted into text represented in a key-value pair, which may ensure that the converted text still retains the field name and field value of the first data item intact. Specifically, the field name of the first data item is used as a key, the corresponding field value is used as a value, and the key and the value are separated by a preset separator (such as comma, colon, etc.), so that a key value pair corresponding to the data item is constructed.

In some specific scenarios, service data to be stored may originate from a plurality of different service departments, and fields with the same name but different service meanings may exist between the service data, and after the service data are stored in a text form of a key value pair, the service departments to which the same name key belongs are difficult to distinguish, which may cause trouble to data reading of downstream services. In other specific scenarios, there may be some special field names in the service data to be stored, for example, the field name is very long, the field name collides with a keyword of the relational database, and when such special field names are used as keys, the downstream service may often cause the database query engine to fail to parse correctly and report errors.

In order to avoid the above problem, in one implementation, a first code may be determined according to the first field name, and the first code is used as a key to form the first key value pair with a first field value. In other words, the implementation may convert the field name of the service data to be stored (i.e. the first data) into the first code by a certain coding rule, where the first code may be a globally unique identification code (for example, add a UUID to the end of the field name), or may be a hybrid code that encodes specific service information (for example, add a field name to the code of the service department to which the first data belongs as a prefix). In general, the first code may be obtained based on specific coding rules according to specific traffic needs. In one example, the initial of the pinyin of the first field name text may be extracted as the first encoding, e.g., the field name "department name" is encoded as "BMMC". In another example, english text of the first field name may also be used as the first code, for example, the field name "Department name" is encoded as "device". In other examples, the field name of the first data may be encoded by using an encoding rule agreed by an upstream and downstream service department, so as to obtain a first code with restorability, when the downstream service queries data, the target query field name may be encoded according to an agreed encoding rule, the encoding is used to write a query sentence, after the query result is obtained, the first code in the query result is decoded based on the agreed encoding rule, and the field name is restored. It can be appreciated that the coding rules are various, and in different service scenarios, the coding rules can be selected according to specific needs, and the embodiments of the present disclosure are not illustrated here.

Through the processing of the steps, each first data item belonging to the specific service attribute in the first data can be correspondingly converted into a first key value pair. A text aggregation operation may then be performed on the first plurality of key-value pairs for storage in a first data column of the relational data table.

Therefore, the first key value pairs corresponding to the plurality of first data items can be spliced based on the first text constraint to obtain the first text.

In this step, the concatenation operation may be a concatenation operation based on a relational query grammar, for example, concatenation of several first key-value pairs in text form through CONCAT, CONCAT _ws operation. Or a splicing operation based on a programming language function, for example, a join function of a character string or a character string formatting (such as format and f-string in sprintf, python in a C language) method is used, and a plurality of first key value pairs are combined and spliced according to a certain template format. Other text splicing methods are also possible, the selection of which depends on the specific application scenario and development habit, and the embodiment of the present specification is not limited in detail.

According to one implementation, between pairs of key values that are to be stitched, a first separator specified in the first text constraint may be used for marking. That is, a first separator is inserted between every two first key value pairs for marking the boundary between the key value pairs, and the first text can be accurately divided into a plurality of original key value pairs based on the first separator during the data processing of the downstream service. Thus, the readability of the first text can be enhanced, and the subsequent data query and analysis can be facilitated.

It can be appreciated that the setting of the first separator can be flexibly adjusted according to actual requirements, so as to avoid the conflict with the characters possibly occurring in the first key value pair. In one particular scenario, the first delimiter may include one or more of brackets, carets (, a), a lineman.

After the first text is determined, a table entry operation may be performed to store the first text in a first data column. In practical applications, the relational data table may have a plurality of first data columns, and each first data column may store a first text formed by a plurality of first key value pairs. The number of the plurality of first data columns may be determined according to the text length limitation of the relational data table on the first data columns, for example, in order to avoid the text length exceeding the limit of the first data columns, the relational data table may be provided with a plurality of first data columns so as to store the ultralong text in a scattered manner, so that the content of the first data columns in each table data is ensured not to exceed the length limitation of the database field.

While the first data item is being stored, in step S305, the plurality of second data items are correspondingly stored in the common data column based on the first data constraint.

As previously described, the second data item corresponds to a data item belonging to a generic service attribute, such data item having a corresponding data column (i.e. the generic data column) in the relational data table. In the process of entering the table into the first data, for the second data item, a corresponding general data column can be found one by one, and the second data item is stored in the corresponding general data column. It will be appreciated that, when storing the second data item, the field name and the field value included in the second data item may be stored according to the data constraint corresponding to the general data column, or only the field value or the field name of the second data item may be stored, which is not limited herein.

The above is an introduction to a storage flow for processing service data based on a relational data table provided in the embodiments of the present specification. In some embodiments of the present disclosure, a related method flow for reading and modifying table data based on the relational data table is further provided, so as to ensure that each downstream task can correctly acquire and process the table data in the relational data table.

Fig. 4 is a flowchart of a method for reading and deleting table data based on a relational data table according to an embodiment of the present disclosure. At least comprises the following steps:

step S401, a second instruction is obtained and used for indicating that the table data in the relational data table is subjected to a second operation, wherein the second instruction comprises query conditions of a plurality of data items related to the table data.

Similar to the first instruction set forth above, the second instruction may be a command or a sentence written in a query language supported by a database to which the relational data table belongs, where a query clause of the second instruction includes a query condition corresponding to a query target (i.e., the table data), where the query condition is used to constrain the query target to take a value on any data column, including, but not limited to, limitation of a value content, limitation of a value range, and limitation of a value attribute (e.g., text length, value type, etc.). In terms of a target column to which a query condition is applied, the query condition may be set for a general data column, the general data column has a data constraint characteristic inherent to a relational database, constraint setting for the general data column in the query condition may be performed according to data constraint of the target data column, writing of a conditional constraint clause may be performed according to a corresponding grammar specification in a query language, and in an execution stage of the query statement, a database query engine may refer to the query condition for the general data column according to an inherent query execution method, so as to realize screening of table data in the relational data table, which is not described in detail in the embodiment of the present specification. The query condition may be set for the first data column having a first text constraint in which a plurality of data items (key value pairs) of each piece of service data specific service attribute are stored in text form. In the query condition applied to the first data column, a constraint for a number of data items of the query target may be included, for example, a constraint for the number of key value pairs included in the first data column, a constraint for the field name or field value content of any data item, and so on. It will be appreciated that the two applications of the above exemplary query conditions may also be used in combination, i.e. in the query clause of the second instruction, both the query conditions applied to any general data column and the query conditions applied to the first data column are included. The execution method for the query condition applied to the first data column will be described in detail below.

As previously described, the constraints applied to the query condition of the first data column include a number of data items for the query target. Thus, in step S403, for any query data item in the query condition, a query key value pair is formed with its query field name as a key and a query field value.

For different constraint types of the data items in the query condition, corresponding rules can be applied to form query key value pairs. The classification is described below.

In one example, the query condition may include a single constraint on a field name of an arbitrary data item, for example, a LIKE keyword is used to constrain the field name, and the query clause may be a column_name LIKE '% name%', where column_name is a query keyword corresponding to the field name, and the LIKE keyword represents that, in the query clause, a text fuzzy query is applied, and% is a text wild card. The query clause of this example may represent a query for records with a name two word in all field names. Correspondingly, when the query key value pairs are combined, the unconstrained field values in the clauses can be replaced by wild cards to represent any value, and the formed query key value pairs can be (package symbols taking angle brackets as key value pairs and colon as key value segmenters are taken as examples) with < '> name% >' >.

In another example, the query condition may also include a single constraint on the field value of any data item, for example, fuzzy matching is performed on the field value by using a text wild card, and similar to the above example, the corresponding query key value pair may be constructed as: <'%: integral% >, which indicates that all records containing integral two words in the field values are queried.

In other examples, the query condition may be a combined application of the two examples, that is, the constraint on the field name and the field value of any data item is included at the same time, which is not further described in the embodiments of the present specification.

After determining the query key value pairs based on the query conditions, in step S405, table data is selected in the relational data table based on the query key value pairs corresponding to the data items in the query conditions, and a first data value corresponding to a first data column in the selected table data contains the query key value pairs.

As described above, the data stored in the first data column is of a text type, so that during the query execution process, table data in the relational data table may be filtered based on the query key value pair by using a text comparison method. For example, using the INSTR function, a determination is made as to whether the first data column content of each piece of table data contains a query key value pair specified in the query condition.

And screening a plurality of table data meeting the query conditions in the second instruction from the relational data table according to the text comparison result, and taking the table data as a query result.

Next, based on the query result obtained in the above step, the second operation is performed with respect to the selected table data in step S407.

The second operation may be a read operation (e.g., a SELECT statement), a DELETE operation (e.g., a DELETE statement), or various operations supported by the relational database for processing the table data, which are not specifically limited herein.

In a specific example, the second operation is a read operation, and in this step, the field values of the first data column and each of the general data columns included in the selected table data may be read out. During reading, for each selected table data in the query result, the values corresponding to the generic data columns may be in the order set in the read operation clause (e.g., SELECT a, B.; the set order is the column a and the column B), and when the set order (for example, SELECT) does not exist in the read operation clause, the set order of the general data columns may be output in accordance with the table design of the relational data table, and the specific limitation is not made here. The field value of the first data column can be directly output according to a single text value, or the reverse operation can be executed according to the first text constraint followed by data storage, and the field value of the first data column is restored into a plurality of first key value pairs to be output. In some practices, the first key value pair may be further subjected to a restore operation (if there is an encoding operation, a corresponding decoding operation may be performed), so as to obtain a data item in the original service data, and output the data item.

In another specific example, the second operation is a delete operation, in which the selected table data may be deleted from the relational data table. According to the difference of the deleting commands in the second operation, different deleting operations can be correspondingly executed. For example, for DML operations such as DELETE, a transaction-based tag DELETE may be performed to DELETE and tag the selected table data, but without immediately freeing the table space of the relational data table, the table data may be retrieved by rollback transactions if necessary, while for DDL operations such as TRUNCATE, DROP, the selected table data is destroyed and immediately freeing the table space corresponding to the relational data table.

In other embodiments of the present disclosure, related method flows for updating and deleting key value pairs in table data based on the relational data table are also provided. FIG. 5 illustrates a related exemplary method flow diagram provided by one embodiment, including at least the steps of:

Step S501, a third instruction is acquired and is used for indicating that a third operation is performed on a third key value pair in table data of the relational data table, wherein the third instruction comprises a third field name.

Similarly, the third instruction may be a command or statement written using a query language supported by a database to which the relational data table belongs. The table data may be table data stored in the relational data table, and may be table data included in a query result obtained by the query operation performed in the above example, or may be all table data in the relational data table, which is not particularly limited herein.

The third instruction may be an instruction for processing the target key value pair, where a field name of the target key value pair, that is, the third field name is included. It is understood that the third field name used as the query condition may be a field name text for exact matching, such as a department name, a coded text of a field name, such as BMMC, or a text containing a wild card for fuzzy matching, such as%name, or%mc%.

Next, in step S503, in the table data, a corresponding third key value pair is selected based on the third field name, the key of the third key value pair matching the third field name.

As described above, since each key value pair is stored in the form of text in the table data of the relational data table, the corresponding third key value pair whose key matches the third field name can be retrieved based on the third field name by using the text comparison method during the execution of the query. That is, the keys of the third key-value pair are exactly matched with the third field name or conform to the fuzzy matching rule specified by the third field name.

It will be appreciated that for any table data, which may contain a plurality of key-value pairs, each key of which is able to match the third field name, the third key-value pair queried may be a plurality.

Based on the query result obtained in the above step, the third operation is performed for the selected third key value pair in step S505.

The third operation may be a read operation (e.g., a SELECT statement), an UPDATE operation (e.g., an UPDATE statement), a DELETE operation (e.g., a DELETE statement), or various operations supported by the relational database for processing the table data, which are not specifically limited herein.

In a specific example, the third operation is a read operation, in which the value of the third key value pair may be read, returning as a result. When a plurality of third key value pairs exist, the values of the key value pairs can be returned as independent data records, namely, each data record only comprises the value of one key value pair in the returned reading result, and the reading result can be divided according to the table data to which the key value pairs belong, namely, the values of a plurality of key value pairs belonging to the same table data in the returned reading result are displayed in the same data record.

In another specific example, the third operation is an update operation, and the third instruction further includes a third update value. In this step, the value of the third key-value pair may be updated to the third updated value. It will be appreciated that for updating the median value of the key-value pair, there may be an updating operation (e.g. UPDATE statement) that covers the value of the third key-value pair with the third updated value, or a replacing operation (e.g. REPLACE statement) that REPLACEs, in the value of the third key-value pair, the text substring that meets the matching condition with the third updated value according to the matching condition given by the replacing operation.

In another specific example, the third operation is a delete operation, in which the third key value pair may be deleted from the table data. In some practices, during the deletion of the third key-value pair from the table data, the first delimiter specified in the first text constraint may also be synchronously deleted to ensure that the contents of the first data column of the table data after the deletion operation is performed remain in compliance with the first text constraint.

The above is an introduction to a method for processing service data based on a relational data table provided in the embodiments of the present disclosure, although in the above embodiments, the flow of the method is mainly described by taking an SQL statement as an example. The technical concepts embodied therein may also be applied to other relational databases and their supported processing of query languages.

In accordance with one or more embodiments, a method for processing business data based on a relational data table is described above in detail. By adopting the method provided by the embodiment of the specification, the data items in the business data can be expressed and standardized by using the unified key value pair format, the key value pairs are spliced, the data items originally scattered in a plurality of columns are integrated into a single data column, the flattening processing of the business data is realized, the storage modes of the business data with different data structures are unified, and the aim of automatically processing multi-party business data access is fulfilled in the relational data table.

In this specification, the words "first" in the words first instruction, first data item, and the like, and the words "second", "third" (if any), and the like, are used merely for convenience of description and distinction, and are not intended to be limiting in any way.

The foregoing describes certain embodiments of the present disclosure, other embodiments being within the scope of the following claims. In some cases, the actions or steps recited in the claims can be performed in a different order than in the embodiments and still achieve desirable results. Furthermore, the processes depicted in the accompanying figures are not necessarily required to achieve the desired result in the particular order shown, or in a sequential order. In some embodiments, multitasking and parallel processing are also possible, or may be advantageous.

Fig. 6 is a schematic diagram of an apparatus for processing service data based on a relational data table according to an embodiment of the present disclosure. The apparatus 600 is deployed in a computing device that may be implemented by any means, device, platform, cluster of devices, etc. having computing, processing capabilities. This embodiment of the device corresponds to the embodiment of the method shown in fig. 3. The relational data table includes a first data column to which a first text constraint is applied and a plurality of general data columns to which a first data constraint is applied, the apparatus 600 includes:

The obtaining module 601 is configured to obtain a first instruction, which is configured to instruct to store first data into the relational data table, where the first data includes 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 includes a field name and a field value corresponding to the first data item.

The first storage module 602 is configured to store the number of first data items in a first data column based on the first text constraint.

The second storage module 603 is configured to store the plurality of second data items in the common data column based on the first data constraint.

According to an embodiment of a further aspect, the present description also provides a computer program product comprising a computer program/instruction which, when executed by a processor, carries out the steps of the method described above in connection with fig. 3.

According to an embodiment of yet another aspect, the present disclosure further provides a computing device, including a memory and a processor, wherein the memory stores executable code, and the processor, when executing the executable code, implements the steps of the method described above in connection with fig. 3.

Those skilled in the art will appreciate that in one or more of the examples described above, the functions described in the embodiments of the present invention may be implemented in hardware, software, firmware, or any combination thereof. When implemented in software, these functions may be stored on or transmitted over as one or more instructions or code on a computer-readable medium.

The foregoing detailed description of the embodiments of the present invention further details the objects, technical solutions and advantageous effects of the embodiments of the present invention. It should be understood that the foregoing description is only specific to the embodiments of the present invention and is not intended to limit the scope of the present invention, and any modifications, equivalent substitutions, improvements, etc. made on the basis of the technical solutions of the present invention should be included in the scope of the present invention.

Claims

1. A method for processing service 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 on the first data column, and a first data constraint is applied on the general data column, and the method comprises the following steps:

Acquiring a first instruction, which is used for indicating to store first data into the relational data table, wherein the first data comprises a plurality of first data items belonging to specific service attributes and a plurality of second data items belonging to general service attributes, and each data item comprises a field name and a field value corresponding to the first data item;

Storing the plurality of first data items into a first data column based on the first text constraint;

2. The method of claim 1, wherein the first data constraint comprises one or more of a foreign key constraint, a unique constraint, a non-null constraint, a range constraint.

3. The method of claim 1, wherein the storing the first plurality of data items into the first data column comprises:

For any first data item, determining a first key value pair according to the first field name and the first field value;

Performing splicing operation on the first key value pairs corresponding to the first data items based on the text constraint to obtain a first text;

The first text is stored in a first data column.

4. A method according to claim 3, wherein said performing a stitching operation comprises:

5. A method according to claim 3, wherein said determining a first key-value pair comprises:

Determining a first code according to the first field name;

and using the first code as a key and forming the first key value pair with a first field value.

6. A method according to claim 3, wherein the method further comprises:

acquiring a second instruction for indicating to perform a second operation on table data in the relational data table, wherein the second instruction comprises query conditions of a plurality of data items related to the table data;

Aiming at any query data item in the query conditions, the query field name is used as a key to form a query key value pair with the query field value;

Selecting table data in the relational data table based on query key value pairs corresponding to a plurality of data items in the query condition, wherein a first data value corresponding to a first data column in the selected table data comprises the query key value pairs;

and carrying out the second operation on the selected table data.

7. The method of claim 6, wherein the second operation is a read operation, the second operation performed on the selected table data comprising:

8. The method of claim 6, wherein the second operation is a delete operation, the second operation performed on the selected table data comprising:

And deleting the selected table data from the relational data table.

9. A method according to claim 3, wherein the method further comprises:

Obtaining a third instruction for indicating that a third operation is performed on a third key value pair in table data of the relational data table, wherein the third instruction comprises a third field name;

Selecting a corresponding third key value pair based on the third field name in the table data, wherein the key of the third key value pair is matched with the third field name;

and performing the third operation for the selected third key value pair.

10. The method of claim 9, wherein the third operation is a read operation, the performing the third operation for the selected third key value pair comprising:

reading the value of the third key value pair.

11. The method of claim 9, wherein the third operation is an update operation, the third instruction further comprising a third update value, the third operation performed for the selected third key value pair comprising:

and updating the value of the third key value pair to the third updated value.

12. The method of claim 9, wherein the third operation is a delete operation, the third operation performed on the selected third key value pair comprising:

the third key value pair is deleted from the table data.

13. An apparatus for processing business data based on a relational data table, the relational data table comprising a first data column and a plurality of generic data columns, a first text constraint applied on the first data column and a first data constraint applied on the generic data column, the apparatus comprising:

The acquisition module is configured to acquire a first instruction and is used for indicating to store first data into the relational data table, wherein the first data comprises a plurality of first data items belonging to specific service attributes and a plurality of second data items belonging to general service attributes, and each data item comprises a field name and a field value corresponding to the first data item;

a first storage module configured to store the number of first data items into a first data column based on the first text constraint;

14. A computer program product comprising computer programs/instructions which, when executed by a processor, implement the steps of the method of any of claims 1-12.

15. A computing device comprising a memory and a processor, wherein the memory has executable code stored therein, which when executed by the processor, implements the method of any of claims 1-12.