search

ontop / ontop

Ontop is a platform to query relational databases as Virtual RDF Knowledge Graphs using SPARQL
https://ontop-vkg.org
Apache License 2.0
635 stars 164 forks source link

Ontop with Microsoft JDBC Driver: Mapping Error #547

Open arrascue opened 1 year ago

arrascue commented 1 year ago

Description

I am using Ontop to create a virtual graph on top of Azure Data Explorer (ADX), which supports the MS JDBC Driver:

My connection properties are the following:

jdbc.url=jdbc:sqlserver://myadxcluster.westeurope.kusto.windows.net:1433;authentication=ActiveDirectoryMSI;database=events;msiClientId=<AUTH_APP>;
jdbc.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
ontop.inferDefaultDatatype=true

My mapping looks as follows:

[PrefixDeclaration]
:               https://example.com/ontology#
owl:            http://www.w3.org/2002/07/owl#
rdf:            http://www.w3.org/1999/02/22-rdf-syntax-ns#
xml:            http://www.w3.org/XML/1998/namespace
xsd:            http://www.w3.org/2001/XMLSchema#
obda:           https://w3id.org/obda/vocabulary#
rdfs:           http://www.w3.org/2000/01/rdf-schema#

[MappingDeclaration] @collection [[
mappingId       EventMapping
target          :Device/{DeviceID} a :Device .
source          SELECT DeviceID FROM DeviceEvents

]]

Problem

If I run $ONTOP_CLI_DIR/ontop validate" on the properties, mappings and ontology, I get the following error:

