19 lines
987 B
Bash
19 lines
987 B
Bash
#!/bin/bash
|
|
|
|
echo "
|
|
|
|
create table public.om_product_view_reseller_business_metrics_db
|
|
as
|
|
select distinct --cast(left(ts,4) || '-' || right(left(ts,6),2) || '-' || right(left(ts,8),2) as date)
|
|
cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) as App_view_date,
|
|
profile_objectid as view_object_id,profile_phone,profile_email
|
|
from clevertap.view_item vi
|
|
union
|
|
select distinct cast(concat(concat(concat(substring(ts,0,5),'-'),concat(substring(ts,5,2),'-')),substring(ts,7,2)) AS date) as App_view_date,
|
|
profile_objectid as view_object_id,profile_phone,profile_email
|
|
from clevertap.view_cart vi
|
|
|
|
" > /home/ec2-user/cronjob/warehouseAnalysis/OM_Test_Launched.sql
|
|
|
|
psql "host=raen-prd-sg-redshift-cluster.cdqj58hfx4p7.ap-southeast-1.redshift.amazonaws.com user=dbadmin dbname=analytics port=5439 password=5qCif6eyY3Kmg4z" -f /home/ec2-user/cronjob/warehouseAnalysis/warehouseAnalysis.sql > OM_Test_Launched.log
|