xplain-import-pilot 0.1.0

Batch import pilot for xplain via YAML-driven configuration

xplain-import-pilot

YAML-driven batch import tool for xplain analytics platform

Import database tables into XOE (Xplain Object Explorer) sessions through declarative configuration files. Define your data model once in YAML, then import entire table hierarchies with a single command.

Features

• 🎯 Declarative Configuration — Define database connections and object hierarchies in YAML
• 🔗 Multi-Level Relationships — Support for root objects and nested child relationships
• 🎲 Smart Sampling — Built-in sampling with database-specific random functions
• 🧮 SQL Dimensions — Add computed dimensions using SQL expressions
• ✅ Validation First — Comprehensive config validation before any network calls
• 🔍 Dry-Run Mode — Preview import plan without connecting to databases
• 🗄️ Multi-Database Support — PostgreSQL, MySQL, Oracle, DB2, MariaDB, Databricks
• 🧬 Attribute Import — Import lookup/dimension tables as XAttributes with hierarchy levels
• 📓 Notebook Export — Generate a Jupyter notebook mirroring the import steps

---

Installation

Prerequisites

• Python ≥3.12
• uv package manager

Install from Source

cd /Users/peide/xplainrepo/xplain_import_pilot
uv sync

This installs the xplain-import command-line tool.

---

Quick Start

1. Get Your XOE Session ID

1. Open your XOE instance in a browser
2. Open DevTools (F12 or right-click → Inspect)
3. Navigate to Application → Cookies
4. Copy the value of JSESSIONID

2. Create a Configuration File

Create my-import.yaml:

database:
  type: POSTGRESQL
  host: localhost
  port: 5432
  user: analytics_user
  password: changeme
  database: sales_db
  schema: public  # optional

objects:
  # Root object
  - name: customers
    table: customers
    role: root
    primary_key: customer_id

  # Child of customers
  - name: orders
    table: orders
    role: child
    parent: customers
    foreign_keys:
      - customer_id

3. Run Import

Dry-run (validate config without connecting):

uv run xplain-import --session-id dummy --config my-import.yaml --dry-run

Execute import:

# Using environment variable (recommended)
export XPLAIN_SESSION_ID=<your-session-id>
uv run xplain-import --config my-import.yaml

# Or using CLI flag
uv run xplain-import --session-id <your-session-id> --config my-import.yaml

---

Configuration Reference

Database Configuration

database:
  type: POSTGRESQL | MYSQL | ORACLE | DB2 | MARIADB | DATABRICKS
  host: <hostname>
  port: <port-number>
  user: <username>
  password: <password>
  database: <database-name>
  schema: <schema-name>          # optional
  driver_properties:             # optional JDBC properties
    key: value

Object Configuration

Root Object

- name: <object-name>
  table: <table-name>
  role: root
  primary_key: <column-name>
  where_clause: "status = 'active'"  # optional SQL filter
  sampling_rate: 0.8                 # optional (0.0-1.0)
  sql_dimensions:                    # optional computed dimensions
    - name: <dimension-name>
      expression: "EXTRACT(YEAR FROM created_at)"
      type: INT | LONG | CATEGORIAL

Child Object

- name: <object-name>
  table: <table-name>
  role: child
  parent: <parent-object-name>
  foreign_keys:
    - <column-name>
  where_clause: "amount > 0"    # optional
  sampling_rate: 0.5            # optional
  sql_dimensions:               # optional
    - name: <dimension-name>
      expression: "EXTRACT(MONTH FROM order_date)"
      type: INT

Field Descriptions

Field	Required	Description
name	✅	Unique object name in XOE
table	✅	Database table name
role	✅	root or child
primary_key	✅ (root)	Primary key column name
parent	✅ (child)	Parent object name
foreign_keys	✅ (child)	List of foreign key columns
where_clause	❌	SQL WHERE condition (without "WHERE")
sampling_rate	❌	Fraction of rows to import (0.0-1.0)
sql_dimensions	❌	Computed dimensions via SQL

Attributes (Dimension Lookup Tables)

Attributes import a lookup/dimension table (e.g. payers, categories) as an XAttribute with a drill-down hierarchy, rather than as a plain XTable. Add an attributes: section alongside objects::

