SQL-Based APP Traffic Data Processing Method, Device, Equipment and Storage Medium

Abstract

The present invention relates to the field of buried point tracking technology, and discloses an SQL-based APP traffic data processing method, device, equipment and storage medium. The method includes: after receiving an APP traffic data processing instruction, splicing a row of executable SQL from the relationship mapping table of the APP according to a first objective function, and writing the row of executable SQL to a preset remote storage path in a distributed file system; converting the row of executable SQL stored in the preset remote storage path into local variables according to a target command, and after reading the local variables, obtaining and executing the row of executable SQL, and storing the execution result in a data warehouse tool; converting the execution result corresponding to the row of executable SQL stored in the data warehouse tool into multiple rows of APP traffic statistics results according to the side view of the data warehouse tool and a second objective function. The APP traffic statistics results of the present invention can accurately reflect the user's behavior trajectory and habits, and at the same time are quickly developable, highly scalable and extensible.

Description

Technical Field

[0001] The present invention relates to the technical field of data collection and buried point tracking, and particularly discloses a method, device, equipment and storage medium for processing APP traffic data based on SQL. Background Art

[0002] A heat map is a simple and effective way to analyze the interactions and user behaviors of APP (Application) visitors. Through the heat map, various indicators such as APP clicks, views, reach rates, and attention can be reflected, helping users analyze the user behavior trajectories, so as to enable users to insight into the current market trends and timely formulate targeted business decision-making bases according to the market trends. At the same time, it can improve the user experience of the APP, and then increase the APP user registration volume, daily active volume, retention rate, customer acquisition conversion rate, etc.

[0003] Currently, the computing frameworks for heat map data analysis based on big data include mapreduce, spark, flink, etc. These computing frameworks have relatively high requirements for language coding levels such as java and scala. Most of the existing data analysis technologies are based on Hive to build a hadoop data warehouse and are mainly written in SQL language. Therefore, for most data analysis developers, if they want to use the above computing frameworks, there will be a problem of mismatch with the underlying engine coding. As a result, there will also be problems such as high data analysis technology thresholds and high development and maintenance costs, and thus may abandon the development of HIVE, losing the advantages of HIVE such as fast development, strong scalability, and high extensibility. Therefore, those skilled in the art urgently need to find a new technical solution to solve the technical problem of how to match HIVE and the computing framework. Summary of the Invention

[0004] Based on this, in view of the above technical problems, it is necessary to provide a method, device, equipment and storage medium for processing APP traffic data based on SQL. The executable SQL developed and spliced based on SQL in the present invention can run on each computing framework, has strong applicability to the computing framework, and its APP traffic statistical results can accurately reflect the user behavior trajectories and habits, while retaining the advantages of the data warehouse HIVE such as fast development, strong scalability, and high extensibility.

[0005] A method for processing APP traffic data based on SQL includes:

[0006] After receiving an APP traffic data processing instruction, obtaining the relationship mapping table of the APP; the relationship mapping table represents the mapping relationship between the APP buried point conditions of the APP and the heat map;

[0007] Concatenate an executable SQL line from the relationship mapping table according to the first objective function, and write the executable SQL line to a preset remote storage path in the distributed file system;

[0008] Convert the executable SQL line stored in the preset remote storage path into a local variable according to the target command, and after reading the local variable, obtain and execute the executable SQL line, and store the execution result after executing the executable SQL line in the data warehouse tool;

[0009] Convert the execution result corresponding to the executable SQL line stored in the data warehouse tool into multiple lines of APP traffic statistics results according to the side view of the data warehouse tool and the second objective function, and synchronously display the multiple lines of APP traffic statistics results on the front-end page.

[0010] An APP traffic data processing device based on SQL, comprising:

[0011] An acquisition module, configured to acquire the relationship mapping table of the APP after receiving the traffic data processing instruction of the APP; the relationship mapping table represents the mapping relationship between the APP buried point conditions of the APP and the heat map;

[0012] A writing module, configured to concatenate an executable SQL line from the relationship mapping table according to the first objective function, and write the executable SQL line to a preset remote storage path in the distributed file system;

[0013] A storage module, configured to convert the executable SQL line stored in the preset remote storage path into a local variable according to the target command, and after reading the local variable, obtain and execute the executable SQL line, and store the execution result after executing the executable SQL line in the data warehouse tool;

[0014] A synchronization module, configured to convert the execution result corresponding to the executable SQL line stored in the data warehouse tool into multiple lines of APP traffic statistics results according to the side view of the data warehouse tool and the second objective function, and synchronously display the multiple lines of APP traffic statistics results on the front-end page.

