Hologres CLI

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:

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:

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):

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

Dynamic Table Commands (V3.1+)

Full lifecycle management for Hologres Dynamic Tables.

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:

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

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

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": "..."}}