A method for converting business label data into a data warehouse wide table in real time based on FLink CDC

Abstract

The application belongs to the technical field of databases, and discloses a method for converting business label data into a data warehouse wide table in real time based on FLink CDC, which comprises the following steps: capturing data changes of a business system user label relationship narrow table in real time through FLink CDC, creating a label field in a wide table in real time, and storing user and label value updates into an analytical database, so as to realize the conversion of a label narrow table into a wide table. The application converts a business system table into a wide table in an analytical database in real time, and can reduce the construction delay of a label wide table from days to seconds in a scenario of having hundreds of thousands of labels for hundreds of millions of users, so that a business administrator can immediately query completed labels created for users, improve the query speed, reduce the database execution time, and save computing resources.

Description

TECHNICAL FIELD

[0001] The present application relates to the technical field of database, and in particular to a method for converting business label data into a data warehouse wide table in real time based on FLink CDC. BACKGROUND

[0002] Labels have the characteristics of high generalization and easy understanding, which can make it easier for people to understand users and facilitate computer processing. Traditional Internet or government business system label services add a series of labels to users according to user attributes, user preferences, living habits, user behavior and other information. Label business personnel create a record of user and label relationship in a relational database label narrow table during the process of labeling users. Business systems often store a large number of users and labels, and the label relationship record table will have hundreds of millions or even billions of data rows. Querying user information based on multiple label combinations will have a huge performance problem. Directly querying labels based on business relational databases will seriously affect database performance and the stability of business systems.

[0003] The traditional way is to pull business system data by Hadoop MapReduce tasks or Spark tasks on a daily basis, extract and convert the data into a label wide table, and write it into a Hive data warehouse or other MPP analytical database. That is, there is only one row of data for each user, each label is a field, and the value of the label is the value of the field. This way of generating label wide tables offline and on a fixed schedule has poor data timeliness. The labels created by business personnel and the labels added for users cannot be generated in the label wide table immediately and can only be queried after the offline task runs the next day. SUMMARY

[0004] To solve the above problems, the present application provides a method for converting business label data into a data warehouse wide table in real time based on FLink CDC. The Flink CDC technology is used to listen to business database data in real time, which can write labels created by business personnel and labels added for users in the wide table in real time. The operation in the system can be immediately queried, greatly improving the timeliness of label queries.

[0005] To achieve the above purpose, the present application adopts the following technical solutions:

[0006] The present application provides a method for converting business label data into a data warehouse wide table in real time based on FLink CDC, which includes:

[0007] The FLink CDC is used to capture the data changes of the user label relationship narrow table of the business system in real time, create a label field in the wide table in real time, and update the user and label value into the analytical database, thereby realizing the conversion of the label narrow table to the wide table.

[0008] Further, the analytic database uses Apache Doris.

[0009] Further, Mysql is used as the business system database.

[0010] Further, before capturing the data changes of the business system user label relationship narrow table in real time through FLink CDC, the method further comprises the following steps of: deploying a basic environment, configuring a basic server environment; deploying and starting an FLink cluster and an Apache Doris database; and starting a log binlog of the business system Mysql database.

[0011] Further, the method specifically comprises the following steps of:

[0012] Step 1, job configuration: developing an FLink CDC job, setting a job name; setting the configuration information of a source Mysql and a target Doris; setting the primary key field, the label name field and the label value field of the source table, and the primary key field of the target table; and setting optional configuration items: automatically generating a prefix name of a target column, and filtering conditions according to field values.

[0013] Step 2, creating an Flink streaming job environment;

[0014] Step 3, creating a Mysql data source according to the configuration in step 1, and setting the data source of the streaming job environment as the Mysql data source;

[0015] Step 4, creating a DorisTagSink object according to the configuration in step 1, setting the serialization format as json, and setting the target end of the streaming job environment as the DorisTagSink object;

[0016] Step 5, configuring a job name, and submitting the job to a deployed environment;

