Clickhouse-cdr-import.php

This script logs into a XC5 softswitch using the JSON api, exports the CDR records and imports them into clickhouse.

You'll need the phpclickhouse library which you can get by invoking the command composer require smi2/phpclickhouse from the directory that this script is saved in.

You're also going to need to create and customise clickhouse_config.php and softswitch_config.php to your environment.

#!/usr/bin/php
<?php

require __DIR__ . '/vendor/autoload.php';

require_once('clickhouse_config.php');
require_once('softswitch_config.php');

function is_not_null($val){
    return !is_null($val);
}

// Get next CDR ID to request from softswitch by querying the last CDRID from the clickhouse database and adding one.

$db = new ClickHouseDB\Client($clickhouse_config);
$db->database('softswitch');
$db->setTimeout(1.5);      // 1500 ms
$db->setTimeout(10);       // 10 seconds
$db->setConnectTimeOut(5); // 5 seconds

$result = $db->select('select max(cdrid)+1 as nextcdrid from cdr');
$row = $result->fetchOne();

$nextcdrid = $row['nextcdrid'];

$ch = curl_init();
curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, $curl_timeout);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_URL, "http://10.1.1.2/api-sys/cdr/?rows=10000&cdrid=${nextcdrid}");
curl_setopt($ch, CURLOPT_USERPWD, "${api_username}:${api_password}");

$json=curl_exec($ch);
curl_close($ch);
if(($json=="[]")||($json===false)) {
	//echo "Nothing returned from softswitch for CDR query, oh oh, exiting now!\n";
	exit;
}
$array = json_decode($json, true);
$array_count = count($array);

//echo "CDR Array has {$array_count} elements in it\n";

$cdr_count=0;

$stream = fopen('php://memory','r+');

foreach ($array as $line) {
	fwrite($stream, json_encode(array_filter($line,'is_not_null')));
	$cdr_count++;
}

rewind($stream);

$streamWrite=new ClickHouseDB\Transport\StreamWrite($stream);
$streamWrite->applyGzip();

$callable = function ($ch, $fd, $length) use ($stream) {
    return ($line = fread($stream, $length)) ? $line : '';
};

$streamWrite->closure($callable);

$r=$db->streamWrite($streamWrite,'INSERT INTO {table_name} FORMAT JSONEachRow', ['table_name'=>'softswitch.cdr']);