smartclickhouse/readme.md

# @push.rocks/smartclickhouse

A TypeScript-based ODM (Object-Document Mapper) for ClickHouse databases, with support for creating and managing tables and handling time-series data.

## Install

To install `@push.rocks/smartclickhouse`, use the following command with npm:

```sh
npm install @push.rocks/smartclickhouse --save
```

Or with yarn:

```sh
yarn add @push.rocks/smartclickhouse
```

This will add the package to your project's dependencies.

## Usage

`@push.rocks/smartclickhouse` is an advanced ODM (Object Document Mapper) module designed for seamless interaction with ClickHouse databases leveraging the capabilities of TypeScript for strong typing and enhanced developer experience. Below is a comprehensive guide to using the package in various scenarios.

### Setting Up and Starting the Connection

To begin using `@push.rocks/smartclickhouse`, you need to establish a connection with the ClickHouse database. This involves creating an instance of `SmartClickHouseDb` and starting it:

```typescript
import { SmartClickHouseDb } from '@push.rocks/smartclickhouse';

// Create a new instance of SmartClickHouseDb with your ClickHouse database details
const dbInstance = new SmartClickHouseDb({
  url: 'http://localhost:8123', // URL of ClickHouse instance
  database: 'yourDatabase',     // Database name you want to connect to
  username: 'default',          // Optional: Username for authentication
  password: 'password',         // Optional: Password for authentication
  unref: true                   // Optional: Allows service to exit while awaiting database startup
});

// Start the instance to establish the connection
await dbInstance.start();
```

### Working with Time Data Tables

`smartclickhouse` allows handling of time-series data through `TimeDataTable`, automating tasks such as table creation and data insertion.

#### Creating or Accessing a Table

To create a new time data table or access an existing one:

```typescript
const tableName = 'yourTimeDataTable'; // Name of the table you want to access or create
const table = await dbInstance.getTable(tableName);
```

#### Adding Data to the Table

Once you have the table instance, you can insert data into it:

```typescript
await table.addData({
  timestamp: Date.now(),       // Timestamp in milliseconds
  message: 'A log message.',   // Arbitrary data field
  temperature: 22.5,           // Another example field
  tags: ['tag1', 'tag2']       // An example array field
});
```

The `addData` method is designed to be flexible, allowing insertion of various data types and automatically managing table schema adjustments.

### Advanced Usage and Custom Data Handling

`smartclickhouse` supports custom data types and complex data structures. For instance, to add support for nested objects or custom data processing before insertion, you might need to extend existing classes or customize the `addData` method to fit your needs.

#### Custom Data Processing

To handle complex data structures or to perform custom data processing before insertion, you might need to modify the `addData` method. Below is an example of extending the `SmartClickHouseDb` method:

```typescript
class CustomClickHouseDb extends SmartClickHouseDb {
  public async addCustomData(tableName: string, data: any) {
    const table = await this.getTable(tableName);
    const customData = {
      ...data,
      processedAt: Date.now(),
      customField: 'customValue',
    };
    await table.addData(customData);
  }
}

const customDbInstance = new CustomClickHouseDb({
  url: 'http://localhost:8123',
  database: 'yourDatabase',
});

await customDbInstance.start();

await customDbInstance.addCustomData('customTable', {
  message: 'Test message',
  randomField: 123456,
});
```

### Bulk Data Insertion

`@push.rocks/smartclickhouse` supports efficient bulk data insertion mechanisms. This feature is useful when you need to insert a large amount of data in a single operation.

```typescript
const bulkData = [
  { timestamp: Date.now(), message: 'Message 1', temperature: 20.1 },
  { timestamp: Date.now(), message: 'Message 2', temperature: 21.2 },
  // Additional data entries...
];

await table.addData(bulkData);
```

### Querying Data

Fetching data from the ClickHouse database includes operations such as retrieving the latest entries, entries within a specific timestamp range, or streaming new entries.

#### Retrieving the Last N Entries

To retrieve the last `N` number of entries:

```typescript
const latestEntries = await table.getLastEntries(10);
console.log('Latest Entries:', latestEntries);
```

#### Retrieving Entries Newer than a Specific Timestamp

To retrieve entries that are newer than a specific timestamp:

```typescript
const timestamp = Date.now() - 60000; // 1 minute ago
const newEntries = await table.getEntriesNewerThan(timestamp);
console.log('New Entries:', newEntries);
```

