MonetDB Archives - Page 3 of 10

0360 Loader Functions in MonetDB

MonetDB / By Bizkapish / February 22, 2025 February 23, 2025

Monetdb-Python3 Integration Package

Previously, we have installed MonetDB with two packages. We have installed monetdb5-sql and monetdb-client.

For python, we will need one more package. Monetdb-Python3 is integration package that allows MonetDB to interact with python. sudo apt install monetdb5-sql monetdb-client

sudo apt install monetdb-python3

I have the version 11.51.7 of MonetDB server. `monetdb --version`
I can install the last version of monetdb-python3: `sudo apt install monetdb-python3`	Or, I can install specific version of monetdb-python3: `sudo apt install monetdb-python3=11.51.7`

Enabling Embedded Python

I will first start monetdb daemon:

monetdbd start /home/fffovde/DBfarm1

Now we can login to our database. I will login as an administrator, although that is not needed, any user has ability to create LOADER function. mclient -u monetdb -d voc Password: monetdb

Python LOADER Function

Instead of the python lists, we can also use NumPy arrays. Instead of [1, 2, 3, 4, 5], we can use np.array( [1, 2, 3, 4, 5] ). NumPy arrays are faster.

Using LOADER Function

It is also possible to add data to an existing table. I will first truncate myLoaderTable and then I will append new data to an existing table.

TRUNCATE myLoaderTable;
COPY LOADER INTO myLoaderTable FROM myloader();

Using a Parameter in a LOADER function

With python we can pull data from anywhere, from any database or file. Here is an example where we will read data from a JSON file.

CREATE LOADER json_loader(filename STRING) LANGUAGE PYTHON {
import json
f = open(filename)
_emit.emit(json.load(f))
f.close()
}; This is how we can create LOADER function, that will read from our JSON file. This time we are using an argument for our function. This argument is of the STRING data type. STRING is an alias for the CLOB data type in MonetDB.

json module is builtin Python3 module.

Missing Columns

TRUNCATE myLoaderTable;

Delete LOADER function

DROP LOADER FUNCTION sys.myloader2; We can always delete this function with DROP LOADER FUNCTION statement.

0350 Exporting Data and Binary Files in MonetDB

MonetDB / By Bizkapish / February 15, 2025 February 22, 2025

Sample Table

Exporting Data from MonetDB to CSV

We saw how to import data from CSV file. For exporting to CSV file, we will again use COPY INTO statement.

Default string wrappers are not the same when writing and reading strings. They are double quotes when writing, and empty strings when reading.

We can not overwrite the existing file with the same name. That would return the error.

Exporting Compressed CSV File

If our file name has extensions .xz .bz2 .gz .lz4, then the result file will be compressed. Compression level is the best when using .xz ( .xz > .bz2 > .gz > .lz4 ). For better compression, the more time is needed.

Exporting CSV File on the Client

I will demonstrate exporting of a CSV file, on the client, by using python. We will use the code bellow. This is the same code used for uploading files, with two distinctions. Instead "set_uploader" we are using "set_downloader". COPY INTO statement is also different.

Binary Files

MonetDB can import/export data even faster than from/to CSV files. For that we can use binary files.

Little Endian vs Big Endian

Let's say that we have hexadecimal number 0x12345678. Every two figures represent one byte:

12 => 00001100

34 => 00100010

56 => 00111000

78 => 01001110

There is also a term Native Endian. That is the preferred byte order of the system MonetDB is running on. If your system is using AMD, ARM, or Intel CPU, then your system is using Little Endian.

We can check the Endianness of our system.
lscpu | grep "Byte Order"

Exporting Binary Files

MonetDB can export data into MonetDB custom binary files. Each table column will become a separate file. COPY SELECT * FROM tblCSV INTO LITTLE ENDIAN BINARY '/home/fffovde/Desktop/Letter', '/home/fffovde/Desktop/Number', '/home/fffovde/Desktop/Calendar' ON SERVER;

Instead of "Little Endian" we can use "Big Endian" or "Native Endian". Instead of "ON SERVER", we can use "ON CLIENT". For exporting data on the client, we can reuse the same python script shown above.

Loading Binary Files to MonetDB

Exported binary files can be imported in any MonetDB database. Before import, we can empty database table "TRUNCATE tblCSV;".

TRUNCATE tblCSV;

TRUNCATE tblCSV;

