Improper use of regular expressions in hive causes the operation to be extremely slow

The Business Assurance department has a need to use hive to calculate the last hour's data in real time, such as 12 points, I need to calculate 11 points of data, and must be run after 1 hours, but they realized with hive when the single map task runs more than 1 hours, There is no need to meet the demand, then call me to help optimize the following is the optimization process:

1. HQL statement:

CREATE TABLE Weibo_mobile_nginx as Selectsplit (split (log, ') [0], ' \\| ') [0] Host,split (split (log, ') [0], ' \\| ') [1] Time,substr (Split (Split (log, ') [2], ' \ \ ') [0], ') [0], 2) request_type,split (Split (log, ') [2], ' \ \ ') [0], ') [1] interface,regexp_extract (log, ' .*& ua =[^ _]* __ ([^ _]*) __ ([^ _]*) __ ([^ _]*] __<span style= "font -family:arial, Helvetica, Sans-serif; >[^&]*</span> ', 3) version,regexp_extract (log, ' .*& ua =[^ _]* __ ([^ _]*) __ ([^ _]*) __ ([^ _]*] __.* ', 1) s Ysterm,regexp_extract (log, '. *&networktype= ([^&%]*). * ', 1) net_type,split (log, ' ") [4] Status,split (log," [5] Client_ip,split (log, "') [6] uid,split (log, ') [8] Request_time,split (log," ") [] request_uid,split (log, ' [] http_host,split (log, ") [] upstream_response_time,split (log,") [+] Idcfromods_wls_wap_base_origwhered t = ' 20150311 ' and HOUR = ' ' and (Split (log, ') []= ' api.weibo.cn ' OR split (log, ') []= ' mapi.we]Ibo.cn '); 

in fact, this hql is very simple, get the data from a table Ods_wls_wap_base_orig with only one column of data, then split the data in each row or match the regular expression to get the required field information. Finally, the Weibo_mobile_nginx table is created from the output data.

One row of the table Ods_wls_wap_base_orig data format is as follows:

Web043.mweibo.yhg.sinanode.com| [11/mar/2015:00:00:01 +0800] '-' "get/2/remind/unread_count?v_f=2&c=android&wm=9847_0002&remind_ version=0&with_settings=1&unread_message=1&from=1051195010&lang=zh_cn&skin=default& with_page_group=1&i=4acbdd0&s=6b2cd11c&gsid=4uq15a2b3&ext_all=0&idc=&ua=oppo-r8007__ weibo__5.1.1__android__android4.3&oldwm=9893_0028 http/1.1 "'" r8007_4.3_weibo_5.1.1_android "' 200 ' [ 121.60.78.23] ' 3226234350 ' "-" ' 0.063 ' 351 '-' 121.60.78.23 ' 1002792675011956002 ' api.weibo.cn '-' 0.063 ' YHG 20150311 00

There are only 1 columns, and the column name is log.

2, since HQL implementation is very slow, my first attempt to optimize is to write MapReduce

The map code is as follows:

public class Map extends mapper<longwritable, text, text, text> {private Text Outputkey = new text ();  Private text Outputvalue = new text ();  Pattern p_per_client = pattern. Compile (". *&ua=[^_]*__ ([^_]*) __ ([^_]*) __ ([^_]*] __[^&]*");  Pattern net_type_parent = Pattern.compile (". *&networktype= ([^&%]*). *");  public void Map (longwritable key, Text value, Context context) throws IOException, interruptedexception {string[]    arr = Value.tostring (). Split ("'"); if (Arr[13].equals ("api.weibo.cn") | | arr[13].equals ("mapi.weibo.cn")) {Matcher Matcher = P_per_client.matcher (value      . toString ());      String host = "";      String time = "";      String request_type = "";      String Interface_url = "";      String Version = "";      String systerm = "";      String net_type = "";      String status = "";      String client_ip = "";      String uid = "";      String request_time = "0";      String request_uid = "";      String http_host = ""; String upStream_response_time = "0";      String IDC = ""; Host = Arr[0].split ("\\|")      [0]; Time = Arr[0].split ("\\|")      [1]; Request_type = arr[2].split ("\ \")      [0].split ("") [0].substring (1); Interface_url = arr[2].split ("\ \")      [0].split ("") [1];        if (Matcher.find ()) {Version = Matcher.group (1);      Systerm = Matcher.group (2);      } Matcher matcher_net = Net_type_parent.matcher (value.tostring ());      if (Matcher_net.find ()) {Net_type = Matcher_net.group (1);      } status = Arr[4];      CLIENT_IP = arr[5];      UID = arr[6];      if (!arr[8].equals ("-")) {request_time = arr[8];      } request_uid = arr[12];      Http_host = arr[13];      if (!arr[15].equals ("-")) {upstream_response_time = arr[15];      } IDC = arr[16];  Outputkey.set (host + "\ T" + time + "\ T" + Request_type + "\ T" + Interface_url + "\ T" + version + "\ T" + systerm + "\ t" + Net_type + "\ T" + status + "\ t" + client_ip + "\ T" + uid + "\ t"+ Request_uid +" \ T "+ http_host +" \ T "+ IDC);      Outputvalue.set (request_time + "\ T" + upstream_response_time);    Context.write (Outputkey, Outputvalue); }  }

Java code is actually very simple, not much to say here. Package submission job, the results map slowest run for 40 minutes, the average map run time of 30 minutes, although the entire job completed within 1 hours, but also very slow, this problem does not seem to be able to use the Java rewrite can be good problem.

3, the final detection of regular expressions

The use of the Java implementation of the MapReduce operation is also very slow, it seems that the problem or other reasons, I looked at the hql in the regular expression, modified several places:

The original:

Regexp_extract (                log, ' .*& ua =[^ _]* __ ([^ _]*) __ ([^ _]*)                __ ([^ _]*] __[^&]* ',                3        ) version,        Regexp_extract (                log, ' .*& ua =[^ _]* __ ([^ _]*) __ ([^ _]*)                __ ([^ _]*) __.* ', 1)        Systerm,regexp_extract (lo G, '. *&networktype= ([^&%]*). * ',                1        ] Net_type,

After modification:

Regexp_extract (log, ' &ua=[^_]*__[^_]*__ ([^_]*) __[^_]*__ ', 1) version,regexp_extract (log, ' &ua=[^_]*__[^_]* __[^_]*__ ([^_]*) __ ', 1) systerm,regexp_extract (log, ' &networktype= ([^&%]*) ', 1) net_type,

In fact, the matching target is very clear, so I took the regular expression before and after the ". *" removed, while removing the unnecessary group, the index is changed to 1.

The regular expressions for Java code have also been modified:

Pattern p_per_client = Pattern      . Compile ("&ua=[^_]*__[^_]*__ ([^_]*) __ ([^_]*] __");  Pattern net_type_parent = Pattern.compile ("&networktype= ([^&%]*).");

The test was submitted separately, the speed SS, modified HQL and mapreduce the entire job 6 minutes to complete the operation, the average map run time of 2 minutes, the speed increased greatly, meet their speed requirements.

Summarize:

1, the regular expression first contains ". *", so that at the time of the match need to start matching, the speed is very very slow, if we match the target is very clear, should be removed ". *"

2, after encountering this problem, be sure to look at the regular expression is not write a problem, remember to remember.

Improper use of regular expressions in hive causes the operation to be extremely slow