Hologres catalog

The Hologres catalog connects Gravitino to an Alibaba Cloud Hologres instance over the PostgreSQL protocol, exposing the schemas of one Hologres database as schemas and their tables as tables. Schemas and tables are both read-write. The database is named in the catalog properties, so reaching a second database means creating a second catalog.

The catalog uses the PostgreSQL JDBC driver, which ships in the image, so there is no driver to supply.

Gravitino records its own identifier in the comment of each schema and table it creates, as text of the form (From Gravitino, DO NOT EDIT: gravitino.v1.uid{id}), and uses it to recognize the object on later loads. Leave that text in place when you edit a comment in Hologres directly.

Quick start

The example connects the Hologres database {database} as the catalog hologres_sales and lists its schemas.

UI

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

2. Enter the connection. Under Connection, enter jdbc:postgresql://{hologres_endpoint}:80/{database} as the URL and the same database name as the Database. Enter the AccessKey ID or database user as the User and its AccessKey Secret or password as the Password. Advanced holds Cloud / region and a Properties list for any other catalog property. The UI fills in jdbc-driver itself.

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

4. List the schemas. In Explore, open hologres_sales. The schemas listed are read from the database, with the system schemas filtered out.

REST

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

   Create a Hologres 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": "hologres_sales",
       "type": "RELATIONAL",
       "provider": "jdbc-hologres",
       "comment": "Hologres sales database",
       "properties": {
         "jdbc-url": "jdbc:postgresql://{hologres_endpoint}:80/{database}",
         "jdbc-driver": "org.postgresql.Driver",
         "jdbc-database": "{database}",
         "jdbc-user": "{access_key_id}",
         "jdbc-password": "{access_key_secret}"
       }
     }'

2. List the schemas. They are read from the database, with the system schemas filtered out.

   List the schemas in the catalog

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

Catalog properties

The Hologres catalog defines no catalog properties of its own; it takes the connection, pool, and driver properties every JDBC catalog shares, listed in Shared properties.

jdbc-database is required here, although it is optional in the shared set, and catalog creation fails without it. Set it to the database named in jdbc-url. jdbc-driver is org.postgresql.Driver, and jdbc-user and jdbc-password take either an Alibaba Cloud AccessKey pair or a database user and password.

Schemas

A schema in this catalog is a Hologres schema in the database named by jdbc-database. Create, list, load, and drop are supported; alter is not.

Creation issues CREATE SCHEMA followed by COMMENT ON SCHEMA when a comment is given. Schema properties are rejected. The names pg_catalog, information_schema, and hologres are reserved, in any case, and cannot be used for a new schema.

Listing filters out the PostgreSQL system schemas pg_toast, pg_catalog, and information_schema, and the Hologres internal schemas hologres, hg_internal, hg_recyclebin, hologres_object_table, hologres_sample, hologres_streaming_mv, and hologres_statistic.

A cascading drop issues DROP SCHEMA ... CASCADE, which drops every table in the schema. Without cascade, dropping a schema that still holds objects fails with the Hologres error.

Tables

Table create, load, list, rename, alter, and drop are supported. Purge is not, and a purge request is rejected rather than being treated as a drop.

Listing returns ordinary tables, partitioned parent tables, and partition child tables. Views and foreign tables are not listed.

Table and column comments are written with COMMENT ON TABLE and COMMENT ON COLUMN after the CREATE TABLE statement.

Properties

Hologres table properties are written to the WITH clause of CREATE TABLE. A property key must be a plain identifier, matching [a-zA-Z_][a-zA-Z0-9_]*, and its value is written as a quoted string. Three keys are not written to WITH: distribution_key and primary_key, which come from the table's distribution and indexes, and is_logical_partitioned_table, which selects logical partitioning as described in Partitioning and distribution.

A load reads properties from hologres.hg_table_properties and returns only these keys, when they have a value:

Property	Description
orientation	Storage orientation, such as column, row, or row,column
clustering_key	Clustering key columns
segment_key	Event time column
bitmap_columns	Bitmap index columns
dictionary_encoding_columns	Dictionary encoding columns
time_to_live_in_seconds	Data time to live
table_group	Table group name
storage_format	Internal storage format
binlog_level	Binlog level
binlog_ttl	Binlog time to live
is_logical_partitioned_table	Whether the table is a logical partitioned table
partition_expiration_time	Logical partition expiration
partition_keep_hot_window	Logical partition hot window
partition_require_filter	Whether queries must filter on the partition key
partition_generate_binlog_window	Logical partition binlog window

Hologres stores the binlog properties as binlog.level and binlog.ttl; Gravitino returns them as binlog_level and binlog_ttl, the names CREATE TABLE accepts.

Table properties cannot be set or removed after creation.

Indexes

