Four frequently-used complex/inefficient statistics on hive architecture optimization are provided from the source to avoid excessive computing on the upper layer.

Case 1: trackinfo, common low-performance UDF for basic table Processing

Background description: logs are loaded to the real-time log table trackreal once every 10 minutes (by hour). To ensure real-time performance, no filtering is performed during the loading process, after being loaded to the trackreal table, the system filters illegal data and crawler data, generates a daily incremental log table trackinfo, and then counts traffic based on different page_types.

The solution is as follows:

Select 'homepage ', count (*) PV, # Each record is a PV count (distinct session_id) UV # Calculate uvfrom trackinfo where DS = '2017-11-11 'and URL like 'HTTP: // cms.yhd.com/cmspage/show.do? Pageid = % ';

Since the URL rules are hard-coded into the code, they are not flexible, so they can be handed over to the UDF for processing.

The transformed scheme is as follows:

Select 'homepage ', count (*) PV, count (distinct session_id) uvfrom trackinfo where DS = '2017-11-11' andGetpageid (URL)= 1

 

After a UDF is used, each row of data needs to be matched once for traffic statistics. This UDF is used for almost all traffic statistics jobs, and the getpageid performance is relatively low.Inefficient statistics, How to optimize the processing?

Another Transformation Scheme: the original page_type is provided directly.Type.

When generating trackinfo, add a field url_page_id to differentiate it. In the future, upper-layer jobs no longer need to use udfs when calling. Instead, they can directly use this field to improve performance.

 

Case 2: session_info, which provides common session_id, attributes, and metrics in a unified manner.

Background: user behavior analysis. There are multiple jobs that need to query some attributes, such as: landing page (which is the first webpage to access the system, landing_referer), source (Network Alliance, search engine, favorites, tracker_u) and PV. This information uses a job to extract it to session_info in a table,Use oneJobThe user's attributes and behaviors are completely calculated. The upper-layer job only needs to use the data of this table.. This belongsComplex statistics.

Session_info Table introduction:

Session_id string # start_time stringstay_time bigintpv intprovince_id intguid string is changed every time you open a new browser. # One is generated on each computer, and the page is the same each time you access it, as long as the system is not reinstalled, the IP address is consistent. String end_user_id bigintlanding_page_url string # landing page is_new_access string # whether the new visitor landing_referer string # landing page on the previous page is: search engine, network alliance, advertising alliance tracker_u string # whether it is the source number tracker_src stringcity_id {stringproduct_pv bigintcart_pv {{bigintadg_keyword {bigintplatform stringapp_vers stringds string # partition field by day

 

Summary: similar to this inefficient or complex statistics, we recommend that you give it on the source.