Table structure

Tables in Gravitino have four configurable structural properties that affect how data is laid out and accessed: partitioning, distribution, sort ordering, and indexing. The sections below cover the syntax for each and how compute engines use them at query time.

Table partitioning

A partitioned table takes a partitioning strategy and the field names it applies to.

• Partitioning strategy. It defines how Gravitino distributes table data across partitions. Gravitino supports the following partitioning strategies.

The score, createTime, and city appearing in the table below refer to the field names in a table.

Partitioning strategy	Description	JSON example	Java example	Equivalent SQL semantics
identity	Source value, unmodified.	{"strategy":"identity","fieldName":["score"]}	Transforms.identity("score")	PARTITION BY score
hour	Extract a timestamp hour, as hours from '1970-01-01 00:00:00'.	{"strategy":"hour","fieldName":["createTime"]}	Transforms.hour("createTime")	PARTITION BY hour(createTime)
day	Extract a date or timestamp day, as days from '1970-01-01'.	{"strategy":"day","fieldName":["createTime"]}	Transforms.day("createTime")	PARTITION BY day(createTime)
month	Extract a date or timestamp month, as months from '1970-01-01'	{"strategy":"month","fieldName":["createTime"]}	Transforms.month("createTime")	PARTITION BY month(createTime)
year	Extract a date or timestamp year, as years from 1970.	{"strategy":"year","fieldName":["createTime"]}	Transforms.year("createTime")	PARTITION BY year(createTime)
bucket[N]	Hash of value, mod N.	{"strategy":"bucket","numBuckets":10,"fieldNames":[["score"]]}	Transforms.bucket(10, "score")	PARTITION BY bucket(10, score)
truncate[W]	Value truncated to width W.	{"strategy":"truncate","width":20,"fieldName":["score"]}	Transforms.truncate(20, "score")	PARTITION BY truncate(20, score)
list	Partition the table by a list value.	{"strategy":"list","fieldNames":[["createTime"],["city"]]}	Transforms.list(new String[] {"createTime", "city"})	PARTITION BY list(createTime, city)
range	Partition the table by a range value.	{"strategy":"range","fieldName":["createTime"]}	Transforms.range("createTime")	PARTITION BY range(createTime)
function	Partition the table by function expression.	{"strategy":"function","funcName":"toYYYYMM","funcArgs":[{"type":"field","fieldName":["VisitDate"]}]}	Transforms.apply("toYYYYMM", new Expression[]{NamedReference.field("VisitDate")})	PARTITION BY toYYYYMM(VisitDate)

For function partitioning, you should provide the function name and the function arguments. The function arguments must be an expression.

• Field names: It defines which fields Gravitino uses to partition the table.

• In some cases, you require other information. For example, if the partitioning strategy is bucket, you should provide the number of buckets; if the partitioning strategy is truncate, you should provide the width of the truncate.

Once a partitioned table exists, its partitions are managed as described in Partitions.

Table distribution

A distributed (bucketed) table takes three components.

Strategy defines how Gravitino distributes table data across buckets.

Distribution strategy	Description	JSON	Java
hash	Distribution table using hash. Gravitino distributes table data into buckets based on the hash value of the key.	hash	Strategy.HASH
range	Distribution table using range. Gravitino distributes table data into buckets based on a specified range or interval of values.	range	Strategy.RANGE
even	Distributes table data evenly across partitions. even implements Doris's random distribution.	even	Strategy.EVEN

Number defines how many buckets the table is distributed across.

funcArgs are the arguments of the strategy, and each must be an expression.

JSON

Hash distribution on score

{
  "strategy": "hash",
  "number": 4,
  "funcArgs": [
    {
      "type": "field",
      "fieldName": ["score"]
    }
  ]
}

Java

Hash distribution on score

Distributions.of(Strategy.HASH, 4, NamedReference.field("score"));

// if you want to use auto distribution, you can use the following code, it will set the number is -1.
// Auto distribution with strategy and fields
Distributions.auto(Strategy.HASH, NamedReference.field("score"));

Sort order

A sort order takes three components.

Direction defines which way Gravitino sorts the table. The default is ascending.

Direction	Description	JSON	Java
ascending	Sorted by a field or a function ascending.	asc	SortDirection.ASCENDING
descending	Sorted by a field or a function descending.	desc	SortDirection.DESCENDING

Null ordering defines where null values sort.

Null ordering Type	Description	JSON	Java
null_first	Puts the null value in the first place.	nulls_first	NullOrdering.NULLS_FIRST
null_last	Puts the null value in the last place.	nulls_last	NullOrdering.NULLS_LAST

With ascending, nulls sort first by default; with descending, they sort last.

sortTerm is the field or function Gravitino sorts the table by, and must be an expression.

JSON

Ascending sort on score with nulls last

 {
  "direction": "asc",
  "nullOrder": "NULLS_LAST",
  "sortTerm":  {
    "type": "field",
    "fieldName": ["score"]
  }
}

Java

Ascending sort on score with nulls last

SortOrders.of(NamedReference.field("score"), SortDirection.ASCENDING, NullOrdering.NULLS_LAST);

Not all catalogs may support those features. Refer to the related document for more details.

REST

Create a partitioned, bucketed and sorted table

