Mappings: teaching Stardog to read your tables

Hand a new hire a printout of the customer table and ask what it means. They'll get most of it right by squinting at the headers: cid is probably an ID, location looks like a UUID pointing somewhere, ssn is scary. Stardog can't squint. After Chapter 7 it can reach Aurora, but it doesn't know which column is the key, which is a pointer, or which ontology property each feeds. A mapping is the note you'd staple to the printout: "run this SQL; for every row, produce these triples." You'll read one line by line, meet the IRI template that Chapter 9 depends on, and create your first two virtual graphs.

8.1What a mapping is

A mapping has an input, a SQL query, and an output, triple patterns with variables in them. For every row, Stardog fills the variables and emits the triples. For a virtual graph nothing is emitted in advance: the mapping is a recipe on file, and Stardog reads it backwards: when a query asks for ?c :email ?e, it looks up ":email comes from the email column of the customer SQL" and generates SQL to fetch just that.

Here is the smallest useful mapping, for Carolyn's email. It has three parts, each marked by a keyword:

MAPPING
FROM SQL {                                   # 1. input: a SQL query on Aurora
  SELECT cid, email FROM c360.customer
}
TO {                                         # 2. output: the triples to produce
  ?c :email ?email .
}
WHERE {                                      # 3. how to build the names
  BIND(TEMPLATE("urn:stardog:demos:c360:customer:{cid}") AS ?c)
}

For the row cid = 225, email = 'cinworth68@g.co', the FROM SQL part supplies the columns, the WHERE part turns 225 into the name customer:225, and the TO part produces the triple customer:225 :email "cinworth68@g.co". Section 8.3 goes through each part in detail.

