# Moose / Getting Started / From Clickhouse Documentation – Python

## Included Files
1. moose/getting-started/from-clickhouse/from-clickhouse.mdx

## from-clickhouse

Source: moose/getting-started/from-clickhouse/from-clickhouse.mdx

# Use Moose with Your Existing ClickHouse

<div className="flex flex-wrap md:flex-nowrap gap-4 my-4">

</div>

## What This Guide Does

This guide sets up a local ClickHouse development environment that mirrors your production database and enables code-first schema management:

1. **Introspect** your remote ClickHouse tables and generate TypeScript/Python data models
2. **Create** a local ClickHouse instance with your exact table schemas
3. **Seed** your local database with production data (optional)
4. **Build** APIs and pipelines on top of your ClickHouse data in code

## How It Works

**Local Development:**
- Your production ClickHouse remains untouched
- You get a local ClickHouse instance that copies your remote table schemas
- All development happens locally with hot-reload

**Production Deployment:**
- When you deploy your code, it connects to your remote ClickHouse
- Any new tables, materialized views, or schema changes you create in code are automatically migrated to your target database
- Your existing data and tables remain intact

## Prerequisites

<br />

## Step 1: Install Moose
Install the Moose CLI globally to your system:

```bash filename="Terminal" copy
bash -i <(curl -fsSL https://fiveonefour.com/install.sh) moose
```

After installation, you'll use `moose init` to create a new project that automatically connects to your ClickHouse and generates all the code you need.

## Step 2: Create Your Project

Use the ClickHouse Playground tab to try it out!

<Tabs.Tab>

```bash filename="Initialize new project" copy
# Option 1: Provide connection string directly
moose init my-project --from-remote <YOUR_CLICKHOUSE_CONNECTION_STRING> --language python

# Option 2: Run without connection string for interactive setup
moose init my-project --from-remote --language python
```

**Connection String Format:**
```
https://username:password@host:port/?database=database_name
```

If you don't provide a connection string, Moose will guide you through an interactive setup process where you'll be prompted to enter:
- **Host and port** (e.g., `https://your-service-id.region.clickhouse.cloud:8443`)
- **Username** (usually `default`)
- **Password** (your ClickHouse password)
- **Database name** (optional, defaults to `default`)

This is perfect if you're not sure about your connection details or prefer a guided experience.

Moose will create a complete project structure with:
- **Data models**: TypeScript/Python classes for every table in your ClickHouse
- **Type definitions**: Full type safety for all your data
- **Development environment**: Local ClickHouse instance that mirrors your production schema
- **Build tools**: Everything configured and ready to go

- Make sure you are using the `HTTPS` connection string, not the `HTTP` connection string.
- Make sure the port is correct. For `HTTPS` the default is `8443`
- The default username is `default`

See the section: <a href="#connect-to-your-remote-clickhouse" className="text-blue-500 hover:underline">Connect to your remote ClickHouse</a>.

</Tabs.Tab>
<Tabs.Tab>

```bash filename="Initialize new project" copy
# Generate code models from your existing ClickHouse tables
moose init my-project --from-remote https://explorer:@play.clickhouse.com:443/?database=default --language python
```

</Tabs.Tab>

<br />

```bash filename="Install dependencies" copy
cd my-project
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
```

You should see: `Successfully generated X models from ClickHouse tables`

### Explore Your Generated Models

Check what Moose created from your tables in the `app/main.py` file:

<FileTree.Folder name="app" defaultOpen>
<FileTree.File name="main.py" />
</FileTree.Folder>

<div>Your generated table models are imported here so Moose can detect them.</div>
<div className="text-sm mt-2">Learn more about export pattern: <a href="/moose/local-dev#hot-reloading-development" className="text-blue-500 hover:underline">local development</a> / <a href="/moose/migrate" className="text-blue-500 hover:underline">hosted</a></div>

### If your database includes ClickPipes/PeerDB (CDC) tables

As noted above, when you use `moose init --from-remote`, Moose introspects your database. If it detects CDC‑managed tables (e.g., PeerDB/ClickPipes with fields like `_peerdb_synced_at`, `_peerdb_is_deleted`, `_peerdb_version`), it marks those as `EXTERNALLY_MANAGED` and writes them into a dedicated external models file. Your root file is updated to load these models automatically.

  <FileTree.Folder name="app" defaultOpen>
    <FileTree.File name="main.py" />
    <FileTree.File name="external_models.py" />
  </FileTree.Folder>

This separation is a best‑effort by the CLI to keep clearly CDC‑owned tables external. For other tables you don’t want Moose to manage, set the lifecycle to external and move them into the external file. See: 
- [External Tables](/moose/olap/external-tables) documentation for more information on how external tables work.
- [DB Pull](/moose/olap/db-pull) for keeping models in sync with the remote schema.

<br />

## Step 3: Start Development

Start your development server. This spins up a local ClickHouse instance that perfectly mirrors your production schema:

```bash filename="Start your dev server" copy
moose dev
```

**What happens when you run `moose dev`:**
- 🏗️ Creates a local ClickHouse instance with your exact table schemas in your project code
- 🔄 Hot-reloads migrations to your local infrastructure as you save code changes
- 🚀 Starts a web server for building APIs

Your production ClickHouse remains completely untouched. This is a separate, local development environment.

```txt
       Created docker compose file
⡗ Starting local infrastructure
  Successfully started containers
     Validated clickhousedb-1 docker container
     Validated redpanda-1 docker container
  Successfully validated red panda cluster
     Validated temporal docker container
  Successfully ran local infrastructure
      Node Id: my-analytics-app::b15efaca-0c23-42b2-9b0c-642105f9c437
      Starting development mode
      Watching "/path/to/my-analytics-app/app"
       Started Webserver.

  Next Steps   

💻 Run the moose 👉 `ls` 👈 command for a bird's eye view of your application and infrastructure

📥 Send Data to Moose
	Your local development server is running at: http://localhost:4000/ingest
```

