Hologres Schema Generator — Ho记录res 模式 生成器

Prerequisites

This 技能 requires ho记录res-命令行工具 to be 安装ed first:

pip 安装 ho记录res-命令行工具 导出 HO记录RES_技能=ho记录res-模式-生成器

All SQL execution depends on ho记录res-命令行工具 commands (ho记录res sql 运行 --write, ho记录res table 创建).

Ho记录res 模式 De签名 & Table Creation

生成 优化d Ho记录res DDL 状态ments with proper storage 格式化, 索引ing, and distribution strategies.

In格式化ion Gathering (导入ANT)

Before generating DDL, 检查 whether the user has provided enough 上下文. Many Ho记录res table properties are immutable after creation (orientation, distribution_key, clustering_key, event_time_column, primary key, partition column) — 获取ting them wrong means recreating the table.

Required In格式化ion

If ANY of the following are unclear, ask the user before generating DDL:

Dimension Why Critical Example Question 查询 pattern Determines orientation (column/row/row-column) "This table is mAInly used for OLAP 分析, KV point lookups, or 机器人h?" Column definitions Core 模式 structure "Can you 列出 the mAIn columns and their rough types (text, number, timestamp, etc.)?" Primary key / unique key Determines UPSERT capability; immutable "Does this table need deduplication or 更新s by a unique key? If so, which column(s)?" MAIn 查询 conditions Determines distribution_key, clustering_key, bitmap "What columns are most often used in WHERE, JOIN, or GROUP BY?" Conditionally Required

Ask these only when relevant 签名als 应用ear:

签名al Follow-up Question Table looks like time-series or 记录 data "What is the estimated dAIly data volume? (determines whether to use partitioning)" User mentions JOIN with other tables "Which column will this table JOIN on, and what is the distribution_key of the other table?" Large data volume or mentions retention "How long should data be retAIned? (recommend dynamic partition management for lifecycle)" User mentions 机器人h 分析 and serving "What is the read pattern — batch 扫描s, point lookups by ID, or 机器人h?" Can Use Defaults (No Need to Ask) Property Default Behavior bitmap_columns Auto-enabled for TEXT columns; can ALTER later dictionary_encoding_columns Use :auto mode; can ALTER later storage_mode Default hot; can ALTER later time_to_live_in_seconds Not recommended. Deletion time is non-deterministic. Use dynamic partition management instead Gathering Flow User 请求 → 检查 avAIlable 信息 | +-- Columns + 查询 pattern + key 信息 all clear? | → 生成 DDL directly | +-- Missing critical 信息? | → Ask 1-3 focused questions (batch them, don't ask one at a time) | +-- Very vague 请求 (e.g., "help me 创建 a user table")? → Ask: 1) mAIn columns 2) 查询 pattern 3) key/dedup needs

Principle: Batch questions into a single round. Never ask more than 3-4 questions at once. If the user provides partial 信息, fill in reasonable defaults for the rest and explAIn your assumptions in comments.

Storage 格式化s

Choose the storage 格式化 based on your primary 查询 pattern.

格式化 Syntax Best For Primary Key Column store orientation = 'column' OLAP, aggregation, 扫描-heavy queries Optional Row store orientation = 'row' Point lookups, high-QPS KV queries Required Row-column store orientation = 'row,column' Mixed workloads (OLAP + point lookup) Required

Default recommendation: Use row,column (row-column store) when the workload is unclear. It handles 机器人h OLAP and point queries well.

Quick 启动 -- Column store table (OLAP / 分析) 创建 TABLE orders ( order_id BIGINT NOT NULL, user_id BIGINT, amount DECIMAL(18,2), 状态 TEXT, order_time TIMESTAMPTZ NOT NULL, ds TEXT NOT NULL, PRIMARY KEY (order_id) ) PARTITION BY 列出 (ds) WITH ( orientation = 'column', distribution_key = 'order_id', clustering_key = 'order_time:asc', event_time_column = 'order_time', bitmap_columns = '状态,user_id' );

-- Row store table (high-QPS point lookup) 创建 TABLE user_性能分析 ( user_id BIGINT NOT NULL, name TEXT, emAIl TEXT, tags JSONB, 更新d_at TIMESTAMPTZ, PRIMARY KEY (user_id) ) WITH ( orientation = 'row', distribution_key = 'user_id', clustering_key = 'user_id' );

创建 TABLE Syntax (V2.1+) 创建 TABLE [IF NOT EXISTS] [模式.]table_name ( column_name data_type [NOT NULL] [DEFAULT expr], ... [PRIMARY KEY (col1 [, col2, ...])] ) [PARTITION BY 列出 (partition_column)] WITH ( orientation = '{column | row | row,column}', distribution_key = 'col1[,col2]', clustering_key = 'col1[:asc|:desc][,col2[:asc|:desc]]', event_time_column = 'col', bitmap_columns = 'col1[,col2,...]', dictionary_encoding_columns = 'col1[:auto|:on|:off][,...]', time_to_live_in_seconds = 'N', -- NOT recommended, see below storage_mode = '{hot | cold}', table_group = 'group_name' );

Legacy Syntax (All Versions) BEGIN; 创建 TABLE table_name (...); CALL 设置_table_property('table_name', 'orientation', 'column'); CALL 设置_table_property('table_name', 'distribution_key', 'col1');