Vector

Introduced or updated: v1.2.777

The VECTOR data type stores multi-dimensional arrays of 32-bit floating-point numbers, designed for machine learning, AI applications, and similarity search operations. Each vector has a fixed dimension (length) specified at creation time.

Syntax

column_name VECTOR(<dimension>)

Where:

• dimension: The dimension (length) of the vector. Must be a positive integer with a maximum value of 4096.
• Elements are 32-bit floating-point numbers.

Vector Indexing

Databend supports creating vector indexes using the HNSW (Hierarchical Navigable Small World) algorithm for fast approximate nearest neighbor search, delivering 23x faster query performance.

note

To enhance query performance and reduce index file size, HNSW indexes perform quantization on raw vector data. Compared to using unquantized raw vectors, this quantization process may introduce minor errors (typically negligible) when calculating distance values. While this causes slight deviations from the exact true distance, the error is usually sufficiently small to not significantly impact the quality or relevance of search results. This trade-off is a common practice in the field of vector indexes to achieve high performance and efficient storage.

Index Syntax

VECTOR INDEX index_name(column_name) distance='cosine,l1,l2'

Where:

• index_name: Name of the vector index
• column_name: Name of the VECTOR column to index
• distance: Distance functions to support. Can be 'cosine', 'l1', 'l2', or combinations like 'cosine,l1,l2'

Supported Distance Functions

Function	Description	Use Case
cosine_distance	Calculates cosine distance between vectors	Semantic similarity, text embeddings
l1_distance	Calculates L1 distance (Manhattan distance)	Feature comparison, sparse data
l2_distance	Calculates L2 distance (Euclidean distance)	Geometric similarity, image features

Basic Usage

Step 1: Create Table with Vector

-- Create table with vector index for efficient similarity search
CREATE OR REPLACE TABLE simple_wiki (
    content VARCHAR,
    embedding VECTOR(1024),
    VECTOR INDEX idx_embedding(embedding) distance='cosine'
);

Note: The vector index is automatically built when data is inserted into the table.

Step 2: Insert Vector Data

-- Copy simple wiki embedding vectors
COPY INTO simple_wiki
FROM 'https://wizardbend.s3.us-east-2.amazonaws.com/databend-doc/dataset/wiki/simple_wiki.csv'
FILE_FORMAT = (
    TYPE = 'CSV',
    FIELD_DELIMITER = '|',
    RECORD_DELIMITER = '\n',
    SKIP_HEADER = 0
);

Step 3: Perform Similarity Search

