BEEM provides a set of SQL functions you can call from your warehouse to enrich, transform, or validate data inline in any query. Just call them like any other SQL function.

All functions below are external functions — they run against BEEM-managed services, so no setup is required in your workspace.

🚧

Rate limits apply

Each function wraps an underlying API (Google, Genderize, Revenu Québec, Bedrock, BEEM internal APIs, etc.) with its own rate limits and quotas. Queries that call a function on many rows can exceed those limits quickly. If you're running a function over a large table, batch your work — e.g. WHERE id BETWEEN X AND Y — or schedule it incrementally rather than running it against the whole table at once.

AI

Apply AI to a row

Apply a natural-language prompt to any value in your query and get an AI-generated result back.

Parameters

Returns

Example

WITH reviews AS (
  SELECT 1 AS review_id, 'Arrived on time and exactly as described, very happy.' AS review_text
  UNION ALL SELECT 2, 'Fabric feels cheap and the color is not what was shown online.'
  UNION ALL SELECT 3, 'Customer service was excellent, they resolved my issue in minutes.'
)
SELECT
  review_id,
  f_ai('low', 'Summarize this review in 5 words:', review_text) AS summary
FROM reviews;

Tips

• Start with 'low' — it's fast and cheap, and works well for most classification, summarization, and extraction tasks.
• Use 'high' only when you need the best possible reasoning (nuanced rewrites, multi-step logic).
• Prompts are applied per row — if you have 10,000 rows, the function runs 10,000 times. Test on a small sample first (LIMIT 10) before running on a large table.

Address & Geocoding

Look up an address from a postal code

Look up a full postal address from a postal or ZIP code using the Google Geocoding API.

Parameters

Returns

Example

SELECT f_google_addr_from_postal('K1A 0A6', 'YOUR_GOOGLE_API_KEY') AS address;

Distance between two addresses

Calculate the driving distance between two addresses using the Google Maps Distance Matrix API.

Parameters

Returns

Example

SELECT f_google_dist_between_addr(
  '123 Main St, Montreal, QC',
  '456 Oak Ave, Toronto, ON',
  'YOUR_GOOGLE_API_KEY'
) AS distance_m;

Names & Demographics

Predict gender from a first name

Predict the likely gender associated with a given first name, based on statistical data from the public Genderize.io API.

Parameters

Returns

Example

SELECT f_assume_gender_from_firstname('John') AS predicted_gender;

Validation

Validate a Quebec TVQ number

Validate a Quebec sales tax (TVQ) number against Revenu Québec's registry.

Parameters

Returns

Example

SELECT f_revenuquebec_tvq_validate('1234567890TQ0001') AS tvq_validation;

Data Conversion

Convert Maestro XML to JSON

Convert a Maestro-formatted XML string into JSON. Useful for flattening XML payloads stored in a column.

Parameters

Returns

Example

SELECT f_maestro_xml_to_json(xml_column) AS as_json
FROM source_table;

BEEM Platform Metadata

These functions return metadata about your BEEM workspace — useful for building dashboards and monitoring usage directly from SQL.

List datasets in a workspace

List all dataset IDs available in a given workspace.

Parameters

Returns

Example

SELECT f_list_datasets('your-workspace-id') AS dataset_ids;

Get dataset metadata

Get metadata (name, owner, columns, etc.) for a single dataset.

Parameters

Returns

Example

SELECT f_get_dataset_metadata('some-dataset-uuid') AS metadata;

Get my organization's consumption

Same as f_get_consumption, but pre-configured for your current organization — just pass a start date.

Parameters

Returns

Example

SELECT f_get_my_consumption('2026-02-01') AS consumption;

Need help?

If you'd like to request a new function or report an issue with an existing one, contact the BEEM team at [email protected].