MonetDB Client Applications

MonetDB Client Applications giulia Mon, 02/24/2020 - 15:03

mclient

mclient sjoerd Tue, 04/26/2011 - 12:34

NAME

mclient − the MonetDB command-line tool

SYNOPSIS

mclient [ options ] [ file or database [ file ... ] ]
mclient −−help

DESCRIPTION

MonetDB is a database management system that is developed from a main-memory perspective with use of a fully decomposed storage model, automatic index management, extensibility of data types and search accelerators, and an SQL front end.

Mclient is the command-line interface to the MonetDB server.

If the −−statement=query (−s query) option is given, the query is executed. If any files are listed after the options, queries are read from the files and executed. The special filename refers to standard input. Note that if there is both a −−statement option and filename arguments, the query given with −−statement is executed first. If no −−statement option is given and no files are specified on the command line, mclient reads queries from standard input.

When reading from standard input, if standard input is a terminal or if the −−interactive (−i) option is given, mclient interprets lines starting with \ (backslash) specially. See the section BACKSLASH COMMANDS below.

Before mclient starts parsing command line options, it reads a configuration file. If the environment variable DOTMONETDBFILE is set and not empty, it reads the file pointed to by that variable. If set but empty, no configuration file is read. If unset, mclient first looks for a file .monetdb in the current working directory, and if that doesn’t exist, it looks for a file monetdb in the XDG configuration directory ($XDG_CONFIG_HOME which defaults to $HOME/.config if not set), and finally for a file .monetdb in the current user’s home directory. This file can contain defaults for the flags user, password, language, database, save_history, format, host, port, and width. For example, an entry in a .monetdb file that sets the default language for mclient to mal looks like this: language=mal. To disable reading the .monetdb file, set the variable DOTMONETDBFILE to the empty string in the environment.

OPTIONS

General Options
−−help
(−?)

Print usage information and exit.

−−version (−v)

Print version information and exit.

−−encoding=encoding (−E encoding)

Specify the character encoding of the input. The option applies to both the standard input of mclient and to the argument of the −−statement (−s) option but not to the contents of files specified on the command line (except for which refers to standard input) or files specified using the \< command (those must be encoded using UTF-8). The default encoding is taken from the locale.

−−language=language (−l language)

Specify the query language. The following languages are recognized: mal and sql. A unique prefix suffices. When the −−language option is omitted, the default of sql is assumed.

−−database=database (−d database)

Specify the name or URI of the database to connect to. The −d can be omitted if an equally named file does not exist in the current directory. As such, the first non-option argument will be interpreted as database to connect to if the argument does not exist as file. Valid URIs are as returned by ‘monetdb discover‘, see monetdb(1), and look like mapi:monetdb://hostname:port/database.

−−host=hostname (−h hostname)

Specify the name of the host on which the server runs (default: localhost). When the argument starts with a forward slash (/), host is assumed to be the directory where the UNIX sockets are stored for platforms where these are supported.

−−port=portnr (−p portnr)

Specify the portnumber of the server (default: 50000).

−−interactive (−i)

When reading from standard input, interpret lines starting with \ (backslash) specially. See the section BACKSLASH COMMANDS below.

−−timer=timermode (−t timermode)

The timer command controls the format of the time reported for queries. The default mode is none which turns off timing reporting. The timer mode clock reports the client-side wall-clock time ("clk") in a human-friendly format. The timer mode performance reports client-side wall-clock time ("clk") as well as detailed server-side timings, all in milliseconds (ms): the time to parse the SQL query, optimize the logical relational plan and create the initial physical (MAL) plan ("sql"); the time to optimize the physical (MAL) plan ("opt"); the time to execute the physical (MAL) plan ("run"). All timings are reported on stderr.
Note
that the client-measured wall-clock time is reported per query only when options −−interactive or −−echo are used, because only then does mclient send individual lines (statements) of the SQL script to the server. Otherwise, when mclient sends the SQL script in large(r) batch(es), only the total wall-clock time per batch is measured and reported. The server-measured detailed performance timings are always measured and reported per query.

−−user=user (−u user)

Specify the user to connect as. If this flag is absent, the client will ask for a user name, unless a default was found in the .monetdb or $DOTMONETDBFILE file.

−−format=format (−f format)

