---
title: Ocient Data Intelligence Platform Release Notes
slug: IDCaY62RWBl_w_tH8NL7m
description: Review release notes specific to the Ocient Data Intelligence Platform, showcasing major updates.
docTags: 
createdAt: Wed Sep 07 2022 18:38:13 GMT+0000 (Coordinated Universal Time)
updatedAt: Fri Apr 10 2026 20:00:32 GMT+0000 (Coordinated Universal Time)
---

## 27.0

**Release Highlights**

- Data Pipeline Loading —
  - Added the new [ALTER PIPELINE](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Data Pipelines.md>) SQL statement for schema evolution. You can now modify the SELECT clause of a data pipeline for columns, filters, and transformations without recreating the pipeline.
- Machine Learning Model Updates —
  - Added ensemble models with the Bagging, Boosting, and Stacking models. For details, see [Ensemble Models](<././SQL Reference/Machine Learning Model Functions/Ensemble Models.md>).

**Features**

- Data Pipeline Loading —
  - Added the `line_number` metadata key for loading the line number of the file. For details, see [Load Metadata and File-Based Partitioned Data in Data Pipelines](<././Load Data/Load Metadata and File-Based Partitioned Data in Data Pipelines.md>).
  - Added these extract options for delimited formats and CSV:
    - STRIP\_FIELD\_QUOTES&#x20;
    - STRIP\_ARRAY\_ELEMENT\_QUOTES&#x20;
    - TRIM\_ARRAY\_ELEMENTS
    - For details, see [Delimited and CSV Extract Options](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Data Pipelines.md>).
- Machine Learning Model Updates —
  - Added the regression version of the Decision Tree model. For details, see [Regression Tree](<././SQL Reference/Machine Learning Model Functions/Regression Models.md>).
  - Added `featureSubsetStrategy` and `maxCellsToFetch` options to the Random Forest model. For details, see [Random Forest](<././SQL Reference/Machine Learning Model Functions/Classification Models.md>).&#x20;
- Data Storage —
  - Added the `DRAIN PAGES` SQL statement to manually migrate data from pages to segments. For details, see the [DRAIN PAGES](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Data Integrity and Storage.md>) statement.
  - Added the `NOINDEX` option for time bucket clauses in `CREATE TABLE` statements. This option prevents a TimeKey® from impacting storage ordering. For details, see [TimeKeys and Clustering Keys](<././Database Administration/Schema Design/TimeKeys and Clustering Keys.md>).
  - Added the `ALTER SYSTEM SET DEFAULT STORAGESPACE` SQL statement to enable default storage spaces for tables. For details, see the [ALTER SYSTEM SET DEFAULT STORAGESPACE](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Cluster and Node Management.md>) statement.
  - Added the `ALTER CLUSTER ADD STORAGESPACE` and `ALTER CLUSTER REMOVE STORAGESPACE` SQL statements to link storage spaces to clusters. For details, see the [ALTER CLUSTER ADD STORAGESPACE](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Cluster and Node Management.md>) and  [ALTER CLUSTER REMOVE STORAGESPACE](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Cluster and Node Management.md>) statements.
- CREATE TASK syntax —
  - Added the `LOCATION` and `OPTIONS` keywords to the `CREATE TASK` SQL statement for easier task creation. For details, see [Distributed Tasks](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Distributed Tasks.md>).&#x20;
- Aggregate Functions —
  - Added these functions to assess the performance of machine learning models:
    - `ACCURACY_SCORE`
    - `COEFFICIENT_OF_DETERMINATION`
    - `CONFUSION_MATRIX`
    - `F1_SCORE`
    - `MEAN_ABSOLUTE_ERROR`
    - `MEAN_ABSOLUTE_PERCENTAGE_ERROR`
    - `PRECISION_SCORE`
    - `RECALL_SCORE`
    - `ROC_AUC_SCORE`
    - For details, see [Aggregate Functions](<././SQL Reference/Aggregate Functions.md>).&#x20;
- Scalar Data Conversion Functions —
  - Updated the `BOOLEAN` function to accept new string and numeric values for casting. For details, see [Scalar Data Conversion Functions](<././SQL Reference/Scalar Data Conversion Functions.md>).
- Default Expressions —
  - Expanded valid column defaults from exclusively literals to any constant, deterministic expression that the system casts to the target column. For details, see [Column Constraint](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Tables.md>).
- Values Expressions
  - You can now use value expressions with the `VALUES` keyword in the `FROM` clause. For details, see the [FROM](<././Query Ocient/General SQL Syntax.md>) clause syntax.
- System Catalog Column Additions —
  - `target_rows_per_cycle` and `applied_dynamic_filter_stats` in the `sys.active_operator_instances` system catalog table
  - `default_schema` in the `sys.completed_queries` system catalog table
  - `begin_timestamp` and `end_timestamp` columns in the `sys.segments` system catalog table
  - `begin_timestamp` and `end_timestamp` columns in the `sys.segment_groups` system catalog table
  - `scope_prefix_len`, `transaction_timeout`, `creation_timestamp`, and `last_activity_timestamp` in the `sys.storage_scopes` system catalog table
  - `default_schema` in the `sys.queries` system catalog table
  - `password_days_remaining` column in the `sys.users` system catalog table
  - For details, see [System Catalog](<././SQL Reference/System Catalog Reference/System Catalog.md>).

