AjR7LHGy

1create EXTERNAL table IF NOT EXISTS DB.efficacy
2(
3product string,
4TP_Silent INT,
5TP_Active INT,
6server_date date
7)
8
9ROW FORMAT DELIMITED
10FIELDS TERMINATED BY ','
11STORED AS TEXTFILE
12LOCATION 'hdfs://hdfsadlproduction/user/DB/Report/efficacy';
13
14Insert INTO DB.efficacy
15select 
16    product,
17    SUM(CASE WHEN verdict = 'TP_Silent' THEN 1 ELSE 0 END ),
18    SUM(CASE WHEN verdict = 'TP_Active' THEN 1 ELSE 0 END ) ,
19    current_date()
20from
21    DB.efficacy_raw
22group by 
23    product
24;
25	
26SET hive.execution.engine=mr;
27SET tez.queue.name=${queueName};
28SET mapreduce.job.queuename=${queueName};
29SET mapreduce.map.memory.mb  = 8192;
30SET mapreduce.reduce.memory.mb = 8192;
31SET hive.exec.dynamic.partition = true;
32SET hive.exec.dynamic.partition.mode = nonstrict;
33SET hive.exec.parallel = true;
34SET hive.exec.parallel.thread.number = 2;
35SET mapreduce.input.fileinputformat.split.maxsize=2048000000;
36SET mapreduce.input.fileinputformat.split.minsize=2048000000;
37SET mapreduce.job.reduces = 20;
38SET hadoop.security.credential.provider.path=jceks://hdfs/user/efficacy/s3-access/efficacy.jceks;
39set hive.vectorized.execution.enabled=false;
40set hive.enforce.bucketmapjoin=false;
41set hive.optimize.bucketmapjoin.sortedmerge=false;
42set hive.enforce.sortmergebucketmapjoin=false;
43set hive.optimize.bucketmapjoin=false;
44set hive.exec.dynamic.partition.mode=nostrict;
45set hive.exec.compress.intermediate=false;
46set hive.exec.compress.output=false;
47**set hive.exec.reducers.max=1;**