[0015] A computer device, comprising a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein when the processor executes the computer program, the above-mentioned APP traffic data processing method based on SQL is implemented.

[0016] A computer-readable storage medium stores a computer program, and when the computer program is executed by a processor, the above-mentioned APP traffic data processing method based on SQL is implemented.

[0017] The above-mentioned method, device, equipment and storage medium for processing APP traffic data based on SQL, after receiving the traffic data processing instruction of the APP, obtain the relationship mapping table of the APP; the relationship mapping table represents the mapping relationship between the APP buried point conditions of the APP and the heat map; splice a row of executable SQL from the relationship mapping table according to the first objective function, and write the row of executable SQL to a preset remote storage path in the distributed file system; convert the row of executable SQL stored in the preset remote storage path into a local variable according to the target command, and after reading the local variable, obtain and execute the row of executable SQL, and store the execution result after executing the row of executable SQL into the data warehouse tool; convert the execution result corresponding to the row of executable SQL stored in the data warehouse tool into multiple rows of APP traffic statistics results according to the side view of the data warehouse tool and the second objective function, and synchronously display the multiple rows of APP traffic statistics results on the front-end page; on the one hand, the APP traffic statistics results can accurately reflect the user's behavior trajectory and user habits, and users can take targeted measures according to the accurate statistics results to improve the user experience effect; on the other hand, the APP traffic statistics results are developed based on SQL, and there are SQL modules available for development using SQL in the three popular computing frameworks mapreduce, spark and flink in the current market. Therefore, the executable SQL completed by splicing based on SQL in the present invention can run on each computing framework and obtain APP traffic statistics results that meet the statistical requirements; at the same time, because the present invention is developed based on SQL, it can be better connected to the data warehouse HIVE through SQL, is suitable for the development languages used by most developers, reduces the manual development conditions, and at the same time retains the advantages of using the data warehouse HIVE before, such as fast development, strong scalability and high extensibility. Description of the Drawings

[0018] In order to more clearly illustrate the technical solutions of the embodiments of the present invention, the following will briefly introduce the drawings required for the description of the embodiments of the present invention. Obviously, the following drawings are only some embodiments of the present invention. For those of ordinary skill in the art, without creative efforts, other drawings can also be obtained based on these drawings.

[0019] Figure 1 It is a schematic diagram of an application environment of a method for processing APP traffic data based on SQL in an embodiment of the present invention;

[0020] Figure 2 It is a flowchart of a method for processing APP traffic data based on SQL in an embodiment of the present invention;

[0021] Figure 3 It is a schematic structural diagram of a SQL-based APP traffic data processing device in an embodiment of the present invention;

[0022] Figure 4 It is a schematic diagram of a computer device in an embodiment of the present invention. Specific embodiments

[0023] Next, the technical solutions in the embodiments of the present invention will be clearly and completely described in conjunction with the accompanying drawings in the embodiments of the present invention. Obviously, the described embodiments are part of the embodiments of the present invention, rather than all the embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those of ordinary skill in the art without creative efforts shall fall within the protection scope of the present invention.

[0024] A SQL-based APP traffic data processing method provided by the present invention can be applied in an application environment such as Figure 1 where the client communicates with the server through the network. Among them, the client may include, but is not limited to, various personal computers, laptop computers, smart phones, tablet computers, and portable wearable devices.

[0025] In an embodiment, as Figure 2 shown, a SQL-based APP traffic data processing method is provided. Taking the server in Figure 1 as an example, the method includes the following steps:

[0026] S10, after receiving the traffic data processing instruction of the APP, obtain the relationship mapping table of the APP; the relationship mapping table represents the mapping relationship between the APP buried point conditions and the heat map of the APP;

[0027] Understandably, data logging is mainly related to the technologies and implementation processes for capturing, processing, and sending specific user behaviors or events. Among them, the data logging conditions of the APP can be reflected by data logging events, and the data logging events can be the page stay duration, click events (data will be recorded once when the user clicks a button on the front-end page), and exposure time (data will be recorded once when the user enters a front-end page, and data will also be recorded once when the page is refreshed). The heat map is a diagram that clearly shows the user's access to the front-end page. The relationship mapping table between the APP data logging conditions and the heat map is a multi-line relationship table pre-configured successfully by the user (each row corresponds to a unique data logging ID). Specifically, the user pre-logs at each page position in multiple front-end pages, that is, associates the data logging ID with each page position in the front-end page. Among them, the data logging ID corresponds to the APP data logging conditions, and the APP data logging conditions can reflect the triggering situation of the data logging events. The triggering situation can be recorded in the form of data, and this data can be used to generate a heat map. For example, the relationship mapping table between the APP data logging conditions and the heat map can be that the page name corresponds to the data logging ID. The "Home Page" of the front-end page corresponds to pv00, and "Mine" corresponds to pv01. If the "Home Page" and "Mine" are clicked by the user, the APP data logging conditions corresponding to the data logging ID are triggered, and the system can record the number of times the "Home Page" and "Mine" are triggered and generate the corresponding heat map. In this embodiment, the APP traffic can be statistically analyzed through the relationship mapping table. Among them, the sources of APP traffic include but are not limited to new users, active users, active accounts, daily, weekly, and monthly active users, etc. Through the statistical results, the user's behavior trajectory and browsing habits can be analyzed, etc.