[0017] Step 6, calling a createWriter method of the DorisTagSink to create a DorisTagWriter object;

[0018] Step 7, the DorisTagWriter object calling a modifyColumnNameList method to obtain existing fields in a target Doris table and save the fields to a tagTableColNameList;

[0019] Step 8, reading data in a data source, and converting each piece of data into a json format according to the setting in step 4;

[0020] Step 9, judging whether the converted json data in step 8 meets the following conditions:

[0021] If the data status is new or historical data, then retrieve the values ​​of the primary key field, tag name field, and tag value field of the source table configured in step 1 from the JSON data, set the value of the prefix name + tag name field of the target column to the target table column name, and construct the JSON format of the sending data {target table primary key field: value of the primary key field of the source table, target table column name: value of the tag value field}.

[0022] If the data status is deleted, retrieve the values ​​of the primary key field and tag name field of the source table configured in step 1 from the JSON data, set the value of the prefix name + tag name field of the target column to the target table column name, and construct the JSON format of the sending data {target table primary key field: value of the primary key field of the source table, target table column name: ""}; where "" represents an empty string;

[0023] Step 10: Determine whether tagTableColNameList in Step 7 contains the target table column name in the generated data. If it does not exist, call the executeDdl method to add a column named after the target table column name and call the modifyColumnNameList method to update the tagTableColNameList field. If it exists, no processing is required.

[0024] Step 11: Write the data generated in step 9 into the cache by calling the writeRecord method;

[0025] Step 12: When the amount of data in the cache reaches the set threshold, the data is written to Doris.

[0026] Furthermore, the configuration information of the source MySQL includes the host IP, port number, database username and password, monitoring database name, and monitoring table name.

[0027] Furthermore, the configuration information of the target Doris includes the Doris connection address, Doris username and password, target database name and table name.

[0028] In another aspect, this invention proposes the application of any of the above-described methods for converting business tag data into a data warehouse wide table in the construction of an e-commerce website tag system.

[0029] This invention provides an electronic device comprising:

[0030] Processor; and

[0031] Memory for storing the executable instructions of the processor;

[0032] The processor is configured to execute any of the methods described above by executing the executable instructions.

[0033] The present invention provides a computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the method described in one of the above-mentioned aspects.

[0034] Compared with the prior art, the present invention has the following beneficial effects:

[0035] This invention converts business system tables into wide tables in the analysis database in real time. In scenarios with hundreds of millions of users and hundreds of thousands of tags, it can reduce the construction latency of the tag wide table from days to seconds. This allows business administrators to query tags created for users immediately, improving query speed, reducing database execution time, and saving computing resources. Attached Figure Description

[0036] Figure 1 This is a flowchart illustrating a method for converting business tag data into a data warehouse wide table in real time based on FLink CDC, according to an embodiment of the present invention. Detailed Implementation

[0037] The present invention will be further explained below with reference to the accompanying drawings and specific embodiments:

[0038] like Figure 1 As shown, a method for converting business tag data into a data warehouse wide table in real time based on FLink CDC includes:

[0039] Step 1, Job Configuration:

[0040] Step 1.1: Develop a FLink CDC job and set the job name;

[0041] Step 1.2: Configure the source MySQL settings, including host IP, port number, database username and password, monitored database name, and monitored table name.

[0042] Step 1.3: Set the configuration information for the target Doris, including the Doris connection address, Doris username and password, target database name and table name;

[0043] Step 1.4: Set the primary key field, tag name field, and tag value field of the source table, and the primary key field of the target table. Optional configuration options: automatically generate the prefix name of the target column, and filter conditions based on field values.

[0044] Step 2: Create a Flink streaming environment;

[0045] Step 3: Create a MySQL data source according to the configuration in Step 1, and set the data source of the streaming job environment to a MySQL data source;

