How to fix Vertica error “Attempted to create too many ROS containers for projection” ?

While loading data into the HPE Vertica database, you may run into this issue :

[Vertica][VJDBC](2245) ERROR: Attempted to create too many ROS containers for projection x.y

You may also notice that the error disapears if you wait a few minutes before restarting your data loading process. Only to reappear a little while later.

The issue comes from the vertica Read Optimized Store (ROS).

When loading data into Vertica, Continue reading

Monetdb : how to get schema and tables information ?

As other SQL database engines, monetdb allows to query schema and table information through SQL queries over a few system tables, namely : sys.schemas, sys.tables, and sys.columns.

The following query will give columns details for the system table “tables”:

SELECT c.id, c.name, c.type, c.type_digits 
    FROM sys.columns c LEFT JOIN sys.tables t ON c.table_id = t.id 
    WHERE t.name = 'tables';

The resultset :

+------+---------------+----------+-------------+
| id   | name          | type     | type_digits |
+======+===============+==========+=============+
| 5211 | id            | int      |          32 |
| 5212 | name          | varchar  |        1024 |
| 5213 | schema_id     | int      |          32 |
| 5214 | query         | varchar  |        2048 |
| 5215 | type          | smallint |          16 |
| 5216 | system        | boolean  |           1 |
| 5217 | commit_action | smallint |          16 |
| 5218 | access        | smallint |          16 |
| 5219 | temporary     | smallint |          16 |
+------+---------------+----------+-------------+ 

Monetdb : how to convert a string to a timestamp ?

Monetdb is great tool, with a not so great documentation… Converting a string/varchar to a timestamp is easy once you know the proper function to call.

The time conversion functions are listed in the [Monetdb installation folder]\MonetDB5\lib\monetdb5\createdb\13_date.sql file :

create function str_to_date(s string, format string) returns date
	external name mtime."str_to_date";

create function date_to_str(d date, format string) returns string
	external name mtime."date_to_str";

create function str_to_time(s string, format string) returns time
	external name mtime."str_to_time";

create function time_to_str(d time, format string) returns string
	external name mtime."time_to_str";

create function str_to_timestamp(s string, format string) returns timestamp
	external name mtime."str_to_timestamp";

create function timestamp_to_str(d timestamp, format string) returns string
	external name mtime."timestamp_to_str";

The syntax of the format string follows the MySQL one.

And eventually, here is a conversion example of a varchar to a timestamp

select str_to_timestamp('2016-02-04 15:30:29', '%Y-%m-%d %H:%M:%S');

How to import data from MySQL to SQL Server 2014 ?

Importing data from one database engine to another is often a pain. Getting the data out of MySQL is easy, but pushing it to SQL Server may be harder. The easiest way to achieve this is to use “SQL Server Import and Export Wizard” to directly query your MySQL Database, and push the data in your SQL Server.

Trying other techniques often leads to a waste of time. For example, Continue reading