188 lines
8.4 KiB
Bash
188 lines
8.4 KiB
Bash
|
#!/bin/bash
|
||
|
|
|
||
|
|
|
||
|
|
echo -e " \n----------- ACCEPTING NUMBER OF DAYS BEFORE THE RUN DATE FOR WHICH THE REPORT IS TO BE RUN --------------\n"
|
||
|
|
|
||
|
|
backDay=$1
|
||
|
|
|
||
|
|
echo $backDay
|
||
|
|
|
||
|
|
echo -e " \n------------- DATE IN THE REQUIRED FORMAT --------------\n"
|
||
|
|
reportDate=$(date -d"$backDay day ago" "+%Y-%m-%d")
|
||
|
|
date
|
||
|
|
echo 'reportDate'=$reportDate
|
||
|
|
|
||
|
|
|
||
|
|
echo "
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE raena_analytics.google_pay_split;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.google_pay_split AS
|
||
|
|
SELECT * ,
|
||
|
|
split_part(address ,', ',1) address1,
|
||
|
|
split_part(address,', ',2) address2,
|
||
|
|
split_part(address,', ',3) address3,
|
||
|
|
split_part(address,', ',4) address4,
|
||
|
|
split_part(address,', ',5) address5,
|
||
|
|
split_part(address,', ',6) address6,
|
||
|
|
split_part(address,', ',7) address7,
|
||
|
|
split_part(address,', ',8) address8,
|
||
|
|
split_part(address,', ',9) address9,
|
||
|
|
split_part(address,', ',10) address10,
|
||
|
|
split_part(address,', ',11) address11
|
||
|
|
FROM
|
||
|
|
(SELECT DISTINCT lower(search_city) city,
|
||
|
|
lower(address) address,
|
||
|
|
CASE
|
||
|
|
WHEN search_city=''
|
||
|
|
AND address='' THEN 1
|
||
|
|
END flag
|
||
|
|
FROM raena_crawler_management.google_map_search_results
|
||
|
|
UNION SELECT DISTINCT lower(current_city) city,
|
||
|
|
lower(address) address,
|
||
|
|
CASE
|
||
|
|
WHEN current_city=''
|
||
|
|
AND address='' THEN 1
|
||
|
|
END flag
|
||
|
|
FROM
|
||
|
|
(SELECT fbp.profile_id,
|
||
|
|
name reseller_name,
|
||
|
|
mobile,
|
||
|
|
Email,
|
||
|
|
active_status is_account_active,
|
||
|
|
current_city,
|
||
|
|
hometown,
|
||
|
|
WORK company,
|
||
|
|
address,
|
||
|
|
friends number_of_friends,
|
||
|
|
followers number_of_followers,
|
||
|
|
following number_of_people_following --,converted_flag
|
||
|
|
FROM
|
||
|
|
(SELECT facebook_profile.*
|
||
|
|
FROM raena_crawler_management.facebook_profile ) fbp
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT DISTINCT coalesce(A.profile_id,B.profile_id) profile_id ,
|
||
|
|
mobile,
|
||
|
|
Email
|
||
|
|
FROM
|
||
|
|
(SELECT profile_id ,
|
||
|
|
CASE WHEN detail_type= 'Mobile' THEN value END AS mobile
|
||
|
|
FROM raena_crawler_management.facebook_profile_detail
|
||
|
|
WHERE detail_type ='Mobile') A
|
||
|
|
FULL OUTER JOIN
|
||
|
|
(SELECT profile_id ,
|
||
|
|
CASE WHEN detail_type= 'Email' THEN value END AS Email
|
||
|
|
FROM raena_crawler_management.facebook_profile_detail
|
||
|
|
WHERE detail_type ='Email') B ON A.profile_id = B.profile_id) fbpd ON fbpd.profile_id = fbp.profile_id) B) A
|
||
|
|
WHERE flag IS NULL;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE raena_analytics.google_pay_mapping_table_stage;
|
||
|
|
|
||
|
|
|
||
|
|
CREATE TABLE raena_analytics.google_pay_mapping_table_stage AS
|
||
|
|
SELECT A.* ,
|
||
|
|
B.city mapping_city
|
||
|
|
FROM raena_analytics.google_pay_split A
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT DISTINCT lower(city) city
|
||
|
|
FROM raena_analytics.google_pay_split) B ON 1=1;
|
||
|
|
|
||
|
|
|
||
|
|
DROP TABLE raena_analytics.city_calculated_values;
|
||
|
|
CREATE TABLE raena_analytics.city_calculated_values AS
|
||
|
|
SELECT address,
|
||
|
|
mapping_city calculated_city
|
||
|
|
FROM
|
||
|
|
(SELECT *,
|
||
|
|
CASE
|
||
|
|
WHEN replace(replace(address1,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END map_value1,
|
||
|
|
CASE
|
||
|
|
WHEN replace(replace(address2,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END map_value2,
|
||
|
|
CASE
|
||
|
|
WHEN replace(replace(address3,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END map_value3,
|
||
|
|
CASE
|
||
|
|
WHEN replace(replace(address4,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END map_value4,
|
||
|
|
CASE
|
||
|
|
WHEN replace(replace(address5,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END map_value5,
|
||
|
|
CASE
|
||
|
|
WHEN replace(replace(address6,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END map_value6,
|
||
|
|
CASE
|
||
|
|
WHEN replace(replace(address7,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END map_value7,
|
||
|
|
CASE
|
||
|
|
WHEN replace(replace(address8,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END map_value8,
|
||
|
|
CASE
|
||
|
|
WHEN replace(replace(address9,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END map_value9,
|
||
|
|
CASE
|
||
|
|
WHEN replace(replace(address10,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END map_value10,
|
||
|
|
CASE
|
||
|
|
WHEN replace(replace(address11,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END map_value11,
|
||
|
|
CASE
|
||
|
|
WHEN replace(replace(address1,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END + CASE
|
||
|
|
WHEN replace(replace(address2,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END + CASE
|
||
|
|
WHEN replace(replace(address3,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END + CASE
|
||
|
|
WHEN replace(replace(address4,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END + CASE
|
||
|
|
WHEN replace(replace(address5,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END + CASE
|
||
|
|
WHEN replace(replace(address6,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END + CASE
|
||
|
|
WHEN replace(replace(address7,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END + CASE
|
||
|
|
WHEN replace(replace(address8,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END + CASE
|
||
|
|
WHEN replace(replace(address9,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END + CASE
|
||
|
|
WHEN replace(replace(address10,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END + CASE
|
||
|
|
WHEN replace(replace(address11,'kota ',''),'city ','') = replace(replace(mapping_city,'kota ',''),'city ','') THEN 1
|
||
|
|
ELSE 0
|
||
|
|
END total
|
||
|
|
FROM raena_analytics.google_pay_mapping_table_stage) A
|
||
|
|
WHERE total>0 ;
|
||
|
|
|
||
|
|
|
||
|
|
|
||
|
|
" > /home/ec2-user/cronjob/redshift/sql_code/city_mapping_for_online_lead_db.sql
|
||
|
|
|
||
|
|
psql "host=redshift-cluster-1.cdqj58hfx4p7.ap-southeast-1.redshift.amazonaws.com user=dbadmin dbname=analytics port=5439 password=5qCif6eyY3Kmg4z" -f /home/ec2-user/cronjob/redshift/sql_code/city_mapping_for_online_lead_db.sql
|
||
|
|
|
||
|
|
|