Support Cockroach DB as Metastore

[sathesuraj]

Is your feature request related to a problem? Please describe.

Currently, Polaris does not support CockroachDB. It only supports PostgreSQL, even though CockroachDB is highly compatible with PostgreSQL.

Describe the solution you'd like

CockroachDB uses INT8 (64-bit signed integer) as the default storage type for INT, INTEGER, and BIGINT data types. It does not support a 32-bit integer type (INT4) as a separate storage type. This design simplifies type handling and avoids integer overflow issues.

In Polaris, a JDBC connection is used to create metastore tables by executing SQL files. These files define types as INT and BIGINT. During the bootstrap process, when Polaris attempts to read INT columns from CockroachDB as the Integer Java datatype, it fails because CockroachDB stores these as INT8, which should be read as Long in Java.

One option would be to update sql scripts to use INT4 and INT8 instead of INT, it should work with both PostgreSQL and Cockroach DB.

Describe alternatives you've considered

No response

Additional context

No response

[flyrain]

Hi @sathesuraj, it's great idea to support Cockroach. Updating sql scripts itself only solve the part of the problem. The Polaris JDBC impl. still expects a 4 bytes integer. We will need to fix these as well. cc @singhpk234

[singhpk234]

The Polaris JDBC impl. still expects a 4 bytes integer. We will need to fix these as wel

precisely, have you tried this out with E2E tests (can try enabling it via overriding the profile here) if there are additional gotchas

[sathesuraj]

@flyrain / @singhpk234 - I haven't tried using E2E tests yet.

In CockroachDB, executing the schema SQL results in all INT types being created and stored as INT8. However, if you explicitly specify the type as INT4 in the SQL, the column is saved with the type alias as INT4, though it is still stored internally as INT8.

To make it work with CockroachDB, I explicitly specified the type as INT4 in the SQL files and executed them before running the bootstrap process. This created an alias for those columns as INT4, even though they are internally stored as INT8. This approach allows the columns to be read as Integer in Java, enabling the use of CockroachDB as a metastore for Polaris.

For example, the types were updated for columns in the "entities" table were - entity_version, type_code, sub_type_code, and grant_record_version.

No code or config change was required.

[flyrain]

Hi @sathesuraj, that sounds good to me. Looking forward to your PR.