ERROR: There is a problem loading the mapping file /home/myuser/Mapping.obda
it.unibz.inf.ontop.exception.InvalidMappingSourceQueriesException: Error: Cannot find relation "master"."dbo"."DeviceEvents" (available choices: ["events"."dbo"."DeviceEvents"]) "DeviceEvents" 
Problem location: source query of triplesMap 
[id: EventMapping
target atoms: triple(s,p,o) with s/RDF(https://example.com/ontology#Device/{}(TmpToNVARCHAR(DeviceID)),IRI), p/<http://www.w3.org/1999/02/22-rdf-syntax-ns#type>, o/<https://example.com/ontology#Device>
source query: SELECT DeviceID FROM "DeviceEvents"]
    at it.unibz.inf.ontop.spec.mapping.pp.impl.SQLPPMappingConverterImpl.getRAExpression(SQLPPMappingConverterImpl.java:145)
    at it.unibz.inf.ontop.spec.mapping.pp.impl.SQLPPMappingConverterImpl.convert(SQLPPMappingConverterImpl.java:61)
    at it.unibz.inf.ontop.spec.mapping.impl.SQLMappingExtractor.convert(SQLMappingExtractor.java:245)
    at it.unibz.inf.ontop.spec.mapping.impl.SQLMappingExtractor.convert(SQLMappingExtractor.java:216)
    at it.unibz.inf.ontop.spec.mapping.impl.SQLMappingExtractor.convert(SQLMappingExtractor.java:185)
    at it.unibz.inf.ontop.spec.mapping.impl.SQLMappingExtractor.convertPPMapping(SQLMappingExtractor.java:151)
    at it.unibz.inf.ontop.spec.mapping.impl.SQLMappingExtractor.extract(SQLMappingExtractor.java:106)
    at it.unibz.inf.ontop.spec.impl.DefaultOBDASpecificationExtractor.extract(DefaultOBDASpecificationExtractor.java:46)
    at it.unibz.inf.ontop.injection.impl.OntopMappingConfigurationImpl.loadSpecification(OntopMappingConfigurationImpl.java:127)
    at it.unibz.inf.ontop.injection.impl.OntopMappingSQLConfigurationImpl.loadSpecification(OntopMappingSQLConfigurationImpl.java:87)
    at it.unibz.inf.ontop.injection.impl.OntopMappingSQLAllConfigurationImpl.loadSpecification(OntopMappingSQLAllConfigurationImpl.java:47)
    at it.unibz.inf.ontop.injection.impl.OntopSQLOWLAPIConfigurationImpl.loadOBDASpecification(OntopSQLOWLAPIConfigurationImpl.java:34)
    at it.unibz.inf.ontop.injection.impl.OntopOBDAConfigurationImpl.loadSpecification(OntopOBDAConfigurationImpl.java:44)
    at it.unibz.inf.ontop.cli.OntopValidate.run(OntopValidate.java:120)
    at it.unibz.inf.ontop.cli.Ontop.main(Ontop.java:18)

The problem seems to be in the SQL part of the mapping: SELECT DeviceID FROM DeviceEvents. The message suggests alternatives (like ["events"."dbo"."DeviceEvents"]). Indeed, I can run a query like SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' OR TABLE_TYPE='VIEW' which returns: TABLE_CATALOG: events, TABLE_SCHEMA: dbo, TABLE_NAME: DeviceEvents

Therefore, I assume the authentication is not a problem, since it can fetch this type of metadata. I have tried many variants of writing that SQL statement such as:

Those variations result into a slightly different error:

ERROR: There is a problem loading the mapping file /home/myuser/Mapping.obda
it.unibz.inf.ontop.exception.MappingIOException: it.unibz.inf.ontop.exception.MetadataExtractionException: com.microsoft.sqlserver.jdbc.SQLServerException: Request is invalid and cannot be processed: Semantic error: OTR0001: Scalar SQL function 'SCHEMA_ID' is not supported. [line:position=1:1016]
RequestId: TDS;4bac0e2d-990d-4c21-9551-2059845208f6;15
Time: 2022-08-11T14:38:06.0669249Z
    at it.unibz.inf.ontop.spec.impl.DefaultOBDASpecificationExtractor.extract(DefaultOBDASpecificationExtractor.java:51)
    at it.unibz.inf.ontop.injection.impl.OntopMappingConfigurationImpl.loadSpecification(OntopMappingConfigurationImpl.java:127)
    at it.unibz.inf.ontop.injection.impl.OntopMappingSQLConfigurationImpl.loadSpecification(OntopMappingSQLConfigurationImpl.java:87)
    at it.unibz.inf.ontop.injection.impl.OntopMappingSQLAllConfigurationImpl.loadSpecification(OntopMappingSQLAllConfigurationImpl.java:47)
    at it.unibz.inf.ontop.injection.impl.OntopSQLOWLAPIConfigurationImpl.loadOBDASpecification(OntopSQLOWLAPIConfigurationImpl.java:34)
    at it.unibz.inf.ontop.injection.impl.OntopOBDAConfigurationImpl.loadSpecification(OntopOBDAConfigurationImpl.java:44)
    at it.unibz.inf.ontop.cli.OntopValidate.run(OntopValidate.java:120)
    at it.unibz.inf.ontop.cli.Ontop.main(Ontop.java:18)
Caused by: it.unibz.inf.ontop.exception.MetadataExtractionException: com.microsoft.sqlserver.jdbc.SQLServerException: Request is invalid and cannot be processed: Semantic error: OTR0001: Scalar SQL function 'SCHEMA_ID' is not supported. [line:position=1:1016]
...

I found this article for another type of database which points to a similar problem for another database technology: https://github.com/ontop/ontop/issues/462

Can I then conclude this is a problem with the particular SQL dialect?

arrascue commented 1 year ago

I have created an instance of a Microsoft SQL database with the same table name and schema and I can confirm the mapping and JDBC properties work and I can create an endpoint. It must be something specific to Azure Data Explorer.

If I execute SELECT SCHEMA_ID(); on the Microsoft SQL database then this returns a result, while on ADX I get: Request is invalid and cannot be processed: Semantic error: OTR0001: Scalar SQL function 'SCHEMA_ID' is not supported. [line:position=1:8]

bcogrel commented 1 year ago

Hi @arrascue, thanks for reporting this compatibility issue with Azure Data Explorer.

Let's hope support for this function will be added on the Microsoft side.