PRIMARY_KEY is the only supported index type, and only at table creation. It can cover several columns, each a top-level column. Any other index type is rejected, and adding or deleting an index through an alter is rejected.

Define a primary key

{
  "indexes": [
    {"indexType": "PRIMARY_KEY", "name": "pk_id", "fieldNames": [["id"]]}
  ]
}

Partitioning and distribution

A table takes at most one partitioning transform, and it must be list.

Table kind	How it is chosen	Clause	Columns
Physical partition	Default	PARTITION BY LIST({column})	Exactly 1
Logical partition	Table property is_logical_partitioned_table set to true	LOGICAL PARTITION BY LIST({columns})	1 or 2

A load reads logical partitioning from hologres.hg_table_properties and physical partitioning from pg_partitioned_table. Partition child tables are not created through Gravitino.

Set list partitioning

{
  "partitioning": [
    {"strategy": "list", "fieldNames": [["ds"]]}
  ]
}

The distribution is optional and must be hash with at least one column, each a plain column reference. It is written as the distribution_key table property. The bucket number is not used, and a load returns the distribution with a bucket number of 0.

Set hash distribution

{
  "distribution": {
    "strategy": "hash",
    "number": 0,
    "funcArgs": [{"type": "field", "fieldName": ["id"]}]
  }
}

Sort orders are not supported.

Auto increment

Auto-increment columns are not supported, and a create or alter that sets one is rejected.

Supported changes

Change	Supported
Rename table	Yes, alone in its request
Update table comment	Yes
Add column	Yes, nullable, without a default, at the default position, with a comment
Delete column	Yes
Rename column	Yes
Update column comment	Yes
Update column type	No
Update column position	No
Update column nullable	No
Update column default	No
Update column auto increment	No
Add or delete index	No
Set or remove property	No

Deleting a column first issues SET hg_experimental_enable_drop_column = on, which Hologres requires before DROP COLUMN. Nested column names are rejected by every column change.

Identifier case and quoting

Names are case-sensitive in Gravitino and are passed to Hologres exactly as given, never folded. Schema, table, and column names must match ^[_a-zA-Z\p{L}/][\w\p{L}-$/=]{0,62}$: 1 to 63 characters, starting with a letter in any script, an underscore, or /, and continuing with letters, digits, underscore, -, $, /, and =.

Gravitino quotes identifiers with double quotes in generated SQL, which keeps their case in Hologres, and doubles any " inside a table or column name. Names returned by a list operation come back exactly as Hologres stores them.

Type mapping

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

Hologres type	Gravitino type
bool	Boolean
int2	Short
int4	Integer
int8	Long
float4	Float
float8	Double
numeric(p,s)	Decimal(p, s)
date	Date
time(p)	Time, precision p
timestamp(p)	Timestamp without time zone, precision p
timestamptz(p)	Timestamp with time zone, precision p
varchar(n)	Varchar(n)
varchar with no length, text	String
bpchar(n)	Char(n)
bytea	Binary
An array of a type, such as int4[]	List of that type, with non-nullable elements
Any other type	External type carrying the Hologres type name

Types such as json, jsonb, uuid, inet, money, and roaringbitmap come back as external types.

When Gravitino creates a column:

• Time without a precision becomes time.
• Both timestamp types become timestamp and timestamptz with no precision, because Hologres does not accept one.
• String becomes text.
• A list must have non-nullable elements and one dimension; a nullable element type or a list of lists is rejected.
• An external type becomes the Hologres type it carries.
• Any other Gravitino type is rejected, including Byte, Fixed, UUID, the interval types, and map, struct, and union types.

Column default values

On load, a literal default written with a cast, such as '2021-10-01 00:00:00'::timestamp, has the cast removed, and NULL::{type} is read as null. Literals on boolean, integer, float, numeric, date, time, timestamp, varchar, bpchar, and text columns are parsed into typed literals. A default that fails to parse is read as the current timestamp when it is CURRENT_TIMESTAMP and as an unparsed expression otherwise, and an expression default is preserved as an unparsed expression.

On create, an unparsed expression is written exactly as given. A function default is written inside parentheses, except CURRENT_TIMESTAMP.

Error mapping

Hologres reports PostgreSQL SQLSTATE codes, which Gravitino translates into Gravitino exceptions so a caller sees the same failure types it would from any other catalog.

SQLSTATE	Gravitino exception
42P04 duplicate database, 42P06 duplicate schema	Schema already exists
42P07 duplicate table	Table already exists
3D000 invalid catalog name, 3F000 invalid schema name	No such schema
42P01 undefined table	No such table
Any code in class 08, connection exception	Connection failed
No SQLSTATE, with "password authentication failed" in the message	Connection failed

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

Related

• JDBC catalogs
• Catalogs and schemas
• Tables and views
• Table structure