MonetDB:Various tips

From MonetDB
Jump to: navigation, search

Interface[edit]

Default login for Mclient[edit]

A common way to connect to a DB server is to use:

  mclient -u<user> [options or database]

The user is then prompted for a password (a user will also be asked for if -u is not specified).

An faster method is to create a file .monetdb in the home folder. The following syntax is to be used:

   user=[user]
   password=[password]

By default, Mclient will use these credentials, no need to specify a user or input a password.

Mclient options and commands[edit]

Two basic but powerful sources of information:

   mclient --help

and

   sql> \?

in a Mclient SQL session

A few useful SQL commands[edit]

Inspecting the catalog[edit]

The table tables lists all the relations and views used in the current session. Within this table, the field type indicates the type of object: 0 for table, 1 for view.

User tables and views can be listed as follows:

 SELECT * FROM tables WHERE system = false;

System tables (describing, e.g. schemas, users, types) are also listed in tables, with self-explanatory names.

Debugging a query[edit]

The following can be used:

  PLAN [query]

Ouputs a high level view view of the query plan

  EXPLAIN [query]

Outputs the query decomposition into a MAL plan.

  TRACE [query]

Provides timings and information about the query execution

  DEBUG [query]

Launches a GDB-like interface for a step-by-step analysis of the query.

Higher levels of precision will be achieved with external tools, such as Stethoscope and GDB.

Optimizer modules in use[edit]

  SELECT optimizer;

It can sometimes be useful to select a minimal optimizer configuration:

  SET optimizer = 'minimal_pipe';

SQL Function lookup[edit]

  SELECT * FROM functions;

More info in source code file:

[source rep]/sql/common/sql_types.c

Loading Data[edit]

Copy into Table[edit]

Assume the Separators are

  • | between attributes
  • newline between records
  • " as string quotes
 copy into targettable from 'inputdata.csv' using delimiters '|', '\n', '\;