Van CSV naar STER met COVID19 data

Als bron-voorbeeld nemen we de COVID19 opendata van de site van RIVM. Deze set wordt dagelijks aangevuld/ververst.

Met de eerste set van comando's wordt een csv-tabel ingelezen en onderzocht op inhoud en structuur. Zo verkrijgt men inzicht in hoe de data er ongeveer uitziet.

Het laden van de bron en bestuderen van de data

csv_to_stage(' | SOURCE https://data.rivm.nl/covid-19/COVID-19_aantallen_gemeente_per_dag.csv | TARGET STG_CORONA2 | DELIMITER ;   | LOG_INFO inlezen coronadata  | xSHOW | ' )

##voorbereiden van de dimensies 

browse select * from xxx.STG_CORONA2 where municipality_name = 'Meppel'

browse select distinct date_of_report from xxx.STG_CORONA2
browse select distinct municipality_name from xxx.STG_CORONA2
browse select distinct province from xxx.STG_CORONA2
browse select distinct security_region_code  from xxx.STG_CORONA2
browse select distinct municipal_health_service from xxx.STG_CORONA2
browse select distinct roaz_region from xxx.STG_CORONA2

Heeft men eenmaal inzicht in de data en is duidelijk welke dimensies om de meetwaarden moeten, kan voor de dimensies eenvoudig views gedefinieerd worden.

create view xxx.dim_gemeenten_v as   select distinct municipality_name from xxx.STG_CORONA2
create view xxx.dim_provincie_v as   select distinct province from xxx.STG_CORONA2
create view xxx.dim_veiligheidsregiocode_v as select distinct security_region_code  from xxx.STG_CORONA2
create view xxx.dim_gezondheidsdienst_v as select distinct security_region_code  from xxx.STG_CORONA2
create view xxx.dim_roaz_regio_v as select distinct roaz_region  from xxx.STG_CORONA2

Vaak is het maken van de DIM-views een iteratief proces waarbij steeds aanpassingen gewenst zijn. Dan is het handig wanneer je de drop-statements beschikbaar hebt.

drop view xxx.dim_gemeenten_v
drop view xxx.dim_provincie_v
drop view xxx.dim_veiligheidsregiocode_v
drop view xxx.dim_gezondheidsdienst_v
drop view xxx.dim_roaz_regio_v

Middels onderstaande statements kan eenvoudig in de tabellen gekeken worden of de data er goed uitziet ..

select * from xxx.dim_gemeenten_v
select * from xxx.dim_provincie_v
select * from xxx.dim_veiligheidsregiocode_v
select * from xxx.dim_gezondheidsdienst_v
select * from xxx.dim_roaz_regio_v

Aanmaak van de dimensies

Is alles naar wens, dan kan met onderstaande commando's de TARGET-DIMENSIE-tabellen ververst worden. De eerste keer zullen de targets nog niet bestaan en vanzelf aangemaakt worden. De actualize-routine is een FULL-refresh-tiepe-2: Van aangeboden voorkomens met verschillen op de aangeboden attributen/kolommen tenopzichte van de vorige actuele voorkomens, worden afgesloten waarna het nieuwe voorkomen als actueel wordt bijgeschreven. De ETL-proces-attributen date_created,date_updated en date_deleted en CURR_ID worden daarbij gebruikt. Met de date-velden kan men tijdreizen en met CURR_ID = 'Y' kan eenvoudig het laatste enof actuele voorkomen geselecteerd worden.

Andere ETL-routines zijn actualize_t1('|SOURCE xx | TARGET yy | commands zzz |') en actualize_t2('|SOURCE xx | TARGET yy | commands zzz |') waar delta-verversing mee gerealiseerd wordt.

actualize('| SOURCE DIM_GEMEENTEN_V             | TARGET DIM_GEMEENTEN            | LOG_INFO full-actualize ')
actualize('| SOURCE DIM_PROVINCIE_V             | TARGET DIM_PROVINCIE            | LOG_INFO full-actualize ')
actualize('| SOURCE DIM_VEILIGHEIDSREGIOCODE_V  | TARGET DIM_VEILIGHEIDSREGIOCODE | LOG_INFO full-actualize ')
actualize('| SOURCE DIM_GEZONDHEIDSDIENST_V     | TARGET DIM_GEZONDHEIDSDIENST    | LOG_INFO full-actualize ')
actualize('| SOURCE DIM_ROAZ_REGIO_V            | TARGET DIM_ROAZ_REGIO           | LOG_INFO full-actualize ')
actualize('| SOURCE GRIP_DATUM_V                | TARGET DIM_DATUM_C              | LOG_INFO full-actualize ')

