Create Users and Schemas

Create Users and Schemas mk Tue, 02/25/2020 - 10:47

In order to explore the wealth of functionality offered by MonetDB/SQL, the best strategy is to get started using a toy database. For this we use the VOC database which provides a peephole view into the administrative system of an early multi-national company, the Vereenigde geoctrooieerde Oostindische Compagnie (VOC for short - The (Dutch) East Indian Company) established on March 20, 1602.

First thing we need to do is download the dataset. The VOC dataset can be downloaded here (542K) gz (519K) bz2 (371K) and it is a compressed file with SQL statements.

After the file has been extracted, load its contents into MonetDB using mclient. Before you load the VOC data set, it is advised to introduce a user different from the omnipresent default monetdb.

The new user is given his own schema for the MonetDB database. Assuming you have started MonetDB with SQL module, proceed as follows:


shell> mclient -u monetdb -d voc
sql>CREATE USER "voc" WITH PASSWORD 'voc' NAME 'VOC Explorer' SCHEMA "sys";
sql>ALTER USER "voc" SET SCHEMA "voc";


To illustrate the use of the newly created account and schema, the following example connects and creates a table, which is dropped afterwards by aborting the transaction.


shell> mclient -u voc -d voc
auto commit mode: off

sql>CREATE TABLE test (id int, data varchar(30));
operation successful (0.722ms)

TABLE  voc.test

sql>\d test
CREATE TABLE "voc"."test" (
    "id"   INTEGER,
    "data" VARCHAR(30)
sql>CREATE TABLE "voc"."test" ("id" int, "data" varchar(30));
auto commit mode: on