Specify the output format. The possible values are sql, expanded, x, csv, tab, raw, xml, trash, and rowcount. csv is comma-separated values; tab is tab-separated values; raw is no special formatting (data is dumped the way the server sends it to the client); sql is a pretty format which is meant for human consumption where columns are clearly shown; expanded and x are synonyms and are another pretty format meant for human consumption where column values are printed in full and below each other; xml is a valid (in the XML sense) document; trash does not render any output, enabling performance measurements free of any output rendering/serialization costs; and rowcount is a variation on trash where only the number of affected rows is printed. In addition to plain csv, two other forms are possible. csv=c uses c as column separator; csv+c uses c as column separator and produces a single header line in addition to the data.

−−echo (−e)

Echo the query. Note that using this option slows down processing.

−−history (−H)

If compiled with the readline(3) library, load and save the command line history (default off).

−−log=logfile (−L logfile)

Save client/server interaction in the specified file.

−−statement=stmt (−s stmt)

Execute the specified query. The query is run before any queries from files specified on the command line are run.

−−timezone (−z)

Do not tell the client’s timezone to the server.

−−Xdebug (−X)

Trace network interaction between mclient and the server.

−−pager=cmd (−| cmd)

Send query output through the specified cmd. One cmd is started for each query. Note that the | will have to be quoted or else the shell will interpret it.

SQL Options
−−null=
nullstr (−n nullstr)

Set the string to be used as NULL representation when using the sql, csv, or tab output formats. If not used, NULL values are represented by the string "null" in the sql output format, and as the empty string in the csv and tab output formats. Note that an argument is required, so in order to use the empty string, use −n "" (with the space) or −−null=.

−−autocommit (−a)

Switch autocommit mode off. By default, autocommit mode is on.

−−allow−remote (−R)

Allow remote content (URLs) in the COPY INTO table FROM file ON CLIENT ... query. Remote content is retrieved by mclient.

−−rows=nr (−r nr)

If specified, query results will be paged by an internal pager at the specified number of lines.

−−width=nr (−w nr)

Specify the width of the screen. The default is the (initial) width of the terminal.

−−dump (−D)

Create an SQL dump.

−−inserts (−N)

Use INSERT INTO statements instead of COPY INTO + CSV values when dumping the data of a table. This option can be used when trying to load data from MonetDB into another database, or when e.g. JDBC applications are used to reload the dump.

BACKSLASH COMMANDS

General Commands

\?

Show a help message explaining the backslash commands.

\q

Exit mclient.

\< file

Read input from the named file.

\> file

Write output to the named file. If no file is specified, write to standard output.

\| command

Pipe output to the given command. Each query is piped to a new invocation of the command. If no command is given, revert to writing output to standard output.

\h

Show the readline(3) history.

\L file

Log client/server interaction in the given file. If no file is specified, stop logging information.

\X

Trace what mclient is doing. This is mostly for debugging purposes.

\e

Echo the query in SQL formatting mode.

\f format

Use the specified format mode to format the output. Possible modes the same as for the −−format (−f) option.

\w width

Set the maximum page width for rendering in the sql formatting mode. If width is −1, the page width is unlimited, when width is 0, use the terminal width. If width is greater than 0, use the given width.

\r rows

Use an internal pager using rows per page. If rows is −1, stop using the internal pager.

SQL Commands

\D

Dump the complete database. This is equivalent to using the program msqldump(1).

\D table

Dump the given table.

\d

Alias for \dvt.

\d[Stvsfn]+

List database objects of the given type. Multiple type specifiers can be used at the same time. The specifiers S, t, v, s, f and n stand for System, table, view, sequence, function and schema respectively. Note that S simply switches on viewing system catalog objects, which is orthogonal to the other specifiers.

\d[Stvsfn]+ object

