Using Amazon Redshift with Hibernate

Amazon Redshift is not designed for ORMs like Hibernate. However, it sometimes may be useful to let Hibernate manage a couple of tables. For example, we were automatically generating some large tables for big-data analysis, and wanted to store metadata about the generated structures in the same database, to keep them in sync. Hibernate seemed convenient to manipulate these metadata.

First, that’s not a good idea. Hibernate tends to generate a lot of useless queries, unless you take the time to properly tweak it, you’ll feel the pain of letting Hibernate query a RedShift server on the other side of the planet (close to 0.5 second for a “SELECT 1” query sent to the database…).

Then, you’ll notice that Redshift SQL may be based on Postgres, still not everything is working as expected. Especially, postgres serial type (identity columns) are not supported.

Eventually, you’ll find out there’s no way to retrieve the value of the last identity generated for a inserted row. That’s a serious issue knowing that hibernate requires a primary key to identify inserted objects.

But if you still crave using Hibernate with RedShift, once you’ll have downloaded the JDBC driver, you may want to use this (unperfect) Hibernate Redshift dialect to get you started :

import org.hibernate.dialect.PostgreSQL82Dialect;
import org.hibernate.id.IdentityGenerator;

public class RedshiftDialect extends PostgreSQL82Dialect {

    @Override
    public String getIdentitySelectString(String table, String column, int type) {
        // There's not method in the JDBC driver to retrieve the latest generated id.
        // So try to retrieve the largest id, and pray for no concurrent access.
        return "SELECT MAX("+column+") from "+table;
    }

    @Override
    public String getIdentitySelectString() {
        // Should never be called
        return "SELECT 1 FROM PG_TEST";
    }

    @SuppressWarnings("rawtypes")
    @Override
    public Class getNativeIdentifierGeneratorClass() {
        return IdentityGenerator.class;
    }
    
    @Override
    public String getIdentityColumnString( int type )
    {
        // replace the Postgres "serial" type with RedShift Vertica one
        return "IDENTITY";
    }
 
    @Override
    public boolean hasDataTypeInIdentityColumn()
    {
        return true;
    }
}

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');