### Setup Virtual Environment and Install Dependencies

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/examples/full_text_search/README.md

This snippet details the process of creating a Python virtual environment and installing the necessary project dependencies from a requirements file. It ensures a clean and isolated environment for running the application.

```bash
virtualenv -p python3.6 env
source env/bin/activate
pip install -r requirements.txt
```

--------------------------------

### Usage Example for Aliased/Materialized Fields

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/field_options.md

Provides a practical example of instantiating a model with aliased/materialized fields, inserting it into the database, and selecting data. It highlights how to retrieve all fields versus specific fields when aliased or materialized columns are present.

```python
obj = Event(created=datetime.now(), name='MyEvent')
db = Database('my_test_db')
db.insert([obj])
# All values will be retrieved from database
db.select('SELECT created, created_date, username, name FROM $db.event', model_class=Event)
# created_date and username will contain a default value
db.select('SELECT * FROM $db.event', model_class=Event)
```

--------------------------------

### Configure SummingMergeTree Engine with Summing Columns

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/table_engines.md

Details the setup of a SummingMergeTree engine, allowing for the optional specification of columns to be summed.

```python
engine = SummingMergeTree('EventDate', ('OrderID', 'EventDate', 'BannerID'),
                          summing_cols=('Shows', 'Clicks', 'Cost'))
```

--------------------------------

### Configure Buffer Engine with BufferModel

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/table_engines.md

Details the setup of the Buffer engine, which is used in conjunction with a BufferModel, and shows how to specify additional buffer parameters.

```python
class PersonBuffer(BufferModel, Person):

    engine = Buffer(Person)

    engine = Buffer(Person, num_layers=16, min_time=10,
                    max_time=100, min_rows=10000, max_rows=1000000,
                    min_bytes=10000000, max_bytes=100000000)
```

--------------------------------

### Load Downloaded Texts into ClickHouse Database

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/examples/full_text_search/README.md

Runs a Python script to populate the ClickHouse database with the content of the downloaded ebooks. This step prepares the data for indexing and searching.

```python
python load.py
```

--------------------------------

### Perform Full Text Search with Wildcards

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/examples/full_text_search/README.md

Demonstrates performing a full-text search query using wildcard characters ('*') to match any word in the sequence. This allows for more flexible searching.

```bash
python search.py "much * than"
```

--------------------------------

### Get Active Partitions and Manage Them - Python

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/system_models.md

Demonstrates how to retrieve active partitions for a database using the SystemPart model and then perform actions like freezing or dropping a partition. Requires the 'infi.clickhouse_orm' library and a configured ClickHouse database connection.

```python
from infi.clickhouse_orm import Database, SystemPart
db = Database('my_test_db', db_url='http://192.168.1.1:8050', username='scott', password='tiger')
partitions = SystemPart.get_active(db, conditions='')  # Getting all active partitions of the database
if len(partitions) > 0:
    partitions = sorted(partitions, key=lambda obj: obj.name)  # Partition name is YYYYMM, so we can sort so
    partitions[0].freeze()  # Make a backup in /opt/clickhouse/shadow directory
    partitions[0].drop()  # Dropped partition
```

--------------------------------

### Download Ebooks for Full Text Search

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/examples/full_text_search/README.md

Executes a Python script to download classical books from Project Gutenberg. These downloaded texts serve as the dataset for the full-text search functionality.

```python
python download_ebooks.py
```

--------------------------------

### ClickHouse Schema Migrations with Python

Source: https://context7.com/infinidat/infi.clickhouse_orm/llms.txt

Shows how to manage ClickHouse schema changes using the migrations module from infi.clickhouse_orm. This example defines an initial migration file that includes creating a `User` model with a MergeTree engine and specifies the `CreateTable` operation.

```python
# migrations/0001_initial.py
from infi.clickhouse_orm import migrations
from infi.clickhouse_orm import Model, DateField, UInt32Field, StringField, MergeTree

class User(Model):
    date = DateField()
    user_id = UInt32Field()
    username = StringField()

    engine = MergeTree(
        partition_key=('toYYYYMM(date)',),
        order_by=('date', 'user_id')
    )

operations = [
    migrations.CreateTable(User)
]
```

--------------------------------

### Build infi.clickhouse_orm Development Version

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/contributing.md

