A design of a wide table with embedded points based on dynamic columns

Abstract

The present invention discloses a design of a tracking point wide table based on dynamic columns, and specifically relates to the field of column storage technology. A tracking point wide table structure of dynamic columns is designed, and a table of dynamic columns is created, and fixed columns are created in the table to store page and event information, and a dynamic column is designed for each private parameter to store the value of the private parameter. Successful tracking point data is collected and reported, and the data is reported to a background server in JSON format. After receiving the data, the background server parses the JSON format and synchronizes the data to a column storage database, creates a corresponding table structure in the column storage database, executes query statements to export the data as a file, and correctly maps the imported data to the corresponding column according to the table structure and field definition, designs an event analysis query interface, supports Chinese query conditions, and realizes the query function for male gender and specified order number conditions according to user needs, and returns the query result to the product through the interface.

Description

Technical Field

[0001] The present invention relates to the field of column storage technology, and more specifically, to a dynamic column-based point-burying wide table design. Background Art

[0002] At present, the Internet industry pays more and more attention to user behavior. Enterprises can analyze users' browsing and clicking habits through tracking points, strengthen the introduction of product user portraits, and provide users with interactive page event query and analysis capabilities. The tracking points refer to the recording of user behavior on apps and websites through technical means, and the events refer to the classification of users' browsing, clicking, and exposure behaviors.

[0003] After recording the user's behavior, design a specific storage table structure. Classify the reported tracking information into the first category of public parameters and the second category of private parameters. The parameters reported on each page are different from the order attributes reported on the list page.

[0004] The traditional method is to store event private parameters in JSON format, and to complete query filtering by parsing JSON when querying private parameters. When the amount of buried data is large, parsing JSON results in a slow response speed, and the key value memory cost is high when using private parameters. Summary of the invention

[0005] In order to overcome the above-mentioned defects of the prior art, an embodiment of the present invention provides a wide table design based on dynamic columns, which parses and stores the parameters in JSON through dynamic columns, realizes one-time parsing and multiple queries, so as to solve the problems raised in the above-mentioned background technology.

[0006] To achieve the above object, the present invention provides the following technical solution, a design of a dynamic column-based wide table for embedding points, which specifically includes the following steps:

[0007] Step 101: Design a dynamic column embedding wide table structure. Create a table of dynamic columns, create fixed columns in the table to store page and event information, and design a dynamic column for each private parameter to store the value of the private parameter.

[0008] Step 102: collect the successfully reported buried point data, report the data to the backend server in JSON format, and parse the JSON format after receiving the data, and store the key and value in the dynamic column table accordingly, so that the data can be correctly mapped to the corresponding column, wherein the key and value respectively represent the field name in the JSON data and the numerical type of each field in the JSON data;

[0009] Step 103: Synchronize the data to the column storage database, create a corresponding table structure in the column storage database, and execute a query statement to export the data into a file. According to the table structure and field definition, correctly map the imported data to the corresponding column and retain the integrity of the data.

[0010] Step 104: Design an event analysis query interface that supports Chinese query conditions. According to user needs, implement query functions for male gender and specified order number conditions, and return the query results to the product through the interface so that the product can perform data analysis and decision-making.

[0011] In a preferred embodiment, in step 101, a table of dynamic columns is created, including fixed columns and dynamic columns. The fixed columns include page names and event names. The dynamic columns represent dynamically generated columns according to the number of private parameters. Fixed columns are created in the table to store page and event information. At the same time, a dynamic column is designed for each private parameter to store the value of the private parameter and improve the readability of the data and the overall management efficiency. The specific steps are as follows:

[0012] Step A1, create a dynamic column tracking wide table: determine the fixed columns to be stored, including page name and event name, and create corresponding fixed columns in the table, represented as page_name and event_name, where page_name represents the page name fixed column, and event_name represents the event name fixed column. By storing parameter information of different events in the same table, the data can be integrated and analyzed;

[0013] Step A2, design dynamic columns: use the background page to collect the private parameters of each event, and dynamically generate a corresponding number of columns according to the number of private parameters to store the values ​​of the private parameters. If the number of private parameters is n, then create n dynamic columns, represented as (param_1, param_2, ..., param_n), where param_n represents the nth dynamic column;

