Supported Column Types

Viewing:

Supported Column Types

Moose supports a comprehensive set of ClickHouse column types across both TypeScript and Python libraries. This guide covers all supported types, their syntax, and best practices for defining table schemas.

Basic Types

String Types

interface User {
  string: string;                             // String
  lowCardinality: string & LowCardinality;    // LowCardinality(String)
  uuid: string & tags.Format<"uuid">;         // UUID (with typia tags)
}

ClickHouse Type	TypeScript	Description
`String`	`string`	Variable-length string
`LowCardinality(String)`	`string & LowCardinality`	Optimized for repeated values
`UUID`	`string & tags.Format<"uuid">`	UUID format strings

from typing import Literal
from uuid import UUID
 
class User(BaseModel):
    string: str                                         # String
    low_cardinality: Annotated[str, "LowCardinality"]   # LowCardinality(String)
    uuid: UUID                                          # UUID

ClickHouse Type	Python	Description
`String`	`str`	Variable-length string
`LowCardinality(String)`	`str` with `Literal[str]`	Optimized for repeated values
`UUID`	`UUID`	UUID format strings

Numeric Types

Integer Types

import { ClickHouseInt } from "@514labs/moose-lib";
 
interface Metrics {
  user_id: number & ClickHouseInt<"int32">;
  count: number & ClickHouseInt<"uint64">;
  small_value: number & ClickHouseInt<"int8">;
}

ClickHouse Type	TypeScript	Description
`Int8`	`number & ClickHouseInt<"int8">`	-128 to 127
`Int16`	`number & ClickHouseInt<"int16">`	-32,768 to 32,767
`Int32`	`number & ClickHouseInt<"int32">`	-2,147,483,648 to 2,147,483,647
`Int64`	`number & ClickHouseInt<"int64">`	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
`UInt8`	`number & ClickHouseInt<"uint8">`	0 to 255
`UInt16`	`number & ClickHouseInt<"uint16">`	0 to 65,535
`UInt32`	`number & ClickHouseInt<"uint32">`	0 to 4,294,967,295
`UInt64`	`number & ClickHouseInt<"uint64">`	0 to 18,446,744,073,709,551,615

from typing import Annotated
 
class Metrics(BaseModel):
    user_id: Annotated[int, "int32"]          # Int32
    count: Annotated[int, "int64"]            # Int64
    small_value: Annotated[int, "uint8"]       # UInt8

ClickHouse Type	Python	Description
`Int8`	`Annotated[int, "int8"]`	-128 to 127
`Int16`	`Annotated[int, "int16"]`	-32,768 to 32,767
`Int32`	`Annotated[int, "int32"]`	-2,147,483,648 to 2,147,483,647
`Int64`	`Annotated[int, "int64"]`	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
`UInt8`	`Annotated[int, "uint8"]`	0 to 255
`UInt16`	`Annotated[int, "uint16"]`	0 to 65,535
`UInt32`	`Annotated[int, "uint32"]`	0 to 4,294,967,295
`UInt64`	`Annotated[int, "uint64"]`	0 to 18,446,744,073,709,551,615

Floating Point Types

interface SensorData {
  temperature: number & tags.Type<"float">; // Float32
  humidity: number;     // Float64
}

ClickHouse Type	TypeScript	Description
`Float64`	`number`	floating point number

from moose_lib import ClickhouseSize
 
class SensorData(BaseModel):
    temperature: float                                    # Float64
    humidity: Annotated[float, ClickhouseSize(4)]       # Float32

ClickHouse Type	Python	Description
`Float64`	`float`	floating point number

Decimal Types

import { ClickHouseDecimal } from "@514labs/moose-lib";
 
interface FinancialData {
  amount: string & ClickHouseDecimal<10, 2>;  // Decimal(10,2)
  rate: string & ClickHouseDecimal<5, 4>;     // Decimal(5,4)
}

ClickHouse Type	TypeScript	Description
`Decimal(P,S)`	`string & ClickHouseDecimal<P,S>`	Fixed-point decimal

from moose_lib import clickhouse_decimal
 
class FinancialData(BaseModel):
    amount: clickhouse_decimal(10, 2)  # Decimal(10,2)
    rate: clickhouse_decimal(5, 4)     # Decimal(5,4)

ClickHouse Type	Python	Description
`Decimal(P,S)`	`clickhouse_decimal(P,S)`	Fixed-point decimal

Boolean Type

interface User {
  is_active: boolean;
  verified: boolean;
}

ClickHouse Type	TypeScript	Description
`Boolean`	`boolean`	`boolean`

ClickHouse Type	Python	Description
`Boolean`	`bool`	`bool`

Date and Time Types

import { ClickHousePrecision } from "@514labs/moose-lib";
 
interface Event {
  created_at: Date;                                    // DateTime
  updated_at: Date & ClickHousePrecision<3>;          // DateTime(3)
  birth_date: Date;                                    // Date
}