This snippet details the steps to build and install the development version of the infi.clickhouse_orm package. It requires 'infi.projector' to be installed and assumes the project has been cloned.

```shell
easy_install -U infi.projector
cd infi.clickhouse_orm
projector devenv build
python setup.py install
```

--------------------------------

### Import ORM Classes Directly in Python

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/whats_new_in_version_2.md

Demonstrates the convenient way to import all necessary ORM classes directly from the top-level `infi.clickhouse_orm` package, simplifying setup and reducing import statements.

```python
from infi.clickhouse_orm import Database, Model, StringField, DateTimeField, MergeTree
```

--------------------------------

### Insert Data into Buffer Model

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/table_engines.md

Provides an example of inserting data into a Buffer model, demonstrating how ClickHouse properly handles data insertions via the Buffer engine.

```python
db.create_table(PersonBuffer)
suzy = PersonBuffer(first_name='Suzy', last_name='Jones')
dan = PersonBuffer(first_name='Dan', last_name='Schwartz')
db.insert([dan, suzy])
```

--------------------------------

### Using Parametric ClickHouse Functions

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/expressions.md

Demonstrates the syntax for using ClickHouse aggregate functions that accept parameters, using two sets of brackets: the first for parameters and the second for arguments. Examples include `topK` and `quantiles`.

```python
# Most common last names
F.topK(5)(Person.last_name)
# Find 90th, 95th and 99th percentile of heights
F.quantiles(0.9, 0.95, 0.99)(Person.height)
```

--------------------------------

### Perform Full Text Search for Word Sequences

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/examples/full_text_search/README.md

Initiates a full-text search query for a specific sequence of words. The search functionality supports exact phrases and wildcard characters.

```bash
python search.py "cheshire cat"
```

--------------------------------

### Run Tests with Tox for infi.clickhouse_orm

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/contributing.md

Instructions to run tests for infi.clickhouse_orm using tox. This method provides a more isolated and comprehensive testing environment. It requires tox to be installed.

```shell
pip install tox
tox
```

--------------------------------

### Query CPU Statistics (Python)

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/README.md

Demonstrates querying the ClickHouse database for CPU usage statistics. It includes examples of filtering data to calculate busy time percentages and aggregating data to find the average CPU usage per core. Utilizes the ORM's query builder and aggregation features.

```python
# Calculate what percentage of the time CPU 1 was over 95% busy
queryset = CPUStats.objects_in(db)
total = queryset.filter(CPUStats.cpu_id == 1).count()
busy = queryset.filter(CPUStats.cpu_id == 1, CPUStats.cpu_percent > 95).count()
print('CPU 1 was busy {:.2f}% of the time'.format(busy * 100.0 / total))

# Calculate the average usage per CPU
for row in queryset.aggregate(CPUStats.cpu_id, average=F.avg(CPUStats.cpu_percent)):
    print('CPU {row.cpu_id}: {row.average:.2f}%'.format(row=row))
```

--------------------------------

### Filter and Exclude with Basic Conditions - Python

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/querysets.md

Shows how to use the `filter` and `exclude` methods to refine querysets. These methods return new queryset instances with added conditions. Example uses LIKE and date comparison.

```python
qs = Person.objects_in(database)
qs = qs.filter(F.like(Person.first_name, 'V%')).exclude(Person.birthday < '2000-01-01')
print(qs.conditions_as_sql())
```

--------------------------------

### Creating Reusable Expressions (Custom Functions)

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/expressions.md

Shows how to create new, reusable "functions" by combining existing ORM expressions. This allows for creating custom building blocks for complex operations, demonstrated with a string normalization example.

```python
def normalize_string(s):
    return F.replaceAll(F.upper(F.trimBoth(s)), ' ', '_')

class Event(Model):
    code = StringField()
    normalized_code = StringField(materialized=normalize_string(code))
```

--------------------------------

### Construct Compound ClickHouse Query for Word Sequence

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/examples/full_text_search/README.md

Builds a compound ClickHouse query to find a sequence of stemmed words. This involves creating subqueries for subsequent words and filtering based on the index incrementing by one, ensuring word order.

```python
subquery = Fragment.objects_in(db).filter(stem='novemb').only(Fragment.document, Fragment.idx)
query = query.filter(F.isIn((Fragment.document, Fragment.idx + 1), subquery))
```

--------------------------------

### Counting and Checking Existence of QuerySet Results

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/querysets.md