[0046] Step 4: Create a DorisTagSink object according to the configuration in Step 1, set the serialization format to JSON, and set the target of the streaming job environment to the DorisTagSink object.

[0047] Step 5: Configure the job name and submit the job to the deployment environment;

[0048] Step 6: Call the createWriter method of DorisTagSink to create a DorisTagWriter object;

[0049] Step 7: The DorisTagWriter object created in Step 6 calls the modifyColumnNameList method to retrieve the existing fields in the target Doris table and save them to tagTableColNameList;

[0050] Step 8: Read the data from the data source and convert each piece of data into JSON format according to the settings in Step 4;

[0051] Step 9: Determine if the converted JSON data from Step 8 contains...

[0052] If the data status is new or historical data, then retrieve the values ​​of the primary key field, tag name field, and tag value field of the source table configured in step 1 from the JSON data, set the value of the prefix name + tag name field of the target column to the target table column name, and construct the JSON format of the sending data {target table primary key field: value of the primary key field of the source table, target table column name: value of the tag value field}.

[0053] If the data status is deleted, retrieve the values ​​of the primary key field and tag name field of the source table configured in step 1 from the JSON data, set the value of the prefix name + tag name field of the target column to the target table column name, and construct the JSON format of the sending data {target table primary key field: value of the primary key field of the source table, target table column name: ""}; where "" represents an empty string;

[0054] Step 10: Determine whether tagTableColNameList in Step 7 contains the target table column name in the generated data. If it does not exist, call the executeDdl method to add a column named after the target table column name and call the modifyColumnNameList method to update the tagTableColNameList field. If it exists, no processing is required.

[0055] Step 11: Write the data generated in step 9 into the cache by calling the writeRecord method;

[0056] Step 12: When the amount of data in the cache reaches the set threshold, the data is written to Doris.

[0057] Furthermore, prior to step 1, the basic environment deployment is also included:

[0058] Configure the basic server environment;

[0059] Deploy and start the FLink cluster and Apache Doris database;

[0060] Enable binlog in the MySQL database of the business system.

[0061] Specifically, this invention can be applied to the construction of a tagging system for e-commerce websites. Business personnel create tags such as highly educated individuals, travelers, pet owners, music lovers, and outdoor sports enthusiasts in the business system and tag individuals who meet the criteria. Since the system contains hundreds of millions of personnel information and tens of thousands of tags, directly querying the business system would cause significant performance problems for the database. However, tags generated offline based on scheduled tasks are only visible the next day. Using this invention, business personnel can immediately query the tag data they created and tagged, and can also query users or houses that have been tagged, greatly improving the system's timeliness. This allows for real-time recommendations of products that users might need based on tags, increasing the user's order rate.

[0062] Based on the above embodiments, the present invention also proposes an electronic device, comprising:

[0063] Processor; and

[0064] Memory for storing the executable instructions of the processor;

[0065] The processor is configured to execute any of the methods described above by executing the executable instructions.

[0066] Based on the above embodiments, the present invention also proposes a computer-readable storage medium having a computer program stored thereon, wherein the computer program, when executed by a processor, implements the method described in one of the above embodiments.

[0067] In summary, this invention enables the real-time conversion of traditional narrow tables of tag and user relationships into wide tables of data warehouse tags by monitoring database binlog jobs in real time. Tag columns can be dynamically added, and the wide table can support tens of thousands to hundreds of thousands of columns, which greatly improves the efficiency of system query and use, optimizes user experience, and increases order placement rate.

[0068] The above description is only a preferred embodiment of the present invention. It should be noted that those skilled in the art can make several improvements and modifications without departing from the principle of the present invention, and these improvements and modifications should also be considered within the scope of protection of the present invention.

Claims