[0014] Step A3, storing data: when a new event occurs, fill the information in the fixed column, store the page name in the page_name column, store the event name in the event_name column, store the values ​​of the private parameters in the corresponding dynamic columns in order, store the value of the first private parameter in the param_1 column, store the value of the second private parameter in the param_2 column, and so on. According to pre-defined rules, the private parameters are stored in the MySQL database. The page name and event name exist as fixed columns, which are used to store the page where each event occurs and the name of the event.

[0015] In a preferred embodiment, in step 102, when the user's behavior data is reported, the data is reported to the backend server in JSON format. After receiving the data, the backend server parses the JSON format and stores the key and value in a dynamic column table accordingly, so that the data can be correctly mapped to the corresponding column, realizing automated data processing and improving the efficiency of data processing. The specific steps are as follows:

[0016] Step B1, define JSON data format: according to the reported data content, design a data structure that conforms to the JSON format, including fixed fields and private parameter fields. The fixed fields are used to store basic information, and the private parameter fields are used to store specific parameter values, which is conducive to subsequent data processing and parsing;

[0017] Step B2, reporting user behavior data: In the client code, trigger the corresponding tracking event according to the user behavior, organize the data to be reported in JSON format, and use HTTP request to send the JSON data to the specified interface of the backend server to ensure that the data can be successfully transmitted. The tracking event means recording the user's behavior on the APP and website through technical means, including the page stay time and the number of button clicks;

[0018] Step B3, the backend server receives and parses data: After receiving the data in JSON format, the backend server performs a parsing operation, converts the JSON data into an object, and extracts the page name, event name, and key-value pairs of private parameters in the JSON data. After extracting the page name and event name information, the backend server monitors user behavior in real time, and performs alarms and exception processing based on specific event names;

[0019] Step B4, store data in a dynamic column table: according to the received data content, store the page name in the corresponding page_name column, store the event name in the event_name column, traverse the key-value pairs of the private parameters, find the corresponding dynamic column according to the key name, and store the value in the column, store the basic information of the page name and event name in the corresponding column, realize flexible processing of data storage, dynamically map different private parameters to different columns, and make data storage more flexible and intelligent.

[0020] In a preferred embodiment, in step 103, by regularly synchronizing the data in MySQL to the column storage database, it is ensured that the data is available for subsequent analysis and query, the speed of data analysis and query is accelerated, the utilization of storage space is further optimized, and the storage cost is reduced. The specific steps are as follows:

[0021] Step C1, data table design: Create a table structure corresponding to the MySQL table in the column storage database, including the definition of fixed columns and dynamic columns. Fixed columns are used to store regular field data, and dynamic columns are used to flexibly store various private parameters to improve data flexibility and scalability.

[0022] Step C2, data export: Use a programming language to connect to the MySQL database, execute query statements to export data as files, and export the MySQL data as corresponding data files according to the table structure, which can be recognized and imported by the column storage database. By exporting the data files according to the table structure, it is ensured that the exported data files are compatible with the data format of the column storage database, so that the data migration and import process between different systems can proceed smoothly, avoiding errors caused by inconsistent data formats;

[0023] Step C3, data import: Use the API provided by the column storage database to import the exported data file into the column storage database. According to the table structure and field definition, correctly map the imported data to the corresponding columns and retain the integrity of the data. By directly mapping the data to the corresponding columns, the data import speed is accelerated and the data processing efficiency is improved.

[0024] Step C4, regular synchronization: During each synchronization, the latest data is exported from MySQL and imported into the column storage database. By importing the latest data into the column storage database, its optimized data storage structure and query engine are used to improve query performance, reduce the concurrent query pressure on the MySQL database, improve the concurrent processing capability of the overall system, and speed up data query.

[0025] In a preferred embodiment, in step 104, an interface is designed for querying and analyzing event data, supporting Chinese query conditions, and realizing query functions for male gender and specified order number conditions according to user needs, and being able to query the number of times and number of people indicators, and returning the query results to the product through the interface, obtaining data that meets the conditions through the interface, conducting in-depth analysis and comparison, understanding user behavior and trends, and supporting product data-driven decision-making, and the specific steps are as follows:

[0026] Step D1, design interface: define the query condition parameter format and request method of the interface, select to return data in JSON format according to the data format returned by the interface, including the number of times and number of people indicator information, and query data through the interface, saving the time of manual query and data sorting, and improving data processing efficiency;

[0027] Step D2, query condition analysis: In the interface processing logic, analyze the query conditions passed in by the user, including gender and order number conditions, and construct corresponding query statements according to the conditions to retrieve qualified data from the database. By analyzing the query conditions, the database query process is optimized, the data retrieval speed is accelerated, and the system response performance is improved;

[0028] Step D3, data query: use SQL statements to filter and aggregate data in combination with conditions, perform database query operations, extract relevant data from the data table based on the conditions of male gender and specified order number, obtain the number and number of people indicator statistics, use aggregate functions to count the number of data items that meet the conditions, and perform statistical analysis on the filtered data;

[0029] Step D4, processing the query results: serializing the query results including the number of times and the number of people index information into JSON format, and returning them to the product end through the interface. The data in JSON format is returned through the interface to achieve unified standardization of the data returned by the interface, reduce the size of data transmission, and improve data transmission efficiency;

[0030] Step D5, interface return: the processed query result is returned to the product end as response data, and when the interface returns, it includes a status code and message prompt information so that the product end can correctly parse and process the returned data.

[0031] The beneficial effects of the present invention are as follows: a wide table structure of dynamic column tracking is designed, a table of dynamic columns is created, fixed columns are created in the table for storing page and event information, a dynamic column is designed for each private parameter to store the value of the private parameter, the successfully reported tracking data is collected and reported to the background server in JSON format, the background server parses the JSON format after receiving the data, and synchronizes the data to a column storage database, a corresponding table structure is created in the column storage database, and a query statement is executed to export the data as a file, according to the table structure and field definition, the imported data is correctly mapped to the corresponding column, an event analysis query interface is designed to support Chinese query conditions, and according to user needs, a query function for male gender and specified order number conditions is implemented, and the query results are returned to the product through the interface, and the Chinese names of the private parameters of the event tracking are recorded, and the user inputs the Chinese value to implement query analysis, thereby improving the user's experience when querying the tracking points. BRIEF DESCRIPTION OF THE DRAWINGS

[0032] Figure 1 The figure is a flow chart of the method of the present invention. DETAILED DESCRIPTION

[0033] The following will be combined with the drawings in the embodiments of the present application to clearly and completely describe the technical solutions in the embodiments of the present application. Obviously, the described embodiments are only part of the embodiments of the present application, not all of the embodiments. Based on the embodiments in the present application, all other embodiments obtained by those skilled in the art without creative work are within the scope of protection of this application.

[0034] In the description of this application, the terms "first" and "second" are used for descriptive purposes only and should not be understood as indicating or implying relative importance or implicitly indicating the number of technical features indicated. Thus, a feature defined as "first" or "second" may explicitly or implicitly include one or more of the features. In the description of this application, "plurality" means two or more, unless otherwise clearly and specifically defined.

[0035] In the description of the present application, the term "for example" is used to mean "used as an example, illustration or description". Any embodiment described as "for example" in the present application is not necessarily to be construed as being more preferred or advantageous than other embodiments. The following description is given to enable any technician in the field to implement and use the present invention. In the following description, details are listed for the purpose of explanation. It should be understood that a person of ordinary skill in the art can recognize that the present invention can be implemented without using these specific details. In other examples, well-known structures and processes will not be elaborated in detail to avoid unnecessary details to obscure the description of the present invention. Therefore, the present invention is not intended to be limited to the embodiments shown, but is consistent with the widest scope consistent with the principles and features disclosed in the present application.

[0036] Example 1

[0037] This embodiment provides Figure 1 The design of a dynamic column-based wide table for embedding points specifically includes the following steps:

[0038] Step 101: Design a dynamic column embedding wide table structure. Create a table of dynamic columns, create fixed columns in the table to store page and event information, and design a dynamic column for each private parameter to store the value of the private parameter.

[0039] Furthermore, a dynamic column table is created, which includes fixed columns and dynamic columns. The fixed columns include page names and event names. The dynamic columns represent dynamically generated columns according to the number of private parameters. Fixed columns are created in the table to store page and event information. At the same time, a dynamic column is designed for each private parameter to store the value of the private parameter. The specific steps are as follows:

[0040] Step A1, create a dynamic column tracking wide table: determine the stored fixed columns, including page name and event name, and create corresponding fixed columns in the table, represented as page_name and event_name, where page_name represents the page name fixed column, and event_name represents the event name fixed column;

[0041] Step A2, design dynamic columns: use the background page to collect the private parameters of each event, and dynamically generate a corresponding number of columns according to the number of private parameters to store the values ​​of the private parameters. If the number of private parameters is n, then create n dynamic columns, represented as (param_1, param_2, ..., param_n), where param_n represents the nth dynamic column;

[0042] Step A3, storing data: When a new event occurs, fill the information in the fixed column, store the page name in the page_name column, store the event name in the event_name column, store the values ​​of the private parameters in the corresponding dynamic columns in order, store the value of the first private parameter in the param_1 column, store the value of the second private parameter in the param_2 column, and so on. Store the private parameters in the MySQL database according to the pre-defined rules.

[0043] Step 102: collect the successfully reported buried point data, report the data to the backend server in JSON format, and parse the JSON format after receiving the data, and store the key and value in the dynamic column table accordingly, so that the data can be correctly mapped to the corresponding column, wherein the key and value respectively represent the field name in the JSON data and the numerical type of each field in the JSON data;

[0044] Furthermore, when the user's behavior data is reported, the data is reported to the backend server in JSON format. After receiving the data, the backend server parses the JSON format and stores the key and value in the dynamic column table accordingly, so that the data can be correctly mapped to the corresponding column. The specific steps are as follows:

[0045] Step B1, define JSON data format: according to the reported data content, design a data structure that conforms to the JSON format, including fixed fields and private parameter fields;

[0046] Step B2, user behavior data reporting: In the client code, trigger the corresponding tracking event according to the user behavior, organize the data to be reported in JSON format, and use HTTP request to send the JSON data to the specified interface of the backend server to ensure that the data can be successfully transmitted;

[0047] Step B3, the backend server receives and parses data: After receiving the data in JSON format, the backend server performs a parsing operation, converts the JSON data into an object, and extracts the page name, event name, and key-value pairs of private parameters in the JSON data;

[0048] Step B4, store data into a dynamic column table: according to the received data content, store the page name into the corresponding page_name column, store the event name into the event_name column, traverse the key-value pairs of the private parameters, find the corresponding dynamic column according to the key name, and store the value into the column.

[0049] Step 103: Synchronize the data to the column storage database, create a corresponding table structure in the column storage database, and execute a query statement to export the data into a file. According to the table structure and field definition, correctly map the imported data to the corresponding column and retain the integrity of the data.

[0050] Furthermore, by regularly synchronizing the data in MySQL to the column storage database, ensure that the data is available for subsequent analysis and query. The specific steps are as follows:

[0051] Step C1, data table design: create a table structure corresponding to the MySQL table in the column storage database, including the definition of fixed columns and dynamic columns;

[0052] Step C2, data export: Use a programming language to connect to the MySQL database, execute query statements to export data into files, and export the MySQL data into corresponding data files according to the table structure, which can be recognized and imported by the column storage database;

[0053] Step C3, data import: Use the API provided by the column storage database to import the exported data file into the column storage database, correctly map the imported data to the corresponding column according to the table structure and field definition, and retain the integrity of the data;

[0054] Step C4, regular synchronization: During each synchronization, the latest data is exported from MySQL and imported into the column storage database to ensure that the data is updated in a timely manner.

[0055] Step 104: Design an event analysis query interface to support Chinese query conditions. According to user needs, implement the query function for male gender and specified order number conditions, and return the query results to the product through the interface so that the product can perform data analysis and decision-making;