These examples show how to determine the number of objects in a QuerySet or check if any objects exist. The `count()` method retrieves the total number of matching records, while boolean evaluation (`bool(qs)` or simply `qs`) can efficiently check for the presence of at least one record.

```python
Person.objects_in(database).count()
```

```python
if qs.count(): ...
```

```python
if bool(qs): ...
```

```python
if qs: ...
```

--------------------------------

### Define ClickHouse Model and Connect (Python)

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/README.md

Defines a model class for CPU statistics and establishes a connection to the ClickHouse database. It specifies fields like timestamp, CPU ID, and CPU percentage, and sets the table engine to Memory. This is the initial setup for data persistence.

```python
from infi.clickhouse_orm import Database, Model, DateTimeField, UInt16Field, Float32Field, Memory, F

class CPUStats(Model):

    timestamp = DateTimeField()
    cpu_id = UInt16Field()
    cpu_percent = Float32Field()

    engine = Memory()

db = Database('demo')
db.create_table(CPUStats)
```

--------------------------------

### Get Start of Week

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Returns the timestamp representing the beginning of the week for a given date. The `mode` parameter controls the definition of the start of the week (e.g., Sunday or Monday).

```python
from infi.clickhouse_orm.functions import toStartOfWeek

# Example usage:
start_of_week_default = toStartOfWeek('2023-10-27') # Default mode
print(start_of_week_default)  # Output depends on default mode (usually Monday)

start_of_week_monday = toStartOfWeek('2023-10-27', mode=1)
print(start_of_week_monday)  # Explicitly Monday
```

--------------------------------

### Configure MergeTree with Custom Partitioning

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/table_engines.md

Demonstrates setting up custom partitioning for MergeTree family engines using `partition_key` and `order_by` parameters, including monthly partitioning.

```python
engine = ReplacingMergeTree(order_by=('OrderID', 'EventDate', 'BannerID'), ver_col='Version',
                                partition_key=(F.toYYYYMM(EventDate), 'BannerID'))
```

--------------------------------

### Get Start of ISO Year

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Returns the timestamp representing the beginning of the ISO year for a given date. The ISO year starts on the Monday of the week containing the first Thursday of the year.

```python
from infi.clickhouse_orm.functions import toStartOfISOYear

# Example usage:
start_of_iso_year = toStartOfISOYear('2023-10-27')
print(start_of_iso_year)  # Output: 2023-01-02 00:00:00 (or similar, depending on exact ISO year definition)
```

--------------------------------

### Get Start of Quarter

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Returns the timestamp representing the beginning of the quarter for a given date. Useful for quarterly analysis.

```python
from infi.clickhouse_orm.functions import toStartOfQuarter

# Example usage:
start_of_quarter = toStartOfQuarter('2023-10-27')
print(start_of_quarter)  # Output: 2023-10-01 00:00:00
```

--------------------------------

### Get Start of Hour

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Returns the timestamp representing the beginning of the hour (e.g., HH:00:00) for a given timestamp. Useful for hourly aggregations.

```python
from infi.clickhouse_orm.functions import toStartOfHour

# Example usage:
start_of_hour = toStartOfHour('2023-10-27 15:30:45')
print(start_of_hour)  # Output: 2023-10-27 15:00:00
```

--------------------------------

### Get Start of Day

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Returns the timestamp representing the beginning of the day (00:00:00) for a given date or timestamp. Useful for daily aggregations.

```python
from infi.clickhouse_orm.functions import toStartOfDay

# Example usage:
start_of_day = toStartOfDay('2023-10-27 15:30:45')
print(start_of_day)  # Output: 2023-10-27 00:00:00
```

--------------------------------

### Get Start of Year

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Returns the timestamp representing the beginning of the year (January 1st, 00:00:00) for a given date. Useful for yearly analysis.

```python
from infi.clickhouse_orm.functions import toStartOfYear

# Example usage:
start_of_year = toStartOfYear('2023-10-27')
print(start_of_year)  # Output: 2023-01-01 00:00:00
```

--------------------------------

### Get Start of Month

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Returns the timestamp representing the beginning of the month (e.g., YYYY-MM-01 00:00:00) for a given date. Useful for monthly analysis.

```python
from infi.clickhouse_orm.functions import toStartOfMonth

# Example usage:
start_of_month = toStartOfMonth('2023-10-27')
print(start_of_month)  # Output: 2023-10-01 00:00:00
```

