> ## Documentation Index
> Fetch the complete documentation index at: https://docs.kinetica.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Vector Search

> Copy-paste examples of using & searching vectors in SQL & Python

## Creating a Table

A `vector` column can optionally be configured to normalize the vector data
inserted into it, giving each vector a magnitude (L2 norm) of *1*.  This can
improve the performance of some vector operations with minimal overhead.

### Vector Column without Normalization

<CodeGroup>
  ```sql SQL theme={null}
  CREATE OR REPLACE TABLE example.vs
  (
  	name VARCHAR(16) NOT NULL,
  	embedding VECTOR(3) NOT NULL
  )
  ```

  ```python Python (native API) theme={null}
  table_name = 'example.vs'
  columns = [
      ['name', 'string', 'char16'],
      ['embedding', 'bytes', 'vector(3)']
  ]

  gpudb.GPUdbTable(columns, table_name, db = kinetica)
  ```

  ```python Python (from DataFrame) theme={null}
  table_name = 'example.vs'
  vs = gpudb.GPUdbTable.from_df(df, kinetica, table_name, clear_table = True)
  ```
</CodeGroup>

### Vector Column with Normalization

<CodeGroup>
  ```sql SQL theme={null}
  CREATE OR REPLACE TABLE example.vs_norm
  (
  	name VARCHAR(16) NOT NULL,
  	embedding VECTOR(3, NORMALIZE) NOT NULL
  )
  ```

  ```python Python (native API) theme={null}
  table_name = 'example.vs_norm'
  columns = [
      ['name', 'string', 'char16'],
      ['embedding', 'bytes', 'vector(3)', 'normalize']
  ]

  vs = gpudb.GPUdbTable(columns, table_name, db = kinetica)
  ```

  ```python Python (from DataFrame) theme={null}
  table_name = 'example.vs_norm'

  vs = gpudb.GPUdbTable.from_df(
          df, kinetica, table_name, clear_table = True,
          column_types = { 'embedding': [ 'vector(3)', 'normalize' ] }
  )
  ```
</CodeGroup>

## Inserting Vector Data

<CodeGroup>
  ```sql SQL theme={null}
  INSERT INTO example.vs
  VALUES
  	('fun','[-0.23764,0.43119,-0.72154]'),
  	('play','[-0.73571,0.19937,-0.89408]'),
  	('food','[0.47222,-0.44545,-0.51833]'),
  	('money','[0.59784,-0.057026,0.97746]'),
  	('work','[0.51359,0.19695,-0.51944]')
  ```

  ```python Python (as strings) theme={null}
  embs = [
      ['fun', '[-0.23764,0.43119,-0.72154]'],
      ['play', '[-0.73571,0.19937,-0.89408]'],
      ['food', '[0.47222,-0.44545,-0.51833]'],
      ['money', '[0.59784,-0.057026,0.97746]'],
      ['work', '[0.51359,0.19695,-0.51944]']
  ]

  vs = gpudb.GPUdbTable(name = 'example.vs', db = kinetica)

  vs.insert_records(embs)
  ```

  ```python Python (as native types) theme={null}
  embs = [
      ['fun', [-0.23764,0.43119,-0.72154]],
      ['play', [-0.73571,0.19937,-0.89408]],
      ['food', [0.47222,-0.44545,-0.51833]],
      ['money', [0.59784,-0.057026,0.97746]],
      ['work', [0.51359,0.19695,-0.51944]]
  ]

  vs = gpudb.GPUdbTable(name = 'example.vs', db = kinetica)

  vs.insert_records(embs)
  ```

  ```python Python (as DataFrame) theme={null}
  vs = gpudb.GPUdbTable(name = 'example.vs', db = kinetica)

  vs.insert_df(df, batch_size = 10000)
  ```
</CodeGroup>

## Retrieving Vector Data

<CodeGroup>
  ```sql SQL theme={null}
  SELECT name, embedding
  FROM example.vs
  ```

  ```python Python (w/o native type conversions) theme={null}
  vs = gpudb.GPUdbTable(name = 'example.vs', db = kinetica)

  records = vs.get_records()

  dim = 3

  print('%-16s %s' % ('Name', 'Embedding'))
  for record in records:
      print('%-16s %s' % (record[0], list(struct.unpack('%df'%dim, record[1]))))
  ```

  ```python Python (w/ native type conversions) theme={null}
  vs = gpudb.GPUdbTable(name = 'example.vs', db = kinetica, convert_special_types_on_retrieval = True)

  records = vs.get_records(encoding = 'json')

  print('%-16s %s' % ('Name', 'Embedding'))
  for record in records:
      print('%-16s %s' % (record["name"], record["embedding"]))
  ```

  ```python Python (as DataFrame) theme={null}
  vs = gpudb.GPUdbTable(name = 'example.vs', db = kinetica)

  df = vs.to_df()

  print(df)
  ```
</CodeGroup>

## Indexing Vector Data

### CAGRA Vector Index

<CodeGroup>
  ```sql SQL theme={null}
  ALTER TABLE example.employee
  ADD CAGRA INDEX (profile)
  ```

  ```python Python theme={null}
  retobj = kinetica.alter_table(
      table_name = "example.employee",
      action = "create_index",
      value = "profile",
      options = {
              "index_type": "cagra"
      }
  )
  ```
