How to use vectors

There are many ways to define vectors depending on the database, read the vectors documentation to know what is supported by your database.

Table with native vectors

If the table has native vectors, you can directly use them in a table without any transformation.

ClickHouse
Databricks

In ClickHouse, native vectors are called Array. Let’s create an example table with a few of them:

create table if not exists
  TUTORIAL.NATIVE_VECTORS
(
  `PRODUCT` String NOT NULL,
  `QUANTITY` Array(Float64) NOT NULL
)
ENGINE = MergeTree()
ORDER BY (PRODUCT)
;
insert into TUTORIAL.NATIVE_VECTORS values
('product_1', [10, 20, 15, 25, 10]),
('product_2', [50, 65, 55, 30, 80]);

This table can directly be used:

final TableDescription table =
    discoverer.discoverTable(new ClickhouseTableId("TUTORIAL", "NATIVE_VECTORS"));

In Databricks, native vectors are called Array. Let’s create an example table with a few of them:

create table if not exists
  `tutorial`.`native_vectors`
(
  `PRODUCT` STRING NOT NULL,
  `QUANTITY` ARRAY<DOUBLE> NOT NULL
);
insert into tutorial.native_vectors values
('product_1', ARRAY(10, 20, 15, 25, 10)),
('product_2', ARRAY(50, 65, 55, 30, 80));

This table can directly be used:

final TableDescription table =
    discoverer.discoverTable(
        new SqlTableId(CATALOG_NAME, TUTORIAL_SCHEMA_NAME, "NATIVE_VECTORS"));

Table with one column per vector element

Let’s create a table with 5 columns, each containing one element of a Quantity vector for a given product.

CREATE TABLE IF NOT EXISTS `tutorial`.MULTI_COLUMN_QUANTITY (
  PRODUCT STRING NOT NULL,
  QUANTITY_0 FLOAT64 NOT NULL,
  QUANTITY_1 FLOAT64 NOT NULL,
  QUANTITY_2 FLOAT64 NOT NULL,
  QUANTITY_3 FLOAT64 NOT NULL,
  QUANTITY_4 FLOAT64 NOT NULL,
);
INSERT INTO
  `tutorial`.MULTI_COLUMN_QUANTITY
VALUES
  ('product_1', 10, 20, 15, 25, 10),
  ('product_2', 50, 65, 55, 30, 80);

CREATE TABLE IF NOT EXISTS `tutorial`.`MULTI_COLUMN_QUANTITY` (
  `PRODUCT` STRING NOT NULL,
  `QUANTITY_0` DOUBLE NOT NULL,
  `QUANTITY_1` DOUBLE NOT NULL,
  `QUANTITY_2` DOUBLE NOT NULL,
  `QUANTITY_3` DOUBLE NOT NULL,
  `QUANTITY_4` DOUBLE NOT NULL);
INSERT INTO `tutorial`.`MULTI_COLUMN_QUANTITY` VALUES
  ('product_1', 10, 20, 15, 25, 10),
  ('product_2', 50, 65, 55, 30, 80);

if not exists (select * from sys.tables where name = 'MULTI_COLUMN_QUANTITY')
CREATE TABLE TUTORIAL.MULTI_COLUMN_QUANTITY (
  [PRODUCT] VARCHAR(50) NOT NULL,
  [QUANTITY_0] FLOAT NOT NULL,
  [QUANTITY_1] FLOAT NOT NULL,
  [QUANTITY_2] FLOAT NOT NULL,
  [QUANTITY_3] FLOAT NOT NULL,
  [QUANTITY_4] FLOAT NOT NULL
  );
INSERT INTO
  TUTORIAL.MULTI_COLUMN_QUANTITY
VALUES
  ('product_1', 10, 20, 15, 25, 10),
  ('product_2', 50, 65, 55, 30, 80);

SET enable_case_sensitive_identifier TO true;
CREATE TABLE dev.tutorial."MULTI_COLUMN_QUANTITY" (
  "PRODUCT" VARCHAR NOT NULL,
  "QUANTITY_0" FLOAT8 NOT NULL,
  "QUANTITY_1" FLOAT8 NOT NULL,
  "QUANTITY_2" FLOAT8 NOT NULL,
  "QUANTITY_3" FLOAT8 NOT NULL,
  "QUANTITY_4" FLOAT8 NOT NULL);