-- Find simple wiki similar to a query vector
SELECT 
    content,
    cosine_distance(embedding, [-0.039933037,-0.021560734,0.024440102,0.002521759,-0.010272103,-0.0014329843,0.0027769457,0.007870142,-0.046696488,0.008686037,-0.01836951,-0.060036782,0.028973266,0.019946467,-0.0018736516,-0.034127645,-0.029007245,-0.038226556,-0.049822643,-0.022114972,0.044502974,-0.024243174,-0.0054523097,-0.0049053733,0.037541658,0.005481121,0.0020597691,0.019144949,0.037816584,0.060224015,0.02804823,-0.057770427,0.012200337,-0.0456118,-0.0158223,0.010360265,0.006539595,-0.052356012,0.008947708,-0.0233614,0.034112025,-0.006252221,-0.015725758,-0.044914573,-0.023845563,0.00955626,-0.031100346,-0.009765235,-0.029881598,-0.03866581,0.029224394,-0.014723449,-0.008826784,-0.026858984,0.05309897,-0.01766305,0.04784403,-0.02885405,-0.017036816,0.011578803,0.026849564,-0.021650068,0.02662856,0.015286329,0.03861724,0.024659181,-0.010742011,0.026483327,0.02818955,-0.00084676413,0.02860798,-0.030394774,0.017738463,-0.039278828,0.031396013,-0.012227515,-0.015788797,0.039025042,-0.04956346,0.032142702,0.0053956425,-0.009891509,0.024410622,-0.010725118,0.04047523,-0.014926191,0.033690933,0.060940236,0.027889939,-0.020630544,-0.00084394595,-0.004017284,-0.0124509,0.028217629,0.024844026,-0.010981587,0.019735651,0.036471285,-0.010195774,0.016049545,0.023868993,0.02659397,-0.056206383,0.058364574,-0.014834511,0.0030844177,-0.0063245185,-0.02207232,0.006551744,-0.052488152,-0.017905239,-0.015412277,0.022892827,0.016487084,-0.029899051,-0.012708291,-0.035586096,0.02596589,0.0029379118,0.038380604,-0.007877647,0.03518495,-0.002010242,0.010625737,-0.005567636,0.034396384,-0.0014768809,0.031846795,-0.052709747,-0.010257822,0.0061065704,-0.010274223,-0.011405558,-0.016665878,-0.004968878,0.07772871,0.011310182,0.0219365,-0.033417672,-0.04040535,0.052248668,0.014371049,0.041794937,-0.0033441822,-0.019891461,-0.0053792787,0.0023917176,0.0025531051,-0.05433755,0.014126035,-0.012321753,-0.005712273,0.05721316,0.0020843758,0.013004933,-0.019866217,-0.004618878,0.008496106,0.004900125,0.0006457499,-0.041960247,-0.0075987596,0.010396549,0.032008134,-0.002487335,0.015960924,0.08054704,-0.015961738,-0.015006569,0.0034933898,-0.0012687037,0.04909167,-0.018214887,0.04728702,0.017690593,-0.013703448,0.041250657,0.00080612017,0.015576544,0.025256036,-0.026594903,-0.040572796,0.013373454,0.06724198,0.023664603,0.0113675045,0.014927227,-0.012118405,-0.056916624,-0.024240792,0.0033584728,-0.025588699,0.008981375,-0.0014575748,-0.015312046,-0.011540035,0.0064686704,0.015048653,-0.008708635,-0.014301029,-0.0024832096,0.023337826,0.0070429966,0.013144369,0.02041083,0.006277923,0.03897181,0.053174395,-0.016995518,-0.0004172065,0.0076942863,-0.003356909,0.007097678,-0.027790003,-0.003991756,-0.045612056,-0.045199793,0.042348653,-0.02947529,0.01554058,-0.016467432,-0.0026772153,0.0019185699,-0.023279784,-0.008291998,0.011236731,-0.03558683,0.06794362,0.026192881,-0.019645019,-0.039347414,-0.013998269,-0.01699295,0.07261961,-0.020888437,-0.048694804,0.02853197,0.030358918,0.031464137,0.017864136,-0.012815341,-0.017518146,0.023409694,0.02714983,0.012034626,0.023927186,-0.010070786,-0.059033453,0.014911582,0.005095107,-0.03787305,0.01246049,0.017725075,-0.021064913,-0.058367312,-0.003428461,0.020181729,0.012336753,-0.031503335,-0.004799914,-0.05646105,-0.01984429,0.058336996,0.0021011438,-0.008577994,0.01385879,0.024231413,0.029866416,-0.0068756547,-0.04194058,-0.01213994,0.0054214345,0.006465198,-0.010105255,-0.030127116,0.0013121354,0.011868225,0.0066204313,0.042110376,0.023357615,-0.01036481,0.008366333,-0.07369769,0.052336935,-0.005812693,0.03108532,0.02044665,-0.0054136915,-0.027532678,-0.02634361,0.0131402025,-0.011917434,-0.032253496,-0.024929227,0.068166055,-0.0055996566,-0.0024826033,-0.028492251,-0.016555956,-0.007588926,-0.016382232,-0.030875143,0.007845721,0.019659696,-0.009922441,-0.012108754,-0.034166787,-0.037560873,0.075659946,0.016890664,-0.06802754,0.01123077,0.042838987,-0.025188228,-0.027680581,0.044700317,0.0453996,0.04640234,0.01726848,-0.020473402,-0.010681011,-0.0031328944,0.003095883,0.020775313,-0.053987157,0.013772471,-0.07178051,-0.013557999,0.013618166,0.020771785,-0.08447924,-0.03225293,-0.030190913,0.024338268,-0.011804759,-0.061444994,-0.016974993,-0.008051971,0.03636079,-0.02141964,-0.024815733,-0.020394482,0.04416552,0.030860247,-0.041991297,-0.02579322,-0.03452515,0.018474394,0.008934209,-0.028817058,0.028967876,0.038335036,0.03724225,-0.01925004,0.0064753983,0.05628201,0.033970427,0.049811717,0.009217419,0.01841436,0.071094945,0.042066,-0.01651293,-0.03706452,-0.022843873,-0.003728514,-0.020151021,0.014640702,0.021030718,-0.08772823,-0.013650267,0.0051809773,0.009917115,0.011039957,-0.014342247,-0.025776912,0.054495063,0.036790144,0.022757804,-0.026672449,0.054716066,0.02900926,0.0059405244,0.030001359,0.008519217,0.0015233222,0.023925865,-0.0154374195,0.010654856,-0.017145056,0.011398358,-0.013738738,-0.029031495,-0.005606388,0.003882481,-0.027220976,0.044363245,0.013492364,0.032712296,0.017945819,0.015551315,0.0019034904,-0.00018370914,0.013732345,-0.035307534,0.035718374,0.0045968452,0.004034745,-0.03336048,-0.055343043,-0.019631283,0.004699886,0.013996215,0.029814066,-0.000154678,-0.027949505,-0.030347785,-0.028985916,-0.024995457,-0.017796125,-0.018648788,-0.004302015,0.017083483,0.031144693,0.041394103,-0.069151685,0.039736934,-0.009535066,0.04143036,0.04953277,0.0145748975,-0.007152605,-0.017511394,-0.04840339,0.027094962,0.026918083,0.057830565,-0.007880084,0.015743539,-0.0036878956,-0.0567741,-0.022091798,-0.010698142,-0.02245472,-0.01757799,0.037471205,-0.052648116,0.049900196,0.053788442,-0.038865305,0.0057106987,-0.02473526,-0.023820246,0.0045047947,0.012621999,-0.018678498,-0.01813632,-0.00012195826,0.07853716,0.008315526,-0.037119206,-0.020903006,0.041795038,0.0030253849,-0.017176986,0.031759594,-0.009211045,0.03144215,0.029955527,0.031503484,-0.031826288,-0.027378425,0.008766635,-0.03924142,0.03364401,0.010653025,-0.0022892836,-0.035691667,-0.035004787,0.009842149,0.010822758,0.012703574,0.008685773,-0.055794135,0.006090148,-0.03345982,0.0129244495,-0.030476095,-0.032982323,-0.035629928,-0.0490593,0.010204472,0.043598823,-0.012672735,-0.04832159,0.03594714,0.016997708,0.05538169,-0.0023494037,-0.029142532,-0.018542767,-0.051356334,-0.01117003,0.037861805,0.029137477,-0.023386285,-0.007015338,-0.026613887,-0.0028003643,0.025743905,-0.01245939,0.0036800557,0.007767204,0.016269377,-0.025229849,0.01201922,-0.023647215,-0.05849589,-0.07951695,0.024513802,-0.026385475,-0.045055453,-0.0077016805,0.06532769,0.0058879885,0.06656787,0.014269071,0.06382564,-0.0034135026,-0.024335757,-0.030015176,-0.017545601,-0.018646596,-0.108097345,-0.041027952,0.009003027,0.0012762699,0.008280786,-0.019606998,-0.0032755614,-0.027661273,-0.0063646864,0.002834647,-0.041689403,-0.016574679,0.030792264,0.017821124,0.05870724,-0.021904483,-0.0074986443,0.025092801,-0.020244813,0.02656664,-0.0059659267,-0.05309905,-0.075604044,-0.0010960719,0.033456154,0.019391162,-0.020096036,0.010127672,0.020431953,-0.014261546,0.019422999,-0.016376399,-0.022349846,-0.003644317,0.036822524,0.010911726,-0.04329986,0.07033871,-0.020313853,0.04484996,0.011330265,0.051181715,0.005774083,0.00947131,-0.028569514,-0.018492075,0.024055328,-0.016621815,0.02473131,0.03791043,0.016186867,0.024546374,0.022638643,0.04991907,0.04418161,0.006206315,-0.04311258,-0.013379863,-0.04812626,0.009906926,0.010288657,-0.003212413,-0.061669104,-0.024754656,-0.0017176361,0.03030152,-0.074314594,-0.03829444,0.012611487,0.0102494275,0.0045574442,0.0048285653,-0.047645736,-0.011894211,-0.021926671,0.04538916,0.01580292,0.042314507,0.0033757954,-0.034430943,0.010440009,0.025956932,0.028568642,0.0050932574,-0.043323226,0.02765941,-0.0037114113,0.009628618,0.056398317,0.00892722,-0.03103889,-0.016217224,-0.017720226,-0.0053833914,-0.031780284,0.0356075,0.0051690512,-0.04703213,-0.035289314,-0.01132067,0.03265562,0.046341296,0.014659944,-0.03623943,-0.02730759,-0.067547664,-0.0059156655,0.068728045,-0.06053017,0.033377975,-0.034934875,-0.02187493,-0.0012026525,0.03545188,0.007382351,-0.0031839162,0.018858379,0.018395567,0.011549551,-0.0011182075,0.0048722075,-0.050581027,-0.01635241,-0.017237768,0.011315683,-0.028737592,0.019962503,-0.007387627,0.050763622,-0.017169124,0.018606586,0.07163366,0.03734185,0.04736131,-0.06403581,-0.025738036,-0.021992266,-0.01319928,-0.08022283,-0.011005046,-0.03884887,0.006379696,-0.0014145619,-0.008193122,-0.006398966,0.0014773665,0.011084989,0.0044709737,-0.028264794,-0.025398243,0.0111127375,-0.054623317,-0.0068070847,-0.01406893,-0.003096321,0.033400264,-0.028150529,0.03965596,0.013693899,0.043754064,-0.00923469,-0.0072740354,0.04488809,-0.012617044,-0.035217658,-0.008500491,0.0034630534,0.031313743,0.04289801,-0.08151663,0.018859988,0.002810951,0.0005882988,0.0036537999,-0.009238664,0.04078265,-0.028712142,-0.011367192,-0.00848377,0.016381364,-0.021466913,0.04730318,-0.0038116602,0.0405969,-0.04121037,-0.021030288,0.018664027,-0.021762447,-0.011776265,-0.04551895,-0.013061067,-0.00058201264,-0.03639096,-0.021912543,0.020368917,0.027740637,0.04082436,-0.027459636,0.035794843,-0.057347205,0.02196459,-0.022118025,-0.047281083,-0.02563404,-0.06805774,-0.006911853,0.058185928,0.027218588,0.038427565,-0.0042237733,-0.03366896,-0.016229564,-0.029186584,-0.03176718,-0.070049964,0.0093576955,0.0032395318,-0.027114615,0.017352665,-0.013284534,-0.07080266,0.054075196,0.03762294,0.03469,0.044409204,-0.015622288,0.018894294,0.032763142,-0.016044721,-0.0058554937,-0.0087420745,0.03271165,-0.002017524,-0.022812437,0.028922994,0.038672272,0.02727705,0.008183337,0.0029323525,0.004047639,-0.018050514,-0.01703084,0.013070722,0.015186862,-0.02212554,0.04957366,-0.010246647,0.036387265,-0.017691536,0.040001247,0.0073721935,0.024487464,0.023548111,0.014214968,0.021323888,0.0039181644,0.035652403,-0.027979897,0.006655018,-0.014366088,-0.0136551345,0.013268369,0.054273065,0.031008286,0.032894958,-0.016528238,0.025737615,0.020457614,-0.024138859,0.021961076,0.009723186,-0.009472737,-0.0621166,0.006848464,-0.041066024,0.016326025,-0.002710949,0.016825637,0.009205761,0.020563355,0.027524417,0.029369347,-0.027464641,0.06149259,-0.021857154,0.040990386,0.0293085,-0.02500536,-0.0056850496,0.034397673,-0.00606149,-0.085631646,-0.018467443,0.056293353,-0.008269984,0.023202974,-0.0110984435,-0.04166591,-0.012643009,0.00012581052,0.015131127,0.037259698,-0.041649986,-0.06952646,-0.025137953,-0.05989198,-0.0039297743,0.0087265195,-0.0022604368,0.019198727,-0.00920593,0.002025578,-0.002004555,0.02481305,0.025386881,-0.018276915,0.002014735,-0.052969497,-0.0045547136,0.0031341368,-0.008278597,0.041760366,0.05500903,-0.016768616,0.05209667,-0.0033320175,0.0048231543,-0.0057336916,0.015312739,-0.027620234,-0.036065836,0.0051241387,-0.05891828,0.027523719,-0.0031922078,0.010368188,0.04026298,0.06313892,-0.029885303,-0.045074854,-0.023634521,0.028227627,-0.013199961,0.030160183,0.018712047,0.0043861,-0.018602764,-0.021192629,-0.029887471,-0.023583911,0.0038232335,-0.044232395,0.0453464,-0.027457736,0.02174932,-0.014318914,0.010380995,-0.0092946645,0.042118732,-0.05783332,-0.022720316,-0.004284339,0.037610166,-0.007278208,0.027652888,0.02568048,-0.023180764,0.014012257,0.02757744,-0.01648688,-0.00992361,0.031669445,0.0023115673,0.0059848484,-0.02707144,-0.012591426,-0.035704438,-0.036750987,0.00034554247,-0.018280635,-0.018120416,-0.0008562952,0.018715842,0.016269296,-0.010571697,0.021552436,-0.0027777094,-0.015317732,-0.024549993,-0.012991394,-0.03699913,0.04451807,0.015323537,-0.075593166,-0.0030781005,-0.055921625,0.19328497,0.0376175,0.023317544,0.0014488162,0.042639796,0.033370387,-0.017134832,-0.019266812,0.051158763,0.009435641,0.057999156,-0.0063074976,-0.00019830484,-0.01905141,0.0011231634,0.054753337,-0.006751183,0.038803753,0.00080272136,-0.036399104,0.000633084,0.031152539,0.0036446073,0.003571354,-0.014301032,0.03885238,0.030267972,0.05781101,-0.06889907,-0.04174844,0.023709996,-0.022390082,0.0039440948,0.01973794,-0.04242749,0.07741949,-0.047385674,0.00030847068,-0.023209738,0.01804641,-0.018974567,0.015027577,-0.009096456,-0.044668037,0.029737286,-0.020110145,-0.06669809,-0.08157275,0.04192294,-0.020739991,0.010827575,-0.0076488387,0.048057295,-0.016977197,-0.008187232,0.042178452,0.008117904,-0.045084924,0.028771017,-0.036470123,0.040879026,-0.031779155,-0.017179752,0.0033341474,0.018291174,-0.0010442613,0.061321683,-0.005425243,-0.06447074,-0.016938401,0.04149427,-0.076094925,0.042653468,-0.03496534,-0.03362807,0.0046461085,-0.013435743,-0.0010403148,-0.058112156,0.039158575,0.03977117,-0.029119678,0.013936549,-0.043197438,-0.010892551,-0.0072019505,-0.024094881,-0.0034056911,-0.013931113,0.027026255,0.016728511,0.04623942,-0.020803045,-0.024414537,-0.014496899]::VECTOR(1024)) AS distance