COPY LITTLE ENDIAN BINARY INTO tblCSV FROM
'/home/fffovde/Desktop/Letter',
'/home/fffovde/Desktop/Number',
'/home/fffovde/Desktop/Calendar' ON SERVER; We don't have to declare columns of the database table. In that case,
we just have to make sure that order and number of files is the same
as the order and number of the columns in the table.

Loading Binary Files to MonetDB on Client

TRUNCATE tblCSV;

import pymonetdb
connection = pymonetdb.connect(username="monetdb", password="monetdb", hostname="localhost", database="voc")

handler = pymonetdb.SafeDirectoryHandler("/home/fffovde/Desktop") connection.set_uploader(handler)

cursor = connection.cursor()
cursor.execute("COPY LITTLE ENDIAN BINARY INTO tblCSV FROM '/home/fffovde/Desktop/Letter', '/home/fffovde/Desktop/Number', '/home/fffovde/Desktop/Calendar' ON CLIENT;")
connection.commit()

cursor.close()
connection.close() For uploading binary files to MonetDB, from the client, we are using the similar python script that we have used for CSV files.

0340 Loading from CSV files into MonetDB

MonetDB / By Bizkapish / February 9, 2025 February 11, 2025

Importing data from CSV files into MonetDB is much faster than using "INSERT INTO". The reason is, CSV can be loaded as is, no need for parsing like for "INSERT INTO". The other reason is because CSV file can be imported by using several CPU cores at once, while "INSERT INTO" can only use one thread.

Sample table and CSV file

To use COPY INTO statement, we have to log in as an administrator (password is monetdb):
mclient -u monetdb -d voc;

Reading from CSV File to MonetDB table

We will read our CSV file into tblCSV table by this statement bellow. User executing this statement must have privilege to read the file.

• OFFSET 2 will skip the first row. In the first row we have the header. Header already exists in our MonetDB table, so we do not import it.
• tblCSV( Letter, Number, Calendar ) – this part is the table and the columns in which we are importing data.
• '/home/fffovde/Desktop/tblCSV.csv'( Letter, Number, Calendar ) ON SERVER – location of our file.
• ',' – this is separator between columns.
• E'\n' – this is delimiter between rows. '\n' is a symbol for LF (line feed).
• '"' – strings are wrapped with double quotes.

Number of Records and Default Options

• 2 RECORDS – we can limit our import to only two rows (after the header). If we know in advance how many rows we will import, that will allow MonetDB to reserve space on disk in advance, so it will increase performance.

There is also no need to always define columns separator and string wrapper. MonetDB will assume that columns separator is either \n or \r\n. It will also assume that nothing is used as a string wrapper. On the image above, double quotes are now considered as a part of a string.

Column Names

TRUNCATE tblCSV;

If we are providing column names of the CSV file, we always have to provide all of the column names, but we can give them fake names. In the example bellow we are importing only the column "Number". Other two columns will not be imported. Because "Letter" column has a default value "Z", all the values in that column will be "Z". "Calendar" column will be filled with nulls.

TRUNCATE tblCSV;

TRUNCATE tblCSV;

If we don't provide column names for the CSV file, MonetDB will assume that CSV file has the same number of columns as the target table. In the example bellow, MonetDB assumes that our CSV file has three columns. MonetDB will raise an error that one column is leftover.

Column Separators and NULL-s

TRUNCATE tblCSV;

TRUNCATE tblCSV;

TRUNCATE tblCSV;

TRUNCATE tblCSV;

Fixed Width Columns

TRUNCATE tblCSV;

Error Handling

TRUNCATE tblCSV;

Loading Several CSV Files at Once

TRUNCATE tblCSV;

Loading Data on the Client Side

TRUNCATE tblCSV;

It is also possible to upload CSV file from the client. We will see how to do this by using python.

import pymonetdb
connection = pymonetdb.connect(username="monetdb", password="monetdb", hostname="localhost", database="voc")

handler = pymonetdb.SafeDirectoryHandler("/home/fffovde/Desktop") –this will designate this directory as safe connection.set_uploader(handler) –we can place our files into this folder to upload them



cursor = connection.cursor()   
cursor.execute("COPY INTO tblCSV FROM '/home/fffovde/Desktop/tblCSV.csv' ON CLIENT BEST EFFORT FWF ( 4, 7, 10 );") 
connection.commit()   

cursor.close() 
connection.close()

You can download CSV files from here:

tblCSV files Download

0330 Loading Data Using SQL in MonetDB and Timing

