{"id":16370,"date":"2024-09-25T17:13:09","date_gmt":"2024-09-26T00:13:09","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=16370"},"modified":"2025-06-13T21:54:21","modified_gmt":"2025-06-14T04:54:21","slug":"simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/","title":{"rendered":"Simplifying Real-Time Analytics on JSON: Capella Columnar vs. ClickHouse"},"content":{"rendered":"<p><span style=\"font-weight: 400;\">Ensuring a seamless flow of data is crucial for effective analysis and decision-making. Today, with data often being unstructured and nested, the choice of database plays a significant role in optimizing processing efficiency and query performance.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">In this blog post, we\u2019ll explore the process of data ingestion from MongoDB, a NoSQL database into both ClickHouse, a relational database, and <a href=\"https:\/\/www.couchbase.com\/products\/analytics\/\">Couchbase Capella Columnar<\/a>, a NoSQL analytical database. We&#8217;ll focus on the preprocessing needed, query efficiency for joins after ingestion, and how each handles real-time data changes. We will show how Capella Columnar simplifies working with nested data, making it easier to store and query compared to the complexities in relational databases like ClickHouse.<\/span><\/p>\n<hr \/>\n<p><strong>TL;DR<\/strong><\/p>\n<p><span style=\"font-weight: 400;\">ClickHouse requires extensive preprocessing for data ingestion due to the unnesting of nested data, and adding new fields can necessitate the creation of additional tables. Data from a single MongoDB collection must be split into separate tables, which means joins are required for querying\u2014these can be resource-intensive. Moreover, adding a new nested field in real-time involves re-creating separate tables and updating schemas, leading to potential pipeline breaks and requiring manual intervention. In contrast, Capella Columnar simplifies the process by not requiring preprocessing for nested data. It allows direct ingestion from MongoDB collections without the need for separate tables or joins, and it automatically reflects real-time changes like adding new nested fields without additional processing.<\/span><\/p>\n<hr \/>\n<h2>What is Capella Columnar?<\/h2>\n<p>Before diving into this migration demo, you may want to watch our in-depth overview of Capella Columnar video or read our <a href=\"https:\/\/www.couchbase.com\/blog\/free-tier-capella-columnar-mobile-vector-search-and-more\/\">recent announcement<\/a> that introduce this new technology that converges operational and real-time analytic workloads:<\/p>\n<p><iframe loading=\"lazy\" title=\"Couchbase Capella Columnar: An in-depth technical overview\" width=\"900\" height=\"506\" src=\"https:\/\/www.youtube.com\/embed\/pXoenAEhz9Q?feature=oembed&#038;enablejsapi=1&#038;origin=https:\/\/www.couchbase.com\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/p>\n<h2><span style=\"font-weight: 400;\">Sample MongoDB data<\/span><\/h2>\n<p><span style=\"font-weight: 400;\">For the scope of this demo, we have used the sample MongoDB collection <\/span><span style=\"font-weight: 400;\">theaters<\/span><span style=\"font-weight: 400;\"> of\u00a0 the <\/span><em><span style=\"font-weight: 400;\">sample_mflix <\/span><\/em><span style=\"font-weight: 400;\">database.\u00a0<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Here is a sample document from the theaters collection:<\/span><\/p>\n<pre class=\"nums:false lang:default decode:true\">{\r\n\u00a0\u00a0\"_id\": {\r\n\u00a0\u00a0\u00a0\u00a0\"$oid\": \"59a47286cfa9a3a73e51e72c\"\r\n\u00a0\u00a0},\r\n\u00a0\u00a0\"theaterId\": 1000,\r\n\u00a0\u00a0\"location\": {\r\n\u00a0\u00a0\u00a0\u00a0\"address\": {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"street1\": \"x1\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"city\": \"Bloomington\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"state\": \"MN\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"zipcode\": \"55425\"\r\n\u00a0\u00a0\u00a0\u00a0},\r\n\u00a0\u00a0\u00a0\u00a0\"geo\": {\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"type\": \"Point\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"coordinates\": [\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0-93.24565,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a044.85466\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0]\r\n\u00a0\u00a0\u00a0\u00a0}\r\n\u00a0\u00a0}\r\n}<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-16371\" style=\"border: 1px solid black;\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image1-5-1024x535.png\" alt=\"\" width=\"900\" height=\"470\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image1-5-1024x535.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image1-5-300x157.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image1-5-768x401.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image1-5-1536x803.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image1-5-1320x690.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image1-5.png 1999w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><\/p>\n<h2><span style=\"font-weight: 400;\">Data ingestion from MongoDB to Capella Columnar<\/span><\/h2>\n<p><span style=\"font-weight: 400;\">We can utilize Kafka Links in Capella Columnar to ingest data from Kafka topics, where data from MongoDB collections has already been published.<\/span><span style=\"font-weight: 400;\"> To make this happen, users need to set up their Kafka pipeline, where MongoDB data is funneled into Kafka topics.<\/span><\/p>\n<h3><span style=\"font-weight: 400;\">Step 1 &#8211; connect MongoDB to Kafka<\/span><\/h3>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Download the <\/span><a href=\"https:\/\/debezium.io\/documentation\/reference\/stable\/connectors\/mongodb.html\"><span style=\"font-weight: 400;\">Debezium MongoDB source connector<\/span><\/a><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Run Kafka Connect \u2013 in either standalone or distributed mode<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><span style=\"font-weight: 400;\">Send a POST request with the required connection properties to link up with <\/span><span style=\"font-weight: 400;\"><br \/>\n<\/span> <span style=\"font-weight: 400;\">MongoDB<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<pre class=\"nums:false lang:default decode:true\">curl -X POST -H \"Content-Type: application\/json\" https:\/\/localhost:8083\/connectors -d '{\"name\": \"&lt;name&gt;\",\"config\": {\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"connector.class\": \"io.debezium.connector.mongodb.MongoDbConnector\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"capture.mode\": \"change_streams_update_full\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"mongodb.ssl.enabled\": \"true\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"topic.prefix\": \"&lt;topic_prefix&gt;\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"value.converter\": \"org.apache.kafka.connect.json.JsonConverter\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"value.converter.schemas.enable\": \"false\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"key.converter.schemas.enable\": \"false\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"key.converter\": \"org.apache.kafka.connect.json.JsonConverter\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"collection.include.list\": \"sample_mflix.theaters\",\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\"mongodb.connection.string\":&lt;mongo connection string&gt;}}'\u00af<\/pre>\n<p><i><span style=\"font-weight: 400;\">Please replace the placeholders in the curl request (indicated by the angle brackets, e.g., <\/span><\/i><i><span style=\"font-weight: 400;\">&lt;value&gt;<\/span><\/i><i><span style=\"font-weight: 400;\">) with the appropriate values for your specific use case.<\/span><\/i><\/p>\n<p><span style=\"font-weight: 400;\">After that, your data will find its way into the Kafka topic. The data will be present in the Confluent Kafka topic <\/span><em>mongo_columnar_topic.sample_mflix.theaters<\/em>:<\/p>\n<p><span style=\"font-weight: 400;\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-16372\" style=\"border: 1px solid black;\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image9-1024x499.png\" alt=\"\" width=\"900\" height=\"439\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image9-1024x499.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image9-300x146.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image9-768x374.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image9-1536x748.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image9-1320x643.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image9.png 1999w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><\/span><\/p>\n<p><span style=\"font-weight: 400;\">Once the data in the Kafka topic, now we can use Capella Columnar to pull data from the Kafka topic into collections.<\/span><\/p>\n<h3><span style=\"font-weight: 400;\">Step 2 &#8211; create a link in Capella Columnar<\/span><\/h3>\n<p><span style=\"font-weight: 400;\">In this example we are using Confluent Kafka as the Kafka flavor, Capella Columnar also supports Amazon MSK as well.<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-16373\" style=\"border: 1px solid black;\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image4-1-1024x500.png\" alt=\"\" width=\"900\" height=\"439\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image4-1-1024x500.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image4-1-300x147.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image4-1-768x375.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image4-1-1536x751.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image4-1-1320x645.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image4-1.png 1999w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-16374\" style=\"border: 1px solid black;\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image3-3-1024x503.png\" alt=\"\" width=\"900\" height=\"442\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image3-3-1024x503.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image3-3-300x147.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image3-3-768x377.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image3-3-1536x755.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image3-3-1320x648.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image3-3.png 1999w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><\/p>\n<h3><span style=\"font-weight: 400;\">Step 3 &#8211; create a linked collection<\/span><\/h3>\n<p><span style=\"font-weight: 400;\">After creating a link, we need to create a linked collection where data will be stored:<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-16375\" style=\"border: 1px solid black;\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image5-2-1024x503.png\" alt=\"\" width=\"900\" height=\"442\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image5-2-1024x503.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image5-2-300x147.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image5-2-768x377.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image5-2-1536x754.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image5-2-1320x648.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image5-2.png 1999w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">The fields are self explanatory, the field primary key is the path of the primary key in the MongoDB document residing in Kafka topic. In the given example the primary key for the MongoDB document is <em>objectId<\/em>:<\/span><\/p>\n<pre class=\"nums:false lang:default decode:true \">_id`$oid`: String<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-16376\" style=\"border: 1px solid black;\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image17-1024x502.png\" alt=\"\" width=\"900\" height=\"441\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image17-1024x502.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image17-300x147.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image17-768x377.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image17-1536x753.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image17-1320x647.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image17.png 1999w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">Enabling CDC creates an agreement on the format of the document which the Capella Columnar engine understands. Currently we only support Debezium as a source connector.<\/span><\/p>\n<h3><span style=\"font-weight: 400;\">Step 4 &#8211; connect the link<\/span><\/h3>\n<p><span style=\"font-weight: 400;\">After creating the collection we have to <\/span><b>connect <\/b><span style=\"font-weight: 400;\">the link.<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-16377\" style=\"border: 1px solid black;\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image8-1-1024x500.png\" alt=\"\" width=\"900\" height=\"439\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image8-1-1024x500.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image8-1-300x147.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image8-1-768x375.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image8-1-1536x751.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image8-1-1320x645.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image8-1.png 1999w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">Once the link has been connected, the data should be flowing in, as we can see from the below query.<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-16378\" style=\"border: 1px solid black;\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image7-2-1024x501.png\" alt=\"\" width=\"900\" height=\"440\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image7-2-1024x501.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image7-2-300x147.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image7-2-768x376.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image7-2-1536x751.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image7-2-1320x646.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image7-2.png 1999w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">The data ingestion from MongoDB to Capella Columnar was seamless and intuitive. Now, let\u2019s turn our attention to the challenges we encounter with relational databases like ClickHouse.<\/span><\/p>\n<h2><span style=\"font-weight: 400;\">Data migration from MongoDB to ClickHouse<\/span><\/h2>\n<p><span style=\"font-weight: 400;\">ClickHouse is a relational database, we can&#8217;t migrate data directly from MongoDB due to its document-based and nested data structures. This requires an additional data transformation step. The complexity of this transformation increases if the MongoDB data is highly nested or if additional fields are introduced, requiring adjustments, such as creating extra tables to manage these changes.<\/span><\/p>\n<h3>Creating a table in ClickHouse for MongoDB collections<\/h3>\n<p><span style=\"font-weight: 400;\">To create a table in ClickHouse that maps to a <\/span><a href=\"https:\/\/clickhouse.com\/docs\/en\/engines\/table-engines\/integrations\/mongodb\"><span style=\"font-weight: 400;\">MongoDB<\/span><\/a><span style=\"font-weight: 400;\"> collection, we use the following syntax:<\/span><\/p>\n<pre class=\"nums:false lang:default decode:true\">CREATE TABLE [IF NOT EXISTS] [db.]table_name\r\n(\r\n\u00a0\u00a0\u00a0name1 [type1],\r\n\u00a0\u00a0\u00a0name2 [type2],\r\n\u00a0\u00a0\u00a0...\r\n) ENGINE = MongoDB(host:port, database, collection, user, password [, options]);<\/pre>\n<p><span style=\"font-weight: 400;\">The MongoDB collection referenced here should be <em>unnested<\/em> before pointing to it.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">We have a <\/span><span style=\"font-weight: 400;\">theaters<\/span><span style=\"font-weight: 400;\"> collection in MongoDB, and our goal is to represent this data in a structured, relational format in ClickHouse.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Since ClickHouse does not support nested fields, we&#8217;ll need to create three tables to handle the <\/span><span style=\"font-weight: 400;\">theaters<\/span><span style=\"font-weight: 400;\"> data with these schemas:<\/span><\/p>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>theaters<\/b><span style=\"font-weight: 400;\">: _id, theaterId<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>theaters_location_address<\/b><span style=\"font-weight: 400;\">: Street1, city, state, zipcode<\/span><\/li>\n<li style=\"font-weight: 400;\" aria-level=\"1\"><b>theaters_location_geo<\/b><span style=\"font-weight: 400;\">: type, coordinates<\/span><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<h3><span style=\"font-weight: 400;\">Preprocessing<\/span><\/h3>\n<p><span style=\"font-weight: 400;\">To prepare the data, we first perform preprocessing on the MongoDB side to unnest the necessary fields. This involves creating MongoDB pipelines to extract and flatten the nested fields into separate collections for <\/span><em><span style=\"font-weight: 400;\">Address<\/span><\/em><span style=\"font-weight: 400;\"> and <\/span><em><span style=\"font-weight: 400;\">Geo<\/span><\/em><span style=\"font-weight: 400;\">, while the <\/span><em><span style=\"font-weight: 400;\">Theaters<\/span><\/em><span style=\"font-weight: 400;\"> table can be created directly.<\/span><\/p>\n<p>Pipelines for MongoDB <em>Address <\/em>collection:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-16379\" style=\"border: 1px solid black;\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image14-1024x587.png\" alt=\"\" width=\"900\" height=\"516\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image14-1024x587.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image14-300x172.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image14-768x440.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image14-1536x881.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image14-1320x757.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image14.png 1999w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><\/p>\n<p>Pipeline for <i>Geo<\/i><span style=\"font-weight: 400;\"> collection:<\/span><\/p>\n<p><span style=\"font-weight: 400;\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-16380\" style=\"border: 1px solid black;\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image6-2-1024x531.png\" alt=\"\" width=\"900\" height=\"467\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image6-2-1024x531.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image6-2-300x156.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image6-2-768x398.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image6-2-1536x797.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image6-2-1320x685.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image6-2.png 1999w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><br \/>\n<\/span><\/p>\n<h3>Creating tables in ClickHouse<\/h3>\n<p><span style=\"font-weight: 400;\">Once preprocessing is complete, we create three corresponding tables in ClickHouse:<\/span><\/p>\n<p>Query to create <em>theaters<\/em>:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-16381\" style=\"border: 1px solid black;\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image15-1024x508.png\" alt=\"\" width=\"900\" height=\"446\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image15-1024x508.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image15-300x149.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image15-768x381.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image15-1536x762.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image15-1320x655.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image15.png 1999w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><\/p>\n<p>Query to create <em>theaters_location_address:<\/em><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-16382\" style=\"border: 1px solid black;\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image2-5-1024x539.png\" alt=\"\" width=\"900\" height=\"474\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image2-5-1024x539.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image2-5-300x158.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image2-5-768x405.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image2-5-1536x809.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image2-5-1320x695.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image2-5.png 1999w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><\/p>\n<p>Query to create <em>theaters_location_geo<\/em>:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-16383\" style=\"border: 1px solid black;\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image13-1024x514.png\" alt=\"\" width=\"900\" height=\"452\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image13-1024x514.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image13-300x151.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image13-768x386.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image13-1536x771.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image13-1320x663.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image13.png 1999w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">Having completed the data ingestion from MongoDB to ClickHouse, let us explore how real-time data changes are handled in both systems. We\u2019ll examine the ease of managing these changes in Capella Columnar compared to the challenges faced with ClickHouse.<\/span><\/p>\n<h2><span style=\"font-weight: 400;\">How are real-time changes handled?<\/span><\/h2>\n<h3>Modification on the MongoDB side<\/h3>\n<p><span style=\"font-weight: 400;\">Let&#8217;s change one document on MongoDB and add a nested owner field.<\/span><\/p>\n<pre class=\"nums:false lang:default decode:true\">{\r\n\u00a0\u00a0\"Owner\": {\r\n\u00a0\u00a0\u00a0\u00a0\"name\": \"John\",\r\n\u00a0\u00a0\u00a0\u00a0\"age\": 50\r\n\u00a0\u00a0}\r\n}<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-16384\" style=\"border: 1px solid black;\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image16-1024x503.png\" alt=\"\" width=\"900\" height=\"442\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image16-1024x503.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image16-300x147.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image16-768x377.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image16-1536x755.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image16-1320x648.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image16.png 1999w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><\/p>\n<h3>Live CDC changes in Capella Columnar<\/h3>\n<p><span style=\"font-weight: 400;\">No extra step is required. On querying the particular document, we can see the change getting reflected:<\/span><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-16385\" style=\"border: 1px solid black;\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image12-1024x504.png\" alt=\"\" width=\"900\" height=\"443\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image12-1024x504.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image12-300x148.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image12-768x378.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image12-1536x756.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image12-1320x650.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image12.png 1999w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><\/p>\n<h3><span style=\"font-weight: 400;\">Setting up ClickHouse<\/span><\/h3>\n<p><span style=\"font-weight: 400;\">ClickHouse faces challenges for real-time changes, as the pipeline breaks when attempting to bring in changes directly.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">To fetch the updated data containing the <\/span><em><span style=\"font-weight: 400;\">Owner<\/span><\/em><span style=\"font-weight: 400;\"> field, you\u2019ll need to create another table in ClickHouse. First, create a new MongoDB collection where the <\/span><em><span style=\"font-weight: 400;\">Owner<\/span><\/em><span style=\"font-weight: 400;\"> field is unnested, and then define a corresponding table in ClickHouse.<\/span><\/p>\n<p>Pipeline on MongoDB to create owner collection:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-16386\" style=\"border: 1px solid black;\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image11-1024x505.png\" alt=\"\" width=\"900\" height=\"444\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image11-1024x505.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image11-300x148.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image11-768x378.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image11-1536x757.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image11-1320x650.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image11.png 1999w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><b><\/b><\/p>\n<p>Creating the <em>owner<\/em> table in ClickHouse:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-16387\" style=\"border: 1px solid black;\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image10-1024x509.png\" alt=\"\" width=\"900\" height=\"447\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image10-1024x509.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image10-300x149.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image10-768x382.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image10-1536x763.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image10-1320x656.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image10.png 1999w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><\/p>\n<p><span style=\"font-weight: 400;\">Now, as we transition to querying the data, it\u2019s important to note that in ClickHouse, executing even simple queries requires stitching the data together, as we created different tables to unnest the original data. In contrast, Capella Columnar handles these queries effortlessly, requiring no additional steps.<\/span><\/p>\n<h2><span style=\"font-weight: 400;\">Querying data in Capella Columnar vs. ClickHouse<\/span><\/h2>\n<p><span style=\"font-weight: 400;\">Let\u2019s retrieve all the <\/span><em><span style=\"font-weight: 400;\">theaterId<\/span><\/em><span style=\"font-weight: 400;\"> and <\/span><span style=\"font-weight: 400;\"><em>street1<\/em><\/span><span style=\"font-weight: 400;\">\u00a0where the <\/span><em><span style=\"font-weight: 400;\">geo<\/span><\/em> <span style=\"font-weight: 400;\">type<\/span><span style=\"font-weight: 400;\"> is <\/span><em><span style=\"font-weight: 400;\">Point<\/span><\/em><span style=\"font-weight: 400;\"> in both ClickHouse and Capella Columnar.<\/span><\/p>\n<p><span style=\"font-weight: 400;\">Query for Capella Columnar:<\/span><\/p>\n<pre class=\"nums:false lang:default decode:true\">select theaterId, address.street1 from columnar_th where geo.type = 'Point';<\/pre>\n<p><span style=\"font-weight: 400;\">Query for ClickHouse:<\/span><\/p>\n<pre class=\"nums:false wrap:true lang:default decode:true\">select theaterId, street1 from theaters_location_geo inner join theaters_location_address on theaters_location_geo.theaterId = theaters_location_address.theaterId where `type` = 'Point';<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-large wp-image-16388\" style=\"border: 1px solid black;\" src=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image18-1024x482.png\" alt=\"\" width=\"900\" height=\"424\" srcset=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image18-1024x482.png 1024w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image18-300x141.png 300w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image18-768x362.png 768w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image18-1536x723.png 1536w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image18-1320x621.png 1320w, https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/image18.png 1999w\" sizes=\"auto, (max-width: 900px) 100vw, 900px\" \/><\/p>\n<h2><span style=\"font-weight: 400;\">Comparison of ClickHouse and Capella Columnar<\/span><\/h2>\n<table>\n<tbody>\n<tr>\n<td><b>Parameter<\/b><\/td>\n<td><b>ClickHouse<\/b><\/td>\n<td><b>Capella Columnar<\/b><\/td>\n<\/tr>\n<tr>\n<td>Data Ingestion<\/td>\n<td>Extensive preprocessing is required due to the unnesting of nested data, and the addition of fields into columns can necessitate the creation of additional tables.<\/td>\n<td>No preprocessing required for nested data.<\/td>\n<\/tr>\n<tr>\n<td>Schema and Data Transformation<\/td>\n<td>Data from a single MongoDB collection needs to be split into separate tables, with joins required for querying, which can be expensive.<\/td>\n<td>Data can be ingested directly from the MongoDB collection without the need for separate tables or joins.<\/td>\n<\/tr>\n<tr>\n<td>Real-Time Data Changes<\/td>\n<td>Adding a new nested field requires re-creating separate tables to unnest the data, necessitating schema updates and potentially complex transformations.<\/p>\n<p>This leads to pipeline breaks and requires manual intervention.<\/td>\n<td>Real-time changes, like adding a new nested field, are automatically reflected without additional processing.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<h2><span style=\"font-weight: 400;\">Conclusion<\/span><\/h2>\n<p>In summary, Couchbase Capella offers a more streamlined approach to ingesting and querying nested MongoDB data, minimizing preprocessing and handling real-time changes with ease. In contrast, ClickHouse requires extensive data transformation and schema adjustments, making it less efficient for managing complex, nested structures. For environments dealing with real-time data and nested formats, Capella Columnar proves to be the more flexible and efficient choice.<\/p>\n<h2><span style=\"font-weight: 400;\">References<\/span><\/h2>\n<ul>\n<li style=\"list-style-type: none;\">\n<ul>\n<li>Watch: <a href=\"https:\/\/www.youtube.com\/watch?v=pXoenAEhz9Q\"><span style=\"font-weight: 400;\">Capella Columnar: An in-depth technical overview<\/span><\/a><\/li>\n<li><a href=\"https:\/\/dzone.com\/articles\/keep-calm-and-column-wise\"><span style=\"font-weight: 400;\">Keep Calm and Column Wise<\/span><\/a><\/li>\n<li><a href=\"https:\/\/www.vldb.org\/pvldb\/vol15\/p2085-alkowaileet.pdf\"><span style=\"font-weight: 400;\">Columnar Formats for Schemaless LSM-based Document Stores<\/span><\/a><\/li>\n<li><a href=\"https:\/\/docs.couchbase.com\/columnar\/intro\/intro.html\"><span style=\"font-weight: 400;\">About Capella Columnar<\/span><\/a><\/li>\n<li><a href=\"https:\/\/cloud.couchbase.com\">Sign up to use Couchbase Capella for free<\/a><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>Ensuring a seamless flow of data is crucial for effective analysis and decision-making. Today, with data often being unstructured and nested, the choice of database plays a significant role in optimizing processing efficiency and query performance. In this blog post, [&hellip;]<\/p>\n","protected":false},"author":85528,"featured_media":16389,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"inline_featured_image":false,"footnotes":""},"categories":[10129,2242,2225],"tags":[10034,1766,1309],"ppma_author":[10035,10036,9918],"class_list":["post-16370","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-columnar","category-connectors","category-cloud","tag-clickhouse","tag-data-migration","tag-mongodb"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v25.9 (Yoast SEO v25.9) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Simplifying Real-Time Analytics on JSON: Capella Columnar vs. ClickHouse - The Couchbase Blog<\/title>\n<meta name=\"description\" content=\"Efficiently ingest and query nested data in Capella Columnar vs. ClickHouse, comparing preprocessing, schema transformation, and real-time updates.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Simplifying Real-Time Analytics on JSON: Capella Columnar vs. ClickHouse\" \/>\n<meta property=\"og:description\" content=\"Efficiently ingest and query nested data in Capella Columnar vs. ClickHouse, comparing preprocessing, schema transformation, and real-time updates.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/\" \/>\n<meta property=\"og:site_name\" content=\"The Couchbase Blog\" \/>\n<meta property=\"article:published_time\" content=\"2024-09-26T00:13:09+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2025-06-14T04:54:21+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/blog-capella-columnar-clickhouse-1024x536.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1024\" \/>\n\t<meta property=\"og:image:height\" content=\"536\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Ayush Tripathi, Ritik Raj, Janhavi Tripurwar\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Ayush Tripathi\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"10 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/\"},\"author\":{\"name\":\"Ayush Tripathi\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/e1fff268cf639be15f4f150d9fc05519\"},\"headline\":\"Simplifying Real-Time Analytics on JSON: Capella Columnar vs. ClickHouse\",\"datePublished\":\"2024-09-26T00:13:09+00:00\",\"dateModified\":\"2025-06-14T04:54:21+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/\"},\"wordCount\":1366,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#organization\"},\"image\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/blog-capella-columnar-clickhouse.png\",\"keywords\":[\"clickhouse\",\"Data Migration\",\"mongodb\"],\"articleSection\":[\"Columnar\",\"Connectors\",\"Couchbase Capella\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/\",\"url\":\"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/\",\"name\":\"Simplifying Real-Time Analytics on JSON: Capella Columnar vs. ClickHouse - The Couchbase Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/blog-capella-columnar-clickhouse.png\",\"datePublished\":\"2024-09-26T00:13:09+00:00\",\"dateModified\":\"2025-06-14T04:54:21+00:00\",\"description\":\"Efficiently ingest and query nested data in Capella Columnar vs. ClickHouse, comparing preprocessing, schema transformation, and real-time updates.\",\"breadcrumb\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/#primaryimage\",\"url\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/blog-capella-columnar-clickhouse.png\",\"contentUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/blog-capella-columnar-clickhouse.png\",\"width\":2400,\"height\":1256},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.couchbase.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Simplifying Real-Time Analytics on JSON: Capella Columnar vs. ClickHouse\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#website\",\"url\":\"https:\/\/www.couchbase.com\/blog\/\",\"name\":\"The Couchbase Blog\",\"description\":\"Couchbase, the NoSQL Database\",\"publisher\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.couchbase.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#organization\",\"name\":\"The Couchbase Blog\",\"url\":\"https:\/\/www.couchbase.com\/blog\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png\",\"contentUrl\":\"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png\",\"width\":218,\"height\":34,\"caption\":\"The Couchbase Blog\"},\"image\":{\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/e1fff268cf639be15f4f150d9fc05519\",\"name\":\"Ayush Tripathi\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/image\/a1dd0a1398a0ca551eafe4c50a320b4d\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ab8d3244859342fa389fed32cf53f2d5dc94a4a30b0c570c7b336bcd9741fa64?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ab8d3244859342fa389fed32cf53f2d5dc94a4a30b0c570c7b336bcd9741fa64?s=96&d=mm&r=g\",\"caption\":\"Ayush Tripathi\"},\"url\":\"https:\/\/www.couchbase.com\/blog\/author\/ayushkumartripathi\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Simplifying Real-Time Analytics on JSON: Capella Columnar vs. ClickHouse - The Couchbase Blog","description":"Efficiently ingest and query nested data in Capella Columnar vs. ClickHouse, comparing preprocessing, schema transformation, and real-time updates.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/","og_locale":"en_US","og_type":"article","og_title":"Simplifying Real-Time Analytics on JSON: Capella Columnar vs. ClickHouse","og_description":"Efficiently ingest and query nested data in Capella Columnar vs. ClickHouse, comparing preprocessing, schema transformation, and real-time updates.","og_url":"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/","og_site_name":"The Couchbase Blog","article_published_time":"2024-09-26T00:13:09+00:00","article_modified_time":"2025-06-14T04:54:21+00:00","og_image":[{"width":1024,"height":536,"url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/blog-capella-columnar-clickhouse-1024x536.png","type":"image\/png"}],"author":"Ayush Tripathi, Ritik Raj, Janhavi Tripurwar","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Ayush Tripathi","Est. reading time":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/#article","isPartOf":{"@id":"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/"},"author":{"name":"Ayush Tripathi","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/e1fff268cf639be15f4f150d9fc05519"},"headline":"Simplifying Real-Time Analytics on JSON: Capella Columnar vs. ClickHouse","datePublished":"2024-09-26T00:13:09+00:00","dateModified":"2025-06-14T04:54:21+00:00","mainEntityOfPage":{"@id":"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/"},"wordCount":1366,"commentCount":0,"publisher":{"@id":"https:\/\/www.couchbase.com\/blog\/#organization"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/#primaryimage"},"thumbnailUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/blog-capella-columnar-clickhouse.png","keywords":["clickhouse","Data Migration","mongodb"],"articleSection":["Columnar","Connectors","Couchbase Capella"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/","url":"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/","name":"Simplifying Real-Time Analytics on JSON: Capella Columnar vs. ClickHouse - The Couchbase Blog","isPartOf":{"@id":"https:\/\/www.couchbase.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/#primaryimage"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/#primaryimage"},"thumbnailUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/blog-capella-columnar-clickhouse.png","datePublished":"2024-09-26T00:13:09+00:00","dateModified":"2025-06-14T04:54:21+00:00","description":"Efficiently ingest and query nested data in Capella Columnar vs. ClickHouse, comparing preprocessing, schema transformation, and real-time updates.","breadcrumb":{"@id":"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/#primaryimage","url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/blog-capella-columnar-clickhouse.png","contentUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/blog-capella-columnar-clickhouse.png","width":2400,"height":1256},{"@type":"BreadcrumbList","@id":"https:\/\/www.couchbase.com\/blog\/simplifying-real-time-analytics-on-json-capella-columnar-vs-clickhouse\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.couchbase.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Simplifying Real-Time Analytics on JSON: Capella Columnar vs. ClickHouse"}]},{"@type":"WebSite","@id":"https:\/\/www.couchbase.com\/blog\/#website","url":"https:\/\/www.couchbase.com\/blog\/","name":"The Couchbase Blog","description":"Couchbase, the NoSQL Database","publisher":{"@id":"https:\/\/www.couchbase.com\/blog\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.couchbase.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.couchbase.com\/blog\/#organization","name":"The Couchbase Blog","url":"https:\/\/www.couchbase.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png","contentUrl":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/2023\/04\/admin-logo.png","width":218,"height":34,"caption":"The Couchbase Blog"},"image":{"@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/e1fff268cf639be15f4f150d9fc05519","name":"Ayush Tripathi","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.couchbase.com\/blog\/#\/schema\/person\/image\/a1dd0a1398a0ca551eafe4c50a320b4d","url":"https:\/\/secure.gravatar.com\/avatar\/ab8d3244859342fa389fed32cf53f2d5dc94a4a30b0c570c7b336bcd9741fa64?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ab8d3244859342fa389fed32cf53f2d5dc94a4a30b0c570c7b336bcd9741fa64?s=96&d=mm&r=g","caption":"Ayush Tripathi"},"url":"https:\/\/www.couchbase.com\/blog\/author\/ayushkumartripathi\/"}]}},"authors":[{"term_id":10035,"user_id":85528,"is_guest":0,"slug":"ayushkumartripathi","display_name":"Ayush Tripathi","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/ab8d3244859342fa389fed32cf53f2d5dc94a4a30b0c570c7b336bcd9741fa64?s=96&d=mm&r=g","author_category":"","last_name":"Tripathi","first_name":"Ayush","job_title":"Software Engineer","user_url":"","description":""},{"term_id":10036,"user_id":85529,"is_guest":0,"slug":"ritikraj","display_name":"Ritik Raj","avatar_url":"https:\/\/secure.gravatar.com\/avatar\/cfe4dbb21fb16ea9137ea6cc359220689e437885565d7bcfaa8796438ba6c163?s=96&d=mm&r=g","author_category":"","last_name":"Raj","first_name":"Ritik","job_title":"Software Engineer 2","user_url":"","description":""},{"term_id":9918,"user_id":85045,"is_guest":0,"slug":"janhavitripurwar","display_name":"Janhavi Tripurwar","avatar_url":{"url":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/janhavi-couchbase.jpg","url2x":"https:\/\/www.couchbase.com\/blog\/wp-content\/uploads\/sites\/1\/2024\/09\/janhavi-couchbase.jpg"},"author_category":"","last_name":"Tripurwar","first_name":"Janhavi","job_title":"Software Engineer 2","user_url":"","description":""}],"_links":{"self":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts\/16370","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/users\/85528"}],"replies":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/comments?post=16370"}],"version-history":[{"count":0,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/posts\/16370\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/media\/16389"}],"wp:attachment":[{"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/media?parent=16370"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/categories?post=16370"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/tags?post=16370"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.couchbase.com\/blog\/wp-json\/wp\/v2\/ppma_author?post=16370"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}