cursus_postgresql.grp
VirtualPrivateServer
Op een VPS is GRIP geïnstalleerd, samen met een PostgreSQL-14 database. Door GRIP op te starten, kun je snel de opdrachten van deze cursus uitvoeren en ontdekken hoe efficiënt je met GRIP een datawarehouse kunt opzetten. Zelfs kleine gegevensbewerkingen, zoals het verwerken van bankmutaties om maandelijks een lijst van regelmatige en minder regelmatige contributiebetalers voor een vereniging te verkrijgen, kunnen eenvoudig worden uitgevoerd op de VPS.
De enige voorwaarde is dat je handig bent met SQL. Met deze cursus kun je de benodigde vaardigheden met GRIP ontwikkelen.
De VPS kan volledig worden afgeschermd, zodat alleen jij toegang hebt, terwijl GRIP de gegevens verwerkt op systemen binnen jouw firewall of een compleet warehouse op de VPS-database. De VPS fungeert als een soort datawarehouse-as-a-service. Naar behoefte kan de VPS worden geïntegreerd met jouw systemen voor gegevensverwerking, waarbij de wachtwoorden veilig zijn opgeborgen in Keypass op de VPS.
Ook kan bijvoorbeeld powerBI op de VPS beschikbaar gesteld worden : Met weinig inspanning en kosten verse dashboards !
Indien ons IP-adres ook wordt opgenomen in de firewall, kunnen we het beheer uitvoeren en functionaliteiten toevoegen.
Mocht je interesse hebben in deze cursus, dan kun je vrijblijvend gebruikmaken van een studie-VPS. Gedurende een bepaalde periode kun je beschikken over een VPS en wanneer je met GRIP verder wilt, kun je het VPS-contract over nemen. Ook kan eenvoudig de GRIP-functionaliteit gemigreerd worden naar je eigen infra .
GRIP in de tijd ..
GRIP in de tijd ..
- 2004 - LNV, mestbeleid, gele oorvlappen, Schapen, Geiten, Fosfaat
- 2007 - TKP, datawarehouse pensioen-branche
- 2009 - Royal Schiphol Group, datawarehouse voor parkeren,retail,vluchten,vastgoed,ITSM,Vertrekhal-drukte
- 2015 - RIVM, Praemis rijksvaccinatie-datawarehouse
- 2016 - Achmea Syntrus vastgoed datawarehouse
- 2019 - Royal Smilde Heerenveen
- 2020 - RIVM, Corona-vaccinatie datawarehouse
- 2021 - Lekkerkerker rijplaten
- 2021 - Koopmans meel
- 2022 - Gemeente Westerkwartier
- 2022 - Probo Dokkum
de CURSUS
In deze cursus komen alle aspecten wel aan de orde welke terugkomen in een datawarehouse. Aanvankelijk bestond GRIP uit "ETL-functie-bouwstenen". Deze bouwstenen kun je aanroepen waarbij je de BRON-VIEW en TARGET-tabelnaam als parameter mee geeft.
De functie werkt op efficiente wijze de TARGET-tabel bij op basis van de data van de BRON-view.
Er zijn slechts een paar soorten ETL-functies nodig voor een héél datawarehouse. Deze functies processen de data op efficiente wijze. De business-logica zit in de VIEWS en alles wordt met een eenvoudige editor geregeld
- trunc_insert ()
- insert_append ()
- actualize_t1 ()
- actualize_t2 ()
- actualize ()
- rid_update ()
- rid_delete ()
- actualize_hub ()
- actualize_link ()
Bovenstaande functies worden in de cursus uitgelegd en gebruikt....
- Hoe werkt de editor
- wat is ODBC en hoe maak je een connectie
- wat is de grip_con.dat
Behalve de "ETL-functies" zijn er ook "PROCESSING-functies" toegevoegd : een soort lowcode-procedurele taal voor automatiseren van de data binnen de organisatie. Ook bestaan er enkele "HTML-functies" waarmee je eenvoudig bv. data-entry-schermen kunt realiseren, dashboards en dergelijke ..
De commando's worden uitgevoerd op een postgresql-14 database.
Geen gesleur en gepleur
Door het werken met de editor oogt GRIP gedateerd. SQL, wat staat voor "Structured Query Language", is ontstaan in de vroege jaren 1970. Met veel ETL-tools kun je modern en grafisch je ETL's ontwerpen maar in de praktijk ben je beter af door de business-logica toch in sql-code te scripten. Maar zodra je deze business-logica in views hebt zitten, ben je met GRIP ook al klaar: GRIP doet de rest.
Een groot voordeel van GRIP is ETL-automation. Wanneer je 100 bron-tabellen over moet halen naar je STAGE ben je in een kwartier al klaar terwijl je grafisch 2 weken bezig bent. Een ander voordeel is dat versiebeheer op sql-code als text goed mogelijk is ..
Belangrijke toetsen ...
De editor is heel eenvoudig van opzet. Middels een paar toetsen/combinaties kun je snel werken en ontwikkelen ..
de knoppen boven de editor :
- execute : uitvoeren van de current-regel of de geselecteerde regels
- Refresh metadata : de locale metadataset verversen met de metadata van de database.
- MyMenu : aanroep van een eigen menu met commando's voor gemak
- Connectie : knop voor connectie-selectie
- Logging window : vast of los logging-window.
- CTRL-ENTER -> uitvoeren current regel
- SHIFT-ENTER -> selecteren aansluitende regels
- SHIFT-ENTER -> uitvoer geselecteerde regels
- F4 : ga op tabel of view staan met cursor en druk op F4 voor browse data .. bv GRIP_DATUM_V
F4 is een dataobject-browser waarmee je het te onderzoeken object kunt bekijken. Maar de browser kent ook enkele tabjes voor bijvoorbeeld bekijken van log-tabellen of running jobs.
Bijvoorbeeld view grip_datum_v kan gebruikt worden voor DIM_CALENDAR, wordt standaard meegelevert, en kan met F4 bekeken worden.
ODBC-connecties ...
Het doel van ODBC is om applicaties, zoals GRIP, in staat te stellen om databases te benaderen, ongeacht het specifieke databasebeheersysteem (DBMS) dat wordt gebruikt.
Met ODBC kan met veel bronnen geconnect worden zoals bijvoorbeeld mysql,oracle,postgresql,msaccess.
Met window-app ODBC-ADMIN-64 kun je de drivers en connecties opzetten. Wanneer je op deze wijze een succesvolle connectie tot stand brengt, kun je de bijbehorende parameters gebruiken voor de connectie van GRIP met de bron. In grip_con.dat wordt deze connectie gedefineerd maar kan ook middels het "add_conn()" commando.
cmd odbcad32.exe ODBC Data Sources (64-bit)
Vanuit het script kan dynamisch een connectie opgebouwd worden :
aanmaak van 2 connecties :
de ODBC_connectie-gegevens staan statisch in de grip_con.dat maar kunnen ook dynamisch geopend en gesloten worden.
In de grip_con.dat staan statische connecties: ...
connection = postgresql
message = Connecting to Postgresql laptop ....
server = localhost
database = DATAWAREHOUSE
username = demo_user
schema = stg
grip_schema = grip
port = 5432
password = gripopdata
odbcDsn = {PostgreSQL ODBC Driver(ANSI)}
con_type = postgresql
met add_conn() kunnen tijdelijke connecties opgezet worden en weer verwijderd ..
add_conn ('| CONNECTION MyDemo | CON_TYPE postgresql | MESSAGE Pg | SERVER 127.0.0.1 | PORT 5432 | DATABASE DATAWAREHOUSE | USERNAME demo_user | SCHEMA stg | GRIP_SCHEMA grip | PASSWORD gripopdata | ODBCDSN {PostgreSQL ODBC Driver(ANSI)} |')
add_conn ('| CONNECTION 2e_MyDemo | CON_TYPE postgresql | MESSAGE Pg | SERVER 127.0.0.1 | PORT 5432 | DATABASE DATAWAREHOUSE | USERNAME demo_user | SCHEMA stg | GRIP_SCHEMA grip | PASSWORD gripopdata | ODBCDSN {PostgreSQL ODBC Driver(ANSI)} |')
add_conn ('| CONNECTION 3e_MyDemo | CON_TYPE postgresql | MESSAGE Pg | SERVER 127.0.0.1 | PORT 5432 | DATABASE DATAWAREHOUSE | USERNAME demo_user | SCHEMA stg | GRIP_SCHEMA grip | PASSWORD gripopdata | ODBCDSN {PostgreSQL ODBC Driver(ANSI)} |')
set dbf MyDemo
set dbf MyDemo NOLOG
DISP huidige directory is: [[CR]] [[__]] [[CR]] [[CURRENT_DIR]] [[CR]] [[CURRENT_CONNECTION]]
show dbf
close_conn('| CONNECTION 2e_MyDemo |')
close_conn('| CONNECTION 3e_MyDemo |')
set dbf MyDemo
Diverse bestanden ...
LETOP : tot de : selecteren
cmd notepad [[CURRENT_DIR]]/grip_con.dat : voor registratie van de ( statische ) connecties
cmd notepad [[CURRENT_DIR]]/config/gui_edt_f4.json : voor configuratie van de F4-Objectbrowser
cmd notepad [[CURRENT_DIR]]/start_grip.bat : batchfile voor starten van de GRIP-tool
cmd notepad [[CURRENT_DIR]]/start_grip_job.bat : starten van een GRIP-FLOW of PROCEDURE vanuit bv de scheduler
Start van de PGAdmin 4..
voer uit : 'pgAdmin 4' links onder in, en de pgAdmin-browser wordt gestart
het wachtwoord dat gebruikt kan worden is gripopdata
ga via de linker panel naar 'databases' en selecteer database DATAWAREHOUSE.
ga verrvolgens naar schema's en zie daar 2 schema's : grip en stg ...
deze 2 schema's en de naam van de database zijn in de grip_con.dat gedefinieerd.
open via 'tools' 'Query tools' en in de rechter panel opent de editor voor je queries.
voeruit:
set search_path to stg,grip
onderstaande querie geeft GRIP info over de connectie-situatie .. middels search_path postgresql de objecten ... anders kijkt hij default in public ..
select * from grip_info_v
create table myfirsttable as select * from grip_info_v
objecten worden nu default aangemaakt in je 'current-schema'
verder heeft iedere DBMS wel zijn eigen invulling aan login,database en schema ..
Postgresql, Mysql, Oracle en Snowflake hebben allemaal kleine verschillen ..
CBS_OPENDATA
CBS heeft een 'API' welke embedded is als commando in GRIP: het commando is CBS_OPENDATA Wanneer je dit commando uitvoert, wordt de inhoudsopgave van opendata-sets in tabel CBS_OPENDATA geplaatst. Het commando en tabelnaam hebben dezelfde naam. De tabel kun je uitvragen voor bestudering van welke datasets er beschikbaar zijn.
voer onderstaande commando uit, doorloop is ca 13,8 seconden
CBS_OPENDATA 13.8 seconden , ga op het commando staan en druk op F4
select * from xxx.CBS_OPENDATA where lower(title) like '%energie%'
-> deze 2 identifiers kunnen we bijvoorbeeld inlezen :
4 2023-10-02T02:00:00 593 85697NED Energieverbruik particuliere woningen; woningtype, wijken en buurten, 2022 Energieverbruik woningen; wijkbuurt 2022 \nDeze tabel geeft regionale gegevens over het gemiddelde energieverbruik per woning (aardgas en elektriciteit) van particuliere woningen onderverdeeld naar verschillende woningtypen en type eigendom voor alle wijken en buurten in Nederland en voor totaal Nederland. Daarnaast is alleen voor totaal woningen het percentage stadsverwarming opgenomen, omdat dit relevant is voor de interpretatie van de hoogte van het gemiddeld aardgasverbruik.\n\nGegevens beschikbaar: over 2022.\n\nStatus van de cijfers:\nVoorlopig.\n\nWijzigingen per oktober 2023:\nGeen, dit is een nieuwe tabel.\n\nWanneer komen er nieuwe cijfers?\nIn het derde kwartaal volgend op het verslagjaar worden geactualiseerde cijfers gepubliceerd.\n\n Gemiddeld gas- en elektriciteitsverbruik, woningtype, stadsverwarming\nregio 2023-10-02T02:00:00 2023-10-02T02:00:00 Nieuw Regulier CBS. nl CBS Eenmalig 2022 Gemiddeld gas- en elektriciteitsverbruik, woningtype, stadsverwarming<br />regio<br /><a href="http://opendata.cbs.nl/ODataApi/OData/85697NED">http://opendata.cbs.nl/ODataApi/OData/85697NED</a><br /><a href="http://opendata.cbs.nl/ODataFeed/OData/85697NED">http://opendata.cbs.nl/ODataFeed/OData/85697NED</a> https://opendata.cbs.nl/ODataApi/OData/85697NED https://opendata.cbs.nl/ODataFeed/OData/85697NED ts=1695890186300&graphtype=Table&r=Woningkenmerken,WijkenEnBuurten&k=Topics&_gu=NL $filter=((Woningkenmerken eq 'T001100') or (Woningkenmerken eq 'ZW25810') or (Woningkenmerken eq 'ZW25805') or (Woningkenmerken eq 'ZW25806') or (Woningkenmerken eq 'ZW10300') or (Woningkenmerken eq 'ZW10320')) and ((WijkenEnBuurten eq 'GM1659 ') or (WijkenEnBuurten eq 'GM0957 ') or (WijkenEnBuurten eq 'GM0858 ') or (substringof('NL',WijkenEnBuurten)))&$select=Woningkenmerken, WijkenEnBuurten, Gemeentenaam_1, GemiddeldAardgasverbruik_4, GemiddeldeElektriciteitslevering_5, GemiddeldeNettoElektriciteitslevering_6, Stadsverwarming_7, IndelingswijzigingWijkenEnBuurten_8 Table,Map 144024 10 2
5 2023-10-02T02:00:00 594 85359NED Energieverbruik particuliere woningen; woningtype, wijken en buurten, 2021 Energieverbruik woningen; wijkbuurt 2021 \nDeze tabel geeft regionale gegevens over het gemiddelde energieverbruik per woning (aardgas en elektriciteit) van particuliere woningen onderverdeeld naar verschillende woningtypen en type eigendom voor alle wijken en buurten in Nederland en voor totaal Nederland. Daarnaast is alleen voor totaal woningen het percentage stadsverwarming opgenomen, omdat dit relevant is voor de interpretatie van de hoogte van het gemiddeld aardgasverbruik.\n\nGegevens beschikbaar: over 2021.\n\nStatus van de cijfers:\nDefinitief.\n\nWijzigingen per oktober 2023:\nCijfers van 2021 zijn geactualiseerd en hebben de status definitief gekregen. De bestaande categorie "Gemiddeld elektriciteitsverbruik" is hernoemd tot "Gemiddelde elektriciteitslevering". Daarnaast is er een categorie "Gemiddelde netto elektriciteitslevering" toegevoegd.\n\nWanneer komen er nieuwe cijfers?\nEr komen geen nieuwe cijfers. Dit zijn de definitieve cijfers.\n Gemiddeld gas- en elektriciteitsverbruik, woningtype, stadsverwarming\nregio 2023-10-02T02:00:00 2023-10-02T02:00:00 Herontwerp Cijfers van 2021 zijn geactualiseerd en hebben de status definitief gekregen. De bestaande categorie "Gemiddeld elektriciteitsverbruik" is hernoemd tot "Gemiddelde elektriciteitslevering". Daarnaast is er een categorie "Gemiddelde netto elektriciteitslevering" toegevoegd. Regulier CBS. nl CBS Eenmalig 2021 Gemiddeld gas- en elektriciteitsverbruik, woningtype, stadsverwarming<br />regio<br /><a href="http://opendata.cbs.nl/ODataApi/OData/85359NED">http://opendata.cbs.nl/ODataApi/OData/85359NED</a><br /><a href="http://opendata.cbs.nl/ODataFeed/OData/85359NED">http://opendata.cbs.nl/ODataFeed/OData/85359NED</a> https://opendata.cbs.nl/ODataApi/OData/85359NED https://opendata.cbs.nl/ODataFeed/OData/85359NED ts=1695889990422&graphtype=Table&r=WijkenEnBuurten&k=Topics&t=Woningkenmerken&_gu=WK $filter=((Woningkenmerken eq 'T001100')) and ((WijkenEnBuurten eq 'GM0363 ') or (WijkenEnBuurten eq 'WK036302 ') or (WijkenEnBuurten eq 'GM0794 ') or (WijkenEnBuurten eq 'WK079413 ') or (WijkenEnBuurten eq 'GM1659 ') or (WijkenEnBuurten eq 'GM1621 '))&$select=Woningkenmerken, WijkenEnBuurten, GemiddeldAardgasverbruik_4, GemiddeldeElektriciteitslevering_5, GemiddeldeNettoElektriciteitslevering_6, Stadsverwarming_7, IndelingswijzigingWijkenEnBuurten_8 Table,Map 141448 10 2
Ophalen van een specifieke dataset, bv 8597NED ... voer onderstaanden aanroepen uit ...
CBS_OPENDATA_READ 85697NED 60 sec
CBS_OPENDATA_READ 85359NED
select * from CBS_OPENDATA_85697NED limit 10
select * from CBS_OPENDATA_85359NED limit 10
ETL-automation : voer onderstaande in de editor uit .. ( let op de kwooten ... )
sqlcall("| SQL select identifier from xxx.CBS_OPENDATA where lower(title) like '%energie%' | CALL verwerk_regel | ")
BEGIN verwerk_regel
DISP_ CBS_OPENDATA_READ [[identifier]]
RETURN
middels de record_on-constructie maak je van de output een flow ..
record_on('| FLOW PF_READ_CBS | CONNECTION MyDemo |')
CBS_OPENDATA_READ 70135ned
CBS_OPENDATA_READ 80382ned
CBS_OPENDATA_READ 83109NED
....
record_off()
LIANDER
Veel bedrijven beieden opendata aan, zo ook LIANDER. Middels https://www.liander.nl/partners/datadiensten/open-data/data kom je op hun site en kun je de data downloaden.
cmd explorer https://www.liander.nl/over-ons/open-data
zorg ervoor dat het bestand op bc c:/data/cursus komt te staan ...
dir c:/data/cursus
csv_to_stage( ' | SOURCE c:/data/cursus/opendata2022.csv | TARGET stg_liander_2022 | DELIMxITER , | LOG_INFO inlezen liander opendata | xSHOW | ' )
csv_to_stage( ' | SOURCE c:/data/cursus/opendata_2023_v3.csv | TARGET stg_liander_2023 | DELIMxITER , | LOG_INFO inlezen liander opendata | xSHOW | ' )
QRY
select * from xxx.stg_liander_2023
limit 10
SHOW;
voor andere formaten is een een specifieke ETL-functie :
json_to_stage ( '||')
xml_to_stage ( '||')
of
rss_to_stage ( '| SOURCE https://www.ad.nl/reizen/rss.xml | TARGET stg_rss_reizen | SHOW | SHOWRES | ' )
rss_to_stage ( '| SOURCE https://www.nu.nl/feeds/rss/algemeen.rss | TARGET stg_rss_nu | SHOW |' )
rss_to_stage ( '| SOURCE https://www.nu.nl/feeds/rss/economie.rss | TARGET stg_rss_nu_economie | SHOW |' )
voer onderstaand commando uit voor meer rss-feeds ...
cmd explorer https://rss.startpagina.nl
INSTALL
Nadat grip.zip is uitgepakt, de grip_con.dat voorzien is van een valide connectie, kan GRIP.exe opgestart worden.
Het eerste dat gebeuren moet is het commando INSTALL uitvoeren. Met dit commando worden alle benodigde GRIP-objecten aangemaakt.
Na de succesvolle INSTALL is GRIP operationeel en kun je van start met ETL-processen. Alle acties worden gelogd in de logtabellen
Met UNINSTALL wordt er een scriptje gegenereerd waarmee je de GRIP-objecten kunt verwijderen .
GRIP-log tabellen ...
De volgende log-tabellen zijn aanwezig:
- grip_log : voor noodzakelijke loging van o.a. de ETL-stappen ..
- grip_debug : debug-informatie
- grip_job, grip_job_run en grip_job_run_hist : flow informatie logging
- grip_aud : van succesvolle ETL wordt doorloop en aantallen
- grip_para : parameters worden in deze tabel opgeslagen.
Verder zijn er een paar views beschikbaar ..
- grip_metadata_v
- grip_datum_v
- grip_info_v
select * from grip_log order by id desc limit 20
select * from grip_debug order by id desc limit 20
browse select * from grip_log order by start_dt desc limit 10
F4 uitvoeren op tabel ( de cursor knipperend een tabelnaam ...)
browse grip_debug
select * from xxx.grip_aud order by start_dt desc limit 10
Creatie van een FLOW
Het maken of vervangen van een FLOW gaat heel eenvoudig. Terwijl je de ETL-functies en andere commando's gewoon uit kunt voeren, kun je middels record_on,record_off er om heen plaatsen, die commandos onderbrengen in een FLOW. In de record_on-modus doen de commando's niet hun ding maar worden ze opgenomen als onderdeel van de FLOW. De volgorde van uitvoeren vanuit de FLOW is identiek aan de volgorde van opnemen. De FLOW-metadata vind je terug in grip_job.
selecteer de record_on .. regels en click op 'execute'
of
ga op een van de regels staan: shift-enter (2x)
LETOP: op te geven connection moet in de grip_con.dat aanwezig zijn. De grip_con.dat wordt ingelezen bij opstart, zodat de flow via die connection gevonden wordt ..
record_on('| FLOW PF_LIANDER | CONNECTION postgresql |')
csv_to_stage( ' | SOURCE c:/data/cursus/opendata2022.csv | DROP | TARGET stg_liander_2022 | DELIMITER tab | HEADERLINE 0 | LOG_INFO inlezen liander opendata 25.11 sec | xSHOW | ' )
csv_to_stage( ' | SOURCE c:/data/cursus/opendata_2023_v3.csv | TARGET stg_liander_2023 | DELIMxITER , | LOG_INFO inlezen liander opendata 27,67 sec | xSHOW | ' )
record_off()
#zoek je job-records op ...
select * from grip_job
Het starten van de flow kan op twee manieren : middels flow_run() of frun() .. Met Frun wordt de FLOW in een eigen sessie uitgevoerd zodat je verder kunt werken in de editor ..
flow_run('| FLOW PF_LIANDER | LOG_INFO duurt 53,6 seconden |')
of
frun('| FLOW PF_LIANDER | LOG_INFO duurt 53,6 seconden |')
Voor verwijderen van een job :
flow_drop('| FLOW PF_LIANDER | LOG_INFO duurt 53,6 seconden |')
in grip_aud zie de resultaten van de ETL, in grip_job_run_hist de resultaten van de flow ..
select * from grip_aud order by start_dt desc limit 10
Dimensionele data.
We kennen de LIANDER-data nog niet goed en middels verkennende queries gaan we op zoek naar de structuur, op zoek naar dimensionele en transactionele data ... Hiervoor kunnen we onderstaande queries stuk voor stuk uitvoeren ..
select * from STG_LIANDER_2023 limit 10
** in de output zie je de select-querie herhaald met ook de bron-attributen voor het gemak voor koieeren.
**
select NETBEHEERDER,NETGEBIED,STRAATNAAM,POSTCODE_VAN,POSTCODE_TOT,WOONPLAATS,LANDCODE,PRODUCTSOORT,VERBRUIKSSEGMENT,AANSLUITINGEN_AANTAL,LEVERINGSRICHTING_PERC,FYSIEKE_STATUS_PERC,SOORT_AANSLUITING_PERC,SOORT_AANSLUITING,SJV_GEMIDDELD,SJV_LAAG_TARIEF_PERC,SLIMME_METER_PERC from STG_LIANDER_2023 limit 10
select distinct NETBEHEERDER from STG_LIANDER_2023
select distinct NETGEBIED from STG_LIANDER_2023
select distinct STRAATNAAM,POSTCODE_VAN,POSTCODE_TOT,WOONPLAATS from STG_LIANDER_2023
select distinct LANDCODE,PRODUCTSOORT,VERBRUIKSSEGMENT from STG_LIANDER_2023
select distinct AANSLUITINGEN_AANTAL from STG_LIANDER_2023
middels procedure-aanroep icm parameters is onderstaand een voorbeeld van ETL-automation
CALL SNUFFEL PARAMETERS NETBEHEERDER
CALL SNUFFEL PARAMETERS LANDCODE,PRODUCTSOORT,VERBRUIKSSEGMENT
CALL SNUFFEL PARAMETERS AANSLUITINGEN_AANTAL
BEGIN SNUFFEL
DISP velden: [[P1]]
QRY
select [[P1]] , sum(1) Aantal from STG_LIANDER_2023 group by [[P1]] order by 2 desc
SHOW;
RETURN
ENEXIS-data
ENEXIS heeft óók opendata die we rechstreeks van hun site kunnen ophalen richting de STAGE (STG).
csv_to_stage( '| SOURCE https://s3-eu-west-1.amazonaws.com/enxp433-oda01/kv/Enexis_kleinverbruiksgegevens_01012023.csv | TARGET stg_kverbruik_2023 | LOG_INFO 25,1 sec | ')
csv_to_stage( '| SOURCE https://s3-eu-west-1.amazonaws.com/enxp433-oda01/kv/Enexis_kleinverbruiksgegevens_01012022.csv | TARGET stg_kverbruik_2022 |')
csv_to_stage( '| SOURCE https://s3-eu-west-1.amazonaws.com/enxp433-oda01/kv/Enexis_kleinverbruiksgegevens_01012021.csv | TARGET stg_kverbruik_2021 |')
#
csv_to_stage( '| SOURCE https://enxp433-oda01.s3.eu-west-1.amazonaws.com/KV_opwek/Enexis_decentrale_opwek_kv_(zon_pv)_01072023.csv | TRUNCATE | TARGET stg_kopwek_2023_71 |')
csv_to_stage( '| SOURCE https://enxp433-oda01.s3.eu-west-1.amazonaws.com/KV_opwek/Enexis_decentrale_opwek_kv_(zon_pv)_01012023.csv | DROP | TARGET stg_opwek_2023_11 |')
LETWEL, indien een tabel nog niet bestaat, wordt hij aangemaakt op basis van de eerste CSV. Wordt een tweede csv geladen met grotere waaarden, zal de ETL-functie in error raken met een error-melding van 'value too large' ... Een mogelijkheid is dan de tabel te droppen en opnieuw aan te laten maken op basis vn die tweede aanlevering.
Soms is een attribuut te klein, kun je hem vergroten middels DBEXEC ....
DBEXEC ALTER TABLE grip_log MODIFY COLUMN source VARCHAR(1000)
Let altijd goed op: Refresh metadata, button links boven de editor, is voor de sql-generator : wanneer je een tabel dropt, dien je de metadata te verversen.
RDW-opendata
Hoewel de set zon 17miljoen records kent, krijgen we maar per fetch 1000 verschillende records.
csv_to_stage( '| SOURCE https://opendata.rdw.nl/resource/m9d7-ebf2.csv | DROP | TARGET stg_rdw_gkvt_1 |')
csv_to_stage( '| SOURCE https://opendata.rdw.nl/resource/m9d7-ebf2.csv | DROP | TARGET stg_rdw_gkvt_2 |')
csv_to_stage( '| SOURCE https://opendata.rdw.nl/resource/m9d7-ebf2.csv | DROP | TARGET stg_rdw_gkvt_3 |')
csv_to_stage( '| SOURCE https://opendata.rdw.nl/resource/m9d7-ebf2.csv | DROP | TARGET stg_rdw_gkvt_4 |')
csv_to_stage( '| SOURCE https://opendata.rdw.nl/resource/m9d7-ebf2.csv | DROP | VARCHAR 250 | TARGET stg_rdw_gkvt_5 | | ')
REPEAT 10 csv_to_stage( '| SOURCE https://opendata.rdw.nl/resource/m9d7-ebf2.csv | TARGET stg_rdw_gkvt_5 |')
select count(*) from STG_RDW_GKVT_5
Soms is de target-tabel initieel te klein gemaakt .. Met onderstaande kunnen de attributen opgerekt worden ...
shift-enter 2x in onderstaande commando's
DBEXEC ALTER TABLE stg_rdw_gkvt_5 alter COLUMN eerste_kleur type VARCHAR(300)
DBEXEC ALTER TABLE stg_rdw_gkvt_5 alter COLUMN tweede_kleur type VARCHAR(300)
DBEXEC ALTER TABLE stg_rdw_gkvt_5 alter COLUMN typegoedkeuringsnummer type VARCHAR(300)
DBEXEC ALTER TABLE stg_rdw_gkvt_5 alter COLUMN handelsbenaming type VARCHAR(300)
DBEXEC ALTER TABLE stg_rdw_gkvt_5 alter COLUMN variant type VARCHAR(300)
DBEXEC ALTER TABLE stg_rdw_gkvt_5 alter COLUMN voertuigsoort type VARCHAR(300)
DBEXEC ALTER TABLE stg_rdw_gkvt_5 alter COLUMN inrichting type VARCHAR(300)
DBEXEC ALTER TABLE stg_rdw_gkvt_5 alter COLUMN merk type VARCHAR(300)
DBEXEC ALTER TABLE stg_rdw_gkvt_5 alter COLUMN verticale_belasting_koppelpunt_getrokken_voertuig type VARCHAR(300)
DBEXEC ALTER TABLE stg_rdw_gkvt_5 alter COLUMN wam_verzekerd type VARCHAR(300)
DBEXEC ALTER TABLE stg_rdw_gkvt_5 alter COLUMN uitvoering type VARCHAR(300)
Om de snelheid van ETL-automation er in te houden, kan de csv_to_stage de parameter "VARCHAR 200" meegegeven worden waardoor alle varchar-attributen deze lengte krijgen.
DBEXEC drop table stg_rdw_gkvt_5
REFRESH -- uitvoeren zodat de grip-metadata gesynchroniseerd is met de database-situatie
REPEAT 10 csv_to_stage( '| SOURCE https://opendata.rdw.nl/resource/m9d7-ebf2.csv | VARCHAR 250 | TARGET stg_rdw_gkvt_5 |')
REPEAT 10 DISP [REPEAT]
REPEAT 5 csv_to_stage( '| SOURCE https://opendata.rdw.nl/resource/m9d7-ebf2.csv | VARCHAR 250 | TARGET stg_rdw_gkvt_5[REPEAT] |')
select count(*) from STG_RDW_GKVT_5
We hebben 4x een fetch gedaan maar soms is er wat overlap tussen de tabellen 1..4
Dat kan met onderstaande query inzichtelijk verkregen worden ..
SHIFT-enter & SHIFT-enter
onderste regel : CTRL-enter
select count(*) aantal_verschillend from stg_rdw_gkvt_1 a where a.kenteken not in ( select kenteken from stg_rdw_gkvt_2 ) union all
select count(*) from stg_rdw_gkvt_1 a where a.kenteken not in ( select kenteken from stg_rdw_gkvt_3 ) union all
select count(*) from stg_rdw_gkvt_1 a where a.kenteken not in ( select kenteken from stg_rdw_gkvt_4 ) union all
select count(*) from stg_rdw_gkvt_3 a where a.kenteken not in ( select kenteken from stg_rdw_gkvt_4 ) union all
select count(*) from stg_rdw_gkvt_2 a where a.kenteken not in ( select kenteken from stg_rdw_gkvt_5 )
Basis-ETL-routines ...
Hieronder de 4 meest gebruikte ETL-functies in een datawarehouse. in geval van size-error :
trunc_insert ('| SOURCE stg_rdw_gkvt_1 | TARGET ods_rdw_gkvt | SHOWC ODE | SHOWRES | ')
insert_append ('| SOURCE stg_rdw_gkvt_1 | TARGET ods_rdw_gkvt |')
actualize_t1 ('| SOURCE stg_rdw_gkvt_1 | TARGET ods_rdw_gkvt | SKIPCOLS SUBCATEGORIE_NEDERLAND | MERGE_KEYS kenteken | SHOWETL | |')
actualize_t2 ('| SOURCE stg_rdw_gkvt_1 | TARGET ods_rdw_gkvt | MERGE_KEYS kenteken | SHOWETL | ')
trunc_insert ('| SOURCE stg_rdw_gkvt_1 | TARGET ods_rdw_gkvt | WHERE 1=2 | LOG_INFO leeggooien van de tabel middels 1=2 |')
Wanneer je ze stuk voor stuk uitvoert, kun je met $4 de resultaten bekijken en wat er gelogd wordt.
Ook hier wordt de TARGET-tabel gecreeerd op basis van de bron-dataset. Is een tweede dataset groter kan men de target tabel droppen en opnieuw creeeren op basis van die twee aanlevering.
DBEXEC drop table ods_rdw_gkvt
REFRESH
Additionele parameters ...
Er zijn een aantal additionele parameters die je aan de functie mee kan geven, zoals SHOWRES of SHOWETL of LAST_DATE_PROCESSED .
actualize_t2 ('| SOURCE stg_rdw_gkvt_1 | TARGET ods_rdw_gkvt | MERGE_KEYS kenteken | ')
actualize_t2 ('| SOURCE stg_rdw_gkvt_1 | TARGET ods_rdw_gkvt | MERGE_KEYS kenteken | SHOWRES | SHOWETL | ')
actualize_t2 ('| SOURCE stg_rdw_gkvt_1 | TARGET ods_rdw_gkvt | MERGE_KEYS kenteken | SHOWRES | SHOWETL | ')
actualize_t2 ('| SOURCE stg_rdw_gkvt_1 | TARGET ods_rdw_gkvt | MERGE_KEYS kenteken | SHOWRES | SHOWETL | LAST_DATE_PROCESSED vervaldatum_apk::text::date | DAYSBACK 10 |')
RDW-Dimensies
Nu we de ODS ( operational datastore, bron-kopie met historie ) hebben, kunnen we de ETL's maken voor de DIMENSIES en vervolgens de FACT-tabel ...
business-attributen en GRIP-attributen ...
GRIP-attributen, ofwel proces-attributen worden door grip aan de target-atbellen toegevoegd en gevuld. De GRIP-etl-functies vullen deze proces-attributen met proces-waarden zoals timestamp of proces-nummer. Met deze gegevens kun je enerzijds de ETL-logging in grip_aud koppelen aan de FLOW-logging in grip_job_run_hist. Aan de andere kant kun je gewaar worden welke business-data geraakt is door een specifieke ETL.
Met date_updated kun je selecteren welke data het laatst is geupdated en welke door een vervolg-ETL opgepakt moet worden.
select kenteken
,ID
,DATE_CREATED
,DATE_UPDATED
,DATE_DELETED
,AUD_ID_CREATED
,AUD_ID_UPDATED
,AUD_ID_DELETED
,CURR_ID
from ODS_RDW_GKVT limit 10
De business-attributen:
select * from ods_rdw_gkvt limit 10
select count(*) from ods_rdw_gkvt
F4 op tabel ( ods_rdw_gkvt ) , dubbelclick -> bestudeer de data ... -> bepaal je dimensies ( )
handige navigatie-toetsen ... DUBBEL-click op tekst, ESC, F3 F3
De volgende dimensie-views zijn gemaakt:
BEGIN CRE_VIEWS
CALL dim_merk_v
CALL dim_voertuig_v
CALL dim_kleur_v
CALL dim_prijs_v
CALL dim_deuren_v
CALL dim_gewicht_v
CALL dim_inrichting_v
CALL dim_cilinders_v
RETURN
BEGIN dim_merk_v
QRY
create or replace view dim_merk_v
as
select coalesce(merk,'leeg') merk , sum(1) aantal
from ods_rdw_gkvt
group by merk
union
select 'onbekend' , 1
EXEC;
RETURN
BEGIN dim_voertuig_v
QRY
create or replace view dim_voertuig_v
as
select coalesce(merk,'leeg') merk ,coalesce(handelsbenaming,'leeg') handelsbenaming , sum(1) aantal
from ods_rdw_gkvt
group by merk,handelsbenaming
union
select 'onbekend' , 'onbekend', 1
EXEC;
RETURN
BEGIN dim_kleur_v
QRY
create or replace view dim_kleur_v
as
select coalesce(eerste_kleur,'leeg') kleur , sum(1) aantal
from ods_rdw_gkvt
group by eerste_kleur
union
select 'onbekend' , 1
EXEC_;
RETURN
BEGIN dim_prijs_v
QRY
create or replace view dim_prijs_v
as
select -1 van , -1 tot union
select 1 van , 5000 tot union
select 5000 van , 10000 tot union
select 10000 van , 15000 tot union
select 20000 van , 25000 tot union
select 25000 van , 30000 tot union
select 30000 van , 35000 tot union
select 35000 van , 40000 tot union
select 40000 van , 45000 tot union
select 45000 van , 50000 tot union
select 50000 van , 55000 tot union
select 55000 van , 60000 tot union
select 60000 van , 65000 tot union
select 65000 van , 70000 tot union
select 70000 van , 75000 tot union
select 75000 van , 80000 tot union
select 80000 van , 85000 tot union
select 85000 van , 90000 tot union
select 90000 van , 95000 tot union
select 95000 van , 100000 tot union
select 100000 van , 300000 tot
EXEC;
RETURN
BEGIN dim_deuren_v
QRY
create or replace view dim_deuren_v
as
select coalesce(aantal_deuren,-2) aantal_deuren, sum(1) aantal
from ods_rdw_gkvt
group by aantal_deuren
union
select -1,1
EXEC;
RETURN
BEGIN dim_gewicht_v
QRY
create or replace view dim_gewicht_v
as
with gw as
( select distinct round(coalesce(massa_ledig_voertuig,-1) / 500) * 500 gewicht -- LETOP, geen duplicaten of null-values
from ods_rdw_gkvt
)
select a.gewicht gewicht_van
, coalesce(( select b.gewicht from gw b
where b.gewicht > a.gewicht
and b.gewicht is not null
order by b.gewicht
limit 1
),10000) gewicht_tm
from gw a
order by 1 asc
EXEC;
RETURN
BEGIN dim_inrichting_v
QRY
create or replace view dim_inrichting_v
as
select coalesce(inrichting,'leeg') inrichting , sum(1) aantal
from ods_rdw_gkvt
group by inrichting
EXEC;
RETURN
BEGIN dim_cilinders_v
QRY
create or replace view dim_cilinders_v
as
select coalesce(aantal_cilinders,-1) aantal_cilinders , sum(1) aantal
from ods_rdw_gkvt
group by coalesce(aantal_cilinders,-1)
EXEC;
RETURN
Aanmaak van de dimensie-tabellen
We kunnen de ETL-aanroepen los uitvoeren maar ook opnemen in de FLOW PF_RDW_DIMENSIES Uieindelijk zijn de dimensies aangemaakt en geladen met data, zodat de FACT-view gemaakt kan worden.
flow_run('| FLOW PF_RDW_DIMENSIES | CONNECTION postgresql | ')
record_on('| FLOW PF_RDW_DIMENSIES | CONNECTION postgresql | ')
actualize_t1( '| SOURCE dim_merk_v | TARGET dim_merk | MERGE_KEYS merk | ')
actualize_t1( '| SOURCE dim_voertuig_v | TARGET dim_voertuig | MERGE_KEYS merk,handelsbenaming |')
actualize_t1( '| SOURCE dim_kleur_v | TARGET dim_kleur | MERGE_KEYS kleur | ')
actualize_t1( '| SOURCE dim_prijs_v | TARGET dim_prijs | MERGE_KEYS van,tot |')
actualize_t1( '| SOURCE dim_deuren_v | TARGET dim_deuren | MERGE_KEYS aantal_deuren |')
actualize_t1( '| SOURCE dim_gewicht_v | TARGET dim_gewicht | MERGE_KEYS gewicht_van,gewicht_tm |')
actualize_t1( '| SOURCE dim_inrichting_v | TARGET dim_inrichting | MERGE_KEYS inrichting |')
actualize_t1( '| SOURCE dim_cilinders_v | TARGET dim_cilinders | MERGE_KEYS aantal_cilinders |')
actualize_t1( '| SOURCE grip_datum_v | TARGET dim_datum | MERGE_KEYS datum | ')
record_off()
flow_run('| FLOW PF_RDW_DIMENSIES |')
of
frun('| FLOW PF_RDW_DIMENSIES |')
middels de frun() wordt een nieuwe GRIP.exe gestart die deze flow uitvoert zodat je in deze editor verder kan met werken..
CRE_INDEXEN
Omwille de performance is het raadzaam om op tenminste de PrimaryKey en MergeKeys een index te creeeren. Middels GRIP_PROCESSING kan eenvoudig een compacte functionaliteit geschreven worden om dit te regelen.
CALL MK_INDEXEN PARAMETERS dim_merk merk
CALL MK_INDEXEN PARAMETERS dim_voertuig merk,handelsbenaming
CALL MK_INDEXEN PARAMETERS dim_kleur kleur
CALL MK_INDEXEN PARAMETERS dim_prijs van,tot
CALL MK_INDEXEN PARAMETERS dim_deuren aantal_deuren
CALL MK_INDEXEN PARAMETERS dim_gewicht gewicht_van,gewicht_tm
CALL MK_INDEXEN PARAMETERS dim_inrichting inrichting
CALL MK_INDEXEN PARAMETERS dim_cilinders aantal_cilinders
CALL MK_INDEXEN PARAMETERS dim_datum datum
#
BEGIN MK_INDEXEN
DISP [[P1]] , [[P2]]
# DBEXEC drop table [[P1]]
DBEXEC alter table [[P1]] add primary key (id)
DBEXEC create index [[P1]]_IDX_bk on [[P1]]([[P2]])
RETURN
Aanmaken van de FACT-tabel
CALL CRE_FACT
BEGIN CRE_FACT
DBEXEC SET enable_seqscan = off;
QRY
create or replace view fact_voertuigen_v
as
select 1 aantal
, kenteken
, coalesce(( select id from dim_merk where curr_id = 'Y' and merk = x.merk ) ,-1) dim_merk
, coalesce(( select id from dim_voertuig where curr_id = 'Y' and merk = x.merk and handelsbenaming = x.handelsbenaming ) ,-1) dim_voertuig
, coalesce(( select id from dim_kleur where curr_id = 'Y' and kleur = x.eerste_kleur ) ,-1) dim_kleur
, coalesce(( select id from dim_prijs where curr_id = 'Y' and x.bruto_bpm between van and tot ) ,-1) dim_prijs
, coalesce(( select id from dim_deuren where curr_id = 'Y' and aantal_deuren = x.aantal_deuren ) ,-1) dim_deuren
, coalesce(( select id from dim_gewicht where curr_id = 'Y' and x.massa_ledig_voertuig between gewicht_van and gewicht_tm -1 ),-1) dim_gewicht
, coalesce(( select id from dim_inrichting where curr_id = 'Y' and inrichting = x.inrichting ) ,-1) dim_inrichting
, coalesce(( select id from dim_cilinders where curr_id = 'Y' and aantal_cilinders = x.aantal_cilinders) ,-1) dim_cilinders
, coalesce(( select datum_id from dim_datum where curr_id = 'Y' and datum = x.vervaldatum_apk::text::date ) ,'-1') dim_datum_vervaldatum_apk
, coalesce(( select datum_id from dim_datum where curr_id = 'Y' and datum = x.datum_eerste_toelating::text::date ) ,'-1') dim_datum_datum_eerste_toelating
, coalesce(( select datum_id from dim_datum where curr_id = 'Y' and datum = x.datum_tenaamstelling::text::date ) ,'-1') dim_datum_datum_tenaamstelling
--#, x.*
from ( select * from ods_rdw_gkvt x limit 100000 ) x
EXEC;
RETURN
actualize_t1( '| SOURCE fact_voertuigen_v | TARGET fact_voertuigen | MERGE_KEYS kenteken | TOP 50 | ')
DUMMIES
Nu de fact-tabel er is, kunnen de foreignkeys en dummies aangemaakt worden .
CALL creeer_DUMMIES
#
CALL aanmaak_foreigkey_datum PARAMETERS dim_datum dim_datum_vervaldatum_apk datum_id
CALL aanmaak_foreigkey_datum PARAMETERS dim_datum dim_datum_datum_eerste_toelating datum_id
CALL aanmaak_foreigkey_datum PARAMETERS dim_datum dim_datum_datum_tenaamstelling datum_id
#
CALL aanmaak_foreigkey PARAMETERS dim_merk dim_merk
CALL aanmaak_foreigkey PARAMETERS dim_voertuig dim_voertuig
CALL aanmaak_foreigkey PARAMETERS dim_kleur dim_kleur
CALL aanmaak_foreigkey PARAMETERS dim_prijs dim_prijs
CALL aanmaak_foreigkey PARAMETERS dim_deuren dim_deuren
CALL aanmaak_foreigkey PARAMETERS dim_gewicht dim_gewicht
CALL aanmaak_foreigkey PARAMETERS dim_inrichting dim_inrichting
CALL aanmaak_foreigkey PARAMETERS dim_cilinders dim_cilinders
BEGIN aanmaak_foreigkey_datum
#DISP ALTER TABLE Orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES Customers(customer_id);
DBEXEC ALTER TABLE fact_voertuigen drop CONSTRAINT fk_[[P2]]
DBEXEC ALTER TABLE fact_voertuigen ADD CONSTRAINT fk_[[P2]] FOREIGN KEY ([[P2]]) REFERENCES [[P1]]([[P3]]);
RETURN
BEGIN aanmaak_foreigkey
#DISP ALTER TABLE Orders ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id) REFERENCES Customers(customer_id);
DBEXEC ALTER TABLE fact_voertuigen drop CONSTRAINT fk_[[P1]]
DBEXEC ALTER TABLE fact_voertuigen ADD CONSTRAINT fk_[[P1]] FOREIGN KEY ([[P2]]) REFERENCES [[P1]](id);
RETURN
BEGIN creeer_DUMMIES
DBEXEC update dim_prijs set id = van where van < 0
DBEXEC update dim_deuren set id = aantal_deuren where aantal_deuren < 0
DBEXEC update dim_cilinders set id = aantal_cilinders where aantal_cilinders < 0
DBEXEC update dim_datum set datum_id = -1 where datum_id = (select min(datum_id ) from dim_datum )
RETURN
Mainflow FD_RDW_MAIN
Met onderstaande wordt een volledige flow samengesteld waarbij PF_RDW_MAIN een subflow aanroept.
Zo Simpel kan het zijn ...
record_on('| FLOW PF_RDW_MAIN | CONNECTION postgresql | ')
SLEEP 10
flow_run('| FLOW PF_RDW_DIMENSIES |')
actualize_t1( '| SOURCE fact_voertuigen_v | TARGET fact_voertuigen | MERGE_KEYS kenteken | TOP 5 | ')
sound('x1')
record_off()
flow_run('| FLOW PF_RDW_MAIN | LOG_INFO 166.8 sec. |')
of
frun('| FLOW PF_RDW_MAIN | LOG_INFO 166.8 sec. |')
Platte tabel magic ..
Middels handige postgresql-queries kun je vrij snel nieuwe queries in elkaar monteren .. Onderstaande werkt goed indien de Foreignkeys goed gedefinieerd zijn ..
LETWEL: indien je procedure creeer_DUMMIES, aanmaak_foreigkey_datum en aanmaak_foreigkey uitvoert, werkt onderstaande vanzelf. Normaal is het stermodel het uitgangspunt voor powerbi, maar voor leveren van datasets aan derden, is de basis van de platte-querie het uitgangs-punt ...
MVAR THE_TABLE = fact_voertuigen
select * from ( [[ SCAN SQL_BASE_0 ]] ) x
BEGIN SQL_BASE_0
QRY
SELECT
conname AS constraint_name,
conrelid::regclass AS table_name,
a.attname AS COLUMN_NAME,
confrelid::regclass AS REFERENCED_TABLE_NAME,
af.attname AS REFERENCED_COLUMN_NAME
FROM pg_constraint c
JOIN pg_attribute a ON a.attnum = ANY(c.conkey) AND a.attrelid = c.conrelid
JOIN pg_attribute af ON af.attnum = ANY(c.confkey) AND af.attrelid = c.confrelid
WHERE conrelid = '[[THE_TABLE]]'::regclass
SHOW;
RETURN
BEGIN SQL_BASE1
QRY
select concat('join ' , concat(REFERENCED_TABLE_NAME,' ##') ,' on ( ' , COLUMN_NAME ,' = ' , concat( '##.' , REFERENCED_COLUMN_NAME) , ')' ) the_join
FROM
( [[ SCAN SQL_BASE_0 ]] ) x
SHOW;
RETURN
BEGIN PLATTE_JOINER
QRY
select replace(the_join,'##', chr( cast( row_number() over ( order by the_join ) + 96 as int )) ) the_join
from ( [[ SCAN SQL_BASE1 ]] ) a
SHOW;
RETURN
BEGIN MK_FLAT_QUERY
MVAR FLAT_QUERY = [[FLAT_QUERY]] [[CR]] [[the_join]]
RETURN
MVAR FLAT_QUERY = select * from fact_voertuigen q
sqlcall('| SSQL PLATTE_JOINER | CALL MK_FLAT_QUERY |')
DISP [[FLAT_QUERY]]
QRY
[[FLAT_QUERY]]
SHOW;
De opgeboude query ziet er als volgt uit ...
select * from fact_voertuigen q
join dim_cilinders a on ( dim_cilinders = a.id)
join dim_datum b on ( dim_datum_datum_eerste_toelating = b.datum_id)
join dim_datum c on ( dim_datum_datum_tenaamstelling = c.datum_id)
join dim_datum d on ( dim_datum_vervaldatum_apk = d.datum_id)
join dim_deuren e on ( dim_deuren = e.id)
join dim_inrichting f on ( dim_inrichting = f.id)
join dim_kleur g on ( dim_kleur = g.id)
join dim_merk h on ( dim_merk = h.id)
join dim_prijs i on ( dim_prijs = i.id)
join dim_voertuig j on ( dim_voertuig = j.id)
AVG anonimisatie
Bij een klant zijn alle kolommen van de stage-tabellen in een excel-sheet geimporteerd. Deze sheet is door de privat-officer voorzien van zijn anonimisatie-wensen.
- Alle foreign-keys moesten gehashed
- Postcodes moesten naar PC4
- Achternaam en Voornaam leeggepoetst
- BSN-nr moest gepseudonimiseerd ...
- Clientnummer moest gepseudonimisseerd ..
Daar waar de attributen avg-gevoelig waren werd door de private-officer een crypte-type geplaatst. De excelsheet werd vervolgens weer ingelezen en op basis van deze data kon de STAGE-views incl. anonimisatie gegenereerd worden.
Hieronder ook een opzet-idee ...
enkele encryptie-functies ...
MVAR geen = [[EMPTY]]
MVAR upper = UPPER
MVAR md5 = MD5
MVAR sha1 = SHA1
de te crypten data ...
select [[geen]](achternaam)
, [[upper]](voornaam)
, [[md5]](bsn)
, [[sha1]](rekeningnr)
from
(
select 'jansonius' achternaam
, 'dolf' voornaam
, '9351ND' postcode
, '0654946006' telefoonnummer
, 'NL56ABNA0402700678' rekeningnr
, '191221222' bsn
) a
Verwijderen schema
sqlcall('| SSQL ALL_TABLES | CALL REMOVE_SCHEMA |')
# metadata verversen vanwege verwijdere objecten ...
REFRESH
BEGIN REMOVE_SCHEMA
# DISP_ DBEXEC drop [[table_type]] "[[table_name]]"
DISP_ DBEXEC drop [[table_type]] [[table_name]]
RETURN
BEGIN ALL_TABLES
select distinct lower(table_name) table_name , table_type
from grip_metadata_v
where schema_name = 'STG'
RETURN