hologres-cli

name	hologres-cli
description	AI-agent-friendly Hologres CLI with safety guardrails and structured JSON output. Use for database operations, schema inspection, SQL execution, data import/export, Dynamic Table lifecycle management (V3.1+ syntax), and GUC parameter management. Triggers: "hologres cli", "hologres command", "hologres database", "dynamic table", "hologres查询", "hologres guc", "GUC parameter"

Hologres CLI

AI-agent-friendly command-line interface for Hologres with safety guardrails and structured JSON output.

Installation

# Requires Python 3.11+
pip install hologres-cli

# Or install a specific version
pip install hologres-cli==0.2.6

Configuration

Profile-based configuration stored in ~/.hologres/config.json.

# Interactive setup wizard
hologres config

# Or set values directly
hologres config set region_id cn-hangzhou
hologres config set instance_id hgprecn-cn-xxx
hologres config set database mydb

Profile resolution priority: --profile <name> flag > current profile > error (prompts to run hologres config).

Connection Modes

connection_mode decides the transport and which fields the user must provide. When helping a user set up a profile, first pick the mode, then prompt only for the fields that mode needs. Set it with hologres config set connection_mode <auto|jdbc|api>.

Direct mode (auto / jdbc) — connects via the instance's PostgreSQL endpoint ({instance_id}-{region_id}[.internal|-vpc-st].hologres.aliyuncs.com:80). This is the only mode that uses the instance endpoint. Prompt the user for:

Field	Required?	Notes
region_id	yes	e.g. cn-hangzhou
instance_id	yes*	e.g. hgpostcn-cn-xxx. *Required unless an explicit endpoint host is given
nettype	yes*	internet / intranet / vpc. *Only used to auto-construct the host when no explicit endpoint is set
auth_mode + credentials	yes	ram / basic / sts — see Auth Modes below
database	yes
warehouse	recommended	computing group
endpoint	optional	host only (no port). If the user pastes host:port, the embedded port is stripped and port field wins. Leave empty to auto-construct from instance_id + region_id + nettype
port	optional	default 80; host-side port, user-changeable

auto vs jdbc: auto (default) tries JDBC first and transparently falls back to the OpenAPI ExecuteStatement API if the JDBC connect fails. To make that fallback available, auto additionally needs the API-mode prerequisites below (RAM/STS credentials). jdbc is strict — no fallback, classic lazy connect.

API mode (api) — runs SQL through the Hologram OpenAPI ExecuteStatement against hologram.{region}.aliyuncs.com. The instance endpoint / port / nettype are not used. Prompt the user for:

Field	Required?	Notes
region_id	yes
instance_id	yes
database	yes
auth_mode + credentials	yes	ram (AK/SK) or sts only — basic is NOT supported (API needs cloud AK/SK, not a DB account)
warehouse	recommended

API-mode instance-side prerequisites (prompt the user to verify, cannot be set via config):

• The instance must have ExecuteStatement enabled (hologres instance-manage enable-execute-statement; check with get-execute-statement-enabled).
• The RAM account/role must hold hologram:ExecuteStatement permission.

Quick decision guide for the Agent:

• User can reach the instance's PostgreSQL port (80/443) over the network → direct mode (auto).
• PostgreSQL port firewalled / cross-region / instance hasn't enabled the PG gateway, or user only has cloud AK/SK and no DB account → api mode.
• On ECS/container/Codeup with a RAM role, passwordless → sts auth works in both modes.

Auth Modes

Choose by what credentials you have (all modes also need region_id / instance_id / database):

Scenario	auth_mode	Credential fields
Have an Alibaba Cloud AccessKey (LTAI…)	ram (default)	access_key_id + access_key_secret
Have only a Hologres DB account	basic	username + password
On ECS/container/Codeup, want passwordless	sts	none persisted — fetched at runtime

ram — long-lived AccessKey:

hologres config set auth_mode ram
hologres config set access_key_id LTAI5tXXXXXXXXXXXX
hologres config set access_key_secret XXXXXXXXXXXXXXXX

basic — Hologres DB account. The username must use the BASIC$<name> format (Hologres-specific prefix, not a plain PostgreSQL username):

hologres config set auth_mode basic
hologres config set username 'BASIC$myuser'
hologres config set password XXXXXXXX

