Importing JSON and CSV Files

This page discusses how to import JSON and CSV files into Stardog.

The same Virtual Graph commands and mappings that are used for creating virtual graphs can be used to import data from delimited (CSV or TSV) and JSON files. Imported data from delimited and JSON files is not truly virtual, but is part of our Virtual Graph APIs and docs because it shares the same mappings syntax.

• The mappings files for importing text files must be expressed in SMS2.

• Unlike all other Virtual Graph data sources, the WHERE clause in SMS2 mappings for text files supports any SPARQL query function when BIND-ing transformed values to new variables.

• A user requires WRITE permission on a database in order to import JSON or CSV files. If Named Graph Security is enabled, they will also require WRITE permission on the named graph into which they want to import data.

Importing CSV Files

From the CLI:

To import a CSV file from the command line, provide the CSV file as the last argument to the virtual import command:

$ stardog-admin virtual import myDB cars.sms cars.csv

In the example above:

• myDB is the database we are going to import the mapped CSV into
• cars.sms is the SMS2 mapping defining how the CSV will be mapped to RDF
• cars.csv is the CSV file to be imported

Using Designer:

It’s also possible to import CSV files using Stardog Designer. See Designer’s CSV Upload for more information.

CSV Properties File

If the input file is using a different kind of separator, e.g. tab character, a properties file can be provided in the virtual import CLI command:

$ stardog-admin virtual import myDB cars.properties cars.sms cars.tsv

• The properties file is a Java properties file.

The properties file for CSVs can specify values for the following virtual graph properties:

• The csv.escape character is used as an alternative to the csv.quote character. To escape a csv.quote character within a string that is enclosed in csv.quote characters, use two consecutive csv.quote characters. Do not set csv.escape to the csv.quote character.

• Note that whitespace characters in the Java properties file format need to be escaped, so if you want to import tab-separated value files, set csv.separator=\t in the properties file.

Example CSV Properties File

Suppose we wanted to import a TSV file without a defined header - we could include the following properties in our properties file:

csv.header=false
csv.separator=\t

CSV Mapping File

The mapping file for importing CSV files must be expressed in SMS2.

Unlike all other Virtual Graph data sources, the WHERE clause in SMS2 mappings for text files supports any SPARQL query function when BIND-ing transformed values to new variables.

See the Example CSV Import for more details on creating a mapping.

• A mapping file can be omitted if a properties file is provided. See Automatically Generated Mappings for more information.
• Stardog Designer’s CSV Upload can also be used to quickly import CSV files without having to create an SMS2 mapping. Note, if more complex transformations are needed, you’ll need to create your own SMS2 mapping and import the CSV from the command line.

CSV Header with Dots and Spaces in Column Name

Stardog will allow you to import a CSV file that has column names containing .’s and spaces. If you are creating a mapping, you’ll need to be able to refer to these column names in the TO and WHERE clauses. Stardog’s mapping parser will fail to parse a variable name like ?first.name to reference a CSV column name first.name.

You have 2 options to refer to these column names containing .’s and spaces as variables in the mapping.

1. Replace all occurrences of . and spaces in variable names with an underscore (_) in mappings.
2. Make use of the sourceField function to return the name of the source field referred to by its string argument in the WHERE clause and BIND it to a new variable.

...
WHERE {
    bind(sourceField("first.name") as ?firstName)
    bind(sourceField("last name") as ?lastName)
}

Automatically Generated CSV Mappings

The mappings for delimited files can be automatically generated given a couple additional properties in the properties file:

To import with automatically generated mappings, omit the command line argument for the mappings file:

$ stardog-admin virtual import myDB cars.properties cars.csv

See the stardog-examples Github repository for an example virtual import that uses autogenerated mappings.

Example CSV Import

In this example, we’ll show how to put together the required elements to import a CSV file into Stardog.

The files used in this example can be found in the stardog-examples Github repository.

CSV file

We will use the following CSV file in the example.

Year,Make,Model,Description,Price
1997,Ford,E350,"ac, abs, moon",3000.0
1999,Chevy,"Venture ""Extended Edition""","",4900.0
1999,Chevy,"Venture ""Extended Edition, Very Large""",,5000.0
1996,Jeep,Grand Cherokee,"MUST SELL! air, moon roof, loaded",4799.0

• Note that lines 3 and 4 are missing Descriptions.

Omitting the CSV Properties File

We can omit a properties file in this example because the default CSV properties work just fine here. If we wanted to import a TSV, we’d need to specify this in the properties file.

Creating the CSV Mapping File

Recall the mapping files for importing CSV files must be expressed in SMS2. If you are not familiar with SMS2, please see the overview provided in Mapping Data Sources.

We could use the following mapping to map the CSV to RDF.

prefix :    <http://example.org/cars#>
prefix gr:  <http://purl.org/goodrelations/v1#>
prefix vso: <http://purl.org/vso/ns#>