attributes:
  - name: payers            # Unique name for this attribute config
    table: payers            # Source table
    attribute_name: Payer     # Display name in XOE
    primary_key: id
    hierarchy_level_columns: [name, ownership]   # leaf first → coarsest last
    hierarchy_level_names: [Payer, Ownership]    # optional display names per level
    xtable: encounters        # which XTable to attach this attribute to in the XView

Field	Required	Description
name	✅	Unique attribute config name
table	✅	Source database table
attribute_name	✅	Display name shown in XOE
primary_key	✅	Primary key column
hierarchy_level_columns	✅	Columns forming the drill-down hierarchy, leaf first
hierarchy_level_names	❌	Display names per hierarchy level (defaults to column names)
xtable	❌	XTable to attach the attribute to (default: the root object)

---

Usage Examples

Multi-Level Hierarchy

objects:
  - name: customers
    table: customers
    role: root
    primary_key: customer_id

  - name: orders
    table: orders
    role: child
    parent: customers
    primary_key: order_id        # required: orders is a parent to order_items below
    foreign_keys: [customer_id]

  - name: order_items
    table: order_items
    role: child
    parent: orders
    foreign_keys: [customer_id, order_id]   # full compound key of orders

Filtering and Sampling

objects:
  - name: active_customers
    table: customers
    role: root
    primary_key: customer_id
    where_clause: "status = 'active' AND created_at > '2023-01-01'"
    sampling_rate: 0.1  # Import 10% of matching rows

Computed Dimensions

objects:
  - name: orders
    table: orders
    role: root
    primary_key: order_id
    sql_dimensions:
      - name: order_year
        expression: "EXTRACT(YEAR FROM order_date)"
        type: INT
      - name: order_month
        expression: "EXTRACT(MONTH FROM order_date)"
        type: INT
      - name: order_quarter
        expression: "CONCAT('Q', CAST(CEIL(EXTRACT(MONTH FROM order_date)/3.0) AS VARCHAR))"
        type: CATEGORIAL

---

Command-Line Options

xplain-import [OPTIONS]

