OceanBase catalog

The OceanBase catalog connects Gravitino to OceanBase over JDBC. It issues MySQL-compatible SQL, so it works with an OceanBase tenant in MySQL mode: each database is a schema, and each table in it is a table. Schemas and tables are both read-write.

The catalog connects through MySQL Connector/J, the same driver as the MySQL catalog, and much of its behavior matches the MySQL catalog. Each section below states where it differs.

Quick start

The example connects an OceanBase tenant as the catalog oceanbase_catalog and lists its databases as schemas. MySQL Connector/J does not ship in the image, so enable it in the chart first with gravitino.jdbcDrivers.enabled and gravitino.jdbcDrivers.mysql.enabled, as described in JDBC driver.

UI

1. Start the catalog. Open Connect → Catalogs and click + Catalog. Enter oceanbase_catalog as the Catalog name, keep Relational as the Catalog type, and choose OceanBase under Databases and warehouses in Select provider.

2. Enter the connection. Under Connection, enter jdbc:mysql://{oceanbase_host}:2881 as the URL, and the User and Password Gravitino connects as. The UI fills in com.mysql.cj.jdbc.Driver as jdbc-driver, which does not accept a jdbc:oceanbase: URL, so use the jdbc:mysql: form rather than the one the field's placeholder shows.

3. Test and create it. Click Test connection, then Create catalog.

4. List the schemas. In Explore, open oceanbase_catalog. The schemas listed are the OceanBase databases, read from the server, with the system databases filtered out.

REST

1. Create the catalog. The public REST endpoint does not fill in jdbc-driver, so the request names it.

   Create an OceanBase catalog

   curl -sS -X POST "https://{gravitino_host}/api/metalakes/{metalake}/catalogs" \
     -H "Accept: application/vnd.gravitino.v1+json" \
     -H "Content-Type: application/json" \
     -H "Authorization: Bearer $TOKEN" \
     -d '{
       "name": "oceanbase_catalog",
       "type": "RELATIONAL",
       "provider": "jdbc-oceanbase",
       "comment": "OceanBase catalog",
       "properties": {
         "jdbc-url": "jdbc:mysql://{oceanbase_host}:2881",
         "jdbc-user": "{oceanbase_user}",
         "jdbc-password": "{oceanbase_password}",
         "jdbc-driver": "com.mysql.cj.jdbc.Driver"
       }
     }'

2. List the schemas. They are the OceanBase databases, read from the server, with the system databases filtered out.

   List the schemas in the catalog

   curl -sS "https://{gravitino_host}/api/metalakes/{metalake}/catalogs/oceanbase_catalog/schemas" \
     -H "Accept: application/vnd.gravitino.v1+json" \
     -H "Authorization: Bearer $TOKEN"

Catalog properties

The OceanBase catalog defines no properties of its own. It takes the jdbc-url, jdbc-driver, jdbc-user, jdbc-password, and pool properties described in Shared properties.

Use a jdbc:mysql:// URL with com.mysql.cj.jdbc.Driver. The connection check that runs when the catalog is created looks up its driver from a jdbc:mysql: URL, so with MySQL Connector/J on the classpath a jdbc:oceanbase: URL fails that check.

Driver version compatibility

MySQL Connector/J releases before 8.0.16 do not report datetime precision reliably. With a driver whose version string starts with mysql-connector-java- and is below 8.0.16, Gravitino reads TIME, TIMESTAMP, and DATETIME columns without a precision and logs a warning for each one rather than returning a wrong precision. The chart supplies Connector/J 8.0.33.

Unlike the MySQL catalog, the OceanBase catalog does not reject a Connector/J release older than 8.0, so the precision limit is the only driver check it applies.

Example warning log

WARN: MySQL driver version mysql-connector-java-8.0.11 is below 8.0.16,
columnSize may not be accurate for precision calculation.
Returning null for TIMESTAMP type precision. Driver version: mysql-connector-java-8.0.11

Schemas

A schema in this catalog is an OceanBase database. Create, list, load, and drop are supported; alter is not. Creation issues CREATE DATABASE, and a schema comment or any schema property is rejected.

Drop without cascade fails when the database still holds tables. Drop with cascade issues DROP DATABASE, which removes the database and every table in it.

Listing filters out information_schema, mysql, sys, and oceanbase. Unlike the MySQL catalog, creation does not reserve those names.

Tables

Table create, load, list, rename, alter, drop, and purge are supported. Listing returns base tables only, not views.

Purge issues TRUNCATE TABLE. It deletes every row and leaves the empty table in place, unlike a purge in catalogs that remove the table.

An alter that carries several changes is executed as one ALTER TABLE statement per change, in sequence, so a failure partway through leaves the earlier changes applied.

Partitioning, distribution, and sort orders are not supported, and a create that carries any of them is rejected. Table and column comments are written as COMMENT clauses.

Gravitino appends an identifier to the comment of each table it creates, in the form (From Gravitino, DO NOT EDIT: gravitino.v1.uid{id}). The UI and the API strip it from the comment they show. Leave it in place when you edit a table comment directly in OceanBase.

Properties

The catalog declares no table properties and passes the properties you supply straight through. A property given at creation is written into CREATE TABLE as a {key} = {value} table option, and a property set on an existing table is written into ALTER TABLE the same way, with no validation by Gravitino. Removing a property is rejected.