[0056] Furthermore, an interface is designed for querying and analyzing event data, supporting Chinese query conditions. According to user needs, the query function for male gender and specified order number conditions is implemented, and the number and number of people indicators can be queried. The query results are returned to the product through the interface so that the product can conduct further data analysis and decision-making. The specific steps are as follows:

[0057] Step D1, design interface: define the query condition parameter format and request method of the interface, and select to return data in JSON format according to the data format returned by the interface, including the number of times and number of people indicator information;

[0058] Step D2, query condition analysis: In the interface processing logic, analyze the query conditions passed in by the user, including gender and order number conditions, and construct corresponding query statements based on the conditions to retrieve data that meets the conditions from the database;

[0059] Step D3, data query: Use SQL statements to filter and aggregate data in combination with conditions, perform database query operations, extract relevant data from the data table based on the conditions of male gender and specified order number, and obtain statistical information on the number of times and number of people;

[0060] Step D4, process the query results: serialize the query results including the number of times and the number of people indicator information into JSON format, and return it to the product end through the interface;

[0061] Step D5, interface return: the processed query result is returned to the product end as response data, and when the interface returns, it includes a status code and message prompt information so that the product end can correctly parse and process the returned data.

[0062] It should be noted that in the above embodiments, the description of each embodiment has its own emphasis, and for parts that are not described in detail in a certain embodiment, reference can be made to the relevant descriptions of other embodiments.

[0063] Those skilled in the art will appreciate that embodiments of the present invention may be provided as methods, systems, or computer program products. Therefore, the present invention may take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware. Moreover, the present invention may take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0064] The present invention is described with reference to flowcharts and / or block diagrams of methods, devices (systems), and computer program products according to embodiments of the present invention. It should be understood that each process and / or block in the flowchart and / or block diagram, as well as the combination of processes and / or blocks in the flowchart and / or block diagram, can be implemented by computer program instructions. These computer program instructions can be provided to a processor of a general-purpose computer, a special-purpose computer, an embedded computer, or other programmable data processing device to generate a machine, so that the instructions executed by the processor of the computer or other programmable data processing device generate instructions for implementing the processes in the flowchart and / or block diagram. Figure 1 A process or multiple processes and / or boxes Figure 1 A device that provides the functions specified in a block or multiple blocks.

[0065] These computer program instructions may also be stored in a computer-readable memory capable of directing a computer or other programmable data processing device to operate in a specific manner, so that the instructions stored in the computer-readable memory produce an article of manufacture including an instruction device, which implements the process Figure 1 A process or multiple processes and / or boxes Figure 1 A function specified in one or more boxes.

[0066] These computer program instructions can also be loaded onto a computer or other programmable data processing device so that a series of operating steps are executed on the computer or other programmable device to produce a computer-implemented process, thereby providing instructions for implementing the process. Figure 1 A process or multiple processes and / or boxes Figure 1 The steps for the functions specified in one or more boxes.

[0067] Although the preferred embodiments of the present invention have been described, those skilled in the art may make additional changes and modifications to these embodiments once they have learned the basic creative concept. Therefore, the appended claims are intended to be interpreted as including the preferred embodiments and all changes and modifications that fall within the scope of the present invention.

[0068] Obviously, those skilled in the art can make various changes and modifications to the present invention without departing from the spirit and scope of the present invention. Thus, if these modifications and variations of the present invention fall within the scope of the claims of the present invention and their equivalents, the present invention is also intended to include these modifications and variations.

Claims

1. A design of a wide table for embedding points based on dynamic columns, characterized by: The specific steps include: Step 101: Design a dynamic column embedding wide table structure. Create a table of dynamic columns, create fixed columns in the table to store page and event information, and design a dynamic column for each private parameter to store the value of the private parameter. Step 102: collect the successfully reported buried point data, report the data to the backend server in JSON format, and parse the JSON format after receiving the data, and store the key and value in the dynamic column table accordingly, so that the data can be correctly mapped to the corresponding column, wherein the key and value respectively represent the field name in the JSON data and the numerical type of each field in the JSON data; Step 103: Synchronize the data to the column storage database, create a corresponding table structure in the column storage database, and execute a query statement to export the data into a file. According to the table structure and field definition, correctly map the imported data to the corresponding column and retain the integrity of the data. Step 104: Design an event analysis query interface that supports Chinese query conditions. According to user needs, implement query functions for male gender and specified order number conditions, and return the query results to the product through the interface so that the product can perform data analysis and decision-making.