</CodeGroup>

### HNSW Vector Index

<CodeGroup>
  ```sql SQL theme={null}
  ALTER TABLE example.employee
  ADD HNSW INDEX (profile)
  ```

  ```python Python theme={null}
  retobj = kinetica.alter_table(
      table_name = "example.employee",
      action = "create_index",
      value = "profile",
      options = {
              "index_type": "hnsw"
      }
  )
  ```
</CodeGroup>

## Searching Vector Data

### Vector Search by Operator

<CodeGroup>
  ```sql SQL Literal theme={null}
  SELECT TOP 5 name, embedding <-> VECTOR('[-0.23764,0.43119,-0.72154]', 3) as distance
  FROM example.vs
  ORDER BY distance
  ```

  ```sql SQL Lookup theme={null}
  SELECT TOP 5 name, embedding <-> search_embedding as distance
  FROM
  	example.vs,
  	(SELECT embedding AS search_embedding FROM example.vs WHERE name = 'fun')
  WHERE name <> 'fun'
  ORDER BY distance
  ```

  ```python Python Literal theme={null}
  sql = """
      SELECT TOP 5 name, embedding <-> VECTOR('[-0.23764,0.43119,-0.72154]', 3) as distance
      FROM example.vs
      ORDER BY distance
  """

  with gpudb.GPUdbSqlIterator(kinetica, sql) as records:

      print('%-16s %s' % ('Name', 'Distance'))
      for record in records:
          print('%-16s %f' % (record[0], record[1]))
  ```

  ```python Python Lookup w/ SQL theme={null}
  sql = """
      SELECT TOP 5 name, embedding <-> search_embedding as distance
      FROM
          example.vs,
          (SELECT embedding AS search_embedding FROM example.vs WHERE name = 'fun')
      WHERE name <> 'fun'
      ORDER BY distance
  """

  with gpudb.GPUdbSqlIterator(kinetica, sql) as records:

      print('%-16s %s' % ('Name', 'Distance'))
      for record in records:
          print('%-16s %f' % (record[0], record[1]))
  ```

  ```python Python Literal to DataFrame theme={null}
  sql = """
      SELECT TOP 5 name, embedding <-> VECTOR('[-0.23764,0.43119,-0.72154]', 3) as distance
      FROM example.vs
      ORDER BY distance
  """

  df = kinetica.to_df(sql)

  print(df)
  ```

  ```python Python Lookup w/ SQL to DataFrame theme={null}
  sql = """
      SELECT TOP 5 name, embedding <-> search_embedding as distance
      FROM
          example.vs,
          (SELECT embedding AS search_embedding FROM example.vs WHERE name = 'fun')
      WHERE name <> 'fun'
      ORDER BY distance
  """

  df = kinetica.to_df(sql)

  print(df)
  ```
</CodeGroup>

### Vector Search by Function

<CodeGroup>
  ```sql SQL Literal theme={null}
  SELECT TOP 5 name, L2_DISTANCE(embedding,'[-0.23764,0.43119,-0.72154]') as distance
  FROM example.vs
  ORDER BY distance
  ```

  ```sql SQL Lookup theme={null}
  SELECT TOP 5 name, L2_DISTANCE(embedding, search_embedding) as distance
  FROM
  	example.vs,
  	(SELECT embedding AS search_embedding FROM example.vs WHERE name = 'fun')
  WHERE name <> 'fun'
  ORDER BY distance
  ```

  ```python Python Literal theme={null}
  vs = gpudb.GPUdbTable(name = 'example.vs', db = kinetica)

  records = vs.get_records_by_column(
          ["name", "L2_DISTANCE(embedding,'[-0.23764,0.43119,-0.72154]') as distance"],
          limit = 5,
          options = {"sort_by": "distance"}
  )

  print('%-16s %s' % ('Name', 'Distance'))
  for record in zip(records['name'], records['distance']):
      print('%-16s %f' % (record[0], record[1]))
  ```

  ```python Python Lookup w/ SQL theme={null}
  sql = """
      SELECT TOP 5 name, L2_DISTANCE(embedding, search_embedding) as distance
      FROM
          example.vs,
          (SELECT embedding AS search_embedding FROM example.vs WHERE name = 'fun')
      WHERE name <> 'fun'
      ORDER BY distance
  """

  with gpudb.GPUdbSqlIterator(kinetica, sql) as records:

      print('%-16s %s' % ('Name', 'Distance'))
      for record in records:
          print('%-16s %f' % (record[0], record[1]))
  ```

  ```python Python Literal to DataFrame theme={null}
  sql = """
      SELECT TOP 5 name, L2_DISTANCE(embedding,'[-0.23764,0.43119,-0.72154]') as distance
      FROM example.vs
      ORDER BY distance
  """

  df = kinetica.to_df(sql)

  print(df)
  ```

  ```python Python Lookup w/ SQL to DataFrame theme={null}
  sql = """
      SELECT TOP 5 name, L2_DISTANCE(embedding, search_embedding) as distance
      FROM
          example.vs,
          (SELECT embedding AS search_embedding FROM example.vs WHERE name = 'fun')
      WHERE name <> 'fun'
      ORDER BY distance
  """

  df = kinetica.to_df(sql)

  print(df)
  ```
</CodeGroup>