Stardog BI Server with Connected Data

nathanielvala · August 4, 2021, 4:15am

Hi all,

I have set up stardog on my machine and followed the music vkg tutorial and wanted to see how I could query this VKG using the BI Server function. As you can see, I am able to query the data that is in a MySQL server in Azure:

When I get into the BI Server, I can see that it created the database (Screenshot below) but there is no tables/data.

Not sure if I am doing something wrong or need to provide additional configuration and would appreciate any pointers in the right direction

jess · August 4, 2021, 4:21am

You will need to create some mappings for tables to be available for querying. Let me first refer to some documentation:

Let me know if this helps or if you have further questions.

Best,
Jess

nathanielvala · August 5, 2021, 7:08am

Hi Jess, I have gone through these links and made a little progress but still don't seem to be able to access the data.

If I run this query in stardog, I get data:

SELECT ?song ?name {
   GRAPH <virtual://music> {
      ?song a :Song ;
            :name ?name .

   }
}

I then used this table mapping for Song:

:SongTableMapping a sql:TableMapping ;
    sql:tableName "Song" ;
    sql:class :Song ;
    sql:hasField [
        sql:property :length ;
        sql:fieldName "length" ;
        sql:type xsd:integer
    ],
    [
        sql:property :writer ;
        sql:fieldName "writer" ;
        sql:refersTo :SongwriterTableMapping
    ] .

But when I access the BI Server I am not getting any songs. Please see the below (query the SPARQL from above to show data, switch to the biserver and table is empty)

bash-4.2$ /opt/stardog/bin/stardog query execute music /var/opt/stardog/tutorial-content/demo-vkg/music_query_2.sparql 
+--------+--------------+
|  song  |     name     |
+--------+--------------+
| :Song1 | "Love Me Do" |
+--------+--------------+

Query returned 1 results in 00:00:01.656
bash-4.2$ exit
*[master][~/Projects/stardog]$                                            
*[master][~/Projects/stardog]$ mysql -h 127.0.0.1 -P5806 -u admin -p      
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 80
Server version: 5.7.28 MySQL Community Server (GPL)

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use music;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_music |
+-----------------+
| Artist          |
| Song            |
| Songwriter      |
+-----------------+
3 rows in set (0.06 sec)

mysql> select * from Song;
Empty set (0.08 sec)

mysql>

nathanielvala · August 10, 2021, 12:54am

Hey @jess ,

Any thoughts on the above? I think I have done everything correctly

jess · August 10, 2021, 11:00pm

Thanks for following up. I was on vacation for a few days.

A few things to check:

Your BI mappings use the default namespace (eg. :Song). This must be consistent with that in the SPARQL query.
In your SPARQL query, you ask for the :name predicate values, but in the BI mapping, you used :length. Is :length present in your data? For the sake of ttroubleshooting, please remove/comment the "writer" mapping until you can get a minimal version working with either :name or :length.

nathanielvala · August 11, 2021, 1:55am

@jess , Hope you had a good break

I have simplified the above to remove all complexity and still isnt working for me. I have now got the following:

A SQL mapping like this:

@prefix : <http://stardog.com/tutorial/> .
@prefix stardog: <tag:stardog:api:> .
@prefix rdf: <http://www.w3.org/1999/02/22-rdf-syntax-ns#> .
@prefix owl: <http://www.w3.org/2002/07/owl#> .
@prefix xsd: <http://www.w3.org/2001/XMLSchema#> .
@prefix rdfs: <http://www.w3.org/2000/01/rdf-schema#> .
@prefix sql: <tag:stardog:api:sql:> .

:ArtistTableMapping a sql:TableMapping ;
    sql:tableName "Artist" ;
    sql:class :Artist ;
    sql:hasField [
        sql:property :name ;
        sql:fieldName "name" ;
        sql:type xsd:string
    ] .


:SongTableMapping a sql:TableMapping ;
    sql:tableName "Song" ;
    sql:class :Song ;
    sql:hasField [
        sql:property :length ;
        sql:fieldName "length" ;
        sql:type xsd:integer
    ],
    [
        sql:property :name ;
        sql:fieldName "Song Title" ;
        sql:type xsd:string
    ] .

Once data is loaded (using the Virtual Knowledge Graph demo found here) I am able to run the following SPARQL and get results:
Query:

SELECT ?song ?name ?length {
   GRAPH <virtual://music> {
      ?song a :Song ;
            :name ?name ;
            :length ?length .
   }
}

Result:

bash-4.2$ ${STARDOG_PATH}/stardog query execute music ${tutorial_content}/music_query_2.sparql
+--------+--------------+--------+
|  song  |     name     | length |
+--------+--------------+--------+
| :Song1 | "Love Me Do" | 125    |
+--------+--------------+--------+

The create statement looks fine in the BI Server:

mysql> show create table Song;
+-------+------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                       |
+-------+------------------------------------------------------------------------------------------------------------------------------------+
| Song  | CREATE TABLE `music`.`Song` (
  `id` varchar(512) NOT NULL,
  `length` bigint(21) NOT NULL,
  `Song Title` varchar(512) NOT NULL
) |
+-------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.07 sec)

Note: I'm not sure if this will make a difference, but my .ttl doesn't have an id field described (Though I assume it will take this from the SongID seemingly generated by Stardog in SPARQL (i.e. Song1 from the results above)

Hopefully this helps, let me know if there is anything more I should provide!

jess · August 11, 2021, 1:59am

I think this is the issue. The default prefix is different in your SPARQL query. Can you try removing this and then clearing+reloading the named graph containing the BI/SQL mappings?

nathanielvala · August 11, 2021, 3:00am

Hey,

I removed the prefix and recreated everything but still the same results as above

nathanielvala · August 11, 2021, 6:41am

Hi @jess ,

I made this to help out. This repo has all the configs and settings that I am using. If you:

Add a licence key location to the stardog.env as SD_LICENCE_LOCATION
Have docker-compose and mysql and can run a bash script
Run ./demostardog start

Reop:

Hope this helps out

jess · August 13, 2021, 7:45pm

I just realized you're querying a virtual graph here. For that to work with a BI query, you'll need to enable the Virtual Transparency setting on your database. Without this, the BI query will only cover local data. We'll get this added to the BI server documentation.

Jess

nathanielvala · August 16, 2021, 4:16am

Thanks heaps, where would I set this? I cant seem to get it to work

jess · August 16, 2021, 4:31am

You should see it in the list of database options.

nathanielvala · August 17, 2021, 5:37am

Hi @jess ,

Thanks heaps, this is now enabled and working. Is there a way I can enable it from the command line / during the creation of the database? I am making the database like the below:

stardog-admin db create -n music
stardog-admin data-source add music.properties music_sql-to-stardog_mappings.ttl
stardog-admin virtual add --name music --data-source music music_sql-to-stardog_mappings.ttl

Thanks a lot

jess · August 17, 2021, 2:23pm

db create accepts a list of database options on the command line and you can enable virtual.transparency this way. It should be something like stardog-admin db create -o virtual.transparency=true -- -n music.

Jess

system · August 31, 2021, 2:23pm

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

Topic		Replies	Views
Can't reproduce Tableau music db training results Support	13	651	September 7, 2020
Teaching Stardog to chase its own tail Support	4	353	November 27, 2019
BI tools and SQL Queries Support	4	560	October 25, 2019
Server stops querying downstream databases (Docker Stardog to Azure MySQL DB) Bug	3	351	August 9, 2021
Mapping succeed but ends up with an un-queriable virtual graph Stardog Studio	10	631	June 10, 2019

Stardog BI Server with Connected Data

Related topics