Skip to content

Plot features, datasets and source data in the database

tdfs4ds draws charts inside Teradata with the UAF function TD_PLOT. The rows never leave the database: only the image comes back (a PNG of some tens of kB, whatever the number of points).

import tdfs4ds

r = tdfs4ds.plot_feature('total_amount')                             # distribution of the stored values
r = tdfs4ds.plot_feature('total_amount', kind='history', entity='CUSTOMER_ID',
                         entity_values=['C001', 'C002'])             # values over time
r = tdfs4ds.plot_dataset('DS_DAILY', 'daily_amount')                 # time axis + entity as series
r = tdfs4ds.plot_dataset('DS_DAILY', 'daily_amount', x='CATEGORY', agg='sum')   # bar chart by category
r = tdfs4ds.plot_dataset('DS_DAILY', 'daily_amount', x='nb_transactions')       # scatter of two features
r = tdfs4ds.plot_in_database('SELECT ... FROM ...', 'x_col', 'y_col', x_is_time=True)   # any query

# several panels in one image (each keeps its own scale)
r = tdfs4ds.plot_features(['total_amount', 'nb_transactions'], kind='history', entity='CUSTOMER_ID')
r = tdfs4ds.plot_dataset('DS_DAILY', ['daily_amount', 'nb_transactions'])

# which entities a history shows
r = tdfs4ds.plot_feature('total_amount', kind='history', max_series=5)                 # the 5 largest now
r = tdfs4ds.plot_feature('total_amount', kind='history', max_series=5, series_order='smallest')
r = tdfs4ds.plot_dataset('DS_DAILY', 'daily_amount', entity_values=['Travel', 'Market'])

tdfs4ds.show_plot(r)          # in a notebook
tdfs4ds.save_plot(r, 'chart.png')                                    # .png, .svg or .html

# correlation matrix of a dataset's numeric features (heatmap)
r = tdfs4ds.plot_correlation('DS_DAILY')                             # r['matrix'] holds the values

# zoom: the rows are filtered to the window in SQL
r = tdfs4ds.plot_feature('total_amount', kind='history', xlim=('2023-03', '2023-06'))
r = tdfs4ds.plot_dataset('DS_DAILY', 'daily_amount', ylim=(0, 20000))

What each function plots

Call Chart
plot_feature(name) Histogram of the feature's stored values at the current valid time (as_of= for another).
plot_feature(name, kind='history') The values over time, one line per entity value. A daily feature (an entity with a date column) uses that date as the axis; otherwise the axis is the valid time: each point is the moment a value started to hold.
plot_dataset(view, y, x=, series=, kind=) A dataset's columns. Defaults: x = the date column (ValidStart for a historical dataset), y = the first numeric feature, series = the entity columns that are not dates. kind: line, scatter, bar (a text x is aggregated with agg='avg'\|'sum'\|'min'\|'max'\|'count'), hist.
plot_in_database(source, x, y, ...) Any table, view or SELECT. A temporal table needs qualifier='CURRENT VALIDTIME' (or VALIDTIME AS OF ..., NONSEQUENCED VALIDTIME), never inside the query.
plot_historian(table, tags, id_values=) Tags of a historian (deep) table as time series, one panel per tag, one line per id. See below.
plot_dataset(view, kind='periods') The periods a table or view holds (a PERIOD column, or a start/end pair), drawn as pulses.
  • History on valid time: each value is drawn as a step over the period it held, from ValidStart to ValidEnd (to now for the current value). Each end point is one second before the boundary, so the line never goes back and forth where one period ends and the next starts.
  • Panels: plot_features(...) (one panel per feature, or dicts to vary kind / entity per panel), plot_dataset(y=[...]) (one panel per column) and plot_panels([panel(...), ...], layout=(columns, rows)). At most tdfs4ds.PLOT_MAX_PANELS (6); a panel that cannot be drawn is left out and named in notes.
  • Legend: above tdfs4ds.PLOT_LEGEND_INLINE_MAX (6) curves, the image grows so the whole legend fits; on a numeric x axis the x range is also extended on the right so the legend sits beside the curves, not on them. Above tdfs4ds.PLOT_LEGEND_MAX (40) curves, the legend is left out. TD_PLOT cannot draw a legend outside the plot, and it has 10 colours, so beyond 10 curves colours repeat (the labels tell them apart).
  • Which series: above max_series (default tdfs4ds.PLOT_MAX_SERIES, 12) the series kept are chosen by series_order: largest (default, by latest value), smallest, alphabetical, most_points.
  • Which entities: entity_values=[...] keeps those values; a value matches the whole key or any one of its columns ("Travel" keeps every series of the Travel category).