A loaded table reports COMMENT and, when OceanBase holds a value for it, AUTO_INCREMENT, both read from SHOW TABLE STATUS.

Indexes

Primary key and unique key are supported, at creation and through an alter, and both are read back from OceanBase. A primary key must be named PRIMARY or left unnamed; any other name is rejected. An index on a nested field is rejected.

Define primary and unique keys

{
  "indexes": [
    {
      "indexType": "primary_key",
      "name": "PRIMARY",
      "fieldNames": [["id"]]
    },
    {
      "indexType": "unique_key",
      "name": "id_name_uk",
      "fieldNames": [["id"], ["name"]]
    }
  ]
}

Deleting an index issues DROP INDEX. A delete without the if-exists flag fails when the table has no index of that name.

Auto increment

A table can have one auto-increment column, and it must belong to the primary key or to a unique key, or creation is rejected. Adding an auto-increment column or turning auto increment on through an alter requires an Integer or Long column.

Define an auto-increment column

{
  "columns": [
    {
      "name": "id",
      "type": "integer",
      "comment": "id column comment",
      "nullable": false,
      "autoIncrement": true
    },
    {
      "name": "name",
      "type": "varchar(500)",
      "comment": "name column comment",
      "nullable": true,
      "autoIncrement": false
    }
  ],
  "indexes": [
    {
      "indexType": "primary_key",
      "name": "PRIMARY",
      "fieldNames": [["id"]]
    }
  ]
}

Supported changes

Change	Supported
Rename table	Yes, as the only change in its request
Update table comment	Yes
Add column	Yes, first, after a named column, or at the default position
Delete column	Yes
Rename column	Yes
Update column type	Yes, and the column's default is dropped
Update column nullable	Yes
Update column default	Yes
Update column comment	Yes
Update column position	Yes
Update column auto increment	Yes, and turning it on needs an Integer or Long column
Add or delete index	Yes, for primary key and unique key
Set property	Yes, written as a table option without validation
Remove property	No

Updating a column's type redefines the column without a default, so set the default again in a separate request afterward.

Changing a column from nullable to not null is rejected when its default is NULL. A nullable column created without a default reads back with a NULL default, so give it a non-null default first.

Nested column names are rejected by every column change.

Identifier case and quoting

Gravitino does not fold the case of OceanBase names. A name is sent as given, quoted with backticks, and a table or column is matched by its exact name when it is loaded or altered.

Schema, table, and column names are 1 to 64 characters of letters from any script, digits, underscores, hyphens, $, /, or =. A backtick cannot appear in a name, so no escaping is needed inside the quotes.

Type mapping

Gravitino reads each OceanBase type as the Gravitino type in its row, and creates each Gravitino type as the OceanBase type in its row, except where the list under the table says otherwise.

OceanBase type	Gravitino type
tinyint, tinyint unsigned	Byte, unsigned Byte
smallint, smallint unsigned	Short, unsigned Short
int, int unsigned	Integer, unsigned Integer
bigint, bigint unsigned	Long, unsigned Long
float	Float
double	Double
decimal(p,s), numeric, number	Decimal(p, s)
date	Date
time(p)	Time, precision p
timestamp(p)	Timestamp with time zone, precision p
datetime(p)	Timestamp without time zone, precision p
char(n)	Char(n)
varchar(n)	Varchar(n)
text	String
binary(n)	Binary
Any other type	External type carrying the OceanBase type name

A binary(n) column reads back as Binary without its length. Unlike the MySQL catalog, the OceanBase catalog does not read the full definition of binary(n), varbinary(n), enum, or set columns, so varbinary, enum, and set read back as external types carrying the type name alone. No OceanBase type reads back as Boolean.

When Gravitino creates a column:

• Boolean becomes boolean.
• Decimal(p, s) becomes decimal(p,s).
• Time and both timestamp types without a precision become time, timestamp and datetime.
• Binary becomes binary with no length.
• An external type becomes the OceanBase type it carries, so any OceanBase type can be created by name.
• Fixed, UUID, List, Map, Struct, Union and the interval types have no mapping and are rejected.

Column default values

CURRENT_TIMESTAMP, with or without a fractional-second precision such as CURRENT_TIMESTAMP(3), is read back as the current timestamp default. NULL is read back as a null literal. A default on a numeric, decimal, date, time, timestamp, datetime, char, varchar, or text column is read back as a literal of the matching type, and a default on any other type is preserved as an unparsed expression.

When Gravitino writes a default, the current timestamp is written as CURRENT_TIMESTAMP, any other function expression is written inside parentheses, numeric literals are written bare, and all other literals are written in single quotes.

Error mapping

The OceanBase catalog translates errors by their MySQL error codes, the same way as the MySQL catalog, so that a caller sees the same failure types it would from any other catalog.

MySQL error code	Gravitino exception
1007 database exists	Schema already exists
1008 cannot drop database, it does not exist	No such schema
1049 unknown database	No such schema
1050 table already exists	Table already exists
1051 unknown table	No such table
1146 table does not exist	No such table
Any error whose message contains Access denied	Connection failed

Any other error code produces a generic runtime exception carrying the OceanBase message.

Related

• JDBC catalogs
• MySQL catalog
• Catalog connections
• Tables and views