Jooq snowflake copy command vendor specific function

Question

Has anyone implemented the COPY command for snowflake in JOOQ using sql templating or some other method? I couldn't find anything out there. I saw a question about it on JOOQ's github page, but it appears there are no current plans to support the COPY command as it is a vendor specific function. If there's nothing out there, perhaps some tips on the best way to support it. There is quite a bit of syntax surrounding it with all the different options.

Answer 1

I think that some middle ground will be optimal for you here to achieve dynamic COPY statement support. Of course, you could implement a full-fledged, jOOQ-style mini-DSL to support COPY. Here's the main idea behind jOOQ's fluent API design. But I think that's probably overkill for the most common use-cases.

Leveraging plain SQL templating with some overloads might be enough. Looking at the Snowflake manual section about COPY, this might be a minimal solution:

public static Query copy(
    DSLContext ctx,
    Table<?> into,
    QueryPart from,
    List<String> files
) {
    return ctx.query("copy into {0} from {1} files = ({2})", 
        table, 
        from,
        DSL.list(files.stream().map(DSL::inline).toList())
    );

Of course, you can make this as versatile and dynamic as you need, providing more overloads, or again, offering some builder API or even a DSL. Check out also CustomQueryPart and other custom syntax elements, for when string templating is not enough.