Uploading CSV Data to Virtuoso with ISQL - DDMAL/linkedmusic-datalake GitHub Wiki

We have switched to using the bulk loader, so the information on this page is for legacy purposes only. Refer to Importing and Updating Data on Virtuoso for our current process.

This section was written by ChatGPT o4-mini.

1. Environment Overview

  • Host directory: /srv/webapps/virtuoso/my_virtdb
  • Container name: my_virtdb
  • Container import mount: Host /srv/webapps/virtuoso/my_virtdb → Container /database
  • Target CSV: sessions-csv-smalltest.csv
  • Final SQL table: my_session_data

2. Ensure Docker Permissions

By default, only root or members of the docker group can talk to the Docker daemon.

  1. Use sudo for Docker commands:

    sudo docker ps

  2. (Optional) Add your user to the docker group:

    sudo usermod -aG docker $USER
newgrp docker

3. Create an Import Directory

  1. SSH into your server:

The nickname you set for the virtuoso server may vary. If this doesn't work, check your ~/.ssh/config. Some people use ddmal.stage.virtuoso.

ssh ddmal.staging_virtuoso

  1. Set ownership of the host‐side import folder:

    sudo chown -R $USER:$USER /srv/webapps/virtuoso/my_virtdb/data

  2. Verify:

    ls -ld /srv/webapps/virtuoso/my_virtdb/data

4. Transfer CSV from Local Machine

On your local machine (Mac/Linux), run:

scp \
  "/path/to/sessions-csv-smalltest.csv" \
  ddmal.staging_virtuoso:/srv/webapps/virtuoso/my_virtdb/data/
  • Uses SSH alias ddmal.staging_virtuoso (configured in ~/.ssh/config).
  • Replace the source path with your actual CSV location.

Verify on server:

ssh ddmal.staging_virtuoso
ls /srv/webapps/virtuoso/my_virtdb/data/

Should list sessions-csv-smalltest.csv.

5. Restart Virtuoso Container

sudo docker restart my_virtdb

6. Load CSV as a SQL Table (File Table Method)

6.1 Enter isql Shell

Log in with your Virtuoso account. dba permissions are required. Contact Liam, Dylan, Hanwen, or Sebastien to make an account or gain access.

sudo docker exec -i my_virtdb isql 1111 dba <YourDBAPassword>

6.2 Create File Table

CREATE FILE TABLE csv_input (
  col1 VARCHAR(100),
  col2 INTEGER,
  col3 DATE,
  col4 VARCHAR(200)
)
USING FILE '/database/data/sessions-csv-smalltest.csv';
  • Define each column to match your CSV structure.

6.3 Create a Persistent SQL Table

CREATE TABLE my_session_data (
  col1 VARCHAR(100),
  col2 INTEGER,
  col3 DATE,
  col4 VARCHAR(200)
);

6.4 Populate the SQL Table

INSERT INTO my_session_data
  SELECT col1, col2, col3, col4
    FROM csv_input;

6.5 Cleanup & Commit

DROP TABLE csv_input;
COMMIT WORK;

6.6 Verify

SELECT COUNT(*) AS rows_loaded FROM my_session_data;

7. (Alternative) Bulk-Load as RDF

If you want RDF instead of relational tables:

  1. Convert CSV to Turtle/JSON-LD via a script or tool.

  2. Drop the file in /srv/webapps/virtuoso/my_virtdb.

  3. In isql:

    ld_dir('/database', 'yourdata.ttl', 'http://example.org/graph');
rdf_loader_run();
COMMIT WORK;

  4. Verify with a SPARQL count:

    SPARQL
SELECT (COUNT(*) AS ?n)
  WHERE { GRAPH <http://example.org/graph> { ?s ?p ?o } };

8. Tips & Pitfalls

  • Bulk Loader vs File Table: RDF loader expects RDF; use File Table for CSV.
  • Paths: host /srv/webapps/virtuoso/my_virtdb ↔ container /database.