Product Updates: October 2024

New ClickHouse SQL Interface, Revamped Docs, and new UI components

Product Updates: October 2024

Propel

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.002 │ Chicken    │ Medium │  6.503 │ Vegetarian │ Mild   │  6.004 │ Beef       │ Mild   │  7.505 │ Beef       │ Medium │  8.006 │ 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

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:

Propel's ClickHouse interface

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.

Propel's documentation

The new docs include the following sections:

👉  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.

Propel's server-side authentication

👉 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
Group By Component

👉 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:

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

Related posts

Product Updates: September 2024

This is some text inside of a div block.

Heading 1

Heading 2

Heading 3

Heading 4

Heading 5
Heading 6

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.

Block quote

Ordered list

  1. Item 1
  2. Item 2
  3. Item 3

Unordered list

  • Item A
  • Item B
  • Item C

Text link

Bold text

Emphasis

Superscript

Subscript

Product Updates: August 2024

This is some text inside of a div block.

Heading 1

Heading 2

Heading 3

Heading 4

Heading 5
Heading 6

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.

Block quote

Ordered list

  1. Item 1
  2. Item 2
  3. Item 3

Unordered list

  • Item A
  • Item B
  • Item C

Text link

Bold text

Emphasis

Superscript

Subscript

Product Updates: July 2024

This is some text inside of a div block.

Heading 1

Heading 2

Heading 3

Heading 4

Heading 5
Heading 6

Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua. Ut enim ad minim veniam, quis nostrud exercitation ullamco laboris nisi ut aliquip ex ea commodo consequat. Duis aute irure dolor in reprehenderit in voluptate velit esse cillum dolore eu fugiat nulla pariatur.

Block quote

Ordered list

  1. Item 1
  2. Item 2
  3. Item 3

Unordered list

  • Item A
  • Item B
  • Item C

Text link

Bold text

Emphasis

Superscript

Subscript

Start shipping today

Deliver the analytics your customers have been asking for.