**Version Compatibility**

- The Ocient® System now supports non-literal expressions (e.g., arithmetic and deterministic functions) with the `DEFAULT` keyword to specify a default value. The system validates default values and ensures consistent casting. For example, you must now specify a TIMESTAMP default value as `TIMESTAMP DEFAULT 0` or `TIMESTAMP DEFAULT '1970-01-01 00:00:00.00000000'`.

## 26.1

**Release Highlights**

- Data Pipeline Loading —
  - The Ocient® System now loads data in the Apache® Avro™ format. For details, see [Load Avro Data](<././Load Data/Data Formats for Data Pipelines.md>). The new `FORMAT avro` extract options are in the syntax of the [CREATE PIPELINE](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Data Pipelines.md>) SQL statement.
- Array Functions —
  - Added the [ARRAY\_CAT\_DISTINCT](<././SQL Reference/Array Functions and Operators.md>) and [ARRAY\_DISTINCT](<././SQL Reference/Array Functions and Operators.md>) array functions for concatenation and deduplication, respectively.
  - Added the [ARRAY\_CONCAT\_AGG](<././SQL Reference/Aggregate Functions.md>)  sorted aggregate function for concatenation.
- Index Types  —
  - &#x20;Added the `ZONE_MAP` index type to store the range of values present in a segment for the indexed column, enabling queries to skip over entire segments with no rows matching a compatible filter. For details, see the [ZONE\_MAP Index Type](<././Database Administration/Schema Design/Secondary Indexes.md>).
- HTTP Query API —
  - Added the [Ocient HTTP Query API](<././Query Ocient/Ocient HTTP Query API.md>) endpoint library to enable execution of SQL statements.&#x20;
- Google® Looker™ Studio Integration —
  - Added the connector for integrating the Ocient System with Looker Studio. For details, see [Looker Connector](<././Connect to Ocient/Ocient Integrations/Looker Connector.md>). &#x20;

**Features**

- Data Pipeline Loading Changes —&#x20;
  - The Ocient System now loads data from Apache® Hadoop® Distributed File System (HDFS) sources. The new source options are in the syntax of the [CREATE PIPELINE](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Data Pipelines.md>) SQL statement. For details, see [Data Pipeline Load of JSON Data from HDFS](<././Load Data/Load by Data Source/Data Pipeline Load of JSON Data from HDFS.md>).
  - The Ocient System can now load data from HDFS. For the source syntax and options, see the [CREATE PIPELINE](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Data Pipelines.md>) SQL statement. For the tutorial, see [Data Pipeline Load of JSON Data from HDFS](<././Load Data/Load by Data Source/Data Pipeline Load of JSON Data from HDFS.md>).
  - The data pipeline loading functionality supports the `TO_CHAR` function for converting `VARBINARY` types. For details, see [Character Data Transformation Functions](<././Load Data/Transform Data in Data Pipelines.md>).
  - Updated the `METADATA` function with:
    - New `key` metadata key to specify the Apache® Kafka® bytes of the Kafka key for the record. For details, see [Load Metadata and File-Based Partitioned Data in Data Pipelines](<././Load Data/Load Metadata and File-Based Partitioned Data in Data Pipelines.md>).&#x20;
    - New `METADATA('hive_partition',search_string)` syntax for retrieving a value from a key-value pair in the filename metadata using Apache® Hive™ naming standards with a filter set in the file path.
  - Added the `NUM_FOOTER_LINES` option for delimited and CSV formats to skip lines for the footer of the file. For details, see [Delimited and CSV Extract Options](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Data Pipelines.md>).
  - The file-based source `START_TIMESTAMP` and `END_TIMESTAMP` options have been removed for data pipelines.
- General SQL Syntax —
  - Added the `TAG` keyword to add a tag to the SQL query. For details, see the [TAG](<././Query Ocient/General SQL Syntax.md>) syntax.
- Table Retention Policies —
  - Added functionality to add retention policies to table definition statements. For details, see [Table Retention Policies](<././Database Administration/Schema Design/Table Retention Policies.md>).
- Schema Database Object —
  - Added the schema database object and its corresponding privileges. For details, see [Data Control Language (DCL) Statement Reference](<././SQL Reference/Data Control Language _DCL_ Statement Reference.md>).
  - You can create, modify, and remove a schema by using the `CREATE SCHEMA`, `ALTER SCHEMA RENAME`, and `DROP SCHEMA` SQL statements, respectively. For details, see [Schemas](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Schemas.md>).
- Cross-Database SSO Authentication —
  - Enabled SSO-authenticated users to access multiple databases using a single identity provider configuration. For details, see [Set Up Cross-Database SSO Integration](<././Database Administration/Authentication Methods.md>).
  - Added the [CURRENT\_GROUPS](<././SQL Reference/Other Functions and Expressions.md>) function that returns the fully qualified names of groups in the database.
- Query Privileges —
  - Added the VIEW QUERIES, VIEW REDACTED QUERIES, and CANCEL QUERIES privileges in the database. For details, see [Data Control Language (DCL) Statement Reference](<././SQL Reference/Data Control Language _DCL_ Statement Reference.md>).
- System and Database Privileges —
  - Added the DELETE privilege to the system and database objects. For details, see [Data Control Language (DCL) Statement Reference](<././SQL Reference/Data Control Language _DCL_ Statement Reference.md>).
