Ophalen in CSV van RIVM Covid-19 Gegevens

Wiki (nl)
, ,
1

Het RIVM biedt op een aantal plaatsen gestructureerd datasets aan rondom Covid-19. In dit artikel lees je hoe je die datasets massaal kunt ophalen en omzetten naar bijvoorbeeld CSV.

De verwerking is gebaseerd op Invantive SQL waarbij vooral de HTTPDownload table function en JSONTABLE gebruikt worden. Voor de export naar CSV is gebruik gemaakt van Invantive Script, zodat je met weinig moeite er ook XML, JSON of Excel sheets uit kunt laten rollen. Uiteraard kun je ook de gegevens wegschrijven naar bijvoorbeeld SQL Server of Oracle.

Om de RIVM Covid-19 gegevens te exporteren naar CSV doorloop je de volgende stappen:

  • Installeer Invantive Query Tool.
  • Kies een Open Data licentie.
  • Meld je aan op een willekeurige Open Data database.
  • Plak alle onderstaande statements gescheiden door witregels.
  • Druk op F5.
  • De CSV-bestanden staan in de c:\temp map.

De eerste statements kiezen de uitvoermap en zorgen er voor dat de huidige systeemtijd in UTC als prefix gebruikt wordt voor bestandsnamen:

local define OUT_FOLDER "c:\temp"

select to_char(sysdateutc, 'YYYYMMDDHH24MISS')

local define OUT_PATH "${OUT_FOLDER}\${outcome:0,0}-"

Per onderdeel wordt een dataset opgehaald en omgezet in een SQL tabel:

SQL Query Rioolwatermetingen per RWZI

Het volgende statement maakt een in-memory tabel met daarin de rioolwatermetingen per rioolwaterzuiveringsinstallatie (“RWZI“):

--
-- Rioolwaterdata.
--
create or replace table rioolwatermetingen@inmemorystorage
as
select to_date(j.Date_measurement_c, 'YYYY-MM-DD')
       date_measurement
,      j.rwzi_awzi_code
,      j.rwzi_awzi_name
,      j.x_coordinate
,      j.y_coordinate
,      j.postal_code
,      j.security_region_code
,      j.security_region_name
,      j.percentage_in_security_region
,      j.rna_per_ml
,      j.representative_measurement
from   HTTPDOWNLOAD@DataDictionary('https://data.rivm.nl/covid-19/COVID-19_rioolwaterdata.json') htp
join   jsontable
       ( '[*]'
         passing htp.content_clob
         columns date_measurement_c            varchar2  path 'Date_measurement'
         ,       rwzi_awzi_code                varchar2  path 'RWZI_AWZI_code'
         ,       rwzi_awzi_name                varchar2  path 'RWZI_AWZI_name'
         ,       x_coordinate                  int64     path 'X_coordinate'
         ,       y_coordinate                  int64     path 'Y_coordinate'
         ,       postal_code                   varchar2  path 'Postal_code'
         ,       security_region_code          varchar2  path 'Security_region_code'
         ,       security_region_name          varchar2  path 'Security_region_name'
         ,       percentage_in_security_region number    path 'Percentage_in_security_region'
         ,       rna_per_ml                    number    path 'RNA_per_ml'
         ,       representative_measurement    boolean    path 'Representative_measurement'
       ) j

SQL Query Landelijke Reproductiegetallen

Vervolgens verwerken we de landelijke reproductiegetallen (R) met een bandbreedte aan de onder- en bovenkant (er is helaas geen standaarddeviatie beschikbaar):

--
-- Reproductiegetal.
--
create or replace table reproductiegetallen@inmemorystorage
as
select to_date(j.date_c, 'YYYY-MM-DD')
       date
,      j.rt_low
,      j.rt_avg
,      j.rt_up
,      j.population
from   HTTPDOWNLOAD@DataDictionary('https://data.rivm.nl/covid-19/COVID-19_reproductiegetal.json') htp
join   jsontable
       ( '[*]'
         passing htp.content_clob
         columns date_c                        varchar2  path 'Date'
         ,       rt_low                        number    path 'Rt_low'
         ,       rt_avg                        number    path 'Rt_avg'
         ,       rt_up                         number    path 'Rt_up'
         ,       population                    varchar2 path 'population'
       ) j

SQL Query Covid19-Casusstatistieken