INSERT INTO dev.tutorial."MULTI_COLUMN_QUANTITY" VALUES
  ('product_1',10,20,15,25,10),
  ('product_2',50,65,55,30,80);

CREATE TRANSIENT TABLE IF NOT EXISTS MULTI_COLUMN_QUANTITY (
  PRODUCT STRING NOT NULL,
  QUANTITY_0 FLOAT NOT NULL,
  QUANTITY_1 FLOAT NOT NULL,
  QUANTITY_2 FLOAT NOT NULL,
  QUANTITY_3 FLOAT NOT NULL,
  QUANTITY_4 FLOAT NOT NULL);

INSERT INTO MULTI_COLUMN_QUANTITY SELECT 'product_1',10,20,15,25,10;
INSERT INTO MULTI_COLUMN_QUANTITY SELECT 'product_2',50,65,55,30,80;

Load multi_column_quantity.csv into a Table:

IF NOT EXISTS (SELECT * from sys.external_tables WHERE name='MULTI_COLUMN_QUANTITY')
  CREATE EXTERNAL TABLE test_resources.TUTORIAL.MULTI_COLUMN_QUANTITY (
    [PRODUCT] VARCHAR(50),
    [QUANTITY_0] FLOAT,
    [QUANTITY_1] FLOAT,
    [QUANTITY_2] FLOAT,
    [QUANTITY_3] FLOAT,
    [QUANTITY_4] FLOAT
  )
  WITH (
    LOCATION = 'your-container/path/to/multi_column_quantity.csv',
    DATA_SOURCE = [YourDataSource],
    FILE_FORMAT = [CsvFormatWithHeader]
  )
GO;

The table looks like this in the database:

PRODUCT	QUANTITY_0	QUANTITY_1	QUANTITY_2	QUANTITY_3	QUANTITY_4
product_1	10	20	15	25	10
product_2	50	65	55	30	80

When a table has at least 2 columns with names following the pattern prefix + delimiter + number, all these columns can be converted into a single vector column (numbers must be consecutive from 0 (or 1) to N). You simply need to create a Table on this and call vectorize on it to transform this table with one column per vector element to a table with vectors. The vectorize method accepts either a list of prefixes or a delimiter to convert columns into a single vector.

final TableDescription tableWithoutVector =
    discoverer.discoverTable(new SqlTableId(PROJECT_ID, "tutorial", "MULTI_COLUMN_QUANTITY"));
// Manually vectorize the table after discovery with a list of prefixes
final TableDescription tableWithVector =
    Vectorization.vectorizeMultiColumn(tableWithoutVector, List.of("QUANTITY"));

final TableDescription tableWithoutVector =
    discoverer.discoverTable(new SqlTableId(PROJECT_ID, "tutorial", "MULTI_COLUMN_QUANTITY"));
// Manually vectorize the table after discovery with a delimiter
final TableDescription tableWithVector =
    Vectorization.vectorizeMultiColumn(tableWithoutVector, "_");

final TableDescription tableWithoutVector =
    discoverer.discoverTable(
        new SqlTableId(CATALOG_NAME, TUTORIAL_SCHEMA_NAME, "MULTI_COLUMN_QUANTITY"));
// Manually vectorize the table after discovery with a list of prefixes
final TableDescription tableWithVector =
    Vectorization.vectorizeMultiColumn(tableWithoutVector, List.of("QUANTITY"));

final TableDescription tableWithoutVector =
    discoverer.discoverTable(
        new SqlTableId(CATALOG_NAME, TUTORIAL_SCHEMA_NAME, "MULTI_COLUMN_QUANTITY"));
// Manually vectorize the table after discovery with a delimiter
final TableDescription tableWithVector =
    Vectorization.vectorizeMultiColumn(tableWithoutVector, "_");

final TableDescription tableWithoutVector =
    discoverer.discoverTable(
        new SqlTableId(DATABASE_NAME, SCHEMA_NAME, MULTI_COLUMN_TABLE_NAME));
// Manually vectorize the table after discovery with a list of prefixes
final TableDescription tableWithVector =
    Vectorization.vectorizeMultiColumn(tableWithoutVector, List.of("QUANTITY"));

final TableDescription tableWithoutVector =
    discoverer.discoverTable(
        new SqlTableId(DATABASE_NAME, SCHEMA_NAME, MULTI_COLUMN_TABLE_NAME));
// Manually vectorize the table after discovery with a delimiter
final TableDescription tableWithVector =
    Vectorization.vectorizeMultiColumn(tableWithoutVector, "_");

