In dit topic leer je hoe je met Invantive SQL (inclusief gratis versies) de Covid-grafieken op de RIVM-site kunt laden in het no code platform Knack voor verdere verwerking. De volgende cijfers worden afgeleid uit de grafieken:
- Aantal overledenen op peildatum na alle correcties t/m laatste rapportagedatum.
- Aantal ziekenhuisopnames op peildatum na alle correcties t/m laatste rapportagedatum.
- Aantal GGD-positieve tests op peildatum na alle correcties t/m laatste rapportagedatum.
- En van elk een cumulatief aantal vanaf begin t/m peildatum.
Op de URL Ontwikkeling SARS-CoV-2 in grafieken | RIVM vind je een groot aantal grafieken die wekelijks op dinsdag bijgewerkt worden. De datasets zijn momenteel niet gemakkelijk geautomatiseerd uit te lezen, maar wel handmatig te downloaden via de “CSV downloaden” optie.
In deze grafiek vallen de cijfers uit elkaar in voorheen gerapporteerde totalen per dag (auberginekleur) en mutaties over het eerder gerapporteerde totaal (oranje). De mutaties over het verleden hebben meestal betrekking op het recente verleden, maar soms ook over langere tijd geleden.
De cijfers zitten in de HTML van de webpagina verwerkt binnen een grote <script> tag met daarin JavaScript. De data staat hier in als JSON, maar dan verpakt als een grote JavaScript tekst:
De cijfers zijn dus eerst in een doosje gedaan als een grote JavaScript tekst en daarna in een HTML-doosje.
Het ophalen van de gegevens gaat met de volgende query:
--
-- Extract highchart source data from RIVM. Note that the actual data
-- is JSON encapsulated into JavaScript text into a <script> tag.
-- There are 7 tables on the page, each with a different numeric code.
--
create or replace table rivmgraphsourcedata@inmemorystorage
as
select jte.*
from
( select substr(contents_char, instr(CONTENTS_CHAR, '"easychart":')) c
from HTTPDOWNLOAD@DataDictionary
( 'https://www.rivm.nl/coronavirus-covid-19/grafieken' )
) idta
join jsontable
( 'data.[*]'
passing '{"data": [{' || substr(c, 1, instr(c, ',"navbar":{"title":"COVID-19') - 1) || '}]}'
columns csv422881 varchar2 path 'easychart.422881-0-field_par_chart.csv'
, config422881 varchar2 path 'easychart.422881-0-field_par_chart.config'
, csv462501 varchar2 path 'easychart.462501-0-field_par_chart.csv'
, config462501 varchar2 path 'easychart.462501-0-field_par_chart.config'
, csv422191 varchar2 path 'easychart.422191-0-field_par_chart.csv'
, config422191 varchar2 path 'easychart.422191-0-field_par_chart.config'
, csv462471 varchar2 path 'easychart.462471-0-field_par_chart.csv'
, config462471 varchar2 path 'easychart.462471-0-field_par_chart.config'
, csv424961 varchar2 path 'easychart.424961-0-field_par_chart.csv'
, config424961 varchar2 path 'easychart.424961-0-field_par_chart.config'
, csv462441 varchar2 path 'easychart.462441-0-field_par_chart.csv'
, config462441 varchar2 path 'easychart.462441-0-field_par_chart.config'
, csv425291 varchar2 path 'easychart.425291-0-field_par_chart.csv'
, config425291 varchar2 path 'easychart.425291-0-field_par_chart.config'
)
jte
Merk op dat na het ophalen van de HTML via HTTPDOWNLOAD er geen regexp_replace of htmltable gebruikt wordt, maar een efficiënt gokje om op een tekst te zoeken. De resulterende tekst wordt vervolgens binnen de jsontable omgezet in een array binnen een data-element zodat er een “repeater” is. De verschillende tabellen staan namelijk naast elkaar in plaats van onder elkaar.
In de volgende stap zet je deze naast elkaar structuur om in een verticale lijst van gegevens per tabel horende bij een specifiek grafiek (“transponeren”):
--
-- Transpose the graph data into one row per graph.
-- We assume that the number is a unique identifying code
-- for the graph.
--
create or replace table rivmgraphsourcedatatrans@inmemorystorage
as
select '422881' code
, csv422881 csv
, config422881 config
from rivmgraphsourcedata@inmemorystorage
union all
select '462501'
, csv462501 csv
, config462501 config
from rivmgraphsourcedata@inmemorystorage
union all
select '422191'
, csv422191 csv
, config422191 config
from rivmgraphsourcedata@inmemorystorage
union all
select '462471'
, csv462471 csv
, config462471 config
from rivmgraphsourcedata@inmemorystorage
union all
select '424961'
, csv424961 csv
, config424961 config
from rivmgraphsourcedata@inmemorystorage
union all
select '462441'
, csv462441 csv
, config462441 config
from rivmgraphsourcedata@inmemorystorage
union all
select '425291'
, csv425291 csv
, config425291 config
from rivmgraphsourcedata@inmemorystorage
Dit transponeren kan ook met een SQL pivot, maar bij deze aanpak voel ik me comfortabeler.
In de volgende stap zoek je de titel op binnen de kolom config met de instellingen van de grafiek. Daarna split je met jsontable de JSON-data in csv op in drie velden.
Aangezien de kolomkoppen als eerste rij in csv staan, slaan we de rijen over waar de x-as geen waarde heeft. Dat zijn toevallig de kolomkoppen.
De datums zijn in geschreven woord volgens een Nederlandse schrijfwijze. Er is voor gekozen om met een (2n+1)-aire replace operator deze te herschrijven naar een tekst die betrouwbaar door een to_date omgezet kan worden in een datum:
create or replace table rivmgraphdata@inmemorystorage
as
select src.code
label 'Unique graph code'
, cfg.title
label 'Title'
, to_date
( replace
( dta.xaxisdatelabel
, ' jan', '-01-2020'
, ' feb', '-02-2020'
, ' mrt', '-03-2020'
, ' apr', '-04-2020'
, ' mei', '-05-2020'
, ' jun', '-06-2020'
, ' jul', '-07-2020'
, ' aug', '-08-2020'
, ' sep', '-09-2020'
, ' okt', '-10-2020'
, ' nov', '-11-2020'
, ' dec', '-12-2020'
)
, 'DD-MM-YYYY'
)
xaxisdate
label 'Date'
, coalesce(cast(dta.corr_cases_c as number), 0)
corr_cases
label 'Correction'
, coalesce(cast(dta.total_cases_c as number), 0)
total_cases_excl_corr
label 'Total cases excl correction'
, coalesce(cast(dta.corr_cases_c as number), 0)
+ coalesce(cast(dta.total_cases_c as number), 0)
total_cases_incl_corr
label 'Total cases incl correction'
from rivmgraphsourcedatatrans@inmemorystorage src
join jsontable
( 'data.[*]'
passing '{"data": [' || src.config || ']}'
columns title varchar2 path 'title.text'
)
cfg
join jsontable
( '[*]'
passing '' || src.csv || ''
columns xaxisdatelabel varchar2 path '[0]'
, corr_cases_c varchar2 path '[1]'
, total_cases_c varchar2 path '[2]'
)
dta
--
-- Remove header row. It is the only row
-- with no value for X-axis.
--
on dta.xaxisdatelabel is not null
where src.code in
( '462501' -- Overledenen per dag vanaf 27 februari 2020
, '462471' -- In ziekenhuis opgenomen patienten vanaf 27 februari 2020
, '462441' -- GGD meldingen positief geteste personen per dag vanaf 27 februari 2020
)
In de vorige stap waren er maar drie tabellen doorgelaten door de where op src.code.
De drie tabellen worden weer naast elkaar gezet en krijgen alvast de namen van de kolommen waar de waardes in Knack in moeten komen:
create or replace table rivmgraphdatawithoutcumulatives@Inmemorystorage
as
select c462501.xaxisdate
Datum
, c462501.total_cases_incl_corr
Overleden_nieuw_GGD_
label 'Overledenen'
, c462471.total_cases_incl_corr
Ziekenhuisopnames_nieuw_GGD_
label 'Opnames'
, c462441.total_cases_incl_corr
Positief_getest_nieuw_GGD_
label 'GGD positief getest'
from rivmgraphdata@inmemorystorage c462501
join rivmgraphdata@inmemorystorage c462471
on c462471.code = '462471'
and c462471.xaxisdate = c462501.xaxisdate
join rivmgraphdata@inmemorystorage c462441
on c462441.code = '462441'
and c462441.xaxisdate = c462501.xaxisdate
where c462501.code = '462501'
In Knack wil je naast de dagstanden na correctie ook cumulatieve totalen hebben vanaf begin van de metingen tot/met de peildatum. Knack biedt hiervoor geen ondersteuning. Met een cartesisch product tussen de dagstanden en een join op kleiner dan/gelijk wordt per datum een cumulatief totaal van alle cijfers toegevoegd als volgt:
--
-- Cartesian product, takes over 2 seconds.
--
create or replace table rivmgraphdatawithcumulatives@Inmemorystorage
as
select curr.Datum
, curr.Overleden_nieuw_GGD_
, curr.Ziekenhuisopnames_nieuw_GGD_
, curr.Positief_getest_nieuw_GGD_
, sum(prev.Overleden_nieuw_GGD_)
Overleden_GGD_
, sum(prev.Ziekenhuisopnames_nieuw_GGD_)
Ziekenhuisopnames_GGD_
, sum(prev.Positief_getest_nieuw_GGD_)
Positief_getest_GGD_
from rivmgraphdatawithoutcumulatives@Inmemorystorage curr
left
outer
join rivmgraphdatawithoutcumulatives@Inmemorystorage prev
on prev.datum <= curr.datum
group
by curr.Datum
, curr.Overleden_nieuw_GGD_
, curr.Ziekenhuisopnames_nieuw_GGD_
, curr.Positief_getest_nieuw_GGD_
De gegevens vergelijk je vervolgens met de huidige cijfers in Knack en worden waar nodig bijgesteld:
synchronize Dagelijkse_update@knk
from rivmgraphdatawithcumulatives@Inmemorystorage
with insert or update
identified
by Datum