--------------------------------

### Get Start of Minute

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Returns the timestamp representing the beginning of the minute (e.g., HH:MM:00) for a given timestamp. Useful for minute-level aggregations.

```python
from infi.clickhouse_orm.functions import toStartOfMinute

# Example usage:
start_of_minute = toStartOfMinute('2023-10-27 15:30:45')
print(start_of_minute)  # Output: 2023-10-27 15:30:00
```

--------------------------------

### Get Start of Ten Minutes

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Returns the timestamp representing the beginning of the 10-minute interval containing the given timestamp. Useful for 10-minute data aggregation.

```python
from infi.clickhouse_orm.functions import toStartOfTenMinutes

# Example usage:
start_of_interval = toStartOfTenMinutes('2023-10-27 15:37:20')
print(start_of_interval)  # Output: 2023-10-27 15:30:00
```

--------------------------------

### Get Start of Five Minutes

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Returns the timestamp representing the beginning of the 5-minute interval containing the given timestamp. Useful for 5-minute data aggregation.

```python
from infi.clickhouse_orm.functions import toStartOfFiveMinute

# Example usage:
start_of_interval = toStartOfFiveMinute('2023-10-27 15:37:20')
print(start_of_interval)  # Output: 2023-10-27 15:35:00
```

--------------------------------

### Convert to UInt64 or Null

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

No description

--------------------------------

### Database Connection and Initialization

Source: https://context7.com/infinidat/infi.clickhouse_orm/llms.txt

Connect to a ClickHouse database, manage tables, and initialize the ORM. Tables can be automatically created or dropped as needed.

```APIDOC
## Database Connection and Initialization

### Description
Connect to a ClickHouse database and manage tables. The ORM can automatically create tables if they do not exist.

### Method
N/A (This is a setup and initialization guide)

### Endpoint
N/A

### Parameters
#### Path Parameters
None

#### Query Parameters
None

#### Request Body
None

### Request Example
```python
from infi.clickhouse_orm import Database, Model, DateTimeField, UInt16Field, Float32Field, Memory

# Initialize database connection - database is created automatically
db = Database('demo',
              db_url='http://localhost:8123/',
              username='default',
              password='',
              readonly=False,
              autocreate=True,
              timeout=60)

# Define a model
class CPUStats(Model):
    timestamp = DateTimeField()
    cpu_id = UInt16Field()
    cpu_percent = Float32Field()
    engine = Memory()

# Create the table
db.create_table(CPUStats)

# Drop the table if needed
db.drop_table(CPUStats)

# Check if table exists
if db.does_table_exist(CPUStats):
    print("Table exists")

# Drop entire database
db.drop_database()
```

### Response
#### Success Response (200)
N/A (This section describes code execution, not API responses)

#### Response Example
N/A
```

--------------------------------

### Get Start of Fifteen Minutes

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Returns the timestamp representing the beginning of the 15-minute interval containing the given timestamp. Useful for 15-minute data aggregation.

```python
from infi.clickhouse_orm.functions import toStartOfFifteenMinutes

# Example usage:
start_of_interval = toStartOfFifteenMinutes('2023-10-27 15:37:20')
print(start_of_interval)  # Output: 2023-10-27 15:30:00
```

--------------------------------

### Get Relative Day Number

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Calculates the relative number of days from a reference point (often the start of the year or month). An optional timezone can be provided for accuracy. Useful for sequencing or comparing day counts.

```python
from infi.clickhouse_orm.functions import toRelativeDayNum

# Example usage:
relative_day = toRelativeDayNum('2023-10-27')
print(relative_day)  # Output depends on the ClickHouse version and context

relative_day_tz = toRelativeDayNum('2023-10-27', timezone='UTC')
print(relative_day_tz)
```

--------------------------------

### Configure Simple ClickHouse Table Engines

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/table_engines.md

Demonstrates the configuration of simple ClickHouse table engines like TinyLog, Log, and Memory, which do not require any parameters.

```python
engine = TinyLog()

engine = Log()

engine = Memory()
```

--------------------------------

### Get Monday of the Week

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Returns the date of the Monday of the week for a given date. This is useful for week-based grouping or reporting.

```python
from infi.clickhouse_orm.functions import toMonday

# Example usage:
monday_date = toMonday('2023-10-27')
print(monday_date)  # Output: 2023-10-23
```