final TableDescription tableWithoutVector =
    discoverer.discoverTable(
        new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "MULTI_COLUMN_QUANTITY"));
// Manually vectorize the table after discovery with a list of prefixes
final TableDescription tableWithVector =
    Vectorization.vectorizeMultiColumn(tableWithoutVector, List.of("QUANTITY"));

final TableDescription tableWithoutVector =
    discoverer.discoverTable(
        new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "MULTI_COLUMN_QUANTITY"));
// Manually vectorize the table after discovery with a delimiter
final TableDescription tableWithVector =
    Vectorization.vectorizeMultiColumn(tableWithoutVector, "_");

final TableDescription tableWithoutVector =
    discoverer.discoverTable(
        new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "MULTI_COLUMN_QUANTITY"));
// Manually vectorize the table after discovery with a list of prefixes
final TableDescription tableWithVector =
    Vectorization.vectorizeMultiColumn(tableWithoutVector, List.of("QUANTITY"));

final TableDescription tableWithoutVector =
    discoverer.discoverTable(
        new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "MULTI_COLUMN_QUANTITY"));
// Manually vectorize the table after discovery with a delimiter
final TableDescription tableWithVector =
    Vectorization.vectorizeMultiColumn(tableWithoutVector, "_");

final TableDescription tableWithoutVector =
    discoverer.discoverTable(
        new SqlTableId(DATABASE_NAME, "TUTORIAL", "MULTI_COLUMN_QUANTITY"));
// Manually vectorize the table after discovery with a list of prefixes
final TableDescription tableWithVector =
    Vectorization.vectorizeMultiColumn(tableWithoutVector, List.of("QUANTITY"));

final TableDescription tableWithoutVector =
    discoverer.discoverTable(
        new SqlTableId(DATABASE_NAME, "TUTORIAL", "MULTI_COLUMN_QUANTITY"));
// Manually vectorize the table after discovery with a delimiter
final TableDescription tableWithVector =
    Vectorization.vectorizeMultiColumn(tableWithoutVector, "_");

In this example, both code snippets produce the same result.

PRODUCT	QUANTITY
product_1	[10,20,15,25,10]
product_2	[50,65,55,30,80]

It is also possible to have this conversion done automatically by the discoverer. It can be enabled by setting the Discover setting useAutoVectorizer to true. With this setting enabled, the auto-vectorizer will detect columns sharing the same prefix and will convert them into a vector. The behavior of the auto-vectorization can be controlled by two more settings:

autoVectorizerDelimiter: sets the delimiter as defined above.
There is no delimiter by default.
minThresholdForAutoVectorizer: sets minimum number of columns with the same prefix to trigger the auto-vectorization.
The default value is 50.

With the settings set to enable the autovectorizer, there is nothing more to do after the discovery.

final DiscovererSettings settings =
    DiscovererSettings.builder()
        .useAutoVectorizer(true)
        .autoVectorizerDelimiter("_")
        .minThresholdForAutoVectorizer(5)
        .build();
final IDirectQueryTableDiscoverer discoverer = connector.getDiscoverer(settings);

final TableDescription table =
    discoverer.discoverTable(new SqlTableId(PROJECT_ID, "tutorial", "MULTI_COLUMN_QUANTITY"));

final IDirectQueryTableDiscoverer discoverer =
    connector.getDiscoverer(
        DiscovererSettings.builder()
            .useAutoVectorizer(true)
            .autoVectorizerDelimiter("_")
            .minThresholdForAutoVectorizer(5)
            .build());

final TableDescription table =
    discoverer.discoverTable(
        new SqlTableId(CATALOG_NAME, TUTORIAL_SCHEMA_NAME, "MULTI_COLUMN_QUANTITY"));

final IDirectQueryTableDiscoverer discoverer =
    connector.getDiscoverer(
        DiscovererSettings.builder()
            .useAutoVectorizer(true)
            .autoVectorizerDelimiter("_")
            .minThresholdForAutoVectorizer(5)
            .build());

final TableDescription table =
    discoverer.discoverTable(
        new SqlTableId(DATABASE_NAME, SCHEMA_NAME, MULTI_COLUMN_TABLE_NAME));

final IDirectQueryTableDiscoverer discoverer =
    connector.getDiscoverer(
        DiscovererSettings.builder()
            .useAutoVectorizer(true)
            .autoVectorizerDelimiter("_")
            .minThresholdForAutoVectorizer(5)
            .build());