#### Retrieving Entries Between Two Timestamps

To retrieve entries between two timestamps:

```typescript
const startTimestamp = Date.now() - 120000; // 2 minutes ago
const endTimestamp = Date.now() - 5000; // 5 seconds ago
const entriesBetween = await table.getEntriesBetween(startTimestamp, endTimestamp);
console.log('Entries Between:', entriesBetween);
```

### Managing and Deleting Data

The module provides functionality for managing and deleting data within the ClickHouse database.

#### Deleting Old Entries

You can delete entries older than a specified number of days:

```typescript
// Ensure there are entries before deletion
let entries = await table.getLastEntries(1000);
console.log('Entries before deletion:', entries.length);

// Delete all entries older than now
await table.deleteOldEntries(0);

// Verify the entries are deleted
entries = await table.getLastEntries(1000);
console.log('Entries after deletion:', entries.length);
```

#### Deleting the Entire Table

To delete the entire table and all its data:

```typescript
await table.delete();

// Verify table deletion
const result = await dbInstance.clickhouseHttpClient.queryPromise(`
  SHOW TABLES FROM ${dbInstance.options.database} LIKE '${table.options.tableName}'
`);
console.log('Table exists after deletion:', result.length === 0);
```

### Observing Real-Time Data

To observe new entries in real-time, you can stream new data entries using the RxJS Observable:

```typescript
const stream = table.watchNewEntries();

const subscription = stream.subscribe((entry) => {
  console.log('New entry:', entry);
});

// Simulate adding new entries
let i = 0;
while (i < 10) {
  await table.addData({
    timestamp: Date.now(),
    message: `streaming message ${i}`,
  });
  i++;
  await new Promise((resolve) => setTimeout(resolve, 1000)); // Add a delay to simulate real-time data insertion
}

subscription.unsubscribe();
```

This method allows continuous monitoring of data changes and integrating the collected data into other systems for real-time applications.

### Comprehensive Feature Set

While the examples provided cover the core functionalities of the `@push.rocks/smartclickhouse` module, it also offers a wide range of additional features, including:

- **Error Handling and Reconnection Strategies**: Robust error handling mechanisms ensure your application remains reliable. Automatic reconnection strategies help maintain persistent connections with the ClickHouse database.
- **Materialized Views and MergeTree Engines**: Support for ClickHouse-specific features such as materialized views and aggregating MergeTree engines, enhancing the module's capabilities in handling large-scale data queries and management.
- **Efficient Data Handling**: Techniques for managing and querying large time-series datasets, providing optimal performance and reliability.

### Contribution

Contributions to `@push.rocks/smartclickhouse` are welcome. Whether through submitting issues, proposing improvements, or adding to the codebase, your input is valuable. The project is designed to be open and accessible, striving for a high-quality, community-driven development process.

To contribute:

1. Fork the repository.
2. Create a new branch (`git checkout -b feature-branch`).
3. Commit your changes (`git commit -am 'Add some feature'`).
4. Push to the branch (`git push origin feature-branch`).
5. Create a new Pull Request.

The above scenarios cover the essential functionality and the more advanced use cases of `@push.rocks/smartclickhouse`, providing a comprehensive guide to utilizing the module into your projects. Happy coding!

## License and Legal Information

This repository contains open-source code that is licensed under the MIT License. A copy of the MIT License can be found in the [license](license) file within this repository. 

**Please note:** The MIT License does not grant permission to use the trade names, trademarks, service marks, or product names of the project, except as required for reasonable and customary use in describing the origin of the work and reproducing the content of the NOTICE file.

### Trademarks

This project is owned and maintained by Task Venture Capital GmbH. The names and logos associated with Task Venture Capital GmbH and any related products or services are trademarks of Task Venture Capital GmbH and are not included within the scope of the MIT license granted herein. Use of these trademarks must comply with Task Venture Capital GmbH's Trademark Guidelines, and any usage must be approved in writing by Task Venture Capital GmbH.

### Company Information

Task Venture Capital GmbH  
Registered at District court Bremen HRB 35230 HB, Germany

For any legal inquiries or if you require further information, please contact us via email at hello@task.vc.

By using this repository, you acknowledge that you have read this section, agree to comply with its terms, and understand that the licensing of the code does not imply endorsement by Task Venture Capital GmbH of any derivative works.