--------------------------------

### Configure ReplicatedMergeTree Engine for Data Replication

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/table_engines.md

Illustrates how to create a replicated engine (e.g., ReplicatedMergeTree) by adding `replica_table_path` and `replica_name` parameters for data replication.

```python
engine = MergeTree('EventDate', ('CounterID', 'EventDate'),
                       replica_table_path='/clickhouse/tables/{layer}-{shard}/hits',
                       replica_name='{replica}')
```

--------------------------------

### Executing Raw SQL Queries with Python

Source: https://context7.com/infinidat/infi.clickhouse_orm/llms.txt

Illustrates executing raw SQL queries against a ClickHouse database using the infi.clickhouse_orm library. This covers fetching text results, using custom query settings, retrieving model instances, processing large result sets with streaming, utilizing database placeholders, and executing complex analytical queries.

```python
from infi.clickhouse_orm import Database, Model, DateField, UInt32Field, StringField, MergeTree

class Event(Model):
    date = DateField()
    user_id = UInt32Field()
    event_type = StringField()

    engine = MergeTree(
        partition_key=('toYYYYMM(date)',),
        order_by=('date', 'user_id')
    )

db = Database('demo')
db.create_table(Event)

# Execute raw SQL and get text result
result_text = db.raw("SELECT count() FROM event WHERE event_type = 'click'")
print(f"Total clicks: {result_text}")

# Execute raw SQL with custom settings
result = db.raw(
    "SELECT user_id, count() as cnt FROM event GROUP BY user_id",
    settings={'max_execution_time': 30}
)

# Execute raw SQL and get model instances
query = "SELECT * FROM event WHERE date >= today() - 7"
for event in db.select(query, Event):
    print(f"User {event.user_id}: {event.event_type}")

# Raw SQL with streaming for large results
query = "SELECT * FROM event WHERE date >= '2024-01-01'"
response = db.raw(query, stream=True)
# Process streaming response
for line in response.iter_lines():
    print(line)

# Using database placeholders in queries
count_query = "SELECT count() FROM $table WHERE date = today()"
result = db.raw(db._substitute(count_query, Event))

# Complex analytical query
complex_query = """
    SELECT
        toStartOfHour(timestamp) as hour,
        event_type,
        count() as cnt,
        uniq(user_id) as unique_users,
        quantile(0.95)(response_time) as p95_response
    FROM $table
    WHERE date >= today() - 7
    GROUP BY hour, event_type
    ORDER BY hour DESC, cnt DESC
    LIMIT 100
"""
for row in db.select(complex_query, Event):
    print(f"{row.hour}: {row.event_type} - {row.cnt} events")
```

--------------------------------

### Get Model Fields with DistributedModel

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Retrieves an ordered dictionary of a model's fields, optionally filtering for writable fields only. Useful for introspection and validation.

```python
class MyModel(DistributedModel):
    id = UInt64Field()
    name = StringField()
    read_only_field = ReadonlyField()

all_fields = MyModel.fields()
writable_fields = MyModel.fields(writable=True)
```

--------------------------------

### Connect to ClickHouse and Manage Tables in Python

Source: https://context7.com/infinidat/infi.clickhouse_orm/llms.txt

This snippet demonstrates how to establish a connection to a ClickHouse database using the ORM, define a model class, create a table, check for its existence, drop a table, and drop the entire database. It handles database initialization and table management operations.

```python
from infi.clickhouse_orm import Database, Model, DateTimeField, UInt16Field, Float32Field, Memory

# Initialize database connection - database is created automatically
db = Database('demo',
              db_url='http://localhost:8123/',
              username='default',
              password='',
              readonly=False,
              autocreate=True,
              timeout=60)

# Define a model
class CPUStats(Model):
    timestamp = DateTimeField()
    cpu_id = UInt16Field()
    cpu_percent = Float32Field()
    engine = Memory()

# Create the table
db.create_table(CPUStats)

# Drop the table if needed
db.drop_table(CPUStats)

# Check if table exists
if db.does_table_exist(CPUStats):
    print("Table exists")

# Drop entire database
db.drop_database()
```

--------------------------------

### Apply Migrations with infi.clickhouse_orm

Source: https://context7.com/infinidat/infi.clickhouse_orm/llms.txt