A feature name used by several entities or data domains needs entity= or data_domain=; the error lists them. When several processes compute the feature, the one written most recently is plotted (process_id= for another).

Source data: historian tables, periods, feature windows

Plotting is not limited to features. Any table or view works with plot_dataset / plot_in_database, and three cases have their own help.

# a historian (deep) table: one row per (ids, timestamp, tag), the value in a payload column
r = tdfs4ds.plot_historian('DB_SOURCE.MARKET_PRICES', 'CLOSE', id_values=['NVDA', 'MSFT'])
r = tdfs4ds.plot_historian('DB_SOURCE.MARKET_PRICES', ['CLOSE', 'VOLUME'], id_values='NVDA')  # two panels

# a feature's stored period as the time window of a series
w = tdfs4ds.feature_window('last_stress_start', 'NVDA')     # either bound of the pair works
r = tdfs4ds.plot_historian('DB_SOURCE.MARKET_PRICES', 'CLOSE', id_values='NVDA', xlim=w['xlim'])

# every period a feature has held over its history, under the series (same time range)
p, _ = tdfs4ds.feature_periods_panel('last_stress_start', 'NVDA')
r = tdfs4ds.plot_historian('DB_SOURCE.MARKET_PRICES', 'CLOSE', id_values='NVDA', extra_panels=[p])

# every stored period superimposed: x = days from its start, values = 100 at the start
r = tdfs4ds.plot_aligned_periods('DB_SOURCE.MARKET_PRICES', 'CLOSE', 'last_stress_start',
                                 id_values='NVDA', margin_days=7)

# a table or view with periods
r = tdfs4ds.plot_dataset('DB.DS_CUSTOMER_HISTORY')                   # values as steps over ValidStart / ValidEnd
r = tdfs4ds.plot_dataset('DB.CONTRACTS', kind='periods')             # the periods themselves
  • Historian tables. The table's layout is read from its documentation (set_deep_table_documentation): the id columns, the tag column, the payload column and the timestamp column. Each tag is a panel (tags carry different units); the rows are filtered on the tag, the value is the payload, one line per id. Tags and ids are matched without regard to case; an unknown tag is refused with the list of tags, an unknown id is named in the notes.
  • Periods. A PERIOD(DATE) / PERIOD(TIMESTAMP [WITH TIME ZONE]) column, or two date/timestamp columns named as bounds (X_START / X_END, BEGIN_X / END_X, ValidStart / ValidEnd), is a period. A value over a period is drawn as a step. kind='periods' draws the periods themselves: 1 while a period holds, 0 outside, one line per entity. An open period (no end, or UNTIL_CHANGED) is drawn up to now.
  • A feature's period as the window. feature_window(feature, entity_value) reads the period a feature stores for one entity value: a PERIOD feature, or a pair of date/timestamp features ..._start / ..._end (either name). It reads the current valid time, or as_of=. A feature with values for several entities needs the value.
  • The periods a feature has held. feature_periods_panel(feature, entity_value) reads the feature's history (NONSEQUENCED VALIDTIME) and draws each distinct period once. When it is an extra_panels of plot_historian, it spans the same time range as the series.
  • These are source data, not features: they have no entity, no process and no tracked freshness, and the chat says so.

