0580 Mclient for MonetDB

Mclient is the MonetDB command line client that connects to the MonetDB server to execute SQL commands. We have used this client in all the previous videos, but now we will see how we can make better use of it.

General Information

With mclient we can find out the version of our server and we can get help for mclient commands.   This will show us version:   mclient -v This is how we can get help: mclient -?

Information About Our Database

In one of the previous blogposts "link", I created database farm "DBfarm". Inside of it we have a database "DBfarmDB".   I will now login to that database as an admin and I will create some database objects. The objects will be created in the "sys" schema.   monetdbd start /home/fffovde/Desktop/DBfarm monetdb set readonly=no DBfarmDB            #if it is still readonly mclient -u monetdb -d DBfarmDB           #password monetdb

CREATE TABLE tablo( Number INT ); CREATE VIEW viewo( Number ) AS SELECT Number from tablo; CREATE SEQUENCE seqo;   CREATE FUNCTION funco() RETURNS INTEGER      RETURN SELECT 2;   CREATE SCHEMA schemo;

If we just type "\D" inside of the mclient, we will get sql dump in the stdout, for the whole database.

We can type the table name after the "\D" command. This will give us SQL to create that table.   \D tablo

If we want to get a list of selected database objects, we just need to list them after the "\d" command. I will list schemas, sequences and views. \dnsv

We can get lists of these objects: n – schemas f – functions s – sequences t – tables v – views     Just "\d" will give as tables and views by default. It is the same as "\dvt".

We can combine "\d" command with the letter "S". That will add system objects to lists. \dSt

"\dS" is equal to "\dSvt".

We can get SQL dump only for object with the specific name. \dt tablo Name should be written with the lower letters, or inside of double quotes, like "Tablo".

We can use wildcards "*" (several letters) and "_" (one letter). \dst *o Wildcards can not be used inside of double quotes. This will not work: \dst "*o"

Query Execution Control

Query Formatting

We can control how the result of a query will be presented ( default is "sql" ). Let's run "SELECT * FROM sys.auths;" with different modes.

If we don't want to see the result of a query, we can use "trash" and "rowcount" modes.

\f trash --trash will return nothing

\f rowcount -- only row count will return

\f sql  --default

\f expanded  -- records are vertical

\f csv -- comma is separator

\f tab -- tab is separator

\f raw --raw database format

\f xml --like xml document

There are some special modes for the CSV.

\f csv=c --separator is letter "c", or any other sign

\f csv+c --header is included

<= Note that there are double quotes around the word "public".

For the rest =>, we will use query:   SELECT schema_path FROM sys.users;

\f csv --quotes are doubled

\f csv-noquote --no doubling

\f sql --default

The Number of Rows

When we run query that will return a lot or rows, we will not be able to see all of them. They will just whiz by before our eyes. We want to control how the rows are displayed. We want to see them page by page. For that we will use "\r" command.

For this example, I will use system table sys.function_types. It has 7 rows. SELECT * FROM sys.function_types;

I will set rows command to four: \r 4 We will get only one row for the first page. For the first page we always get N-3 rows. This is probably bug. If N is three or smaller, then the first page will be empty.   For other pages we will get the complete 4 pages. We can jump to the next page with "Enter". That is the same as "next".

If we type the letter "c" and then we press ENTER, we will jump to the end of a table.

If we type the letter "q" and then we press ENTER, we will immediately exit the execution.

For the last example I will make terminal window small.

I will set command to zero: \r 0 I will query this table. SELECT * FROM sys.keywords;

This is the best mode, because now the page is the size of the window.

I will press ENTER several times to go through a few pages. Then I will enlarge the window.

Only the first page will have 2 rows that are visible in one page. Other pages will have five rows. Obviously we have some "offset by 3" bug.

We will exit paging mode by setting the command to minus one "\r -1".

Width of a Table

If we set "\w" to minus one, MonetDB will think that we have unlimited width terminal. I've narrowed the window, so presentation will be squeezed and crumbly. It becomes better if window is wider.

Default value of the "\w" command is zero. I made my terminal window narrow, so in this mode 11/16 columns will not be shown. Only the width of the terminal is used.

If we set "\w" to some positive number, MonetDB will think that the terminal is only a certain number of characters wide. I'll set it to 50 characters, which is enough for 4 columns.

Auto Commit Mode

By default, auto commit mode is turned ON in mclient. We can turn it OFF with the command "\a". We turn it back to ON with "\A".

\a    INSERT INTO tablo( Number ) VALUES ( 1 ); ROLLBACK; SELECT * FROM tablo; --nothing was auto commited

\A INSERT INTO tablo( Number ) VALUES ( 2 ); ROLLBACK;      --not allowed in auto commit mode SELECT * FROM tablo;    --value 2 is auto commited

Logging and Monitoring

I will leave the session and then I will log in, just to clear the history. quit mclient -u monetdb -d DBfarmDB --password monetdb

I will turn on logging and tracing on mclient. This is mostly useful for debugging. \L /home/fffovde/Desktop/FileLog   --log to this file \X    --trace everything that mclient is executing

I will now execute a query: SELECT 2;

This is tracing. We'll get a lot of lines.

A new "LogFile" will appear with the similar content. This is logging.

We can disable logging, but not tracing. For some commands the only way to disable them is to create a new session. \L           --we use \L without giving a full path

I will run a few more queries: SELECT 3; SELECT 4;

Using the "\history" command, we can list all the SQL and commands we run in this session.

I will again quit the session, and log again, to disable tracing. I will also set my working directory to be the Desktop.

quit cd /home/fffovde/Desktop mclient -u monetdb -d DBfarmDB --password monetdb

SQL Input and Output

We can redirect the result of our statement to some shell command. \| grep "alpha" SELECT * FROM (VALUES('alpha'),('beta'));