This snippet demonstrates how to apply database migrations using the infi.clickhouse_orm library. It initializes a Database object and calls the migrate method to run all migrations up to a specified number. The migration system automatically tracks applied migrations by creating a 'migration_history' table.

```python
from infi.clickhouse_orm import Database

db = Database('demo')

# Run all migrations up to a specific number
db.migrate('myapp.migrations', up_to=9999)
```

--------------------------------

### Get Relative Year Number

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Calculates the relative number of years from a reference point. An optional timezone can be provided for accuracy. Useful for analyzing long-term trends.

```python
from infi.clickhouse_orm.functions import toRelativeYearNum

# Example usage:
relative_year = toRelativeYearNum('2023-10-27')
print(relative_year)  # Output depends on the ClickHouse version and context

relative_year_tz = toRelativeYearNum('2023-10-27', timezone='Pacific/Auckland')
print(relative_year_tz)
```

--------------------------------

### Get Relative Second Number

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Calculates the relative number of seconds from a reference point. An optional timezone can be provided for accuracy. Useful for precise time-series analysis.

```python
from infi.clickhouse_orm.functions import toRelativeSecondNum

# Example usage:
relative_second = toRelativeSecondNum('2023-10-27 10:30:45.123')
print(relative_second)  # Output depends on the ClickHouse version and context

relative_second_tz = toRelativeSecondNum('2023-10-27 10:30:45.123', timezone='Australia/Sydney')
print(relative_second_tz)
```

--------------------------------

### Create and Iterate Basic Queryset - Python

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/querysets.md

Demonstrates how to create a base queryset for a model and iterate over its matching rows. Querysets are lazy and do not hit the database until iteration.

```python
qs = Person.objects_in(database)
for person in qs:
    print(person.first_name, person.last_name)
```

--------------------------------

### Get Model Table Name with DistributedModel

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Retrieves the name of the database table associated with the model. Allows overriding the default behavior (lowercase class name).

```python
class MyCustomTable(DistributedModel):
    ...

    def table_name(self):
        return "custom_table_name"

table_name = MyCustomTable.table_name()
```

--------------------------------

### Get QuerySet for Model Instances with DistributedModel

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Returns a QuerySet object for constructing database queries to retrieve instances of the model. Enables fluent querying capabilities.

```python
class MyModel(DistributedModel):
    ...

db = Database("my_db")
queryset = MyModel.objects_in(db)

# Example query: get all instances
all_instances = queryset.all()
```

--------------------------------

### Apply Database Migrations

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/schema_migrations.md

To apply pending migrations, create a `Database` instance and call its `migrate` method, providing the package name where your migration files are located. This method ensures that only unapplied migrations are executed.

```python
from infi.clickhouse_orm import Database

Database('analytics_db').migrate('analytics.analytics_migrations')
```

--------------------------------

### Initialize Database Connection in Python

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Establishes a connection to a ClickHouse database. The database is automatically created if it doesn't exist and autocreate is enabled. Supports custom URLs, credentials, and connection settings.

```python
from infi.clickhouse_orm import Database

db = Database(
    "my_database",
    db_url="http://localhost:8123/",
    username="user",
    password="password",
    timeout=30,
    log_statements=True
)
```

--------------------------------

### Get Relative Week Number

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Calculates the relative number of weeks from a reference point. An optional timezone can be provided for accuracy. Useful for analyzing weekly patterns or cycles.

```python
from infi.clickhouse_orm.functions import toRelativeWeekNum

# Example usage:
relative_week = toRelativeWeekNum('2023-10-27')
print(relative_week)  # Output depends on the ClickHouse version and context

relative_week_tz = toRelativeWeekNum('2023-10-27', timezone='Europe/Berlin')
print(relative_week_tz)
```

--------------------------------

### Configure MergeTree Engine with Key Columns and Sampling

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/table_engines.md

Shows how to define a MergeTree engine by specifying the date column, key columns, and an optional sampling expression.

```python
engine = MergeTree('EventDate', ('CounterID', 'EventDate'))

engine = MergeTree('EventDate', ('CounterID', 'EventDate'), sampling_expr=F.intHash32(UserID))
```

--------------------------------

### Get Relative Month Number

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Calculates the relative number of months from a reference point. An optional timezone can be provided for accuracy. Useful for analyzing trends over longer periods.

