Redshift CREATE TABLE with DISTKEY

[Sujeevan Nagarajah]

Hello,

I am using jOOQ 3.7.0-SNAPSHOT to generate Redshift CREATE TABLE statement. I'm able to successfully generate simple table statement. Currently I'm looking for a way to add Redshift specific keywords like DISTKEY, SORTKEY, DISTSTYLE, ..etc (http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html). 

But I couldn't find any methods related to them. Can someone advice me on how to specify them?

My code currently looks something like below,

DSL.using(SQLDialect.REDSHIFT).createTable(name("schemaName", "tableName")).column("sales", RedshiftDataType.DECIMAL.precision(19, 3)).column("product", RedshiftDataType.VARCHAR).toString();

Thank you.

Best Regards,

Sujee

[Lukas Eder]

Hello Sujee,

Thanks for your enquiry. The main reason why jOOQ supports DDL statements is the fact that developers may want to quickly reproduce a simplified version of their schema on a test database like H2, for instance. The workflow would be :

- Write DDL for Oracle

- Generate jOOQ classes from your DDL

- Generate DDL for H2 and execute it

- Run integration tests using jOOQ classes on H2 instead of Oracle

We cannot go beyond the above, because each vendor has a very rich set of syntax extensions to the SQL standard, involving storage clauses, hints, flags, etc. While we could add support for the keywords you've mentioned (DISTKEY, SORTKEY, DISTSTYLE), you would be quickly missing other keywords later on, again.

This is why we generally suggest using something like Flyway for database migrations and DDL management. Since Flyway is a SQL template based API, it will not suffer from these limitations.

More information can be found here:

http://www.jooq.org/doc/latest/manual/sql-building/ddl-statements

I hope this helps,
Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Sujeevan Nagarajah]

Thank you Lukas. Now I understand the primary use case of jOOQ createTable feature. 

Typical application has a fixed schema or schema that rarely changes. Also when there is a change, schema changes applied during application start.

But my use case is totally different from it. I'm working on a feature where schema of a dataset is detected and corresponding table is created in run-time. Since the schema is generated programatically in runtime, I'm looking for some kind of Java DSL library like jOOQ createTable. Obviously Flyway can be useful here to manage the schema generated by DSL but it doesn't help to generate it. What would you recommend for my use-case? An idea I have is to do some String manipulation on the table statement result generated by jOOQ. 

Thank you.

[ben....@gmail.com]

Have you considered Liquibase? It provides an abstraction from SQL, with an escape hatch to write custom scripts. It may be easier to generate a changelog document from your inferred schema (and unit test it with H2), injecting custom SQL for a dialect as needed.

[Lukas Eder]

Hi Sujee, Ben

2015-05-21 9:03 GMT+02:00 <ben....@gmail.com>:

Have you considered Liquibase? It provides an abstraction from SQL, with an escape hatch to write custom scripts. It may be easier to generate a changelog document from your inferred schema (and unit test it with H2), injecting custom SQL for a dialect as needed.

I was tempted to mention Liquibase myself, although I'm not sure if it can handle this particular syntax (i.e. a flag that appears after the data type).

Clearly, Liquibase is providing a similar idea of SQL migration as jOOQ does, although Liquibase probably has a richer DDL API right now. This will certainly change as we add support for more DDL features.

On Wednesday, May 20, 2015 at 10:12:23 PM UTC-7, Sujeevan Nagarajah wrote:

Thank you Lukas. Now I understand the primary use case of jOOQ createTable feature. 

Typical application has a fixed schema or schema that rarely changes. Also when there is a change, schema changes applied during application start.

But my use case is totally different from it. I'm working on a feature where schema of a dataset is detected and corresponding table is created in run-time. Since the schema is generated programatically in runtime, I'm looking for some kind of Java DSL library like jOOQ createTable. Obviously Flyway can be useful here to manage the schema generated by DSL but it doesn't help to generate it. What would you recommend for my use-case? An idea I have is to do some String manipulation on the table statement result generated by jOOQ. 

Thank you for these clarifications. That certainly makes sense.

Well probably, there are other databases that have similar flags at the same syntactic location. Also, we still have an open issue to support inline constraints as well in jOOQ 3.7. I think we can look into this for the next release and make this work for you:

https://github.com/jOOQ/jOOQ/issues/4298

Right now, I can see these options:

Doing string manipulation after SQL generation by jOOQ

This might work using regular expressions, as the injection place would be after a "(column name) (data type)(insert here)," pattern. This certainly doesn't feel very good

Using VisitListener to transform the SQL

The standard approach do perform custom SQL transformation directly on the jOOQ AST would be via a VisitListener implementation:

http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-sql-transformation

I'm not sure if the possibilities that we currently offer here are sufficient for DDL transformation, though, as that wasn't our primary use-case at the time we added VisitListeners

Writing your own custom Query implementation

This might be a good option if you want to support also other vendor-specific flags and options. Via plain SQL and custom QueryParts, you could write your own little DSL for this use-case:

http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-queryparts/

I guess as a workaround, the simplest solution would be to use string manipulation, indeed.

I'll be very happy to help you find other alternatives, though.

Cheers,

Lukas

[Sujeevan Nagarajah]

Thanks Ben and Lukas. This is great stuff. Will definitely keep me busy for next few days :) My comments below.

On Thursday, May 21, 2015 at 3:24:59 PM UTC+8, Lukas Eder wrote:

Hi Sujee, Ben

2015-05-21 9:03 GMT+02:00 <ben....@gmail.com>:

Have you considered Liquibase? It provides an abstraction from SQL, with an escape hatch to write custom scripts. It may be easier to generate a changelog document from your inferred schema (and unit test it with H2), injecting custom SQL for a dialect as needed.

I was tempted to mention Liquibase myself, although I'm not sure if it can handle this particular syntax (i.e. a flag that appears after the data type).

Clearly, Liquibase is providing a similar idea of SQL migration as jOOQ does, although Liquibase probably has a richer DDL API right now. This will certainly change as we add support for more DDL features.

I use Liquibase in another module in a normal way. I haven't thought about your idea which is very clever. However I'm still uncertain how much Liquibase offers in terms of Redshift dialect. I'll explore further. 

 

On Wednesday, May 20, 2015 at 10:12:23 PM UTC-7, Sujeevan Nagarajah wrote:

Thank you Lukas. Now I understand the primary use case of jOOQ createTable feature. 

Typical application has a fixed schema or schema that rarely changes. Also when there is a change, schema changes applied during application start.

But my use case is totally different from it. I'm working on a feature where schema of a dataset is detected and corresponding table is created in run-time. Since the schema is generated programatically in runtime, I'm looking for some kind of Java DSL library like jOOQ createTable. Obviously Flyway can be useful here to manage the schema generated by DSL but it doesn't help to generate it. What would you recommend for my use-case? An idea I have is to do some String manipulation on the table statement result generated by jOOQ. 

Thank you for these clarifications. That certainly makes sense.

Well probably, there are other databases that have similar flags at the same syntactic location. Also, we still have an open issue to support inline constraints as well in jOOQ 3.7. I think we can look into this for the next release and make this work for you:

https://github.com/jOOQ/jOOQ/issues/4298 

As usual, thanks Lukas for making things happen. This is my most preferred solution in the future. I'll be happy to test and provide feedback when you decided to implement.

 

Right now, I can see these options:

Doing string manipulation after SQL generation by jOOQ

This might work using regular expressions, as the injection place would be after a "(column name) (data type)(insert here)," pattern. This certainly doesn't feel very good

Using VisitListener to transform the SQL

The standard approach do perform custom SQL transformation directly on the jOOQ AST would be via a VisitListener implementation:

http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-sql-transformation

I'm not sure if the possibilities that we currently offer here are sufficient for DDL transformation, though, as that wasn't our primary use-case at the time we added VisitListeners

Writing your own custom Query implementation

This might be a good option if you want to support also other vendor-specific flags and options. Via plain SQL and custom QueryParts, you could write your own little DSL for this use-case:

http://www.jooq.org/doc/latest/manual/sql-building/queryparts/custom-queryparts/

I guess as a workaround, the simplest solution would be to use string manipulation, indeed.

I'll be very happy to help you find other alternatives, though.

My preference is more towards jOOQ createTable DSL since it is easy to express queries in Java DSL. I'm thinking to try both VisitListener and Custom query parts as they look more cleaner than String manipulation. I'll try and contact you if I need any help.