[0028] S20. Concatenate a row of executable SQL from the relationship mapping table according to the first objective function, and write the row of executable SQL to a preset remote storage path in the distributed file system;

[0029] Understandably, the preset storage path is a specified remote storage path in the HDFS directory. Here, HDFS is a distributed file system, and this distributed file system is Hadoop, which refers to a distributed file system designed to run on general-purpose hardware. The first objective function is a UDAF function. In the embodiment, the relationship mapping table of multiple data rows is converted into a data output row (that is, a line of executable SQL). Therefore, the first objective function - the UDAF function used is the aggregation function UDAF. Specifically, the process of concatenating a line of executable SQL is as follows: First, select the buried point ID (such as buried point ID - a) from the relationship mapping table through the select in the SQL statement. Then, take the sum of the field data corresponding to another buried point ID (such as buried point ID - b) as the page view volume, and when the sum of the field data is greater than the preset data threshold, take the sum of the field data greater than the preset data threshold as the unique visitor volume. Then, select the buried point ID from the relationship mapping table through the select in the SQL statement. After performing the select count(1) operation on this buried point ID, determine the sum of the field data, and at the same time define various situations of the data, such as from the relationship mapping table, where time conditions, and group by fields or buried point IDs. Finally, splice the field data corresponding to the above buried point ID with the field data corresponding to another buried point ID (such as adding the field data corresponding to buried point ID - a to the left of the field data corresponding to another buried point ID - b, and at the same time all the corresponding field data have been defined). Specifically, a line of executable SQL spliced can include:

[0030] select a.id_name, sum(b.cnt) as pv, sum(if(b.cnt>0, 1, 0)) as uv

[0031] from relationship mapping table M a

[0032] left join(

[0033] select id_str, phone_no, count(1) as cnt

[0034] From buried point table T

[0035] where day = '2021-08-13'

[0036] group by id_str, phone_no) b

[0037] on a.id_str = b.id_str

[0038] group by a.id_name。

[0039] S30. Convert the executable SQL line stored in the preset remote storage path into local variables according to the target command. After reading the local variables, obtain and execute the executable SQL line, and store the execution result after executing the executable SQL line in the data warehouse tool.

[0040] Understandably, the target command is a Hadoop command. The Hadoop command is associated with the above-mentioned distributed file system Hadoop. This command is a Hadoop Shell command, and the local variables are created by the Shell command. Local variables are variables that can only be used in the scripts during the lifetime of the user's current shell. Local variables become invalid as the shell script process dies. Among them, this local variable is a variable of Linux. When entering the system, the local variables are read through Linux (this environment variable can determine the preset remote storage path of an executable SQL line) to obtain an executable SQL line through the local variables and execute the executable SQL line. The data warehouse tool is Hive.

[0041] S40. Convert the execution result corresponding to the executable SQL line stored in the data warehouse tool into multiple lines of APP traffic statistics results according to the side view of the data warehouse tool and the second target function, and synchronously display the multiple lines of APP traffic statistics results on the front-end page.

[0042] Understandably, the second target function is a UDTF function; the side view of the data warehouse tool is the LateralView of Hive. Among them, the side view Lateral View and the second target function - UDTF function are used together to connect the execution result corresponding to an executable SQL line and the data output by the UDTF function to generate a new virtual table (multiple lines of APP traffic statistics results, including but not limited to page views, unique visitors, and daily user visits). For example, if the execution result corresponding to an executable SQL line is 1, 2, 3, it becomes The synchronous display uses a data transfer tool - Sqoop tool. Through this tool, the data in the data warehouse tool Hive can be converted into Hadoop data, where the Hadoop data is used to be displayed on the front-end page, and the displayed data is already the statistically completed data (multiple lines of APP traffic statistics results).