De landelijke Covid-casusstatistieken lees je als volgt uit bij het RIVM:

--
-- Casus landelijk.
--
create or replace table casussen_landelijk@inmemorystorage
as
select to_date(j.date_file, 'YYYY-MM-DD HH24:MI:SS')
       date_file
,      to_date(j.date_statistics, 'YYYY-MM-DD')
       date_statistics
,      j.date_statistics_type
,      j.agegroup
,      j.sex
,      j.province
,      j.hospital_admission = 'Yes'
       hospital_admission_flag
,      j.deceased = 'Yes'
       deceased_flag
,      j.week_of_death
,      j.municipal_health_service
from   HTTPDOWNLOAD@DataDictionary('https://data.rivm.nl/covid-19/COVID-19_casus_landelijk.json') htp
join   jsontable
       ( '[*]'
         passing htp.content_clob
         columns date_file                        varchar2  path 'Date_file'
         ,       date_statistics                  varchar2  path 'Date_statistics'
         ,       date_statistics_type             varchar2  path 'Date_statistics_type'
         ,       agegroup                         varchar2  path 'Agegroup'
         ,       sex                              varchar2  path 'Sex'
         ,       province                         varchar2  path 'Province'
         ,       hospital_admission               varchar2  path 'Hospital_admission'
         ,       deceased                         varchar2  path 'Deceased'
         ,       week_of_death                    int32     path 'Week_of_death'
         ,       municipal_health_service         varchar2  path 'Municipal_health_service'
       ) j

Deel 2 in reacties…

2

SQL Query Covid19-aantallen per Gemeente

De Covid19-aantallen zijn ook beschikbaar per Nederlandse gemeente, inclusief gemeentecode:

--
-- Aantallen gemeente per dag.
--
create or replace table casussen_gemeente@inmemorystorage
as
select to_date(j.date_of_report_c, 'YYYY-MM-DD HH24:MI:SS')
       date_of_report
,      to_date(j.date_of_publication_c, 'YYYY-MM-DD')
       date_of_publication
,      j.municipality_code
,      j.municipality_name
,      j.province
,      j.security_region_code
,      j.security_region_name
,      j.municipal_health_service
,      j.roaz_region
,      j.total_reported
,      j.hospital_admission
,      j.deceased
from   HTTPDOWNLOAD@DataDictionary('https://data.rivm.nl/covid-19/COVID-19_aantallen_gemeente_per_dag.json') htp
join   jsontable
       ( '[*]'
         passing htp.content_clob
         columns date_of_report_c                 varchar2  path 'Date_of_report'
         ,       date_of_publication_c            varchar2  path 'Date_of_publication'
         ,       municipality_code                varchar2  path 'Municipality_code'
         ,       municipality_name                varchar2  path 'Municipality_name'
         ,       province                         varchar2  path 'Province'
         ,       security_region_code             varchar2  path 'Security_region_code'
         ,       security_region_name             varchar2  path 'Security_region_name'
         ,       municipal_health_service         varchar2  path 'Municipal_health_service'
         ,       roaz_region                      varchar2  path 'ROAZ_region'
         ,       total_reported                   int32     path 'Total_reported'
         ,       hospital_admission               int32     path 'Hospital_admission'
         ,       deceased                         int32     path 'Deceased'
       ) j

SQL Query Covid19-aantallen per Gemeente

De cumulatieve aantallen per gemeente (Covid19-besmettingen, ziekenhuisopnames en overleden) volgens RIVM zet je in een tabel met:

--
-- Aantallen gemeente cumulatief.
--
create or replace table casussen_gemeente_cumulatief@inmemorystorage
as
select to_date(j.date_of_report_c, 'YYYY-MM-DD HH24:MI:SS')
       date_of_report
,      j.municipality_code
,      j.municipality_name
,      j.province
,      j.total_reported
,      j.hospital_admission
,      j.deceased
from   HTTPDOWNLOAD@DataDictionary('https://data.rivm.nl/covid-19/COVID-19_aantallen_gemeente_cumulatief.json') htp
join   jsontable
       ( '[*]'
         passing htp.content_clob
         columns date_of_report_c                 varchar2  path 'Date_of_report'
         ,       municipality_code                varchar2  path 'Municipality_code'
         ,       municipality_name                varchar2  path 'Municipality_name'
         ,       province                         varchar2  path 'Province'
         ,       total_reported                   int32     path 'Total_reported'
         ,       hospital_admission               int32     path 'Hospital_admission'
         ,       deceased                         int32     path 'Deceased'
       ) j

