jmErE5ef

1SET hive.exec.compress.output=true;
2SET mapred.output.fileoutputformat.compress=true;
3SET mapred.output.compression.codec = org.apache.hadoop.io.compress.GzipCodec;
4
5SET mapred.job.queue.name = adhoc;
6SET hive.exec.dynamic.partition.mode=nonstrict;
7
8SET mapred.reduce.tasks=200;
9
10USE arstel;
11
12drop table if exists arstel.finstar_intent_notify_filtered_by_default_score;
13CREATE TABLE arstel.finstar_intent_notify_filtered_by_default_score(
14  ctn string,
15  send_time string,
16  text string)
17partitioned by (date string);
18
19insert overwrite table finstar_intent_notify_filtered_by_default_score PARTITION (date)
20SELECT nfy.*
21from arstel.finstar_intent_notify_prod2 nfy
22join (SELECT ctn, count(*) as cnt
23    from arstel.finstar_intent_notify_prod2 where date > 20171024
24    and length(ctn) == 11
25    and ctn is not null
26    and ctn not in ('79684640327',  '79250494925',  '79261724155',  '79055399813',  '79621829264',  '79651627367',
27     '79099093353', '79633465819', '79295774344', '79067979646', '79651247893', '79037788742', '79637268082', '79031247548',
28     '79060469848', '79055168735', '79611074084', '79652648148', '79651262538', '79036607132', '79636730047', '79637820444',
29     '79651352462', '79616209779', '79057636945', '79057027930', '79031995417', '79032053127', '79037487195', '79653267573',
30     '79084427106', '79055190600', '79652431218', '79670223356')
31     group by ctn
32     having count(*) < 10000 ) counts on counts.ctn = nfy.ctn
33join (SELECT subs_key 
34      from (SELECT concat('', '7', subs_key) subs_key
35            from arstel.fr_finstar_score_new where time_key = '2017-09-01' and score <= 0.4) tmp) def on nfy.ctn = def.subs_key
36where date > 20171024;