MonetDB / By Bizkapish / February 2, 2025 February 8, 2025

Sample Table

We will start mclient with the timer turned on. The timer will measure the time to execute the query.

mclient --timer="clock" -u voc -d voc

Then we will create the sample table:

Inserting Data with INSERT INTO

We can insert data through some application to MonetDB, by sending INSERT INTO statements. This will work great if we don' t load a lot of rows. If we are USING a lot of consecutive INSERT statements then we can have performance issues.

INSERT INTO tblSample ( Letter, Number, Calendar ) VALUES ( 'D', 4, '2024-02-04' );
··· 1.000.000 X ···
INSERT INTO tblSample ( Letter, Number, Calendar ) VALUES ( 'ZZZZZZZ', 1000004, '2240-02-04' );

We know that in MonetDB, we can insert several rows with the one INSERT statement. This will not save us, because we shouldn't use more than 20 rows per one INSERT STATEMENT. If we use more than that, we will decrease performance.

INSERT INTO tblSample ( Letter, Number, Calendar ) VALUES ( 'D', 4, '2024-02-05' )
                                                        , ( 'E', 5, '2024-02-06' )
                                                        ··· 17 X ··· 
                                                        , ( 'F', 6, '2024-02-07' );    --20 rows at most

We can improve performance by following these 5 advice:

Disable autocommit. Autocommit will commit the data after each INSERT statement. If we can avoid that, we can speed up things.
We should prepare our statement. That means that our statement will be parsed and cached once. After that, each consecutive INSERT query will use the same statement, just with another parameters.
Use batch processing. Instead of sending a million INSERT statements, we can sent 100 batches of 10.000 INSERT statements. This will reduce communication latency between application and MonetDB, it will reduce memory usage and will minimize locking of a table.
We should disable optimizer. Optimizer can speed up more complex statements, but there is nothing that can be improved for the simple INSERT statement.
We can temporarily disable table constraints like primary key, foreign key or unique. We can restore those constraints after the import.

SQL benchmark

We'll insert one row, with one INSERT INTO statement. Then we'll see if we can noticeably increase the speed by following the tips above.

INSERT INTO tblSample ( Letter, Number, Calendar ) VALUES ( 'D', 4, '2024-02-05' );

First, we will disable our optimizer.
SET sys.optimizer = 'minimal_pipe';

In order to delete constraints, we have to found out their names. We can do that from the system tables.

We will not remove constraint "NOT NULL", because that constraint will not restrain performance.

START TRANSACTION; Now, we will start a transaction to disable autocommit.

After all this, we will again check the timing of our INSERT statement. We are now faster.
EXECUTE 0( 'E', 5, '2024-02-06');

The last thing is that we have to change
everything the way it was. COMMIT; -- finish transaction
DEALLOCATE PREPARE ALL; -- delete prepared statement
SET sys.optimizer = 'default_pipe'; -- turn on optimizer
ALTER TABLE tblSample ADD UNIQUE ( Letter ); -- bring back unique constraint
ALTER TABLE tblSample ADD PRIMARY KEY ( Number );-- bring back primary key constraint

Python Benchmark

We will now try INSERT with python script. In the blog post "Connect to MonetDB from Python" we have already saw how to use python with MonetDB. Bellow is the script we will use now. This time we will insert 10 rows of data.

import pymonetdb
import time

connection = pymonetdb.connect(username="voc", password="voc", hostname="localhost", database="voc")
cursor = connection.cursor()
insert_statements = [
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('F', 6, '2024-02-06');",
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('G', 7, '2024-02-07');",
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('H', 8, '2024-02-08');",
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('I', 9, '2024-02-09');",
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('J', 10, '2024-02-10');",
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('K', 11, '2024-02-11');",
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('L', 12, '2024-02-12');",
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('M', 13, '2024-02-13');",
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('N', 14, '2024-02-14');",
        "INSERT INTO tblSample (Letter, Number, Calendar) VALUES ('O', 15, '2024-02-15');",
    ]
overall_start_time = time.time()
for i, sql in enumerate(insert_statements, start=1):
       cursor.execute(sql)
connection.commit()
overall_end_time = time.time()
total_time = overall_end_time - overall_start_time
print(f"\n⏳ Total execution time for all inserts: {total_time:.6f} seconds")
cursor.close()
connection.close()

Total execution time is 0.008114 seconds.

Python Benchmark With Accelerations

We can speed up our Python script by using all of the advice mentioned in the text above. This is how our python procedure now looks like:

import pymonetdb
import time

connection = pymonetdb.connect(username="voc", password="voc", hostname="localhost", database="voc")
cursor = connection.cursor()
overall_start_time = time.time()
cursor.execute("SET sys.optimizer = 'default_pipe';")
cursor.execute("ALTER TABLE tblSample DROP CONSTRAINT tblsample_number_pkey;")
cursor.execute("ALTER TABLE tblSample DROP CONSTRAINT tblsample_letter_unique;")
sql = "INSERT INTO tblSample (Letter, Number, Calendar) VALUES (%s, %s, %s);"
data = [    ('P', 16, '2024-02-16'),
            ('Q', 17, '2024-02-17'),
            ('R', 18, '2024-02-18'),
            ('S', 19, '2024-02-19'),
            ('T', 20, '2024-02-20'),
            ('U', 21, '2024-02-21'),
            ('V', 22, '2024-02-22'),
            ('W', 23, '2024-02-23'),
            ('X', 24, '2024-02-24'),
            ('Y', 25, '2024-02-25')
        ]
overall_start_time = time.time()
cursor.executemany(sql, data)
connection.commit()
overall_end_time = time.time()
cursor.execute("DEALLOCATE PREPARE ALL;")
cursor.execute("SET sys.optimizer = 'default_pipe';")
cursor.execute("ALTER TABLE tblSample ADD PRIMARY KEY ( Number )")
cursor.execute("ALTER TABLE tblSample ADD UNIQUE ( Letter );")
connection.commit()
total_time = overall_end_time - overall_start_time
print(f"\n⏳ Total execution time for all inserts: {total_time:.6f} seconds")
cursor.close()
connection.close()

We don't have to explicitly start a new transaction, pymonetdb will do that automatically.

Now our timing is 0.004584 seconds.

Timing

How to Measure Time of the Query Execution

mclient --timer="clock" -u voc -d voc Timer can be "none" (default), "clock" or "performance".

Bellow we can results for these three modes of the timer switch.

When we use "performance", we get 4 results. "SQL" is time used for parsing. "Opt" is time used for optimizing statement. "Run" is time used for running the statement. "Clk" is total time used.

Query History

Data about executed statements is kept in the two tables. Those two tables can be returned with functions " sys.querylog_catalog()" and "sys.querylog_calls()". In order to work with those tables we have to login as administrator.

mclient -u monetdb -d voc Password is "monetdb".

Data saved in these two tables is persistent between sessions. We can use procedure "sys.querylog_empty()" to clear content from those two tables.

CALL sys.querylog_empty(); –procedures are started with the "call" keyword

In the current session we can start logging with the procedure "querylog_enable()".

CALL querylog_enable();

After that, I will run statement "SELECT * FROM voc.tblSample;" three times.

We can also read from the "querylog_calls()" table.

We can stop logging or queries before the end of the session with:

CALL querylog_disable();

Threshold

Each time we enable logging, our logging tables will become bigger and bigger. This can make the search for a query troublesome. In order to control amount od statements that will be logged, we can use "threshold" argument.

CALL querylog_enable(5000);

The threshold will limit the logged statements to only those whose execution time is longer than 5000 milliseconds. This allows us to perform profiling, to find the queries that are sucking up our resources the most.

0025 SystemD, Monetdbd, Mserver5: Clarification

MonetDB / By Bizkapish / January 21, 2025 January 25, 2025

Computer Process

When we start one program (e.g., Excel), we'll start one process.

Process occupies part of a CPU, part of memory and can only see files that are opened by that process. In that way, process is like a small virtual machine. Excel process is working inside of such small virtual machine and can only access workbooks that we have opened inside of that Excel (e.g., Book1.xlsx and Book2.xlsx). The purpose of a process is to isolate one program from everything else.

Processes, Daemons and Services

We can divide processes into 4 groups:

User process is a process with a friendly interface toward human users ( like Notepad.exe ).
Daemon process is a process with a friendly interface toward other programs ( "Windows Time" (w32time) service ).
Service is a daemon which provides interface to some essential functionality. For example, Apache server is a service because through it we get our web pages. In the background, Apache server will pull needed data from the MySQL database which is another daemon. In this setup, Apache server is a service daemon, and MySQL database is a normal daemon.
Some processes are in between. Microsoft Outlook is a user process dominantly, but it also has an VBA api, so it is partially a daemon.