So who knows that customers live in Aurora? Not the ontology (it only defines what :Customer means) and not Aurora (it's never heard of the ontology). The mapping does: it names ontology terms in its TO block and tables in its FROM SQL, and the virtual graph ties it to a data source. Chapter 9 draws this as three layers.

8.2Two languages: R2RML and SMS2

R2RML ("RDB to RDF Mapping Language") is the W3C standard for this job, portable between vendors but verbose: it is itself written as Turtle (rr:subjectMap, rr:predicateObjectMap, rr:column…), and a simple table takes twenty lines. Stardog reads it.

SMS2 (Stardog Mapping Syntax 2) is Stardog's own format: it looks like SPARQL's CONSTRUCT (Chapter 4) and also handles CSV and JSON. The docs recommend it, every mapping in this book uses it, and you select it with the virtual-graph option mappings.syntax=SMS2.

8.3The shape of an SMS2 mapping

An SMS2 file has a prologue (prefix declarations, exactly like Turtle and SPARQL) followed by one or more mapping blocks separated by semicolons. Each block has four parts:

• MAPPING <iri> names the block, for humans and error messages; it doesn't appear in the data.
• FROM SQL { … } is ordinary SQL in the source's dialect. Each output column becomes a variable: email becomes ?email.
• TO { … } lists triple patterns using those variables, in the ontology terms you loaded in Chapters 5 and 6.
• WHERE { … } creates new variables with BIND. For SQL sources, only two kinds of function are allowed: TEMPLATE(…) to build IRIs, and datatype casts such as xsd:integer(…), xsd:decimal(…), xsd:date(…) (plus strdt). Anything else, such as string concatenation, has to happen in the SQL.

8.4The customer mapping, line by line

We will map Aurora's data into two virtual graphs. aurora_c360_safe holds everything except the sensitive values: Carolyn's name, email, phone and address go here. aurora_c360_pii holds only the sensitive ones (PII, personally identifiable information): her SSN 640-83-5765 (fake data) and full card numbers. Keeping them apart lets you later give Marketing the safe graph and HR both (Section 8.7 explains why, Chapter 12 does it).

Here is the first block of aurora_c360_safe.sms, the mapping file for the safe graph (.sms is just the file extension for Stardog Mapping Syntax, like .sql for SQL), in full:

prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#>
prefix xsd: <http://www.w3.org/2001/XMLSchema#>
prefix : <tag:stardog:api:ecomm:>

MAPPING <urn:c360:aurora:customer>
FROM SQL { SELECT cid, first_name, last_name, email, phone, location, full_name FROM c360.customer }
TO {
  ?iri a :Customer ;
    :email ?email ; :firstName ?first_name ; :lastName ?last_name ;
    :phone ?phone ; :address ?loc_iri ; :name ?full_name ; rdfs:label ?full_name .
}
WHERE {
  BIND(TEMPLATE("urn:stardog:demos:c360:customer:{cid}") AS ?iri)
  BIND(TEMPLATE("urn:stardog:demos:c360:location:{location}") AS ?loc_iri)
}

8.5IRI templates: the most important line in the file

An IRI is RDF's globally unique name for a thing (Chapter 3). An IRI template is a string with {column} placeholders that turns a row's local key into such a name: cid = 225 means nothing outside the customer table, but urn:stardog:demos:c360:customer:225 means one customer anywhere. Three things matter in practice:

1. A template is a primary key and a foreign key at once. Used as a subject (?iri in the customer mapping) it identifies the row. Used as an object (?loc_iri) it points at another row. location:{location} in the customer mapping and location:{id} in the address mapping produce the same string for the same UUID: the matching names are the join.
2. Stardog can read a template backwards. Ask about <urn:stardog:demos:c360:customer:225>. The slow way would be to fetch all 500 customer rows, build a name for each (…customer:1 … …customer:500) and compare every one with yours. Instead, Stardog knows the template is …customer:{cid}, removes the fixed part, is left with 225, and sends Aurora SELECT … FROM c360.customer WHERE cid = 225: one row, using the database's index.
3. Values are percent-encoded. By default (percent.encode=true) illegal IRI characters such as spaces are escaped: "Acme Sports" becomes …vendor:Acme%20Sports (Chapter 9's vendor mapping).

These are the exact IRIs the original c360 kit used, so anything written against the kit keeps working. And the Redshift mapping will mint the same customer IRI from purchase.cid; Chapter 9 is built on that fact.

8.6The other Aurora mappings: address, card, rewards

The rest of aurora_c360_safe.sms uses the same four parts; each block adds one idea.

Address: computed values belong in the SQL

MAPPING <urn:c360:aurora:address>
FROM SQL {
  SELECT id, city, state, zip,
         street_number || ' ' || street_name || ' ' || street_suffix AS street_addr,
         street_number || ' ' || street_name || ', ' || state       AS label
  FROM c360.address
}
TO {
  ?iri a :Address ; :city ?city ; :streetAddress ?street_addr ; :zipCode ?zip ; :state ?state ; rdfs:label ?label .
}
WHERE { BIND(TEMPLATE("urn:stardog:demos:c360:location:{id}") AS ?iri) }

The ontology has one :streetAddress; the table has three street columns. Since BIND can't concatenate for SQL sources, the SELECT does it and the alias becomes a variable. The subject template, location:{id}, is where the customer's :address arrow lands.

Card: expose the fact, not the number

MAPPING <urn:c360:aurora:card>
FROM SQL { SELECT id, cid, card_type, card_type || ' ' || right(card_num, 4) AS label FROM c360.credit_card }
TO { ?cc a :CreditCard ; rdfs:label ?label ; :cardType ?card_type ; :cardHolder ?cust_iri . }
WHERE {
  BIND(TEMPLATE("urn:stardog:demos:c360:cards:{id}") AS ?cc)
  BIND(TEMPLATE("urn:stardog:demos:c360:customer:{cid}") AS ?cust_iri)
}

The safe graph knows a card's type and holder, and labels it with the type and last four digits (the usual "safe to display" convention). The full card_num is never exposed as a column, so no triple can carry it.

Rewards: an arrow in the other direction, and a cast

MAPPING <urn:c360:aurora:rewards>
FROM SQL { SELECT id, cid, account_id, create_date FROM c360.rewards_account }
TO {
  ?r a :RewardsAccount ; :openDate ?open_dt ; :accountId ?account_id ; rdfs:label ?account_id .
  ?cust :hasRewards ?r .
}
WHERE {
  BIND(TEMPLATE("urn:stardog:demos:c360:customer:{cid}") AS ?cust)
  BIND(TEMPLATE("urn:stardog:demos:c360:rewards:{id}") AS ?r)
  BIND(xsd:date(?create_date) AS ?open_dt)
}

Two new ideas. The TO block has two subjects: the row describes the account and emits ?cust :hasRewards ?r with the customer as subject. The foreign key lives on the rewards table, but the ontology's arrow points from customer to account (Chapter 6), and a mapping may emit arrows in whichever direction the ontology wants. And xsd:date(?create_date) is a cast: it types the literal as xsd:date, so date comparisons and YEAR() work (Chapter 3).

8.7Safe and PII: why two graphs

Aurora holds two things marketing should never see: SSNs and full card numbers. Rather than hide them later, the build splits Aurora into two virtual graphs over the same data source:

• aurora_c360_safe: customers, addresses, cards (type and last four only), rewards accounts. Everything you've seen so far.
• aurora_c360_pii: only the two sensitive properties, :ssn and :cardNumber.

Here is the whole PII mapping. It's short on purpose:

# Virtual graph: aurora_c360_pii — ONLY the sensitive properties. Grant to hr_user, not marketing_user.
prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#>
prefix xsd: <http://www.w3.org/2001/XMLSchema#>
prefix : <tag:stardog:api:ecomm:>

MAPPING <urn:c360:aurora:pii:ssn>
FROM SQL { SELECT cid, ssn FROM c360.customer }
TO { ?iri :ssn ?ssn . }
WHERE { BIND(TEMPLATE("urn:stardog:demos:c360:customer:{cid}") AS ?iri) }
;
MAPPING <urn:c360:aurora:pii:card>
FROM SQL { SELECT id, card_num FROM c360.credit_card }
TO { ?cc :cardNumber ?card_num . }
WHERE { BIND(TEMPLATE("urn:stardog:demos:c360:cards:{id}") AS ?cc) }

The PII graph declares no types, names or addresses; it only adds facts to IRIs the safe graph describes, using the same templates. Read both graphs and you see one customer with an SSN; read only the safe graph and the SSN doesn't exist.

The payoff comes in Chapter 12: named-graph security grants read access per graph, so marketing_user gets virtual://aurora_c360_safe only and hr_user gets both. (Chapter 12 also compares a property-level alternative, sensitive properties with masking.)

8.8Try it: create the two Aurora virtual graphs (real build step 3)

The graphs you create here stay. Aurora is probably paused, so wake it with any DBeaver query first (or expect the first Stardog query to take 15 to 20 seconds).

Optional, for automation. The same step can be done from the command line, without Studio: one POST /admin/virtual_graphs request per graph. This is what you would put in a setup script. You don't need it for the book; skip it.

# Reference only. Run from the folder holding the .sms files; needs jq.
jq -n --rawfile m aurora_c360_safe.sms \
  '{name:"aurora_c360_safe", data_source:"aurora_c360", db:"kit-c360",
    mappings:$m, options:{"mappings.syntax":"SMS2"}}' \
| curl -s -u "stardog_admin:$STARDOG_PASSWORD" -H 'Content-Type: application/json' \
       -d @- https://<your-endpoint>.stardog.cloud:5820/admin/virtual_graphs

8.9How it works: small mappings in, one SQL query out

You now have separate mapping blocks for customers, addresses and rewards accounts. None of them knows about the others, and none contains a JOIN. So what happens when one query needs all three? Take this question: which Wisconsin customers have a rewards account, with their city and account ID?

PREFIX : <tag:stardog:api:ecomm:>
SELECT ?fullname ?city ?accountId
WHERE {
  GRAPH <virtual://aurora_c360_safe> {
    ?c :name ?fullname ; :address ?a ; :hasRewards ?r .
    ?a :state "Wisconsin" ; :city ?city .
    ?r :accountId ?accountId .
  }
}

