tdx CLI - Basic Operations

Expert assistance for using tdx, the AI-native CLI for Treasure Data, to manage databases, tables, queries, and CLI context.

When to Use This Skill

Use this skill when:

• Setting up or configuring tdx CLI
• Managing databases and tables via command line
• Executing SQL queries from terminal
• Working with tdx context (profiles, sessions, project config)
• Troubleshooting tdx CLI issues

Installation and Setup

Installation Options

# Option 1: Install globally (recommended for regular use)
npm install -g @treasuredata/tdx

# Option 2: Run with bunx (no installation, always latest)
bunx @treasuredata/tdx@latest databases

# Option 3: Run with npx
npx @treasuredata/tdx@latest databases

After global installation, use tdx command directly:

tdx databases
tdx tables
tdx query "SELECT * FROM mydb.users"

Configure API Key

Create ~/.config/tdx/.env:

TD_API_KEY=your-key-id/your-key-secret

Or use environment variable:

export TD_API_KEY=your-key-id/your-key-secret

Context Management

tdx context is resolved with this priority:

1. CLI flags (highest) - --database, --site
2. Session context - tdx use database mydb
3. Project config - tdx.json in project root
4. Profile config - ~/.config/tdx/tdx.json
5. Global config - ~/.config/tdx/tdx.json

Profiles

Define reusable configurations in ~/.config/tdx/tdx.json:

{
  "profiles": {
    "production": {
      "site": "us01",
      "database": "analytics"
    },
    "dev": {
      "site": "jp01",
      "database": "dev_db"
    }
  }
}

Usage:

# Switch profile
tdx use profile production

# List profiles
tdx profiles

Session Context

Set temporary overrides for current shell:

# Set session database (PID-scoped by default)
tdx use database mydb

# Set session site
tdx use site jp01

# View current context
tdx context

# Clear session
tdx context --clear

Important: By default, sessions are scoped per terminal window (by PPID). This means context set in one terminal won't be available in another.

To persist context across terminals/processes, use --session:

# Set context with explicit session name
tdx --session my-workflow use database analytics
tdx --session my-workflow use site jp01

# Use the same session from different terminal or script
tdx --session my-workflow tables
tdx --session my-workflow query "SELECT * FROM users"

# Clear named session
tdx --session my-workflow context --clear

When to use --session:

• Scripts that span multiple processes
• Sharing context across multiple terminal windows
• CI/CD pipelines
• When you need persistent context beyond current terminal

Project Config

Create tdx.json in project root:

{
  "site": "us01",
  "database": "customer_analytics",
  "parent_segment": "active_users"
}

Security: Never commit API keys. Use profiles or ~/.config/tdx/.env.

Core Commands

Databases

# List all databases
tdx databases

# Filter with pattern
tdx databases "prod_*"

# Specify site
tdx databases --site jp01

# JSON output
tdx databases --json

Sites: us01 (default), jp01, eu01, ap02

Tables

# List all tables
tdx tables

# Tables from specific database
tdx tables "mydb.*"
tdx tables --in mydb
tdx tables -d mydb

# Filter with pattern
tdx tables "mydb.user_*"

# Describe table schema
tdx describe mydb.users
tdx desc users --in mydb

# Show table contents
tdx show mydb.users --limit 10
tdx show users --in mydb

Pattern Syntax:

• mydb.* - all tables from mydb
• *.users - users table from all databases
• prod_*.access_log - access_log from databases starting with prod_

Queries

# Execute SQL query
tdx query "SELECT * FROM mydb.users LIMIT 10"

# With database context
tdx query "SELECT * FROM users" --database mydb

# From file
tdx query -f query.sql

# Multi-statement from file
tdx query -f setup-and-query.sql

Multi-statement execution: Separate statements with semicolons. Execution stops on first error.

Output Formats

# Table format (default, human-readable)
tdx databases

# JSON (for jq/scripting)
tdx databases --json

# JSON Lines (streaming)
tdx query "SELECT * FROM users" --jsonl

# TSV (tab-separated)
tdx databases --tsv

# Save to file
tdx databases --json --output databases.json

Common Patterns

Pattern 1: Explore Database

# Set context once
tdx use database sample_datasets
tdx use site jp01

# Now explore without flags
tdx tables
tdx describe www_access
tdx show www_access --limit 10

Pattern 2: Profile-Based Workflow

# Switch to production
tdx use profile prod
tdx tables

# Switch to dev
tdx use profile dev
tdx tables

Pattern 3: Query and Process

# Query and pipe to jq
tdx query "SELECT * FROM users" --json | jq '.[0]'

# Query as JSONL and process line by line
tdx query "SELECT * FROM users" --jsonl | while read line; do
  echo "$line" | jq '.name'
