Visualizing ClickHouse Data with Grafana

foreign we'll demonstrate how the popular visualization tool grafana integrates seamlessly with clickhouse by building a few visualizations the blog post for this content can also be found at clickhouse.com with a link in the video description before we start let's quickly review what clickhouse is clickhouse is an open source technology and the world's fastest column-oriented distributed database popular for analytics and observability use cases you can download clickhouse from clickhouse.com today for free and start experiencing the speed of your soon to be favorite database you can now also try our serverless offering of clickhouse via clickhouse.cloud which lets you enjoy the performance you love whilst we take care of the details so you can spend more time getting Insight out of the fastest database on Earth since originating as a fork of Cabana in 2014 grafana has grown to be one of the most popular visualization techniques for time series data the code has long diverged from its Cabana Roots extending its support for data stores adding new visualizations and expanding Beyond a simple dashboarding tool while applicable to business intelligence use cases it predominantly optimizes for time series data with excellent support for metrics logs and traces let's start with connecting clickhouse to grafana for our examples we'll use grafana cloud which offers a generous free tier more than sufficient for our needs to connect grafana to clickhouse a user is simply required to create a clickhouse data source effectively an instantiation of the plugin users can create multiple data sources each potentially connecting to a different click House service or possibly providing access to the same instance with different credentials first we select data sources from the left menu and then the official signed clickhouse plugin from the subsequent list built using the latest grafana framework in collaboration with grafana this plugin supports features such as alerting out of the box we next enter the details of our click house instance connecting to play.clickhouse.com with the Explorer user this instance is publicly available and has all of the data sets we need note how we connect securely over http finally we save the data source which is now ready to use visualizing data in grafana requires us to create a dashboard from care the user can add a visualization panel as shown and then specify that this should use our clickhouse data source when creating a visual the user is required to be familiar with SQL Concepts however a visual query Builder assists with constructing clickhouse SQL for most chart types with grafana focusing on time series data most charts require the user to define a date time column along with a numerical value for our first visual we use The Hacker News data set this contains all of the posts and comments from Hacker News for the last seven years let's create a simple bar chart showing posts over time restricted to those concerning clickhouse first we select time series as our chart type and populate a database and table before adding our account aggregation and filters restricting to comments and stories containing the word clickhouse next we modify our generated SQL to group by month before switching our chart type to bar finally we improve the presentation with a softer color and increased opacity great our first visual is done let's add that to our dashboard for our next visual we'll create a multi-line chart multi-line charts require a string column in addition to the standard date time and numeric columns this string column defines the series key for this chart we'll use a Forex data set to show the price of various currency pairs where the US dollar is the base we use a SQL query here directly rather than the query Builder the quote column which denotes our currency pair acts as our series key to get a series per currency pair we use this column with a group by clause note the time filter macro which automatically injects our selected time range into the query as a filter thus allowing us to later drill down when changing the date range of the dashboard we also use the ARG Max function to find the close price for each currency pair per day we set max result rows to 10 000 to allow us to get a larger data set finally we again refine the chart visually by increasing the opacity a little more work is required for Less standard visualizations such as heat map and candlestick the Candlestick visualization is great for showing how prices change over a Time this type of chart is used as a trading tool to visualize and analyze the price movements over time for currencies Candlestick charts display multiple bits of price information such as the open and close price as well as the highest and lowest values through the use of candlestick-like symbols this chart fits our Forex data set perfectly we will focus on the most traded currency pair the US dollar euro first we had a new panel before selecting Candlestick as our chart type from the write menu again we set clickhouse as our data source as shown we need a SQL query which returns us the open close low and high values per day for the specifics of this query we recommend you read our Forex blog Linked In the description with these values we can configure the chart note how we've grouped by day in our SQL function zooming in on a shorter time period we can see how this visualization allows us to understand the scale of price movements from day to day geomaps in grafana support a heat map effect which allows us to bucket our geodata and provide a numerical value to control the color scale our weather data set described in a blog post Linked In the description provides us with a simple means to demonstrate this capability by plotting temperature ranges for North America the SQL query we've entered here relies on the geohash encode function to aggregate on a point field finding the max temperature recorded for each grid note also the geographic restriction via the polygon dictionary a filter optimization explored in our blog post once we selected the geomap chart type from the right menu we can configure it as a heat map selecting the temperature column to define the intensity of color and show the hottest areas of the United States and Mexico finally we modify some values controlling appearance of our map to achieve the visual effect we're looking for our previous examples focus on interesting time series data sets rafana however is typically used for the observability use case features such as the explore view which is great for structured logs have been developed with this use case in mind fortunately clickhouse now supports the Json type perfect for log data let's switch to the explore view via the left menu and use some simple Json log data in play.clickhouse.com to demonstrate this here we can use a simple select from SQL statement to read the first 10 000 rows from our logs table we offset the timestamp to the last 24 hours to simplify the visualization and create a level field from the status code using a multi-function this ensures the logs are categorized and color-coded useful for highlighting errors the Json Fields such as method can be accessed using simple.notation in this video we have shown some simple visualization techniques using the grafana plugin for further details on the visuals and queries shown in this video we recommend the blog posts Linked In the description thank you for watching

Channel: ClickHouse

Views: 6,285

Keywords: ClickHouse, 2020, Grafana

Id: Ve-VPDxHgZU

Length: 8min 47sec (527 seconds)

Published: Wed Nov 02 2022