- Machine Learning Model Updates —
  - Added `featureArray` option to all models except Simple Linear Regression, Vector Autoregression, Association Rules, and Feedforward Neural Network models.&#x20;
  - Added the `normalize` option.
  - Added new options `bootstrap` and `maxChildThreads` to the Random Forest model.&#x20;
  - Added new options `learningRate`, `numEpochs`, `miniBatchSize`, `gradientClipThreshold`, and `finiteDifferenceH`. These options replace `popSize`, `initialIterations`, `subsequentIterations`, `momentum`, `gravity`, `lossFuncNumSamples`, `numGAAttempts`, `maxLineSearchIterations`, `minLineSearchStepSize`, and `consistentSample` options for Nonlinear Regression, Logistic Regression, Support Vector Machine, and Feedforward Neural Network models.&#x20;
  - Added new models [Gradient Boosted Trees](<././SQL Reference/Machine Learning Model Functions/Regression Models.md>) and [Regression Tree](<././SQL Reference/Machine Learning Model Functions/Regression Models.md>).
- Data Type Updates —&#x20;
  - Array and tuples without specified data types, such as `ARRAY[]`, `ARRAY[NULL]`, and `TUPLE(NULL, NULL)`, are now valid expressions. The Ocient System treats these types as `CHAR[]` for arrays and `TUPLE<<CHAR,CHAR>>(NULL, NULL)` for tuples.
- System Catalog Table Changes —
  - The `sys.pipeline_files` table no longer contains the `stream_source_id` column. The table now contains the `file_index` column.
  - The `sys.columns` table has these new columns:
    - `system` — Boolean value that specifies whether the column is internal
    - `type_hint` — expected type of the column
  - With k-nearest neighbors models, the `machine_learning_models` table returns the `K_NEAREST_NEIGHBORS` value for the `machine_learning_model_type` column.
  - All authenticated users can now view these tables:&#x20;
    - `sys.nodes `
    - `sys.service_roles `
    - `sys.node_status `
    - `sys.channel_endpoint_parameters`
  - The `encrypted` column has been removed from the `sys.channel_endpoint_parameters` table.

**Version Compatibility**

- Decimal values use rounding instead of truncation when you cast types from DECIMAL or STRING to a lower precision and scale.
- The system automatically grants the DELETE privilege to the creator of database and schema objects for objects created in version 26.1 and later.&#x20;

## 26.0

Internal updates only.

## 25.5

**Version Compatibility**

- The `||` operator behavior has changed. If you specify a NULL value for concatenation, then the result is NULL.

## 25.2

**Features**

- Data Pipeline Changes —
  - Added the `WHERE` filter keyword to the `SELECT` SQL statement in the `CREATE PIPELINE` statement. For details, see [CREATE PIPELINE](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Data Pipelines.md>).
  - Added new `HEADERS` Amazon® Web Services℠ (AWS℠) S3 source option for the `CREATE PIPELINE` SQL statement to define the headers to send with every request.
  - Added the `MODE` option to the `PREVIEW PIPELINE` SQL statement. This option indicates whether to perform a validation of the `PREVIEW PIPELINE` statement. For details, see [PREVIEW PIPELINE](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Data Pipelines.md>).
  - Added the `streamloader.extractorEngineParameters.configurationOption.filesystem.access.directories` configuration setting to denote where you can load server file system data. For details, see [Configuration Settings for Data Pipelines](<././System Administration/Loading Administration/Configuration Settings for Data Pipelines.md>).
  - Updated the `METADATA` function with a new metadata key and syntax. For details, see [Load Metadata and File-Based Partitioned Data in Data Pipelines](<././Load Data/Load Metadata and File-Based Partitioned Data in Data Pipelines.md>):
    - The `source_record_id` metadata key indicates a unique source row identifier that is generated by the Ocient System during loading.&#x20;
    - New `METADATA('hive_partition',search_string)` syntax that uses a search string as a key for searching the filename metadata to return the value from the key-value pair as a string.
  - Added these transformation functions. For details, see [Transform Data in Data Pipelines](<././Load Data/Transform Data in Data Pipelines.md>):
    - `IF` — Evaluate an expression for `true` or `false`.
    - `CASE WHEN` — Return a result value based on whether an expression is `true`.
    - `ELEMENT_AT` — Returns the element value of the array or tuple at the specified index.
    - `MAP_KEYS` — Returns the keys in the specified JSON string.
    - `MAP_VALUES` — Returns the values in the specified JSON string.
    - `REDUCE` — Applies a merge function to a starting value and all elements in the array, and then reduces the array to a single value.&#x20;
- System Catalog Table Changes —
  - Added Boolean parameter `scopes_on_refresh` to the `sys.oidc_integrations` system catalog table to include or not include scopes when submitting a refresh token request.

## 25.1

**Features**

- New data pipeline transformation function `ARRAY_CONTAINS` determines whether an array contains the specified value, including NULLs.
- System Catalog Table Changes —&#x20;
  - The `record_number` column of the `sys.pipeline_errors` system catalog table is now the index, which starts at 1, of the processed record relative to the source file (or Apache® Kafka® partition) where the record was extracted. &#x20;
  - The `record_offset` column of the `sys.pipeline_errors` is now the offset of 1 for the processed record relative to the start of the source file for files with contiguous records, -1 for files with non-contiguous records (i.e., Apache® Parquet™), and the Kafka offset for Kafka loads.