Correlation, zoom, download

  • plot_correlation(view, columns=None): every Pearson correlation in one pass (CORR), the matrix built from that single row and drawn by TD_PLOT as a heatmap (mesh, colour bar from -1 to 1). Default columns: the dataset's numeric features (at most PLOT_MAX_CORR_COLUMNS, 16). The matrix is in r['matrix'], and the interactive version names both axes.
  • Zoom: xlim=(low, high) (dates, YYYY-MM, or numbers; either end may be None) filters the rows to the window in SQL, so the database draws more points in it. TD_PLOT refuses XRANGE on a time axis; on a numeric axis it is set too. A history's steps are cut at the window edges; for a histogram the window is on the values. ylim=(low, high) sets YRANGE.
  • Download: the chart page shown in the chat has Download PNG, Download SVG (made for requests up to PLOT_SVG_MAX_POINTS, 20,000, since an SVG holds every point) and Actual size (the image at full resolution, scrolling). save_plot(r, path) writes the same files from Python.
  • Time axis format: follows the data's span: hours within one day (the date goes in the axis label), YYYY-MM-DD, or months over about 13 months. TD_PLOT accepts no date and time together.

How it works

  1. The rows of the request are counted. An empty request is said so. There is no limit by default (tdfs4ds.PLOT_MAX_POINTS = None): TD_PLOT draws large requests, and when it fails its whole message comes back in error. Set a number to refuse requests above it.
  2. They are copied to a volatile table (x, y, id = the series): TD_PLOT reads a table, not a query. No CREATE right is needed and nothing permanent is created.
  3. The first tdfs4ds.PLOT_MAX_SERIES (12) series are kept; the result says how many there were.
  4. EXECUTE FUNCTION TD_PLOT(...) returns the image.
  5. Small requests only (at most tdfs4ds.PLOT_INTERACTIVE_MAX_POINTS, 2,000 points; 0 = never): the staged rows are read back and an interactive Plotly version is added. Above that, the data stays in the database.
  6. The volatile tables are dropped.

The result is a dict: ok, image (bytes), mime, n_points, n_series, n_series_total, series_capped, interactive_html, td_plot_sql, staging_sql, notes, source and error (the whole database message; database errors are returned, not raised).

Measured on Vantage 20: 1.5–2 s per chart end to end, of which TD_PLOT itself takes 0.1–0.5 s, including 200,000 points.

Entity values and the ends of a distribution

Two questions often come before a chart: which values does an entity take, and who sits at the ends of a feature's distribution. Both are answered in the database; only the rows shown leave it.

from tdfs4ds.feature_values import entity_sample, feature_extremes

# how many distinct TICKER values are stored, and a random sample of 5 (SAMPLE in the database)
s = entity_sample('TICKER', n=5)
s['total'], s['values']            # 5, [{'TICKER': 'AMD'}, {'TICKER': 'JNJ'}, ...]

# statistics, the 5 highest and lowest entities, and the outliers (Tukey's rule)
e = feature_extremes('sum_Transaction_Amount', entity='CustomerID',
                     data_domain='Customer Transaction Analytics', n=5)
e['stats']                         # min, q1, median, q3, max, mean, std
e['top'], e['bottom']              # [{'key': {'CustomerID': 33357}, 'value': 94754.4}, ...]
e['outliers']                      # {'rule': 'tukey', 'low': ..., 'high': ..., 'count': 132, 'examples': [...]}
  • Entity values are the keys of the entity's storage tables (FS_T_*) in every data domain holding it, at the current valid time (as_of= for another): one COUNT over the union of distinct keys, then SAMPLE n.
  • Extremes: one aggregate pass (PERCENTILE_CONT, AVG, STDDEV_SAMP) on the feature's storage table, then TOP n reads. Outliers are outside [Q1 - 1.5 IQR, Q3 + 1.5 IQR]; when the IQR is 0 the values that differ from the median are given instead. Only the most recently written process version is read. A non-numeric feature gets its count only.
  • In the chat: "give me 5 TICKER values", "donne-moi des exemples de CustomerID", "which customers have the highest sum_Transaction_Amount?", "are there outliers in stress_drawdown?" (the VALUES skill), and every distribution chart of a feature lists the same statistics, top, bottom and outliers under it.

In the chat

