# Virtual graphs and uuid based identifiers

**URL:** <https://community.stardog.com/t/virtual-graphs-and-uuid-based-identifiers/3404>\
**Category:** Support\
**Created:** [December 7, 2021, 9:29pm UTC](https://community.stardog.com/t/virtual-graphs-and-uuid-based-identifiers/3404 "2021-12-07T21:29:04Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![saboris](https://avatars.discourse-cdn.com/v4/letter/s/7c8e57/32.png) [@saboris](https://community.stardog.com/u/saboris)\
**Post date:** [December 7, 2021, 9:29pm UTC](https://community.stardog.com/t/virtual-graphs-and-uuid-based-identifiers/3404/1 "2021-12-07T21:29:04Z")

</div>

Hi,

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

```auto
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

---

<div class="post-metadata">

**Author:** ![jess](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/jess/32/10_2.png) [@jess](https://community.stardog.com/u/jess)\
**Post date:** [December 7, 2021, 10:33pm UTC](https://community.stardog.com/t/virtual-graphs-and-uuid-based-identifiers/3404/2 "2021-12-07T22:33:05Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![saboris](https://avatars.discourse-cdn.com/v4/letter/s/7c8e57/32.png) [@saboris](https://community.stardog.com/u/saboris)\
**Post date:** [December 7, 2021, 10:47pm UTC](https://community.stardog.com/t/virtual-graphs-and-uuid-based-identifiers/3404/3 "2021-12-07T22:47:40Z")

</div>

Hi Jess,

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

Boris

---

<div class="post-metadata">

**Author:** ![jess](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/jess/32/10_2.png) [@jess](https://community.stardog.com/u/jess)\
**Post date:** [December 7, 2021, 11:02pm UTC](https://community.stardog.com/t/virtual-graphs-and-uuid-based-identifiers/3404/4 "2021-12-07T23:02:21Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![saboris](https://avatars.discourse-cdn.com/v4/letter/s/7c8e57/32.png) [@saboris](https://community.stardog.com/u/saboris)\
**Post date:** [December 7, 2021, 11:05pm UTC](https://community.stardog.com/t/virtual-graphs-and-uuid-based-identifiers/3404/5 "2021-12-07T23:05:38Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jess](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/jess/32/10_2.png) [@jess](https://community.stardog.com/u/jess)\
**Post date:** [December 7, 2021, 11:06pm UTC](https://community.stardog.com/t/virtual-graphs-and-uuid-based-identifiers/3404/6 "2021-12-07T23:06:58Z")

</div>

Right, so you can use a SQL function as documented here: [uuid function | Databricks on AWS](https://docs.databricks.com/sql/language-manual/functions/uuid.html)

The mapping would look something like:

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

```

---

<div class="post-metadata">

**Author:** ![saboris](https://avatars.discourse-cdn.com/v4/letter/s/7c8e57/32.png) [@saboris](https://community.stardog.com/u/saboris)\
**Post date:** [December 9, 2021, 6:58am UTC](https://community.stardog.com/t/virtual-graphs-and-uuid-based-identifiers/3404/7 "2021-12-09T06:58:30Z")

</div>

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:

```auto
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:

```auto
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

---

<div class="post-metadata">

**Author:** ![saboris](https://avatars.discourse-cdn.com/v4/letter/s/7c8e57/32.png) [@saboris](https://community.stardog.com/u/saboris)\
**Post date:** [December 9, 2021, 7:34am UTC](https://community.stardog.com/t/virtual-graphs-and-uuid-based-identifiers/3404/8 "2021-12-09T07:34:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![jess](https://yyz2.discourse-cdn.com/flex030/user_avatar/community.stardog.com/jess/32/10_2.png) [@jess](https://community.stardog.com/u/jess)\
**Post date:** [December 9, 2021, 4:39pm UTC](https://community.stardog.com/t/virtual-graphs-and-uuid-based-identifiers/3404/9 "2021-12-09T16:39:06Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![saboris](https://avatars.discourse-cdn.com/v4/letter/s/7c8e57/32.png) [@saboris](https://community.stardog.com/u/saboris)\
**Post date:** [December 9, 2021, 4:58pm UTC](https://community.stardog.com/t/virtual-graphs-and-uuid-based-identifiers/3404/10 "2021-12-09T16:58:00Z")

</div>

Jess,

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

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.

---

<div class="post-metadata">

**Author:** ![system](https://canada1.discourse-cdn.com/flex030/uploads/stardog/original/2X/e/ed66b48a616f505106a4acde3c9cee7e6da9bc67.svg) [@system](https://community.stardog.com/u/system)\
**Post date:** [December 23, 2021, 4:58pm UTC](https://community.stardog.com/t/virtual-graphs-and-uuid-based-identifiers/3404/11 "2021-12-23T16:58:01Z")

</div>

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