SQL Query Verdenkingen door Huisartsen

Huisartsen verzamelen verdenkingen en die worden beschikbaar gesteld als tabel:

--
-- Verdenkingen huisartsen.
--
create or replace table nl_verdenkingen_huisartsen@inmemorystorage
as
select from_unixtime(j.week_unix)
       week
,      from_unixtime(j.week_start_unix)
       date_week_start
,      from_unixtime(j.week_end_unix)
       date_week_end
,      j.incidentie
,      j.geschat_aantal
--
,      from_unixtime(j.date_of_insertion_unix)
       date_insertion
,      from_unixtime(j.control_last_generated_unix)
       control_date_last_generated
,      j.control_proto_name
,      j.control_name
,      j.control_code
from   HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join   jsontable
       ( 'verdenkingen_huisartsen.values[*]'
         passing htp.content_clob
         columns control_last_generated_unix int64   path '::ancestor.::ancestor.last_generated'
         ,       control_proto_name          varchar2 path '::ancestor.::ancestor.proto_name'
         ,       control_name                varchar2 path '::ancestor.::ancestor.name'
         ,       control_code                varchar2 path '::ancestor.::ancestor.code'
         ,       week_unix                   int64    path 'week_unix'
         ,       week_start_unix             int64    path 'week_start_unix'
         ,       week_end_unix               int64    path 'week_end_unix'
         ,       incidentie                  number   path 'incidentie'
         ,       geschat_aantal              number   path 'geschat_aantal'
         ,       date_of_insertion_unix      int64    path 'date_of_insertion_unix'
       ) j
order
by     control_date_last_generated
,      week

SQL Query Covid19-ziekenhuisopnames

De Covid19-ziekenhuisopnames leg je in een tabel vast met:

--
-- Intake hospital ma.
--
create or replace table nl_intake_hospital_ma@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
       date_report
,      j.moving_average_hospital
--
,      from_unixtime(j.date_of_insertion_unix)
       date_insertion
,      from_unixtime(j.control_last_generated_unix)
       control_date_last_generated
,      j.control_proto_name
,      j.control_name
,      j.control_code
from   HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join   jsontable
       ( 'intake_hospital_ma.values[*]'
         passing htp.content_clob
         columns control_last_generated_unix int64    path '::ancestor.::ancestor.last_generated'
         ,       control_proto_name          varchar2 path '::ancestor.::ancestor.proto_name'
         ,       control_name                varchar2 path '::ancestor.::ancestor.name'
         ,       control_code                varchar2 path '::ancestor.::ancestor.code'
         ,       date_of_report_unix         int64    path 'date_of_report_unix'
         ,       moving_average_hospital     number   path 'moving_average_hospital'
         ,       date_of_insertion_unix      int64    path 'date_of_insertion_unix'
       ) j
order
by     control_date_last_generated
,      date_report

SQL Query Covid19-besmettingen

De tellingen van bevestigde Covid19-besmette personen schrijf je naar een tabel met:

--
-- Infectious people count.
--
create or replace table nl_infectious_people_counts@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
       date_report
,      j.infectious_low
,      j.infectious_avg
,      j.infectious_high
--
,      from_unixtime(j.date_of_insertion_unix)
       date_insertion
,      from_unixtime(j.control_last_generated_unix)
       control_date_last_generated
,      j.control_proto_name
,      j.control_name
,      j.control_code
from   HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join   jsontable
       ( 'infectious_people_count.values[*]'
         passing htp.content_clob
         columns control_last_generated_unix int64    path '::ancestor.::ancestor.last_generated'
         ,       control_proto_name          varchar2 path '::ancestor.::ancestor.proto_name'
         ,       control_name                varchar2 path '::ancestor.::ancestor.name'
         ,       control_code                varchar2 path '::ancestor.::ancestor.code'
         ,       date_of_report_unix         int64    path 'date_of_report_unix'
         ,       infectious_low              number   path 'infectious_low'
         ,       infectious_avg              number   path 'infectious_avg'
         ,       infectious_high             number   path 'infectious_high'
         ,       date_of_insertion_unix      int64    path 'date_of_insertion_unix'
       ) j
