Customizing Asterisk’s cdr_adaptive_odbc Module for ClickHouse CDR Storage

Managing Asterisk’s Call Detail Records (CDRs) at scale poses significant challenges, as traditional databases often struggle with the volume and complexity of queries required for efficient analysis. Asterisk, an open-source framework for building communictions applications, tracks detailed call metadata via CDRs but does not, at the time of this post, natively support ClickHouse, a columnar database optimized for high-speed analytics. The cdr_adaptive_odbc module, while designed to connect Asterisk’s CDR storage to various databases via ODBC, requires modifications to integrate with ClickHouse, allowing CDRs to be stored and analyzed in a high-performance environment.

Disclaimer

The source code modifications for the cdr_adaptive_odbc module presented in this post are provided as a custom solution to enable Asterisk CDR storage with ClickHouse. These changes are not officially supported by the Asterisk project or ClickHouse, and their implementation is at the user’s own risk. The author assumes no liability for any issues, data loss, or system failures resulting from the use of these modifications. Always back up critical data and configurations prior to applying custom changes.

Clickhouse ODBC:

• Please note, this solution was tested with Clickhouse ODBC driver 1.3.0.20241018

Install the latest Clickhouse ODBC driver from: https://github.com/ClickHouse/clickhouse-odbc/

wget https://github.com/ClickHouse/clickhouse-odbc/releases/download/v1.5.0.20251127/clickhouse-odbc-linux-Clang-UnixODBC-Release.zip
unzip clickhouse-odbc-linux-Clang-UnixODBC-Release.zip
tar -zxvf clickhouse-odbc-1.5.0-Linux.tar.gz
cd clickhouse-odbc-1.5.0-Linux/lib
mkdir /usr/lib/x86_64-linux-gnu/odbc/clickhouse
mv *.* /usr/lib/x86_64-linux-gnu/odbc/clickhouse

Add driver to /etc/odbcinst.ini:
(example only)

[ClickHouse]
Description=ODBC Driver (Unicode) for ClickHouse
Driver=/usr/lib/x86_64-linux-gnu/odbc/clickhouse/libclickhouseodbcw.so
Setup=/usr/lib/x86_64-linux-gnu/odbc/clickhouse/libclickhouseodbcw.so

Add DSN to /etc/odbc.ini:
(example only)

[cdr]
Description=cdr
Trace=Off
TraceFile=/var/log/odbc.log
Server=127.0.0.1
Driver=ClickHouse
Database=cdr
UID=username
PWD=password
Port=8123
Proto=http

Original source of cdr_adaptive_odbc.c:

https://github.com/asterisk/asterisk/blob/master/cdr/cdr_adaptive_odbc.c

Asterisk cdr_adaptive_odbc.c changes:

• The below changes are based on the official module source, with last commits on Apr 25, 2022.
  Changes to the module after this date may affect the below solution.
• If Asterisk is already installed, this will require a recompile and overwrite of the installed module

diff of cdr_adaptive_odbc.c

diff --git a/cdr_adaptive_odbc.c.old b/cdr_adaptive_odbc.c
index 90032e2..eb1305f 100644
--- a/cdr_adaptive_odbc.c.old
+++ b/cdr_adaptive_odbc.c
@@ -1,3 +1,4 @@
+/* Customized for ClickHouse - Dan Graham 2024-11-24 */
 /*
  * Asterisk -- An open source telephony toolkit.
  *
@@ -588,7 +589,8 @@ static int odbc_log(struct ast_cdr *cdr)
                        }

                        LENGTHEN_BUF2(26);
-                       ast_str_append(&sql2, 0, "%s{ ts '%04d-%02d-%02d %02d:%02d:%02d' }", separator, year, month, day, hour, minute, second);
+                       /* The following timestamp format was customized for ClickHouse - Dan Graham 2024-11-25 */
+                       ast_str_append(&sql2, 0, "%s'%04d-%02d-%02d %02d:%02d:%02d'", separator, year, month, day, hour, minute, second);
                    }
                    break;
                case SQL_INTEGER:
@@ -758,13 +760,22 @@ static int odbc_log(struct ast_cdr *cdr)
                ast_debug(3, "Executing [%s]\n", ast_str_buffer(sql));

                stmt = ast_odbc_prepare_and_execute(obj, generic_prepare, ast_str_buffer(sql));
-               if (stmt) {
-                       SQLRowCount(stmt, &rows);
-                       SQLFreeHandle(SQL_HANDLE_STMT, stmt);
-               }
-               if (rows == 0) {
-                       ast_log(LOG_WARNING, "cdr_adaptive_odbc: Insert failed on '%s:%s'.  CDR failed: %s\n", tableptr->connection, tableptr->table, ast_str_buffer(sql));
-               }
+
+        /* The following if block was customized for ClickHouse - Dan Graham 2024-11-25 */
+        if (stmt) {
+                char sql_state[6];
+                char message[512];
+                SQLINTEGER native_error;
+                SQLSMALLINT msg_length;
+                SQLRowCount(stmt, &rows);
+                SQLGetDiagRec(SQL_HANDLE_STMT, stmt, 1, (SQLCHAR*)sql_state, &native_error, (SQLCHAR*)message, sizeof(message), &msg_length);
+
+                if (strcmp(sql_state, "00000") != 0) {
+                    ast_log(LOG_WARNING, "cdr_adaptive_odbc: Insert failed on '%s:%s'. SQL: %s. Query state: %s.\n", tableptr->connection, tableptr->table, ast_str_buffer(sql), sql_state);
+                }
+                SQLFreeHandle(SQL_HANDLE_STMT, stmt);
+        }
+
 early_release:
                ast_odbc_release_obj(obj);
        }

Optionally, add additional logging:

• Adjust log level as desired:

if (strcmp(sql_state, "00000") == 0) {
    ast_log(LOG_NOTICE, "cdr_adaptive_odbc: Insert successful on '%s:%s'. SQL: %s\n", tableptr->connection, tableptr->table, ast_str_buffer(sql));
} else {
    ast_log(LOG_WARNING, "cdr_adaptive_odbc: Insert failed on '%s:%s'. SQL: %s. Query state: %s.\n", tableptr->connection, tableptr->table, ast_str_buffer(sql), sql_state);
}

Extend further by using Clickhouse cluster:

https://clickhouse.com/docs/architecture/cluster-deployment

https://www.chproxy.org