```python
from infi.clickhouse_orm.functions import toRelativeMonthNum

# Example usage:
relative_month = toRelativeMonthNum('2023-10-27')
print(relative_month)  # Output depends on the ClickHouse version and context

relative_month_tz = toRelativeMonthNum('2023-10-27', timezone='America/Los_Angeles')
print(relative_month_tz)
```

--------------------------------

### Get Relative Minute Number

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Calculates the relative number of minutes from a reference point. An optional timezone can be provided for accuracy. Useful for analyzing time-series data at a minute granularity.

```python
from infi.clickhouse_orm.functions import toRelativeMinuteNum

# Example usage:
relative_minute = toRelativeMinuteNum('2023-10-27 10:30:45')
print(relative_minute)  # Output depends on the ClickHouse version and context

relative_minute_tz = toRelativeMinuteNum('2023-10-27 10:30:45', timezone='Asia/Tokyo')
print(relative_minute_tz)
```

--------------------------------

### Ordering, Limiting, and Pagination

Source: https://context7.com/infinidat/infi.clickhouse_orm/llms.txt

Demonstrates how to control the order, limit, and pagination of query results using infi.clickhouse_orm. Supports ordering by single or multiple fields (ascending/descending), slicing for limiting, and using the `paginate` helper for page-based retrieval. Requires a defined Model and Database connection.

```python
from infi.clickhouse_orm import Database, Model, DateTimeField, UInt16Field, Float32Field, Memory
import datetime

class CPUStats(Model):
    timestamp = DateTimeField()
    cpu_id = UInt16Field()
    cpu_percent = Float32Field()
    engine = Memory()

db = Database('demo')

# Order by single field
qs = CPUStats.objects_in(db).order_by('cpu_percent')

# Order descending (prefix with -)
qs = CPUStats.objects_in(db).order_by('-cpu_percent')

# Order by multiple fields
qs = CPUStats.objects_in(db).order_by('cpu_id', '-timestamp')

# Limit results using slicing
first_10 = CPUStats.objects_in(db).order_by('-cpu_percent')[:10]
for record in first_10:
    print(f"{record.cpu_id}: {record.cpu_percent}%")

# Skip and limit
records_11_to_20 = CPUStats.objects_in(db).order_by('timestamp')[10:20]

# Get single record by index
highest = CPUStats.objects_in(db).order_by('-cpu_percent')[0]

# Pagination with page helper
page = db.paginate(
    CPUStats,
    order_by='timestamp',
    page_num=1,
    page_size=50,
    conditions=CPUStats.cpu_percent > 50
)

print(f"Page {page.number} of {page.pages_total}")
print(f"Total records: {page.number_of_objects}")
for record in page.objects:
    print(f"CPU {record.cpu_id}: {record.cpu_percent}%")

# Get last page
last_page = db.paginate(
    CPUStats,
    order_by='timestamp',
    page_num=-1,
    page_size=50
)

# Using QuerySet paginate method
qs = CPUStats.objects_in(db).filter(cpu_percent__gt=50).order_by('-timestamp')
page = qs.paginate(page_num=2, page_size=25)
```

--------------------------------

### Querying with Filters and Q Objects

Source: https://context7.com/infinidat/infi.clickhouse_orm/llms.txt

Shows how to build and execute queries with various filtering conditions using infi.clickhouse_orm. Supports simple keyword arguments, multiple AND conditions, comparison operators, and complex OR/AND logic with Q objects. Also includes exclude, string operators, count, and existence checks. Requires a defined Model and Database connection.