order
by     control_date_last_generated
,      date_report

SQL Query Genormaliseerde versie besmettingen

En de genormaliseerde versie hiervan met onder- en bovengrenzen met:

--
-- Infectious people count normalized.
--
create or replace table nl_infectious_people_counts_normalized@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
       date_report
,      j.infectious_low_normalized
,      j.infectious_avg_normalized
,      j.infectious_high_normalized
--
,      from_unixtime(j.date_of_insertion_unix)
       date_insertion
,      from_unixtime(j.control_last_generated_unix)
       control_date_last_generated
,      j.control_proto_name
,      j.control_name
,      j.control_code
from   HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join   jsontable
       ( 'infectious_people_count_normalized.values[*]'
         passing htp.content_clob
         columns control_last_generated_unix int64    path '::ancestor.::ancestor.last_generated'
         ,       control_proto_name          varchar2 path '::ancestor.::ancestor.proto_name'
         ,       control_name                varchar2 path '::ancestor.::ancestor.name'
         ,       control_code                varchar2 path '::ancestor.::ancestor.code'
         ,       date_of_report_unix         int64    path 'date_of_report_unix'
         ,       infectious_low_normalized   number   path 'infectious_low_normalized'
         ,       infectious_avg_normalized   number   path 'infectious_avg_normalized'
         ,       infectious_high_normalized  number   path 'infectious_high_normalized'
         ,       date_of_insertion_unix      int64    path 'date_of_insertion_unix'
       ) j
order
by     control_date_last_generated
,      date_report

SQL Query Intensive Care Opnames

Vergelijkbaar vraag je de opnames op de Intensive Care op en stop je die in een databasetabel met:

--
-- Intake intensive care ma.
--
create or replace table nl_intake_intensive_care_ma@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
       date_report
,      j.moving_average_ic
--
,      from_unixtime(j.date_of_insertion_unix)
       date_insertion
,      from_unixtime(j.control_last_generated_unix)
       control_date_last_generated
,      j.control_proto_name
,      j.control_name
,      j.control_code
from   HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join   jsontable
       ( 'intake_intensivecare_ma.values[*]'
         passing htp.content_clob
         columns control_last_generated_unix int64    path '::ancestor.::ancestor.last_generated'
         ,       control_proto_name          varchar2 path '::ancestor.::ancestor.proto_name'
         ,       control_name                varchar2 path '::ancestor.::ancestor.name'
         ,       control_code                varchar2 path '::ancestor.::ancestor.code'
         ,       date_of_report_unix         int64    path 'date_of_report_unix'
         ,       moving_average_ic           number   path 'moving_average_ic'
         ,       date_of_insertion_unix      int64    path 'date_of_insertion_unix'
       ) j
order
by     control_date_last_generated
,      date_report

SQL Query Covid19-Dagstanden

De Covid19-dagstanden verwerk je tot een tabel met:

--
-- Infected people nursery count daily.
--
create or replace table nl_infected_people_nursery_counts_daily@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
       date_report
,      j.infected_nursery_daily
--
,      from_unixtime(j.date_of_insertion_unix)
       date_insertion
,      from_unixtime(j.control_last_generated_unix)
       control_date_last_generated
,      j.control_proto_name
,      j.control_name
,      j.control_code
from   HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join   jsontable
       ( 'infected_people_nursery_count_daily.values[*]'
         passing htp.content_clob
         columns control_last_generated_unix int64    path '::ancestor.::ancestor.last_generated'
         ,       control_proto_name          varchar2 path '::ancestor.::ancestor.proto_name'
         ,       control_name                varchar2 path '::ancestor.::ancestor.name'
         ,       control_code                varchar2 path '::ancestor.::ancestor.code'
         ,       date_of_report_unix         int64    path 'date_of_report_unix'
         ,       infected_nursery_daily      number   path 'infected_nursery_daily'
         ,       date_of_insertion_unix      int64    path 'date_of_insertion_unix'
       ) j
order
by     control_date_last_generated
,      date_report

SQL Query Covid19 Overlijdensgevallen Zorg

En de aan Covid19-overleden personen in de zorg met:

--
-- Deceased people nursery count daily.
--
create or replace table nl_deceased_people_nursery_counts_daily@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
       date_report