final TableDescription table =
    discoverer.discoverTable(
        new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "MULTI_COLUMN_QUANTITY"));

final IDirectQueryTableDiscoverer discoverer =
    connector.getDiscoverer(
        DiscovererSettings.builder()
            .useAutoVectorizer(true)
            .autoVectorizerDelimiter("_")
            .minThresholdForAutoVectorizer(5)
            .build());

final TableDescription table =
    discoverer.discoverTable(
        new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "MULTI_COLUMN_QUANTITY"));

final IDirectQueryTableDiscoverer discoverer =
    connector.getDiscoverer(
        DiscovererSettings.builder()
            .useAutoVectorizer(true)
            .autoVectorizerDelimiter("_")
            .minThresholdForAutoVectorizer(5)
            .build());

final TableDescription table =
    discoverer.discoverTable(
        new SqlTableId(DATABASE_NAME, "TUTORIAL", "MULTI_COLUMN_QUANTITY"));

Table with one index and value per row

In this example we will use the following table:

PRODUCT	INDEX	QUANTITY
product_1	0	10
product_1	1	20
product_1	2	15
product_1	3	25
product_1	4	10
product_2	0	50
product_2	1	65
product_2	2	55
product_2	3	30
product_2	4	80

This table represents 2 vectors split over multiple lines. Let’s create the table and feed it.

CREATE TABLE IF NOT EXISTS `tutorial`.MULTI_ROW_QUANTITY (
  PRODUCT STRING NOT NULL,
  INDEX INTEGER NOT NULL,
  QUANTITY FLOAT64 NOT NULL
);
INSERT INTO
  `tutorial`.MULTI_ROW_QUANTITY
VALUES
  ('product_1', 0, 10),
  ('product_1', 1, 20),
  ('product_1', 2, 15),
  ('product_1', 3, 25),
  ('product_1', 4, 10),
  ('product_2', 0, 50),
  ('product_2', 1, 65),
  ('product_2', 2, 55),
  ('product_2', 3, 30),
  ('product_2', 4, 80);

CREATE TABLE IF NOT EXISTS
  `tutorial`.`MULTI_ROW_QUANTITY`
(
  `PRODUCT` STRING NOT NULL,
  `INDEX` INTEGER NOT NULL,
  `QUANTITY` DOUBLE NOT NULL
);
INSERT INTO `tutorial`.`MULTI_ROW_QUANTITY` VALUES
  ('product_1',0,10),
  ('product_1',1,20),
  ('product_1',2,15),
  ('product_1',3,25),
  ('product_1',4,10),
  ('product_2',0,50),
  ('product_2',1,65),
  ('product_2',2,55),
  ('product_2',3,30),
  ('product_2',4,80);

if not exists (select * from sys.tables where name = 'MULTI_ROW_QUANTITY')
CREATE TABLE TUTORIAL.MULTI_ROW_QUANTITY (
  [PRODUCT] VARCHAR(50) NOT NULL,
  [INDEX] INTEGER NOT NULL,
  [QUANTITY] FLOAT NOT NULL
  );
INSERT INTO
  TUTORIAL.MULTI_ROW_QUANTITY
VALUES
  ('product_1', 0, 10),
  ('product_1', 1, 20),
  ('product_1', 2, 15),
  ('product_1', 3, 25),
  ('product_1', 4, 10),
  ('product_2', 0, 50),
  ('product_2', 1, 65),
  ('product_2', 2, 55),
  ('product_2', 3, 30),
  ('product_2', 4, 80)
  ;

SET enable_case_sensitive_identifier TO true;
CREATE TABLE dev.tutorial."MULTI_ROW_QUANTITY" (
  "PRODUCT" VARCHAR NOT NULL,
  "INDEX" INT8 NOT NULL,
  "QUANTITY" FLOAT8 NOT NULL);

INSERT INTO dev.tutorial."MULTI_ROW_QUANTITY" VALUES
  ('product_1',0,10),
  ('product_1',1,20),
  ('product_1',2,15),
  ('product_1',3,25),
  ('product_1',4,10),
  ('product_2',0,50),
  ('product_2',1,65),
  ('product_2',2,55),
  ('product_2',3,30),
  ('product_2',4,80);

CREATE TRANSIENT TABLE IF NOT EXISTS MULTI_ROW_QUANTITY ( PRODUCT STRING NOT NULL, INDEX INTEGER NOT NULL, QUANTITY FLOAT NOT NULL);

