tdfs4ds.dataset.batch_retrieval¶
tdfs4ds.dataset.batch_retrieval¶
Parser-limit-safe batch retrieval of dataset rows for a list of entity keys.
A naive batch lookup builds SELECT * FROM ds WHERE (a=1 AND b=2) OR … whose
text grows with the number of keys and, for many / wide composite keys,
exceeds Teradata's ~1 MB request parser limit (Error 5639). The robust strategy
is a volatile-table join: load the (deduplicated) keys into a volatile table
whose PRIMARY INDEX matches the actual distribution of the feature-store
technical tables the dataset joins, then INNER JOIN — keys travel as data,
not SQL text, so there is no parser limit and the join is AMP-local.
Public API¶
- :func:
retrieve_batch— fetch rows for a list of entity-key tuples, choosing thein_listpredicate or thevolatile_joinstrategy (method='auto'). - :func:
resolve_dataset_pi— read the real PRIMARY INDEX / partitioning of the feature tables a dataset joins (so the volatile table co-locates with them). - :func:
dataset_entity_columns— the dataset grain as[(name, type), …].
Connection seam¶
Every helper takes an optional raw teradatasql conn. When conn is
None the active teradataml context is used via tdml.execute_sql — so
package users get the zero-config path and the benchmark can reuse its own raw
per-thread connection (avoiding teradataml's pooling latency).
module-attribute
¶
Teradata-valid batch predicate for a list of (composite) key tuples.
Single-column key -> col IN (…); composite key ->
(a=1 AND b=2) OR (a=3 AND b=4) OR ….
Build a SQL WHERE for ONE composite key (AND of equalities).
True when the IN/OR predicate text stays safely under the parser limit.
UTF-8 byte length of the IN/OR predicate for key_tuples.
Return name as a Teradata identifier, quoting it only if required.
Idempotent: an already-quoted name is returned unchanged rather than being
wrapped a second time. Embedded quotes are escaped as "".
Parameters¶
name : str
The raw object/database name.
force : bool, default False
Quote even when the name does not need it. Use only at the few sites
that have always emitted unconditional quotes and whose exact output is
already baked into persisted DDL (see
feature_store/feature_query_retrieval.py) — elsewhere forcing would
change the SQL of existing stores for no reason.
Build db.obj with each part quoted only as needed.
qualified('TDB', 'FS_X') == 'TDB.FS_X' (unchanged for ordinary names);
qualified('SVC@CORP', 'FS_X') == '"SVC@CORP".FS_X'. An empty/None
database yields the bare object name, so unqualified callers keep working.
force=True quotes both parts unconditionally — only for the sites that
have always emitted quotes and whose exact text is already persisted (dataset
view DDL, returned volatile-table names).
Split db.obj into (database, object), both de-quoted.
Quote-aware, so a dot inside a quoted part does not split the name:
split_qualified('"DEINF.CARLOSN"."FS_X"') == ('DEINF.CARLOSN', 'FS_X').
The naive rsplit('.', 1) this replaces returns ('"DEINF.CARLOSN"',
'"FS_X"') only by luck and ('"DEINF', 'CARLOSN"."FS_X"') in the
split('.') variant.
An unqualified name returns ('', name).
Execute sql on a raw teradatasql conn or the teradataml context.
Returns the fetched rows when fetch=True, else None.
Return the session user as a DATABASE QUALIFIER for the volatile table.
Quoted whenever the logon name is not a bare-legal Teradata identifier: a dotted LDAP name unquoted raises Error 3707 (invalid three-part name), and a name carrying an '@' is a syntax error. An ordinary name is unchanged.
Quote-aware split. The DBC predicates below compare the name as DATA, so both parts come back de-quoted.
Resolve a dataset view name to its DATASET_ID via the dataset catalog.
Return the dataset grain as [(column_name, sql_type), …].
Reads ENTITY (name) + ENTITY_TYPE (type) from the dataset catalog's
entity table. Requires an active teradataml context. Returns [] when the
dataset can't be resolved.
The dataset's DatasetSpec read from its own DDL, or None.
The view text is the authoritative description of a dataset (see
ddl_spec): it names every storage table it reads. None when the view
cannot be read or is not a tdfs4ds dataset view.
[(db, table), ...] read by a parsed dataset, in first-appearance order.
Collect the FS_T_* storage tables the dataset's features are stored in.
Read from the dataset view's own DDL (FROM db.FS_T_... B1 in every
sub-query). This used to go through the process catalog's VIEW_NAME and
look for FS_T_* in THAT view's DDL - but VIEW_NAME is the user's
process view, which reads source tables, so nothing was found and the
volatile key table silently fell back to the full entity as its PRIMARY
INDEX. That path is kept only as a fallback for a view the parser does not
understand. Fail-open: returns [] on any error.
Read a table's PRIMARY INDEX columns (ordered) from DBC.IndicesV.
Read a table's partitioning constraint text from DBC, if any.
Resolve the REAL primary index / partitioning of a dataset's feature tables.
Returns a dict::
{"primary_index": [...], "partitioning": "...",
"source": "DBC.IndicesV" | "fallback_entity_cols",
"feature_tables": ["db.FS_T_…", …]}
The volatile key table should use primary_index so the join co-locates
with the feature storage tables. Fail-open: returns entity_cols when the
real distribution can't be read (restricted DBC, view-only, no match).
How a dataset's storage tables are laid out, as far as a lookup cares.
Returns {"spec": DatasetSpec | None, "tables": {<norm table>: {"table",
"primary_index", "partitioning", "partition_columns"}}}, where
partition_columns are the ENTITY columns the table's partitioning
expression names (FEATURE_ID is filtered by the dataset itself).
Fail-open: unreadable DBC rows leave empty lists.
A parsed snapshot dataset whose entity covers the key columns.
Redundant B1.<col> IN (...) on each partitioning entity column.
Implied by the key predicate (each key's value is in the list), so it
changes no result; it is there because the optimizer derives static
partition elimination from a plain IN-list on the partitioning column and
not from an OR of ANDs or a join to an unpartitioned key table. Left out
when a key component is NULL (IN would drop that key, which the
IS NULL predicate keeps) or when the list is too long to be worth it.
build_batch_sql(dataset, entity_cols, keys, *, key_table=None, columns=None, conn=None, pushdown='auto')
The SQL of one batch lookup, and its shape: (sql, 'pushdown' | 'view').
The single authority for batch lookup SQL - retrieve_batch and the
benchmark both call it, so the benchmark measures what users run.
pushdown- the dataset's own query regenerated from its DDL (ddl_spec.render_retrieval_sql) with the keys inside every storage table read, plus the partition IN-lists of_partition_predicates. EachFS_T_*table is then read by primary index / row hash and only in the partitions the keys fall in.view- the historicalSELECT ... FROM <view> WHERE <keys>(or the join to the key table): used whenpushdown=False, for a historical dataset, a view the parser does not understand, or keys that are not the dataset's entity.
key_table switches from the OR-of-ANDs predicate to an inner join to that
(volatile) table. columns is honoured by the view form only; the pushed
form selects everything and retrieve_batch projects.
Run a batch statement; project columns when the pushed-down form, which always selects the dataset's full column list, was used.
The in_list statement, or None when it would exceed the parser limit.
retrieve_batch(dataset, list_entity_keys, entity_cols=None, *, columns=None, method='auto', volatile_threshold=DEFAULT_VOLATILE_THRESHOLD, conn=None, dedup=True, pushdown='auto')
Retrieve rows of dataset for a list of entity-key tuples.
Parameters¶
dataset : str
Fully-qualified DB.dataset_view to read from.
list_entity_keys : list[tuple]
Entity-key tuples in the order of entity_cols (each tuple one row's key).
entity_cols : list[str] | None
Grain/entity key columns. When None they (and their SQL types) are
auto-derived from the dataset catalog (needs an active teradataml context).
columns : list[str] | None
Columns to project; None selects all.
method : {'auto','in_list','volatile_join'}
'in_list' builds a WHERE predicate (single-col IN / composite
OR-of-ANDs); 'volatile_join' loads the keys into a volatile
table co-located on the feature-store primary index and joins; 'auto'
(default) uses the volatile join when the unique-key count reaches
volatile_threshold OR when the in_list predicate would exceed the
parser limit, else the in_list predicate.
volatile_threshold : int
Key count at/above which 'auto' switches to the volatile join.
conn : teradatasql connection | None
Optional raw connection (benchmark reuse). None uses the teradataml
context.
dedup : bool
Drop duplicate key tuples first (required for the volatile join; also
shrinks the in_list predicate).
pushdown : {'auto', True, False}
'auto' (default) regenerates the dataset's query from its DDL with
the keys inside every storage-table read, so each FS_T_* table is
read by primary index and only in the partitions the keys fall in -
filtering the view from outside reaches neither, and under FULL OUTER
nothing can be pushed through the view at all. Falls back to the view
for a historical dataset or a view the parser does not understand.
False keeps the previous SQL exactly. Same rows either way.
Returns¶
list[tuple] The fetched rows.