,      j.deceased_nursery_daily
--
,      from_unixtime(j.date_of_insertion_unix)
       date_insertion
,      from_unixtime(j.control_last_generated_unix)
       control_date_last_generated
,      j.control_proto_name
,      j.control_name
,      j.control_code
from   HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join   jsontable
       ( 'deceased_people_nursery_count_daily.values[*]'
         passing htp.content_clob
         columns control_last_generated_unix int64    path '::ancestor.::ancestor.last_generated'
         ,       control_proto_name          varchar2 path '::ancestor.::ancestor.proto_name'
         ,       control_name                varchar2 path '::ancestor.::ancestor.name'
         ,       control_code                varchar2 path '::ancestor.::ancestor.code'
         ,       date_of_report_unix         int64    path 'date_of_report_unix'
         ,       deceased_nursery_daily      number   path 'deceased_nursery_daily'
         ,       date_of_insertion_unix      int64    path 'date_of_insertion_unix'
       ) j
order
by     control_date_last_generated
,      date_report

SQL Query Covid19 Locaties

De totale aantallen gemelde locaties schrijf je naar een databasetabel met het volgende SQL-statement:

--
-- Total reported locations.
--
create or replace table nl_reported_locations_total@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
       date_report
,      j.total_reported_locations
--
,      from_unixtime(j.date_of_insertion_unix)
       date_insertion
,      from_unixtime(j.control_last_generated_unix)
       control_date_last_generated
,      j.control_proto_name
,      j.control_name
,      j.control_code
from   HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join   jsontable
       ( 'total_reported_locations.values[*]'
         passing htp.content_clob
         columns control_last_generated_unix int64    path '::ancestor.::ancestor.last_generated'
         ,       control_proto_name          varchar2 path '::ancestor.::ancestor.proto_name'
         ,       control_name                varchar2 path '::ancestor.::ancestor.name'
         ,       control_code                varchar2 path '::ancestor.::ancestor.code'
         ,       date_of_report_unix         int64    path 'date_of_report_unix'
         ,       total_reported_locations    number   path 'total_reported_locations'
         ,       date_of_insertion_unix      int64    path 'date_of_insertion_unix'
       ) j
order
by     control_date_last_generated
,      date_report

SQL Query Nieuwe Locaties

Het nieuw aantal gemelde locatie schrijf je naar een tabel met:

--
-- Total newly reported locations.
--
create or replace table nl_reported_locations_newly@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
       date_report
,      j.total_new_reported_locations
--
,      from_unixtime(j.date_of_insertion_unix)
       date_insertion
,      from_unixtime(j.control_last_generated_unix)
       control_date_last_generated
,      j.control_proto_name
,      j.control_name
,      j.control_code
from   HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join   jsontable
       ( 'total_newly_reported_locations.values[*]'
         passing htp.content_clob
         columns control_last_generated_unix  int64    path '::ancestor.::ancestor.last_generated'
         ,       control_proto_name           varchar2 path '::ancestor.::ancestor.proto_name'
         ,       control_name                 varchar2 path '::ancestor.::ancestor.name'
         ,       control_code                 varchar2 path '::ancestor.::ancestor.code'
         ,       date_of_report_unix          int64    path 'date_of_report_unix'
         ,       total_new_reported_locations number   path 'total_new_reported_locations'
         ,       date_of_insertion_unix       int64    path 'date_of_insertion_unix'
       ) j
order
by     control_date_last_generated
,      date_report

SQL Query Besmette Personen

Net zoals je de totalen qua besmette personen vastlegt met:

--
-- Infected people total.
--
create or replace table nl_infected_people_total@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
       date_report
,      j.infected_daily_total
--
,      from_unixtime(j.date_of_insertion_unix)
       date_insertion
,      from_unixtime(j.control_last_generated_unix)
       control_date_last_generated
,      j.control_proto_name
,      j.control_name
,      j.control_code
from   HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join   jsontable
       ( 'infected_people_total.values[*]'
         passing htp.content_clob
         columns control_last_generated_unix  int64    path '::ancestor.::ancestor.last_generated'
         ,       control_proto_name           varchar2 path '::ancestor.::ancestor.proto_name'
         ,       control_name                 varchar2 path '::ancestor.::ancestor.name'
         ,       control_code                 varchar2 path '::ancestor.::ancestor.code'
         ,       date_of_report_unix          int64    path 'date_of_report_unix'
         ,       infected_daily_total         number   path 'infected_daily_total'
         ,       date_of_insertion_unix       int64    path 'date_of_insertion_unix'
       ) j