done

Pattern 4: Multi-Site Comparison

# Check databases in different regions
tdx databases --site us01 --json > us_dbs.json
tdx databases --site jp01 --json > jp_dbs.json

Global Options

Available for all commands:

• --site <site> - TD site/region (us01, jp01, eu01, ap02)
• --format <format> - Output format (table, json, jsonl, tsv)
• --json - JSON output (shorthand)
• --jsonl - JSON Lines output (shorthand)
• --tsv - TSV output (shorthand)
• --output <file> - Save to file
• --limit <rows> - Max rows (table format, default: 40)
• --verbose - Verbose logging
• --timeout <seconds> - Timeout (default: 30)
• --dry-run - Preview without executing
• -y, --yes - Skip confirmations

Best Practices

1. Use Context Management - Set database/profile once instead of repeating flags
2. Use Profiles - Define prod/dev/staging profiles for easy switching
3. Pattern Matching - Use wildcards (*) to filter databases/tables
4. Right Output Format - JSON/JSONL for scripting, table for review
5. Never Commit Keys - Store API keys in ~/.config/tdx/.env, not in git
6. Test with LIMIT - Add LIMIT when exploring to avoid long queries
7. Use --dry-run - Preview operations on production

TD-Specific Conventions

Table Naming

TD uses dot notation: database_name.table_name

tdx show sample_datasets.www_access

Time-Based Filtering

For partitioned tables, use time filters for performance:

# Use TD_INTERVAL for relative time (UTC default)
tdx query "
SELECT COUNT(*)
FROM mydb.access_logs
WHERE TD_INTERVAL(time, '-1d')
"

# With explicit timezone for Japan data
tdx query "
SELECT COUNT(*)
FROM mydb.access_logs
WHERE TD_INTERVAL(time, '-1d', 'JST')
"

# Use TD_TIME_RANGE for absolute time (UTC default)
tdx query "
SELECT COUNT(*)
FROM mydb.access_logs
WHERE TD_TIME_RANGE(time, '2025-01-01', '2025-01-31')
"

Timezone

• UTC is the default - timezone parameter can be omitted
• JST for Japan data - must specify explicitly: TD_INTERVAL(time, '-1d', 'JST')
• Other timezones must be explicitly specified

Common Issues

API Key Not Found

Error: "TD_API_KEY not found"

Solution:

1. Create ~/.config/tdx/.env with TD_API_KEY=key_id/key_secret
2. Or: export TD_API_KEY=key_id/key_secret
3. Verify format: key_id/key_secret (not just key_id)

Database Not Found

Error: "Database 'mydb' does not exist"

Solution:

1. List databases: tdx databases
2. Check correct site: tdx databases --site jp01
3. Use correct name in query

Pattern Matching Not Working

Solution:

1. Always quote patterns: tdx tables "prod_*"
2. Or use --in flag: tdx tables --in mydb

Query Timeout

Solution:

1. Increase timeout: tdx query "..." --timeout 300
2. Add LIMIT clause
3. Use TD_INTERVAL/TD_TIME_RANGE for partition pruning

Session Context Not Working Across Terminals

Expected Behavior: Sessions are scoped per terminal window (by PPID) by default.

Solution - Use --session for shared context:

# Set context with explicit session name
tdx --session my-workflow use database mydb
tdx --session my-workflow use site jp01

# Access from any terminal
tdx --session my-workflow tables

Alternative solutions:

1. Use profiles: tdx use profile prod (switch in each terminal)
2. Use project config: Create tdx.json (automatic per directory)

See the Session Context section above for more details on --session.

Table-Specific Options

For table commands (tables, describe, show):

• -d, --database <name> - Specify database
• --in <database> - Alias for --database (natural language)

# All equivalent
tdx tables "mydb.*"
tdx tables --in mydb
tdx tables -d mydb

Query-Specific Options

For query command:

• -f, --file <path> - Read SQL from file
• -d, --database <db> - Database to query (default: information_schema)
• --catalog <catalog> - Trino catalog (default: td)

Complete Command Reference

For full command list and advanced features, visit: https://www.npmjs.com/package/@treasuredata/tdx

Additional commands available:

• Job management (submit, list, kill, results)
• Segment operations (CDP)
• Activation commands
• Workflow commands
• LLM agent management
• Chat interface
• API access

Related Skills

• sql-skills/trino - Advanced Trino query optimization
• sql-skills/hive - Hive query patterns
• workflow-skills/digdag - Automate tdx operations

Resources

• npm Package: https://www.npmjs.com/package/@treasuredata/tdx
• GitHub: https://github.com/treasure-data/tdx
• TD Documentation: https://docs.treasuredata.com