SPARQL_Intro.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "colab": {
      "provenance": [],
      "toc_visible": true,
      "authorship_tag": "ABX9TyPFbBu7koSYt40Y8vPfUviF",
      "include_colab_link": true
    },
    "kernelspec": {
      "name": "python3",
      "display_name": "Python 3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "view-in-github",
        "colab_type": "text"
      },
      "source": [
        "<a href=\"https://colab.research.google.com/github/AhmadAghaebrahimian/UZH-BIO390/blob/master/SPARQL_Intro.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AWIypj62gFNs"
      },
      "source": [
        "**SPARQL**"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "DzDCOmClhoXP"
      },
      "source": [
        "1. Prefix declarations: Abbreviating URIs\n",
        "          PREFIX foo: <http://example.com/resources/>\n",
        "\n",
        "2. Result clause: Identify what information to return\n",
        "          SELECT *\n",
        "\n",
        "3. Dataset definition: Which RDF graph(s) to query\n",
        "          FROM\n",
        "\n",
        "4. Query pattern: Specify what to query\n",
        "          WHERE {\n",
        "                  ?s ?p ?o .\n",
        "                }\n",
        "\n",
        "5. Modifiers, Slicing, Ordering, or Rearranging the results\n",
        "          ORDER BY ?s"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "y9hPW2i64L49"
      },
      "source": [
        "\n",
        "Open Dbpedia Sparql endpoint https://dbpedia.org/sparql\n",
        "\n",
        "      PREFIX rdf:\t<http://www.w3.org/1999/02/22-rdf-syntax-ns#>\n",
        "      \n",
        "      SELECT DISTINCT ?concept\n",
        "      WHERE {\n",
        "          ?subject rdf:type ?concept .\n",
        "      } \n",
        "      ORDER BY DESC(?concept)\n",
        "      LIMIT 10\n",
        "      OFFSET 2\n",
        "\n",
        "\n",
        "      PREFIX dbp-owl: <http://dbpedia.org/ontology/>\n",
        "      PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>\n",
        "      \n",
        "      SELECT DISTINCT ?country ?label \n",
        "      WHERE {\n",
        "        ?country a dbp-owl:Country .        \n",
        "        ?country rdfs:label ?label .           \n",
        "        FILTER langMatches(lang(?label),\"en\")  \n",
        "        FILTER contains(?label,\"land\")         \n",
        "      }\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "NEhWoo1p67NV"
      },
      "source": [
        "Built-in filter functions\n",
        "\n",
        "    Logical: !, &&, ||\n",
        "    Math: +, -, *, /\n",
        "    Comparison: =, !=, >, <, ...\n",
        "    SPARQL tests: isURI, isBlank, isLiteral, bound\n",
        "    SPARQL accessors: str, lang, datatype\n",
        "    Other: sameTerm, langMatches, regex"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "D6K5Hf9ZJakn"
      },
      "source": [
        "Install Sparql Engine\n",
        "\n",
        "We use Fuseki server for as the Sparql endpoint. You can download it from the following link. Please make sure you have Java installed on your machine.\n",
        "\n",
        "https://jena.apache.org/download/index.cgi\n",
        "\n",
        "\n",
        "Download 'apache-jena-fuseki-4.6.1.zip' and unzip it somewhere in your machine\n",
        "\n",
        "In the terminal where you unzipped the file:\n",
        "\n",
        "     ./fuseki-server --update --mem /graph\n",
        "\n",
        "In the browser\n",
        "\n",
        "    http://localhost:3030/\n",
        "\n",
        "Get the example files\n",
        "\n",
        "    http://www.learningsparql.com/2ndeditionexamples/index.html"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "o1Y9zKkMP01J"
      },
      "source": [
        "Upload 'ex002.ttl'\n",
        "Get Craig's email\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#> \n",
        "\n",
        "    SELECT ?craigEmail\n",
        "    WHERE { \n",
        "            ab:craig ab:email ?craigEmail . \n",
        "          }"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bX1rgPSdWTfP"
      },
      "source": [
        "Graph traversal\n",
        "Load ex012.ttl\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#> \n",
        "\n",
        "    SELECT ?person\n",
        "    WHERE { \n",
        "            ?person ab:homeTel \"(229) 276-5135\" . \n",
        "          } \n",
        "\n",
        "Linking\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#> \n",
        "\n",
        "    SELECT ?first ?last\n",
        "    WHERE\n",
        "    {\n",
        "      ?person ab:homeTel \"(229) 276-5135\" . \n",
        "      ?person ab:firstName ?first . \n",
        "      ?person ab:lastName  ?last . \n",
        "    }\n",
        "\n",
        "Integration\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#> \n",
        "\n",
        "    SELECT ?full_name\n",
        "    WHERE\n",
        "    {\n",
        "      ?person ab:homeTel \"(229) 276-5135\" . \n",
        "      ?person ab:firstName ?first . \n",
        "      ?person ab:lastName  ?last . \n",
        "      BIND (concat(?first, ' ', ?last) AS ?full_name) .\n",
        "    }\n",
        "\n",
        "Multiple conditions\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#> \n",
        "\n",
        "    SELECT ?first ?last\n",
        "    WHERE\n",
        "    {\n",
        "      ?person ab:homeTel \"(229) 276-5135\" . \n",
        "      ?person ab:firstName ?first . \n",
        "      ?person ab:lastName  ?last . \n",
        "    }\n",
        "\n",
        "Shortcut ;\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#> \n",
        "\n",
        "    SELECT ?first ?last\n",
        "    WHERE\n",
        "    {\n",
        "      ?person ab:homeTel \"(229) 276-5135\" ; \n",
        "              ab:firstName ?first ; \n",
        "              ab:lastName  ?last . \n",
        "    }\n",
        "\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#>\n",
        "    PREFIX foaf: <http://xmlns.com/foaf/0.1/> \n",
        "\n",
        "    SELECT ?name ?homepage\n",
        "    WHERE\n",
        "    {\n",
        "      ?name ab:firstName \"John\" ;\n",
        "            foaf:homepage  \"<https://first-homepage.com>\" ,\n",
        "                           \"<https://second-homepage.com>\" .\n",
        "                            \n",
        "           \n",
        "    }\n",
        "\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#>\n",
        "    PREFIX foaf: <http://xmlns.com/foaf/0.1/> \n",
        "    Prefix xsd: <http://www.w3.org/2001/XMLSchema#>\n",
        "\n",
        "    SELECT ?name ?last\n",
        "    WHERE\n",
        "    {\n",
        "      ?name ab:firstName \"Jürgen\"@de ; \n",
        "            foaf:age \"32\"^^xsd:integer .\n",
        "           \n",
        "    }\n",
        "    "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "plW__5lrb3yV"
      },
      "source": [
        "Optional conditions\n",
        "\n",
        "Load ex054.ttl\n",
        "\n",
        "Logical AND\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#> \n",
        "\n",
        "    SELECT ?first ?last ?workTel ?nick\n",
        "    WHERE\n",
        "    {\n",
        "      ?s  ab:firstName ?first ;\n",
        "          ab:lastName ?last ;\n",
        "          ab:workTel ?workTel ; \n",
        "          ab:nick ?nick . \n",
        "\n",
        "    }\n",
        "\n",
        "Logical OR\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#> \n",
        "\n",
        "    SELECT ?first ?last ?workTel ?nick\n",
        "    WHERE\n",
        "    {\n",
        "      ?s ab:firstName ?first ;\n",
        "         ab:lastName ?last .\n",
        "      OPTIONAL { ?s ab:workTel ?workTel . }\n",
        "      OPTIONAL { ?s ab:nick ?nick .  }\n",
        "\n",
        "    }\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "9Qff8YSgeRSf"
      },
      "source": [
        "Negation\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#> \n",
        "\n",
        "    SELECT ?first ?last \n",
        "    WHERE\n",
        "    {\n",
        "      ?s ab:firstName ?first ;\n",
        "        ab:lastName ?last .\n",
        "      FILTER NOT EXISTS { ?s ab:workTel ?workNum }\n",
        "    }\n",
        "\n",
        "\n",
        "Bind and Filter\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#> \n",
        "    PREFIX foaf: <http://xmlns.com/foaf/0.1/>\n",
        "    Prefix xsd: <http://www.w3.org/2001/XMLSchema#>\n",
        "\n",
        "    SELECT ?first ?last \n",
        "    WHERE\n",
        "    {\n",
        "      ?s ab:firstName ?first ;\n",
        "         ab:lastName ?last ;\n",
        "         foaf:age ?age .\n",
        "\n",
        "      FILTER (!bound(?workNum))\n",
        "      FILTER (xsd:integer(?age) > 30)\n",
        "    }\n",
        "\n",
        " \n",
        "\n",
        "Minus\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#> \n",
        "    SELECT ?first ?last \n",
        "    WHERE\n",
        "    {\n",
        "      ?s ab:firstName ?first ;\n",
        "         ab:lastName ?last .\n",
        "      MINUS { ?s ab:workTel ?workNum }\n",
        "    }\n",
        "\n",
        "Limit search results\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#> \n",
        "    SELECT ?first ?last \n",
        "    WHERE\n",
        "    {\n",
        "      ?s ab:firstName ?first ;\n",
        "         ab:lastName ?last .\n",
        "    }\n",
        "    VALUES ?first {\"Richard\" \"Cindy\"}\n",
        " \n",
        "\n",
        "Graph traversal\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#>\n",
        "    PREFIX foaf: <http://xmlns.com/foaf/0.1/>  \n",
        "    SELECT ?friend \n",
        "    WHERE\n",
        "    {\n",
        "      ?s ab:firstName \"John\" ;\n",
        "         foaf:knows+ ?friend.\n",
        "    }\n",
        "Other regex include: | ! * ? \n",
        "    "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "7A5INhhLgyOa"
      },
      "source": [
        "Multiple graphs\n",
        "\n",
        "Load ex072.ttl, ex073.ttl, ex368.ttl\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#> \n",
        "\n",
        "    SELECT ?last ?first ?courseName \n",
        "    WHERE\n",
        "        {\n",
        "          ?s ab:firstName ?first ;\n",
        "            ab:lastName ?last ;\n",
        "            ab:takingCourse ?course . \n",
        "\n",
        "          ?course ab:courseTitle ?courseName . \n",
        "        }\n",
        "\n",
        "Deduplicate\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#>\n",
        "\n",
        "    SELECT DISTINCT ?first ?last \n",
        "    WHERE\n",
        "        {\n",
        "          ?s ab:takingCourse ?class ;\n",
        "            ab:firstName ?first ;\n",
        "            ab:lastName ?last . \n",
        "        }\n",
        "\n",
        "Union\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#> \n",
        "    PREFIX d:  <http://learningsparql.com/ns/data#> \n",
        "\n",
        "    SELECT * \n",
        "    WHERE\n",
        "    {\n",
        "          { ?person ab:firstName ?first ; \n",
        "                    ab:lastName ?last . }\n",
        "\n",
        "          UNION\n",
        "\n",
        "          { ?course ab:courseTitle ?courseName . }\n",
        "\n",
        "    }\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "ymvT1hGgm9sP"
      },
      "source": [
        "Result modifiers\n",
        "\n",
        "Load ex145.ttl\n",
        "\n",
        "    PREFIX e: <http://learningsparql.com/ns/expenses#>\n",
        "\n",
        "    SELECT ?description ?date ?amount\n",
        "    WHERE\n",
        "    {\n",
        "      ?meal e:description ?description ;\n",
        "            e:date ?date ;\n",
        "            e:amount ?amount . \n",
        "    }\n",
        "\n",
        "    ORDER BY ?amount\n",
        "    LIMIT 1\n",
        "\n",
        "Similarly\n",
        "\n",
        "    PREFIX e: <http://learningsparql.com/ns/expenses#> \n",
        "\n",
        "    SELECT (MAX(?amount) as ?maxAmount) \n",
        "    WHERE { ?meal e:amount ?amount . }\n",
        "\n",
        "Counting, Summing, Averaging, Max and minimizing\n",
        "\n",
        "    PREFIX e: <http://learningsparql.com/ns/expenses#>\n",
        "\n",
        "    SELECT (COUNT(?amount) AS ?count)\n",
        "    WHERE\n",
        "        {\n",
        "          ?meal e:description ?description ;\n",
        "          e:date ?date ;\n",
        "          e:amount ?amount . \n",
        "        }\n",
        "\n",
        "Aggregation\n",
        "\n",
        "    PREFIX e: <http://learningsparql.com/ns/expenses#>\n",
        "\n",
        "    SELECT (SUM(?amount) as ?Sum)\n",
        "    WHERE\n",
        "        {\n",
        "          ?meal e:description ?description ;\n",
        "          e:date ?date ;\n",
        "          e:amount ?amount . \n",
        "        }\n",
        "    GROUP BY ?description\n",
        "    # HAVING (?Sum < 40)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VoiQG0vgvfN2"
      },
      "source": [
        "Subquery\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#>\n",
        "\n",
        "    SELECT ?lastName ?courseName \n",
        "    WHERE\n",
        "    {\n",
        "      {\n",
        "        SELECT ?lastName \n",
        "        WHERE { ?student ab:lastName ?lastName . }\n",
        "      }\n",
        "\n",
        "      {\n",
        "        SELECT ?courseName \n",
        "        WHERE { ?course ab:courseTitle ?courseName . }\n",
        "      }\n",
        "\n",
        "    }"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qvi9rEFDKAuL"
      },
      "source": [
        "Blank node\n",
        "ex041.ttl\n",
        "\n",
        "\n",
        "    @prefix ab: <http://learningsparql.com/ns/addressbook#> .\n",
        "\n",
        "    ab:i0432 ab:firstName    \"Richard\" ;\n",
        "            ab:lastName      \"Mutt\" ;\n",
        "            ab:homeTel       \"(229) 276-5135\" ;\n",
        "            ab:email         \"richard49@hotmail.com\" ;\n",
        "            ab:postalCode    \"49345\" ;\n",
        "            ab:city          \"Springfield\" ;\n",
        "            ab:streetAddress \"32 Main St.\" ;\n",
        "            ab:region        \"Connecticut\" . \n",
        "\n",
        "\n",
        "\n",
        "    @prefix ab: <http://learningsparql.com/ns/addressbook#> .\n",
        "\n",
        "    ab:i0432 ab:firstName    \"Richard\" ;\n",
        "            ab:lastName      \"Mutt\" ;\n",
        "            ab:homeTel       \"(229) 276-5135\" ;\n",
        "            ab:email         \"richard49@hotmail.com\" ;\n",
        "            ab:address       _:b1 .\n",
        "\n",
        "    _:b1    ab:postalCode    \"49345\" ;\n",
        "            ab:city          \"Springfield\" ;\n",
        "            ab:streetAddress \"32 Main St.\" ;\n",
        "            ab:region        \"Connecticut\" . \n",
        "\n",
        "Result?\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#> \n",
        "\n",
        "    SELECT ?addressVal\n",
        "    WHERE { ?s ab:address ?addressVal }\n",
        "\n",
        "-\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#> \n",
        "\n",
        "    SELECT ?firstName ?lastName ?streetAddress ?city ?region ?postalCode\n",
        "    WHERE \n",
        "    { \n",
        "      ?s ab:firstName ?firstName ;\n",
        "        ab:lastName ?lastName ;\n",
        "        ab:address ?address . \n",
        "\n",
        "      ?address ab:postalCode ?postalCode ;\n",
        "              ab:city ?city ;\n",
        "              ab:streetAddress ?streetAddress ;\n",
        "              ab:region ?region . \n",
        "    }"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "BtLNzDJVMb2V"
      },
      "source": [
        "Remote endpoint\n",
        "\n",
        "    SELECT ?p ?o \n",
        "    WHERE\n",
        "    {\n",
        "      SERVICE <http://DBpedia.org/sparql>\n",
        "      { SELECT ?p ?o \n",
        "        WHERE { <http://dbpedia.org/resource/Joseph_Hocking> ?p ?o . }\n",
        "      }\n",
        "    }\n",
        "\n",
        "\n",
        "Named Graph\n",
        "\n",
        "    PREFIX ab: <http://learningsparql.com/ns/addressbook#>\n",
        "\n",
        "    SELECT ?lname ?courseName \n",
        "    FROM <ex069.ttl> \n",
        "    FROM NAMED <ex125.ttl>\n",
        "    FROM NAMED <ex122.ttl> \n",
        "\n",
        "    WHERE\n",
        "    {\n",
        "      { ?student ab:lastName ?lname }\n",
        "      UNION\n",
        "      { GRAPH <ex125.ttl> { ?course ab:courseTitle ?courseName } }\n",
        "    }\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "VCDtLyeCweLK"
      },
      "source": [
        "Open BioPortal Sparql endpoint: http://sparql.bioontology.org/\n",
        "\n",
        "    skos:prefLabel vs. rdfs:label (PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>)\n",
        "\n",
        "      PREFIX skos: <http://www.w3.org/2004/02/skos/core#>\n",
        "      PREFIX snomed-term: <http://purl.bioontology.org/ontology/SNOMEDCT/>\n",
        "\n",
        "      SELECT DISTINCT *\n",
        "      FROM <http://bioportal.bioontology.org/ontologies/SNOMEDCT>\n",
        "      FROM <http://bioportal.bioontology.org/ontologies/globals>\n",
        "      WHERE {\n",
        "          ?x skos:prefLabel ?label .\n",
        "          FILTER (CONTAINS ( UCASE(str(?label)), \"MELANOMA\") )\n",
        "      }"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "q-IMQ_NLYtk_"
      },
      "source": [
        "Open UniProt Sparql endpoint: https://sparql.uniprot.org/\n",
        "\n",
        "    Shortest comments for a UniProtKB Natural Variant Annotation\n",
        "\n",
        "      PREFIX up: <http://purl.uniprot.org/core/>\n",
        "      PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>\n",
        "\n",
        "      SELECT ?annotation ?comment\n",
        "      WHERE {\n",
        "        ?annotation a up:Natural_Variant_Annotation ;\n",
        "                    rdfs:comment ?comment . \n",
        "      } \n",
        "      ORDER BY (STRLEN(?comment))\n",
        "\n",
        "    \n",
        "    \n",
        "    Find how often an article in pubmed was used in an evidence tag\n",
        "\n",
        "      PREFIX up: <http://purl.uniprot.org/core/>\n",
        "      PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>\n",
        "      \n",
        "      SELECT ?source (COUNT(?attribution) AS ?attribitions)\n",
        "      WHERE\n",
        "      {\n",
        "        ?protein  a up:Protein ;\n",
        "                  up:annotation ?annotation .\n",
        "        ?linkToEvidence rdf:object ?annotation ;\n",
        "                        up:attribution ?attribution .\n",
        "        ?attribution up:source ?source .\n",
        "        ?source a up:Journal_Citation .\n",
        "      } \n",
        "      GROUP BY ?source ORDER BY DESC(COUNT(?attribution))\n",
        "      \n",
        " "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "LAXeIy2-QZ88"
      },
      "source": [
        "Open NeXtProt Sparql endpoint: https://snorql.nextprot.org/\n",
        " \n",
        " \n",
        "    List Lipoproteins\n",
        "\n",
        "    SELECT distinct ?protein \n",
        "    WHERE {\n",
        "      ?protein :isoform ?iso.\n",
        "      ?iso :uniprotKeyword / :term cv:KW-0449.\n",
        "    }"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "qs7sTZ0xmiK5"
      },
      "source": [
        "Other usefull Endpoints\n",
        " \n",
        "\t• EBI European Bioinformatics Institute:\n",
        "\t\t- BioSamples, BioModels, ChEMBL, Expression Atlas, Reactome, Ensembl\n",
        "\t\t- https://www.ebi.ac.uk/rdf/services/sparql\n",
        "\n",
        "\t• NCBI National Center for Biotechnology Information:\n",
        "\t\t- PubChemRDF (rdf only, no SPARQL endpoint)\n",
        "\t\t- https://pubchemdocs.ncbi.nlm.nih.gov/rdf\n",
        "\n",
        "\t• SIB:\n",
        "\t\t- http://sparql-playground.sib.swiss/ "
      ]
    }
  ]
}