MAPPING
FROM CSV {
}
TO {
    ?car_iri a vso:Automobile, gr:ActualProductOrServiceInstance ;
        rdfs:label ?car_label ;
        gr:hasManufacturer ?make_iri ;
        gr:hasMakeAndModel ?model_iri ;
        vso:modelDate ?model_date .

    ?make_iri a gr:BusinessEntity ;
        rdfs:label ?Make .

    ?model_iri a gr:ProductOrServiceModel ;
        rdfs:label ?model_string ;
        gr:hasManufacturer ?make_iri .

    ?offer_iri a gr:Offering ;
        rdfs:comment ?Description ;
        gr:includes ?car_iri ;
        gr:hasBusinessFunction gr:Sell ;
        gr:hasPriceSpecification ?price_bnode .

    ?price_bnode a gr:UnitPriceSpecification ;
        gr:hasCurrency "USD"^^xsd:string ;
        :uuid ?uuidstr ;
        gr:hasCurrencyValue ?price_float .
}
WHERE {

    BIND(TEMPLATE("http://example.org/cars#Manufacturer-{Make}") AS ?make_iri)
    BIND(TEMPLATE("http://example.org/cars#Model-{Model}") AS ?model_iri)

    BIND(CONCAT(?Make, " ", ?Model) AS ?model_string)
    BIND(CONCAT(?Make, " ", ?Model, " (", ?Year, ")") AS ?car_label)

    BIND(TEMPLATE("http://example.org/cars#Car-{_ROW_NUMBER_}") AS ?car_iri)
    BIND(TEMPLATE("http://example.org/cars#Offer-{_ROW_NUMBER_}") AS ?offer_iri)

    BIND(xsd:float(?Price) AS ?price_float)

    BIND(xsd:date(CONCAT(?Year, "-01-01")) AS ?model_date)

    BIND(BNODE(CONCAT("price-", STRUUID())) AS ?price_bnode)
}

Let’s break this mapping down in further detail:

Executing the virtual import CLI command

Finally, we can import our CSV file (cars.csv) into our database (carsDb) using the virtual import Stardog Admin CLI command like so:

$ stardog-admin virtual import carsDb cars_mappings.sms cars.csv

• Our mapping file (cars_mappings.sms) is provided to map the CSV to the desired RDF.

Query the Imported CSV Data

Let’s verify our data was imported correctly by quickly executing the following query to find all instances of gr:BusinessEntity in the database. We expect there to be three - Ford, Chevy, and Jeep.

PREFIX gr: <http://purl.org/goodrelations/v1#>

SELECT *
{
    ?businessEntity a gr:BusinessEntity
}

Results:

You can see a Turtle export of all data imported into the database in our stardog-examples.

Importing JSON Files

To import a JSON file into Stardog from the CLI, provide the JSON file as the final argument to the virtual import command like so:

$ stardog-admin virtual import myDB bitcoin.sms bitcoin.json

We’ll look at the JSON file (bitcoin.json) and mapping (bitcoin.sms) in more detail below.

JSON File

{
  "hash": "00000000000000000028484e3ba77273ebd245f944e574e1d4038d9247a7ff8e",
  "time": 1569266867591,
  "block_index": 1762564,
  "height": 575144,
  "txIndexes": [
    445123952,
    445058113,
    445054577,
    445061250
  ]
}

JSON Mapping File

PREFIX : <http://example.com/>

MAPPING
FROM JSON {
  {
    "hash" : "?hash",
    "time" : "?time",
    "block_index" : "?block_index",
    "height" : "?height",
    "txIndexes" : [ "?txIndex" ]
  }
}
TO {
  ?block a :Block ;
    :hash ?hash ;
    :time ?dateTime ;
    :height ?height ;
    :includesTx ?tx .
  ?tx a :Tx ;
    :index ?txIndex .
}
WHERE {
  bind(xsd:dateTime(?time) as ?dateTime)
  bind(template("http://example.com/tx/{txIndex}") as ?tx)
  bind(template("http://example.com/block/{hash}") as ?block)
}

Let’s break this mapping down in further detail:

Verify the JSON Import

We can export the data in Turtle to verify the structure of the data we just imported:

$ stardog data export myDB

@prefix : <http://api.stardog.com/> .
@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#> .

<http://example.com/block/00000000000000000028484e3ba77273ebd245f944e574e1d4038d9247a7ff8e> a <http://example.com/Block> ;
   <http://example.com/hash> "00000000000000000028484e3ba77273ebd245f944e574e1d4038d9247a7ff8e" ;
   <http://example.com/time> "2019-09-23T19:27:47.591Z"^^xsd:dateTime ;
   <http://example.com/height> 575144 ;
   <http://example.com/includesTx> <http://example.com/tx/445123952> , <http://example.com/tx/445058113> , <http://example.com/tx/445054577> , <http://example.com/tx/445061250> .
<http://example.com/tx/445123952> a <http://example.com/Tx> ;
   <http://example.com/index> 445123952 .
<http://example.com/tx/445058113> a <http://example.com/Tx> ;
   <http://example.com/index> 445058113 .
<http://example.com/tx/445054577> a <http://example.com/Tx> ;
   <http://example.com/index> 445054577 .
<http://example.com/tx/445061250> a <http://example.com/Tx> ;
   <http://example.com/index> 445061250 .