sts — temporary credentials, passwordless (ideal for ECS/containers). Temporary credentials are fetched at runtime, never persisted, auto-refreshed in-process. Applies uniformly to SQL execution, instance management, and metric (CMS) commands (shared credentials.get_credential_client resolver):

hologres config set auth_mode sts
hologres config set credentials_uri http://my-sts-endpoint/sts   # optional
# or: export ALIBABA_CLOUD_CREDENTIALS_URI=http://...

STS prerequisite: the assumed RAM role must hold Hologres + CloudMonitor (CMS) permissions — otherwise connections may succeed but operations fail with permission errors.

credentials_uri / ALIBABA_CLOUD_CREDENTIALS_URI details — the URI must GET-return JSON (camelCase): {"Code":"Success","AccessKeyId":"STS.xxx","AccessKeySecret":"yyy","SecurityToken":"zzz","Expiration":"2026-06-24T12:00:00Z"}. Resolution priority: profile credentials_uri field (if set → explicit provider that bypasses the default chain) > default chain (whose steps are: standard STS env vars → OIDC → ~/.aliyun/config.json → ECS metadata → env ALIBABA_CLOUD_CREDENTIALS_URI as last-resort fallback). Session-type credentials auto-refresh on expiry; do not set the static STS env vars together with the URI (the env vars win and the URI is never reached).

Verify any mode with hologres status.

Quick Start

pip install hologres-cli
hologres config                                   # Interactive setup
hologres status                                    # Check connection
hologres schema tables                             # List tables
hologres sql run "SELECT * FROM orders LIMIT 10"   # Query data
hologres --profile prod status                     # Use specific profile
hologres dt list                                   # List Dynamic Tables

Core Commands

Command	Description
hologres status	Check connection status
hologres instance <name>	Query instance version/connections
hologres warehouse [name]	List or query warehouses
hologres schema tables	List all tables
hologres schema describe <table>	Show table structure
hologres schema dump <schema.table>	Export DDL
hologres schema size <schema.table>	Get table storage size
hologres table list [--schema S]	List all tables
hologres table create -n TABLE -c COLS [options] [--dry-run]	Create a table (supports logical partition V3.1+)
hologres table dump <schema.table>	Export DDL for a table
hologres table show <table>	Show table structure (columns, types, nullable, defaults, primary key, comments)
hologres table size <schema.table>	Get table storage size
hologres table properties <table>	Show Hologres-specific table properties (orientation, distribution_key, clustering_key, TTL, etc.)
hologres table drop <table> [--if-exists] [--cascade] --confirm	Drop a table (dry-run by default)
hologres table truncate <table> --confirm	Truncate (empty) a table (dry-run by default)
hologres table alter TABLE [options] [--dry-run]	Alter table properties (add column, rename, TTL, etc.)
hologres partition list --table <table>	List partitions of a logical partition table
hologres partition create --table <table>	Create partition (no-op for logical tables, returns notice)
hologres partition drop --table <table> --partition VALUE --confirm	Drop partition (deletes partition data)

Dynamic Table Commands (V3.1+)

Full lifecycle management for Hologres Dynamic Tables.

Command	Description
hologres dt create	Create a Dynamic Table
hologres dt list	List all Dynamic Tables
hologres dt show <table>	Show Dynamic Table properties
hologres dt ddl <table>	Show DDL (CREATE statement)
hologres dt lineage <table>	Show dependency lineage
hologres dt lineage --all	Show lineage for all DTs
hologres dt storage <table>	Show storage details
hologres dt state-size <table>	Show state table size (incremental)
hologres dt refresh <table>	Trigger manual refresh
hologres dt alter <table>	Alter DT properties
hologres dt drop <table>	Drop DT (dry-run by default)
hologres dt convert [table]	Convert V3.0 → V3.1 syntax

dt create

# Minimal
hologres dt create -t my_dt --freshness "10 minutes" \
  -q "SELECT col1, SUM(col2) FROM src GROUP BY col1"

# With partitioning and serverless
hologres dt create -t ads_report --freshness "5 minutes" --refresh-mode auto \
  --logical-partition-key ds --partition-active-time "2 days" \
  --partition-time-format YYYY-MM-DD \
  --computing-resource serverless --serverless-cores 32 \
  -q "SELECT repo_name, COUNT(*) AS events, ds FROM src GROUP BY repo_name, ds"