[0043] In the embodiments of steps S10 to S40, on the one hand, the APP traffic statistics results can accurately reflect the user's behavior trajectory and user habits. The user can take targeted measures based on the accurate statistics results to improve the user experience effect. On the other hand, the APP traffic statistics results are developed based on SQL, and there are SQL modules available for development with SQL in the three popular computing frameworks in the market, namely mapreduce, spark, and flink. Therefore, the executable SQL developed and spliced based on SQL in the present invention can run on each computing framework and obtain the APP traffic statistics results that meet the statistical requirements. At the same time, since the present invention is developed based on SQL, it can be better connected to the data warehouse HIVE through SQL, is suitable for the development languages used by most developers, reduces the manual development conditions, and retains the advantages of using the data warehouse HIVE before, such as fast development, strong scalability, and high extensibility.

[0044] Further, before obtaining the relationship mapping table of the APP, it further includes:

[0045] Preset the page position of the target in the front-end page to set the APP buried point condition;

[0046] Set the data analysis condition of the heat map for the APP buried point condition, and set the relationship mapping table between the APP buried point condition and the heat map according to the data analysis method of the heat map.

[0047] Understandably, there are multiple preset target page positions for buried points in the front-end page. Each button in the front-end page can be used as a preset target page position. For example, the buttons in the front-end page are "Home Page", "About Us", and "Mine", etc. Among them, one preset target page position corresponds to one buried point, and this buried point has a unique buried point ID, such as pv00, pv01, etc.; the APP buried point condition means that the buried point with a unique buried point ID corresponding to the above preset target page position is triggered. The triggering of the buried point includes button clicks, the user successfully entering another page, the user's stay duration on the page, etc.; the data analysis condition is the way to generate the heat map, including random sampling and using all points. Since all the preset target page positions in the front-end page are buried points, and each time the APP buried point condition corresponding to the buried point is triggered, the data of the preset target page position corresponding to this buried point can be recorded and used as the data source for rendering the heat map (the heat map contains all page positions in the front-end page, and when the preset target page position is triggered, the preset target page position in the heat map will be presented in a highlighted form). At the same time, the corresponding heat map is generated using the above data analysis conditions; in this embodiment, the corresponding relationship mapping table is pre-configured, and the buried point conditions of the APP can be set based on the preset statistical requirement rules to generate data that meets the statistical requirements.

[0048] Further, converting the line of executable SQL stored in the preset remote storage path according to the target command into a local variable includes:

[0049] Moving the SQL stored in the preset remote storage path in the cluster formed by the distributed file system to the local client according to the target command, and using the line of executable SQL in the client as the local variable.

[0050] Understandably, the target command - Hadoop command can store a certain file on the distributed file system - Hadoop (Hadoop cluster composed of distributed file systems) to an existing local directory or save all the contents under the specified directory of the distributed file system - Hadoop as a file and store it locally at the same time. Here, the above local refers to the local client; the local variable is a variable in Linux, which refers to the variable in the current shell, including environment variables and non-environment variables. Non-environment variables do not have inheritance, while environment variables refer to the environment variables read into the system by Linux after entering the system. This environment variable can be used to determine the logged-in username, command path, terminal type, and login directory; the local variable in this embodiment will define a running environment for the shell for the normal operation of subsequent steps.

[0051] Further, after step S40, it further includes:

[0052] After obtaining the reconfiguration instruction of the relationship mapping table in the front-end page, adjusting the executable SQL corresponding to the relationship mapping table according to the reconfigured relationship mapping table.

[0053] Understandably, the relationship mapping table is set in a front-end page that allows users to view and set. For example, users can set the corresponding buried point ID according to statistical requirements, that is, reset the corresponding APP buried point conditions. The buried point ID corresponding to "Home Page" was previously pv00 and can be set to pv01 according to statistical requirements. Suppose the buried point event corresponding to pv00 was a click event before, and the buried point event corresponding to pv01 is a page stay event; the adjustment process can have a preset time period, such as a preset time period set in hours or days. After the configuration is completed, the mapping relationship changes, and the data scheduled by the mapping relationship table will also change, and the generated heat map will also be different from before; in this embodiment, only by logging in to the front-end page corresponding to the relationship configuration table, the modification of the business logic can be completed without changing the calculation logic in the system, greatly improving the operation efficiency and simplicity of the system.

[0054] Further, converting the execution result corresponding to the executable SQL stored in the data warehouse tool into multiple lines of APP traffic statistical results according to the side view of the data warehouse tool and the second objective function includes:

[0055] Calling a preset initialization method according to the second objective function, and obtaining the return row data associated with the execution result corresponding to the executable SQL through the preset initialization method;

[0056] Calling a preset process method according to the second objective function, and calling the execution result corresponding to the executable SQL through the preset process method and the return row data to generate multiple lines of first data;

[0057] Obtaining multiple lines of second data output by the side view of the data warehouse tool according to the execution result stored in the data warehouse tool;

[0058] Concatenating each line of the first data and the second data to obtain the multiple lines of APP traffic statistical results.

[0059] Understandably, the side view of the data warehouse tool is the Lateral View of Hive, which can be used together with the UDTF class function. Each row in the data table is connected to each row output by the UDTF function (the second objective function) to generate a new virtual table. Among them, field names can be set for the data generated by the UDTF function, and the newly added field names can be used in statements such as sort by and group by. The role of the Lateral View is to expand the original table data; the preset initialization method is the initialize method (if this method is not used, there is no need to initialize the process of generating the first data). This method is used to return the return row data of the UDTF function and initialize the process of using the UDTF function. Among them, the return row data includes but is not limited to the return count and type; the preset process method is the process method, and the function associated with this method is the process function. Specifically, after initialization through the initialize method, the return row data is obtained. Under the return row data, one call to the process function generates one line of data; this embodiment also includes the close method, where this close method performs a cleanup operation on the above-mentioned preset initialization method and preset process method; this embodiment connects the input (second data) and output (first data) of the UDTF function through the side view of the data warehouse tool, and then obtains multiple lines of APP traffic statistical results that meet the statistical requirements.

[0060] Further, synchronously displaying the multiple lines of APP traffic statistical results on the front-end page includes:

[0061] After obtaining the last value recorded when synchronously displaying the last multi-line APP traffic statistics result to the front-end page through a data transfer tool, set the script parameters in the data transfer tool according to the last value;

[0062] After running the script parameters through the synchronization script in the data transfer tool, synchronously display the current multi-line APP traffic statistics result to the front-end page.

[0063] Understandably, the data transfer tool is the Sqoop tool, which is a tool for transferring data between relational database data (Hive) and Hadoop; the last value refers to the last-value, which can be incrementally synchronized starting from 1 (implemented through the append mode in the Sqoop tool). The last value recorded when the last synchronization was displayed to the front-end page refers to the value after the last-value changes after synchronizing the data in Hive to the Hadoop data (after determining whether the synchronization is completed by querying with exec your-sync-job, such as changing from 1 to other numbers, and determining the current last-value by querying with showyour--sync-job), and it is used to set the script parameters to ensure correct synchronization. In this embodiment, the last value recorded when the last synchronization was displayed to the front-end page during the current synchronization process will be directly obtained, and the script parameters will be set according to this last value. After running the script parameters through the synchronization script, the multi-line APP traffic statistics result will be directly synchronously displayed to the front-end page for the user to directly and clearly view the statistical results of various front-end pages and determine various usage situations of the user for the APP based on various statistical results.

[0064] In summary, the above provides a method for processing APP traffic data based on SQL. On the one hand, the APP traffic statistics results can accurately reflect the user's behavior trajectory and user habits, and users can take targeted measures based on the accurate statistics results to improve the user experience effect. On the other hand, the APP traffic statistics results are developed based on SQL, and there are SQL modules available for development using SQL in the three popular computing frameworks in the current market, namely mapreduce, spark, and flink. Therefore, the executable SQL developed and spliced based on SQL in the present invention can run on each computing framework and obtain APP traffic statistics results that meet the statistical requirements. At the same time, since the present invention is developed based on SQL, it can be better connected to the data warehouse HIVE through SQL, is suitable for the development languages used by most developers, reduces the manual development conditions, and retains the advantages of using the data warehouse HIVE before, such as fast development, strong scalability, and high extensibility. In addition, this method pre-configures the corresponding relationship mapping table, can set the APP embedding conditions based on the preset statistical requirement rules, and generate data that meets the statistical requirements. In addition, this method only needs to log in to the front-end page corresponding to the relationship configuration table to complete the modification of the business logic, without changing the computing logic in the system, greatly improving the operation efficiency and simplicity of the system.

[0065] It should be understood that the magnitudes of the sequence numbers of the steps in the above embodiments do not mean the order of execution is prior or subsequent. The execution order of each process should be determined according to its function and internal logic, and should not constitute any limitation to the implementation process of the embodiments of the present invention.

