[DISCUSS] FLINK-31553

[Marios Trivyzas <ma...@gmail.com>]

Hello to all,

I'd like to discuss an issue with flink-jdbc-connector:
https://issues.apache.org/jira/browse/FLINK-31553.
Currently, the Dialect/Catalog/TypeMappers, etc. are chosen automatically
based on the JDBC connection string prefix.
For example, if `jdbc:postgresql://....` is used, then the
PostgresDialect/Catalog,etc. is chosen which can be problematic
for databases like CrateDB or Cochroach DB which they fully support the
PostgreSQL JDBC driver (and therefore use the
`jdbc:postgresql://...` connection strings) but internally they don't
support the same database/schema structure along with
PostgreSQL specific pg_tables, (example pg_cursors, pg_constraints, etc.),
and maybe the need different type mapping.

I propose to add some new parameter to the `JdbcCatalog`, which will allow
the users to select the Dialect, overriding the
automatic url-based decision.

I'd propose to just add another method in `JdbcDialectFactory` which
resolves the dialect before the `acceptsURL` method
which will be the fallback. So if the method is `useDialect(String
dialectName)` which will be used first, and if string is null/empty
or not a matching Dialect is found, the `acceptsURL` is called as a
fallback. I propose that the `dialectName` is something simple
like `CrateDB`, `MySQL`, etc. or another option would be the fully
qualified name of a dialect, which is then loaded by the class loader.
With the latter, users can create their own Dialect which will be loaded
and used, without the need to merge this into the upstream
flink-jdbc-connector repo.

Please let me know what do you think of this proposal, and provide your
suggestions.

Thank you!

-- 
Marios