# Incremental refresh
hologres dt create -t tpch_q1 --freshness "3 minutes" --refresh-mode incremental \
  -q "SELECT l_returnflag, l_linestatus, COUNT(*) FROM lineitem GROUP BY 1,2"

# Dry-run (preview SQL without executing)
hologres dt create -t my_dt --freshness "10 minutes" -q "SELECT 1" --dry-run

Key create options:

Option	Description
-t, --table	Table name [schema.]table (required)
-q, --query	SQL query for data definition (required)
--freshness	Data freshness target, e.g. "10 minutes" (required)
--refresh-mode	auto / full / incremental
--auto-refresh/--no-auto-refresh	Enable/disable auto refresh
--cdc-format	stream (default) / binlog
--computing-resource	local / serverless / <warehouse>
--serverless-cores	Serverless computing cores
--logical-partition-key	Partition column for logical partition
--partition-active-time	Active partition window, e.g. "2 days"
--partition-time-format	Partition key format, e.g. YYYY-MM-DD
--orientation	column / row / row,column
--distribution-key	Distribution key columns
--clustering-key	Clustering key with sort order
--event-time-column	Event time column (Segment Key)
--ttl	Data TTL in seconds
--refresh-guc	GUC params for refresh (repeatable)
--dry-run	Preview SQL without executing

dt list / show / ddl

hologres dt list                     # List all DTs with refresh info
hologres dt show public.my_dt        # Show all properties
hologres dt ddl public.my_dt         # Show CREATE statement
hologres dt list -f table            # Table format output

dt lineage

hologres dt lineage public.my_dt     # Single table lineage
hologres dt lineage --all            # All DTs lineage
hologres dt lineage my_dt -f table   # Table format

base_table_type: r=table, v=view, m=materialized view, f=foreign table, d=Dynamic Table.

dt storage / state-size

hologres dt storage public.my_dt      # Storage breakdown
hologres dt state-size public.my_dt   # State table size (incremental DTs)

dt refresh

hologres dt refresh my_dt
hologres dt refresh my_dt --overwrite --partition "ds = '2025-04-01'" --mode full
hologres dt refresh my_dt --dry-run

dt alter

hologres dt alter my_dt --freshness "30 minutes"
hologres dt alter my_dt --no-auto-refresh
hologres dt alter my_dt --refresh-mode full --computing-resource serverless
hologres dt alter my_dt --refresh-guc timezone=GMT-8:00 --dry-run

dt drop

hologres dt drop my_dt               # Dry-run by default (safety)
hologres dt drop my_dt --confirm     # Actually drop
hologres dt drop my_dt --if-exists --confirm

dt convert (V3.0 → V3.1)

hologres dt convert my_old_dt          # Convert single table
hologres dt convert --all              # Convert all V3.0 tables
hologres dt convert my_old_dt --dry-run

Output Formats

Partition Management

# List partitions
hologres partition list -t public.logs

# Drop a partition
hologres partition drop -t my_table --partition "2025-04-01" --confirm

# Alter partition properties
hologres partition alter -t public.logs --partition "ds=2025-03-16" --set "keep_alive=TRUE"
hologres partition alter -t my_table --partition "ds=2025-03-16" --set "keep_alive=TRUE" --set "storage_mode=hot" --dry-run

Output Formats

hologres -f json schema tables    # JSON (default)
hologres -f table schema tables   # Human-readable table
hologres -f csv schema tables     # CSV
hologres -f jsonl schema tables   # JSON Lines

Response Structure

// Success
{"ok": true, "data": {"rows": [...], "count": 10}}

// Error
{"ok": false, "error": {"code": "ERROR_CODE", "message": "..."}}

Safety Features

0. Default Session GUC Protection

All connections automatically set safety GUCs upon creation:

• SET hg_experimental_enable_adaptive_execution = on — Enables adaptive execution to prevent OOM
• SET hg_computing_resource = 'serverless' — Routes queries to the serverless computing pool

These are applied transparently at the connection layer; no user action needed.

1. Row Limit Protection

Queries without LIMIT returning >100 rows fail with LIMIT_REQUIRED.

# Will fail if >100 rows
hologres sql run "SELECT * FROM large_table"

# Fix: add LIMIT
hologres sql run "SELECT * FROM large_table LIMIT 50"

# Or disable check
hologres sql run --no-limit-check "SELECT * FROM large_table"

