Getting StartedGetting Started giulia Mon, 02/24/2020 - 16:59
Now that we have a working setup for MonetDB, it's time to start using it!
In this tutorial we are going to cover the basics for data loading and operations with MonetDB
Create a DatabaseCreate a Database mk Tue, 02/25/2020 - 10:22
The MonetDB/SQL tutorial is based on the material published in the book J.R. Bruijn, F.S. Gaastra and I. Schaar, Dutch-Asiatic Shipping in the 17th and 18th Centuries, which gives an account of the trips made to the East and ships that returned safely (or wrecked on the way). A total of 8000 records are provided. They include information about ship name and type, captain, the arrival/departure of harbors along the route, personnel accounts, and anecdotal information.
The first step to use MonetDB on Linux is starting daemon monetdbd, code name meriovingian. It runs in the background and facilitates, amongst others, management of local/remote servers. The daemon is controlled by the application monetdb. See its documentation for all details. The monetdbd is not available on Windows, but for all these systems a default database called demo is available by default (you can find the Windows tutorial here).
Every newly created database contains a system user named monetdb and system schemas such as "sys", "tmp", "profiler" and some more. To connect/login to the database server the first time, you must use monetdb as user name and enter its default password: monetdb. Once connected you may create new users, create new schemas, create tables etc. It is also strongly recommended to change the default password of the monetdb system user via ALTER USER SET PASSWORD command.
shell> monetdbd create /path/to/mydbfarm shell> monetdbd start /path/to/mydbfarm shell> monetdb create voc shell> monetdb release voc shell> mclient -u monetdb -d voc password:<monetdb> Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2014-SP1) Database: MonetDB v11.19.7 (Oct2014-SP1), 'mapi:monetdb://localhost:50000/voc' Type \q to quit, \? for a list of available commands auto commit mode: on sql>SELECT 'hello world'; +---------------+ | single_value | +===============+ | hello world | +---------------+ 1 tuple (0.530ms) sql>\q shell> _________________________________________________________________________________________
The command \q or end-of-file <Ctrl-d> signal terminates the connection with the server.
Create Users and SchemasCreate 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.
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 password:<monetdb> sql>CREATE USER "voc" WITH PASSWORD 'voc' NAME 'VOC Explorer' SCHEMA "sys"; sql>CREATE SCHEMA "voc" AUTHORIZATION "voc"; sql>ALTER USER "voc" SET SCHEMA "voc"; sql>\q
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 password:<voc> sql>START TRANSACTION; auto commit mode: off sql>CREATE TABLE test (id int, data varchar(30)); operation successful (0.722ms) sql>\d 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));
sql>ROLLBACK; auto commit mode: on sql>\d
Load DataLoad Data mk Tue, 02/25/2020 - 10:40
It is possible to populate the database importing data from a file. The voc_dump.sql file can be imported into the database using the textual client interface. Some alternative ways are as follows:
1: shell> mclient -u voc -d voc voc_dump.sql password:<voc>
2:shell> mclient -u voc -d voc < voc_dump.sql password:<voc>
3:shell> mclient -u voc -d voc password:<voc> sql> \< voc_dump.sql ___________________________________________________________________________________
After the import procedure is complete, we can access it through the mclient interface. The VOC data set contains data for around 8000 voyages.
___________________________________________________________________________________ sql>\d TABLE voc.craftsmen TABLE voc.impotenten TABLE voc.invoices TABLE voc.passengers TABLE voc.seafarers TABLE voc.soldiers TABLE voc.total TABLE voc.voyages
Let's check out the data we just loaded. First, count the number of rows the voyages table.
sql>SELECT count(*) FROM voyages; +------+ | L1 | +======+ | 8131 | +------+
In the next section we are going to show examples on how to play with views and queries