## 25.0

**Release Highlights**

- Load Data into the Ocient® System — The new way to load data into the Ocient System is to use Data Pipeline functionality. This functionality supports loading data from multiple formats such as CSV, JSON, and Apache® Parquet™. Data pipelines support data transformation during the load. You can load multiple data formats, including geospatial data. This functionality supports these new syntaxes:&#x20;
  - `CREATE PIPELINE` SQL statement to create a data pipeline.
  - `DROP PIPELINE` statement to remove a data pipeline.
  - `PREVIEW PIPELINE` statement to view the results of a data load before creating the data pipeline.
  - `START PIPELINE` statement to start the execution of the data load.
  - `STOP PIPELINE` statement to stop the execution of the data load.
  - `ALTER PIPELINE RENAME` statement to rename a data pipeline.
  - `EXPORT PIPELINE` statement to return the `CREATE PIPELINE` SQL statement used to create the specified data pipeline.
  - `CREATE OR REPLACE PIPELINE FUNCTION` statement to define your own data pipeline function for loading data.
  - `DROP PIPELINE FUNCTION` statement to remove the data pipeline function.
  - For details, see [Load Data](<././Load Data.md>) and [Data Pipelines](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Data Pipelines.md>).&#x20;

**Features**

- Java® Runtime Environment — The Ocient System installation requires Java® 21, where the `openjdk-21-jre-headless` package is recommended.
- Persistent Data — Data persists in the system storage space of the Ocient System in system catalog tables such as the `sys.completed_queries` table. For details, see [Storage Space Settings](<././Ocient Architecture/Core Elements of an Ocient System.md>).
- Default Compression Scheme — The default compression scheme for variable-length columns is `NONE`.
- Set more granular privileges for database and system objects. For details, see [Object-Type Level Privileges Management](<././Database Administration/Manage Users_ Groups_ and Roles/Object-Type Level Privileges Management.md>) and [Data Control Language (DCL) Statement Reference](<././SQL Reference/Data Control Language _DCL_ Statement Reference.md>).&#x20;
- The `SHOW SYSTEM TABLES` SQL statement displays the system catalog tables.
- System Catalog Table Changes —&#x20;
  - `information_schema.tables` has a column `table_type` with two new types: `SYSTEM TABLE` for system catalog tables and `SYSTEM VIEW` for information schema tables. `BASE TABLE` and `VIEW` still exist for user-defined tables and views.
  - `sys.system_tables` has new columns: `schema`, `type`, and `description`.
  - `sys.segment_groups` has the new `depth` and `visibility` columns.
  - `sys.stored_segments` has the new `abnormal_placement` column.
  - `sys.completed_queries` contains persistent data. The system experiences a small loading delay between completing the execution of a SQL query and when the corresponding results appear in the table.
  - The definition for the `sys.queries` table has been updated to remove these columns:&#x20;
    - `time_start`
    - `time_optimization_start`
    - `time_execution_start`
    - `time_first_byte_sent`
  - The definition for the `sys.queries` table has been updated to change the data type of these columns from `LONG` to `TIMESTAMP`:&#x20;
    - `timestamp_start`
    - `timestamp_optimization_start`
    - `timestamp_execution_start`
    - `timestamp_first_byte_sent`
  - The definition for the `sys.locks` table has been updated for these columns:
    - Renamed `createTime` to `create_time`.
    - Renamed `lastRefreshTime` to `last_refresh_time`.
    - Renamed `priortyId` to `priority_id`.
  - Updated column descriptions for these tables:
    - `sys.segment_directories`&#x20;
    - `sys.addendum_directories`&#x20;
    - `sys.segment_groups`
    - `sys.segment_parts`
    - `sys.segments`
    - `sys.stored_segments`
    - `sys.service_role_status`
  - Updated the data type for the `segment_group_ids` column in the `sys.segment_group_transfers` table from `ARRAY(LONG)` to `ARRAY(CHAR)` because segment group identifiers are unsigned.
  - Renamed the `service_roles_id` column in the `sys.service_role_channel_endpoint` table to `service_role_id` to align with the naming in the `sys.service_role_status` table.

**Version Compatibility**

- `sys.built_in_views` has been removed. The built-in views have been removed from `sys.built_in_views`, `sys.views`, and `information_schema.views`. The view information now appears in the `sys.system_tables` system catalog table. `information_schema.views` includes user-defined views and only information schema views.
-
  `sys.views` no longer has a `view_type` column.

## 24.0

**Release Highlights**

- Better SQL Errors: Improved error messages for poorly constructed SQL queries.
- Enhance Build Metadata: Updated Ocient® package file names and build information for clarity.
- Improved Tracing: Enabled using the `TRACE` keyword in SQL queries to profile query performance. For details, see the [TRACE](<././Query Ocient/General SQL Syntax.md>) keyword.
- Large Blobstore Support: Enabled more than 2TiB of data to spill per disk for high-density drive situations.
- Large Drive Support: Added support for drives up to 15.36TB in size per node.
- Rebalance System: Added the `REBALANCE` task, which enables optimization of query efficiency by transferring data around the system until nodes are roughly balanced in terms of data volume per node. For details, see [Rebalance System](<././System Administration/Maintenance Overview/Expand and Rebalance System.md>).
- System Storage Space: Added support for multiple storage spaces and enabled the creation of a system storage space for internal Ocient System data.
- Workload Management Usability:&#x20;
  - Added support for assigning service classes to queries based on query text. For details, see the [CREATE SERVICE CLASS](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Users_ Groups_ and Service Classes.md>) SQL statement.
  - Added support for changing the priority for queries. For details, see the [ALTER QUERY](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Users_ Groups_ and Service Classes.md>) SQL statement.

