Comments (20)
Hi @snatch2013
If I understand it correctly, AdHoc filters helps to explore your data. But we are talking about columnar database with huge amount of data and these on-the-fly
queries might be quite slow.
And there are more questions:
- from which table we must query tags and values?
- for which period we must query the data (day, month or year)?
- how will we apply these filters if there are graphs with different tables on the page?
- how to prevent unexpected query to the tag with huge number of values (billions)?
For now, I see a lot of problems with it. Could you plz provide us with some scenarios for AdHoc filters?
from clickhouse-grafana.
Adhoc filters are applied automatically to all queries that target the given datasource.
For example, I parse network logs and NetFlow. Fields can be src ip, dst ip, src port, dst port, protocol, tcp flags, access-list name, etc. On the dashboard can be many graphs and tables, and with ad-hoc filters for influxdb, it is easy to filter data quickly for all graphs and tables but specifying a value for some of the fields. We just add this value to the top of the dashboard, and it affects everything.
from clickhouse-grafana.
But CH is not a key-value storage. You can't just fetch tags and values, as it required by Grafana API, without defining table and interval. If we will try to get uniq IPs from our table, the browser will just hang because of billions of results.
I've checked postgres and mysql datasources, and they doesn't support AdHocs also.
For our datasource it is possible to define regular variables, and make them dependent on each other to achieve desired filtering.
from clickhouse-grafana.
Roman,
I don't need the list of unique IPs. I need to add some filtering condition for every database request on the dashboard. So if I add ad-hoc filters protocol='tcp', dst_port=53, it will be added to each database request as where protocol='tcp' and dst_port=53.
Maybe I understand wrong something about grafana, but variables seem not so suitable. For the example above, I have to add the condition AND protocol=$protocol AND dst_port=$dst_port to each request?
from clickhouse-grafana.
I need to add some filtering condition for every database request on the dashboard
Grafana requires to implement next funcs for AdHoc:
- getTagKeys(options)
- getTagValues(options)
It means that tags and values must be queried from datasource
I have to add the condition AND protocol=$protocol AND dst_port=$dst_port to each request
Yes
So if I add ad-hoc filters protocol='tcp', dst_port=53, it will be added to each database request as where protocol='tcp' and dst_port=53
But no guarantees that every table will have the same list of columns, like dst_port
and protocol
.
Also queries might consist of joins and inner requests and we can't just add where protocol='tcp'
in the end of query
I see that AdHoc is supported only by ElasticSearch and InfluxDB. I am not familiar with those databases and might mistaken in such questions.
from clickhouse-grafana.
They collect tag keys and values just to provide the ability to choose from the list. But, you can also type manually any field name, it shouldn't be the tag name. Also, it is possible to type any value, not only to choose from the list. So in case of clickhouse, it seems like it possible to fetch the list of columns and show this list, but of cause there is no need to fetch all values.
So, according to your answer, it's not possible to use variables to achieve the same goal. Every time you want to change some filtering field, you need to reconfigure all graphs and tables.
Of cause there are cases when you cannot use this filters, but it's the same for influxdb and elastic. But it's ok, nobody make you use this feature if you don't need it.
from clickhouse-grafana.
Good point. I can try to fetch all columns and their types from system.columns. And after each change parse all queries into AST and add conditions into WHERE
statement at root level. I need more time for this.
Thx for your patience!
from clickhouse-grafana.
Thanks, Roman
from clickhouse-grafana.
/long negative post deleted/
Never mind. Adhoc filters implementation for initial filtering on the $table could be useful, definitely.
from clickhouse-grafana.
We have a use case where we have a drop-down list of ad-hoc filters that we commonly use (e.g. filtering for HTTP request code 200).
Using the SqlDB plugin, it's possible to use ad-hoc filters by adding AND ($filter)
to the where clause. The value of $filter
is unquoted using the SqlDB plugin, e.g. the query sent to Clickhouse includes:
AND(scheme = 'https')
This technique does not work with the Vertamedia plugin because it seems that $filter
is being quoted:
AND('scheme = \'https\'')
...which prevent this technique from working.
Maybe this could be a simple way to implement this feature?
from clickhouse-grafana.
Maybe it would be easier to transform the $table to a subquery with this filters applied? It wouldn't require any AST hustle and $timeFilter should be applied in this subquery (the additional point here - it is now easy to produce the excessive load on Clickhouse server by accidently running several full-range queries if one miss the $timeFilter while writing the query).
from clickhouse-grafana.
Hi @mattbostock
Maybe this could be a simple way to implement this feature?
Plugin automatically quotes string arguments in variables. I did it to simplify query building:
WHERE Type = $type
- here u don't need to know whether $type variable is integer or string. The same quoting is used for IN
arguments - ('type1', 'type2')
or (1,2)
from clickhouse-grafana.
@snatch2013
I've implemented adhoc filters in #35
Could u plz check whether it's ok for u? U need simply download plugin from that branch and copy into grafana plugin folder to make it work.
Some info about adhocs:
- If there is an adhoc variable, plugin will fetch all columns of all tables of all databases (except
system
database) astags
. So in dropdown menu u will see options kindadatabase.table.column
- if there are
ENUM
columns, plugin will fetch their options and use them as tag values - plugin will apply adhoc filters to all queries on the dashboard if their settings
$database
and$table
are the same as adhoc'sdatabase.table
- there are no option to apply
OR
operator for multiple adhoc filters - see grafana/grafana#10918 - there are no option to use
IN
operator for adhoc filters due to Grafana limitations
from clickhouse-grafana.
@snatch2013 up
from clickhouse-grafana.
Roman, sorry for late answer. I will check it today.
from clickhouse-grafana.
I have downloaded the branch adhoc-filters, restarted the grafana-server, but wasn't able to make it work.
When I add the variable with type adhoc-filter, I receive a warning that this type of datasource doesn't support adhoc filters. After that, on the dashboard, I can add filters, but they don't have any effect on the output. Also, there is no list of columns to choose from and there is no option to delete the filter (usually it is in the same list with the fields names).
Checked, that new files are included in the plugin:
# ls -a /var/lib/grafana/plugins/vertamedia-clickhouse-datasource/dist/ | grep adhoc
adhoc.d.ts
adhoc.js
adhoc.js.map
adhoc.ts
Could you please suggest what to check? Can it be a problem for grafana version 5.0.0? I will check the version 4.6 tomorrow.
Regards,
Vladimir
from clickhouse-grafana.
Hi @snatch2013
This is strange, it should work. I've just tested it:
- downloaded latest grafana (
brew install grafana
) - downloaded adhoc branch
https://github.com/Vertamedia/clickhouse-grafana/tree/adhoc-filters
- copied source code to plugin directory
- started grafana
- configured datasource
- added adhoc filter to dashboards
Probably it's browsers cache? Try to create new dashboard in browser's incognito mode ctrl+shift+n
or disable cache in developer tools
from clickhouse-grafana.
Yes, sorry, it was my mistake. I missed that the name of the variable should be adhoc. Now it works.
Thanks a lot, this is a very helpful functionality for us.
Regards,
Vladimir
from clickhouse-grafana.
Ok, I'll merge it
from clickhouse-grafana.
see https://github.com/Vertamedia/clickhouse-grafana/releases/tag/1.4.0
from clickhouse-grafana.
Related Issues (20)
- Support for multiple grouping columns for calculations like increaseColumns HOT 1
- Unexpected error when using $conditionalTest macro HOT 11
- The 'Format As' feature is not working. HOT 1
- No longer possible to specify custom timestamp column value in 3.0.0 HOT 1
- golang part of plugin ignore tlsSkipVerify oprtion from datasource settings
- Broken table with specific query in 3.0.0 HOT 6
- databend datase return 400 error code which not correctly handled on plugin side HOT 12
- Show Generated SQL - wrong formatting and lost query state HOT 2
- `step` field in query editor doesn't work
- state doens't pass to query execution HOT 2
- autocomplete duplicate the same names
- Drop down select how to clear value?
- `isClearable` doesn't work for Timestamp Column, and this is weird
- Annotations doesn't work
- Problems with Cloudflare analytics engine Clickhouse after upgrading to 3.0.0 HOT 3
- Syntax highlight stop works when auto-complete query failed HOT 1
- Adding new alerts doesn't work
- selected values in drop-down select in QueryBuilder doesn't clear when we change datasource
- $columns + subquery stop works after 3.1.1
- Explore query mode keeps resetting to Time Series when editing query
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from clickhouse-grafana.