Virtual graphs and uuid based identifiers

Support
1

Hi,

Is it possible to create a SQL mapping using SMS where the struuid() function is used to generate IRIs. Something like this:

mapping
from sql {
  select * from product
}
to {
  ?product pdf:type domain:Product
}
where {
  bind(template("http://www.mycompany.com/onto/domain/product/{struuid()}"))
}

I understand that every time the mapping is applied there would be a new UUID and that's fine. At some point, when I want to materialize the virtual graph, I'd commit to whatever UUIDs get generated then. In the meantime, every row from the SQL result would get it's UUID every time the query is run.

In general, doesn't look like functions are supported in these templates, or am I missing something? It seems like a silent failure - StarDog Studio doesn't complain about them, yet the resulting graph appears empty.

Thanks!
Boris

2

Hi Boris,

It's possible to put the UUID generation in your SQL query. This would only be safe for importing the data, not for arbitrary queries over the virtual graph.

Jess

3

Hi Jess,

Thanks much for the response! Could you offer a working example?

Boris

4

What database system is the upstream data source? Is it Microsoft SQL Server?

5

Databricks (so...Spark/Hive), but ok, I see what you are saying, functions are not supported in templates.

6

Right, so you can use a SQL function as documented here: uuid function | Databricks on AWS

The mapping would look something like:

FROM SQL {
  select uuid() as my_uuid, ...
}
TO {
  # Triple patterns
}
WHERE {
  bind(template("http://www.mycompany.com/onto/domain/product/{my_uuid}"))
1 Like
7

Hi again Jesse,

Have you tried the example you provided? I am unable to get it to work. Starting with a working mapping, extremely simple, pretty much like this:

FROM SQL {
  select * from table
}
TO {
  # Triple patterns
  ?entity a pref:MyClass .
  ?entity pref:name ?name
}
WHERE {
  bind(template("http://www.mycompany.com/onto/domain/product/{internalId}") as ?entity)
}

I can do a "select *" SPARQL query against the virtual graph and see results. But as soon as I add the call to the uuid function in the select clause, without even using the result, just like this:

FROM SQL {
  select *, uuid() as my_uuid from table
}
TO {
  # Triple patterns
  ?entity a pref:MyClass .
  ?entity pref:name ?name
}
WHERE {
  bind(template("http://www.mycompany.com/onto/domain/product/{internalId}") as ?entity)
}

The SPARQL query returns empty results, no errors in the log.

The SQL query itself ( select *, uuid() as my_uuid from table ) when executed in a Databricks notebook works correctly.

If this is any help, the SPARQL query plan in StarDog Studio show "Empty".

Best,
Boris

8

Also, what is the recommended way to load relational data into a graph? I was hoping to make use of SMS by creating a virtual graph first and then materializing it. But it seems fairly limited (or maybe I'm not looking at the right place for documentation?) - for example does the where clause support anything besides BIND? What if I want to instantiate some variables based on data in the triple store, i.e. matching some existing triples against a value coming from SQL. Is that possible? Or is writing a program in a general purpose language to do the data loading the way forward here?

Thanks for your help, much appreciated!
Boris

9

Boris,

As I mentioned earlier, using a non-deterministically generated value in the subject template will only work with virtual import, not SPARQL queries. Can you try importing the graph?

10

Jess,

You mentioned that it would only be "safe" not that it would only "work" - those are different things :slight_smile:

I can give the import a try, ok. But the issue is that developing those mapping is a development cycle of its own and if I need to import the data every time, the cycle becomes quite long.

11

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.