problem for data entry

[jcalhinho]

hello ismael, great job for the sankey, i have a problem, i have a clickhouse database and my sql request is bad because the "required field source, target and value are missing...

i have a csv with fieldname: ip_src, ip_dst and nb_bytes. should i replace the fieldname by alias source, target, value?
do you have an example of request sql?

Thanks a lot

[IsmaelMasharo]

Hey @jcalhinho, you're right, the expected fields for the plugin to work are source (source nodes), target (target nodes) and value (link values). Each record in the SQL should have these 3 fields. Also, this is an acyclic network implementation of the Sankey, so loops are not allowed.

I will soon expand the docs with more details 🙏

[jcalhinho]

thanks for the answer ismael so this sql request is bad?
SELECT $timeSeries as t,
ip_src as source,
ip_dst as target,
nb_bytes as value,
count()
FROM $table
WHERE $timeFilter

GROUP BY t,source,target
ORDER BY t,value

[jcalhinho]

for me it doesn t work...

[jcalhinho]

update, after many try, it s ok with this sql:
SELECT
ip_src as source,
ip_dst as target,
nb_byte as value,
count()
FROM $table
GROUP BY source,target,value
ORDER BY value LIMIT 25

i have an error with d[2].toFixed(2) is not a function so i delete this method and it s work but when i try more than 25 for the limit
there is an error like you said : circular link ...
any idea for fix this problem?
thanks again ismael!

[IsmaelMasharo]

Hey @jcalhinho, looking at the sql it seems that it was failing at first since the group by clause was also expecting the field value. Although about your final sql, Is your intention to count the number of times a source-target pair appears? or to sum up the total nb_yte for each source-target pair? Remember that it's the value field that should express your total aggregation, so in your sql count() by its own it's not being taken for the chart construction. Any extra field besides source, target and value won't be taken into account. So I would say the proper sql should be whether this

COUNT() as value

or this

SUM(nb_byte) as value

I would guess for the last one.

Regarding the d(2).toFixed(2) is not a function error, It seems that maybe one of the fields had missing values, or maybe the value field had non-numeric values. I'll add this in the docs.

For the circular link error, you could try to mask the target values with a prefix, something like this

CASE 
  WHEN ip_src = ip_dst THEN CONCAT('P-', ip_dst)
  ELSE ip_dst
END "target",

Hope it was helpful 🙌

[IsmaelMasharo]

One last piece of advice, you could first build the sql and display it using a regular table, this way you could double-check the values being generated by your sql.

[jcalhinho]

great i will try now, thanks a lot!