| </a></div><button type="button" class="clean-btn close announcementBarClose_iXyO" aria-label="Close"><svg viewBox="0 0 15 15" width="14" height="14" style="color:white"><g stroke="currentColor" stroke-width="3.1"><path d="M.75.75l13.5 13.5M14.25.75L.75 14.25"></path></g></svg></button></div><nav aria-label="Main" class="navbar navbar--fixed-top"><div class="navbar__inner" style="padding:"><div class="navbar__items"><div class="navbar-left"><div class="navbar-logo-wrapper flex items-center"><a class="navbar__brand" href="/"><div class="navbar__logo"><img src="https://cdnd.selectdb.com/images/logo.svg" alt="Apache Doris" class="themedImage_ToTc themedImage--light_HNdA"><img src="https://cdnd.selectdb.com/images/logo.svg" alt="Apache Doris" class="themedImage_ToTc themedImage--dark_i4oU"></div><b class="navbar__title text--truncate"></b></a></div><a class="navbar__item navbar__link" style="text-align:center" href="/docs/get-starting/quick-start">Docs</a><a aria-current="page" class="navbar__item navbar__link navbar__link--active" style="text-align:center" href="/blog">Blog</a><a class="navbar__item navbar__link" style="text-align:center" href="/users">Users</a><a href="https://github.com/apache/doris/discussions" target="_blank" rel="noopener noreferrer" class="navbar__item navbar__link" style="text-align:center">Discussions</a><a class="navbar__item navbar__link" style="text-align:center" href="/ecosystem/cluster-management">Ecosystem</a><a class="navbar__item navbar__link" style="text-align:center" href="/community/join-community">Community</a></div></div><div class="navbar__items navbar__items--right"><button aria-label="Toggle navigation bar" aria-expanded="false" class="navbar__toggle clean-btn" type="button"><svg width="30" height="30" viewBox="0 0 30 30" aria-hidden="true"><path stroke="currentColor" stroke-linecap="round" stroke-miterlimit="10" stroke-width="2" d="M4 7h22M4 15h22M4 23h22"></path></svg></button><div class="docs-search searchBox_H2mL"><div class="navbar__search searchBarContainer_PzyC"><input placeholder="Search" aria-label="Search" class="navbar__search-input navbarSearchInput_tb6T"><div class="loadingRing__K5d searchBarLoadingRing_e2f0"><div></div><div></div><div></div><div></div></div><div class="searchHintContainer_m7ml"><kbd class="searchHint_zuPL">ctrl</kbd><kbd class="searchHint_zuPL">K</kbd></div></div></div><div class="custom-navbar-item navbar__item dropdown dropdown--hoverable dropdown--right"><a class="navbar__link" aria-haspopup="true" aria-expanded="false" role="button" href="/docs/get-starting/what-is-apache-doris"><span class="text-sm">Versions: </span></a><ul class="dropdown__menu"><li><a class="dropdown__link" style="text-align:center" href="/docs/dev/get-starting/what-is-apache-doris">Dev</a></li><li><a class="dropdown__link" style="text-align:center" href="/docs/get-starting/what-is-apache-doris">2.1</a></li><li><a class="dropdown__link" style="text-align:center" href="/docs/2.0/get-starting/what-is-apache-doris">2.0</a></li><li><a class="dropdown__link" style="text-align:center" href="/docs/1.2/get-starting/">1.2</a></li></ul></div><a class="navbar__item navbar__link header-right-button-primary navbar-download-mobile" style="text-align:center" href="/download">Download</a><a href="https://github.com/apache/doris" target="_blank" rel="noopener noreferrer" class="github-btn desktop header-right-button-github"></a><a href="https://join.slack.com/t/apachedoriscommunity/shared_invite/zt-2kl08hzc0-SPJe4VWmL_qzrFd2u2XYQA" target="_blank" rel="noopener noreferrer" class="slack-btn desktop header-right-button-slack"></a><a class="header-right-button-primary navbar-download-desktop" href="/download">Download</a></div></div><div class="navbar__bottom"></div><div role="presentation" class="navbar-sidebar__backdrop"></div></nav><div id="__docusaurus_skipToContent_fallback" class="main-wrapper mainWrapper_z2l0"><div role="region" aria-label="Skip to main content"><a class="skipToContent_fXgn" href="#__docusaurus_skipToContent_fallback">Skip to main content</a></div><div class="main-wrapper"><div class="mb-[4.875rem] container"><div class="lg:row lg:flex"><main class="col col--9 col--offset-1" itemscope="" itemtype="http://schema.org/Blog"><article class="blog-article-content" itemprop="blogPost" itemscope="" itemtype="http://schema.org/BlogPosting"><header><div class="text-center mb-4"><a class="text-[#8592A6] cursor-pointer hover:no-underline" href="/blog">Blog</a><span class="px-2 text-[#8592A6]">/</span><span><span class="s-tags"><span class="s-tag">Release Notes</span></span></span></div><h1 class="blog-post-title text-[2rem] leading-normal lg:text-[2.5rem] text-center" itemprop="headline">Another big leap: Apache Doris 2.1.0 is released</h1><div class="blog-info text-center flex justify-center text-sm text-black"><span class="authors"><span class="s-author text-black">Apache Doris</span></span><time datetime="2024-03-12T00:00:00.000Z" itemprop="datePublished" class="text-black ml-4">March 12, 2024</time></div></header><div id="__blog-post-container" class="markdown" itemprop="articleBody"><p>Dear Apache Doris community, we are thrilled to announce the advent of Apache Doris 2.1.0. In this version, you can expect:</p><ul><li><p><strong>Higher out-of-the-box query performance</strong>: 100% faster speed proven by TPC-DS 1TB benchmark tests.</p></li><li><p><strong>Improved data lake analytics capabilities</strong>: 4~6 times faster than Trino and Spark, compatibility with various SQL dialects for smooth migration, read/write interface based on Arrow Flight for 100 times faster data transfer.</p></li><li><p><strong>Solid support for semi-structured data analysis</strong>: a newly-added Variant data type, support for more IP types, and a more comprehensive suite of analytic functions.</p></li><li><p><strong>Materialized view with multiple tables</strong>: a new feature to accelerate multi-table joins, allowing transparent rewriting, auto refresh, materialized views of external tables, and direct query.</p></li><li><p><strong>Enhanced real-time writing efficiency</strong>: faster data writing at scale powered by AUTO_INCREMENT column, AUTO PARTITION, forward placement of MemTable, and Group Commit. </p></li><li><p><strong>Better workload management</strong>: optimizations of the Workload Group mechanism for higher performance stability and the display of SQL resource consumption in the runtime.</p></li></ul><p>We appreciate the 237 contributors who made nearly 6000 commits in total to the Apache Doris project, and the nearly 100 enterprise users who provided valuable feedback. We will keep aiming for the stars with our agile release planning, and we appreciate your feedback in the <a href="https://join.slack.com/t/apachedoriscommunity/shared_invite/zt-2kl08hzc0-SPJe4VWmL_qzrFd2u2XYQA" target="_blank" rel="noopener noreferrer">Apache Doris developer and user community</a>. </p><p><strong>Download from GitHub</strong>: <a href="https://github.com/apache/doris/releases" target="_blank" rel="noopener noreferrer">https://github.com/apache/doris/releases</a></p><p><strong>Download from website</strong>: <a href="https://doris.apache.org/download" target="_blank" rel="noopener noreferrer">https://doris.apache.org/download</a></p><h2 class="anchor anchorWithStickyNavbar_LWe7" id="higher-performance">Higher performance<a href="#higher-performance" class="hash-link" aria-label="Direct link to Higher performance" title="Direct link to Higher performance"></a></h2><p>Apache Doris V2.1 makes a big leap in out-of-the-box query performance. It can deliver high performance even for complicated SQL queries without any fine-tuning. TPC-DS 1TB benchmark tests with 1 Frontend and 3 Backends (48C, 192G each) show that:</p><ul><li><p>The total query execution time of V2.1.0 is 245.7s, <strong>up 100%</strong> from the 489.6s of V2.0.5;</p></li><li><p>V2.1 is more than twice as fast as V2.0.5 on one-third of the total 99 SQL queries, and outperforms V2.0.5 on over 80 of the SQL queries; </p></li><li><p>V2.1 delivers better performance in data filtering, sorting, aggregation, multi-table joins, sub-queries, and window function computation.</p></li></ul><p><img loading="lazy" alt="2.1-Doris-TPC-DS-higher-performance" src="https://cdnd.selectdb.com/assets/images/2.1-Doris-TPC-DS-best-performance-196cfa22d1783b3e3a367bed4e300dd1.png" width="1280" height="675" class="img_ev3q"></p><p>Meanwhile, we have compared Doris V2.1.0 against many other OLAP systems with the same hardware environment under various data sizes. <strong>Recurring results show that Doris is undoubtedly far ahead</strong>.</p><h3 class="anchor anchorWithStickyNavbar_LWe7" id="smarter-optimizer">Smarter optimizer<a href="#smarter-optimizer" class="hash-link" aria-label="Direct link to Smarter optimizer" title="Direct link to Smarter optimizer"></a></h3><p>In our last big release, we introduced a new query optimizer that enables fast performance for most use cases without any manual fine-tuning. Now, the V2.1 query optimizer is an upgrade on that basis. It comes with:</p><ul><li><p><strong>More solid infrastructure</strong>: We have improved the statistics-based inference and the cost model that underpin the query optimizer, so it can collect statistical information from a wider range to undertake more complicated optimization tasks.</p></li><li><p><strong>Extended optimization rules</strong>: Absorbing feedback from our many actual use cases, we have improved many frequently used rules (operator pushdown, etc.) and introduced new rules to fit in more scenarios.</p></li><li><p><strong>Enhanced enumeration framework</strong>: Building on Cascades and DPhyper, the V2.1 query optimizer has a clearer enumeration strategy that achieves a better balance between quality and efficiency. For example, we have dialed up the default limit of query plans in the enumeration table from 5 to 8, and then we have sharpened the DPhyper enumeration capabilities to produce better query plans.</p></li></ul><h3 class="anchor anchorWithStickyNavbar_LWe7" id="better-heuristic-optimization">Better heuristic optimization<a href="#better-heuristic-optimization" class="hash-link" aria-label="Direct link to Better heuristic optimization" title="Direct link to Better heuristic optimization"></a></h3><p>In large-scale data analytics or data lake scenarios, it is always challenging and time-consuming to collect statistical information to provide references for query plans. For that, the V2.1 query optimizer, leveraging a combination of heuristic technologies, is able to generate <strong>high-quality query plans without statistical reference</strong>. Meanwhile, the RuntimeFilter is part of the trick. It is now more self-adaptive. It can self-adjust the predicates in expressions during execution, so it can enable higher performance without statistical information.</p><h3 class="anchor anchorWithStickyNavbar_LWe7" id="parallel-adaptive-scan">Parallel Adaptive Scan<a href="#parallel-adaptive-scan" class="hash-link" aria-label="Direct link to Parallel Adaptive Scan" title="Direct link to Parallel Adaptive Scan"></a></h3><p>A complex data query will involve large sums of data scanning, during which the scan I/O can be the bottleneck for query execution speed. That's why we have Parallel Scan, which means one scan thread can read multiple tablets (buckets). However, that is highly dependent on the bucket number you set for data partitioning in the first place. If the user has set an inappropriate number of buckets, the scan thread will not be able to work parallelly. </p><p>That's why we have adopted Parallel Adaptive Scan in Doris V2.1. What happens is that the tablets are pooled so the scanning process can be divided into a flexible number of threads based on the total number of rows. (The upper limit is 48 threads.) In this way, users no longer have to worry that their query speed might be dragged down by unreasonable bucket numbers.</p><p><img loading="lazy" alt="Parallel Adaptive Scan" src="https://cdnd.selectdb.com/assets/images/2.1-doris-parallel-adaptive-scan-ac95c58d6afe58c11121e37ee355685e.png" width="1280" height="424" class="img_ev3q"></p><p>In 2.1 and future versions, we recommend that you set <strong>the number of buckets equal to the total number of disks in the cluster</strong>, in order to fully utilize the I/O resources of the entire cluster.</p><div class="theme-admonition theme-admonition-note alert alert--secondary admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 14 16"><path fill-rule="evenodd" d="M6.3 5.69a.942.942 0 0 1-.28-.7c0-.28.09-.52.28-.7.19-.18.42-.28.7-.28.28 0 .52.09.7.28.18.19.28.42.28.7 0 .28-.09.52-.28.7a1 1 0 0 1-.7.3c-.28 0-.52-.11-.7-.3zM8 7.99c-.02-.25-.11-.48-.31-.69-.2-.19-.42-.3-.69-.31H6c-.27.02-.48.13-.69.31-.2.2-.3.44-.31.69h1v3c.02.27.11.5.31.69.2.2.42.31.69.31h1c.27 0 .48-.11.69-.31.2-.19.3-.42.31-.69H8V7.98v.01zM7 2.3c-3.14 0-5.7 2.54-5.7 5.68 0 3.14 2.56 5.7 5.7 5.7s5.7-2.55 5.7-5.7c0-3.15-2.56-5.69-5.7-5.69v.01zM7 .98c3.86 0 7 3.14 7 7s-3.14 7-7 7-7-3.12-7-7 3.14-7 7-7z"></path></svg></span>note</div><div class="admonitionContent_S0QG"><p>Parallel Adaptive Scan is currently available for the Duplicate Key model and the Merge-on-Write tables of the Unique Key model. We plan to add it to the Aggregate Key model and the Merge-on-Read tables of the Unique Key model in version 2.1.1.</p></div></div><h3 class="anchor anchorWithStickyNavbar_LWe7" id="local-shuffle">Local Shuffle<a href="#local-shuffle" class="hash-link" aria-label="Direct link to Local Shuffle" title="Direct link to Local Shuffle"></a></h3><p>We have introduced Local Shuffle to prevent uneven data distribution. Benchmark tests show that Local Shuffle in combination with Parallel Adaptive Scan can guarantee fast query performance despite unreasonable bucket number settings upon table creation.</p><p>For queries across multiple instances, uneven data distribution can prolong the query execution time. To address data skew across instances on a single backend (BE), we have introduced Local Shuffle in V2.1. It aims to shuffle and distribute data as evenly as possible, thereby accelerating queries. For example, in a typical aggregation query, a Local Shuffle node will redistribute the data evenly across different pipeline tasks, before the data is aggregated.</p><p><img loading="lazy" alt="Local Shuffle" src="https://cdnd.selectdb.com/assets/images/2.1-doris-local-shuffle-6f7365d5417f254f369c691827d034eb.png" width="1220" height="1280" class="img_ev3q"></p><p>For a proof of concept, we have simulated unreasonable bucket number settings. Firstly, we use the ClickBench dataset and run flat-table queries with the bucket number being 1 and 16, respectively. Then, we use the TPC-H 100G dataset and run join queries with 1 bucket and 16 buckets in each partition, respectively. Results from the runs show minimal fluctuations, which means the combination of Parallel Adaptive Scan and Local Shuffle is able to guarantee high query performance even with inappropriately sharded or unevenly distributed data.</p><p><img loading="lazy" alt="Clickbench and Local Shuffle" src="https://cdnd.selectdb.com/assets/images/2.1-Clickbench-and-Local-shuffle-1b3c08174cf021f9bb10a1b0ea1c3b72.png" width="1280" height="701" class="img_ev3q"></p><div class="theme-admonition theme-admonition-note alert alert--secondary admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 14 16"><path fill-rule="evenodd" d="M6.3 5.69a.942.942 0 0 1-.28-.7c0-.28.09-.52.28-.7.19-.18.42-.28.7-.28.28 0 .52.09.7.28.18.19.28.42.28.7 0 .28-.09.52-.28.7a1 1 0 0 1-.7.3c-.28 0-.52-.11-.7-.3zM8 7.99c-.02-.25-.11-.48-.31-.69-.2-.19-.42-.3-.69-.31H6c-.27.02-.48.13-.69.31-.2.2-.3.44-.31.69h1v3c.02.27.11.5.31.69.2.2.42.31.69.31h1c.27 0 .48-.11.69-.31.2-.19.3-.42.31-.69H8V7.98v.01zM7 2.3c-3.14 0-5.7 2.54-5.7 5.68 0 3.14 2.56 5.7 5.7 5.7s5.7-2.55 5.7-5.7c0-3.15-2.56-5.69-5.7-5.69v.01zM7 .98c3.86 0 7 3.14 7 7s-3.14 7-7 7-7-3.12-7-7 3.14-7 7-7z"></path></svg></span>note</div><div class="admonitionContent_S0QG"><p>See doc: <a href="https://doris.apache.org/docs/query-acceleration/pipeline-x-execution-engine/" target="_blank" rel="noopener noreferrer">https://doris.apache.org/docs/query-acceleration/pipeline-x-execution-engine/</a></p></div></div><h2 class="anchor anchorWithStickyNavbar_LWe7" id="increase-performance-on-arm">Increase performance on ARM<a href="#increase-performance-on-arm" class="hash-link" aria-label="Direct link to Increase performance on ARM" title="Direct link to Increase performance on ARM"></a></h2><p>V2.1 is specifically adapted to and optimized for ARM architecture. Compared to Doris 2.0.3, it has achieved over 100% performance improvement on multiple test datasets:</p><ul><li><p><strong>ClickBench large flat-table queries</strong>: The execution time of 43 SQL queries for V2.1 adds up to 30.73 seconds, as compared to 102.36 seconds for V2.0.3, representing a <strong>230%</strong> speedup.</p></li><li><p><strong>TPC-H multi-table joins</strong>: The execution time of 22 SQL queries for V2.1 adds up to 90.4 seconds, as compared to 174.8 seconds for V2.0.3, representing a <strong>93%</strong> speedup.</p></li></ul><h2 class="anchor anchorWithStickyNavbar_LWe7" id="improved-data-lake-analytics-capabilities">Improved data lake analytics capabilities<a href="#improved-data-lake-analytics-capabilities" class="hash-link" aria-label="Direct link to Improved data lake analytics capabilities" title="Direct link to Improved data lake analytics capabilities"></a></h2><h3 class="anchor anchorWithStickyNavbar_LWe7" id="data-lake-analytic-performance">Data lake analytic performance<a href="#data-lake-analytic-performance" class="hash-link" aria-label="Direct link to Data lake analytic performance" title="Direct link to Data lake analytic performance"></a></h3><p>V2.1 also reaches new heights in data lake analysis. According to TPC-DS benchmark tests (1TB) of Doris V2.1 against Trino V435,</p><ul><li><p>Without caching, Apache Doris is <strong>45% faster than</strong> <strong>Trino</strong>, with their total execution time being 717s and 1296s, respectively. Specifically, Doris outperforms Trino in 80% of the total 99 SQL queries.</p></li><li><p>If you enable file cache, you can expect another 2.2-time speedup from Doris (323s). <strong>That is 4 times the speed of Trino, with a straight win in all 99 SQL queries.</strong></p></li></ul><p>In addition, TPC-DS 10TB benchmark tests show that Apache Doris 2.1 is 4.2 times as fast as Spark 3.5.0 and 6.1 times as Spark 3.3.1.</p><p><img loading="lazy" alt="Data lake analytic performance" src="https://cdnd.selectdb.com/assets/images/2.1-doris-TPC-DS-326352a5581f27b587ff6e7d451c0ee4.png" width="950" height="550" class="img_ev3q"></p><p>This is achieved by a series of optimizations in I/O for HDFS and object storage, parquet/ORC file reading, floating-point decompression, predicate pushdown, caching, and scan task scheduling. It is also built upon a more precise cost model in the optimizer and more accurate statistics collection for different data sources.</p><h3 class="anchor anchorWithStickyNavbar_LWe7" id="sql-dialects-compatibility">SQL dialects compatibility<a href="#sql-dialects-compatibility" class="hash-link" aria-label="Direct link to SQL dialects compatibility" title="Direct link to SQL dialects compatibility"></a></h3><p>SQL incompatibility used to bother our users when they migrated from their existing OLAP systems (built on Clickhouse, Trino, Presto, Hive, etc.) to Doris, because they had to modify and update a significant amount of business query logic. Also, if they tried to use Doris as a unified data analysis gateway, they would also need to integrate it with their Hive or Spark systems, and incompatible SQLs could make it tough.</p><p>To facilitate a smooth migration or integration, we have enabled SQL dialect conversion in V2.1. Users can continue using the SQL dialect they are used to after simply setting the SQL dialect type for the current session in Doris. </p><p>So far, the ClickHouse, Presto, Trino, Hive, and Spark SQL dialects have been supported in this experimental feature. For example, by <code>set sql_dialect = "trino"</code>, you can perform queries using Trino SQL syntax, without any modifications. Tests in user production environment show that Doris V2.1 is compatible with 99% of Trino SQL. </p><div class="theme-admonition theme-admonition-note alert alert--secondary admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 14 16"><path fill-rule="evenodd" d="M6.3 5.69a.942.942 0 0 1-.28-.7c0-.28.09-.52.28-.7.19-.18.42-.28.7-.28.28 0 .52.09.7.28.18.19.28.42.28.7 0 .28-.09.52-.28.7a1 1 0 0 1-.7.3c-.28 0-.52-.11-.7-.3zM8 7.99c-.02-.25-.11-.48-.31-.69-.2-.19-.42-.3-.69-.31H6c-.27.02-.48.13-.69.31-.2.2-.3.44-.31.69h1v3c.02.27.11.5.31.69.2.2.42.31.69.31h1c.27 0 .48-.11.69-.31.2-.19.3-.42.31-.69H8V7.98v.01zM7 2.3c-3.14 0-5.7 2.54-5.7 5.68 0 3.14 2.56 5.7 5.7 5.7s5.7-2.55 5.7-5.7c0-3.15-2.56-5.69-5.7-5.69v.01zM7 .98c3.86 0 7 3.14 7 7s-3.14 7-7 7-7-3.12-7-7 3.14-7 7-7z"></path></svg></span>note</div><div class="admonitionContent_S0QG"><p>See Doc: <a href="https://doris.apache.org/docs/lakehouse/sql-dialect/" target="_blank" rel="noopener noreferrer">https://doris.apache.org/docs/lakehouse/sql-dialect/</a></p></div></div><h3 class="anchor anchorWithStickyNavbar_LWe7" id="high-speed-data-interface-for-100-fold-performance">High-speed data interface for 100-fold performance<a href="#high-speed-data-interface-for-100-fold-performance" class="hash-link" aria-label="Direct link to High-speed data interface for 100-fold performance" title="Direct link to High-speed data interface for 100-fold performance"></a></h3><p>Most big data systems today adopt columnar in-memory data formats and interact with other database systems using MySQL/JDBC/ODBC protocols. That means during data transfer, there is a need to covert the data from columnar format to row-based format to fit in with the MySQL/JDBC/ODBC protocols, and then vice versa. This serialization and deserialization process slows down the data transfer speed, which becomes more noticeable when the data size is huge, like in data science scenarios.</p><p>Apache Arrow is a columnar in-memory format designed for large-scale data processing. It has efficient data structures that facilitate faster data transfer across different systems. If both the source database and target client support Arrow Flight SQL protocol, data transfer between them will entail no data serialization and deserialization. That can cut down a huge chunk of overheads. Moreover, Arrow Flight can give full play to the multi-node and multi-core architecture to parallelize operations and thus increase throughputs.</p><p><img loading="lazy" alt="High-speed data interface for 100-fold performance" src="https://cdnd.selectdb.com/assets/images/2.1-doris-arrow-flight-3585c9cac786689f50ad42c8b76d3b8e.png" width="1280" height="436" class="img_ev3q"></p><p>Reading data from Apache Doris using Python used to be a complex process. Firslty, data blocks in Doris had to be converted from its columnar format into row-based bytes. Then, in the Python client, the data had to be deserialized into a Pandas data structure. These steps largely slow down data transfer.</p><p>Now this is revolutionized in Doris V2.1, where we provide a high-throughput data read/write interface based on Arrow Flight: HTTP Data API. Using Arrow Flight SQL, Doris converts the columnar data blocks into Arrow RecordBatch, which is also in columnar format. Then, in the Python client, Arrow RecordBatch is converted into column-oriented Pandas DataFrame. Both conversions are highly efficient and involve no serialization and deserialization. </p><p>This allows fast data access to Apache Doris by data science tools like Pandas and Numpy, which means Apache Doris can be seamlessly integrated with the entire AI and data science ecosystem. This unveils a future of endless possibilities. </p><div class="language-C++ codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#F8F8F2;--prism-background-color:#282A36"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-C++ codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#F8F8F2"><span class="token plain">conn = flight_sql.connect(uri="grpc://127.0.0.1:9090", db_kwargs={</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> adbc_driver_manager.DatabaseOptions.USERNAME.value: "user",</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> adbc_driver_manager.DatabaseOptions.PASSWORD.value: "pass",</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> })</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">cursor = conn.cursor()</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">cursor.execute("select * from arrow_flight_sql_test order by k0;")</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">print(cursor.fetchallarrow().to_pandas())</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>According to our comparative tests using different MySQL clients for the common data types, the Arrow Flight SQL protocol delivers almost 100 times faster performance than the MySQL protocol in data transfer.</p><p><img loading="lazy" alt="MySQL protocol" src="https://cdnd.selectdb.com/assets/images/2.1-doris-arrow-flight-sql-f8707eb6a387ed03a83fd20c373e810a.png" width="1280" height="502" class="img_ev3q"></p><h3 class="anchor anchorWithStickyNavbar_LWe7" id="other-improvements">Other improvements<a href="#other-improvements" class="hash-link" aria-label="Direct link to Other improvements" title="Direct link to Other improvements"></a></h3><ul><li><p>Paimon Catalog: upgrade to Paimon 0.6.0, optimized reading of Read Optimized tables, able to bring 10-fold speeds when Paimon data is fully merged</p></li><li><p>Iceberg Catalog: upgrade to Iceberg 1.4.3, fixed compatibility issues in AWS S3 authentication</p></li><li><p>Hudi Catalog: upgrade to Hudi 0.14.1, fixed compatibility issues in Hudi Flink Catalog</p></li></ul><h2 class="anchor anchorWithStickyNavbar_LWe7" id="materialized-view-with-multiple-tables">Materialized view with multiple tables<a href="#materialized-view-with-multiple-tables" class="hash-link" aria-label="Direct link to Materialized view with multiple tables" title="Direct link to Materialized view with multiple tables"></a></h2><p>As a typical "trade disk space for time" strategy, materialized views pre-compute and store SQL query results so that when the same queries are requested, the materialized view table can directly provide the results. This increases query performance and reduces resource consumption by avoiding repetitive computation.</p><p>Previous versions of Doris offer strong consistency for single-table materialized views, ensuring atomicity between the base table and the materialized view table. They also support smart routing for query statements on materialized views, allowing for efficient query execution.</p><p><strong>What's more exciting is that, in V2.1, we have introduced materialized views with multiple tables (also known as<a href="https://doris.apache.org/docs/query-acceleration/async-materialized-view/" target="_blank" rel="noopener noreferrer">asynchronous materialized view</a>).</strong> As the name implies, you can build a materialized view across tables. It can be based on full data or incremental data, and it can be refreshed manually or periodically. For multi-table joins or large data scale scenarios, the optimizer transparently rewrites queries based on the cost model and automatically searches for the right materialized view for <strong>optimal query performance</strong>. You can build asynchronous materialized views for external tables, and you can perform queries on these views directly. In other words, <strong>this can be a game changer for</strong> <strong>data warehouse</strong> <strong>layering, data modeling, job scheduling, and data processing</strong>.</p><p>Now let's get started: </p><p><strong>1. Create the tables:</strong></p><div class="language-SQL codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#F8F8F2;--prism-background-color:#282A36"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-SQL codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#F8F8F2"><span class="token plain">use tpch;</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">CREATE TABLE IF NOT EXISTS orders (</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> o_orderkey integer not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> o_custkey integer not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> o_orderstatus char(1) not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> o_totalprice decimalv3(15,2) not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> o_orderdate date not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> o_orderpriority char(15) not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> o_clerk char(15) not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> o_shippriority integer not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> o_comment varchar(79) not null</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> )</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> DUPLICATE KEY(o_orderkey, o_custkey)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> PARTITION BY RANGE(o_orderdate)(</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> DISTRIBUTED BY HASH(o_orderkey) BUCKETS 3</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> PROPERTIES ("replication_num" = "1");</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">insert into orders values</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> (1, 1, 'ok', 99.5, '2023-10-17', 'a', 'b', 1, 'yy'),</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> (2, 2, 'ok', 109.2, '2023-10-18', 'c','d',2, 'mm'),</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> (3, 3, 'ok', 99.5, '2023-10-19', 'a', 'b', 1, 'yy');</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">CREATE TABLE IF NOT EXISTS lineitem (</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_orderkey integer not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_partkey integer not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_suppkey integer not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_linenumber integer not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_quantity decimalv3(15,2) not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_extendedprice decimalv3(15,2) not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_discount decimalv3(15,2) not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_tax decimalv3(15,2) not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_returnflag char(1) not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_linestatus char(1) not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_shipdate date not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_commitdate date not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_receiptdate date not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_shipinstruct char(25) not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_shipmode char(10) not null,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_comment varchar(44) not null</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> )</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> DUPLICATE KEY(l_orderkey, l_partkey, l_suppkey, l_linenumber)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> PARTITION BY RANGE(l_shipdate)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> (FROM ('2023-10-17') TO ('2023-10-20') INTERVAL 1 DAY)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> DISTRIBUTED BY HASH(l_orderkey) BUCKETS 3</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> PROPERTIES ("replication_num" = "1");</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">insert into lineitem values</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> (1, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-17', '2023-10-17', '2023-10-17', 'a', 'b', 'yyyyyyyyy'),</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> (2, 2, 3, 4, 5.5, 6.5, 7.5, 8.5, 'o', 'k', '2023-10-18', '2023-10-18', '2023-10-18', 'a', 'b', 'yyyyyyyyy'),</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> (3, 2, 3, 6, 7.5, 8.5, 9.5, 10.5, 'k', 'o', '2023-10-19', '2023-10-19', '2023-10-19', 'c', 'd', 'xxxxxxxxx');</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> CREATE TABLE IF NOT EXISTS partsupp (</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> ps_partkey INTEGER NOT NULL,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> ps_suppkey INTEGER NOT NULL,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> ps_availqty INTEGER NOT NULL,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> ps_supplycost DECIMALV3(15,2) NOT NULL,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> ps_comment VARCHAR(199) NOT NULL </span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">DUPLICATE KEY(ps_partkey, ps_suppkey)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">DISTRIBUTED BY HASH(ps_partkey) BUCKETS 3</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">PROPERTIES (</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> "replication_num" = "1"</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">)</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p><strong>2. Create materialized view:</strong></p><div class="language-SQL codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#F8F8F2;--prism-background-color:#282A36"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-SQL codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#F8F8F2"><span class="token plain">CREATE MATERIALIZED VIEW mv1 </span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> BUILD DEFERRED REFRESH AUTO ON MANUAL</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> partition by(l_shipdate)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> DISTRIBUTED BY RANDOM BUCKETS 2</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> PROPERTIES ('replication_num' = '1') </span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> AS </span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> select l_shipdate, o_orderdate, l_partkey, </span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_suppkey, sum(o_totalprice) as sum_total</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> from lineitem</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> left join orders on lineitem.l_orderkey = orders.o_orderkey </span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> and l_shipdate = o_orderdate</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> group by</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_shipdate,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> o_orderdate,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_partkey,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> l_suppkey;</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>To sum up, asynchronous materialized view in V2.1 supports:</p><ul><li><p><strong>Transparent rewriting</strong>: transparently rewrites common operators including Select, Where, Join, Group By, and Aggregation, for faster query speed. For example, in BI reporting, you can create materialized views for some particularly slow queries.</p></li><li><p><strong>Auto refresh</strong>: periodic refresh, manual refresh, full refresh, (partition-based) incremental refresh.</p></li><li><p><strong>Materialized view of external tables</strong>: You can build materialized views based on external data such as Hive, Hudi, and Iceberg tables. You can also synchronize data from data lakes into Doris internal tables via materialized views.</p></li><li><p><strong>Direct query on materialized views</strong>: If you regard the making of materialized views as an ETL process, then the materialized views will be the result set of ETL. In this sense, materialized views can be seen as data tables, so users can conduct queries on them directly.</p></li></ul><h2 class="anchor anchorWithStickyNavbar_LWe7" id="enhanced-storage">Enhanced storage<a href="#enhanced-storage" class="hash-link" aria-label="Direct link to Enhanced storage" title="Direct link to Enhanced storage"></a></h2><h3 class="anchor anchorWithStickyNavbar_LWe7" id="auto_increment-column">AUTO_INCREMENT column<a href="#auto_increment-column" class="hash-link" aria-label="Direct link to AUTO_INCREMENT column" title="Direct link to AUTO_INCREMENT column"></a></h3><p>AUTO_INCREMENT column is a common feature in OLTP databases. It provides an efficient way to automatically assign unique identifiers to newly inserted data rows. However, it is less commonly found in distributed OLAP databases because the value allocation for AUTO_INCREMENT columns involves global transactions. </p><p>As an MPP-based OLAP system, Apache Doris V2.1 implements AUTO_INCREMENT column with an innovative pre-allocation strategy. Leveraging the uniqueness guarantee provided by AUTO_INCREMENT, users can achieve efficient dictionary encoding and query pagination.</p><p><strong>Dictionary encoding</strong>: AUTO_INCREMENT column is helpful for queries that require accurate deduplication, such as PV/UV calculation or user segmentation. Utilizing AUTO_INCREMENT column, you can create a dictionary table for string values like UserID or OrderID. Simply writing user data in batches or in real time to the dictionary table can generate a dictionary. Then, by applying various dimensional conditions, the corresponding bitmaps can be aggregated.</p><div class="language-SQL codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#F8F8F2;--prism-background-color:#282A36"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-SQL codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#F8F8F2"><span class="token plain">CREATE TABLE `demo`.`dictionary_tbl` (</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> `user_id` varchar(50) NOT NULL,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> `aid` BIGINT NOT NULL AUTO_INCREMENT</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">) ENGINE=OLAP</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">UNIQUE KEY(`user_id`)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">DISTRIBUTED BY HASH(`user_id`) BUCKETS 32</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">PROPERTIES (</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">"replication_allocation" = "tag.location.default: 3",</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">"enable_unique_key_merge_on_write" = "true"</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">);</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p><strong>Query pagination</strong>: Pagination is often necessary when displaying data on a webpage. Traditional pagination typically involves using <code>limit</code>, <code>offset</code> + <code>order by</code> in SQL queries. However, this can be inefficient in deep pagination queries, because even if only a small portion of the data is being requested, the database still needs to read and sort the entire dataset. This is addressed by AUTO_INCREMENT column. It generates a unique identifier for each row, remembers the maximum one from the previous page and uses it as a reference for retrieving the next page.</p><p>The following is an example, where <code>unique_value</code> is an AUTO INCREMENT column.</p><div class="language-SQL codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#F8F8F2;--prism-background-color:#282A36"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-SQL codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#F8F8F2"><span class="token plain">CREATE TABLE `demo`.`records_tbl2` (</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> `key` int(11) NOT NULL COMMENT "",</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> `name` varchar(26) NOT NULL COMMENT "",</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> `address` varchar(41) NOT NULL COMMENT "",</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> `city` varchar(11) NOT NULL COMMENT "",</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> `nation` varchar(16) NOT NULL COMMENT "",</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> `region` varchar(13) NOT NULL COMMENT "",</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> `phone` varchar(16) NOT NULL COMMENT "",</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> `mktsegment` varchar(11) NOT NULL COMMENT "",</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> `unique_value` BIGINT NOT NULL AUTO_INCREMENT</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">) DUPLICATE KEY (`key`, `name`)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">DISTRIBUTED BY HASH(`key`) BUCKETS 10</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">PROPERTIES (</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> "replication_num" = "3"</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">);</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>In pagination display, where each page displays 100 records, this is how you can fetch the data of the first page:</p><div class="language-SQL codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#F8F8F2;--prism-background-color:#282A36"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-SQL codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#F8F8F2"><span class="token plain">select * from records_tbl2 order by unique_value limit 100;</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>The program marks down the maximum of <code>unique_value</code> in the returned result (assuming it is 99). This is how you can fetch the data of the second page:</p><div class="language-SQL codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#F8F8F2;--prism-background-color:#282A36"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-SQL codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#F8F8F2"><span class="token plain">select * from records_tbl2 where unique_value > 99 order by unique_value limit 100;</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>If you need data from the latter pages, for example, page 101, it will be difficult to retrieve the maximum <code>unique_value</code> of page 100, so this is how you can perform the query:</p><div class="language-SQL codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#F8F8F2;--prism-background-color:#282A36"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-SQL codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#F8F8F2"><span class="token plain">select key, name, address, city, nation, region, phone, mktsegment</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">from records_tbl2, (select unique_value as max_value from records_tbl2 order by uniuqe_value limit 1 offset 9999) as previous_data</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">where records_tbl2.uniuqe_value > previous_data.max_value</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">order by unique_value limit 100;</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><div class="theme-admonition theme-admonition-note alert alert--secondary admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 14 16"><path fill-rule="evenodd" d="M6.3 5.69a.942.942 0 0 1-.28-.7c0-.28.09-.52.28-.7.19-.18.42-.28.7-.28.28 0 .52.09.7.28.18.19.28.42.28.7 0 .28-.09.52-.28.7a1 1 0 0 1-.7.3c-.28 0-.52-.11-.7-.3zM8 7.99c-.02-.25-.11-.48-.31-.69-.2-.19-.42-.3-.69-.31H6c-.27.02-.48.13-.69.31-.2.2-.3.44-.31.69h1v3c.02.27.11.5.31.69.2.2.42.31.69.31h1c.27 0 .48-.11.69-.31.2-.19.3-.42.31-.69H8V7.98v.01zM7 2.3c-3.14 0-5.7 2.54-5.7 5.68 0 3.14 2.56 5.7 5.7 5.7s5.7-2.55 5.7-5.7c0-3.15-2.56-5.69-5.7-5.69v.01zM7 .98c3.86 0 7 3.14 7 7s-3.14 7-7 7-7-3.12-7-7 3.14-7 7-7z"></path></svg></span>note</div><div class="admonitionContent_S0QG"><p>See doc: <a href="https://doris.apache.org/docs/advanced/auto-increment/" target="_blank" rel="noopener noreferrer">https://doris.apache.org/docs/advanced/auto-increment/</a></p></div></div><h3 class="anchor anchorWithStickyNavbar_LWe7" id="auto-partition">AUTO PARTITION<a href="#auto-partition" class="hash-link" aria-label="Direct link to AUTO PARTITION" title="Direct link to AUTO PARTITION"></a></h3><p>Before V2.1, Doris requires users to manually create data partitions before data ingestion, otherwise data loading will just fail. Now, to release burden on operation and maintenance, V2.1 allows AUTO PARTITION. Upon data ingestion, it detects whether a partition exists for the data based on the partitioning column. If not, it automatically creates one and starts data ingestion. </p><p>To apply AUTO PARTITION in Doris:</p><div class="language-SQL codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#F8F8F2;--prism-background-color:#282A36"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-SQL codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#F8F8F2"><span class="token plain">CREATE TABLE `DAILY_TRADE_VALUE`</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">(</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> `TRADE_DATE` datev2 NULL COMMENT 'Trade Date',</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> `TRADE_ID` varchar(40) NULL COMMENT 'Trade ID',</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> ......</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">UNIQUE KEY(`TRADE_DATE`, `TRADE_ID`)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">AUTO PARTITION BY RANGE date_trunc(`TRADE_DATE`, 'year')</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">(</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">DISTRIBUTED BY HASH(`TRADE_DATE`) BUCKETS 10</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">PROPERTIES (</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> "replication_num" = "1"</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">);</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><div class="theme-admonition theme-admonition-tip alert alert--success admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 12 16"><path fill-rule="evenodd" d="M6.5 0C3.48 0 1 2.19 1 5c0 .92.55 2.25 1 3 1.34 2.25 1.78 2.78 2 4v1h5v-1c.22-1.22.66-1.75 2-4 .45-.75 1-2.08 1-3 0-2.81-2.48-5-5.5-5zm3.64 7.48c-.25.44-.47.8-.67 1.11-.86 1.41-1.25 2.06-1.45 3.23-.02.05-.02.11-.02.17H5c0-.06 0-.13-.02-.17-.2-1.17-.59-1.83-1.45-3.23-.2-.31-.42-.67-.67-1.11C2.44 6.78 2 5.65 2 5c0-2.2 2.02-4 4.5-4 1.22 0 2.36.42 3.22 1.19C10.55 2.94 11 3.94 11 5c0 .66-.44 1.78-.86 2.48zM4 14h5c-.23 1.14-1.3 2-2.5 2s-2.27-.86-2.5-2z"></path></svg></span>tip</div><div class="admonitionContent_S0QG"><ol><li><p>Currently, you can only specify one partitioning column for AUTO PARTITION, and it has to be NOT NULL.</p></li><li><p>It supports AUTO PARTITION by Range or by List. For the former, it supports <code>date_trunc</code> as the partitioning function, and <code>DATE</code> or <code>DATETIME</code> format for the partitioning column. For the latter, it does not support function calling, it supports <code>BOOLEAN</code>, <code>TINYINT</code>, <code>SMALLINT</code>, <code>INT</code>, <code>BIGINT</code>, <code>LARGEINT</code>, <code>DATE</code>, <code>DATETIME</code>, <code>CHAR</code>, and <code>VARCHAR</code> for the partitioning column, and the values are enumeration values.</p></li><li><p>For AUTO PARTITION by List, if there is no partition for a value in the partitioning column, Doris will create one for it.</p></li></ol></div></div><div class="theme-admonition theme-admonition-note alert alert--secondary admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 14 16"><path fill-rule="evenodd" d="M6.3 5.69a.942.942 0 0 1-.28-.7c0-.28.09-.52.28-.7.19-.18.42-.28.7-.28.28 0 .52.09.7.28.18.19.28.42.28.7 0 .28-.09.52-.28.7a1 1 0 0 1-.7.3c-.28 0-.52-.11-.7-.3zM8 7.99c-.02-.25-.11-.48-.31-.69-.2-.19-.42-.3-.69-.31H6c-.27.02-.48.13-.69.31-.2.2-.3.44-.31.69h1v3c.02.27.11.5.31.69.2.2.42.31.69.31h1c.27 0 .48-.11.69-.31.2-.19.3-.42.31-.69H8V7.98v.01zM7 2.3c-3.14 0-5.7 2.54-5.7 5.68 0 3.14 2.56 5.7 5.7 5.7s5.7-2.55 5.7-5.7c0-3.15-2.56-5.69-5.7-5.69v.01zM7 .98c3.86 0 7 3.14 7 7s-3.14 7-7 7-7-3.12-7-7 3.14-7 7-7z"></path></svg></span>note</div><div class="admonitionContent_S0QG"><p>See doc: <a href="https://doris.apache.org/zh-CN/docs/table-design/data-partition#%E8%87%AA%E5%8A%A8%E5%88%86%E5%8C%BA" target="_blank" rel="noopener noreferrer">https://doris.apache.org/zh-CN/docs/table-design/data-partition#%E8%87%AA%E5%8A%A8%E5%88%86%E5%8C%BA</a></p></div></div><h3 class="anchor anchorWithStickyNavbar_LWe7" id="100-faster-insert-into-select">100% faster INSERT INTO SELECT<a href="#100-faster-insert-into-select" class="hash-link" aria-label="Direct link to 100% faster INSERT INTO SELECT" title="Direct link to 100% faster INSERT INTO SELECT"></a></h3><p><code>INSERT INTO…SELECT</code> is one of the most frequently used statements in ETL. It enables fast data migration, transformation, cleaning, and aggregation. That's why we've been optimizing its performance. In V2.0, we introduced Single Replica Load to reduce repetitive data writing and data compaction. </p><p>For further improvement, in V2.1, we have moved forward the execution of MemTable to reduce data ingestion overheads. Tests show that this can <strong>double the data ingestion speed in most cases compared to V2.0</strong>. </p><p><img loading="lazy" alt="100% faster INSERT INTO SELECT" src="https://cdnd.selectdb.com/assets/images/2.1-INSERT-INTO-SELECT-EN-381ebcb56119c8c3020120fde276cfc3.png" width="1280" height="751" class="img_ev3q"></p><p>The process comparison before and after moving forward the execution of MemTable is illustrated above. The Sink node no longer sends encoded data blocks but instead processes MemTable locally and sends the generated segments to downstream nodes. This reduces the overheads caused by multiple data encoding and improves the speed and accuracy of memory backpressure. In addition, we have replaced Ping-Pong RPC with Streaming RPC so there will be less waiting during data transfer.</p><p>We've done tests to see how moving forward MemTable impacts data ingestion performance.</p><p><strong>Test environment</strong>: 1 Frontend + 3 Backend, 16C 64G each node, 3 high-performance cloud disks (to make sure that disk I/O is not a bottleneck)</p><p><strong>Test results</strong>: </p><p>In single-replica ingestion, the execution time of V2.1 is only 36% of what takes V2.0 to finish. In three-replica ingestion, that figure is 54%. This means, V2.1 has sped up the performance of <code>INSERT INTO…SELECT</code> by more than 100% in general.</p><p><img loading="lazy" alt="Insert into table" src="https://cdnd.selectdb.com/assets/images/2.1-insert-into-select-0a877bdc14d4fabffc7e0f5da6c1c4d3.png" width="3501" height="1031" class="img_ev3q"></p><div class="theme-admonition theme-admonition-note alert alert--secondary admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 14 16"><path fill-rule="evenodd" d="M6.3 5.69a.942.942 0 0 1-.28-.7c0-.28.09-.52.28-.7.19-.18.42-.28.7-.28.28 0 .52.09.7.28.18.19.28.42.28.7 0 .28-.09.52-.28.7a1 1 0 0 1-.7.3c-.28 0-.52-.11-.7-.3zM8 7.99c-.02-.25-.11-.48-.31-.69-.2-.19-.42-.3-.69-.31H6c-.27.02-.48.13-.69.31-.2.2-.3.44-.31.69h1v3c.02.27.11.5.31.69.2.2.42.31.69.31h1c.27 0 .48-.11.69-.31.2-.19.3-.42.31-.69H8V7.98v.01zM7 2.3c-3.14 0-5.7 2.54-5.7 5.68 0 3.14 2.56 5.7 5.7 5.7s5.7-2.55 5.7-5.7c0-3.15-2.56-5.69-5.7-5.69v.01zM7 .98c3.86 0 7 3.14 7 7s-3.14 7-7 7-7-3.12-7-7 3.14-7 7-7z"></path></svg></span>note</div><div class="admonitionContent_S0QG"><p>V2.1 moves forward the execution of MemTable by default, so you don't have to modify the data ingestion command. You can return to the old ingestion method by setting <code>enable_memtable_on_sink_node=false</code> in MySQL connection.</p></div></div><h3 class="anchor anchorWithStickyNavbar_LWe7" id="high-concurrency-real-time-data-ingestion--group-commit">High-concurrency real-time data ingestion / Group Commit<a href="#high-concurrency-real-time-data-ingestion--group-commit" class="hash-link" aria-label="Direct link to High-concurrency real-time data ingestion / Group Commit" title="Direct link to High-concurrency real-time data ingestion / Group Commit"></a></h3><p>For data writing, V2.1 has a back pressure mechanism to avoid excessive data versions, so as to reduce resource consumption caused by data version merging. </p><p>During data ingestion, data batches are written to an in-memory table and then written to disk as individual RowSet files. Each RowSet file corresponds to a specific data import version. The background compaction process automatically merges the RowSets, combining the small ones into a big one in order to increase query speed and reduce storage consumption. However, each compaction process consumes CPU, memory, and disk IO resources. The more frequently that data is written, the more RowSets are generated, and the more resources the compaction process consumes. The backpressure mechanism is a solution to this. It will throw a -235 error when there are too many data versions.</p><p><img loading="lazy" alt="High-concurrency real-time data ingestion " src="https://cdnd.selectdb.com/assets/images/2.1-doris-group-commit-d6b8b226770cf1fdb5836854ed595e49.png" width="1280" height="912" class="img_ev3q"></p><p>In addition, V2.1 supports Group Commit, which means to accumulate multiple data writings in the backend and commit them as one batch. In this way, users don't have to keep their writing frequency at a low level because Doris will merge multiple writings into one. </p><p><img loading="lazy" alt="Group Comit" src="https://cdnd.selectdb.com/assets/images/2.1-doris-group-commit-2-4bc76d5f1b65ab702ea7156c1eab24a4.png" width="1280" height="757" class="img_ev3q"></p><p>Group Commit so far supports two modes: <code>sync_mode</code> and <code>async_mode</code>. The <code>sync_mode</code> commits multiple imports within a single transaction, and then the data becomes immediately visible. While in the <code>async_mode</code>, data is first written to the Write-Ahead Log (WAL). Then Doris, based on the system load and the value of <code>group_commit_interval</code>, asynchronously commits the data, after which the data becomes visible. When a single import is huge, the system automatically switches to the <code>sync_mode</code> to prevent the WAL from occupying too much disk space. </p><p>Benchmark tests on Group Commit (<code>async_mode</code>) with JDBC ingestion and the Stream Load method present great results.</p><ul><li><p><strong>JDBC ingestion:</strong> </p><ul><li><p>A 1 Frontend + 1 Backend cluster, TPC-H SF10 Lineitem table (22GB, 180 million rows);</p></li><li><p>At a concurrency level of 20, with each Insert involving less than 100 rows, Doris V2.1 reaches a writing speed of 106,900 row/s and a throughput of 11.46 MB/s. CPU usage of the Backend remains at 10%~20%.</p></li></ul></li><li><p><strong>Stream Load:</strong></p><ul><li><p>A 1 Frontend + 3 Backends cluster, httplogs (31GB, 247 million rows);</p></li><li><p>At a concurrency level of 10, with each writing involving less than 1MB, Doris returns a -235 error when disabling Group Commit. With Group Commit enabled, it delivers stable performance and reaches a writing speed of 810,000 row/s and a throughput of 104 MB/s.</p></li><li><p>At a concurrency level of 10, with each writing involving less than 10MB, enabling Group Commit increases the writing speed by 45% and the writing throughput by 79%.</p></li></ul></li></ul><div class="theme-admonition theme-admonition-note alert alert--secondary admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 14 16"><path fill-rule="evenodd" d="M6.3 5.69a.942.942 0 0 1-.28-.7c0-.28.09-.52.28-.7.19-.18.42-.28.7-.28.28 0 .52.09.7.28.18.19.28.42.28.7 0 .28-.09.52-.28.7a1 1 0 0 1-.7.3c-.28 0-.52-.11-.7-.3zM8 7.99c-.02-.25-.11-.48-.31-.69-.2-.19-.42-.3-.69-.31H6c-.27.02-.48.13-.69.31-.2.2-.3.44-.31.69h1v3c.02.27.11.5.31.69.2.2.42.31.69.31h1c.27 0 .48-.11.69-.31.2-.19.3-.42.31-.69H8V7.98v.01zM7 2.3c-3.14 0-5.7 2.54-5.7 5.68 0 3.14 2.56 5.7 5.7 5.7s5.7-2.55 5.7-5.7c0-3.15-2.56-5.69-5.7-5.69v.01zM7 .98c3.86 0 7 3.14 7 7s-3.14 7-7 7-7-3.12-7-7 3.14-7 7-7z"></path></svg></span>note</div><div class="admonitionContent_S0QG"><p>See doc and full test results: <a href="https://doris.apache.org/docs/data-operate/import/import-way/group-commit-manual/#performance" target="_blank" rel="noopener noreferrer">https://doris.apache.org/docs/data-operate/import/import-way/group-commit-manual/#performance</a></p></div></div><h2 class="anchor anchorWithStickyNavbar_LWe7" id="semi-structured-data-analysis">Semi-structured data analysis<a href="#semi-structured-data-analysis" class="hash-link" aria-label="Direct link to Semi-structured data analysis" title="Direct link to Semi-structured data analysis"></a></h2><h3 class="anchor anchorWithStickyNavbar_LWe7" id="a-new-data-type-variant">A new data type: Variant<a href="#a-new-data-type-variant" class="hash-link" aria-label="Direct link to A new data type: Variant" title="Direct link to A new data type: Variant"></a></h3><p>Before V2.1, Doris processes semi-structured data in two ways:</p><ol><li><p>It requires users to pre-define table schema, make a flat table, and parse the data before it is loaded into Doris. This method ensures fast data writing and avoids parsing upon query execution. The downside is the lack of flexibility. Any change to the table schema will require a lot of maintenance efforts.</p></li><li><p>It accommodates semi-structured data with JSON or stores it as JSON strings. Raw JSON data is ingested into Doris without any pre-processing and is parsed by functions upon query execution. This option requires no extra effort from the users, but you might need to put up with inefficient data parsing and reading.</p></li></ol><p>V2.1 supports a new data type named Variant. It can accommodate semi-structured data such as JSON as well as compound data structures that contain various data types such as integers, strings, and booleans. Users don't have to pre-define the exact data types for a Variant column in the table schema. </p><p>The Variant type is handy when processing nested data structures, where the structure can change dynamically. During data writing, it is capable of auto-inference for columns based on what is given, after which it merges them into the existing table schema, and stores the JSON keys and their corresponding values as dynamic sub-columns. </p><p>You can include both Variant columns and static columns with pre-defined data types in the same table. This provides greater flexibility in storage and queries. Additionally, the Variant type is empowered by the columnar storage, vectorized execution engine, and query optimizer for high efficiency in queries and storage.</p><p>Use Variant in Doris:</p><div class="language-SQL codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#F8F8F2;--prism-background-color:#282A36"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-SQL codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#F8F8F2"><span class="token plain">-- No index</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">CREATE TABLE IF NOT EXISTS ${table_name} (</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> k BIGINT,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> v VARIANT</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">table_properties;</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">-- Create index for the v column, specify the parser</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">CREATE TABLE IF NOT EXISTS ${table_name} (</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> k BIGINT,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> v VARIANT,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> INDEX idx_var(v) USING INVERTED [PROPERTIES("parser" = "english|unicode|chinese")] [COMMENT 'your comment']</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">table_properties;</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">-- Perform queries, access sub-columns using`[]`</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">SELECT v["properties"]["title"] from ${table_name}</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p><strong>Variant VS JSON</strong></p><p>In Apache Doris, JSON data is stored in a binary JSONB format, and the entire JSON row is stored in segments in a row-oriented way. However, with the Variant type, it automatically infers the data type upon data writing and stores the JSON data in a columnar method. Thus, no parsing is needed during queries.</p><p>Furthermore, the Variant type is optimized for sparse JSON scenarios. It only extracts frequently occurring columns. The sparse columns are stored in a separate format.</p><p>Tests prove that <strong>data in Variant columns takes up the same storage space as data in static columns, which is only 35% of that in JSON format</strong>. The Variant type should be a more cost-effective choice in low-cardinality scenarios.</p><p><img loading="lazy" alt="Variant vs JSON" src="https://cdnd.selectdb.com/assets/images/2.1-variant-vs-json-e0a280dc9f81543c22cb53f85170ac38.png" width="2585" height="962" class="img_ev3q"></p><p>In terms of query performance, <strong>the Variant type enables 8 times higher query speed than JSON</strong> in hot runs and even more in cold runs.</p><p><img loading="lazy" alt="Variant vs JSON" src="https://cdnd.selectdb.com/assets/images/2.1-variant-vs-json-2-c897e07d34381c7ce54e53284d6e04bf.png" width="3556" height="1095" class="img_ev3q"></p><div class="theme-admonition theme-admonition-tip alert alert--success admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 12 16"><path fill-rule="evenodd" d="M6.5 0C3.48 0 1 2.19 1 5c0 .92.55 2.25 1 3 1.34 2.25 1.78 2.78 2 4v1h5v-1c.22-1.22.66-1.75 2-4 .45-.75 1-2.08 1-3 0-2.81-2.48-5-5.5-5zm3.64 7.48c-.25.44-.47.8-.67 1.11-.86 1.41-1.25 2.06-1.45 3.23-.02.05-.02.11-.02.17H5c0-.06 0-.13-.02-.17-.2-1.17-.59-1.83-1.45-3.23-.2-.31-.42-.67-.67-1.11C2.44 6.78 2 5.65 2 5c0-2.2 2.02-4 4.5-4 1.22 0 2.36.42 3.22 1.19C10.55 2.94 11 3.94 11 5c0 .66-.44 1.78-.86 2.48zM4 14h5c-.23 1.14-1.3 2-2.5 2s-2.27-.86-2.5-2z"></path></svg></span>tip</div><div class="admonitionContent_S0QG"><ul><li><p>Currently, the Variant type is not supported in the Aggregate Key model of Doris. It can not be the primary key or sorting key in a Unique Key model table or Duplicate model table;</p></li><li><p>It is recommended to go with the RANDOM mode or Group Commit for higher writing performance;</p></li><li><p>It is recommended to extract non-standard JSON types such as date or decimal as static fields to enable higher performance;</p></li><li><p>In columnar format, arrays of two or more dimensions, as well as arrays with nested objects, will be stored as JSONB encoding, resulting in lower performance than native arrays;</p></li><li><p>Queries involving filtering or aggregation require the use of Cast, where the storage layer will provide hints for the storage engine for predicate pushdown based on the storage type and the Cast type and thus accelerate queries.</p></li></ul></div></div><div class="theme-admonition theme-admonition-note alert alert--secondary admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 14 16"><path fill-rule="evenodd" d="M6.3 5.69a.942.942 0 0 1-.28-.7c0-.28.09-.52.28-.7.19-.18.42-.28.7-.28.28 0 .52.09.7.28.18.19.28.42.28.7 0 .28-.09.52-.28.7a1 1 0 0 1-.7.3c-.28 0-.52-.11-.7-.3zM8 7.99c-.02-.25-.11-.48-.31-.69-.2-.19-.42-.3-.69-.31H6c-.27.02-.48.13-.69.31-.2.2-.3.44-.31.69h1v3c.02.27.11.5.31.69.2.2.42.31.69.31h1c.27 0 .48-.11.69-.31.2-.19.3-.42.31-.69H8V7.98v.01zM7 2.3c-3.14 0-5.7 2.54-5.7 5.68 0 3.14 2.56 5.7 5.7 5.7s5.7-2.55 5.7-5.7c0-3.15-2.56-5.69-5.7-5.69v.01zM7 .98c3.86 0 7 3.14 7 7s-3.14 7-7 7-7-3.12-7-7 3.14-7 7-7z"></path></svg></span>note</div><div class="admonitionContent_S0QG"><p>See doc: <a href="https://doris.apache.org/docs/sql-manual/sql-reference/Data-Types/VARIANT/" target="_blank" rel="noopener noreferrer">https://doris.apache.org/docs/sql-manual/sql-reference/Data-Types/VARIANT/</a></p></div></div><h3 class="anchor anchorWithStickyNavbar_LWe7" id="ip-types">IP types<a href="#ip-types" class="hash-link" aria-label="Direct link to IP types" title="Direct link to IP types"></a></h3><p>IP address is a widely used field in statistical analysis for network traffic monitoring. Doris V2.1 provides native support for IPv4 and IPv6. It stores IP data in binary format, which cuts down storage space usage by 60% compared to IP string in plain texts. Along with these IP types, we have added over 20 functions for IP data processing, including:</p><ul><li><p>IPV4_NUM_TO_STRING: It converts a big-endian representation of an IPv4 address of Int16, Int32, or Int64 into its corresponding string representation;</p></li><li><p>IPV4_CIDR_TO_RANGE: It receives an IPv4 address and a CIDR-containing Int16 value, and returns a structure containing two IPv4 fields, representing the lower range (min) and upper range (max) of the subnet, respectively;</p></li><li><p>INET_ATON: It retrieves a string containing an IPv4 address in the format of A.B.C.D, where A, B, C, and D are decimal numbers separated by periods.</p></li></ul><div class="theme-admonition theme-admonition-note alert alert--secondary admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 14 16"><path fill-rule="evenodd" d="M6.3 5.69a.942.942 0 0 1-.28-.7c0-.28.09-.52.28-.7.19-.18.42-.28.7-.28.28 0 .52.09.7.28.18.19.28.42.28.7 0 .28-.09.52-.28.7a1 1 0 0 1-.7.3c-.28 0-.52-.11-.7-.3zM8 7.99c-.02-.25-.11-.48-.31-.69-.2-.19-.42-.3-.69-.31H6c-.27.02-.48.13-.69.31-.2.2-.3.44-.31.69h1v3c.02.27.11.5.31.69.2.2.42.31.69.31h1c.27 0 .48-.11.69-.31.2-.19.3-.42.31-.69H8V7.98v.01zM7 2.3c-3.14 0-5.7 2.54-5.7 5.68 0 3.14 2.56 5.7 5.7 5.7s5.7-2.55 5.7-5.7c0-3.15-2.56-5.69-5.7-5.69v.01zM7 .98c3.86 0 7 3.14 7 7s-3.14 7-7 7-7-3.12-7-7 3.14-7 7-7z"></path></svg></span>note</div><div class="admonitionContent_S0QG"><p>See doc: <a href="https://doris.apache.org/docs/sql-manual/sql-reference/Data-Types/IPV6/" target="_blank" rel="noopener noreferrer">https://doris.apache.org/docs/sql-manual/sql-reference/Data-Types/IPV6/</a></p></div></div><h3 class="anchor anchorWithStickyNavbar_LWe7" id="more-powerful-functions-for-compound-data-types">More powerful functions for compound data types<a href="#more-powerful-functions-for-compound-data-types" class="hash-link" aria-label="Direct link to More powerful functions for compound data types" title="Direct link to More powerful functions for compound data types"></a></h3><p>V2.1 provides more supported data types:</p><ul><li><code>explode_map</code>: supports exploding rows into columns for the Map data type (only with the new optimizer)</li></ul><p>Each key-value pair in the Map field is expanded into a separate row, with the Map field replaced by two separate fields representing the key and value. The <code>explode_map</code> function should be used in conjunction with Lateral View. You can apply multiple Lateral Views. The result is a Cartesian product.</p><p>This is how it is used:</p><div class="language-SQL codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#F8F8F2;--prism-background-color:#282A36"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-SQL codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#F8F8F2"><span class="token plain">-- Create table</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> CREATE TABLE `sdu` (</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> `id` INT NULL,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> `name` TEXT NULL,</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> `score` MAP<TEXT,INT> NULL</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">) ENGINE=OLAP</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">DUPLICATE KEY(`id`)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">COMMENT 'OLAP'</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">DISTRIBUTED BY HASH(`id`) BUCKETS 1</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">PROPERTIES (</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">"replication_allocation" = "tag.location.default: 1"</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">);</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">-- Insert data</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">insert into sdu values (0, "zhangsan", {"Chinese":"80","Math":"60","English":"90"});</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">insert into sdu values (1, "lisi", {"null":null});</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">insert into sdu values (2, "wangwu", {"Chinese":"88","Math":"90","English":"96"});</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">insert into sdu values (3, "lisi2", {null:null});</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">insert into sdu values (4, "amory", NULL);</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">mysql> select name, course_0, score_0 from sdu lateral view explode_map(score) tmp as course_0,score_0;</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+----------+----------+---------+</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| name | course_0 | score_0 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+----------+----------+---------+</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| zhangsan | Chinese | 80 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| zhangsan | Math | 60 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| zhangsan | English | 90 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| lisi | null | NULL |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| wangwu | Chinese | 88 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| wangwu | Math | 90 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| wangwu | English | 96 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| lisi2 | NULL | NULL |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+----------+----------+---------+</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">mysql> select name, course_0, score_0, course_1, score_1 from sdu lateral view explode_map(score) tmp as course_0,score_0 lateral view explode_map(score) tmp1 as course_1,score_1;</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+----------+----------+---------+----------+---------+</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| name | course_0 | score_0 | course_1 | score_1 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+----------+----------+---------+----------+---------+</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| zhangsan | Chinese | 80 | Chinese | 80 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| zhangsan | Chinese | 80 | Math | 60 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| zhangsan | Chinese | 80 | English | 90 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| zhangsan | Math | 60 | Chinese | 80 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| zhangsan | Math | 60 | Math | 60 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| zhangsan | Math | 60 | English | 90 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| zhangsan | English | 90 | Chinese | 80 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| zhangsan | English | 90 | Math | 60 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| zhangsan | English | 90 | English | 90 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| lisi | null | NULL | null | NULL |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| wangwu | Chinese | 88 | Chinese | 88 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| wangwu | Chinese | 88 | Math | 90 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| wangwu | Chinese | 88 | English | 96 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| wangwu | Math | 90 | Chinese | 88 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| wangwu | Math | 90 | Math | 90 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| wangwu | Math | 90 | English | 96 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| wangwu | English | 96 | Chinese | 88 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| wangwu | English | 96 | Math | 90 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| wangwu | English | 96 | English | 96 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| lisi2 | NULL | NULL | NULL | NULL |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+----------+----------+---------+----------+---------+</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p><code>explode_map_outer</code> and <code>explode_outer</code> serve the same purpose. They display rows with NULL values in the Map-type columns.</p><div class="language-SQL codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#F8F8F2;--prism-background-color:#282A36"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-SQL codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#F8F8F2"><span class="token plain">mysql> select name, course_0, score_0 from sdu lateral view explode_map_outer(score) tmp as course_0,score_0;</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+----------+----------+---------+</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| name | course_0 | score_0 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+----------+----------+---------+</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| zhangsan | Chinese | 80 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| zhangsan | Math | 60 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| zhangsan | English | 90 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| lisi | null | NULL |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| wangwu | Chinese | 88 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| wangwu | Math | 90 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| wangwu | English | 96 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| lisi2 | NULL | NULL |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| amory | NULL | NULL |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+----------+----------+---------+</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><ul><li><code>IN</code>: supports the <code>STRUCT</code> data type (only with the new optimizer)</li></ul><p>The <code>IN</code> predicate supports Struct type data constructed using the <code>struct()</code>function as the left parameter. It also allows selecting a column that contains Struct type data from a table. It supports a Struct-type array constructed using the <code>struct()</code> function as the right parameter.</p><p>It is an efficient alternative to WHERE clauses with many OR conditions. For example, instead of using expressions like <code>(a = 1 and b = '2') or (a = 1 and b = '3') or (...)</code>, you can use <code>struct(a,b) in (struct(1, '2'), struct(1, '3'), ...)</code>.</p><div class="language-SQL codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#F8F8F2;--prism-background-color:#282A36"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-SQL codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#F8F8F2"><span class="token plain">mysql> select struct(1,"2") in (struct(1,3), struct(1,"2"), struct(1,1), null);</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| cast(struct(1, '2') as STRUCT<col1:TINYINT,col2:TEXT>) IN (NULL, cast(struct(1, '2') as STRUCT<col1:TINYINT,col2:TEXT>), cast(struct(1, 1) as STRUCT<col1:TINYINT,col2:TEXT>), cast(struct(1, 3) as STRUCT<col1:TINYINT,col2:TEXT>)) |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| 1 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">mysql> select struct(1,"2") not in (struct(1,3), struct(1,"2"), struct(1,1), null);</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| ( not cast(struct(1, '2') as STRUCT<col1:TINYINT,col2:TEXT>) IN (NULL, cast(struct(1, '2') as STRUCT<col1:TINYINT,col2:TEXT>), cast(struct(1, 1) as STRUCT<col1:TINYINT,col2:TEXT>), cast(struct(1, 3) as STRUCT<col1:TINYINT,col2:TEXT>))) |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| 0 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><ul><li><code>MAP_AGG</code>: It receives expr1 as the key, expr2 as the corresponding value, and returns a MAP.</li></ul><div class="theme-admonition theme-admonition-note alert alert--secondary admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 14 16"><path fill-rule="evenodd" d="M6.3 5.69a.942.942 0 0 1-.28-.7c0-.28.09-.52.28-.7.19-.18.42-.28.7-.28.28 0 .52.09.7.28.18.19.28.42.28.7 0 .28-.09.52-.28.7a1 1 0 0 1-.7.3c-.28 0-.52-.11-.7-.3zM8 7.99c-.02-.25-.11-.48-.31-.69-.2-.19-.42-.3-.69-.31H6c-.27.02-.48.13-.69.31-.2.2-.3.44-.31.69h1v3c.02.27.11.5.31.69.2.2.42.31.69.31h1c.27 0 .48-.11.69-.31.2-.19.3-.42.31-.69H8V7.98v.01zM7 2.3c-3.14 0-5.7 2.54-5.7 5.68 0 3.14 2.56 5.7 5.7 5.7s5.7-2.55 5.7-5.7c0-3.15-2.56-5.69-5.7-5.69v.01zM7 .98c3.86 0 7 3.14 7 7s-3.14 7-7 7-7-3.12-7-7 3.14-7 7-7z"></path></svg></span>note</div><div class="admonitionContent_S0QG"><p>See doc: <a href="https://doris.apache.org/docs/sql-manual/sql-functions/aggregate-functions/map-agg/" target="_blank" rel="noopener noreferrer">https://doris.apache.org/docs/sql-manual/sql-functions/aggregate-functions/map-agg/</a></p></div></div><h2 class="anchor anchorWithStickyNavbar_LWe7" id="workload-management">Workload management<a href="#workload-management" class="hash-link" aria-label="Direct link to Workload management" title="Direct link to Workload management"></a></h2><h3 class="anchor anchorWithStickyNavbar_LWe7" id="hard-isolation-of-resources">Hard isolation of resources<a href="#hard-isolation-of-resources" class="hash-link" aria-label="Direct link to Hard isolation of resources" title="Direct link to Hard isolation of resources"></a></h3><p>On the basis of the Workload Group mechanism, which imposes a soft limit on the resources that a workload group can use, Doris 2.1 introduces a hard limit on CPU resource consumption for workload groups as a way to ensure <strong>higher stability in query performance</strong>. This means that regardless of the overall CPU availability on the physical machine, workload groups configured with hard limits cannot exceed the maximum CPU usage specified in the configuration. In this way, as long as there is no significant change in user query workload, there will be stable query performance. A caveat is that, in addition to CPU usage, memory, I/O, and resource contention at the software level will all impact query execution. Thus, when the cluster switches between idle and heavy load, even with CPU hard limits configured, there might still be fluctuations in query performance. However, you can still expect better performance from the hard limits than the soft limits.</p><div class="theme-admonition theme-admonition-tip alert alert--success admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 12 16"><path fill-rule="evenodd" d="M6.5 0C3.48 0 1 2.19 1 5c0 .92.55 2.25 1 3 1.34 2.25 1.78 2.78 2 4v1h5v-1c.22-1.22.66-1.75 2-4 .45-.75 1-2.08 1-3 0-2.81-2.48-5-5.5-5zm3.64 7.48c-.25.44-.47.8-.67 1.11-.86 1.41-1.25 2.06-1.45 3.23-.02.05-.02.11-.02.17H5c0-.06 0-.13-.02-.17-.2-1.17-.59-1.83-1.45-3.23-.2-.31-.42-.67-.67-1.11C2.44 6.78 2 5.65 2 5c0-2.2 2.02-4 4.5-4 1.22 0 2.36.42 3.22 1.19C10.55 2.94 11 3.94 11 5c0 .66-.44 1.78-.86 2.48zM4 14h5c-.23 1.14-1.3 2-2.5 2s-2.27-.86-2.5-2z"></path></svg></span>tip</div><div class="admonitionContent_S0QG"><p><strong>Note</strong></p><ol><li><p>In Doris V2.0, CPU resource isolation was implemented based on a priority queue. In V2.1, this relies on the CGroup mechanism. Therefore, please note that you should configure the CGroups in advance after upgrading from V2.0 to V2.1. </p></li><li><p>Currently, the Workload Group mechanism supports query-query workload isolation and ingestion-query isolation. Note that if you need to impose hard limits on import workloads, you should enable <code>memtable_on_sink_node</code>.</p></li><li><p>Users need to specify either soft limits or hard limits for the current cluster using a switch. Currently, it is not supported to run both modes on the same cluster. In the future, we will consider bringing in simultaneous support for both modes based on user feedback.</p></li></ol></div></div><div class="theme-admonition theme-admonition-note alert alert--secondary admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 14 16"><path fill-rule="evenodd" d="M6.3 5.69a.942.942 0 0 1-.28-.7c0-.28.09-.52.28-.7.19-.18.42-.28.7-.28.28 0 .52.09.7.28.18.19.28.42.28.7 0 .28-.09.52-.28.7a1 1 0 0 1-.7.3c-.28 0-.52-.11-.7-.3zM8 7.99c-.02-.25-.11-.48-.31-.69-.2-.19-.42-.3-.69-.31H6c-.27.02-.48.13-.69.31-.2.2-.3.44-.31.69h1v3c.02.27.11.5.31.69.2.2.42.31.69.31h1c.27 0 .48-.11.69-.31.2-.19.3-.42.31-.69H8V7.98v.01zM7 2.3c-3.14 0-5.7 2.54-5.7 5.68 0 3.14 2.56 5.7 5.7 5.7s5.7-2.55 5.7-5.7c0-3.15-2.56-5.69-5.7-5.69v.01zM7 .98c3.86 0 7 3.14 7 7s-3.14 7-7 7-7-3.12-7-7 3.14-7 7-7z"></path></svg></span>note</div><div class="admonitionContent_S0QG"><p>See doc: <a href="https://doris.apache.org/docs/admin-manual/workload-group/" target="_blank" rel="noopener noreferrer">https://doris.apache.org/docs/admin-manual/workload-group/</a></p></div></div><h3 class="anchor anchorWithStickyNavbar_LWe7" id="topsql">TopSQL<a href="#topsql" class="hash-link" aria-label="Direct link to TopSQL" title="Direct link to TopSQL"></a></h3><p>V2.1 allows users to check the most resource-consuming SQL queries in the runtime. This can be a big help when handling cluster load spikes caused by unexpected large queries.</p><div class="language-SQL codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#F8F8F2;--prism-background-color:#282A36"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-SQL codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#F8F8F2"><span class="token plain">mysql [(none)]>desc function active_queries();</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+------------------------+--------+------+-------+---------+-------+</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| Field | Type | Null | Key | Default | Extra |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+------------------------+--------+------+-------+---------+-------+</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| BeHost | TEXT | No | false | NULL | NONE |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| BePort | BIGINT | No | false | NULL | NONE |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| QueryId | TEXT | No | false | NULL | NONE |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| StartTime | TEXT | No | false | NULL | NONE |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| QueryTimeMs | BIGINT | No | false | NULL | NONE |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| WorkloadGroupId | BIGINT | No | false | NULL | NONE |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| QueryCpuTimeMs | BIGINT | No | false | NULL | NONE |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| ScanRows | BIGINT | No | false | NULL | NONE |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| ScanBytes | BIGINT | No | false | NULL | NONE |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| BePeakMemoryBytes | BIGINT | No | false | NULL | NONE |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| CurrentUsedMemoryBytes | BIGINT | No | false | NULL | NONE |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| ShuffleSendBytes | BIGINT | No | false | NULL | NONE |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| ShuffleSendRows | BIGINT | No | false | NULL | NONE |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| Database | TEXT | No | false | NULL | NONE |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| FrontendInstance | TEXT | No | false | NULL | NONE |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| Sql | TEXT | No | false | NULL | NONE |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+------------------------+--------+------+-------+---------+-------+</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>The <code>active_queries()</code> function records the audit information of queries running on various Backends in Doris. You can query <code>active_queries()</code> like querying a regular table. It supports operations including querying, filtering with predicates, sorting, and joining. Common metrics captured by this function include the SQL execution time, CPU time, peak memory usage on a single Backend, data volume scanned, and data volume shuffled during the query execution. It also allows rolling up to the Backend level to examine the global resource consumption of SQL queries.</p><p>Note that only the SQL in the runtime will be displayed. The SQLs that finish execution will be written into the audit logs (fe.audit.log, mostly) instead. A few frequently used SQLs are as follows: </p><div class="language-SQL codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#F8F8F2;--prism-background-color:#282A36"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-SQL codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#F8F8F2"><span class="token plain">Check the top N longest-running SQLs in the cluster</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">select QueryId,max(QueryTimeMs) as query_time from active_queries() group by QueryId order by query_time desc limit 10;</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">Check the top N most CPU-consuming SQLs in the cluster</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">select QueryId, sum(QueryCpuTimeMs) as cpu_time from active_queries() group by QueryId order by cpu_time desc limit 10</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">Check the top N SQLs with the most scan rows and their execution time</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">select t1.QueryId,t1.scan_rows, t2.query_time from </span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> (select QueryId, sum(ScanRows) as scan_rows from active_queries() group by QueryId order by scan_rows desc limit 10) t1 </span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> left join (select QueryId,max(QueryTimeMs) as query_time from active_queries() group by QueryId) t2 on t1.QueryId = t2.QueryId</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> </span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">Check the load of all Backends and sort them in descending order based on CPU time/scan rows/shuffle bytes.</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">select BeHost,sum(QueryCpuTimeMs) as query_cpu_time, sum(ScanRows) as scan_rows,sum(ShuffleSendBytes) as shuffle_bytes from active_queries() group by BeHost order by query_cpu_time desc,scan_rows desc ,shuffle_bytes desc limit 10</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">Check the top N SQL queries with the highest peak memory usage on a single Backend.</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">select QueryId,max(BePeakMemoryBytes) as be_peak_mem from active_queries() group by QueryId order by be_peak_mem desc limit 10;</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>Currently, the main displayed workload types include <code>Select</code> and <code>Insert Into...Select</code>. The iterative versions of V2.1 are expected to support displaying the resource usage of Stream Load and Broker Load.</p><div class="theme-admonition theme-admonition-note alert alert--secondary admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 14 16"><path fill-rule="evenodd" d="M6.3 5.69a.942.942 0 0 1-.28-.7c0-.28.09-.52.28-.7.19-.18.42-.28.7-.28.28 0 .52.09.7.28.18.19.28.42.28.7 0 .28-.09.52-.28.7a1 1 0 0 1-.7.3c-.28 0-.52-.11-.7-.3zM8 7.99c-.02-.25-.11-.48-.31-.69-.2-.19-.42-.3-.69-.31H6c-.27.02-.48.13-.69.31-.2.2-.3.44-.31.69h1v3c.02.27.11.5.31.69.2.2.42.31.69.31h1c.27 0 .48-.11.69-.31.2-.19.3-.42.31-.69H8V7.98v.01zM7 2.3c-3.14 0-5.7 2.54-5.7 5.68 0 3.14 2.56 5.7 5.7 5.7s5.7-2.55 5.7-5.7c0-3.15-2.56-5.69-5.7-5.69v.01zM7 .98c3.86 0 7 3.14 7 7s-3.14 7-7 7-7-3.12-7-7 3.14-7 7-7z"></path></svg></span>note</div><div class="admonitionContent_S0QG"><p>See doc: <a href="https://doris.apache.org/docs/sql-manual/sql-functions/table-functions/active_queries/" target="_blank" rel="noopener noreferrer">https://doris.apache.org/docs/sql-manual/sql-functions/table-functions/active_queries/</a></p></div></div><h2 class="anchor anchorWithStickyNavbar_LWe7" id="others">Others<a href="#others" class="hash-link" aria-label="Direct link to Others" title="Direct link to Others"></a></h2><h3 class="anchor anchorWithStickyNavbar_LWe7" id="decimal-256">Decimal 256<a href="#decimal-256" class="hash-link" aria-label="Direct link to Decimal 256" title="Direct link to Decimal 256"></a></h3><p>For users in the financial sector or high-end manufacturing, V2.1 supports a high-precision data type: Decimal, which supports up to 76 significant digits (To enable this experimental feature, please set <code>enable_decimal256=true</code>.)</p><p>Example:</p><div class="language-SQL codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#F8F8F2;--prism-background-color:#282A36"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-SQL codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#F8F8F2"><span class="token plain">CREATE TABLE `test_arithmetic_expressions_256` (</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> k1 decimal(76, 30),</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> k2 decimal(76, 30)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> )</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> DISTRIBUTED BY HASH(k1)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> PROPERTIES (</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> "replication_num" = "1"</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> );</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain" style="display:inline-block"></span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">insert into test_arithmetic_expressions_256 values</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> (1.000000000000000000000000000001, 9999999999999999999999999999999999999999999998.999999999999999999999999999998),</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> (2.100000000000000000000000000001, 4999999999999999999999999999999999999999999999.899999999999999999999999999998),</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> (3.666666666666666666666666666666, 3333333333333333333333333333333333333333333333.333333333333333333333333333333);</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><p>Query and result:</p><div class="language-SQL codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#F8F8F2;--prism-background-color:#282A36"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-SQL codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#F8F8F2"><span class="token plain">select k1, k2, k1 + k2 a from test_arithmetic_expressions_256 order by 1, 2;</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+----------------------------------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| k1 | k2 | a |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+----------------------------------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| 1.000000000000000000000000000001 | 9999999999999999999999999999999999999999999998.999999999999999999999999999998 | 9999999999999999999999999999999999999999999999.999999999999999999999999999999 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| 2.100000000000000000000000000001 | 4999999999999999999999999999999999999999999999.899999999999999999999999999998 | 5000000000000000000000000000000000000000000001.999999999999999999999999999999 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">| 3.666666666666666666666666666666 | 3333333333333333333333333333333333333333333333.333333333333333333333333333333 | 3333333333333333333333333333333333333333333336.999999999999999999999999999999 |</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">+----------------------------------+-------------------------------------------------------------------------------+-------------------------------------------------------------------------------+</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">3 rows in set (0.09 sec)</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><div class="theme-admonition theme-admonition-note alert alert--secondary admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 14 16"><path fill-rule="evenodd" d="M6.3 5.69a.942.942 0 0 1-.28-.7c0-.28.09-.52.28-.7.19-.18.42-.28.7-.28.28 0 .52.09.7.28.18.19.28.42.28.7 0 .28-.09.52-.28.7a1 1 0 0 1-.7.3c-.28 0-.52-.11-.7-.3zM8 7.99c-.02-.25-.11-.48-.31-.69-.2-.19-.42-.3-.69-.31H6c-.27.02-.48.13-.69.31-.2.2-.3.44-.31.69h1v3c.02.27.11.5.31.69.2.2.42.31.69.31h1c.27 0 .48-.11.69-.31.2-.19.3-.42.31-.69H8V7.98v.01zM7 2.3c-3.14 0-5.7 2.54-5.7 5.68 0 3.14 2.56 5.7 5.7 5.7s5.7-2.55 5.7-5.7c0-3.15-2.56-5.69-5.7-5.69v.01zM7 .98c3.86 0 7 3.14 7 7s-3.14 7-7 7-7-3.12-7-7 3.14-7 7-7z"></path></svg></span>note</div><div class="admonitionContent_S0QG"><p>The Decimal256 type consumes more CPU resources so the queries might not be as fast compared to other data types.</p></div></div><h3 class="anchor anchorWithStickyNavbar_LWe7" id="job-scheduler">Job scheduler<a href="#job-scheduler" class="hash-link" aria-label="Direct link to Job scheduler" title="Direct link to Job scheduler"></a></h3><p>According to user feedback, there is a recurring need for scheduled job execution, such as:</p><ul><li><p>Periodic backup;</p></li><li><p>Scheduled data expiration;</p></li><li><p>Periodic import jobs: scheduling incremental or full data synchronization jobs using the Catalog feature;</p></li><li><p>Regular ETL: such as loading data from a flat table into a specified table on a scheduled basis, pulling data from detailed tables and storing it in aggregate tables at specific intervals, and performing scheduled denormalization for tables in the ODS layer and updates to the existing flat table.</p></li></ul><p>Despite the availability of various external scheduling systems such as Airflow and DolphinScheduler, there still exists a consistency challenge. When an external scheduling system triggers an import job in Doris and successfully executes it, but unexpectedly experiences a crash. In this case, since the external scheduling system fails to retrieve the execution result, it assumes the schedule has failed. Then it will trigger its fault tolerance mechanism. Either retries or a direct failure will result in:</p><ul><li><p><strong>Waste of resources</strong>: Since the scheduling system can mistakenly consider a job as failed, it might reschedule the execution of a job that has already succeeded, resulting in unnecessary resource consumption.</p></li><li><p><strong>Data duplication or loss</strong>: On the one hand, retrying the import job might lead to duplicate data imports, resulting in data redundancy or inconsistency. On the other hand, if the job is marked as failed, it can result in the neglect or loss of data that has actually been successfully imported.</p></li><li><p><strong>Time delay</strong>: After the fault tolerance mechanism is triggered, extra time is needed for job scheduling and retries, prolonging the overall data processing time.</p></li><li><p><strong>Compromised system stability</strong>: Frequent retries or immediate failures can increase the load on both the scheduling system and Doris, thereby undermining the stability and performance of the system.</p></li></ul><p>V2.1 provides a good option for regular job scheduling: Doris Job Scheduler. It can trigger the pre-defined operations on schedule or at fixed intervals. The Doris Job Scheduler is accurate to the second. In addition to consistency guarantee for data writing, it provides:</p><ol><li><p><strong>Efficiency</strong>: The Doris Job Scheduler can schedule jobs and events at specified time intervals to ensure efficient data processing. By employing the time wheel algorithm, it guarantees precise triggering of events at a granularity of seconds.</p></li><li><p><strong>Flexibility</strong>: It offers multiple scheduling options, such as scheduling at intervals of minutes, hours, days, or weeks. It supports both one-time scheduling and recurring (cyclic) event scheduling. For the latter, you can specify the start and end times for the scheduling period.</p></li><li><p><strong>Event pool and high-performance processing queues</strong>: It utilizes Disruptor for a high-performance producer-consumer model to minimize job overload.</p></li><li><p><strong>Traceable scheduling records</strong>: It stores the latest job execution records (configurable), which users can view via a simple command.</p></li><li><p><strong>High availability</strong>: On the basis of the Doris high availability mechanism, the jobs are easily self-recoverable.</p></li></ol><p>An example of creating a scheduled job:</p><div class="language-SQL codeBlockContainer_Ckt0 theme-code-block" style="--prism-color:#F8F8F2;--prism-background-color:#282A36"><div class="codeBlockContent_biex"><pre tabindex="0" class="prism-code language-SQL codeBlock_bY9V thin-scrollbar"><code class="codeBlockLines_e6Vv"><span class="token-line" style="color:#F8F8F2"><span class="token plain">// Execute an insert statement every day from 2023-11-17 to 2038</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">CREATE</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain">JOB e_daily</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> ON SCHEDULE</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> EVERY 1 DAY </span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> STARTS '2023-11-17 23:59:00'</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> ENDS '2038-01-19 03:14:07'</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> COMMENT 'Saves total number of sessions'</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> DO</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> INSERT INTO site_activity.totals (time, total)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> SELECT CURRENT_TIMESTAMP, COUNT(*)</span><br></span><span class="token-line" style="color:#F8F8F2"><span class="token plain"> FROM site_activity.sessions where create_time >= days_add(now(),-1) ;</span><br></span></code></pre><div class="buttonGroup__atx"><button type="button" aria-label="Copy code to clipboard" title="Copy" class="clean-btn"><span class="copyButtonIcons_eSgA" aria-hidden="true"><svg viewBox="0 0 24 24" class="copyButtonIcon_y97N"><path fill="currentColor" d="M19,21H8V7H19M19,5H8A2,2 0 0,0 6,7V21A2,2 0 0,0 8,23H19A2,2 0 0,0 21,21V7A2,2 0 0,0 19,5M16,1H4A2,2 0 0,0 2,3V17H4V3H16V1Z"></path></svg><svg viewBox="0 0 24 24" class="copyButtonSuccessIcon_LjdS"><path fill="currentColor" d="M21,7L9,19L3.5,13.5L4.91,12.09L9,16.17L19.59,5.59L21,7Z"></path></svg></span></button></div></div></div><div class="theme-admonition theme-admonition-note alert alert--secondary admonition_LlT9"><div class="admonitionHeading_tbUL"><span class="admonitionIcon_kALy"><svg viewBox="0 0 14 16"><path fill-rule="evenodd" d="M6.3 5.69a.942.942 0 0 1-.28-.7c0-.28.09-.52.28-.7.19-.18.42-.28.7-.28.28 0 .52.09.7.28.18.19.28.42.28.7 0 .28-.09.52-.28.7a1 1 0 0 1-.7.3c-.28 0-.52-.11-.7-.3zM8 7.99c-.02-.25-.11-.48-.31-.69-.2-.19-.42-.3-.69-.31H6c-.27.02-.48.13-.69.31-.2.2-.3.44-.31.69h1v3c.02.27.11.5.31.69.2.2.42.31.69.31h1c.27 0 .48-.11.69-.31.2-.19.3-.42.31-.69H8V7.98v.01zM7 2.3c-3.14 0-5.7 2.54-5.7 5.68 0 3.14 2.56 5.7 5.7 5.7s5.7-2.55 5.7-5.7c0-3.15-2.56-5.69-5.7-5.69v.01zM7 .98c3.86 0 7 3.14 7 7s-3.14 7-7 7-7-3.12-7-7 3.14-7 7-7z"></path></svg></span>note</div><div class="admonitionContent_S0QG"><p>Doris Job Scheduler only supports Insert operations on internal tables currently. See doc: <a href="https://doris.apache.org/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-JOB/" target="_blank" rel="noopener noreferrer">https://doris.apache.org/docs/sql-manual/sql-reference/Data-Definition-Statements/Create/CREATE-JOB/</a></p></div></div><h2 class="anchor anchorWithStickyNavbar_LWe7" id="behavior-changed">Behavior changed<a href="#behavior-changed" class="hash-link" aria-label="Direct link to Behavior changed" title="Direct link to Behavior changed"></a></h2><ul><li><p>The Unique Key model enables Merge-on-Write by default, which means <code>enable_unique_key_merge_on_write=true</code> will be included as a default setting when a table is created in the Unique Key model.</p></li><li><p>Since inverted index has proven to be more performant than bitmap index, V2.1 and future versions stop supporting bitmap index. Existing bitmap indexes will remain effective but new creation is not allowed. We will remove bitmap index-related code in the future.</p></li><li><p><code>cpu_resource_limit</code> is no longer supported. It is to put a limit on the number of scanner threads on Doris Backend. Since the Workload Group mechanism also supports such settings, the already configured <code>cpu_resource_limit</code> will be invalid.</p></li><li><p>Segment compaction is enabled by default. This means Doris supports compaction of multiple segments in the same rowset, which is useful in single-batch ingestion of large datasets.</p></li><li><p>Audit log plug-in</p><ul><li><p>Since V2.1.0, Doris has a built-in audit log plug-in. Users can simply enable or disable it by setting the <code>enable_audit_plugin</code> parameter. </p></li><li><p>If you have already installed your own audit log plug-in, you can either continue using it after upgrading to Doris V2.1, or uninstall it and use the one in Doris. Please note that the audit log table will be relocated after switching plug-in.</p></li><li><p>For more details, please see doc: <a href="https://doris.apache.org/docs/ecosystem/audit-plugin/" target="_blank" rel="noopener noreferrer">https://doris.apache.org/docs/ecosystem/audit-plugin/</a></p></li></ul></li></ul><h2 class="anchor anchorWithStickyNavbar_LWe7" id="credits">Credits<a href="#credits" class="hash-link" aria-label="Direct link to Credits" title="Direct link to Credits"></a></h2><p>Special thanks to the following contributors for making this happen: </p><p>morrySnow, Gabriel39, BiteTheDDDDt, kaijchen, starocean999, morningman, jackwener, zy-kkk, englefly, Jibing-Li, XieJiann, yujun777, Mryange, HHoflittlefish777, LiDongyangLi, HappenLee, zhangstar333, lihangyu, zclllyybb, amory, bobhan1, AKIRA, zhangdong, ZouXinyiZou, HuJerryHu, yiguolei, airborne12, wangbo, jacktengg, jacktengg, TangSiyang2001, BePPPower, Yukang-Lian, mymeiyi, liugddx, kaka11chen, AshinGau, DrogonJackDrogon, wsjz, seuhezhiqiang, zhannngchen, shuke987, KassieZ, huanghaibin, zzzxl1993, Nitin-Kashyap, AlexYue, dataroaring, seawinde, walter, xzj7019, xiaokang, SWJTU-ZhangLei, liaoxin01, dutyu, wuwenchihdu, LiBinfeng-01, daidai, qidaye, mch_ucchi, zhangguoqiang, zhengyu, plat1ko, LemonLiTree, ixzc, deardeng, yiguolei, catpineapple, LingAdonisLing, DongLiang-0, whuxingying, Tanya-W, Yulei-Yang, zzzzzzzs, caoliang-web, xueweizhang, yangshijie, Luwei, lsy3993, xy720, HowardQin, DeadlineFen, Petrichor, caiconghui, KirsCalvinKirs, SunChenyangSun, ChouGavinChou, Luzhijing, gnehil, wudi, zhiqqqq, zfr95, zxealous, kkop, yagagagaga, Chester, LuGuangmingLu, Lightman, Xiaocc, taoxutao, yuanyuan8983, KirsCalvinKirs, DuRipeng, GoGoWen, JingDas, camby, camby, Euporia, rohitrs1983, felixwluo, wudongliang, FreeOnePlus, PaiVallishPai, XuJianxu, seuhezhiqiang, luozenglin, 924060929, HB, LiuLijiaLiu, Ma1oneZhang, bingquanzhao, chunping, echo-dundun, feiniaofeiafei, walter, yongjinhou, zgxme, zhangy5, httpshirley, ChenyangSunChenyang, ZenoYang, ZhangYu0123, hechao, herry2038, jayhua, koarz, nanfeng, LiChuangLi, LiuGuangdongLiu, Jeffrey, liuJiwenliu, Stalary, DuanXujianDuan, HuZhiyuHu, jiafeng.zhang, nanfeng, py023, xiongjx, yuxuan-luo, zhaoshuo, XiaoChangmingXiao, ElvinWei, LiuHongLiu, QiHouliangQi, Hyman-zhao, HelgeLarsHelge, Uniqueyou, YangYAN, acnot, amory, feifeifeimoon, flynn, gohalo, htyoung, realize096, shee, wangqt, xyfsjq, zzwwhh, songguangfan, 467887319, BirdAmosBird, ZhuArmandoZhu, CanGuan, ChengDaqi2023, ChinaYiGuan, gitccl, colagy, DeadlineFen, Doris-Extras, HonestManXin, q763562998, guardcrystal, Dragonliu2018, ZhaoLongZhao, LuoMetaLuo, Miaohongkai, YinShaowenYin, Centurybbx, hongkun-Shao, Wanghuan, Xinxing, XueYuhai, Yoko, HeZhangJianHe, ZhongJinHacker, alan_rodriguez, allenhooo, beat4ocean, bigben0204, chen, czzmmc, dalong, deadlinefen, didiaode18, dong-shuai, feelshana, fornaix, hammer, xuke-hat, hqx871, i78086, irenesrl, julic20s, kindred77, lihuigang, wenluowen, lxliyou001, CSTGluigi, ranxiang327, shysnow, sunny, vhwzIs, wangtao, wangtianyi2004, wyx123654, xuefengze, xiangran0327, xy, yimeng, ytwp, yujian, zhangstar333, figurant, sdhzwc, LHG41278, zlw5307</p></div></article><div class="pl-4 mt-20 text-[#1D1D1D]"><div class="text-[2rem] leading-[3.25rem]">Recent posts</div><div class="mt-4 flex flex-col"><a href="https://doris.apache.org/blog/evolution-of-the-apache-doris-execution-engine" target="_blank" rel="noopener noreferrer" class="text-lg leading-10 hover:no-underline hover:text-[#444FD9]">Steps to industry-leading query speed: evolution of the Apache Doris execution engine</a><a href="https://doris.apache.org/blog/job-scheduler-for-task-automation" target="_blank" rel="noopener noreferrer" class="text-lg leading-10 hover:no-underline hover:text-[#444FD9]">Another lifesaver for data engineers: Apache Doris Job Scheduler for task automation</a><a href="https://doris.apache.org/blog/release-note-2.0.11" target="_blank" rel="noopener noreferrer" class="text-lg leading-10 hover:no-underline hover:text-[#444FD9]">Apache Doris version 2.0.11 has been released</a><a href="https://doris.apache.org/blog/apache-doris-for-log-and-time-series-data-analysis-in-netease" target="_blank" rel="noopener noreferrer" class="text-lg leading-10 hover:no-underline hover:text-[#444FD9]">Apache Doris for log and time series data analysis in NetEase, why not Elasticsearch and InfluxDB?</a></div><a class="flex group text-primary items-center text-base cursor-pointer hover:no-underline mt-4" href="/blog"><span class="mr-2">View all blogs</span><span class="transition-slide"><svg xmlns="http://www.w3.org/2000/svg" class="transition-slide" width="1em" height="1em" viewBox="0 0 16 14" fill="none"><path d="M9.37549 12.3542L14.8755 6.85419L9.37549 1.35419" stroke="currentColor" stroke-width="1.65" stroke-linecap="round" stroke-linejoin="round"></path><path d="M1.12549 6.85419L14.8755 6.85419" stroke="currentColor" stroke-width="1.65" stroke-linecap="round" stroke-linejoin="round"></path></svg></span></a></div></main><div class="col col--2"><div class="tableOfContents_jeP5 thin-scrollbar"><a href="https://github.com/apache/doris/discussions" target="_blank" rel="noopener noreferrer" class="ml-4 mb-8 flex items-center hover:no-underline"><span class="pr-2">Join Discussion</span><svg viewBox="0 0 1024 1024" version="1.1" xmlns="http://www.w3.org/2000/svg" p-id="8500" id="mx_n_1711090272569" width="16" height="16"><path d="M522.24 896.512c-25.6 4.608-51.712 7.168-78.336 7.168-79.36 0-157.696-21.504-225.792-62.464l-18.432-10.752-103.936 28.16c-28.672 7.68-54.784-18.432-47.104-47.104l28.16-103.936c-10.752-17.92-17.408-30.208-20.992-36.864C20.992 607.232 3.072 536.064 3.584 463.36c0-243.2 197.12-440.32 440.32-440.32 221.696 0 405.504 164.352 435.712 377.856 90.112 55.808 144.896 154.112 144.896 260.096 0 51.2-12.8 100.352-36.352 144.384-2.048 4.096-6.144 10.752-11.776 20.48l17.408 64c7.68 28.672-18.432 54.784-47.104 47.104l-64-17.408-7.68 4.608c-47.616 28.672-101.888 43.52-157.184 43.52-71.68-0.512-140.8-25.088-195.584-71.168z m95.232-28.672c31.232 15.36 65.536 23.04 100.352 23.04 41.472 0 82.432-11.264 117.76-32.768 2.56-1.536 9.728-5.632 22.016-12.8 8.704-5.12 19.456-6.656 29.184-3.584l14.848 4.096-4.096-14.848c-2.56-10.24-1.536-20.48 4.096-29.696 6.144-10.24 12.288-20.992 18.432-31.232 17.92-33.28 27.136-70.656 27.136-108.544 0-59.904-23.552-117.76-65.536-160.256-13.312 164.352-118.272 303.616-264.192 366.592z m-462.848-155.648l-14.848 54.784 54.784-14.848c9.728-2.56 20.48-1.536 29.184 4.096 18.432 10.752 29.184 16.896 32.768 19.456 56.32 33.792 120.832 51.712 186.88 51.712 200.704 0 363.52-162.816 363.52-363.52s-162.816-363.52-363.52-363.52-363.52 162.816-363.52 363.52c0 60.928 14.848 119.296 43.008 171.52 3.584 7.168 13.312 23.04 27.648 47.616 5.632 8.704 6.656 19.456 4.096 29.184z m448.512-382.976c20.992 0 38.4 16.896 38.4 38.4 0 20.992-16.896 38.4-38.4 38.4H284.16c-20.992 0-38.4-16.896-38.4-38.4 0-20.992 16.896-38.4 38.4-38.4h318.976z m-153.088 191.488c20.992 0 38.4 16.896 38.4 38.4 0 20.992-16.896 38.4-38.4 38.4H284.16c-20.992 0-38.4-16.896-38.4-38.4 0-20.992 16.896-38.4 38.4-38.4h165.888z m0 0" p-id="8501" fill="currentColor"></path></svg></a><span class="ml-4">On This Page</span><ul class="table-of-contents table-of-contents__left-border"><li><a href="#higher-performance" class="table-of-contents__link toc-highlight">Higher performance</a><ul><li><a href="#smarter-optimizer" class="table-of-contents__link toc-highlight">Smarter optimizer</a></li><li><a href="#better-heuristic-optimization" class="table-of-contents__link toc-highlight">Better heuristic optimization</a></li><li><a href="#parallel-adaptive-scan" class="table-of-contents__link toc-highlight">Parallel Adaptive Scan</a></li><li><a href="#local-shuffle" class="table-of-contents__link toc-highlight">Local Shuffle</a></li></ul></li><li><a href="#increase-performance-on-arm" class="table-of-contents__link toc-highlight">Increase performance on ARM</a></li><li><a href="#improved-data-lake-analytics-capabilities" class="table-of-contents__link toc-highlight">Improved data lake analytics capabilities</a><ul><li><a href="#data-lake-analytic-performance" class="table-of-contents__link toc-highlight">Data lake analytic performance</a></li><li><a href="#sql-dialects-compatibility" class="table-of-contents__link toc-highlight">SQL dialects compatibility</a></li><li><a href="#high-speed-data-interface-for-100-fold-performance" class="table-of-contents__link toc-highlight">High-speed data interface for 100-fold performance</a></li><li><a href="#other-improvements" class="table-of-contents__link toc-highlight">Other improvements</a></li></ul></li><li><a href="#materialized-view-with-multiple-tables" class="table-of-contents__link toc-highlight">Materialized view with multiple tables</a></li><li><a href="#enhanced-storage" class="table-of-contents__link toc-highlight">Enhanced storage</a><ul><li><a href="#auto_increment-column" class="table-of-contents__link toc-highlight">AUTO_INCREMENT column</a></li><li><a href="#auto-partition" class="table-of-contents__link toc-highlight">AUTO PARTITION</a></li><li><a href="#100-faster-insert-into-select" class="table-of-contents__link toc-highlight">100% faster INSERT INTO SELECT</a></li><li><a href="#high-concurrency-real-time-data-ingestion--group-commit" class="table-of-contents__link toc-highlight">High-concurrency real-time data ingestion / Group Commit</a></li></ul></li><li><a href="#semi-structured-data-analysis" class="table-of-contents__link toc-highlight">Semi-structured data analysis</a><ul><li><a href="#a-new-data-type-variant" class="table-of-contents__link toc-highlight">A new data type: Variant</a></li><li><a href="#ip-types" class="table-of-contents__link toc-highlight">IP types</a></li><li><a href="#more-powerful-functions-for-compound-data-types" class="table-of-contents__link toc-highlight">More powerful functions for compound data types</a></li></ul></li><li><a href="#workload-management" class="table-of-contents__link toc-highlight">Workload management</a><ul><li><a href="#hard-isolation-of-resources" class="table-of-contents__link toc-highlight">Hard isolation of resources</a></li><li><a href="#topsql" class="table-of-contents__link toc-highlight">TopSQL</a></li></ul></li><li><a href="#others" class="table-of-contents__link toc-highlight">Others</a><ul><li><a href="#decimal-256" class="table-of-contents__link toc-highlight">Decimal 256</a></li><li><a href="#job-scheduler" class="table-of-contents__link toc-highlight">Job scheduler</a></li></ul></li><li><a href="#behavior-changed" class="table-of-contents__link toc-highlight">Behavior changed</a></li><li><a href="#credits" class="table-of-contents__link toc-highlight">Credits</a></li></ul></div></div></div></div></div></div><div class="footer pt-16 pb-10"><div class="container"><div class="footer-box"><div class="left"><img src="/images/asf_logo_apache.svg" alt="" class="themedImage_ToTc themedImage--light_HNdA footer__logo"><img src="/images/asf_logo_apache.svg" alt="" class="themedImage_ToTc themedImage--dark_i4oU footer__logo"><div class="row footer__links"><div class="col footer__col"><div class="footer__title">ASF</div><ul class="footer__items clean-list"><li class="footer__item"><a href="https://www.apache.org/" target="_blank" rel="noopener noreferrer" class="footer__link-item">Foundation<svg width="13.5" height="13.5" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li class="footer__item"><a href="https://www.apache.org/licenses/" target="_blank" rel="noopener noreferrer" class="footer__link-item">License<svg width="13.5" height="13.5" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li class="footer__item"><a href="https://www.apache.org/events/current-event" target="_blank" rel="noopener noreferrer" class="footer__link-item">Events<svg width="13.5" height="13.5" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li class="footer__item"><a href="https://www.apache.org/foundation/sponsorship.html" target="_blank" rel="noopener noreferrer" class="footer__link-item">Sponsorship<svg width="13.5" height="13.5" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li class="footer__item"><a href="https://privacy.apache.org/policies/privacy-policy-public.html" target="_blank" rel="noopener noreferrer" class="footer__link-item">Privacy<svg width="13.5" height="13.5" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li class="footer__item"><a href="https://www.apache.org/security/" target="_blank" rel="noopener noreferrer" class="footer__link-item">Security<svg width="13.5" height="13.5" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li class="footer__item"><a href="https://www.apache.org/foundation/thanks.html" target="_blank" rel="noopener noreferrer" class="footer__link-item">Thanks<svg width="13.5" height="13.5" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li></ul></div><div class="col footer__col"><div class="footer__title">Resources</div><ul class="footer__items clean-list"><li class="footer__item"><a class="footer__link-item" href="/download">Download</a></li><li class="footer__item"><a class="footer__link-item" href="/docs/get-starting/quick-start">Docs</a></li><li class="footer__item"><a class="footer__link-item" href="/blog">Blog</a></li><li class="footer__item"><a class="footer__link-item" href="/ecosystem/cluster-management">Ecosystem</a></li><li class="footer__item"><a class="footer__link-item" href="/users">Users</a></li><li class="footer__item"><a href="https://github.com/apache/doris/discussions" target="_blank" rel="noopener noreferrer" class="footer__link-item">Discussions<svg width="13.5" height="13.5" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li></ul></div><div class="col footer__col"><div class="footer__title">Community</div><ul class="footer__items clean-list"><li class="footer__item"><a class="footer__link-item" href="/community/how-to-contribute/">How to contribute</a></li><li class="footer__item"><a href="https://github.com/apache/doris/" target="_blank" rel="noopener noreferrer" class="footer__link-item">Source code<svg width="13.5" height="13.5" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li class="footer__item"><a href="https://cwiki.apache.org/confluence/display/DORIS/Doris+Improvement+Proposals" target="_blank" rel="noopener noreferrer" class="footer__link-item">Improvement proposal<svg width="13.5" height="13.5" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li><li class="footer__item"><a class="footer__link-item" href="/community/team">Doris team</a></li><li class="footer__item"><a href="https://github.com/apache/doris/issues/30669" target="_blank" rel="noopener noreferrer" class="footer__link-item">Roadmap<svg width="13.5" height="13.5" aria-hidden="true" viewBox="0 0 24 24" class="iconExternalLink_nPIU"><path fill="currentColor" d="M21 13v10h-21v-19h12v2h-10v15h17v-8h2zm3-12h-10.988l4.035 4-6.977 7.07 2.828 2.828 6.977-7.07 4.125 4.172v-11z"></path></svg></a></li></ul></div></div></div><div class="right"><div class="footer__title">Join the community</div><div class="social-list"><div class="social"><a href="mailto:dev@doris.apache.org" target="_blank" title="mail" class="item"><svg xmlns="http://www.w3.org/2000/svg" width="2em" height="2em" viewBox="0 0 32 32" fill="none"><path d="M5.6003 6H26.3997C27.8186 6 28.982 7.10964 29 8.46946L16.0045 15.454L3.01202 8.47829C3.02405 7.11258 4.1784 6 5.6003 6ZM3.01202 11.1508L3 23.5011C3 24.8756 4.16938 26 5.6003 26H26.3997C27.8306 26 29 24.8756 29 23.5011V11.145L16.3111 17.8028C16.1157 17.9058 15.8813 17.9058 15.6889 17.8028L3.01202 11.1508Z" fill="currentColor"></path></svg></a><a href="https://github.com/apache/doris" target="_blank" title="github" class="item"><svg width="2em" height="2em" viewBox="0 0 32 32" fill="none" xmlns="http://www.w3.org/2000/svg"><path d="M16.0001 2.66675C8.63342 2.66675 2.66675 8.63341 2.66675 16.0001C2.66524 18.7991 3.54517 21.5276 5.1817 23.7983C6.81824 26.0691 9.12828 27.7668 11.7841 28.6508C12.4508 28.7668 12.7001 28.3668 12.7001 28.0161C12.7001 27.7001 12.6828 26.6508 12.6828 25.5334C9.33342 26.1508 8.46675 24.7174 8.20008 23.9668C8.04942 23.5828 7.40008 22.4001 6.83342 22.0828C6.36675 21.8334 5.70008 21.2161 6.81608 21.2001C7.86675 21.1828 8.61608 22.1668 8.86675 22.5668C10.0668 24.5828 11.9841 24.0161 12.7494 23.6668C12.8668 22.8001 13.2161 22.2174 13.6001 21.8841C10.6334 21.5508 7.53342 20.4001 7.53342 15.3001C7.53342 13.8494 8.04942 12.6507 8.90008 11.7161C8.76675 11.3827 8.30008 10.0161 9.03342 8.18275C9.03342 8.18275 10.1494 7.83342 12.7001 9.55075C13.7855 9.2495 14.907 9.09787 16.0334 9.10008C17.1668 9.10008 18.3001 9.24942 19.3668 9.54942C21.9161 7.81608 23.0334 8.18408 23.0334 8.18408C23.7668 10.0174 23.3001 11.3841 23.1668 11.7174C24.0161 12.6507 24.5334 13.8334 24.5334 15.3001C24.5334 20.4174 21.4174 21.5508 18.4508 21.8841C18.9334 22.3001 19.3508 23.1001 19.3508 24.3508C19.3508 26.1334 19.3334 27.5668 19.3334 28.0174C19.3334 28.3668 19.5841 28.7828 20.2508 28.6494C22.8975 27.7558 25.1973 26.0547 26.8266 23.7856C28.4559 21.5165 29.3327 18.7936 29.3334 16.0001C29.3334 8.63341 23.3668 2.66675 16.0001 2.66675V2.66675Z" fill="currentColor"></path></svg></a><a href="https://twitter.com/doris_apache" target="_blank" title="twitter" class="item"><svg xmlns="http://www.w3.org/2000/svg" width="2em" height="2em" viewBox="0 0 32 32" fill="none"><path d="M4.625 4.625H11.2809L27.375 27.375H20.7191L4.625 4.625ZM7.52549 6.10639L21.5236 25.8936H24.4746L10.4764 6.10639H7.52549Z" fill="currentColor"></path><path d="M14.4268 18.4803L6.53447 27.375H4.625L13.5581 17.2525L14.4268 18.4803ZM18.1299 14.3066L26.7203 4.625H24.7017L17.2525 13.0662L18.1299 14.3066Z" fill="currentColor"></path></svg></a><a href="https://join.slack.com/t/apachedoriscommunity/shared_invite/zt-2kl08hzc0-SPJe4VWmL_qzrFd2u2XYQA" title="slack" target="_blank" class="item"><svg width="2em" height="2em" viewBox="0 0 32 32" fill="none" xmlns="http://www.w3.org/2000/svg"><g clip-path="url(#clip0_125_278)"><path d="M12.5875 16.6906C11.0844 16.6906 9.86562 17.9094 9.86562 19.4125V26.2375C9.86562 26.9594 10.1524 27.6517 10.6628 28.1622C11.1733 28.6726 11.8656 28.9594 12.5875 28.9594C13.3094 28.9594 14.0017 28.6726 14.5122 28.1622C15.0226 27.6517 15.3094 26.9594 15.3094 26.2375V19.4531C15.3094 17.9094 14.0906 16.6906 12.5875 16.6906ZM3 19.4531C3 20.175 3.28677 20.8673 3.79722 21.3778C4.30767 21.8882 4.99999 22.175 5.72187 22.175C6.44376 22.175 7.13608 21.8882 7.64653 21.3778C8.15698 20.8673 8.44375 20.175 8.44375 19.4531V16.7312H5.7625C4.25938 16.6906 3 17.9094 3 19.4531ZM12.5875 3C11.8656 3 11.1733 3.28677 10.6628 3.79722C10.1524 4.30767 9.86562 4.99999 9.86562 5.72187C9.86562 6.44376 10.1524 7.13608 10.6628 7.64653C11.1733 8.15698 11.8656 8.44375 12.5875 8.44375H15.3094V5.72187C15.3094 4.21875 14.0906 3 12.5875 3ZM5.72187 15.3094H12.5469C13.2688 15.3094 13.9611 15.0226 14.4715 14.5122C14.982 14.0017 15.2688 13.3094 15.2688 12.5875C15.2688 11.8656 14.982 11.1733 14.4715 10.6628C13.9611 10.1524 13.2688 9.86562 12.5469 9.86562H5.72187C4.99999 9.86562 4.30767 10.1524 3.79722 10.6628C3.28677 11.1733 3 11.8656 3 12.5875C3 13.3094 3.28677 14.0017 3.79722 14.5122C4.30767 15.0226 4.99999 15.3094 5.72187 15.3094ZM26.2375 9.86562C24.7344 9.86562 23.5156 11.0844 23.5156 12.5875V15.3094H26.2375C26.9594 15.3094 27.6517 15.0226 28.1622 14.5122C28.6726 14.0017 28.9594 13.3094 28.9594 12.5875C28.9594 11.8656 28.6726 11.1733 28.1622 10.6628C27.6517 10.1524 26.9594 9.86562 26.2375 9.86562ZM16.6906 5.72187V12.5875C16.6906 13.3094 16.9774 14.0017 17.4878 14.5122C17.9983 15.0226 18.6906 15.3094 19.4125 15.3094C20.1344 15.3094 20.8267 15.0226 21.3372 14.5122C21.8476 14.0017 22.1344 13.3094 22.1344 12.5875V5.72187C22.1344 4.99999 21.8476 4.30767 21.3372 3.79722C20.8267 3.28677 20.1344 3 19.4125 3C18.6906 3 17.9983 3.28677 17.4878 3.79722C16.9774 4.30767 16.6906 4.99999 16.6906 5.72187ZM22.1344 26.2781C22.1344 24.775 20.9156 23.5562 19.4125 23.5562H16.6906V26.2781C16.6906 27 16.9774 27.6923 17.4878 28.2028C17.9983 28.7132 18.6906 29 19.4125 29C20.1344 29 20.8267 28.7132 21.3372 28.2028C21.8476 27.6923 22.1344 27 22.1344 26.2781ZM26.2781 16.6906H19.4125C18.6906 16.6906 17.9983 16.9774 17.4878 17.4878C16.9774 17.9983 16.6906 18.6906 16.6906 19.4125C16.6906 20.1344 16.9774 20.8267 17.4878 21.3372C17.9983 21.8476 18.6906 22.1344 19.4125 22.1344H26.2375C27.7406 22.1344 28.9594 20.9156 28.9594 19.4125C29 17.9094 27.7812 16.6906 26.2781 16.6906Z" fill="currentColor"></path></g><defs><clipPath id="clip0_125_278"><rect width="26" height="26" fill="currentColor" transform="translate(3 3)"></rect></clipPath></defs></svg></a></div><div class="social"><a href="https://www.youtube.com/@apachedoris/channels" title="youtube" target="_blank" class="item"><svg xmlns="http://www.w3.org/2000/svg" width="2em" height="2em" viewBox="0 0 32 32" fill="none"><path d="M28.5167 7.83429C28.9436 8.25423 29.2532 8.77539 29.4154 9.34742C29.8205 11.5462 30.0159 13.7775 29.999 16.0121C30.0144 18.2382 29.819 20.4609 29.4154 22.6515C29.2532 23.2235 28.9436 23.7446 28.5167 24.1645C28.0898 24.5845 27.5601 24.889 26.9785 25.0486C24.7728 25.625 16.0124 25.625 16.0124 25.625C16.0124 25.625 7.22652 25.625 5.04638 25.0486C4.46489 24.889 3.9351 24.5845 3.5082 24.1645C3.08132 23.7446 2.77176 23.2235 2.60948 22.6515C2.19736 20.4617 1.9934 18.239 2.00025 16.0121C1.9918 13.7767 2.19577 11.5455 2.60948 9.34742C2.77176 8.77539 3.08132 8.25423 3.5082 7.83429C3.9351 7.41436 4.46489 7.10985 5.04638 6.95021C7.25103 6.36354 16.0124 6.37502 16.0124 6.37502C16.0124 6.37502 24.796 6.37502 26.9785 6.95021C27.5601 7.10985 28.0898 7.41436 28.5167 7.83429ZM12.5 21.25L21.25 16.008L12.5 10.75V21.25Z" fill="currentColor"></path></svg></a><a href="https://www.linkedin.com/company/doris-apache/" title="linkedin" target="_blank" class="item"><svg width="2rem" height="2rem" viewBox="0 0 32 32" fill="none" xmlns="http://www.w3.org/2000/svg"><path d="M4.29925 26.9996H9.66738V11.6781H4.29925V26.9996ZM22.1628 11.1949C19.9409 11.1949 18.7157 11.9388 17.3054 13.7407V11.6777H11.9459V26.9996H17.305V18.6738C17.305 16.9168 18.145 15.1982 20.1535 15.1982C22.162 15.1982 22.6559 16.9164 22.6559 18.632V27H28V18.2902C28 12.2386 24.3854 11.1949 22.1628 11.1949ZM6.99325 4C5.3395 4 4 5.21047 4 6.7046C4 8.19759 5.3395 9.40617 6.99325 9.40617C8.6455 9.40617 9.985 8.19722 9.985 6.7046C9.985 5.21047 8.6455 4 6.99325 4Z" fill="white"></path></svg></a><a href="https://medium.com/@ApacheDoris" title="medium" target="_blank" class="item"><svg width="2em" height="2em" viewBox="0 0 32 32" fill="none" xmlns="http://www.w3.org/2000/svg"><g id="Frame"><path id="Vector" d="M17.7967 16.5385C17.8029 18.53 16.9746 20.4425 15.4937 21.8559C14.0128 23.2693 12.0004 24.0681 9.89836 24.0769C7.79633 24.0681 5.78391 23.2693 4.30302 21.8559C2.82212 20.4425 1.99383 18.53 2.00003 16.5385C1.99383 14.5469 2.82212 12.6344 4.30302 11.221C5.78391 9.80759 7.79633 9.00878 9.89836 9C12.0004 9.00878 14.0128 9.80759 15.4937 11.221C16.9746 12.6344 17.8029 14.5469 17.7967 16.5385ZM26.4533 16.5385C26.4533 20.4514 24.6917 23.6348 22.51 23.6348C20.3283 23.6348 18.555 20.4514 18.555 16.5385C18.555 12.6255 20.3283 9.44214 22.51 9.44214C24.6917 9.44214 26.4533 12.6255 26.4533 16.5385ZM30 16.5385C30 20.0424 29.3817 22.8942 28.6117 22.8942C27.8417 22.8942 27.2233 20.0424 27.2233 16.5385C27.2233 13.0345 27.8417 10.1827 28.6117 10.1827C29.3817 10.1827 30 13.0345 30 16.5385Z" fill="currentColor"></path></g></svg></a><a class="item wechat"><svg width="2em" height="2em" viewBox="0 0 32 32" fill="none" xmlns="http://www.w3.org/2000/svg"><path d="M20.7578 11.5169C21.0708 11.5169 21.3795 11.5398 21.6851 11.573C20.8524 7.73517 16.7052 4.88306 11.9718 4.88306C6.67951 4.88306 2.34412 8.45283 2.34412 12.9854C2.34412 15.6013 3.78679 17.7498 6.19667 19.4161L5.2339 22.2827L8.59917 20.6122C9.80411 20.8478 10.7698 21.0906 11.9718 21.0906C12.2738 21.0906 12.5728 21.0759 12.8703 21.0523C12.682 20.4159 12.5728 19.7485 12.5728 19.0566C12.5728 14.8947 16.1847 11.5169 20.7578 11.5169ZM15.5822 8.9335C16.3072 8.9335 16.7871 9.40601 16.7871 10.1229C16.7871 10.8369 16.3072 11.3153 15.5822 11.3153C14.8601 11.3153 14.1365 10.8369 14.1365 10.1229C14.1365 9.40601 14.8601 8.9335 15.5822 8.9335ZM8.84429 11.3153C8.12218 11.3153 7.3942 10.8368 7.3942 10.1229C7.3942 9.40597 8.12218 8.93346 8.84429 8.93346C9.56559 8.93346 10.0463 9.40597 10.0463 10.1229C10.0463 10.8369 9.56559 11.3153 8.84429 11.3153ZM29.5453 18.9422C29.5453 15.1332 25.6935 12.0285 21.3677 12.0285C16.7871 12.0285 13.1797 15.1332 13.1797 18.9422C13.1797 22.7567 16.7871 25.8547 21.3677 25.8547C22.326 25.8547 23.2932 25.6169 24.2559 25.3777L26.897 26.8086L26.1726 24.4282C28.1056 22.993 29.5453 21.0906 29.5453 18.9422ZM18.7126 17.7498C18.2335 17.7498 17.7499 17.278 17.7499 16.7966C17.7499 16.3219 18.2335 15.8442 18.7126 15.8442C19.4406 15.8442 19.9176 16.3219 19.9176 16.7966C19.9176 17.278 19.4406 17.7498 18.7126 17.7498ZM24.0079 17.7498C23.5324 17.7498 23.0518 17.278 23.0518 16.7966C23.0518 16.3219 23.5324 15.8442 24.0079 15.8442C24.73 15.8442 25.2128 16.3219 25.2128 16.7966C25.2128 17.278 24.73 17.7498 24.0079 17.7498Z" fill="currentColor"></path></svg><div class="wechat-dropdown"><p class="text-[#4c576c] text-xs">Connect on WeChat</p><img src="https://cdnd.selectdb.com/assets/images/doris-wechat-b949e908a3bc2776d824f79a9100bd4b.png" alt=""></div></a></div></div></div></div><div class="footer__copyright">Copyright © 2024 The Apache Software Foundation,Licensed under the <a href="https://www.apache.org/licenses/LICENSE-2.0" target="_blank">Apache License, Version 2.0</a>. Apache, Doris, Apache Doris, the Apache feather logo and the Apache Doris logo are trademarks of The Apache Software Foundation.</div></div></div></div> |