order
by     control_date_last_generated
,      date_report

SQL Query Besmettingen (genormaliseerd, verschil)

De genormaliseerde delta verwerk je met:

--
-- Infected people delta normalized.
--
create or replace table nl_infected_people_delta_normalized@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
       date_report
,      j.infected_daily_increase
--
,      from_unixtime(j.date_of_insertion_unix)
       date_insertion
,      from_unixtime(j.control_last_generated_unix)
       control_date_last_generated
,      j.control_proto_name
,      j.control_name
,      j.control_code
from   HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join   jsontable
       ( 'infected_people_delta_normalized.values[*]'
         passing htp.content_clob
         columns control_last_generated_unix  int64    path '::ancestor.::ancestor.last_generated'
         ,       control_proto_name           varchar2 path '::ancestor.::ancestor.proto_name'
         ,       control_name                 varchar2 path '::ancestor.::ancestor.name'
         ,       control_code                 varchar2 path '::ancestor.::ancestor.code'
         ,       date_of_report_unix          int64    path 'date_of_report_unix'
         ,       infected_daily_increase      number   path 'infected_daily_increase'
         ,       date_of_insertion_unix       int64    path 'date_of_insertion_unix'
       ) j
order
by     control_date_last_generated
,      date_report

SQL Query Covid19-instroom per Leeftijdsgroep

De instroom per leeftijdsgroep vind je met:

--
-- Intake share age groups.
--
create or replace table nl_intake_share_age_groups@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
       date_report
,      j.agegroup
,      j.infected_per_agegroup_increase
--
,      from_unixtime(j.date_of_insertion_unix)
       date_insertion
,      from_unixtime(j.control_last_generated_unix)
       control_date_last_generated
,      j.control_proto_name
,      j.control_name
,      j.control_code
from   HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join   jsontable
       ( 'intake_share_age_groups.values[*]'
         passing htp.content_clob
         columns control_last_generated_unix    int64    path '::ancestor.::ancestor.last_generated'
         ,       control_proto_name             varchar2 path '::ancestor.::ancestor.proto_name'
         ,       control_name                   varchar2 path '::ancestor.::ancestor.name'
         ,       control_code                   varchar2 path '::ancestor.::ancestor.code'
         ,       date_of_report_unix            int64    path 'date_of_report_unix'
         ,       agegroup                       varchar2 path 'agegroup'
         ,       infected_per_agegroup_increase number   path 'infected_per_agegroup_increase'
         ,       date_of_insertion_unix         int64    path 'date_of_insertion_unix'
       ) j
order
by     control_date_last_generated
,      date_report

SQL Query Covid19-reproductiegetal

Een ander versie van de reproductiegetallen stop je in een tabel met het volgende SQL-statement:

--
-- Reproduction index.
--
create or replace table nl_reproduction_indexes@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
       date_report
,      j.reproduction_index_low
,      j.reproduction_index_avg
,      j.reproduction_index_high
--
,      from_unixtime(j.date_of_insertion_unix)
       date_insertion
,      from_unixtime(j.control_last_generated_unix)
       control_date_last_generated
,      j.control_proto_name
,      j.control_name
,      j.control_code
from   HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join   jsontable
       ( 'reproduction_index.values[*]'
         passing htp.content_clob
         columns control_last_generated_unix    int64    path '::ancestor.::ancestor.last_generated'
         ,       control_proto_name             varchar2 path '::ancestor.::ancestor.proto_name'
         ,       control_name                   varchar2 path '::ancestor.::ancestor.name'
         ,       control_code                   varchar2 path '::ancestor.::ancestor.code'
         ,       date_of_report_unix            int64    path 'date_of_report_unix'
         ,       reproduction_index_low         number   path 'reproduction_index_low'
         ,       reproduction_index_avg         number   path 'reproduction_index_avg'
         ,       reproduction_index_high        number   path 'reproduction_index_high'
         ,       date_of_insertion_unix         int64    path 'date_of_insertion_unix'
       ) j