**Features**

- \[DB-19266]: Network Configuration —&#x20;
  - All nodes must now belong to a connectivity pool. Manage connectivity pools using these new SQL statements. For details, see [CONNECTIVITY POOL](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Cluster and Node Management.md>).
    - `CREATE CONNECTIVITY_POOL` to create a connectivity pool.
    - `DROP CONNECTIVITY_POOL` to drop a connectivity pool.
    - `ALTER CONNECTIVITY_POOL SET` to set the metadata of a connectivity pool.
    - `ALTER CONNECTIVITY_POOL RENAME TO` to rename a connectivity pool.
    - `ALTER CONNECTIVITY_POOL ADD PARTICIPANTS` to add nodes to a connectivity pool.
    - `ALTER CONNECTIVITY_POOL DROP PARTICIPANTS` to remove nodes from a connectivity pool.
  - The `ALTER NODE SET ADDRESS` SQL statement changes the internal IP address for a node. For details, see [ALTER NODE SET ADDRESS](<././SQL Reference/Data Definition Language _DDL_ Statement Reference/Cluster and Node Management.md>).
  - You can now configure the network of an Ocient System. For details, see [Manage the Network Configuration of an Ocient System](<././System Administration/System Configuration/Manage the Network Configuration of an Ocient System.md>).
  - Redirects now occur only within connectivity pools. When you upgrade an Ocient System, you must first configure a connectivity pool.
- \[DB-28986]: System Catalog Table Updates —&#x20;
  - Renamed `client_version` to `protocol_version` in the `sys.queries` and `sys.completed_queries` system catalog tables.&#x20;
  - Added `driver_version` column to the `sys.queries` and `sys.completed_queries` tables.
- \[DB-29788]: Regular Expression Functions —
  - Added new functions that use regular expression search patterns. The new functions are:&#x20;
    - [REGEXP\_COUNT](<././SQL Reference/Character and Binary Functions.md>) &#x20;
    - [REGEXP\_INSTR](<././SQL Reference/Character and Binary Functions.md>)&#x20;
    - [REGEXP\_REPLACE](<././SQL Reference/Character and Binary Functions.md>)&#x20;
    - [REGEXP\_SUBSTR](<././SQL Reference/Character and Binary Functions.md>)&#x20;

**Version Compatibility**

- The `bootstrap.conf` no longer supports `highspeedAddress` as an advanced system configuration option. For other options, see [Node Bootstrapping Reference](<././System Administration/Install an Ocient System/Installation Reference/Node Bootstrapping Reference.md>).
- The default behavior for the `UNNEST` function no longer uses the `NULL_INPUT` clause for a multi-item `SELECT` list. If you would like to utilize the default behavior from version 23.0 and prior, you may do so by changing `ALTER SYSTEM ALTER CONFIG SET sql.unnestLegacySelectListBehavior = 'true'`.

**Feature Removal**

- Removed `COMPRESSION LZ4` from the compression options. This compression scheme remains in use as part of `COMPRESSION DYNAMIC` for variable-length columns.
- Removed the table-valued function `REPLACEMENT_JOIN`. For creating compressed lookup tables, see [Global Dictionary Compression](<././Database Administration/Schema Design/Table Compression Options/Global Dictionary Compression.md>).
- Particle swarm optimization functionality has been removed from the Ocient System.
- ODBC connection has been removed from the Ocient System.

## 23.0

**Release Highlights**

- All machine learning functionality is available to use. For details, see [Machine Learning Model Functions](<././SQL Reference/Machine Learning Model Functions.md>) and [Machine Learning in Ocient](<././Analysis in Ocient/Machine Learning in Ocient.md>) to get started.
- Delete Syntax: Enabled the deletion of individual rows in the database.
- Integrations: Added drivers and support for the following third-party applications:&#x20;
  - DBeaver
  - Tableau®

**Features**

- \[DB-13607]: Delete Syntax — Added the SQL DELETE statement syntax that enables the deletion of individual rows in the database.&#x20;
- \[DB-18020]: Large Geospatial Types — Increased the size of `LINESTRING` and `POLYGON` geospatial data types to 512 MB. For details, see [Load Geospatial Data](<././LAT Reference/LAT Data Types in Loading.md>).
- \[DB-19048]: Geospatial Index — Added the SPATIAL index type for indexing geospatial data. For details, see [SPATIAL Index Type](<././Database Administration/Schema Design/Secondary Indexes.md>).
- \[DB-18280]: Connectors Refresh — Added integration with DBeaver and Tableau®. For details, see [DBeaver Integration](<././Connect to Ocient/Ocient Integrations/DBeaver Integration.md>) and [Tableau Integration](<././Connect to Ocient/Ocient Integrations/Tableau Integration.md>).
- \[DB-20412]: Multi-Cluster Loading and Cluster of Clusters — Added support for loading and working with multiple clusters. For details, see [Multiple Storage Clusters for Loading Data](<././System Administration/Multiple Storage Clusters for Loading Data.md>).
- \[DB-21609]: Machine Learning Model Updates —&#x20;
  - Added the [Gaussian Mixture Model](<././SQL Reference/Machine Learning Model Functions/Clustering and Dimension Reduction Models.md>). &#x20;
  - Added the [Association Rules Model](<././SQL Reference/Machine Learning Model Functions/Other Models.md>).&#x20;
  - Added the [Linear Discriminant Analysis Model](<././SQL Reference/Machine Learning Model Functions/Clustering and Dimension Reduction Models.md>).&#x20;