[0066] In one embodiment, there is provided an apparatus for processing APP traffic data based on SQL. This apparatus for processing APP traffic data based on SQL corresponds one-to-one with the method for processing APP traffic data based on SQL in the above embodiment. As Figure 3 shown, this apparatus for processing APP traffic data based on SQL includes an acquisition module 11, a writing module 12, a storage module 13, and a synchronization module 14. The detailed description of each functional module is as follows:

[0067] The acquisition module 11 is configured to obtain the relationship mapping table of the APP after receiving the APP traffic data processing instruction; the relationship mapping table represents the mapping relationship between the APP embedding conditions of the APP and the heat map.

[0068] The writing module 12 is configured to splice a row of executable SQL from the relationship mapping table according to the first objective function, and write the row of executable SQL to a preset remote storage path in the distributed file system.

[0069] The storage module 13 is used to convert the executable SQL in the preset remote storage path into local variables according to the target command, and after reading the local variables, obtain and execute the executable SQL, and store the execution result after executing the executable SQL in the data warehouse tool;

[0070] The synchronization module 14 is used to convert the execution result corresponding to the executable SQL stored in the data warehouse tool into multiple lines of APP traffic statistics results according to the side view of the data warehouse tool and the second target function, and synchronously display the multiple lines of APP traffic statistics results on the front-end page.

[0071] Furthermore, the SQL-based APP traffic data processing device further includes:

[0072] The first setting module is used to set the APP buried point condition at a preset page position on the front-end page;

[0073] The second setting module is used to set the data analysis condition of the heat map for the APP buried point condition, and set the relationship mapping table between the APP buried point condition and the heat map according to the data analysis method of the heat map.

[0074] Furthermore, the storage module includes:

[0075] The mobile sub-module is used to move the SQL stored in the preset remote storage path in the distributed file system cluster to the local client according to the target command, and use the executable SQL in the client as the local variable.

[0076] Furthermore, the SQL-based APP traffic data processing device further includes:

[0077] The adjustment module is used to adjust the executable SQL corresponding to the relationship mapping table according to the reconfigured relationship mapping table after obtaining the reconfiguration instruction of the relationship mapping table on the front-end page.

[0078] Furthermore, the synchronization module includes:

[0079] The first acquisition sub-module is used to call a preset initialization method according to the second target function, and obtain the return row data associated with the execution result corresponding to the executable SQL through the preset initialization method;

[0080] The generation sub-module is used to call a preset process method according to the second target function, and call the execution result corresponding to the executable SQL through the preset process method and the return row data to generate multiple lines of first data;

[0081] A second acquisition sub-module, configured to acquire multiple lines of second data output according to a side view of the data warehouse tool based on an execution result stored in the data warehouse tool;

[0082] A splicing sub-module, configured to splice each line of the first data and the second data to obtain the multiple lines of APP traffic statistics results.

[0083] Further, the synchronization module includes:

[0084] A setting sub-module, configured to set script parameters in the Sqoop tool according to the last value recorded when synchronously displaying the last obtained multiple lines of APP traffic statistics results to a front-end page through the Sqoop tool;

[0085] A synchronization sub-module, configured to synchronously display the current multiple lines of APP traffic statistics results to the front-end page after running the script parameters through a synchronization script in the Sqoop tool.

[0086] For the specific limitations of an SQL-based APP traffic data processing device, reference may be made to the limitations of an SQL-based APP traffic data processing method described above, which will not be elaborated here. Each module in the above SQL-based APP traffic data processing device can be implemented in whole or in part by software, hardware, and their combination. The above modules can be embedded in or independent of a processor in a computer device in the form of hardware, or stored in a memory in the computer device in the form of software, so that the processor can call and execute the operations corresponding to the above modules.

[0087] In one embodiment, a computer device is provided. The computer device can be a server, and its internal structure diagram can be as Figure 4 shown. The computer device includes a processor, a memory, a network interface, and a database connected through a system bus. Among them, the processor of the computer device is used to provide computing and control capabilities. The memory of the computer device includes a non-volatile storage medium and an internal memory. The non-volatile storage medium stores an operating system, a computer program, and a database. The internal memory provides an environment for the operation of the operating system and the computer program in the non-volatile storage medium. The database of the computer device is used to store data involved in an SQL-based APP traffic data processing method. The network interface of the computer device is used to communicate with an external terminal through a network connection. The computer program, when executed by the processor, implements an SQL-based APP traffic data processing method.

[0088] In one embodiment, a computer device is provided, including a memory, a processor, and a computer program stored on the memory and executable on the processor. When the processor executes the computer program, the steps of a method for processing APP traffic data based on SQL in the above embodiment are implemented, such as Figure 2 the steps S10 to S30 shown. Alternatively, when the processor executes the computer program, the functions of each module / unit of an apparatus for processing APP traffic data based on SQL in the above embodiment are implemented, such as Figure 3 the functions of the modules 11 to 13 shown. To avoid repetition, it will not be elaborated here.