Don't see this output? [Check out the troubleshooting section](#troubleshooting)

### Seed Your Local Database (Optional)
Copy real data from your production ClickHouse to your local development environment. This gives you realistic data to work with during development.

**Why seed?** Your local database starts empty. Seeding copies real data so you can:
- Test with realistic data volumes
- Debug with actual production data patterns
- Develop features that work with real data structures

<Tabs.Tab>
```bash filename="Terminal" copy
moose seed clickhouse --clickhouse-url <YOUR_CLICKHOUSE_CONNECTION_STRING> --limit 100000
```

**Connection String Format:**
The connection string must use ClickHouse native protocol:
```bash
# ClickHouse native protocol (secure connection)
clickhouse://username:password@host:9440/database
```

**Note:** Data transfer uses ClickHouse's native TCP protocol via `remoteSecure()`. The remote server must have the native TCP port accessible. The command automatically handles table mismatches gracefully.
</Tabs.Tab>
<Tabs.Tab>
```bash filename="Terminal" copy
moose seed clickhouse --clickhouse-url clickhouse://explorer:@play.clickhouse.com:9440/default --limit 100000
```
</Tabs.Tab>
<Tabs.Tab>
```bash filename="Terminal" copy
# You can omit --clickhouse-url by setting an env var
export MOOSE_SEED_CLICKHOUSE_URL='clickhouse://username:password@host:9440/database'

# copy a limited number of rows (batched under the hood)
moose seed clickhouse --limit 100000
```
</Tabs.Tab>

- `--limit` and `--all` are mutually exclusive
- `--all` can be used to copy the entire table(s), use with caution as it can be very slow and computationally intensive.
- Large copies are automatically batched to avoid remote limits; you’ll see per-batch progress.
- If you stop with Ctrl+C, the current batch finishes and the command exits gracefully.

**Expected Output:**
```bash
✓ Database seeding completed
Seeded 'local_db' from 'remote_db'

✓ table1: copied from remote
⚠️  table2: skipped (not found on remote)
✓ table3: copied from remote
```

**Troubleshooting:**
- Tables that don't exist on remote are automatically skipped with warnings
- Use `--table <name>` to seed a specific table that exists in both databases
- Check `moose ls table` to see your local tables

<br />

## Step 4: Build Your First API
Now that you have your data models, let's build something useful! You can create APIs, materialized views, and applications with full type safety.

- **REST APIs** that expose your ClickHouse data to frontend applications
- **Materialized Views** for faster queries and aggregations
- **Streaming pipelines** for real-time data processing
- **Full-stack applications** with your ClickHouse data as the backend

### Add APIs

Build REST APIs on top of your existing tables to expose your data to your user-facing apps. This is a great way to get started with Moose without changing any of your existing pipelines.

Check out the MooseAPI module for more information on building APIs with Moose.

### Build Materialized Views 
Build materialized views on top of your existing tables to improve query performance. If you have Materialized Views in your ClickHouse, you can use Moose to build new Materialized Views on top of your existing tables, or to migrate your existing Materialized Views to Moose.

Check out the MooseOLAP module for more information on building Materialized Views with Moose.

<br />

## Known Limitations

Some advanced ClickHouse features may not be fully supported yet. Join the Moose Slack and let us know if you have any issues, feedback, or requests.

**What we're working on:**
- **Selective table import** (currently imports all tables)
- **Default value annotations** 

<br />

## Troubleshooting
### Error: Failed to connect to ClickHouse

This guide shows exactly where to find your host, port, username, and password, and how to construct a valid HTTPS connection string.

<Tabs.Tab>
1. Log into your [ClickHouse Cloud console](https://clickhouse.cloud/)
2. Open your service details page
3. Click "Connect" in the sidebar
<div className="flex justify-center p-10">

</div>
4. Select the `HTTPS` tab and copy the values shown
   - **Host**: e.g. `your-service-id.region.clickhouse.cloud`
   - **Port**: usually `8443`
   - **Username**: usually `default`
   - **Password**: the password you configured
<div className="flex justify-center p-10">

</div>
5. Build your connection string:

```txt
https://USERNAME:PASSWORD@HOST:PORT/?database=DATABASE_NAME
```

6. Example (with placeholders):

```txt
https://default:your_password@your-service-id.region.clickhouse.cloud:8443/?database=default
```

7. Optional: Test with curl

```bash
curl --user "USERNAME:PASSWORD" --data-binary "SELECT 1" https://HOST:PORT
```
</Tabs.Tab>
<Tabs.Tab>
### Self-hosted or Docker
- Check your server config (usually `/etc/clickhouse-server/config.xml`)
  - `<http_port>` default: `8123`
  - `<https_port>` default: `8443`
- Check users in `/etc/clickhouse-server/users.xml` or `users.d/`
- For Docker, check environment variables in your compose/run config:
  - `CLICKHOUSE_USER`, `CLICKHOUSE_PASSWORD`, `CLICKHOUSE_DB`

Build the HTTPS connection string with your values:

```txt
https://USERNAME:PASSWORD@HOST:8443/?database=DB
```

If you only have HTTP enabled, enable HTTPS or use an HTTPS proxy; Moose init expects an HTTPS URL for remote introspection.
</Tabs.Tab>

### `moose dev` fails to start

Double check Docker is running and you do not have any port conflicts.

- ClickHouse local runs on port `18123`
- Your local webserver runs on port `4000`
- Your local management API runs on port `5001`

## What's Next?