**Version Compatibility**

- Large Geospatial Types are not backwards compatible with earlier releases. For details, see [Version Compatibility](<././Version Compatibility.md>).
- The database data control language (DCL) denotes user role privileges to remove data using the `DELETE` keyword instead of `TRUNCATE`.&#x20;

## 22.0

**Release Highlights**

- HyperLogLog (HLL): Added HLL sketch functionality.&#x20;
- Information Schema: Added the `information_schema` schema that shows system metadata.&#x20;
- Integrations: Added drivers and support for the following third-party applications:&#x20;
  - Metabase℠
  - SQLAlchemy
  - Apache® Superset®&#x20;

**Features**

- \[DB-13603]: Information Schema — Added the information\_schema schema that shows system metadata in an accessible format.
- \[DB-20484]: Superset Integration — Merged sqlalchemy-ocient driver into Superset repository, allowing Superset to support Ocient® database connections.&#x20;
- \[DB-20484]: SQLAlchemy Integration — Published sqlalchemy-ocient driver to PyPI.
- \[DB-21011]: EXCEPT Clause —  Added EXCEPT clause so that SELECT \* queries can explicitly omit columns from results.
- \[DB-21769]: Metabase Integration — Added Ocient as a Metabase partner driver, allowing Metabase to access Ocient databases out-of-the-box.&#x20;
- \[DB-23030]: JDBC Packaging — Removed OpenJump dependency from ocient-jdbc4.
- \[DB-23175]: Time Zone Adjustment Support — Added various improvements to time zone functionality, including:&#x20;
  - Support for daylight savings adjustment based on time zone.&#x20;
  - Added time zone functions CONVERT\_UTC\_TIMESTAMP\_TO LOCAL and CONVERT\_LOCAL\_TIMESTAMP\_TO\_UTC. For more information, see&#x20;
    [Time Zone Functions](<././SQL Reference/Time Zone Functions.md>).&#x20;
  - Enhanced performance for time zone conversion.&#x20;
- \[DB-23177]: Push-Down Aggregation to the I/O Layer — Under certain conditions, the system pushes aggregation to the I/O operator for better efficiency and performance.
- \[DB-23299]: HLL Sketch Functionality — Added support for variable log2k HLL sketch algorithm and associated functions. For details, see the [HLL Functions page](<././SQL Reference/HyperLogLog Functions.md>).
- \[DB-23745]: Implement Evacuate Node — Evacuate node is a tool to move all segments off of a node in a system that is overprovisioned to the other nodes in the cluster. This tool is useful when you replace drives or a node.
- \[DB-19888]: Machine Learning Model Updates —&#x20;
  - The Ocient System scopes machine learning models to schemas. The system assigns the `pre_v22_mlmodel` schema to any model you created prior to version 22.0.
  - The `sys.multiple_linear_regression_slopes` system catalog table has been removed.
  - Rename machine learning models using ALTER MLMODEL.
  - New DDL commands:
    - CREATE OR REPLACE
    - REFRESH
    - EXPORT
  - For details about the new syntaxes, see [Machine Learning Model Functions](<././SQL Reference/Machine Learning Model Functions.md>).

**LAT Features**

- \[LAT-1469]: Manual Configuration of LAT Endpoints — Enabled manual configuration of LAT endpoints for OAuth with OKTA®.
- \[LAT-1475]: Enablement of Stopping Load Processing During Error Condition — Added default behavior to stop processing during file loading in the event of an unrecoverable error when the system extracts records from a file. For details, see [continue\_on\_unrecoverable\_error](<././LAT Reference/LAT Pipeline Configuration.md>).
- \[LAT-1476]: Enablement of LAT Service in Installation — Enabled LAT Service in `systemd` by default upon installation completion. This update reflects a change in the default behavior during installation.
- \[LAT-1477]: LAT Version for Metrics — Exposed `lat_version` in the metrics.
- \[LAT-1557]: Support for Loading Multiple S3 Buckets — Added LAT functionality to load data from multiple S3 buckets simultaneously within the same pipeline.&#x20;

**Version Compatibility**

- Information Schema — Views created prior to Version 22.0 do not have column data appearing in the information\_schema. You can drop and recreate these views to populate column data.
- LAT — Version 3.0.0 and greater is only compatible with Version 22.0 and greater of the Ocient system. For details, see [Version Compatibility](<././Version Compatibility.md>).

## 21.0

**Release Highlights**

The Ocient System now supports the following operating systems:

- Ubuntu® 20.04
- Debian 11
- RHEL 8

Other highlights include:

- Whole column compression: Added Zstandard (ZSTD) compression for fixed and variable length columns.
- Check system configuration: Added `precheck` and `postcheck` commands to check system configuration before and after installation.
- Workload management dynamic priority: Enabled the adjustment of the query priority dynamically at the session, service class, and query levels.
- Ability to quiesce node: Added process for graceful node shutdown.