I will disable this command like this. Now our result is normal. \|     --without shell command SELECT * FROM (VALUES('alpha'),('beta'));

We can redirect the result of our statement to a file. \>ResultFile SELECT 10, 20;

Every statement we run will now go to the file, and not to the terminal. We can disable the effect of  this command with: \>       --without the name of a file

On the Desktop, I will create one textual file with one query inside of it.

I can call this query from the file. I'll type: \<SqlFile

This is usefull when we want to run some prepared SQL script. The problem is that we will get the result, but we will not know what statement caused that result. To solve this we will use echo "\e".

After we turn on echo, every statement will be shown before the result of the statement. Useless for SQL but great for files.

I will again quit, and then login back, to disable effect of the echo command. quit mclient -u monetdb -d DBfarmDB --password monetdb

Other Backslash Commands

"\?" will give a list of all of the backslash commands.

"\help" will give a list of SQL statements.

We already saw how to use timing in this blog post "link". It is also possible to turn on timing inside of the mclient. Options are "none, clock, performance". \t clock SELECT 599;

\t performance SELECT 599;

--statements-- sql = parsing opt = optimizing run = running clk = total time

\t To disable timing.

We can always exit mclient with the "\q", or "quit".

Dash Commands

When we log in to mclient, we can provide an IP address, port number, username and database name. There are many more of these arguments

# mclient -h 192.168.100.124 -p 50001 -u monetdb -d DBfarmDB

Already Known Commands

Many commands exist in both backslash and dash version. I will give you example of such commands here.
During this example I will alternately login and quit, because dash commands can be run only during the login.

This will create sql dump of the whole database. mclient -u monetdb -d DBfarmDB -D

This is the same as "\D" backslash command, that we saw earlier.

We can enable tracing and logging.   mclient -u monetdb -d DBfarmDB -X -L FileLog

The same as "\X" and "\L".

We will set that our queries return expanded format, we will turn on echo and the timing. mclient -u monetdb -d DBfarmDB -f expanded -t clock -e   Format and Time support all of the possible modes.

The same as "\f, \t, \e".

With "-a" switch we can turn off autocommit for the whole session: "mclient -u monetdb -d DBfarmDB -f -a".

When we run a query, it will be paged to 5 rows, and the visible width will be 50 characters.   mclient -u monetdb -d DBfarmDB -r 5 -w 50

We already saw this with commands "\r, \w".

We can apply shell command on all of our queries. Notice that pipe must be escaped.   mclient -u monetdb -d DBfarmDB -\| 'grep m'

The same as \| grep "m".

History, Null and Timezone

I will login again with some new options: mclient -u monetdb -d DBfarmDB -z -n "zero" -H

Option "-z" is for disabling the time zone. Statement: SELECT CURRENT_TIME; , will give me Greenwich time, not my local time.

Option "-n" will define how the null will be presented in the terminal. This is only valid when "-f" format is either "sql,csv or tab".

Default format is "sql", we have set nul to be "zero", so the null will be presented as a "zero". SELECT null;        --result is "zero"

In this session we have run two statements: --SELECT CURRENT_TIME; --SELECT null;

Because our session was started with the switch history "-H" both of these statements are recorded in the file: /home/fffovde/.mapiclient_history_sql

I will now quit this session, and then I will login again: quit mclient -u monetdb -d DBfarmDB -H

Because I have used "-H" switch, history of mclient statements will be available for me. By pressing keyboard arrows, I will list that history.

If I run one more statement, that statement will be added to "/.mapiclient_history_sql" file. So, when "-H" switch is active, new statements will be added to history file, and that history will be available to us. SELECT 55; We can delete the history by deleting directly from the ".mapiclient_history_sql" file.

Getting Data from URLs

I will create a new table and then I will try to get data from the internet to insert into that table. Mclient will refuse to do that. CREATE TABLE IndustryList( Industry VARCHAR(50) ); COPY INTO IndustryList FROM 'https://cdn.wsform.com/wp-content/uploads/2020/06/industry.csv' ON CLIENT;

I will quit the session, and then I will login with a remote "-R" switch. quit mclient -u monetdb -d DBfarmDB -R   --password monetdb COPY INTO IndustryList FROM 'https://cdn.wsform.com/wp-content/uploads/2020/06/industry.csv' ON CLIENT;

It will work now. I can read from "IndustryList" table.   SELECT * FROM IndustryList;

SQL Dump with Insert Into

Normal dump will create a script that will fill a table with the data from the Stdin. It will use "COPY INTO FROM stdin" statement.

If we want to get "INSERT INTO" statements, then we have to add switch "-N". mclient -u monetdb -d DBfarmDB -D -N

Running SQL Statements from the Shell

With "-s" switch we can run a statement, instead of logging in. mclient -u monetdb -d DBfarmDB -s "SELECT null;"

We can also run a script directly from the shell. We previously created this file. mclient -u monetdb -d DBfarmDB SqlFile

We can use them both: mclient -u monetdb -d DBfarmDB -s "SELECT null;" SqlFile

Many of the previously shown options will work with the shell query, too.   mclient -u monetdb -d DBfarmDB -s "SELECT null;" -n "zero"

I will add command "\f xml" in the SqlFile.

This will make our command to fail. mclient -u monetdb -d DBfarmDB SqlFile

We will add interactive switch "-i". It will work now.             mclient -u monetdb -d DBfarmDB SqlFile -i

DOTMONETDBFILE

We already talked about DOTMONETDBFILE environment variable, in the blog post about backup "link". All of that is valid for the mclient, too. Inside of the ".monetdb" file we can have properties on the right.

user=monetdb password=monetdb host=192.168.100.145 port=50000 save_history=true format=csv width=50