Using Postgres (and AWS Redshift) DB Link with JOOQ

Question

We are trying to establish a DB link between Postgres and AWS Redshift DB (which isn't a problem) but we are using JOOQ to construct DB Query for the same.

What is working? We are able to write JOIN SQL queries for the data we want to fetch perfectly fine if the two tables were in the same database. For example, if we have a query:

SELECT somefields
FROM dblink('global_database'::text, '
SELECT
    ... some data selected...) t1(username text, location int, createdAt timestamptz)
         JOIN user_meta t2 on "userId" = t1.userId
    AND createdAt between ... some date range ...
WHERE ...'
GROUP BY ...
ORDER BY ... DESC;

Now we are constructing the query with JOOQ:

Query query = dslContext
                .select(somefields))
                .from(table(TABLE))
                .rightJoin(TABLE_TWO).on(getJoinOnCondition())
                .where(whereCondition)
                .groupBy(groupByFields)
                .orderBy(orderByFields)
                .limit((int) pageRequest.getPageSize());

How to establish the DB link in the JOOQ Query?

Lukas Eder · Accepted Answer · 2021-04-02T17:10:21.873

Do it in 2 steps:

Generate code for both of your databases separately. Make sure the Redshift tables don't have a schema associated with them, using <outputSchemaToDefault>true</outputSchemaToDefault>, see the manual
Whenever you use a dblink, wrap your Redshift table in a plain SQL template when you put it in the FROM clause, and use the generated class otherwise, using e.g. the below utility:

static Table<?> dblink(String conn, Table<?> table, Field<?>... fields) {
    return table("dblink({0}, {1}) as {2}({3})",
        inline(conn),
        inline(DSL.using(POSTGRES).render(select(fields).from(table))),
        table.getUnqualifiedName(),
        list(
            Stream.of(fields)
                  .map(f -> DSL.sql("{0} {1}",
                       f.getUnqualifiedName(),
                       sql(f.getDataType().getCastTypeName())
                  ))
                  .toArray(QueryPart[]::new)
        )
    );
}

The usual static import is implied:

import static org.jooq.impl.DSL.*;

And then use this utility like this:

Query query = dslContext
                .select(somefields))
                .from(dblink("global_database", TABLE, TABLE.fields()))
                .rightJoin(TABLE_TWO).on(getJoinOnCondition())
                // You can now use TABLE as if it were a local table
                .where(whereCondition)
                .groupBy(groupByFields)
                .orderBy(orderByFields)
                .limit((int) pageRequest.getPageSize());

Not sure where you constructed the Redshift query separately. The main part which I do not understand is `.from(dblink("global_database", TABLE, TABLE.fields()))`. This just specifies the DB Link name, table reference and fields. Where do we put the actual query, with possible WHERE clause in it? — Shubham A., Apr 02 '21 at 14:04
@ShubhamA.: I showed you the definition of the `dblink()` method. Look at it, it's all there. It uses a [plain SQL template](https://www.jooq.org/doc/latest/manual/sql-building/plain-sql-templating/) — Lukas Eder, Apr 02 '21 at 17:09

Using Postgres (and AWS Redshift) DB Link with JOOQ

1 Answers1