We can see that the major difference between these processes is stemming from their indented usage. Usually, daemons will start when the OS starts and they will work in the background without users' interaction. On the other side, daemons can also be started manually and users can interact with them. We have already saw an example of such, manually started, daemon:

monetdbd start /home/fffovde/DBfarm1 monetdbd get all /home/fffovde/DBfarm1

Official Processes

In the "real world" other definitions are used. Something is a daemon or service only if it is officially registered with the operating system. Only processes registered in "Systemd" on Linux systems are called Daemons. In Windows, only processes registered in SCM (Service Control Manager) are called Services.

Systemd and SCM are operating system components used to automatically start official daemons and services in the correct order, control resource usage, access rights, log, and restart failed services. From an OS perspective, this is the only way to distinguish between ordinary and special processes. Since Windows and Linux use such definitions, most people will also use those definitions.

Two Ways to Manually Start Monetdbd Daemon

We will first check that Monetdbd daemon is not working. "pgrep" command is "process grep". We are searching the process by its name. Pgrep should return ID of a monetdbd process, but our process is not active. pgrep monetdbd

We can also start monetdbd daemon with the "start" command. This will fail because our daemon is already working.
monetdbd start /home/fffovde/DBfarm1

The explanation is that when we open monetdbd with systemctl, two things will happen:

Systemctl will start the daemon, but not "/home/fffovde/Dbfarm1", this server will not start. This is because systemctl will start the default server. The default server is on the location "/var/monetdb5/dbfarm".
The server "/etc/monetdb5/dbfarm" will occupy the port 50.000. That is why we are getting an error "cannot remove socket files".

Mserver5

When our database is opened, then the process Mserver5 is working. Currently our database is not opened. We can check that with: pgrep mserver5.

When we login to the voc database with mclient, at that moment the database will be opened if it is not already open. When our database is opened, process Mserver5 is also working. This is because Mserver5 process is OUR DATABASE. This process will perform all processing on request of clients for a database voc. Mclient console application is used to send our queries to this Mserver5 process.

Starting the Monetdbd Daemon When the Computer Starts up

We can not start "/home/fffovde/DBfarm1" server automatically when the computer boot up. We can only do that with the default server "/var/monetdb5/dbfarm". First we will stop "/home/fffovde/DBfarm1" server to release the port 50.000:

monetdbd stop /home/fffovde/DBfarm1

Then we will start "/var/monetdb5/dbfarm" server.
systemctl start monetdbd

Now that we have confirmed that our database is working, we will make our "/var/monetdb5/dbfarm" to a full fledged systemctl controled daemon. This will make our server to open automatically after each system boot.

systemctl enable monetdbd

Now we can exit everything and we can reboot our computer.

Avoid Entering Credentials Every Time

Running Two Servers at the Same Time

First, we will change the port of the "DBfarm1" server:
monetdbd set port=50001 /home/fffovde/DBfarm1

Cleaning up

Summary

We can open the server manually or automatically. To open it manually, we use "monetdbd start". For automatic startup we use "systemctl". We can only automatically open the server "/var/monetdb5/dbfarm". If we use two servers at the same time, they must have different port numbers.

In MonetDB, the databases are quite independent. In Microsoft SQL server things are different. SQL server can have many databases that are sharing many resources. They are sharing logins, tempdb, resource pools, memory settings, collation. Backups, replication, and monitoring tools (like SQL Agent jobs) are often configured at the server level.

Because of that it is correct to say that Mserver5 process is not just a database, it is a whole server. Monetdbd is not really a server. That daemon is only a managing tool for Mserver5 processes. Almost the only thing databases within the same dbfarm folder share is their port number. So, "voc" and "newDB" are monetdb servers. "/home/fffovde/DBfarm1" and "/var/monetdb5/dbfarm" are just folders for those servers.

Monetdb is console application used by a user to interact with montdbd daemon.

`CREATE LOADER myloader() LANGUAGE PYTHON { _emit.emit( { 'Col1': [ "A", "B" ], 'Col2': [ 1, 2 ] } ) _emit.emit( { 'Col1': [ "C", "D" ], 'Col2': [ 3, 4 ] } ) };`	This statement will create LOADER function. Columns are defined as python lists. Each list, together with the name of a column, is placed inside of the python dictionary.
	We are using function "_emit.emit" do divide our inserts into chunks. In this way we can preserve memory. After inserting the first chunk, (A1,B2), we can delete it from the memory, and we can continue inserting the second chunk (C3,D4).