Duo bied data aan via CKAN. (Pip installable, dus python 😁) CKAN is bedoeld om data aan te bieden, juist voor overheden. Waaronder search mogelijkheden, of een PostgresQL 9.1 webinterface.

• https://duo.nl/open_onderwijsdata/images/technische-toelichting-gebruik-duo-api-met-open-onderwijsdata.pdf

Eenvoudige queries

https://onderwijsdata.duo.nl/api/3/action/help_show?name=datastore_search

Search a DataStore resource.

    The datastore_search action allows you to search data in a resource.
    DataStore resources that belong to private CKAN resource can only be
    read by you if you have access to the CKAN resource and send the
    appropriate authorization.

    :param resource_id: id or alias of the resource to be searched against
    :type resource_id: string
    :param filters: matching conditions to select, e.g
                    {"key1": "a", "key2": "b"} (optional)
    :type filters: dictionary
    :param q: full text query. If it's a string, it'll search on all fields on
              each row. If it's a dictionary as {"key1": "a", "key2": "b"},
              it'll search on each specific field (optional)
    :type q: string or dictionary
    :param distinct: return only distinct rows (optional, default: false)
    :type distinct: bool
    :param plain: treat as plain text query (optional, default: true)
    :type plain: bool
    :param language: language of the full text query
                     (optional, default: english)
    :type language: string
    :param limit: maximum number of rows to return (optional, default: 100)
    :type limit: int
    :param offset: offset this number of rows (optional)
    :type offset: int
    :param fields: fields to return
                   (optional, default: all fields in original order)
    :type fields: list or comma separated string
    :param sort: comma separated field names with ordering
                 e.g.: "fieldname1, fieldname2 desc"
    :type sort: string
    :param include_total: True to return total matching record count
                          (optional, default: true)
    :type include_total: bool
    :param records_format: the format for the records return value:
        'objects' (default) list of {fieldname1: value1, ...} dicts,
        'lists' list of [value1, value2, ...] lists,
        'csv' string containing comma-separated values with no header,
        'tsv' string containing tab-separated values with no header
    :type records_format: controlled list


    Setting the ``plain`` flag to false enables the entire PostgreSQL
    `full text search query language`_.

    A listing of all available resources can be found at the
    alias ``_table_metadata``.

    .. _full text search query language: http://www.postgresql.org/docs/9.1/static/datatype-textsearch.html#DATATYPE-TSQUERY

    If you need to download the full resource, read :ref:`dump`.

    **Results:**

    The result of this action is a dictionary with the following keys:

    :rtype: A dictionary with the following keys
    :param fields: fields/columns and their extra metadata
    :type fields: list of dictionaries
    :param offset: query offset value
    :type offset: int
    :param limit: query limit value
    :type limit: int
    :param filters: query filters
    :type filters: list of dictionaries
    :param total: number of total matching records
    :type total: int
    :param records: list of matching results
    :type records: depends on records_format value passed

PostgresQL Access

Onderstaande links geven toelichting, in meerdere of mindere mate over de mogelijkheden om rechstreeks SQL af te kunnen voeren op de CKAN backend .

1. https://onderwijsdata.duo.nl/api/3/action/help_show?name=datastore_search_sql
2. https://docs.ckan.org/en/latest/maintaining/datastore.html#ckanext.datastore.logic.action.datastore_search_sql

Execute SQL queries on the DataStore.

    The datastore_search_sql action allows a user to search data in a resource
    or connect multiple resources with join expressions. The underlying SQL
    engine is the
    `PostgreSQL engine <http://www.postgresql.org/docs/9.1/interactive/>`_.
    There is an enforced timeout on SQL queries to avoid an unintended DOS.
    DataStore resource that belong to a private CKAN resource cannot be
    searched with this action. Use
    :meth:`~ckanext.datastore.logic.action.datastore_search` instead.

    .. note:: This action is only available when using PostgreSQL 9.X and
        using a read-only user on the database.
        It is not available in :ref:`legacy mode<legacy-mode>`.

    :param sql: a single SQL select statement
    :type sql: string

    **Results:**

    The result of this action is a dictionary with the following keys:

    :rtype: A dictionary with the following keys
    :param fields: fields/columns and their extra metadata
    :type fields: list of dictionaries
    :param records: list of matching results
    :type records: list of dictionaries

Dit laatste maakt het mogelijk om queries uit te voeren, waarin verschillende tabellen aan elkaar geknoopt kunnen worden. De uitdaging zit hem in de join zelf, want kolomnamen herkent hij nog niet.

Tabelnamen en GIDs

Maar helaas... Probeer het zelf ...

SELECT * 
  from "320b3f53-24d1-40d0-836e-9129326e445e" instellingenbo 
  inner join "36dc8898-d47d-45d4-91bf-b4c3354b0f7a" vestigingenbo 
    on instellingenbo."brin nummer" = vestigingenbo."brin nummer"

Waarom zou ik dat verwachten? Zodra ik kolommen wil gebruiken om te selecteren, op te filteren of op te joinen, krijg ik bovengenoemde fouten. Maar het meest simpele, waarvoor je niet echt SQL nodig hebt, dat werkt.

https://onderwijsdata.duo.nl/api/3/action/datastore_search_sql?sql=SELECT * from "320b3f53-24d1-40d0-836e-9129326e445e" limit 1  levert:

{
  "help": "https://onderwijsdata.duo.nl/api/3/action/help_show?name=datastore_search_sql",
  "success": true,
  "result": {
    "records": [
      {
        "STRAATNAAM CORRESPONDENTIEADRES": "Oosterstraat",
        "NODAAL GEBIED NAAM": "Emmen",
        "GEMEENTENAAM": "EMMEN",
        "RMC-REGIO NAAM": "Zuid-Oost Drenthe",
        "RPA-GEBIED NAAM": "Zuid- en Midden-Drenthe",
        "WGR-GEBIED CODE": "8",
        "POSTCODE": "7822HG",
        "INTERNETADRES": "www.obs-eenspan.nl",
        "GEMEENTENUMMER": "114",
        "ONDERWIJSGEBIED NAAM": "Assen-Hoogeveen-Emmen",
        "ONDERWIJSGEBIED CODE": "4",
        "PLAATSNAAM": "EMMEN",
        "PLAATSNAAM CORRESPONDENTIEADRES": "EMMEN",
        "DENOMINATIE": "Openbaar",
        "COROPGEBIED NAAM": "Zuidoost-Drenthe",
        "BRIN NUMMER": "16AH",
        "NODAAL GEBIED CODE": "12",
        "BEVOEGD GEZAG NUMMER": "10249",
        "PROVINCIE": "Drenthe",
        "WGR-GEBIED NAAM": "Zuidoost-Drenthe",
        "RMC-REGIO CODE": "8",
        "HUISNUMMER-TOEVOEGING": "58",
        "RPA-GEBIED CODE": "5",
        "POSTCODE CORRESPONDENTIEADRES": "7822HG",
        "HUISNUMMER-TOEVOEGING CORRESPONDENTIEADRES": "58",
        "INSTELLINGSNAAM": "Openbare Basisschool 't Eenspan",
        "COROPGEBIED CODE": "8",
        "_full_text": "'10249':27 '114':22 '12':6 '16ah':9 '4':32 '5':35 '58':8 '591659840':33 '7822hg':36 '8':34 'assen':19 'assen-hoogeveen-emmen':18 'basisschool':29 'drenth':5,13,17,26 'eenspan':31 'emmen':14,15,21 'en':2 'hoogeveen':20 'midden':4 'midden-drenth':3 'oost':25 'oosterstraat':10 'openbaar':16 'openbar':28 'www.obs-eenspan.nl':7 'zuid':1,24 'zuid-oost':23 'zuidoost':12 'zuidoost-drenth':11",
        "STRAATNAAM": "Oosterstraat",
        "_id": 1,
        "TELEFOONNUMMER": "591659840"
      }
    ],
    "fields": [
      {
        "type": "int4",
        "id": "_id"
      },
      ...
    ],
    "sql": "SELECT * from \"320b3f53-24d1-40d0-836e-9129326e445e\" limit 1"
  }
}

Let's throw some python at it

Om de data alsnog te downloaden en te mergen maak ik gebruik van twee krachtige libraries: requests en pydal. Als ik dat zelf doe, maak ik een ETL proces. Extraction, Transformation en Load. Dus Extracten uit DUO, Transformeren tot zoals wij het willen hebben en dan Loaden onze eigen database in.
Dat zou in een keer kunnen, maar in twee slagen is dat misschien makkelijker. Daarom doe ik twee ETL slagen. In de eerste combineren we de bron data vanuit DUO die nog erg gemakkelijk aan elkaar te knopen is juist omdat ze van dezelfde bron komen. Dit slaan we op in een SQLite in memory database. Daarmee doen we nog geen joins, en als de schema's aan de kant van DUO veranderen, komen ze in memory ook zo bij ons aan.

In ETL stap 2 combineren en filteren we die data en maken daar ons eigen extract van, wat we weer pushen naar onze eigen postgres database.

In deel twee hiervan duiken we de python kan in.

Antwoord van DUO

Beste Remco,

Er zijn een aantal dingen waar je op moet letten. Zo moeten tabellen en kolommen met een dubbele quote worden geselecteerd. String waarden moeten met een enkele quote worden geselecteerd. Daarnaast zag ik dat je bij query 1 een kolom probeert te joinen die niet bestaat. Hieronder een voorbeeld van een werkende join, maar met alternatieve kolommen.

1. https://onderwijsdata.duo.nl/api/3/action/datastore_search_sql?sql=SELECT * from "320b3f53-24d1-40d0-836e-9129326e445e" instellingenbo inner join "36dc8898-d47d-45d4-91bf-b4c3354b0f7a" vestigingenbo on instellingenbo."PLAATSNAAM"=vestigingenbo."STRAATNAAM"
2. https://onderwijsdata.duo.nl/api/3/action/datastore_search_sql?sql=SELECT * from "320b3f53-24d1-40d0-836e-9129326e445e" where "PLAATSNAAM" LIKE 'EMMEN' limit 1

Zo te horen hebben jullie alle documentatie die wij hier omtrent leveren. De documentatie van de CKAN Datastore API is jullie ook bekend?
https://docs.ckan.org/en/2.7/maintaining/datastore.html#the-datastore-api

Met vriendelijke groet,

Dus daar bne ik mee bezig! Volgende vraag terug is geplaatst. Hopelijk maandag verder!