Skip to main content
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.
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"));

​
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);
The table looks like this in the database:
PRODUCTQUANTITY_0QUANTITY_1QUANTITY_2QUANTITY_3QUANTITY_4
product_11020152510
product_25065553080
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, "_");
In this example, both code snippets produce the same result.
PRODUCTQUANTITY
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"));

​
Table with one index and value per row

In this example we will use the following table:
PRODUCTINDEXQUANTITY
product_1010
product_1120
product_1215
product_1325
product_1410
product_2050
product_2165
product_2255
product_2330
product_2480
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);
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"));
You now have a table with a Vector column which will be equivalent to the following table:
PRODUCTQUANTITY
product_1[10,20,15,25,10]
product_2[50,65,55,30,80]