.. _builtin: Built-in Functionality ======================= General ---------- We strive to ensure that PipelineDB maintains full compatibility with PostgreSQL 10.1+ and 11.0+. As a result, all of `PostgreSQL's built-in functionality`_ is available to PipelineDB users. .. _`PostgreSQL's built-in functionality`: http://www.postgresql.org/docs/current/static/functions.html .. _pg-built-in: http://www.postgresql.org/docs/current/static/functions.html Aggregates ------------- As one of PipelineDB's fundamental design goals is to **facilitate high-performance continuous aggregation**, PostgreSQL aggregate functions are fully supported for use in :ref:`continuous-views` (with a couple of rare exceptions). In addition to this large suite of standard aggregates, PipelineDB has also added some of its own :ref:`aggregates` that are purpose-built for continuous time-series data processing. See :ref:`aggregates` for more information about some of PipelineDB's most useful features. PipelineDB-specific Types ---------------------------- PipelineDB supports a number of native types for efficiently leveraging :ref:`probabilistic` on streams. You'll likely never need to manually create tables with these types but often they're the result of :ref:`aggregates`, so they'll be transparently created by :ref:`continuous-views`. Here they are: **bloom** :ref:`bloom-filter` **cmsketch** :ref:`count-min-sketch` **topk** :ref:`topk` **hyperloglog** :ref:`hll` **tdigest** :ref:`t-digest` .. _pipeline-funcs: PipelineDB-specific Functions --------------------------------- PipelineDB ships with a number of functions that are useful for interacting with these types. They are described below. .. _bloom-funcs: Bloom Filter Functions ------------------------------ **bloom_add ( bloom, expression )** Adds the given expression to the :ref:`bloom-filter`. **bloom_cardinality ( bloom )** Returns the cardinality of the given :ref:`bloom-filter`. This is the number of **unique** elements that were added to the Bloom filter, with a small margin or error. **bloom_contains ( bloom, expression )** Returns true if the Bloom filter **probably** contains the given value, with a small false positive rate. **bloom_intersection ( bloom, bloom, ... )** Returns a Bloom filter representing the intersection of the given Bloom filters. **bloom_union ( bloom, bloom, ... )** Returns a Bloom filter representing the union of the given Bloom filters. See :ref:`bloom-aggs` for aggregates that can be used to generate Bloom filters. .. _topk-funcs: Top-K Functions --------------------------------- **topk_increment ( topk, expression )** Increments the frequency of the given expression within the given **topk** and returns the resulting :ref:`topk`. **topk_increment ( topk, expression, weight )** Increments the frequency of the given expression by the specified weight within the given :ref:`topk` and returns the resulting :ref:`topk`. **topk ( topk )** Returns up to k tuples representing the given :ref:`topk` top-k values and their associated frequencies. **topk_freqs ( topk )** Returns up to k frequencies associated with the given :ref:`topk` top-k most frequent values. **topk_values ( topk )** Returns up to k values representing the given :ref:`topk` top-k most frequent values. See :ref:`topk-aggs` for aggregates that can be used to generate **topk** objects. .. _cmsketch-funcs: Frequency Functions ------------------------------ **freq_add ( cmsketch, expression, weight )** Increments the frequency of the given expression by the specified weight within the given :ref:`count-min-sketch`. **freq ( cmsketch, expression )** Returns the number of times the value of **expression** was added to the given :ref:`count-min-sketch`, with a small margin of error. **freq_norm ( cmsketch, expression )** Returns the normalized frequency of **expression** in the given :ref:`count-min-sketch`, with a small margin of error. **freq_total ( cmsketch )** Returns the total number of items added to the given :ref:`count-min-sketch`. See :ref:`cmsketch-aggs` for aggregates that can be used to generate **cmsketches**. .. _hll-funcs: HyperLogLog Functions ------------------------- **hll_add ( hyperloglog, expression )** Adds the given expression to the :ref:`hll`. **hll_cardinality ( hyperloglog )** Returns the cardinality of the given :ref:`hll`, with roughly a ~0.2% margin of error. **hll_union ( hyperloglog, hyperloglog, ... )** Returns a **hyperloglog** representing the union of the given **hyperloglog**. See :ref:`hll-aggs` for aggregates that can be used to generate **hyperloglog** objects. .. _tdigest-funcs: Distribution Functions ----------------------- **dist_add ( tdigest, expression, weight )** Increments the frequency of the given expression by the given weight in the :ref:`t-digest`. **dist_cdf ( tdigest, expression )** Given a :ref:`t-digest`, returns the value of its cumulative-distribution function evaluated at the value of **expression**, with a small margin of error. **dist_quantile ( tdigest, float )** Given a **tdigest**, returns the value at the given quantile, **float**. **float** must be in :code:`[0, 1]`. See :ref:`tdigest-aggs` for aggregates that can be used to generate **tdigest** objects. .. _misc-funcs: Miscellaneous Functions ----------------------------- **bucket_cardinality ( bucket_agg, bucket_id )** Returns the cardinality of the given **bucket_id** within the given **bucket_agg**. **bucket_ids ( bucket_agg )** Returns an array of all bucket ids contained within the given **bucket_agg**. **bucket_cardinalities ( bucket_agg )** Returns an array of cardinalities contained within the given **bucket_agg**, one for each bucket id. See :ref:`misc-aggs` for aggregates that can be used to generate **bucket_agg** objects. **date_round ( timestamp, resolution )** "Floors" a date down to the nearest **resolution** (or bucket) expressed as an interval. This is typically useful for summarization. For example, to summarize events into 10-minute buckets: .. code-block:: sql CREATE VIEW v AS SELECT date_round(arrival_timestamp, '10 minutes') AS bucket_10m, COUNT(*) FROM stream GROUP BY bucket_10m; **year ( timestamp )** Truncate the given timestamp down to its **year**. **month ( timestamp )** Truncate the given timestamp down to its **month**. **day ( timestamp )** Truncate the given timestamp down to its **day**. **hour ( timestamp )** Truncate the given timestamp down to its **hour**. **minute ( timestamp )** Truncate the given timestamp down to its **minute**. **second ( timestamp )** Truncate the given timestamp down to its **second**. **set_cardinality ( array )** Returns the cardinality of the given set array. Sets can be built using **set_agg**. .. _operations: Operational Functions ------------------------------------------ **pipelinedb.activate ( name )** Acitvates the given continuous view or transform. See :ref:`activation-deactivation` for more information. **pipelinedb.deactivate ( name )** Deacitvates the given continuous view or transform. See :ref:`activation-deactivation` for more information. **pipelinedb.combine_table( continuous view name, table )** :ref:`combine` the rows from the given **table** into the given continuous view. **combine_table** uses the given continuous view's query definition to combine aggregate values from both relations with no loss of information. **combine_table** can be used for purposes such as backfilling a continuous view (possibly running on a completely separate installation) by combining the backfilled rows into the "live" continuous view only once they have been fully populated. **pipelinedb.get_views ( )** Returns the set of all continuous views. **pipelinedb.get_transforms ( )** Returns the set of all continuous transforms. **pipelinedb.truncate_continuous_view ( name )** Truncates all rows from the given continuous view. **pipelinedb.version ( )** Returns a string containing all of the version information for your PipelineDB installation. System Views --------------------------- PipelineDB includes a number of system views for viewing useful information about your continuous views and transforms: **pipelinedb.views** Describes :ref:`continuous-views`. .. code-block:: psql View "pipelinedb.views" Column | Type | -------+---------+ id | oid | schema | text | name | text | active | boolean | query | text | **pipelinedb.transforms** Describes :ref:`continuous-transforms`. .. code-block:: psql View "pipelinedb.transforms" Column | Type | -------+---------+ id | oid | schema | text | name | text | active | boolean | tgfunc | text | tgargs | text[] | query | text | **pipelinedb.stream_readers** For each stream, shows all of the continuous queries that are reading from it. .. code-block:: psql View "pipelinedb.transforms" Column | Type | -------------------+-----------+ stream | text | continuous_queries | text[] | More system views are available for viewing :ref:`stats` for PipelineDB processes, continuous queries, and streams.