NOT_IMPLEMENTED ClickHouse® error¶

The NOT_IMPLEMENTED error in ClickHouse® (and Tinybird) happens when you try to use a feature, function, or operation that isn't implemented in ClickHouse® or isn't available in your version. This commonly occurs when using features from other database systems or trying to use newer ClickHouse® features in older versions.

What causes this error¶

You typically see it when:

• Using features from other database systems (MySQL, PostgreSQL, etc.)
• Using newer ClickHouse® features in older versions
• Using unsupported SQL operations
• Using client-specific features that aren't implemented
• Using deprecated or removed features

Example errors¶

SELECT * FROM events FOR UPDATE

SELECT * FROM events OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY

UPDATE events SET event_type = 'click' WHERE user_id = 123

SELECT * FROM events USING SAMPLE 0.1

How to fix it¶

Use ClickHouse®-compatible syntax¶

Replace unsupported features with ClickHouse® equivalents:

SELECT * FROM events LIMIT 5 OFFSET 10

Use supported operations¶

Replace unsupported operations with supported alternatives:

INSERT INTO events_new SELECT * FROM events WHERE user_id != 123
INSERT INTO events_new (user_id, event_type) VALUES (123, 'click')

Check ClickHouse® version¶

Verify feature support in your ClickHouse® version:

SELECT version()

Use alternative approaches¶

Find alternative ways to achieve the same result:

SELECT * FROM events SAMPLE 0.1

Common patterns and solutions¶

Pagination¶

ClickHouse® pagination syntax:

SELECT * FROM events ORDER BY timestamp DESC LIMIT 10 OFFSET 20

Data modification¶

ClickHouse® doesn't support UPDATE/DELETE like other databases:

-- Use INSERT with deduplication
INSERT INTO events_new SELECT * FROM events WHERE user_id != 123
INSERT INTO events_new (user_id, event_type) VALUES (123, 'click')

Using MergeTree engines for "updates"¶

CREATE TABLE events_replacing (
    user_id UInt32,
    event_type String,
    timestamp DateTime
) ENGINE = ReplacingMergeTree() ORDER BY user_id

-- Insert new row to "update"
INSERT INTO events_replacing VALUES (123, 'click', now())

Sampling¶

ClickHouse® sampling syntax:

SELECT * FROM events SAMPLE 0.1
SELECT * FROM events SAMPLE 1000 ROWS

Transactions¶

ClickHouse® has limited transaction support:

-- Use atomic operations where possible
-- Consider using Materialized Views for complex updates

Advanced solutions¶

Using Materialized Views¶

For complex data modifications:

CREATE MATERIALIZED VIEW events_updated AS
SELECT
    user_id,
    CASE WHEN user_id = 123 THEN 'click' ELSE event_type END as event_type,
    timestamp
FROM events

Using INSERT with deduplication¶

For data replacement:

INSERT INTO events_new
SELECT
    user_id,
    CASE WHEN user_id = 123 THEN 'click' ELSE event_type END as event_type,
    timestamp
FROM events

Using window functions¶

For complex aggregations:

SELECT
    user_id,
    event_type,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY timestamp) as row_num
FROM events

ClickHouse®-specific features¶

Using ReplacingMergeTree¶

For handling updates:

CREATE TABLE events_replacing (
    user_id UInt32,
    event_type String,
    timestamp DateTime,
    version UInt32
) ENGINE = ReplacingMergeTree(version)
ORDER BY (user_id, timestamp)

Using CollapsingMergeTree¶

For handling deletes:

CREATE TABLE events_collapsing (
    user_id UInt32,
    event_type String,
    timestamp DateTime,
    sign Int8
) ENGINE = CollapsingMergeTree(sign)
ORDER BY (user_id, timestamp)

Feature compatibility¶

MySQL to ClickHouse®¶

PostgreSQL to ClickHouse®¶

Tinybird-specific notes¶

In Tinybird, this error often occurs when:

• Using unsupported ClickHouse® features in Pipes
• Trying to use features not available in Tinybird's ClickHouse® version
• Using client-specific methods that aren't supported
• Working with Materialized Views that use unsupported operations

To debug in Tinybird:

1. Check Tinybird's ClickHouse® version and supported features
2. Use the Query Builder to test operations
3. Consult Tinybird documentation for supported features
4. Use alternative approaches for unsupported operations

Best practices¶

Feature compatibility¶

• Check ClickHouse® documentation before using new features
• Test operations in development environment first
• Use version-specific feature checks
• Keep up with ClickHouse® updates and changes

Alternative approaches¶

• Use Materialized Views for complex data modifications
• Implement logic in app layer when needed
• Use INSERT with deduplication for updates
• Consider using specialized table engines

Error handling¶

• Implement proper error handling for unsupported features
• Have fallback approaches ready
• Log unsupported feature attempts
• Document workarounds for common issues

See also¶

• ClickHouse® Table Engines
• Working with Data Sources
• Common function errors