It returns 8 rows (Christine Ricardon has 3 accounts, Renaud Brosio and Sephira McKleod 2 each, Corbin Mandel 1). Carolyn is missing because she has no rewards account. The interesting part is what Stardog sent to Aurora. This is its real plan for that query:

SELECT rewards_account.account_id, customer.full_name, address.city
FROM c360.rewards_account
INNER JOIN c360.customer ON rewards_account.cid = customer.cid
INNER JOIN c360.address  ON customer.location   = address.id
                         AND address.state = 'Wisconsin'

One SQL query, with two JOINs Stardog wrote itself. Step by step:

1. Find the mappings. For each line of the query, Stardog finds the mapping block whose TO produces that property: :name and :address from the customer block, :state and :city from the address block, :accountId from the rewards block.
2. Notice they share a source. All three blocks read from the same data source, aurora_c360, so one SQL statement can cover them all.
3. Turn shared variables into JOINs. ?c appears in the customer and rewards lines, and both mappings build it from cid, so Stardog writes rewards_account.cid = customer.cid. ?a links customer and address, both built from the location ID, so it writes customer.location = address.id. It joins on the real key columns, never on the long IRI strings.
4. Push the filter down. "Wisconsin" becomes address.state = 'Wisconsin' inside the SQL, so Aurora uses its own indexes and returns only 8 rows.
5. Turn rows back into answers. Each result row fills ?fullname, ?city and ?accountId.