operator=bigqueryoperator
task_id=06_dashboard_metadata_acc_vw_full
write_disposition=WRITE_TRUNCATE
destination_dataset_table={project_dataset_alice_in}.dashboard_metadata_acc_vw_full
---sqlscript
sql=select distinct pp.documentcategory,
pp.documenteffectivedate,
pp.contractnumber,
pp.contractname,
case when lower(substr(trim(documentpath),-3))='zip' then doc.real_name else documenttitle end as documenttitle,
pp.engagementenddate,
pp.contractcreateddate,
pp.masterclientnumber,
pp.clientnumber,
case when lower(substr(trim(documentpath),-3))='zip' then concat(documentid, '_' ,suffix) else cast(documentid as string) end as documentid,
pp.documentexpirationdate,
string(date(pp.submissiondate)) as submissiondate,
pp.masterclientname,
pp.clientname,
pp.clientservicegroup,
pp.clientserviceregion,
pp.clientcountry,
pp.contractcountry,
pp.contractregion,
pp.syslastupdated,
'' as contractunit,
deleteflag
from `{project_dataset_alice_in}.es_metadata_ip` pp
inner join (select documentname as documentname1,regexp_extract(mapping_full_path, r'[^\/]+[^_]+') documentname,
substr(mapping_full_path,-3) as suffix,real_name
from `{project_dataset_alice_in}.documents_in_hdfs_vw`) doc on pp.documentname = doc.documentname
where date(pp.submissiondate)>='2010-09-01';
we are joining these 2 tables with contractnumber key
in dashboard for example if we select " AB INBEV" from "master client" filter client clasification filter values has to enable only one value instead of 3 values basically it should be 1-1