ISQL (Virtuoso) - DDMAL/linkedmusic-datalake GitHub Wiki

ISQL (Interactive SQL) is a command-line interface provided by OpenLink Virtuoso, a multi-model database management system. ISQL allows users to interact with the Virtuoso database using SQL commands. It is particularly useful for database administrators and developers who need to perform tasks such as querying, updating, and managing the database.

Key Features of ISQL in OpenLink Virtuoso:

1. SQL Query Execution:

You can execute standard SQL queries to retrieve, insert, update, or delete data in the Virtuoso database.

2. SPARQL Query Execution:

ISQL supports the execution of SPARQL queries, allowing you to interact with RDF data stored in the Virtuoso database.

3. Database Management:

ISQL provides commands for managing database objects such as tables, indexes, and users. You can create, alter, and drop these objects as needed.

4. Transaction Management:

You can manage database transactions, including committing and rolling back transactions to ensure data integrity.

5. Script Execution:

ISQL allows you to execute SQL scripts, which can be useful for automating repetitive tasks or running complex sequences of commands.

6. Permission Management:

You can manage user permissions and roles, controlling access to various database resources.

How to Use ISQL:

1. Accessing ISQL:

1.1 Navigation from Conductor

or Database > Interactive SQL

1.2

ISQL can be accessed from the command line. Typically, you would start ISQL by running a command like isql followed by connection parameters (e.g., hostname, port, username, password). isql 1111 dba dba In this example, 1111 is the port number, and dba is both the username and password. Refer to https://github.com/DDMAL/linkedmusic-datalake/wiki/Virtuoso-Setup-Guide.

2. Executing Commands:

Once you are in the ISQL interface, you can start executing SQL or SPARQL commands. For example, to select data from a table: SELECT * FROM my_table; Or to execute a SPARQL query:

SPARQL #Add a "SPARQL" prefix
SELECT ?s ?p ?o
WHERE {
  ?s ?p ?o .
}
LIMIT 10; #Don't forget the ";"

3. Managing Transactions:

You can manage transactions using commands like COMMIT and ROLLBACK:

BEGIN;
INSERT INTO my_table (column1, column2) VALUES ('value1', 'value2');
COMMIT;

4. Running Scripts:

To execute a script file, you can use the LOAD command: LOAD 'path/to/script.sql';

Example Use Cases:

Database Initialization:

Setting up the initial schema and loading data into the database.

Data Transformation:

Using SQL and SPARQL to transform and integrate data from various sources.

Maintenance Tasks:

Performing regular maintenance tasks such as backups, indexing, and performance tuning.

Permission Management:

Granting and revoking permissions to ensure secure access to the database.

Conclusion:

ISQL is a powerful tool for interacting with the OpenLink Virtuoso database. It provides a flexible and efficient way to manage and query both relational and RDF data, making it an essential tool for database administrators and developers working with Virtuoso.

Uploading CSV Data to Virtuoso with ISQL

This process will likely change when we switch to using the bulk RDF loader instead (#312). 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.