Standaard is de grip_datum_v beschikbaar. Middels BROWSE kunnen tabellen bekeken worden.

select top 10 * from xxx.grip_datum_v               ( sqlserver )
select * from xxx.grip_datum_v where rownum <= 10   ( oracle )
select * from xxx.grip_datum_v limit 10             ( postgresql )

browse DIM_GEMEENTEN
browse DIM_ROAZ_REGIO
browse DIM_DATUM_C

Aanmaak van de feitentabel

Tenslotte dient de FACT-tabel nog gemaakt te worden:

drop view xxx.fact_covid_aantallen_gemeente_per_dag_v

create view xxx.fact_covid_aantallen_gemeente_per_dag_v 
as
select  TOTAL_REPORTED , HOSPITAL_ADMISSION, DECEASED, DATE_OF_PUBLICATION 
, coalesce ( g.id ,-1 ) DIM_GEMEENTEN_ID
, coalesce ( p.id ,-1 ) DIM_PROVINCIE_ID
, coalesce ( v.id ,-1 ) DIM_VEILIGHEIDSREGIOCODE_ID
, coalesce ( d.id ,-1 ) DIM_GEZONDHEIDSDIENST_ID
, coalesce ( r.id ,-1 ) DIM_ROAZ_REGIO_ID
, coalesce ( f.id ,-1 ) DIM_DATUM_ID
from xxx.STG_CORONA2 x
left join xxx.DIM_GEMEENTEN g            on (x.municipality_name = g.municipality_name )
left join xxx.DIM_PROVINCIE p            on (x.province = p.province )
left join xxx.DIM_VEILIGHEIDSREGIOCODE v on (x.security_region_code = v.security_region_code )
left join xxx.DIM_GEZONDHEIDSDIENST d    on (x.security_region_code = d.security_region_code )
left join xxx.DIM_ROAZ_REGIO r           on (x.roaz_region = r.roaz_region )
left join xxx.DIM_DATUM_C f              on (x.DATE_OF_PUBLICATION = f.datum )

actualize('| SOURCE fact_covid_aantallen_gemeente_per_dag_v   | TARGET fact_covid_aantallen_gemeente_per_dag   | LOG_INFO full-actualize ')

browse fact_covid_aantallen_gemeente_per_dag

Plotten van de cijfers

Tonen van cijfers uit de data :

select   province, sum(DECEASED) overleden_mensen 
from xxx.STG_CORONA2 
group by province

    plot('| TITLE ETL-functions used | QUERY  select province, sum(DECEASED) from xxx.STG_CORONA2 group by province  | TYPE  HBAR | XLABEL covid slachtoffers  | ') 
    plot('| TITLE ETL-functions used | QUERY  select province, sum(DECEASED) from xxx.STG_CORONA2 group by province  | TYPE  PIE  | XLABEL covid slachtoffers   | ') 
    plot('| TITLE ETL-functions used | QUERY  select province, sum(DECEASED) from xxx.STG_CORONA2 group by province  | TYPE  PIE1 | XLABEL covid slachtoffers   | ') 

Alle componenten voor een datamart zijn nu klaar en kunnen eenvoudig in een FLOW gehangen worden. Door de flow uit te voeren, worden alle ETL's uitgevoerd.

Aanmaak van de etl-flow

record_on('| FLOW PF_COVID |')
  csv_to_stage(' | SOURCE https://data.rivm.nl/covid-19/COVID-19_aantallen_gemeente_per_dag.csv | TARGET STG_CORONA2 | DELIMITER ;   | LOG_INFO inlezen coronadata  | xSHOW | ' )
  actualize('| SOURCE DIM_GEMEENTEN_V             | TARGET DIM_GEMEENTEN | LOG_INFO full-actualize ')
  actualize('| SOURCE DIM_PROVINCIE_V             | TARGET DIM_PROVINCIE | LOG_INFO full-actualize ')
  actualize('| SOURCE DIM_VEILIGHEIDSREGIOCODE_V  | TARGET DIM_VEILIGHEIDSREGIOCODE | LOG_INFO full-actualize ')
  actualize('| SOURCE DIM_GEZONDHEIDSDIENST_V     | TARGET DIM_GEZONDHEIDSDIENST | LOG_INFO full-actualize ')
  actualize('| SOURCE DIM_ROAZ_REGIO_V            | TARGET DIM_ROAZ_REGIO | LOG_INFO full-actualize ')
  actualize('| SOURCE GRIP_DATUM_V                | TARGET DIM_DATUM_C       | LOG_INFO full-actualize ')
  actualize('| SOURCE fact_covid_aantallen_gemeente_per_dag_v   | TARGET fact_covid_aantallen_gemeente_per_dag   | LOG_INFO full-actualize ')
