FK-Constraint Isses with Ontop and Dremio (using Lenses)

[Frank Nebel]

Hello Community,

I have a strange problem that relates to FK-constraints. I am using Ontop with Dremio and therefore need to define FK-constraints. For this, I am using Lenses.

I setup a very easy example. I took the United Nations list for geographic subregions and continental regions.

I have two views in Dremio

ContinentalRegion
- id (PK)
- name

GeographicSubregions
- id (PK)
- name
- geographicalSubregionPartOfContinentalRegion (FK)

I specified this as follows

{
      "name" : [
        "\"lenses\"",
        "\"X.Y.Z\"",
        "\"GeographicalSubregion\""
      ],
      "baseRelation" : [
        "\"X.Y.Z\"",
        "\"GeographicalSubregion\""
      ],
      "filterExpression" : null,
      "columns" : {
        "added" : [
        ],
        "hidden" : [
        ]
      },
      "uniqueConstraints" : {
        "added" : [
          {
            "name" : "95114c51-d722-49aa-9460-7883da8b338b",
            "determinants" : [
              "\"id\""
            ],
            "isPrimaryKey" : false
          }
        ]
      },
      "otherFunctionalDependencies" : {
        "added" : [
        ]
      },
      "foreignKeys" : {
        "added" : [
          {
            "name" : "943125aa-9e2c-49fc-8efc-ee62ef175471",
            "from" : [
              "\"geographicalSubregionPartOfContinentalRegion\""
            ],
            "to" : {
              "relation" : [
                "\"X.Y.Z\"",
                "\"ContinentalRegion\""
              ],
              "columns" : [
                "\"id\""
              ]
            }
          }
        ]
      },
      "nonNullConstraints" : {
        "added" : [
          "\"id\""
        ]
      },
      "type" : "BasicLens"
    }



{
      "name" : [
        "\"lenses\"",
        "\"X.Y.Z\"",
        "\"ContinentalRegion\""
      ],
      "baseRelation" : [
        "\"X.Y.Z\"",
        "\"ContinentalRegion\""
      ],
      "filterExpression" : null,
      "columns" : {
        "added" : [
        ],
        "hidden" : [
        ]
      },
      "uniqueConstraints" : {
        "added" : [
          {
            "name" : "2fb4ba94-b724-4997-bba6-f621fb00b4ef",
            "determinants" : [
              "\"id\""
            ],
            "isPrimaryKey" : false
          }
        ]
      },
      "otherFunctionalDependencies" : {
        "added" : [
        ]
      },
      "foreignKeys" : {
        "added" : [
        ]
      },
      "nonNullConstraints" : {
        "added" : [
          "\"id\""
        ]
      },
      "type" : "BasicLens"
    }


When I now run my query:

PREFIX meta: <https://ABC#>
PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#>
SELECT *
WHERE {
  ?cr rdf:type meta:ContinentalRegion .  
}

I get the typical UNION ALL behaviour that happens if no FK-constraints are used.

SELECT DISTINCT v5."id0m73" AS "id0m73"
FROM ((SELECT v1."id" AS "id0m73"
FROM "X.Y.Z"."ContinentalRegion" v1
WHERE v1."id" IS NOT NULL
)

UNION ALL

(SELECT v3."geographicalSubregionPartOfContinentalRegion" AS "id0m73"
FROM "X.Y.Z"."GeographicalSubregion" v3
WHERE v3."geographicalSubregionPartOfContinentalRegion" IS NOT NULL
)) v5


I tried all kinds of things with escaping, because this was necessary in the past when worked with DB metadata file.

I am using GraphDB 10.7.1 and the corresponding Ontop instance as well as Dremio (Build: 25.1.1-202409260159070462-716c0676)

Maybe one of you has seen similar behavior when working with Dremio.

Thanks and all the best

Frank

[Frank Nebel]

Hello, I forgot to add the mapping files

[ <http://www.w3.org/ns/r2rml#logicalTable>
          [ <http://www.w3.org/ns/r2rml#tableName>
                    "\"lenses\".\"X.Y.Z\".\"GeographicalSubregion\"" ];
  <http://www.w3.org/ns/r2rml#subjectMap>
          [ <http://www.w3.org/ns/r2rml#class>
                    base:GeographicalSubregion;
            <http://www.w3.org/ns/r2rml#template>
                    "https://ABC/data/gs#{\"id\"}";
            <http://www.w3.org/ns/r2rml#termType>
                    <http://www.w3.org/ns/r2rml#IRI>
          ]
] .

[ <http://www.w3.org/ns/r2rml#logicalTable>
          [ <http://www.w3.org/ns/r2rml#tableName>
                    "\"lenses\".\"X.Y.Z\".\"GeographicalSubregion\"" ];
  <http://www.w3.org/ns/r2rml#predicateObjectMap>
          [ <http://www.w3.org/ns/r2rml#objectMap>
                    [ <http://www.w3.org/ns/r2rml#template>
                              "https://ABC/data/cr#{\"geographicalSubregionPartOfContinentalRegion\"}";
                      <http://www.w3.org/ns/r2rml#termType>
                              <http://www.w3.org/ns/r2rml#IRI>
                    ];
            <http://www.w3.org/ns/r2rml#predicateMap>
                    [ <http://www.w3.org/ns/r2rml#constant>
                              base:geographicalSubregionPartOfContinentalRegion ]
          ];
  <http://www.w3.org/ns/r2rml#subjectMap>
          [ <http://www.w3.org/ns/r2rml#template>
                    "https://ABC/data/gs#{\"id\"}";
            <http://www.w3.org/ns/r2rml#termType>
                    <http://www.w3.org/ns/r2rml#IRI>
          ]
] .


[ <http://www.w3.org/ns/r2rml#logicalTable>
          [ <http://www.w3.org/ns/r2rml#tableName>
                    "\"lenses\".\"X.Y.Z\".\"ContinentalRegion\"" ];
  <http://www.w3.org/ns/r2rml#subjectMap>
          [ <http://www.w3.org/ns/r2rml#class>
                    base:ContinentalRegion;
            <http://www.w3.org/ns/r2rml#template>
                    "https://ABC/data/cr#{\"id\"}";
            <http://www.w3.org/ns/r2rml#termType>
                    <http://www.w3.org/ns/r2rml#IRI>
          ]
] .

Best

Frank

[Benjamin Cogrel]

Hi Frank,

This problem has likely been fixed by https://github.com/ontop/ontop/pull/759, shipped with Ontop 5.2.0 (August 2024).

The GraphDB version you reported is based on Ontop 5.0.2 (https://graphdb.ontotext.com/documentation/10.7/virtualization.html), which is almost 2 years old.

Unfortunately the latest version of GraphDB (10.8) is based on Ontop 5.1.0.

Could you please give it a try with Ontop 5.2.x or 5.3.0 to confirm the issue is gone?

Best,

Benjamin

--
Please follow our guidelines on how to report a bug https://ontop-vkg.org/community/contributing/bug-report
---
You received this message because you are subscribed to the Google Groups "ontop4obda" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ontop4obda+...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/ontop4obda/f035057e-2cd1-4d64-ac14-45d90ec54096n%40googlegroups.com.