ClickHouse Type	TypeScript	Description
`Date`	`Date`	Date only
`Date16`	`Date`	Compact date format
`DateTime`	`Date`	Date and time
`DateTime(P)`	`Date & ClickHousePrecision<P>`	DateTime with precision

from datetime import date, datetime
from moose_lib import ClickhouseSize, clickhouse_datetime64
 
class Event(BaseModel):
    created_at: datetime                               # DateTime
    updated_at: clickhouse_datetime64(3)              # DateTime(3)
    birth_date: date                                   # Date
    compact_date: Annotated[date, ClickhouseSize(2)]  # Date16

ClickHouse Type	Python	Description
`Date`	`date`	Date only
`Date16`	`date`	`Annotated[date, ClickhouseSize(2)]`
`DateTime`	`datetime`	Date and time

Network Types

import { tags } from "typia";
 
interface NetworkEvent {
  source_ip: string & tags.Format<"ipv4">;
  dest_ip: string & tags.Format<"ipv6">;
}

ClickHouse Type	TypeScript	Description
`IPv4`	`string & tags.Format<"ipv4">`	IPv4 addresses
`IPv6`	`string & tags.Format<"ipv6">`	IPv6 addresses

from ipaddress import IPv4Address, IPv6Address
 
class NetworkEvent(BaseModel):
    source_ip: IPv4Address
    dest_ip: IPv6Address

ClickHouse Type	Python	Description
`IPv4`	`ipaddress.IPv4Address`	IPv4 addresses
`IPv6`	`ipaddress.IPv6Address`	IPv6 addresses

Complex Types

Array Types

Arrays are supported for all basic types and some complex types.

interface User {
  tags: string[];           // Array(String)
  scores: number[];         // Array(Float64)
  metadata: Record<string, any>[];  // Array(Json)
  tuple: {
    name: string;
    age: number;
  } & ClickHouseNamedTuple[]; // Array(Tuple(String, Int32))
}

from typing import List, Dict, Any
 
class User(BaseModel):
    tags: List[str]                    # Array(String)
    scores: List[float]                # Array(Float64)
    metadata: List[Dict[str, Any]]     # Array(Json)
    tuple: List[Tuple[str, int]]       # Array(Tuple(String, Int32))

Map Types

Maps store key-value pairs with specified key and value types.

interface User {
  preferences: Record<string, string>;     // Map(String, String)
  metrics: Record<string, number>;         // Map(String, Float64)
}

Nested Types

Nested types allow embedding complex objects within tables.

interface Address {
  street: string;
  city: string;
  zip: string;
}
 
interface User {
  name: string;
  address: Address;  // Nested type
}

Named Tuple Types

Named tuples provide structured data with named fields.

import { ClickHouseNamedTuple } from "@514labs/moose-lib";
 
interface Point {
  x: number;
  y: number;
}
 
interface Shape {
  center: Point & ClickHouseNamedTuple;  // Named tuple
  radius: number;
}

Enum Types

Enums map to ClickHouse enums with string or integer values.

enum UserRole {
  ADMIN = "admin",
  USER = "user",
  GUEST = "guest"
}
 
interface User {
  role: UserRole;  // Enum with string values
}

Special Types

JSON Type

The Json type stores arbitrary JSON data.

interface Event {
  metadata: Record<string, any>;  // Json
  config: any;                    // Json
}

Nullable Types

All types support nullable variants using optional types.

interface User {
  name: string;           // Required
  email?: string;         // Nullable
  age?: number;           // Nullable
}

Table Engines

Moose supports all common ClickHouse table engines:

Engine	Python	Description
`MergeTree`	`ClickHouseEngines.MergeTree`	Default engine
`ReplacingMergeTree`	`ClickHouseEngines.ReplacingMergeTree`	Deduplication
`SummingMergeTree`	`ClickHouseEngines.SummingMergeTree`	Aggregates numeric columns
`AggregatingMergeTree`	`ClickHouseEngines.AggregatingMergeTree`	Advanced aggregation

import { ClickHouseEngines } from "@514labs/moose-lib";
 
const userTable = new OlapTable<User>("users", {
  engine: ClickHouseEngines.ReplacingMergeTree,
  orderByFields: ["id", "updated_at"]
});

Best Practices

Type Selection

Use specific integer types when you know the value ranges to save storage
Prefer Float64 for most floating-point calculations unless storage is critical
Use LowCardinality for string columns with repeated values
Choose appropriate DateTime precision based on your accuracy needs

Performance Considerations

Order columns by cardinality (low to high) for better compression
Use ReplacingMergeTree for tables with frequent updates
Specify orderByFields for optimal query performance
Consider LowCardinality for string columns with < 10,000 unique values

Modeling Materialized Views

Schema Optimization