2. Write Protection

Write operations (INSERT, UPDATE, DELETE, DROP, CREATE, ALTER, TRUNCATE, GRANT, REVOKE) require --write flag.

hologres sql run --write "INSERT INTO logs VALUES (1, 'test')"

3. Dangerous Write Blocking

DELETE/UPDATE without WHERE clause are blocked.

# Blocked
hologres sql run --write "DELETE FROM users"

# Must have WHERE
hologres sql run --write "DELETE FROM users WHERE status='inactive'"

Error Codes

Code	Description
CONNECTION_ERROR	Failed to connect
QUERY_ERROR	SQL execution error
LIMIT_REQUIRED	Need LIMIT clause
WRITE_GUARD_ERROR	Write operation without --write flag
DANGEROUS_WRITE_BLOCKED	DELETE/UPDATE without WHERE clause
WRITE_BLOCKED	Write operation not allowed
NOT_FOUND	Table or resource not found
INVALID_INPUT	Invalid identifier or input validation failed
INVALID_ARGS	Invalid or missing arguments
NO_CHANGES	No properties specified to alter
EXPORT_ERROR	Data export failed
IMPORT_ERROR	Data import failed
VIEW_NOT_FOUND	View not found
OSS_ERROR	OSS operation failed (e.g. directory placeholder creation on volume create)

Sensitive Data Masking

Auto-masks by column name pattern:

• phone/mobile/tel → 138****5678
• email → j***@example.com
• password/secret/token → ********

Disable: hologres sql run --no-mask "SELECT * FROM users LIMIT 10"

References

Document	Content
commands.md	Complete command reference with DT commands
ai-volume-model.md	AI generation, volume storage, and model commands
safety-features.md	Safety guardrails details

Best Practices

1. Always use LIMIT for large result sets
2. Use --dry-run to preview DT SQL before executing
3. Use --confirm explicitly for destructive operations (table drop, table truncate, dt drop)
4. Include WHERE clause in DELETE/UPDATE
5. Use JSON output for automation/scripting
6. Check hologres status before batch operations
7. Use hologres dt lineage to understand DT dependencies before altering

SQL Tracking

Set HOLOGRES_SKILL environment variable before calling CLI to tag queries with skill origin:

export HOLOGRES_SKILL=hologres-query-optimizer
hologres sql run "SELECT * FROM orders LIMIT 10"

Queries will appear in hg_query_log with application_name = "hologres-cli/hologres-query-optimizer".

This enables per-skill SQL statistics on the Hologres server:

SELECT
  split_part(application_name, '/', 2) AS skill,
  COUNT(*) AS query_count,
  AVG(duration) AS avg_duration_ms
FROM hologres.hg_query_log
WHERE query_start > now() - interval '1 hour'
  AND application_name LIKE 'hologres-cli/%'
GROUP BY 1
ORDER BY 2 DESC;

Error Codes Reference

All CLI errors return structured JSON with retryable and hint fields for automatic retry decisions:

{"ok": false, "error": {"code": "...", "message": "...", "retryable": true/false, "hint": "..."}}

Code	Retryable	When	Agent Action
CONNECTION_ERROR	Yes	Network/auth failure	Check config, retry after delay
CONNECTION_TIMEOUT	Yes	Server busy	Retry after short delay
CONFIG_ERROR	No	Invalid config	Run hologres config
PROFILE_NOT_FOUND	No	Profile missing	Use hologres config list
INVALID_INPUT	No	Bad parameters	Fix input and retry
INVALID_ARGS	No	Wrong arguments	Check --help
WRITE_GUARD_ERROR	No	Write without flag	Add --write flag
DANGEROUS_WRITE_BLOCKED	No	DELETE/UPDATE no WHERE	Add WHERE clause
LIMIT_REQUIRED	No	SELECT >100 rows	Add LIMIT or --no-limit-check
QUERY_ERROR	Yes	SQL execution failed	Check syntax, retry once
QUERY_TIMEOUT	Yes	Query too slow	Simplify query or add filters
TABLE_NOT_FOUND	No	Table doesn't exist	Verify with hologres table list
NOT_FOUND	No	Resource missing	Run corresponding list command
FILE_NOT_FOUND	No	Path invalid	Verify file path
OSS_ERROR	Yes	Storage failure	Check credentials, retry
NO_CHANGES