2. According to claim 1, a dynamic column-based point embedding wide table design is characterized by: In step 101, a table of dynamic columns is created, and a dynamic column is designed for each private parameter to store the value of the private parameter. The specific steps are as follows: Step A1, create a dynamic column tracking wide table: determine the stored fixed columns, including page name and event name, and create corresponding fixed columns in the table, represented as page_name and event_name, where page_name represents the page name fixed column, and event_name represents the event name fixed column; Step A2, design dynamic columns: use the background page to collect the private parameters of each event, and dynamically generate a corresponding number of columns according to the number of private parameters to store the values ​​of the private parameters. If the number of private parameters is n, then create n dynamic columns, represented as (param_1, param_2, ..., param_n), where param_n represents the nth dynamic column; Step A3, storing data: When a new event occurs, fill the information in the fixed column, store the value of the private parameter in the corresponding dynamic column in order, and store the private parameter in the MySQL database according to the pre-defined rules.

3. According to the design of a dynamic column-based wide table for embedding points in claim 1, it is characterized by: In step 102, when the user's behavior data is reported, the data is reported to the backend server in JSON format. After receiving the data, the backend server parses the JSON format. The specific steps are as follows: Step B1, define JSON data format: design a data structure that conforms to the JSON format according to the reported data content; Step B2, reporting user behavior data: In the client code, trigger the corresponding tracking event according to the user behavior, organize the data to be reported in JSON format, and use HTTP request to send the JSON data to the specified interface of the backend server; Step B3, the backend server receives and parses data: After receiving the data in JSON format, the backend server performs a parsing operation, converts the JSON data into an object, and extracts the page name, event name, and key-value pairs of private parameters in the JSON data; Step B4, store data into a dynamic column table: store the page name and event name into the corresponding dynamic column according to the received data content, traverse the key-value pairs of the private parameters, find the corresponding dynamic column according to the key name, and store the value into the column.

4. According to claim 1, a dynamic column-based point embedding wide table design is characterized by: In step 103, the data in MySQL is periodically synchronized to the column storage database to ensure that the data is available for subsequent analysis and query. The specific steps are as follows: Step C1, data table design: create a table structure corresponding to the MySQL table in the column storage database; Step C2, data export: Use a programming language to connect to the MySQL database, execute query statements to export data into files, and export the MySQL data into corresponding data files according to the table structure; Step C3, data import: Use the API provided by the column storage database to import the exported data file into the column storage database, and correctly map the imported data to the corresponding columns according to the table structure and field definition; Step C4, regular synchronization: During each synchronization, the latest data is exported from MySQL, and the exported data is imported into the column storage database.

5. According to claim 1, a dynamic column-based point embedding wide table design is characterized by: In step 104, an interface is designed for querying and analyzing event data, supporting Chinese query conditions, and implementing query functions for male gender and specified order number conditions according to user needs, and returning the query results to the product through the interface. The specific steps are as follows: Step D1, design interface: define the query condition parameter format and request method of the interface, and select to return data in JSON format according to the data format returned by the interface, including the number of times and number of people indicator information; Step D2, query condition analysis: In the interface processing logic, analyze the query condition passed in by the user, and construct a corresponding query statement according to the condition, so as to retrieve the data that meets the condition from the database; Step D3, data query: Use SQL statements to filter and aggregate data in combination with conditions, perform database query operations, extract relevant data from the data table based on the conditions of male gender and specified order number, and obtain statistical information on the number of times and number of people; Step D4, process the query results: serialize the query results including the number of times and the number of people indicator information into JSON format, and return it to the product end through the interface; Step D5, interface return: return the processed query result as response data to the product end so that the product end can correctly parse and process the returned data.

Images