1. A method for converting business tag data into a data warehouse wide table in real time based on FLink CDC, characterized in that, include: By capturing data changes in the narrow table of user tag relationships in the business system in real time through FLink CDC, tag fields are created in the wide table in real time, and user and tag values ​​are updated and stored in the analytical database, thereby realizing the transformation of the tag narrow table into a wide table; The method specifically includes: Step 1, Job Configuration: Develop a FLink CDC job and set the job name; set the configuration information for the source MySQL and target Doris tables; set the primary key field, tag name field, and tag value field of the source table, and the primary key field of the target table. Optional configuration items include: automatically generating the prefix name of the target column and filtering conditions based on field values. Step 2, create a Flink streaming job environment; Step 3: Create a MySQL data source according to the configuration in Step 1, and set the data source of the streaming job environment to a MySQL data source; Step 4: Create a DorisTagSink object according to the configuration in Step 1, set the serialization format to JSON, and set the target of the streaming job environment to the DorisTagSink object. Step 5: Configure the job name and submit the job to the deployment environment; Step 6: Call the createWriter method of DorisTagSink to create a DorisTagWriter object; Step 7: The DorisTagWriter object calls the modifyColumnNameList method to retrieve the existing fields in the target Doris table and save them to tagTableColNameList; Step 8: Read the data from the data source and convert each piece of data into JSON format according to the settings in Step 4; Step 9, determine the following in the converted JSON data from Step 8: If the data status is new or historical data, then retrieve the values ​​of the primary key field, tag name field, and tag value field of the source table configured in step 1 from the JSON data, set the value of the prefix name + tag name field of the target column to the target table column name, and construct the JSON format of the sending data {target table primary key field: value of the primary key field of the source table, target table column name: value of the tag value field}. If the data status is deleted, retrieve the values ​​of the primary key field and tag name field of the source table configured in step 1 from the JSON data, set the value of the prefix name + tag name field of the target column to the target table column name, and construct the JSON format for sending data: {target table primary key field: value of the primary key field of the source table, target table column name: ""}; where "" represents an empty string; Step 10: Determine whether tagTableColNameList in Step 7 contains the target table column name in the generated data. If it does not exist, call the executeDdl method to add a column named after the target table column name and call the modifyColumnNameList method to update the tagTableColNameList field. If it exists, no processing is required. Step 11: Write the data generated in step 9 into the cache by calling the writeRecord method; Step 12: When the amount of data in the cache reaches the set threshold, the data is written to Doris.

2. The method for real-time conversion of business tag data into a data warehouse wide table based on FLink CDC according to claim 1, characterized in that, The analytical database used is Apache Doris.

3. The method for real-time conversion of business tag data into a data warehouse wide table based on FLink CDC according to claim 1, characterized in that, MySQL is used as the database for the business system.

4. The method for real-time conversion of business tag data into a data warehouse wide table based on FLink CDC according to claim 1, characterized in that, Before capturing data changes in the narrow table of user tag relationships in the business system in real time through FLink CDC, the following steps are also included: basic environment deployment: configuring the basic server environment; deploying and starting the FLink cluster and Apache Doris database; and enabling the binlog of the business system's MySQL database.

5. The method for real-time conversion of business tag data into a data warehouse wide table based on FLink CDC according to claim 1, characterized in that, The configuration information of the source MySQL includes the host IP, port number, database username and password, monitoring database name, and monitoring table name.

6. The method for real-time conversion of business tag data into a data warehouse wide table based on FLink CDC according to claim 1, characterized in that, The configuration information of the target Doris includes the Doris connection address, Doris username and password, target database name and table name.

7. The application of the method for converting business tag data into a data warehouse wide table in real time as described in any one of claims 1-6 in the construction of an e-commerce website tag system.

8. An electronic device, characterized in that, include: processor; as well as Memory for storing the executable instructions of the processor; The processor is configured to execute the method of any one of claims 1-6 by executing the executable instructions.

9. A computer-readable storage medium having a computer program stored thereon, characterized in that, When the computer program is executed by a processor, it implements the method described in any one of claims 1-6.

Images