```python
from infi.clickhouse_orm import Database, Model, DateTimeField, UInt16Field, Float32Field, StringField, Memory, F, Q
import datetime

class CPUStats(Model):
    timestamp = DateTimeField()
    cpu_id = UInt16Field()
    cpu_percent = Float32Field()
    hostname = StringField()
    engine = Memory()

db = Database('demo')
db.create_table(CPUStats)

# Get a QuerySet
qs = CPUStats.objects_in(db)

# Simple filter with keyword arguments
high_cpu = qs.filter(cpu_percent__gt=90)
for record in high_cpu:
    print(f"CPU {record.cpu_id}: {record.cpu_percent}%")

# Multiple filters (AND condition)
recent_high = qs.filter(
    cpu_percent__gt=80,
    timestamp__gte=datetime.datetime(2024, 1, 1)
)

# Using comparison operators
filtered = qs.filter(CPUStats.cpu_percent > 90, CPUStats.cpu_id == 1)

# Using Q objects for complex conditions
from infi.clickhouse_orm import Q

# OR condition
high_or_low = qs.filter(
    Q(cpu_percent__gt=90) | Q(cpu_percent__lt=10)
)

# Combined AND/OR conditions
complex = qs.filter(
    (Q(cpu_id=0) | Q(cpu_id=1)) & Q(cpu_percent__gt=80)
)

# Exclude records
not_zero = qs.exclude(cpu_id=0)

# Filter operators: eq, ne, gt, gte, lt, lte, in, not_in, between
in_range = qs.filter(cpu_id__in=[0, 1, 2, 3])
between = qs.filter(cpu_percent__between=[40, 60])

# String operators: contains, startswith, endswith, icontains, iexact
name_filter = qs.filter(hostname__startswith='server')

# Count records
count = qs.filter(cpu_percent__gt=80).count()
print(f"High CPU count: {count}")

# Check if any records exist
if qs.filter(cpu_percent__gt=95):
    print("Critical CPU usage detected")
```

--------------------------------

### Get Relative Hour Number

Source: https://github.com/infinidat/infi.clickhouse_orm/blob/develop/docs/class_reference.md

Calculates the relative number of hours from a reference point. An optional timezone can be provided for accuracy. Useful for analyzing time-series data at an hourly granularity.

```python
from infi.clickhouse_orm.functions import toRelativeHourNum

# Example usage:
relative_hour = toRelativeHourNum('2023-10-27 10:30:00')
print(relative_hour)  # Output depends on the ClickHouse version and context

relative_hour_tz = toRelativeHourNum('2023-10-27 10:30:00', timezone='Europe/London')
print(relative_hour_tz)
```

--------------------------------

### Configuring ClickHouse Table Engines in Python

Source: https://context7.com/infinidat/infi.clickhouse_orm/llms.txt

Demonstrates how to define and configure various ClickHouse table engines using the `infi.clickhouse_orm` library in Python. This includes `MergeTree` for general-purpose storage, `ReplacingMergeTree` for deduplication, `CollapsingMergeTree` for state changes, `SummingMergeTree` for automatic aggregation of numeric columns, and `Memory` for in-memory tables. Each engine is configured with specific parameters relevant to its functionality.

```python
from infi.clickhouse_orm import (
    Model, DateField, DateTimeField, UInt32Field, StringField, Int8Field, Float32Field,
    MergeTree, ReplacingMergeTree, CollapsingMergeTree, SummingMergeTree,
    Memory, TinyLog, Log, Buffer, Merge, Distributed
)
import datetime

# MergeTree - Standard engine for most use cases
class PageView(Model):
    date = DateField()
    timestamp = DateTimeField()
    user_id = UInt32Field()
    page_url = StringField()

    engine = MergeTree(
        partition_key=('toYYYYMM(date)',),
        order_by=('date', 'user_id', 'timestamp'),
        primary_key=('date', 'user_id'),
        index_granularity=8192
    )

# ReplacingMergeTree - Deduplicates rows with same primary key
class UserProfile(Model):
    date = DateField()
    user_id = UInt32Field()
    name = StringField()
    email = StringField()
    version = UInt32Field()

    engine = ReplacingMergeTree(
        ver_col='version',  # Column for version comparison
        partition_key=('toYYYYMM(date)',),
        order_by=('user_id', 'date')
    )

# CollapsingMergeTree - For storing state changes
class UserBalance(Model):
    date = DateField()
    user_id = UInt32Field()
    balance = Float32Field()
    sign = Int8Field()  # 1 for state, -1 for cancellation

    engine = CollapsingMergeTree(
        sign_col='sign',
        partition_key=('toYYYYMM(date)',),
        order_by=('user_id', 'date')
    )

# SummingMergeTree - Auto-sums numeric columns
class Statistics(Model):
    date = DateField()
    metric_id = UInt32Field()
    value = Float32Field()
    count = UInt32Field()

    engine = SummingMergeTree(
        summing_cols=('value', 'count'),
        partition_key=('toYYYYMM(date)',),
        order_by=('date', 'metric_id')
    )

# Memory engine - stores data in RAM
class TempData(Model):
    timestamp = DateTimeField()
    data = StringField()
    engine = Memory()
```