**Features**

- \[DB-18636]: ZSTD Compression - Added a new whole-column compression scheme (ZSTD) that can be enabled for fixed and variable length columns.
- \[DB-18990]: Improved Stats Storage And Usage - Various improvements have been added to speed up the fetching of statistics by the optimizer and ensure it gets up-to-date statistics. These changes primarily center around probability density functions being stored as pre-aggregated stats files instead of on a per-segment basis.
- \[DB-20190]: Distributed Tasks - Added `check_disk` task type and new vtables `sys.subtasks`, `sys.tasks`, and `sys.rebuild_tasks` for monitoring tasks. Remove `CHECK DATA` command.
- \[DB-19117]: Metadata - Added `participating_nodes` to the `sys.queries` and `sys.completed_queries` virtual tables.
- \[DB-18633]: Graceful Node Shutdown - Added quiesce process for graceful node shutdown.
- \[DB-18061]: LCK Deprecation - Added new disk data format that is smaller and also improves performance of some index based queries.
- \[DB-19414]: Range Query Improvement - Improved performance of range queries by utilizing the inverted secondary index.
- \[DB-20168]: Geospatial Function Expansion - Added these geospatial scalar functions.
  - Measurement Functions
    - ST\_ANGLE
    - ST\_DISTANCESPHERE
    - ST\_DISTANCESPHEROID
    - ST\_LENGTH2D
    - ST\_HAUSDORFFDISTANCE
  - Analytic and Property Functions
    - ST\_DIMENSION
    - ST\_GEOHASH
    - ST\_SRID
    - ST\_ISPOLYGONCW
    - ST\_ISPOLYGONCCW
  - To String and Binary Functions
    - ST\_ASWKT
    - ST\_ASWKB
    - ST\_ASEWKT
  - Geography Simplification Function
    - ST\_SIMPLIFY
  - Constructor Functions
    - ST\_POINTFROMGEOHASH
    - ST\_GEOGPOINT
    - ST\_MAKEPOLYGONORIENTED
    - ST\_POINT\_FROMEWKT
    - ST\_LINESTRING\_FROMEWKT
    - ST\_POLYGON\_FROMEWKT&#x20;
    - ST\_MAKEENVELOPE
  - Additionally, you can construct ST\_POLYGON types directly from a POINT\[] without going through an intermediate ST\_LINESTRING.

**Keywords**&#x20;

Added these new keywords as reserved words in the Ocient system.

- ANALYSIS
- AUTOREGRESSION
- BAYES
- CANCEL
- COMPONENT
- DECISION
- DISABLE
- DISABLE\_STATS\_FILE\_UPDATES
- ENABLE
- FEEDFORWARD
- INSERT
- KMEANS
- KNN
- LOGISTIC
- MACHINE
- MOVE
- NAIVE
- NETWORK
- NONLINEAR
- PRINCIPAL
- REPLACE
- SOURCE
- SUPPORT
- TREE
- VECTOR
- ZSTD

## 20.0

**Release Highlights**

- CREATE TABLE AS SELECT SQL Statement: Extract, load, and transform (ELT) workflow functionality to extract data and load it into a new database table by using the query results from a SELECT SQL statement. The tables you create using the CREATE TABLE AS SELECT SQL statement have some indexing limitations in version 20.0. For details, see the "About Create Table As Select (CTAS)" section of the Ocient user documentation.
- INSERT INTO SQL Statement: ELT workflow functionality to extract data and insert it into an existing database table using the INSERT INTO SQL statement.
- N-gram Indexes: Full index on VARCHAR, VARCHAR arrays, and VARCHAR tuple components for efficient queries using the LIKE SQL statement.
- Large VARCHAR \[DB-16142]: Support VARCHAR columns up to 1GB in size.
- Ocient Simulator: An instance of the Ocient system for data loading and functional testing.
- Single Sign-On (SSO): Authenticate access to Ocient through an external SSO server and assign SSO users to groups in Ocient.

**Feature Removal**

The ALTER ROLE DDL command has been removed. You can make all changes using the ALTER CONFIG SQL statement. To alter a role, prefix the key with the role name followed by a dot.

The following system tables have been added:

- average\_bb\_sizes
- linear\_combination\_regression\_models
- node\_config
- node\_status
- sso\_connections
- storage\_device\_status

The following system tables have been removed:

- hugepage\_configurations
- memory\_module\_models
- node\_memory\_modules
- oidc\_integrations
- oidc\_sessions
- polynomial\_regression\_models
- security\_integrations
- sessions

## 19.0

**Features**

