May 2023 Summaries

ClickHouse Cloud is offering $300 in credits for new users who want to start using their cloud-based data warehousing platform. The company has been focusing on vector search capabilities, which are being used to improve the functionality of traditional search engines like Elasticsearch and Solr. Vector search uses machine learning algorithms to represent content as vectors, allowing for more accurate and efficient searching across different data types such as text, images, and audio. ClickHouse supports vector storage and retrieval, making it a suitable choice for applications that require semantic search capabilities. The company's focus on vector search is part of their broader efforts to provide a scalable and flexible platform for data warehousing and analytics.

This summary provides an overview of how to use ClickHouse Cloud for vector search and demonstrates its capabilities through a series of examples. The text explains the benefits of using ClickHouse for vector search, including its ability to combine vector matching with filtering on metadata and aggregating on metadata. It also discusses the importance of choosing the right distance function, such as cosine or L2 distance, depending on the specific use case. The summary highlights the features of ClickHouse's experimental secondary indices feature, including inverted indices, which can improve query performance by limiting the number of rows to be searched. Additionally, it mentions the introduction of user-defined functions (UDFs) in ClickHouse, which allow users to extend the behavior of the database by creating custom functions that can leverage SQL constructs and functions. The summary concludes by emphasizing the benefits of using ClickHouse Cloud for vector search and encourages readers to try out its features with a 30-day free trial and pay-as-you-go pricing model.

You can effortlessly manage your ClickHouse Cloud services within your existing CI/CD pipeline and programmatically perform operations, such as service provisioning, configuration, and scaling. The newly introduced API support enables automation of various tasks, including programmatically provisioning, configuring, and managing services, which saves time and effort by eliminating manual processes, reducing the risk of human errors, and ensuring consistency across environments. You can leverage tools like Ansible, Chef, and Puppet to automate deployments quickly and take advantage of features such as scheduled scaling, flexible configuration management, and lifecycle operations on ClickHouse services. To get started with ClickHouse Cloud API, create an account or login with ClickHouse Cloud, navigate to the API Keys tab, generate a key, and set your policy. The API is secure, with measures in place to protect your API key, secret, and connection, including a web application firewall and strong hashing algorithm with salt.

The Full sorting merge join algorithm is non-memory bound and can take advantage of the physical row order of the joined tables, skipping the sorting phase in such cases. It generally requires less memory than hash join algorithms but may have reduced execution speed if the data blocks are not already sorted by the join key. The partial merge join algorithm is optimized for minimizing memory usage when large tables are joined and always fully sorts the right table first via external sorting. This optimization allows it to skip the need for in-memory sorting, reducing memory consumption at the expense of relatively slower execution speed.

ClickHouse Cloud enables users to leverage its powerful IP-based geolocation feature, which can be used to determine the approximate location of an IP address. To achieve this, users need to import an open-source GeoIP dataset into ClickHouse, transform the data using bit functions, and create an ip_trie dictionary. The ip_trie dictionary is a structured dictionary that maps network prefixes (CIDR blocks) to coordinates and country codes. Users can then use the dictGet() function to perform IP lookups from the dictionary. Additionally, tools like Grafana and Metabase can be used to generate geospatial visualizations from the latitude/longitude coordinates, but pre-aggregating the data using geo-hashing is necessary to make it efficient for visualization. By following these steps, users can gain valuable insights into the geographic distribution of their users and better understand how they interact with their websites and applications.

The Open Observatory of Network Interference (OONI) is a non-profit free software project that empowers decentralized efforts in documenting internet censorship worldwide. It provides free software tools for users to test their internet connection quality, detect censorship, and measure network interference. OONI uses ClickHouse for handling large volumes of data, as its data storage and analytics engine. The organization was established more than ten years ago to increase transparency about internet censorship worldwide. OONI collects the data generated by its global network of volunteers, analyzes it, publishes it as open data, and produces research that contextualizes the findings within specific countries, regions, and ongoing social or political events. By switching to ClickHouse, they improved their architecture for faster data access and analysis, allowing real-time publication of measurements and aiding human rights defenders in rapidly responding to censorship events around the world.