curl -sS -X POST "https://{gravitino_host}/api/metalakes/metalake/catalogs/catalog/schemas/schema/tables" \
  -H "Accept: application/vnd.gravitino.v1+json" \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $TOKEN" \
  -d '{
  "name": "table",
  "columns": [
    {
      "name": "id",
      "type": "integer",
      "nullable": true,
      "comment": "Id of the user"
    },
    {
      "name": "name",
      "type": "varchar(2000)",
      "nullable": true,
      "comment": "Name of the user"
    },
    {
      "name": "age",
      "type": "short",
      "nullable": true,
      "comment": "Age of the user"
    },
    {
      "name": "score",
      "type": "double",
      "nullable": true,
      "comment": "Score of the user"
    }
  ],
  "comment": "Create a new Table",
  "properties": {
    "format": "ORC"
  },
  "partitioning": [
    {
      "strategy": "identity",
      "fieldName": ["score"]
    }
  ],
  "distribution": {
    "strategy": "hash",
    "number": 4,
    "funcArgs": [
      {
        "type": "field",
        "fieldName": ["score"]
      }
    ]
  },
  "sortOrders": [
    {
      "direction": "asc",
      "nullOrder": "NULLS_LAST",
      "sortTerm":  {
        "type": "field",
        "fieldName": ["name"]
      }
    }
  ]
}'

Java

Create a partitioned, bucketed and sorted table

tableCatalog.createTable(
    NameIdentifier.of("schema", "table"),
    new Column[] {
      Column.of("id", Types.IntegerType.get(), "Id of the user", true, false, null),
      Column.of("name", Types.VarCharType.of(2000), "Name of the user", true, false, null),
      Column.of("age", Types.ShortType.get(), "Age of the user", true, false, null),
      Column.of("score", Types.DoubleType.get(), "Score of the user", false, false, null)
    },
    "Create a new Table",
    tablePropertiesMap,
    new Transform[] {
    // PARTITIONED BY score
      Transforms.identity("score")
    },
    // CLUSTERED BY id
    Distributions.of(Strategy.HASH, 4, NamedReference.field("id")),
    // SORTED BY age ASC NULLS LAST
    new SortOrder[] {
      SortOrders.of(
        NamedReference.field("age"), SortDirection.ASCENDING, NullOrdering.NULLS_LAST),
    });

Indexes

An index takes four components.

IndexType is the type of index, a primary key or a unique key.

IndexType	Description	JSON	Java
PRIMARY_KEY	The PRIMARY KEY is a column or set of columns that uniquely identifies each row in a table. It enforces uniqueness and ensures that no two rows have the same values in the specified columns. Additionally, the PRIMARY KEY constraint automatically creates a unique index on the specified columns.	PRIMARY_KEY	IndexType.PRIMARY_KEY
UNIQUE_KEY	The UNIQUE KEY constraint ensures that all values in a specified column or set of columns are unique across the entire table. Unlike the PRIMARY KEY constraint, a table can have multiple UNIQUE KEY constraints, allowing for unique values in multiple columns or sets of columns.	UNIQUE_KEY	IndexType.UNIQUE_KEY

Name is the name of the index.

FieldNames are the table fields the index covers.

Properties are optional extra index settings, such as granularity for ClickHouse data-skipping indexes. Omitted, they default to an empty map.

JSON

Primary key on col_1 and col_2

 {
  "indexType": "PRIMARY_KEY",
  "name": "PRIMARY",
  "fieldNames": [["col_1"],["col_2"]],
  "properties": {}
}

Java

Primary key on col_1 and col_2

Indexes.of(IndexType.PRIMARY_KEY, "PRIMARY", new String[][]{{"col_1"}, {"col_2"}}, Map.of());

REST

Create a table with an index

curl -sS -X POST "https://{gravitino_host}/api/metalakes/metalake/catalogs/catalog/schemas/schema/tables" \
  -H "Accept: application/vnd.gravitino.v1+json" \
  -H "Content-Type: application/json" \
  -H "Authorization: Bearer $TOKEN" \
  -d '{
  "name": "table",
  "columns": [
    {
      "name": "id",
      "type": "integer",
      "nullable": true,
      "comment": "Id of the user"
    },
    {
      "name": "name",
      "type": "varchar(2000)",
      "nullable": true,
      "comment": "Name of the user"
    },
    {
      "name": "age",
      "type": "short",
      "nullable": true,
      "comment": "Age of the user"
    },
    {
      "name": "score",
      "type": "double",
      "nullable": true,
      "comment": "Score of the user"
    }
  ],
  "comment": "Create a new Table",
  "indexes": [
    {
      "indexType": "PRIMARY_KEY",
      "name": "PRIMARY",
      "fieldNames": [["id"]],
      "properties": {}
    },
    {
      "indexType": "UNIQUE_KEY",
      "name": "name_age_score_uk",
      "fieldNames": [["name"],["age"],["score"]],
      "properties": {}
    }
  ]
}'

Java

Create a table with an index

tableCatalog.createTable(
    NameIdentifier.of("schema", "table"),
    new Column[] {
      Column.of("id", Types.IntegerType.get(), "Id of the user", false, true, null),
      Column.of("name", Types.VarCharType.of(1000), "Name of the user", true, false, null),
      Column.of("age", Types.ShortType.get(), "Age of the user", true, false, null),
      Column.of("score", Types.DoubleType.get(), "Score of the user", true, false, null)
    },
    "Create a new Table",
    tablePropertiesMap,
    Transforms.EMPTY_TRANSFORM,
    Distributions.NONE,
    new SortOrder[0],
    new Index[] {
      Indexes.of(IndexType.PRIMARY_KEY, "PRIMARY", new String[][]{{"id"}}, Map.of()),
      Indexes.of(IndexType.UNIQUE_KEY, "name_age_score_uk", new String[][]{{"name"}, {"age"}, {"score"}}, Map.of())
    });