[0089] In one embodiment, a computer-readable storage medium is provided, on which a computer program is stored. When the computer program is executed by a processor, the steps of a method for processing APP traffic data based on SQL in the above embodiment are implemented, such as Figure 2 the steps S10 to S30 shown. Alternatively, when the computer program is executed by a processor, the functions of each module / unit of an apparatus for processing APP traffic data based on SQL in the above embodiment are implemented, such as Figure 3 the functions of the modules 11 to 13 shown. To avoid repetition, it will not be elaborated here.

[0090] Those of ordinary skill in the art can understand that all or part of the processes in the methods of the above embodiments can be completed by instructing relevant hardware through a computer program. The computer program can be stored in a non-volatile computer-readable storage medium. When the computer program is executed, it can include the processes of the embodiments of the above methods. Among them, any reference to a memory, storage, database, or other medium used in the embodiments provided by the present invention can include non-volatile and / or volatile memories. Non-volatile memories can include read-only memory (ROM), programmable ROM (PROM), electrically programmable ROM (EPROM), electrically erasable programmable ROM (EEPROM), or flash memory. Volatile memories can include random access memory (RAM) or external cache memory. By way of illustration and not limitation, RAM is available in various forms, such as static RAM (SRAM), dynamic RAM (DRAM), synchronous DRAM (SDRAM), double data rate SDRAM (DDR SDRAM), enhanced SDRAM (ESDRAM), synchronous link DRAM (SLDRAM), Rambus direct RAM (RDRAM), direct memory bus dynamic RAM (DRDRAM), and Rambus dynamic RAM (RDRAM), etc.

[0091] Those skilled in the art can clearly understand that, for the convenience and brevity of description, only the above-mentioned division of each functional unit and module is used as an example. In actual applications, the above functions can be allocated to different functional units and modules according to needs, that is, the internal structure of the device can be divided into different functional units or modules to complete all or part of the functions described above.

[0092] The above-described embodiments are only used to illustrate the technical solutions of the present invention, rather than to limit it; although the present invention has been described in detail with reference to the foregoing embodiments, those of ordinary skill in the art should understand that they can still modify the technical solutions recorded in the foregoing embodiments, or perform equivalent replacements for some of the technical features; and these modifications or replacements do not cause the essence of the corresponding technical solutions to deviate from the spirit and scope of the technical solutions of the embodiments of the present invention, and should all be included in the protection scope of the present invention.

Claims

1. A method for processing APP traffic data based on SQL, characterized in that, it includes: After receiving the APP traffic data processing instruction, obtain the relationship mapping table of the APP; The relationship mapping table represents the mapping relationship between the APP buried point conditions of the APP and the heat map; The APP buried point condition means that the buried point with a unique buried point ID corresponding to the preset target page position in the front-end page is triggered; According to the first objective function, splice a row of executable SQL from the relationship mapping table and write the row of executable SQL to a preset remote storage path in the distributed file system; the process of splicing a row of executable SQL is as follows: First, select the buried point ID from the relationship mapping table through the select in the SQL statement; then, take the sum of the field data corresponding to another buried point ID as the page access volume, and when the sum of the field data is greater than the preset data threshold, take the sum of the field data greater than the preset data threshold as the unique visitor volume; then, select the buried point ID from the relationship mapping table through the select in the SQL statement, determine the sum of the field data after performing the select count operation on the buried point ID, and at the same time define various situations of the data, including from the relationship mapping table, where time conditions, and group by fields or buried point IDs; finally, splice the field data corresponding to the above buried point ID with the field data corresponding to another buried point ID; According to the target command, convert the row of executable SQL stored in the preset remote storage path into a local variable, and after reading the local variable, obtain and execute the row of executable SQL, and store the execution result after executing the row of executable SQL in the data warehouse tool; According to the side view of the data warehouse tool and the second objective function, convert the execution result corresponding to the row of executable SQL stored in the data warehouse tool into multiple rows of APP traffic statistics results, and synchronously display the multiple rows of APP traffic statistics results on the front-end page.

2. The method for processing APP traffic data based on SQL according to claim 1, characterized in that, Before obtaining the relationship mapping table of the APP, it further includes: Set the APP buried point condition at the preset target page position in the front-end page; Set the data analysis condition of the heat map for the APP buried point condition, and set the relationship mapping table between the APP buried point condition and the heat map according to the data analysis method of the heat map.

