Hi there!

We're thrilled to share the latest Propel product updates and ClickHouse tips and tricks.

Query of the month

Adding row numbers to results

This month's query showcases the ROW_NUMBER() window function in ClickHouse, which adds a sequential number to each row in a result set. Here's what the query does:

• Creates a sample dataset of tacos with their types, salsa, and prices
• Uses ROW_NUMBER() to assign a unique, sequential number to each row
• Orders the rows based on the price of the tacos

SELECT
    ROW_NUMBER() OVER (ORDER BY price) AS row_num,
    taco, 
    salsa,
    price
FROM
(
    SELECT 'Beef' AS taco, 'Mild' AS salsa, 7.50 as price UNION ALL
    SELECT 'Chicken' AS taco, 'Medium' AS salsa, 6.50 as price UNION ALL
    SELECT 'Vegetarian' AS taco, 'Hot' AS salsa, 4.00 as price UNION ALL
    SELECT 'Beef' AS taco, 'Medium' AS salsa, 8.00 as price UNION ALL
    SELECT 'Chicken' AS taco, 'Hot' AS salsa, 10.00 as price UNION ALL
    SELECT 'Vegetarian' AS taco, 'Mild' AS salsa, 6.00 as price
) AS menu;

Returns:

┌─row_num─┬─taco───────┬─salsa  ┬─price─┐
│       1 │ Vegetarian │ Hot    │  4.00 │
│       2 │ Chicken    │ Medium │  6.50 │
│       3 │ Vegetarian │ Mild   │  6.00 │
│       4 │ Beef       │ Mild   │  7.50 │
│       5 │ Beef       │ Medium │  8.00 │
│       6 │ Chicken    │ Hot    │ 10.00 │
└─────────┴────────────┴────────┴───────┘

You can learn more in the window functions documentation.

What’s New

Here are the highlights of what’s new 👇

• ClickHouse SQL Interface
• New documentation site
• Easier server-side authentication
• New GroupBy UI component
• 18 improvements and fixes

ClickHouse SQL interface

We've launched a native ClickHouse SQL HTTP interface that lets you query Propel's Serverless ClickHouse directly. You can use any standard ClickHouse library or SDK to run SELECT and SHOW queries against your data.

To get started, use the following connection details you’ll find in the Console:

Here's an example of querying data using the ClickHouse HTTP interface with curl:

echo 'SELECT 1' | \
curl https://clickhouse.us-east-2.propeldata.com:8443 \
  --user "$APPLICATION_ID:$APPLICATION_SECRET" \
  --data-binary @-

👉 Learn more about the ClickHouse SQL interface.

New docs

We've completely revamped our documentation to make it easier for developers to get started and be successful with Propel.

The new docs include the following sections:

• Serverless ClickHouse: Connection details, SQL syntax, SQL reference, functions, and everything you need to know to manage, transform, and query your data.
• Ingestion: Guides on ingesting data from a variety of sources to Propel, including Webhooks, events streams, and data warehouses.
• Query APIs: Guides and examples for using the SQL and Query APIs for common analytics use cases and data visualizations.
• Embeddable UIs: Guides and documentation for embedding Propel's visualization components into your applications.

👉  Explore the new docs.

Easier server-side authentication

You can now easily authenticate server-side applications using HTTP Basic Authentication with your Application ID and secret.

👉 Learn more about server-side authentication.

New GroupBy UI component

The new Group By component is a dropdown menu that dynamically lets users change data grouping for multidimensional analysis in your dashboards. It offers several key features:

• Automatically populates with unique values from a specified column.
• Integrates with the Filter Provider component to apply breakdowns across components.
• Supports both single and multiple dimensional breakdowns.
• Allows setting a default breakdown for the initial display

👉 Learn more about the Group By component.

From Propel

Funnel analysis in ClickHouse

In this post, we explain funnel analysis—a method to track user progression through steps such as viewing a menu, adding items to a cart, and making a purchase. ClickHouse's windowFunnel function enables this analysis by identifying how many users complete each step within a specified time frame. For example, to assess user behavior in an online taco shop, you can create a taco_events table to log user actions. By applying windowFunnel, you can determine the number of users who view the menu, add tacos to their cart, and complete a purchase within a two-hour window. This approach helps pinpoint where users drop off, allowing for targeted improvements in the customer journey.

Read the full post.

Other posts worth reading:

• How to annotate PostgreSQL ASTs with location information
• Understanding ReplacingMergeTree in ClickHouse
• Flattening DynamoDB JSON in ClickHouse

From the community

From Zero to Terabytes: Building SaaS Analytics with ClickHouse

By Baptiste Jamin @ Crisp.chat

In this post, the team at Crisp explains how they transitioned from MySQL to ClickHouse to power its customer-facing analytics, addressing the need for real-time, detailed insights into customer support interactions. The shift was driven by MySQL's limitations in scalability and flexibility, particularly around aggregating large volumes of data and efficiently supporting ad hoc queries. ClickHouse enabled Crisp to ingest raw event data directly, avoiding the need for precomputation and allowing far more flexible, real-time analytics. Instead of relying on MySQL's expensive JOIN and aggregation operations, ClickHouse's ability to perform complex operations like GROUP BY and time-window analyses in an optimized manner proved essential for handling high-cardinality datasets.

By moving to ClickHouse, Crisp transformed its analytics stack from a rigid, precomputed structure into a flexible, real-time environment. Three key elements of their ClickHouse deployment stand out: the use of materialized views to efficiently generate derived metrics without redundant processing, leveraging MergeTree tables to maintain high query performance as data scaled to terabytes, and implementing efficient data compression techniques like LZ4 and ZSTD to save storage space. These features helped Crisp manage terabytes of conversational data while maintaining high performance.

Read the full post.

How to get started with Propel for free?

Start building today with a generous free tier that gets you 10GB of storage and 50,000 queries per month 👩🏽‍💻  🏗️  🚀!

Claim your $15 of free monthly usage forever.

Thank you for reading!

Nico Acosta
Co-founder & CEO
Propel

‍