FROM simple_wiki
ORDER BY distance ASC
LIMIT 3;

Result:

╭────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╮
│                                                                           content                                                                           │   distance   │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼──────────────┤
│ Bernd FÃ rster ( born 3 May , 1956 ) is a former German football player .                                                                                   │ 0.0031585693 │
│ Nuneaton also has a football team called Nuneaton Town .                                                                                                    │    0.6006012 │
│ Dr Patrick John Hillery ( ; 2 May , 1923 -- April 12 , 2008 ) is an Irish Fianna FÃ il politician and the sixth President of Ireland from 1976 until 1990 . │    0.6014652 │
╰────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╯

Explanation: The query finds the 3 most similar simple wikis to the search vector. Lower cosine distance values indicate higher similarity.

Unloading and Loading Vector Data

Unloading Vector Data

-- Export vector data to stage
COPY INTO @mystage/unload/
FROM (
    SELECT 
        content,
        embedding
    FROM simple_wiki
)
FILE_FORMAT = (TYPE = 'PARQUET');

Loading Vector Data

-- Create target table for import
CREATE OR REPLACE TABLE simple_wiki_imported (
    content VARCHAR,
    embedding VECTOR(1024),
    VECTOR INDEX idx_embedding(embedding) distance='cosine'
);

-- Import vector data
COPY INTO simple_wiki_imported (content, embedding)
FROM (
    SELECT 
        content,
        embedding
    FROM @mystage/unload/
)
FILE_FORMAT = (TYPE = 'PARQUET');

Vector Functions

See Vector Functions.