This documentation page is also available as an interactive notebook. You can launch the notebook in
Kaggle or Colab, or download it for use with an IDE or local Jupyter installation, by clicking one of the
above links.
Create lookup functions that query tables by key—for customer records,
product catalogs, or financial data.
Problem
You have structured data—customer records, product catalogs, financial
data—and need to look up rows by key values. Common scenarios:
Solution
What’s in this recipe:
Create lookup functions from tables with retrieval_udf
Query by single or multiple keys
Use lookups in computed columns for data enrichment
Use pxt.retrieval_udf(table) to automatically create a function that
queries the table by its columns.
Setup
%pip install -qU pixeltable
import pixeltable as pxt# Create a fresh directorypxt.drop_dir('lookup_demo', force=True)pxt.create_dir('lookup_demo')
Connected to Pixeltable database at: postgresql+psycopg://postgres:@/pixeltable?host=/Users/pjlb/.pixeltable/pgdata
Created directory ‘lookup_demo’.
<pixeltable.catalog.dir.Dir at 0x143224e50>
Created table ‘products’.
Inserting rows into `products`: 5 rows [00:00, 502.31 rows/s]
Inserted 5 rows with 0 errors.
Create a lookup function with retrieval_udf
# Create a lookup function that searches by SKUget_product = pxt.retrieval_udf( products, name='get_product', description='Look up a product by its SKU code', parameters=['sku'], # Only use SKU as the lookup key limit=1, # Return at most 1 result)# Check the function signature
# Look up a product by SKUresult = products.select(get_product(sku='LAPTOP-001')).limit(1).collect()
Look up by category (multiple results)
# Create a category lookup (returns multiple products)get_by_category = pxt.retrieval_udf( products, name='get_by_category', description='Get all products in a category', parameters=['category'], limit=10, # Return up to 10 products)# Find all electronicsproducts.select(get_by_category(category='electronics')).limit( 1).collect()