INSERT INTO MULTI_ROW_QUANTITY SELECT 'product_1',0,10;
INSERT INTO MULTI_ROW_QUANTITY SELECT 'product_1',1,20;
INSERT INTO MULTI_ROW_QUANTITY SELECT 'product_1',2,15;
INSERT INTO MULTI_ROW_QUANTITY SELECT 'product_1',3,25;
INSERT INTO MULTI_ROW_QUANTITY SELECT 'product_1',4,10;
INSERT INTO MULTI_ROW_QUANTITY SELECT 'product_2',0,50;
INSERT INTO MULTI_ROW_QUANTITY SELECT 'product_2',1,65;
INSERT INTO MULTI_ROW_QUANTITY SELECT 'product_2',2,55;
INSERT INTO MULTI_ROW_QUANTITY SELECT 'product_2',3,30;
INSERT INTO MULTI_ROW_QUANTITY SELECT 'product_2',4,80;

IF NOT EXISTS (SELECT * from sys.external_tables WHERE name='MULTI_ROW_QUANTITY')
  CREATE EXTERNAL TABLE test_resources.TUTORIAL.MULTI_ROW_QUANTITY (
    [PRODUCT] VARCHAR(50),
    [INDEX] INTEGER,
    [QUANTITY] FLOAT
  )
  WITH (
    LOCATION = 'multi_row_quantity.csv',
    DATA_SOURCE = DirectQueryTutorial,
    FILE_FORMAT = CsvFormatWithHeader
  )
GO;

You simply need to create a Table on this and call vectorize on it to transform this table with row by row values to a table with vectors.

final TableDescription multiRowTable =
    discoverer.discoverTable(new SqlTableId(PROJECT_ID, "tutorial", "MULTI_ROW_QUANTITY"));
final MultiRowVectorTableDescription tableWithVector =
    Vectorization.vectorizeMultiRow(multiRowTable, "INDEX", List.of("QUANTITY"));

final TableDescription multiRowTable =
    discoverer.discoverTable(
        new SqlTableId(CATALOG_NAME, TUTORIAL_SCHEMA_NAME, "MULTI_ROW_QUANTITY"));
final MultiRowVectorTableDescription tableWithVector =
    Vectorization.vectorizeMultiRow(multiRowTable, "INDEX", List.of("QUANTITY"));

final TableDescription multiRowTable =
    discoverer.discoverTable(new SqlTableId(DATABASE_NAME, SCHEMA_NAME, MULTI_ROW_TABLE_NAME));
final MultiRowVectorTableDescription tableWithVector =
    Vectorization.vectorizeMultiRow(multiRowTable, "INDEX", List.of("QUANTITY"));

final TableDescription multiRowTable =
    discoverer.discoverTable(
        new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "MULTI_ROW_QUANTITY"));
final MultiRowVectorTableDescription tableWithVector =
    Vectorization.vectorizeMultiRow(multiRowTable, "INDEX", List.of("QUANTITY"));

final TableDescription multiRowTable =
    discoverer.discoverTable(
        new SqlTableId(TEST_DATABASE_NAME, TUTORIAL_SCHEMA_NAME, "MULTI_ROW_QUANTITY"));
final MultiRowVectorTableDescription tableWithVector =
    Vectorization.vectorizeMultiRow(multiRowTable, "INDEX", List.of("QUANTITY"));

final TableDescription multiRowTable =
    discoverer.discoverTable(new SqlTableId(DATABASE_NAME, "TUTORIAL", "MULTI_ROW_QUANTITY"));
final MultiRowVectorTableDescription tableWithVector =
    Vectorization.vectorizeMultiRow(multiRowTable, "INDEX", List.of("QUANTITY"));

You now have a table with a Vector column which will be equivalent to the following table:

PRODUCT	QUANTITY
product_1	[10,20,15,25,10]
product_2	[50,65,55,30,80]

Releases and upgrades

Getting started

Concepts

Step 1: Bring your data into Atoti

Step 2: Design your Cube

Step 3: Build an Atoti application

Step 4: Query an Atoti application

Configure Atoti

Secure Atoti

Operate Atoti

Reference

How to use vectors

Table with native vectors

Table with one column per vector element

Table with one index and value per row

​Table with native vectors

​Table with one column per vector element

​Table with one index and value per row

Table with native vectors

Table with one column per vector element

Table with one index and value per row