record_off( )

show flow

-- starten van de flow 
 flow_run('| FLOW PF_COVID |')
 repeat 10 flow_run('| FLOW PF_COVID |')

Scheduler

Tot slot kan de FLOW in een scheduler opgenomen worden en kan de flow op gezette tijden uitgevoerd worden. Onderstaande aanroep kan opgenomen worden in de windows-scheduler. Omdat de metadata in een van de connections zit, dient in de aanroep dit doorgegeven te worden.

-- aanroep vanaf de commandline: 
grip.exe -m exec  -l "| COMMAND   flow_run('| FLOW PF_COVID |') | CONNECTION GW_grip  |"

Wil men een flow aftrappen terwijl men door wil werken, kan dat middels ECALL. De FLOW wordt gestart een een nieuwe sessie terwijl je verder kan werken in de oude sessie.

-- aanroep vanuit een grip.exe 
ECALL flow_run('| FLOW PF_COVID |') 

Datavault

Voor datavault is het volgende voorbeeld te aanschouwen : 3 views voor de HUB's :

Hubs

create  view H_PROVINCIE_V
as
  select  province  PROVINCIE ,  'H_PROVINCIE_xxV' record_source, 'YesSir' omschr
  from xxx.STG_CORONA2 
  group by province

create or replace view H_WOONPLAATS_V
as
  select  municipality_name woonplaats , 'H_WOONPLAATS_V' RECORD_SOURCE , current_date  LOAD_DTS , sum(deceased) deceased, sum(total_reported) total_reported
  from  xxx.STG_CORONA2 
  group by municipality_name

create or replace view H_GEZONDHEIDSDIENST_V
as
  select  roaz_region  gezondheidsdienst  , 'H_GEZONDHEIDSDIENST_V' RECORD_SOURCE  , current_date  LOAD_DTS , sum(deceased) deceased, sum(total_reported) total_reported
  from  xxx.STG_CORONA2 
  group by roaz_region

En 2 views voor de links :

Links

create or replace view L_PROVINCIE_WOONPLAATS_V
as
  select municipality_name woonplaats ,province provincie  , sum(deceased) deceased, sum(total_reported) total_reported
  from  xxx.STG_CORONA2 
  group by municipality_name   ,province 

create or replace view L_PROV_WOONPL_GZHD_V
as
  select municipality_name woonplaats ,province provincie  , roaz_region  gezondheidsdienst, sum(deceased) deceased, sum(total_reported) total_reported
  from  xxx.STG_CORONA2 
  group by municipality_name   ,province ,roaz_region   

De aanroep van de ETL-routines is dan als volgt :

Processing van de datavault-ETL's..

actualize_hub  ( '| SOURCE H_PROVINCIE_V            | TARGET H_PROVINCIE            | SAT_IS HS_PROVINCIE            | MERGE_KEYS PROVINCIE         |  SHOW ETL |')
actualize_hub  ( '| SOURCE H_WOONPLAATS_V           | TARGET H_WOONPLAATS           | SAT_IS HS_WOONPLAATS           | MERGE_KEYS WOONPLAATS        |  SHO WETL |')
actualize_hub  ( '| SOURCE H_GEZONDHEIDSDIENST_V    | TARGET H_GEZONDHEIDSDIENST    | SAT_IS HS_GEZONDHEIDSDIENST    | MERGE_KEYS GEZONDHEIDSDIENST |  SHO WETL |')
--
actualize_link ( '| SOURCE L_PROVINCIE_WOONPLAATS_V | TARGET L_PROVINCIE_WOONPLAATS | SAT_IS LS_PROVINCIE_WOONPLAATS | HUB_LIST H_PROVINCIE, H_WOONPLAATS | SHOW ETL | ')
actualize_link ( '| SOURCE L_PROV_WOONPL_GZHD_V     | TARGET L_PROV_WOONPL_GZHD     | SAT_IS LS_PROV_WOONPL_GZHD     | HUB_LIST H_PROVINCIE, H_WOONPLAATS ,H_GEZONDHEIDSDIENST | SHOWETL | ')