3. The method for processing APP traffic data based on SQL according to claim 1, characterized in that, The conversion of the row of executable SQL stored in the preset remote storage path into a local variable according to the target command includes: According to the target command, move the SQL stored in the preset remote storage path in the cluster composed of the distributed file system to the local client, and use the row of executable SQL in the client as the local variable.

4. The method for processing APP traffic data based on SQL according to claim 1, characterized in that, After converting the execution result corresponding to the line of executable SQL stored in the data warehouse tool into multiple lines of APP traffic statistics results according to the side view of the data warehouse tool and the second objective function, and synchronously displaying the multiple lines of APP traffic statistics results to the front-end page, the following further includes: After obtaining the reconfiguration instruction of the relationship mapping table in the front-end page, adjust the executable SQL corresponding to the relationship mapping table according to the reconfigured relationship mapping table.

5. The SQL-based APP traffic data processing method according to claim 1, characterized in that the conversion of the execution result corresponding to the line of executable SQL stored in the data warehouse tool into multiple lines of APP traffic statistics results according to the side view of the data warehouse tool and the second objective function includes: calling a preset initialization method according to the second objective function, and obtaining return row data associated with the execution result corresponding to the line of executable SQL through the preset initialization method; calling a preset process method according to the second objective function, and calling the execution result corresponding to the line of executable SQL through the preset process method and the return row data to generate multiple lines of first data; obtaining multiple lines of second data output by the side view of the data warehouse tool according to the execution result stored in the data warehouse tool; concatenating each line of the first data and the second data to obtain the multiple lines of APP traffic statistics results.

6. The SQL-based APP traffic data processing method according to claim 1, characterized in that the synchronous display of the multiple lines of APP traffic statistics results to the front-end page includes: after obtaining the last value recorded when the multiple lines of APP traffic statistics results obtained last time are synchronously displayed to the front-end page through the data transfer tool, setting the script parameters in the data transfer tool according to the last value; after running the script parameters through the synchronization script in the data transfer tool, synchronously displaying the current multiple lines of APP traffic statistics results to the front-end page.

7. An SQL-based APP traffic data processing device, characterized in that it includes: an acquisition module, configured to acquire the relationship mapping table of the APP after receiving the traffic data processing instruction of the APP; the relationship mapping table represents the mapping relationship between the APP buried point conditions of the APP and the heat map; the APP buried point condition refers to that the buried point with a unique buried point ID corresponding to the preset target page position in the front-end page is triggered; A writing module, configured to splice an executable SQL line from the relationship mapping table according to a first objective function, and write the executable SQL line to a preset remote storage path in a distributed file system; the writing module further includes: first selecting a buried point ID from the relationship mapping table through select in the SQL statement; then taking the sum of the field data corresponding to another buried point ID as the page access volume, and when the sum of the field data is greater than a preset data threshold, taking the sum of the field data greater than the preset data threshold as the unique visitor volume; then selecting a buried point ID from the relationship mapping table through select in the SQL statement, determining the sum of the field data after performing a select count operation on the buried point ID, and simultaneously defining various situations of the data, including from the relationship mapping table, where time conditions, and group by fields or buried point IDs; finally splicing the field data corresponding to the above buried point ID with the field data corresponding to another buried point ID; A storage module, configured to convert the executable SQL line stored in the preset remote storage path into a local variable according to a target command, and after reading the local variable, obtain and execute the executable SQL line, and store the execution result after executing the executable SQL line in a data warehouse tool; A synchronization module, configured to convert the execution result corresponding to the executable SQL line stored in the data warehouse tool into multiple lines of APP traffic statistics results according to a side view of the data warehouse tool and a second objective function, and synchronously display the multiple lines of APP traffic statistics results on a front-end page.

8. The SQL-based APP traffic data processing device according to claim 7, wherein, the SQL-based APP traffic data processing device further includes: A first setting module, configured to set the APP buried point condition at a preset page position on the front-end page; A second setting module, configured to set data analysis conditions for a heat map for the APP buried point condition, and set a relationship mapping table between the APP buried point condition and the heat map according to the data analysis method of the heat map.

9. A computer device, including a memory, a processor, and a computer program stored in the memory and executable on the processor, wherein, when the processor executes the computer program, it implements a SQL-based APP traffic data processing method according to any one of claims 1 to 6.

10. A computer-readable storage medium storing a computer program, wherein, when the computer program is executed by a processor, it implements a SQL-based APP traffic data processing method according to any one of claims 1 to 6.

Images