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.

#!/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']);