Options:
  --version                Show version and exit
  --session-id ID          XOE JSESSIONID (or set XPLAIN_SESSION_ID env var)
  --config PATH            Path to YAML configuration file (required)
  --url URL                XOE server URL (default: http://localhost:8080, or XPLAIN_SERVER_URL env var)
  --dry-run                Validate config and print plan without connecting
  --log-level LEVEL        Logging verbosity: DEBUG, INFO, WARNING, ERROR (default: INFO)
  --sampling-rate RATE     Override sampling rate for all objects (0.0-1.0]
  --persist                Save configs to XOE for reuse (connection, xtables, xview)
  --persist-prefix PREFIX  Prefix for saved config names (default: YAML filename)
  --persist-ownership LVL  Ownership: PUBLIC or PRIVATE (default: PUBLIC)
  --export-notebook        Export a Jupyter notebook with the xplain method sequence
  --notebook-output PATH   Output path for notebook (default: <config-name>_import.ipynb)
  -h, --help               Show help message

Exporting a Notebook

--export-notebook writes a Jupyter notebook that replays the same import steps (xplain.Xsession, connection setup, per-object import_xtable() calls, xview build) as executable cells — useful for handing off an import to someone working in a notebook, or for tweaking a step interactively after the CLI run:

uv run xplain-import --config sales.yaml --export-notebook
uv run xplain-import --config sales.yaml --export-notebook --notebook-output sales_import.ipynb

Persisting Configurations

Use --persist to save all configurations to XOE for later reuse:

uv run xplain-import --config sales.yaml --persist

This saves:

• Database connection → sales_connection.xdbconnection
• XTable configs → sales_<object>.xtableconfig for each object
• XView → sales_view.xview

Customize the prefix or ownership:

uv run xplain-import --config sales.yaml --persist --persist-prefix my_project --persist-ownership PRIVATE

---

Troubleshooting

Config Validation Errors

Error: object 'orders' references parent 'customers' which is not defined

Solution: Ensure the parent object is defined in the objects list before the child.

---

Error: object 'customers': root requires 'primary_key'

Solution: Add primary_key: <column-name> to root objects.

---

Error: validation error for DimensionType

Solution: Ensure type is one of: INT, LONG, CATEGORIAL (note spelling).

---

Connection Errors

Error: DB connection test failed

Solution:

• Verify database credentials (host, port, user, password)
• Check network connectivity to database
• Ensure database user has SELECT permissions on tables

---

Error: Import failed: ... session ... not found

Solution:

• Verify XOE session is still active (sessions expire)
• Get a fresh JSESSIONID from browser
• Ensure XOE server URL is correct

---

Import Errors

Error: xtable import failed for 'orders'

Solution:

• Check that foreign key columns exist in child table
• Verify WHERE clause syntax is valid SQL
• Ensure parent object was imported successfully

---

Security Best Practices

[!WARNING] Credential Security

• Never commit YAML files with real passwords to version control
• Use environment variables for sensitive data
• Prefer XPLAIN_SESSION_ID env var over --session-id CLI flag (CLI args are visible in process lists)

Recommended approach:

1. Create .env file (already in .gitignore):

   XPLAIN_SESSION_ID=your_session_id_here
   

2. Load environment variables:

   source .env  # or use direnv, dotenv, etc.
   

3. Run without exposing secrets:

   uv run xplain-import --config my-import.yaml
   

---

Development

Setup Development Environment

# Install with dev dependencies
uv sync --extra dev

# Run tests
uv run pytest

# Type checking
uv run mypy xplain_import_pilot/

# Linting
uv run ruff check xplain_import_pilot/

Running Tests

# All tests with coverage
uv run pytest tests/ -v --cov=xplain_import_pilot --cov-report=term-missing

# Specific test file
uv run pytest tests/test_config.py -v

# With debug output
uv run pytest tests/ -v -s

---

How It Works

The import pipeline follows these steps:

1. Connect — Establish connection to existing XOE session via http_session_id
2. Database Setup — Create and test database connection
3. Import XTables — Import each table in dependency order (parents before children)
4. Build XView — Construct view configuration mirroring object hierarchy
5. Load Session — Load XView into live XOE session

Objects are imported in topological order using stable sorting (preserves YAML order for objects at the same depth).

---

Compound Keys & FK Dimension Alignment

How Xplain builds compound object keys

Xplain identifies every record in a hierarchy through a compound key — the ordered concatenation of ancestor key dimensions down to the object itself.

Level	Object	Own PK column	Compound key dimensions
1 (root)	patients	patientid	[patientid]
2 (child)	admissions	admissionid	[patientid, admissionid]
3 (grandchild)	clinical_observations	observationid	[patientid, admissionid, observationid]

A child's foreignKeyDimensions must equal the full compound key of its parent — not just the immediate FK column. The importer calculates and applies this expansion automatically; you only need to list the direct FK column(s) in the YAML.

FK column names vs. dimension names

After auto-mapping, every column starts with dimensionName == dbColumnName. But the parent's key dimension may have been named differently from the corresponding FK column in the child table:

Table	DB column	Must become dimension
patients	patientid	patientid (no change)
admissions	patient_id	patientid — renamed
clinical_observations	patient_id	patientid — renamed
clinical_observations	admission_id	admissionid — renamed

foreignKeyDimensions must reference dimension names, not column names. The importer aligns them automatically using normalized name matching: both the column name and the parent dimension name are lowercased and stripped of underscores. If they match, dimensionName is updated to the parent's canonical form. dbColumnName is never changed.

Normalization rule

normalize(name) = name.lower().replace("_", "")

patient_id   → patientid
patientid    → patientid   ← same → dimension is renamed to "patientid"

admission_id → admissionid
admissionid  → admissionid ← same → dimension is renamed to "admissionid"

Worked example — 3-level hierarchy

YAML config:

database:
  type: POSTGRESQL
  host: localhost
  port: 5432
  user: postgres
  password: secret
  database: hospital

objects:
  - name: patients
    table: patients
    role: root
    primary_key: patientid

  - name: admissions
    table: admissions
    role: child
    parent: patients
    primary_key: admissionid
    foreign_keys: [patient_id]        # direct FK column in the admissions table

  - name: clinical_observations
    table: clinical_observations
    role: child
    parent: admissions
    primary_key: observationid
    foreign_keys: [admission_id]      # direct FK column; importer expands to full compound key

Step 1 — patients (root)

No FK processing needed. Compound key tracked: ["patientid"].

{
  "objectName": "patients",
  "keyDimension": "patientid",
  "foreignKeyDimensions": null,
  "dimensionConfigurations": [
    { "dbColumnName": "patientid", "dimensionName": "patientid" },
    { "dbColumnName": "name",      "dimensionName": "name" }
  ]
}

Step 2 — admissions (child of patients)

Parent compound key: ["patientid"].

The admissions table has a column patient_id. Normalized: patientid == patientid → dimensionName is renamed from patient_id to patientid. foreignKeyDimensions is set to the full parent compound key ["patientid"].

Compound key tracked: ["patientid", "admissionid"].

{
  "objectName": "admissions",
  "keyDimension": "admissionid",
  "foreignKeyDimensions": ["patientid"],
  "dimensionConfigurations": [
    { "dbColumnName": "patient_id",   "dimensionName": "patientid"   },  ← renamed
    { "dbColumnName": "admissionid",  "dimensionName": "admissionid" },
    { "dbColumnName": "admit_date",   "dimensionName": "admit_date"  }
  ]
}

Step 3 — clinical_observations (child of admissions)

Parent compound key: ["patientid", "admissionid"].

Two columns are aligned:

• patient_id → normalize → patientid == patientid → renamed to patientid
• admission_id → normalize → admissionid == admissionid → renamed to admissionid

foreignKeyDimensions is set to the full parent compound key ["patientid", "admissionid"] (not just the direct admission_id).

Compound key tracked: ["patientid", "admissionid", "observationid"].

{
  "objectName": "clinical_observations",
  "keyDimension": "observationid",
  "foreignKeyDimensions": ["patientid", "admissionid"],
  "dimensionConfigurations": [
    { "dbColumnName": "patient_id",    "dimensionName": "patientid"    },  ← renamed
    { "dbColumnName": "admission_id",  "dimensionName": "admissionid"  },  ← renamed
    { "dbColumnName": "observationid", "dimensionName": "observationid"},
    { "dbColumnName": "value",         "dimensionName": "value"        }
  ]
}

Tables that do not carry grandparent FK columns

In a normalized (3NF) schema, clinical_observations may only have admission_id — no patient_id column. In that case:

• The importer still sets foreignKeyDimensions = ["patientid", "admissionid"]
• admissionid dimension exists (column admission_id was renamed) ✓
• patientid dimension does not exist — Xplain will reject the import

Options:

1. Add patient_id to the clinical_observations table in the database (denormalize the key chain).
2. Shorten the hierarchy — import admissions only (without going one level deeper).
3. Use admissions as the root object and import clinical_observations as its child.

---

Auto-Pilot Mode

Auto-pilot mode removes the need to write a YAML config by hand. It connects to your database, discovers all tables, infers primary keys and foreign-key relationships from column naming conventions, proposes a root/child hierarchy, writes a YAML file, and — after a single confirmation — executes the import.

Quick Start

# Using a DB config file
export XPLAIN_SESSION_ID=<your-session-id>
uv run xplain-import-autopilot --db-config conn.yaml

# Using inline flags
uv run xplain-import-autopilot \
    --db-type POSTGRESQL --db-host localhost --db-port 5432 \
    --db-user admin --db-password secret --db-name mydb

DB Config File Format

conn.yaml needs only the database: section (same format as a full import config):

database:
  type: POSTGRESQL
  host: localhost
  port: 5432
  user: postgres
  password: secret
  database: mydb
  schema: public   # optional — restrict to one schema

What Happens Step by Step

1. Connect — Verifies XOE session and database credentials.
2. Explore — Lists all tables in the schema, fetches a small sample of rows per table for type inference.
3. Infer relationships — Detects foreign keys from column names ending in _id. A column foo_id in table bar is treated as a FK to table foo (or its plural variant). Exact name matches get 90 % confidence; singular/plural matches get 75 %.
4. Suggest hierarchy — Tables with no outgoing FK become roots; others become children of their highest-confidence parent. Primary keys are inferred from column names (id, <table>_id, etc.).
5. Display — Prints a schema table, an inferred-relationships table, and a tree view of the suggested hierarchy, with confidence indicators coloured green/yellow/red.
6. Write YAML — Saves the suggested config to <db-name>_autopilot.yaml (override with --output-yaml). Low-confidence items are flagged as # WARNING comments at the top of the file.
7. Confirm — Prompts Proceed with import? [y/N] (skip with --yes).
8. Import — Runs the same pipeline as xplain-import.

Command-Line Reference

xplain-import-autopilot [OPTIONS]

Database connection (one of --db-config or all --db-* flags required):
  --db-config YAML        YAML file with a 'database:' section
  --db-type TYPE          POSTGRESQL | MYSQL | ORACLE | DB2 | MARIADB | DATABRICKS
  --db-host HOST
  --db-port PORT
  --db-user USER
  --db-password PASSWORD
  --db-name DBNAME
  --db-schema SCHEMA      Restrict table discovery to this schema

XOE session:
  --session-id ID         XOE JSESSIONID (or set XPLAIN_SESSION_ID env var)
  --url URL               XOE server URL (default: http://localhost:8080, or XPLAIN_SERVER_URL env var)

Autopilot options:
  --output-yaml PATH      Save suggested config here (default: <db-name>_autopilot.yaml)
  --max-tables N          Cap table discovery at N tables (default: 50)
  --sample-rows N         Rows fetched per table for type inference (default: 5)
  --yes                   Skip confirmation prompt
  --dry-run               Explore and print suggestion only — do not import
  --persist               Persist xtable configs and xview to XOE after import
  --persist-prefix PREFIX Prefix for saved config names (default: <db-name>)
  --log-level LEVEL       DEBUG | INFO | WARNING | ERROR (default: INFO)

Common Workflows

Preview without importing:

uv run xplain-import-autopilot --db-config conn.yaml --dry-run

Fully automated (no prompts, save configs to XOE):

uv run xplain-import-autopilot --db-config conn.yaml --yes --persist

Limit to a specific schema and cap table count:

uv run xplain-import-autopilot --db-config conn.yaml --db-schema analytics --max-tables 20

Review, edit, then import manually:

# 1. Generate the YAML (dry-run so nothing is imported yet)
uv run xplain-import-autopilot --db-config conn.yaml --dry-run

# 2. Edit the generated file
vi mydb_autopilot.yaml

# 3. Import using the regular CLI
uv run xplain-import --config mydb_autopilot.yaml

Reading the Output

Confidence colours in the hierarchy tree:

Colour	Meaning
Green	High confidence (≥ 85 % for FKs, ≥ 70 % for PKs)
Yellow	Medium confidence — review before importing
Red	Low confidence — manual correction recommended

YAML warnings: Any relationship with confidence below 80 % (FK) or 70 % (PK) appears as a # WARNING comment at the top of the generated file. Review these before running the import.

Limitations

• FK inference relies on column naming conventions (<table>_id). Columns that use non-standard naming won't be detected — edit the generated YAML to add them.
• Composite foreign keys are not currently inferred; add them manually in the YAML.
• Tables with no columns matching the PK heuristics fall back to the first column — check the warning and correct if needed.
• The --max-tables limit applies to the first N tables returned by the database driver; if the most important tables appear later, increase the limit or use --db-schema to narrow the search.

---

AI/Claude Skill

This repository includes an AI skill for natural language database imports. The skill allows Claude (or other AI agents) to generate YAML configurations from natural language descriptions.

Skill Location

skill/
├── SKILL.md                    # Main skill instructions
├── references/
│   └── config-reference.md     # YAML field reference
└── examples/
    ├── example.yaml            # Basic example
    └── productronica.yaml      # Multi-level example

Installing the Skill

For Clawdbot users:

# Copy to your workspace skills directory
cp -r skill/ ~/.clawdbot/workspace/skills/xplain-import/

# Or symlink for development
ln -s $(pwd)/skill ~/.clawdbot/workspace/skills/xplain-import

For Claude Code users: Add the skill path to your project's .claude/settings.json or reference it in your AGENTS.md.

Using the Skill

Once installed, describe your data structure in natural language:

Example prompts:

• "Import customers and their orders from PostgreSQL into xplain"
• "Create an xplain import for products with categories as a computed dimension"
• "Load boards → components → alerts hierarchy from the productronica database"

The AI will:

1. Ask clarifying questions about database connection and structure
2. Generate a YAML configuration file
3. Optionally run the import with --dry-run first
4. Execute the import with --persist to save configurations

Skill Triggers

The skill activates on phrases like:

• "import into xplain"
• "xplain import"
• "create xtables"
• "load database into XOE"
• Describing table structures with parent-child relationships

---

License

See project repository for license information.

---

Support

For issues or questions:

• Check the Troubleshooting section
• Review example.yaml for reference configuration
• Examine logs with --log-level DEBUG for detailed diagnostics