> ## Documentation Index > Fetch the complete documentation index at: https://docs.activeviam.com/llms.txt > Use this file to discover all available pages before exploring further. # Vectors in DirectQuery Atoti Datastore technology allow users to store, retrieve and aggregate [vectors](../core_features/vectors) as any other value. Atoti DirectQuery offers the same features, whatever the underlying database is. Some databases, like Clickhouse, offer a native support for vectors, as the [Array type](https://clickhouse.com/docs/en/sql-reference/data-types/array/), with pre-build functions supporting Atoti use-cases. Other databases do offer vectors types, like [BigQuery Array](https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types#array_type) or [PostgreSQL Array](https://www.postgresql.org/docs/current/arrays.html) but lack of the appropriate methods, such as aggregation on these vectors. DirectQuery fills this technical gap with technics emulating vectors. Generally, when a database natively support vectors, emulation techniques are not implemented, as they would only impair performance while not providing interesting benefits. In the following sections, databases listed in each section are to be considered as supporting the featured vector support. | Database | Native Support | Emulation via multi row values | Emulation via multi column values | | ----------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Amazon Redshift | cross | tick | tick | | ClickHouse | tick | cross | cross | | Databricks | partial | tick | tick | | Google BigQuery | partial | tick | tick | | Microsoft Azure Synapse | cross | tick | tick | | Microsoft SQL Server | cross | tick | tick | | Snowflake | partial | tick | tick | ## Native support A table defining fields of the type Array are automatically represented as vectors. This is the most efficient way of dealing with vectors. The Database automatically provides the best solution for storage. Join operations are simpler because this is a single row in the Database.
However, aggregations on such values is not natively supported in most of the Database Engines. When available, DirectQuery makes use of the existing functions. Otherwise, such data type is not rejected at query time. **Full support: out of the box**: * Clickhouse **Partially supported**: * *can only support run list queries but no aggregation queries.* * BigQuery * Databricks * Snowflake ## Vector emulation When the Database does not support vectors, DirectQuery emulates them using the following techniques. ### Emulation with multiple columns Vectors of fixed size can be represented as many columns in a table. Then, one can instruct DirectQuery to regroup all those columns as a vector.
| Id | Category | Price1 | Price2 | .. | PriceN | | -- | ----------- | ------ | ------ | -- | ------ | | 1 | category\_1 | 10 | 20 | .. | 307 | | 2 | category\_2 | 12 | 420 | .. | 748 | In the table above, the table contains two standard fields `Id` and `Category`. Columns `Price1` to `PriceN` represent the vector exploded across N columns. Being the scene, DirectQuery will apply the same operation to each column and return a final vector value to calling code. The only condition for this emulation is that all vectors share the same size throughout the table. This size matches the number of columns grouped to form the vector. It is possible to put multiple vectors in the same table. Each vector is identified by its prefix name. In such case, the vectors can have any size. | Id | Category | Price1 | .. | PriceN | Quantity1 | .. | QuantityM | | -- | -------- | ------ | -- | ------ | --------- | -- | --------- | | 1 | cat1 | 1.99 | .. | 0.99 | 73 | .. | 17 | *where N and M can be different.* **Full support**: * BigQuery * Databricks * Microsoft Azure Synapse * Microsoft SQL Server * Redshift * Snowflake #### Limitations Multi column vectors have a few limitations: * All vectors must have the same size * Some databases have a limit in the number of columns in a single table, generally a few thousands, so it cannot be used to store huge vectors. * The generated SQL string to query Multi Column vector tables is big. On some database it will take up to a few seconds to compile this query before running it. ### Emulation with multiple rows Vectors of any size can be represented as many rows in a table. Then, one can instruct DirectQuery to regroup all those rows as a vector, using a field as the pivot value indexing all vectors.
| Id | Category | Index | Price | | -- | ----------- | ----- | ----- | | 1 | category\_1 | 0 | 10 | | 1 | category\_1 | 1 | 20 | | 1 | category\_1 | .. | .. | | 1 | category\_1 | N | 307 | | 2 | category\_2 | 0 | 12 | | 2 | category\_2 | 1 | 420 | | 2 | category\_2 | .. | .. | | 2 | category\_2 | M | 748 | The table above contains one vector field `Price`, indexed by `Index`. Being a technical field, `Index` is automatically hidden from the "vectorized" table. In addition, DirectQuery automatically configure the remaining columns `Id` and `Category` as primary keys of the table.
There are no constraint on the size of distinct vectors. Here, we can have N \< M or N = M or N > M. It is possible for such a table to define multiple vectors. The following now contains two vectors `Price` and `Quantity`. `Index` remains the indexing column. | Id | Category | Index | Price | Quantity | | -- | ----------- | ----- | ------ | -------- | | 1 | category\_1 | 0 | 10.99 | 7 | | 1 | category\_1 | .. | .. | .. | | 1 | category\_1 | N | 307.01 | 4 | In this case, DirectQuery requires that all vectors have the same size for a given key. Here, for `(Id, Category) = (1, category_1)`, `Price` and `Quantity` both have N elements. **Full support**: * BigQuery * Databricks * Microsoft Azure Synapse * Microsoft SQL Server * Redshift * Snowflake #### Query factorization For some aggregations, such as `sum`, it is possible to factorize the SQL query, so that the actual computation is a multiplication (e.g. `n * vector`) instead of a sum of n elements (e.g. `vector + ... + vector`). This is particularly useful for cases with many-to-one relationships, so this optimization is enabled by default. It can be disabled in the `IDatabaseSettings`, for instance if the data model is mostly one-to-one relationships.