Hash tables are the core data structure used in ClickHouse for aggregation and join operations. They provide constant average performance for insert, lookup, and delete operations. The design of a hash table is critical for performance, as it depends on factors such as the type of keys, unique keys, and load factor. ClickHouse uses a zero-cost C++ framework to generate ideal hash tables for specific use cases. The framework provides a policy-based design, allowing users to customize the hash function, allocator, cell, grower, and hash table itself. It also includes features such as zero-value storage, custom resizing policies, and LRU caches. Specialized hash tables are available for various scenarios, including small tables, string hash tables, and two-level hash tables. The framework's performance is optimized for cache locality, making it suitable for large-scale data aggregation and join operations.

At ClickHouse, the company revisited the comparison between OLTP databases like Postgres and OLAP databases like themselves to help developers understand when to use each for an application. They demonstrated how to enrich an application built with Supabase using real-time analytics powered by ClickHouse and integrated using Foreign Data Wrappers (FDWs). The demo showed how users can query ClickHouse from a Supabase instance, integrate real-time analytics into the application without leaving the familiar Supabase ecosystem, and use FDWs to communicate through a single interface. To achieve this, they used Postgres as an OLTP database for transactional data and ClickHouse as an OLAP database for analytical queries. The company also discussed best practices when using FDWs with ClickHouse, including parameterized views and pushing query push-down. They concluded by explaining how to update analytics data in ClickHouse periodically, either by inserting rows into the table or pulling rows from a Supabase instance.

ClickHouse Cloud is now available on Google Cloud Platform (GCP) in public Beta, enabling users to build real-time applications without the overhead of a self-managed installation. The service offers simplicity and ease of use, with autoscaling and no overprovisioning or unused capacity charges. Users can choose from two options: Development for small workloads and Storage for production environments. ClickHouse Cloud on GCP features integration with Google Cloud Storage, end-to-end encryption, endpoint security, a built-in SQL Console, and seamless database connections. The service is available in three geographies and offers a 30-day trial with $300 in usage credits, after which users can choose to continue on a pay-as-you-go monthly plan or reach out for volume-based discounts.

Coinpaprika is a cryptocurrency market data platform that offers comprehensive insights into price data, historical trends, and other relevant metrics for numerous cryptocurrencies across hundreds of exchanges. The company transitioned from InfluxDB to ClickHouse for improved performance and scalability, overcoming challenges with server overload, response times, and lack of built-in replication and scalability in the open-source version of InfluxDB. Coinpaprika evaluated various factors before choosing ClickHouse, including its single binary architecture, availability of training materials, community support, storage size, query throughput, sales team engagement, maturity of technology, and out-of-the-box support provided by Gorm. Since adopting ClickHouse, the platform has experienced significant improvements in performance, query concurrency, and scalability, enabling them to develop their product using ClickHouse within three months and migrate all data to ClickHouse. The seamless integration with Go and Gorm infrastructure has optimized their development process, allowing Coinpaprika to offer a reliable and user-friendly experience for developers, investors, and businesses in the cryptocurrency space.

ClickHouse Cloud provides painless configuration management for on-premise ClickHouse deployments`, allowing users to focus on data analysis, while support engineers must stay up-to-date with features and provide world-class support. To assist with this, ClickHouse Support is providing docker-compose files for production deployments of ClickHouse beyond a simple single server installation, giving users confidence in their tested architecture. The deployment considerations depend on the stage of the ClickHouse journey, whether it's running ClickHouse Cloud or setting up a single server for data import and analysis. `One shard until hardware capacity is reached` is the general guideline for determining the number of shards needed. Additional resources and examples are available on the ClickHouse documentation website and GitHub repo.

Zing Data is a modern data analysis and business intelligence platform that supports fast queries on ClickHouse databases, enabling users to query with natural language querying, visually query in just a few taps, set up real-time alerts, and query based on their phone's current location. The platform provides a powerful yet simple interface for querying and visualizing ClickHouse data on mobile devices, the web, or desktops, with support for various visualizations and complex data operations. Zing Data's integration with OpenAI enables users to ask complex questions in a conversational style without worrying about syntax or debugging SQL, while real-time alerts allow users to monitor their data and take action when important trends or anomalies are detected. The platform is highly customizable and suitable for both data analysts and business users.