Describe the given object in the database using SQL statements that reconstruct the object. The same specifiers as above can be used, following the same rules. When no specifiers are given, vt is assumed. The object can be given with or without a schema, separated by a dot. The object name can contain the wildcard characters * and _ that represent zero or more, and exactly one character respectively. An object name is converted to lowercase, unless the object name is quoted by double quotes ("). Examples of this, are e.g. *.mytable, tabletype*, or "myschema.FOO". Note that wildcard characters do not work in quoted objects. Quoting follows SQL quoting rules. Arbitrary parts can be quoted, and two quotes following each other in a quoted string represent the quote itself.

\A

Enable auto commit mode.

\a

Disable auto commit mode.

EXAMPLES

Efficiently import data from a CSV (comma-separated values) file into a table. The file must be readable by the server. $file is the absolute path name of the file, $table is the name of the table, $db is the name of the database.

mclient −d $db −s "COPY INTO $table FROM ’$file’ USING DELIMITERS ’,’,E’\\n’,’\"’"

Efficiently import data from a CSV file into a table when the file is to be read by mclient (e.g. the server has no access to the file). $file is the (absolute or relative) path name of the file, $table is the name of the table, $db is the name of the database.

mclient −d $db −s "COPY INTO $table FROM STDIN USING DELIMITERS ’,’,E’\\n’,’\"’" − < $file

Note that in this latter case, if a count of records is supplied, it should be at least as large as the number of records actually present in the CSV file. This, because otherwise the remainder of the file will be interpreted as SQL queries.

Another, easier method to have the client read the file content is as follows:

mclient −d $db −s "COPY INTO $table FROM ’$file’ ON CLIENT USING DELIMITERS ’,’,E’\\n’,\"’"

In this case the value of $file can be a path name relative to the directory in which mclient was started. If, in addition, the option −−allow−remote is passed to mclient, the $file in the above query can also be a URL. It then has to have the form schema://string, e.g., https://www.example.org/dumpdata.csv.

See https://www.monetdb.org/Documentation/Manuals/SQLreference/CopyInto for more information about the COPY INTO query.

SEE ALSO

msqldump(1), mserver5(1)

mclient --help

mclient --help mk Sun, 03/28/2010 - 23:14

The mclient program is the universal command-line tool that implements the MAPI protocol for client-server interaction with MonetDB.
On a Windows platform it can be started using start->MonetDB->MonetDB SQL Client. Alternatively, you can use the command window to start mclient.exe. Be aware that your environment variables are properly set to find the libraries of interest.
On a Linux platform it provides readline functionality, which greatly improves user interaction. A history can be maintained to ease interaction over multiple sessions.

The default setting is geared at establishing a guest connection to a MonetDB SQL database server at a default server running on the localhost. The -h hostname specifies on which machine the MonetDB server is running. If you communicate with a MonetDB server on the same machine, it can be omitted. The default TCP port used is 50000. If this port happens to be in use on the server machine (which generally is only the case if you run two MonetDB servers on it), you will have to use the -p port do define the port to which the mserver is listening. Otherwise, it may also be omitted. If there is more than one mserver running, you must also specify the database name -d database. In this case, if your port is set to the wrong database, the connection will always be redirected to the correct one. Note that the default port (and other default options) can be set in the server configuration file.

shell>mclient --help
Usage: mclient [ options ] [ file or database [ file ... ] ]

Options are:
 -h hostname | --host=hostname    host or UNIX domain socket to connect to
 -p portnr   | --port=portnr      port to connect to
 -u user     | --user=user        user id
 -d database | --database=database  database to connect to (may be URI)
 -e          | --echo             echo the query
 -E charset  | --encoding=charset specify encoding (character set) of the terminal
 -f kind     | --format=kind      specify output format {csv,tab,raw,sql,xml,trash,rowcount}
 -H          | --history          load/save cmdline history (default off)
 -i          | --interactive      interpret `\' commands on stdin
 -t          | --timer=format     use time formatting {none,clock,performance} (none is default)
 -l language | --language=lang    {sql,mal}
 -L logfile  | --log=logfile      save client/server interaction
 -s stmt     | --statement=stmt   run single statement
 -X          | --Xdebug           trace mapi network interaction
 -z          | --timezone         do not tell server our timezone
 -| cmd      | --pager=cmd        for pagination
 -v          | --version          show version information and exit
 -?          | --help             show this usage message

SQL specific opions 
 -n nullstr  | --null=nullstr     change NULL representation for sql, csv and tab output modes
 -a          | --autocommit       turn off autocommit mode
 -R          | --allow-remote     allow remote content
 -r nr       | --rows=nr          for pagination
 -w nr       | --width=nr         for pagination
 -D          | --dump             create an SQL dump
 -N          | --inserts          use INSERT INTO statements when dumping
The file argument can be - for stdin

Within the context of each query language there are more options. They can be shown using the command \? or using the commandline.

For SQL there are several knobs to tune for a better rendering of result tables (\w).

shell>mclient -d demo
Welcome to mclient, the MonetDB/SQL interactive terminal (Oct2020)
Database: MonetDB v11.39.5 (Oct2020), 'demo'
FOLLOW US on https://twitter.com/MonetDB or https://github.com/MonetDB/MonetDB
Type \q to quit, \? for a list of available commands
auto commit mode: on
sql>\?
\?       - show this message
\<file   - read input from file
\>file   - save response in file, or stdout if no file is given
\|cmd    - pipe result to process, or stop when no command is given
\history - show the readline history
\help    - synopsis of the SQL syntax
\D table - dumps the table, or the complete database if none given.
\d[Stvsfn]+ [obj] - list database objects, or describe if obj given
\A       - enable auto commit
\a       - disable auto commit
\e       - echo the query in sql formatting mode
\t       - set the timer {none,clock,performance} (none is default)
\f       - format using renderer {csv,tab,raw,sql,xml,trash,rowcount,expanded}
\w#      - set maximal page width (-1=unlimited, 0=terminal width, >0=limit to num)
\r#      - set maximum rows per page (-1=raw)
\L file  - save client-server interaction
\X       - trace mclient code
\q       - terminate session and quit mclient
sql>

msqldump

msqldump sjoerd Tue, 09/20/2011 - 14:45

NAME

msqldump − dump a MonetDB/SQL database

SYNOPSIS

msqldump [ options ] [ dbname ]

DESCRIPTION

MonetDB is a database management system that is developed from a main-memory perspective with use of a fully decomposed storage model, automatic index management, extensibility of data types and search accelerators, and an SQL front end.

Msqldump is the program to dump an MonetDB/SQL database. The dump can be used to populate a new MonetDB/SQL database.

Before msqldump starts parsing command line options, it reads a configuration file. If the environment variable DOTMONETDBFILE is set and not empty, it reads the file pointed to by that variable. If set but empty, no configuration file is read. If unset, msqldump first looks for a file .monetdb in the current working directory, and if that doesn’t exist, it looks for a file monetdb in the XDG configuration directory ($XDG_CONFIG_HOME which defaults to $HOME/.config if not set), and finally for a file .monetdb in the current user’s home directory. This file can contain defaults for the flags user, password, host, and port. To disable reading the .monetdb file, set the variable DOTMONETDBFILE to the empty string in the environment.

OPTIONS

−−help (−?)

Print usage information and exit.

−−database=database (−d database)

Specify the name of the database to connect to. The −d can be omitted if it is the last option.

−−host=hostname (−h hostname)

Specify the name of the host on which the server runs (default: localhost).

−−port=portnr (−p portnr)

Specify the portnumber of the server (default: 50000).

−−user=user (−u user)

Specify the user to connect as. If this flag is absent, the client will ask for a user name.

−−describe (−D)

Only dump the database schema.

−−inserts (−N)

When dumping the table data, use INSERT INTO statements, rather than COPY INTO + CSV values. INSERT INTO statements are more portable, and necessary when the load of the dump is processed by e.g. a JDBC application.

−−noescape (−e)

When dumping the table data, use the NO ESCAPE option on the COPY INTO query.

−−functions (−f)

Only dump functions definitions.

−−table=table (−t table)

Only dump the specified table.

−−quiet (−q)

Don’t print the welcome message.

−−Xdebug (−X)

Trace network interaction between mclient and the server.

SEE ALSO

mclient(1), mserver5(1)

jdbcclient

jdbcclient mk Sun, 03/28/2010 - 23:15

The jdbcclient program is a textual command line program (inspired by mclient) written in Java using the JDBC API and the MonetDB JDBC driver. It is available as a single small jar file jdbcclient.jre7.jar downloadable from MonetDB Java Download Area. The jar file already includes the MonetDB JDBC driver, which is used to communicate to a MonetDB server, so no further jar files (or classpath settings) are needed.

The jdbcclient program supports startup options. To view them simply type in a shell the command:

     java -jar jdbcclient.jre7.jar --help

This will show following usage information:

Usage java -jar jdbcclient.jre7.jar
		[-h host[:port]] [-p port] [-f file] [-u user]
		[-l language] [-d database] [-e] [-D [table]] [-X<opt>]
		| [--help] | [--version]
or using long option equivalents --host --port --file --user --language
--database --echo --dump.
Arguments may be written directly after the option like -p50000.

If no host and port are given, localhost and 50000 are assumed.
An .monetdb file may exist in the user's home directory.  This file can contain
preferences to use each time jdbcclient is started.  Options given on the
command line override the preferences file.  The .monetdb file syntax is
<option>=<value> where option is one of the options host, port, file, mode
debug, or password.  Note that the last one is perilous and therefore not
available as command line option.
If no input file is given using the -f flag, an interactive session is
started on the terminal.

OPTIONS
-h --host     The hostname of the host that runs the MonetDB database.  A port
              number can be supplied by use of a colon, i.e. -h somehost:12345.
-p --port     The port number to connect to.
-f --file     A file name to use either for reading or writing.  The file will
              be used for writing when dump mode is used (-D --dump).  In read
              mode, the file can also be an URL pointing to a plain text file
              that is optionally gzip compressed.
-u --user     The username to use when connecting to the database.
-d --database Try to connect to the given database (only makes sense if
              connecting to monetdbd).
-l --language Use the given language, defaults to 'sql'.
--help        This help screen.
--version     Display driver version and exit.
-e --echo     Also outputs the contents of the input file, if any.
-q --quiet    Suppress printing the welcome header.
-D --dump     Dumps the given table(s), or the complete database if none given.
-Xoutput      The output mode when dumping.  Default is sql, xml may be used for
              an experimental XML output.
-Xhash        Use the given hash algorithm during challenge response.  Supported
              algorithm names: SHA1, MD5, plain.
-Xdebug       Writes a transmission log to disk for debugging purposes.  If a
              file name is given, it is used, otherwise a file called
              monet<timestamp>.log is created.  A given file never be
              overwritten; instead a unique variation of the file is used.
-Xbatching    Indicates that a batch should be used instead of direct
              communication with the server for each statement.  If a number is
              given, it is used as batch size.  i.e. 8000 would execute the
              contents on the batch after each 8000 statements read.  Batching
              can greatly speedup the process of restoring a database dump.

 

Using the jdbcclient program

It is easy to start it (assuming you have a MonetDB/SQL server running) from a shell, for example:

% java -jar jdbcclient.jre7.jar -p50000 -ddemo -umonetdb
password:

Welcome to the MonetDB interactive JDBC terminal!
JDBC Driver: MonetDB Native Driver v2.29 (Liberica 20190926 based on MCL v1.18)
Database Server: MonetDB v11.35.19
Current Schema: sys
Type \q to quit (you can also use: quit or exit), \? or \h for a list of available commands
auto commit mode: on
sql>

As the password cannot be provided as startup option, you will be asked to enter it after the password: prompt.

Tip: if you do not want to enter the password each time, use a .monetdb file (which contains the user and password settings) similar as for mclient

After a successful connection the sql> prompt is shown, allowing you to enter any SQL query or backslash command and execute it by using the enter-key. You can use multiple lines to enter the SQL query. To execute it enter the ; character after the SQL query or command, and press the enter-key. For example:

sql> select * from table_types
more> order by 2;
+---------------+------------------------+
| table_type_id | table_type_name        |
+===============+========================+
|            20 | GLOBAL TEMPORARY TABLE |
|            30 | LOCAL TEMPORARY TABLE  |
|             3 | MERGE TABLE            |
|             5 | REMOTE TABLE           |
|             6 | REPLICA TABLE          |
|             4 | STREAM TABLE           |
|            10 | SYSTEM TABLE           |
|            11 | SYSTEM VIEW            |
|             0 | TABLE                  |
|             1 | VIEW                   |
+---------------+------------------------+
10 rows
Elapsed Time: 4 ms
sql> 

To view a list of available backslash commands enter \? after the sql> prompt and the enter-key.

sql> \?
Available commands:
\q       quits this program (you can also use: quit or exit)
\d       list available user tables and views in current schema
\dS      list available system tables and views in sys schema
\d <obj> describes the given table or view
\l<uri>  executes the contents of the given file or URL
\i<uri>  batch executes the inserts from the given file or URL
\? or \h this help screen
sql>

Use \q to quit the program. Alternatively you may use quit or exit commands.

If the connection or authentication fails, observe the printed error messages from jdbcclient (and/or the merovingian logs) for clues.

Note: The JDBC protocol does not support the SQL: DEBUG <query> ; or TRACE <query> ; commands functionality. Use the mclient program for those instead.

Note: the jdbcclient program is provided as a simple tool for performing connectivity and interactive tests using MonetDB JDBC driver.
It is *not* recommended for production environments, the mclient program should be used instead.