production

AdaptiveGears CLI

CLI tools for data engineering workflows

#cli#postgres#uuid#tools#aws#arn

Generating Time-Sortable UUIDs

UUID v4 is random. UUID v7 embeds a timestamp - records sort chronologically by ID.

uvx adaptivegears uuid           # v4 (random)
uvx adaptivegears uuid -7        # v7 (time-sortable)
uvx adaptivegears uuid -7 -n 5   # multiple

v7 is useful for primary keys where you want insertion order without a separate timestamp column.

Checking PostgreSQL Connectivity

Quick sanity check before running queries:

export PGDATABASE=mydb PGHOST=localhost PGUSER=postgres

uvx adaptivegears pg ping
# PONG

Uses libpq environment variables - same as psql.

Listing Tables with Size Statistics

What’s in this database? How big are the tables?

uvx adaptivegears pg list
# users          1,234    12.5 MB     10.4 kB
# orders       892,103   156.2 MB       184 B
# events     5,421,892     2.1 GB       412 B

Columns: table name, row count, total size, average row size.

Filter and sort:

uvx adaptivegears pg list 'page_*'           # glob pattern
uvx adaptivegears pg list -s myschema        # different schema
uvx adaptivegears pg list --min-rows 1000    # only tables with 1000+ rows
uvx adaptivegears pg list --sort size        # largest first
uvx adaptivegears pg list --json             # machine-readable

Analyzing Temporal Distribution of Records

How many records per day? Per month? Where are the gaps?

uvx adaptivegears pg histogram events created_at
# 2024-12-01    1,234
# 2024-12-02      892
# 2024-12-03    1,456

The column must be a date or timestamp type - the command validates this before querying.

Change granularity:

uvx adaptivegears pg histogram events created_at --by month
uvx adaptivegears pg histogram events created_at --by hour

Filter by date range:

uvx adaptivegears pg histogram events created_at --since 2024-01-01
uvx adaptivegears pg histogram events created_at --last 30d
uvx adaptivegears pg histogram events created_at --last 3m --bars
# cumulative percentage
uvx adaptivegears pg histogram events created_at --last 30d --cumulative

Nullable columns automatically filter out NULLs.

Generating DDL from PostgreSQL Tables

PostgreSQL has no SHOW CREATE TABLE. This reconstructs it from system catalogs:

uvx adaptivegears pg schema users
CREATE TABLE users (
    id uuid NOT NULL,
    email text NOT NULL,
    created_at timestamp with time zone DEFAULT now(),
    CONSTRAINT users_pkey PRIMARY KEY (id),
    CONSTRAINT users_email_key UNIQUE (email)
);

CREATE INDEX users_created_at_idx ON users (created_at);

Includes columns, constraints (PK, FK, UNIQUE, CHECK), and indexes. Uses format_type() and pg_get_constraintdef() internally for accurate type representation.

uvx adaptivegears pg schema users -s myschema   # different schema
uvx adaptivegears pg schema users --json        # DDL as JSON string

Exporting and Importing AWS Resource Tags

Bulk tag management via spreadsheet. Export current tags, edit in Excel, import changes.

uvx adaptivegears tags get
# Exported 1,234 resources to tags.xlsx

Edit the XLSX file—fill in tag:Workload and tag:Name columns.

uvx adaptivegears tags set -i tags.xlsx
# Planned changes (42 resources):
#   arn:aws:ec2:us-east-1:123456789012:instance/i-abc123
#     + Workload: platform
#     + Name: api-server
# Dry run. Use --apply to execute changes.

uvx adaptivegears tags set -i tags.xlsx --apply
# Apply 84 tag changes to 42 resources? [y/n]: y
# Done: 42 success, 0 failed

Customize which tags to export/import:

uvx adaptivegears tags get --tags Environment,Team,CostCenter
uvx adaptivegears tags set -i tags.xlsx --tags Environment,Team,CostCenter

Filter to billable resources only (excludes VPCs, subnets, security groups):

uvx adaptivegears tags get --billable

Requires AWS Resource Explorer 2 enabled in the account.

Finding Orphaned Launch Templates

Which launch templates have no references and zero running instances?

uvx adaptivegears lookup lt --orphaned
uvx adaptivegears lookup lt --orphaned -s created   # oldest first
uvx adaptivegears lookup lt --json                  # machine-readable

Checks: ASG, EKS Node Groups, EC2 Fleet, Spot Fleet, running EC2 instances. Karpenter templates detected by naming convention.

For legacy Launch Configurations (deprecated): uvx adaptivegears lookup lc --orphaned

Finding Orphaned AMIs

Which AMIs have no references and zero instances?

uvx adaptivegears lookup ami --orphaned
uvx adaptivegears lookup ami --orphaned -s created   # oldest first
uvx adaptivegears lookup ami --json                  # machine-readable

Checks: Launch Templates (all versions), Launch Configurations, EC2 instances (running + stopped). Reports associated snapshots for cleanup.

Finding Orphaned CloudWatch Alarms

Which alarms reference resources that no longer exist?

uvx adaptivegears lookup alarm --orphaned
uvx adaptivegears lookup alarm --orphaned -s name    # alphabetically
uvx adaptivegears lookup alarm --json                # machine-readable

Checks: RDS/DocDB instances and clusters, MQ brokers, SQS queues, EC2 instances, ASGs, ElastiCache clusters, Classic/Application ELBs, OpenSearch domains, Route53 health checks.

Interactive cleanup: uvx adaptivegears lookup alarm --orphaned --delete

Finding Orphaned Load Balancers

Which load balancers have no registered targets?

uvx adaptivegears lookup lb --orphaned
uvx adaptivegears lookup lb --orphaned -s type       # group by type
uvx adaptivegears lookup lb --json                   # machine-readable

Checks: ALB/NLB target groups and their targets, Classic ELB registered instances. A load balancer is orphaned if it has zero targets across all target groups.

Interactive cleanup: uvx adaptivegears lookup lb --orphaned --delete

Finding Orphaned EBS Volumes

Which EBS volumes are not attached to any instance?

uvx adaptivegears lookup ebs --orphaned
uvx adaptivegears lookup ebs --orphaned -s size      # largest first
uvx adaptivegears lookup ebs --json                  # machine-readable

Orphaned = volume state is available (not attached). Reports volume ID, name, size, type, and availability zone.

Interactive cleanup: uvx adaptivegears lookup ebs --orphaned --delete

Parsing AWS ARNs

Parse an ARN to extract all components:

uvx arnmatch arn:aws:lambda:us-east-1:123456789012:function:my-function

# partition: aws
# service: lambda
# region: us-east-1
# account: 123456789012
# resource_type: function
# resource_id: my-function
# sdk_service: lambda
# cloudformation: AWS::Lambda::Function

Useful for identifying resource types, finding the correct boto3 client name, or getting CloudFormation resource type. Supports 300+ AWS services.

References