Cheers,

Lukas

[Lukas Eder]

2015-05-21 11:03 GMT+02:00 Sujeevan Nagarajah <su...@einsights.com>:

Thanks Ben and Lukas. This is great stuff. Will definitely keep me busy for next few days :) My comments below.

On Thursday, May 21, 2015 at 3:24:59 PM UTC+8, Lukas Eder wrote:

Clearly, Liquibase is providing a similar idea of SQL migration as jOOQ does, although Liquibase probably has a richer DDL API right now. This will certainly change as we add support for more DDL features.

I use Liquibase in another module in a normal way. I haven't thought about your idea which is very clever. However I'm still uncertain how much Liquibase offers in terms of Redshift dialect. I'll explore further. 

Excellent! If you have Liquibase on your stack anyway, that's going to be a good solution for now.

Would be very interesting to hear about your experience here on the user group!

On Wednesday, May 20, 2015 at 10:12:23 PM UTC-7, Sujeevan Nagarajah wrote:

Thank you Lukas. Now I understand the primary use case of jOOQ createTable feature. 

Typical application has a fixed schema or schema that rarely changes. Also when there is a change, schema changes applied during application start.

But my use case is totally different from it. I'm working on a feature where schema of a dataset is detected and corresponding table is created in run-time. Since the schema is generated programatically in runtime, I'm looking for some kind of Java DSL library like jOOQ createTable. Obviously Flyway can be useful here to manage the schema generated by DSL but it doesn't help to generate it. What would you recommend for my use-case? An idea I have is to do some String manipulation on the table statement result generated by jOOQ. 

Thank you for these clarifications. That certainly makes sense.

Well probably, there are other databases that have similar flags at the same syntactic location. Also, we still have an open issue to support inline constraints as well in jOOQ 3.7. I think we can look into this for the next release and make this work for you:

https://github.com/jOOQ/jOOQ/issues/4298 

As usual, thanks Lukas for making things happen. This is my most preferred solution in the future. I'll be happy to test and provide feedback when you decided to implement.

Well, I will be very happy to ask you for feedback :)

Cheers,

Lukas

[Sujeevan Nagarajah]

Hi Lukas,

I tried both VisitListener and custom query parts. The issue is that customised SQL string for a Field is added before its data type definition. 

e.g. "product" distkey sortkey varchar null
It has to be
       "product" varchar null distkey sortkey

I traced this to CreateTableImpl.accept() where Field object is visited and rendered before adding data type and null constraint. It looks like it is not possible to make it work without changing the code.

Is there any better solution? 

Thank you.

[Lukas Eder]

Hi Sujee,

That's what I feared - I didn't think it would be easy to implement this kind of SQL rewriting correctly via VisitListener. So, unfortunately, I don't see any better solution right now, short of using regular expressions to replace the SQL code.

Note that this kind of replacement can be implemented inside of an ExecuteListener:

- http://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-execute-listeners

- http://www.jooq.org/doc/latest/manual/sql-execution/execute-listeners

That might be a way to make reuse a bit more simple.

--

[Sujeevan Nagarajah]

Hi Lukas,

I have realised that Redshift column attributes that I'm interested in can also be defined as Table attributes which is defined at the end of Table definition. This made my work easier. I can now simply append additional String at the end instead of using RegEx to insert in the column level. I'm happy with this solution for now.

        create table "schemaname"."sales"(

                "amount" decimal(19, 3) null,

                "trx_date" date null

        )

        DISTKEY ("trx_date")

        SORTKEY ("trx_date")

Thank you for your support. I learnt a lot about jOOQ while trying out different ideas.

[Lukas Eder]

Hi Sujee,

Excellent, I hadn't thought of the possibility of adding these storage clauses after the table. It makes sense, though. Many constraints / storage clauses can be expressed in 3 ways:

- Directly on the column,

- In a specific constraint / storage clause section of the table,

- In independent alter table statements

We'll keep this in mind when implementing https://github.com/jOOQ/jOOQ/issues/4298.

Should you encounter any further issues, just let us know. We'll be more than happy to help.

Cheers,
Lukas

[Sujeevan Nagarajah]

Noted. Thank you.