- \[DB-14527] - Adaptive Water Mark Feature - Indexer Node dynamically increase and reduce batch size without manual tuning
- \[DB-14656] - Added a rest endpoint to expose a node’s configuration parameters (:9090/v1/configparams)
- \[DB-15123] - Expose cluster total storage space and storage usage through virtual tables
- \[DB-15515] - Add support for expr::dtype cast notation
- \[DB-16289] - Remove the web ui and YAML service role configuration
- \[DB-16904] - Allow any predicate type to be used in conjunction with the values in arrays
- \[DB-17889] - Improve ability to continue data loading when a foundation node is down
- \[DB-18393] - Leverage hyperthreading in query execution
- In v19 the service role configuration previously set through the web UI has been replaced by the ALTER …​ ALTER ROLE/CONFIG …​ DDL command. The web UI is still available in v19, but will be removed in a subsequent release. The ALTER …​ ALTER ROLE/CONFIG …​ command should be used to change system configuration, rather than the web UI. Please reference the Upgrade Ocient Software section of the user documentation for details.
- \[DB-12747] - Add support for lateral joins.
- \[DB-13924] - Add support for multi-column subqueries.
- \[DB-14990] - Add support for native right joins.
- \[DB-15996] - Add support for array\_to\_string function.
- \[DB-16231] - Improve GIS function performance and introduce expanded support for GIS functions. Please refer to the User Documentation for details.
- \[DB-17037] - Add new scalar functions and operators added for GIS types (POINT, LINESTRING, and POLYGON). Please refer to the User Documentation for details.
- \[DB-17892] - Add support for right lateral joins.
- \[DB-16061] - Secondary indexes can now be created on VARCHAR and VARCHAR\[] columns. Please refer to the User Documentation for details.

## 18.0

**Features**

- \[DB-17635] - Remove query log properties timestamp\_optimizationcomplete and time\_optimizationcomplete and add new properties timestamp\_optimizationstart and time\_optimizationstart
- \[DB-16567] - Make error messages more clear for queries with GroupBy missing
- \[DB-17316] - Change array\_length(empty array) to return 0
- \[DB-16417] - Allow for integral types for integer field is GIS functions
- \[DB-16200] - Make Explains more convenient for the user.
- \[DB-16092] - Distributed Result Set Caching
- \[DB-15623] - Add support for rebuilding individual nodes via DDL
- \[DB-15375] - ALTER CLUSTER ADD PARTICIPANTS DDL
- \[DB-14720] - Provide a way to kill long running optimizations
- \[DB-14017] - Support for CLI command history across sessions

## 17.0

**Features**

- \[DB-12888] - Add support for array values larger than 128 KB. The new maximum value of an array is 512 MB.

## 16.0

**Features**

- \[DB-10329] - Add support for full disk encryption of Opal drives. Disk encryption will be automatically enabled when Opal support is detected.

## 14.0

**Features**

- \[DB-14159] - Default hex values for binary or varbinary columns must contain a leading 0x

## 13.0

**Features**

- \[DB-14330] - Remove last dependencies on PostgreSQL from the database

## 12.0

**Features**

- \[DB-13334] - Add support for zip unnest, which unnests multiple arrays in parallel

## 11.0

**Features**

- \[DB-12887] - Add support for the array of tuples. Users can create array columns containing tuple SQL types. Please refer to the User Documentation for the latest information on supported data types.
- \[DB-12885] - Add support for unnest(), which expands array elements from input array columns out to individual output rows

## 10.0

**Features**

- \[DB-12394] - Added support for running on CentOS 8.

## 9.0

**Features**

- \[DB-13162] - Added support for Tasks to the System Catalog
- \[DB-10332] - Implemented access controls on system and database-level objects. Improved users, groups, and added new roles within Ocient.
- \[DB-12829] - Optionally enforce encrypted connections for JDBC and ODBC.

## 8.0

**Features**

- \[DB-10330] - External Network Security. SSL/TLS support in ODBC and JDBC, SSL support for the web interface.

## 7.0

**Features**

- \[DB-10921] - Adds support for multi-dimensional arrays and the ability to do joins, windows, sorts and aggregations that involve arrays.
- \[DB-10927] - Adds support for global dictionary compression (GDC) on VARCHAR array columns and the ability to do replacement joins.
- \[DB-10282] - Adds support for DROP COLUMN DDL to remove columns from a table.
- \[DB-11472] - Adds support for skipping failed rows for CSV loading up to some specified threshold.

## 6.0

**Features**

- \[DB-9707] - Scriptable Bulk Load Essentials: Allows users to create translations and launch bulk load tasks via DDL
- \[DB-10479] - Adds support for Tableau through Ocient’s JDBC Custom Connector. Users can find Ocient’s connector and the installation instructions on Tableau’s extension gallery. Please refer to Tableau for more inforamation.

## 5.0

**Features**

- \[DB-9477] - Adds support for the array data type. Users can create single-dimensional array columns from any other supported data type. Please refer to the User Documentation for the latest information on supported data types.
- \[DB-9656] - Add column support. The engine now supports the add column DDL statement with the ability to add columns to an existing table. Existing data that was loaded without the new column uses the configured default values when queried. Please refer to User Documentation for information on the DDL syntax and default values.

## 4.0

**Features**

- \[DB-6386] - Availability of the storage engine, allowing queries to run with a node or drive failure
- \[DB-6588] - Bulk loading of CSV files from HDFS or an S3 endpoint
- \[DB-7221] - Delta compression in the TKT engine for timestamp columns
- \[DB-7623] - Virtual tables to retrieve information from the storage cluster state
- \[DB-7247] - OS Upgrade functionality
- \[DB-6125] - AWS initial support
- \[DB-6383] - Data Definition Language (DDL) operations
- \[DB-6940] - All system configuration in the System Catalog
- \[DB-6362] - Stats Virtual Tables
- \[DB-7098] - External Window Operator support
- \[DB-7497] - List Running Tasks Page
- \[DB-7097] - Segment Group Deletion
- \[DB-7139] - Cancel Query and Cancel Task support
- Numerous stability and performance improvements