order
by     control_date_last_generated
,      date_report

SQL Query Gemiddelde R-waardes

De uitgemiddelde R-waardes maak je met de volgende Invantive SQL query:

--
-- Reproduction index last known average.
--
create or replace table nl_reproduction_indexes_last_known_avg@inmemorystorage
as
select from_unixtime(j.date_of_report_unix)
       date_report
,      j.reproduction_index_low
,      j.reproduction_index_avg
,      j.reproduction_index_high
--
,      from_unixtime(j.date_of_insertion_unix)
       date_insertion
,      from_unixtime(j.control_last_generated_unix)
       control_date_last_generated
,      j.control_proto_name
,      j.control_name
,      j.control_code
from   HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join   jsontable
       ( 'reproduction_index_last_known_average.values[*]'
         passing htp.content_clob
         columns control_last_generated_unix    int64    path '::ancestor.::ancestor.last_generated'
         ,       control_proto_name             varchar2 path '::ancestor.::ancestor.proto_name'
         ,       control_name                   varchar2 path '::ancestor.::ancestor.name'
         ,       control_code                   varchar2 path '::ancestor.::ancestor.code'
         ,       date_of_report_unix            int64    path 'date_of_report_unix'
         ,       reproduction_index_low         number   path 'reproduction_index_low'
         ,       reproduction_index_avg         number   path 'reproduction_index_avg'
         ,       reproduction_index_high        number   path 'reproduction_index_high'
         ,       date_of_insertion_unix         int64    path 'date_of_insertion_unix'
       ) j
order
by     control_date_last_generated
,      date_report
3

SQL Query Covid19-rioolwatermetingen

Een andere versie van de Covid19-rioolwatermetingen vind je met de volgende query:

--
-- Rioolwater metingen.
--
create or replace table nl_rioolwatermetingen@inmemorystorage
as
select from_unixtime(j.week_unix)
       week
,      j.average
--
,      from_unixtime(j.date_of_insertion_unix)
       date_insertion
,      from_unixtime(j.control_last_generated_unix)
       control_date_last_generated
,      j.control_proto_name
,      j.control_name
,      j.control_code
from   HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join   jsontable
       ( 'rioolwater_metingen.values[*]'
         passing htp.content_clob
         columns control_last_generated_unix    int64    path '::ancestor.::ancestor.last_generated'
         ,       control_proto_name             varchar2 path '::ancestor.::ancestor.proto_name'
         ,       control_name                   varchar2 path '::ancestor.::ancestor.name'
         ,       control_code                   varchar2 path '::ancestor.::ancestor.code'
         ,       week_unix                      int64    path 'week_unix'
         ,       average                        number   path 'average'
         ,       date_of_insertion_unix         int64    path 'date_of_insertion_unix'
       ) j
order
by     control_date_last_generated
,      week

SQL Query Covid19-rioolwatermetingen per RWZI

Het volgende SQL-statement verzamelt de rioolwatermetingen per RWZI:

--
-- Rioolwatermetingen per RWZI.
--
create or replace table nl_rioolwatermetingen_rwzi@inmemorystorage
as
select from_unixtime(j.date_measurement_unix)
       date_measurement
,      from_unixtime(j.week_start_unix)
       date_week_start
,      from_unixtime(j.week_end_unix)
       date_week_end
,      j.rwzi_awzi_code
,      j.rwzi_awzi_name
,      j.vrcode
,      j.vrnaam
,      j.gm_code
,      j.rna_per_ml
--
,      from_unixtime(j.date_of_insertion_unix)
       date_insertion
,      from_unixtime(j.control_last_generated_unix)
       control_date_last_generated