Ask for a chart of a named feature or dataset: "plot the distribution of total_amount", "show total_amount over time for the CUSTOMER entity", "plot daily_amount by CATEGORY from DS_DAILY", "trace l'évolution de total_amount". The PLOT skill draws it with TD_PLOT; the chart appears under the answer, with an interactive version for small requests.

  • "plot A and B over time" draws two panels; "the top 5", "the 3 smallest", "les 5 plus grands" choose the series; entity values named in the question ("for Travel and Market", "customers 14329 and 33357") restrict the chart - the store says which words are values.
  • A request that names no feature or dataset ("how did the daily spending per category evolve?") is read by the instruct model against the store's datasets and features; every name it returns is checked, and the answer says how it was understood. Without an instruct model the agent asks for a name.
  • A name used by several entities or data domains: the agent lists them and shows how to ask.
  • "plot the correlation matrix of DS", "montre la matrice de corrélation de DS", a heatmap.
  • Zoom: "plot X between 2023-10-02 and 2023-10-04", or after a chart "zoom from 2023-03 to 2023-06", which redraws the previous one. Dates that form a window are a zoom, never the conversation's valid time.
  • Source data. A documented table or view (in the business dictionary or the lineage graph), or a tag of a historian table, can be named: "plot the close of NVDA and MSFT" (CLOSE is a tag of the historian MARKET_PRICES, NVDA and MSFT are ids), "plot NVDA close and volume" (two panels), "plot all tags of MARKET_PRICES for MSFT", "plot the transactions table". Feature-store internals (FS_T_*, catalogs) are never targets, and a name the lineage graph holds but the database does not is ignored.
  • A feature's period. "plot NVDA close during its last stress episode" takes the window from the stored period (last_stress_start / last_stress_end for NVDA) and says so. "... with its stress episodes" adds a panel of every episode the feature has held. The time feature is the one named, or the one whose name shares the most words with the question.
  • An incomplete request. The agent asks rather than guesses, and keeps the request: the reply completes it.
> Plot all the stocks during the stress period
To draw this chart I need a little more:
- Which measure? DB_SOURCE.MARKET_PRICES holds the tags CLOSE, HIGH, LOW, OPEN, VOLUME.
- Which period?
  1. last_stress_start / last_stress_end: 2017-08-11 20:00:00 to 2017-08-14 20:00:00 (NVDA)
  2. stress_episode_start / stress_episode_end: 2017-08-11 20:00:00 to 2017-08-14 20:00:00
> the close, period 1, around it                 (five stocks, the episode widened by 7 days each side)
> superimpose them                               (each episode a curve, from its own start: day 0)
  • The table: "the stocks" names no table; it is found from the documented descriptions.
  • Which measure: asked when no tag is named; "all tags" draws them all.
  • Which period: the pairs of date features that share a word with the question, with the dates they store. A number in the reply names one.
  • "around": widens the period by its length on each side, at least 7 days.
  • "superimpose", "each episode", "align", "same start", "day 0": with a period feature in play, every period the feature has held becomes a curve, on an axis of days from its start, indexed to 100 at the start (every stock and every period in one chart, a curve per stock and period; "separately" gives one panel per stock). "raw values" keeps the prices. Python: plot_aligned_periods(..., per_id=False).
  • "base 100", "rebase" without periods: the series of one window, as an index.
  • "on the median", "the mean instead": 100 at the median / mean of each whole curve rather than at its first value (rebase='median' / 'mean' in Python). The x alignment is separate: "raw values" keeps the prices and still aligns the episodes on day 0.
  • A question with a plot word (plot, chart, histogram, graphique, courbe, trace la / le…) goes to this skill, unless it asks about lineage.
  • With MCP enabled, the MCP server's plot_* tools are not used: they read the rows out of the database to draw them in the browser.
  • The interactive version loads plotly.js from its CDN (tdfs4ds.PLOT_PLOTLYJS = 'cdn'): inlined it would add 4.8 MB to every chart. Without network the TD_PLOT image still shows; set 'inline' for offline use.

Limits

  • TD_PLOT needs the Unbounded Array Framework (Vantage 17.20 or later). Without it the error says so, and a small request is drawn interactively instead.
  • TD_PLOT cannot label a category axis: a bar chart by category is drawn over positions 1..k, with the names in the axis label (and on the interactive version).
  • TD_PLOT text arguments (title, axis labels) are cut at 127 characters, and images are at least 400 x 400 pixels: longer or smaller is refused with a bare syntax error.