,      j.control_proto_name
,      j.control_name
,      j.control_code
from   HTTPDOWNLOAD@DataDictionary('https://coronadashboard.rijksoverheid.nl/json/NL.json') htp
join   jsontable
       ( 'rioolwater_metingen_per_rwzi.values[*]'
         passing htp.content_clob
         columns control_last_generated_unix    int64    path '::ancestor.::ancestor.last_generated'
         ,       control_proto_name             varchar2 path '::ancestor.::ancestor.proto_name'
         ,       control_name                   varchar2 path '::ancestor.::ancestor.name'
         ,       control_code                   varchar2 path '::ancestor.::ancestor.code'
         ,       date_measurement_unix          int64    path 'date_measurement_unix'
         ,       week_start_unix                int64    path 'week_start_unix'
         ,       week_end_unix                  int64    path 'week_end_unix'
         ,       rwzi_awzi_code                 varchar2 path 'rwzi_awzi_code'
         ,       rwzi_awzi_name                 varchar2 path 'rwzi_awzi_name'
         ,       vrcode                         varchar2 path 'vrcode'
         ,       vrnaam                         varchar2 path 'vrnaam'
         ,       gm_code                        varchar2 path 'gm_code'
         ,       rna_per_ml                     number   path 'rna_per_ml'
         ,       date_of_insertion_unix         int64    path 'date_of_insertion_unix'
       ) j
order
by     control_date_last_generated
,      date_measurement
,      rwzi_awzi_code

CSV Export van RIVM Covid19-cijfers

De volgende SQL-statements exporteren de gegevens naar een reeks CSV-bestanden:

select *
from   rioolwatermetingen@inmemorystorage

local export results as "${OUT_PATH}rioolwaterdata.csv" format csv include technical headers

select *
from   reproductiegetallen@inmemorystorage

local export results as "${OUT_PATH}reproductiegetal.csv" format csv include technical headers

select *
from   casussen_landelijk@inmemorystorage

local export results as "${OUT_PATH}casus-landelijk.csv" format csv include technical headers

select *
from   casussen_gemeente@inmemorystorage

local export results as "${OUT_PATH}aantallen-gemeente-per-dag.csv" format csv include technical headers

select *
from   casussen_gemeente_cumulatief@inmemorystorage

local export results as "${OUT_PATH}aantallen-gemeente-cumulatief.csv" format csv include technical headers

select *
from   nl_verdenkingen_huisartsen@inmemorystorage

local export results as "${OUT_PATH}verdenkingen-huisartsen.csv" format csv include technical headers

select *
from   nl_intake_hospital_ma@inmemorystorage

local export results as "${OUT_PATH}intake-hospital-ma.csv" format csv include technical headers

select *
from   nl_infectious_people_counts@inmemorystorage

local export results as "${OUT_PATH}infectious-people-count.csv" format csv include technical headers

select *
from   nl_infectious_people_counts_normalized@inmemorystorage

local export results as "${OUT_PATH}infectious-people-count-normalized.csv" format csv include technical headers

select *
from   nl_intake_intensive_care_ma@inmemorystorage

local export results as "${OUT_PATH}intake-intensivecare-ma.csv" format csv include technical headers

select *
from   nl_infected_people_nursery_counts_daily@inmemorystorage

local export results as "${OUT_PATH}infected-people-nursery-count-daily.csv" format csv include technical headers

select *
from   nl_deceased_people_nursery_counts_daily@inmemorystorage

local export results as "${OUT_PATH}deceased-people-nursery-count-daily.csv" format csv include technical headers

select *
from   nl_reported_locations_total@inmemorystorage

local export results as "${OUT_PATH}total-reported-locations.csv" format csv include technical headers

select *
from   nl_reported_locations_newly@inmemorystorage

local export results as "${OUT_PATH}total-newly-reported-locations.csv" format csv include technical headers

select *
from   nl_infected_people_total@inmemorystorage

local export results as "${OUT_PATH}infected-people-total.csv" format csv include technical headers

select *
from   nl_infected_people_delta_normalized@inmemorystorage

local export results as "${OUT_PATH}infected-people-delta-normalized.csv" format csv include technical headers

select *
from   nl_intake_share_age_groups@inmemorystorage

local export results as "${OUT_PATH}intake-share-age-groups.csv" format csv include technical headers

select *
from   nl_reproduction_indexes@inmemorystorage

local export results as "${OUT_PATH}reproduction-index.csv" format csv include technical headers

select *
from   nl_reproduction_indexes_last_known_avg@inmemorystorage

local export results as "${OUT_PATH}reproduction-index-last-known-average.csv" format csv include technical headers

select *
from   nl_rioolwatermetingen@inmemorystorage

local export results as "${OUT_PATH}rioolwater-metingen.csv" format csv include technical headers

select *
from   nl_rioolwatermetingen_rwzi@inmemorystorage

local export results as "${OUT_PATH}rioolwater-metingen-per-rwzi.csv" format csv include technical headers