Run a step inside a database

Status

Variants

Control what is pushed down

Stop later steps from being pushed down to the source, or intercept them so the source can answer them.

Move data or queries between the database and the tool

Bring rows into the database to join an in-database chain, pull its result back, or pass its query text in or out as values.

Run a query in the database's own language

Send hand-written SQL to the source database to run there as written, and take its result.

Push pipeline steps down into the database

Have the tool translate a chain of ordinary steps into SQL, or another engine's jobs, that run where the data lives.

What other tools call it

Meridian's classification; an independent blind rating agreed on 86.2% of 312 rows (exact class, version 2).

Background

When to use it

Doing the analysis in the database, where the data resides, eliminates the costs, time and security issues of moving the data out for processing and back.^[1] In Power Query, query folding often gives faster query execution than extracting all the required data from the source and running every transform in the Power Query engine.^[2] In Power Query, sources without a query engine, such as comma-separated values (CSV) and Excel files, don't support query folding.^[2] Reach for a database step when the table is large and already sits in a database that can do the work, so that only the result has to travel. (Meridian's view)

In Power Query, transformations that can't be folded run in the Power Query engine.^[2] Power Query's Table.StopFolding function prevents any downstream operations from being run against the original source of the data.^[4] During optimisation, the Power Query engine might reorder query steps.^[2] Where the connector allows it, Power Query's View Native Query or View data source query shows the request that Power Query sends to the data source.^[2] Confirm which steps actually ran in the database rather than assuming the tool sent them there. (Meridian's view)

In RapidMiner's In Database Nest operator, setting the parallelize parameter submits several queries to the database at once, which may increase the load on the source database significantly.^[5] In Alteryx Designer, the Data Stream In tool's Create Temporary Table mode requires CREATE TABLE permissions for all data sources except SQL Server.^[6] In Alteryx Designer, a Pre-SQL statement runs before the in-database execution, and across several streams of In-Database tools no order of execution is guaranteed.^[6] When one step's setup must happen before another's in the database, keep them in a single chain rather than relying on the order the tool happens to choose. (Meridian's view)

The method

In-database processing comes in three main types: translating a model into SQL code, loading C or C++ libraries into the database process as a user-defined function (UDF), and registering out-of-process libraries as UDFs.^[1] When a predictive model is translated into SQL, the SQL usually runs in the database in a stored procedure, with values passed in through parameters.^[1] An in-process UDF has full access to the database server's memory, parallelism and processing management, so it must be well-behaved so as not to harm the database or the engine.^[1] Out-of-process UDFs run in their own process space with their own resources, so they carry less risk to the database but are not expected to perform as well as in-process ones.^[1]

Power Query's query folding sends metadata requests to learn the data source's capabilities, table schemas and relationships, then determines what to extract from the data source and which transformations must occur inside the Power Query engine.^[2] Power Query's data source functions, commonly shown as the Source step of a query, query the data source in its native language, and later transformations fold only when the connector and transformations support it.^[2] Power Query's query folding can translate and combine supported transformations into a single data source query.^[2] Power Query distinguishes full query folding, where the data source processes all transformations, partial query folding, and no query folding, where the Power Query engine processes them all.^[2] RapidMiner's In Database Nest operator translates the steps its subprocess defines to SQL and submits that code into the selected database.^[5] In RapidMiner's In Database Nest, only operators from the In-Database Processing group can be used inside it unless its allow all operators parameter is set, and the generated query is kept as an annotation on each data set it returns.^[5]

KNIME's DB Query node lets the user write SQL around the incoming query, which appears in it as the placeholder #table# and is replaced at execution, creating a sub-query.^[7] Power Query's Value.NativeQuery function evaluates a query against a target, with the query expressed in a manner specific to the target and optional parameter values supplied for it.^[8] DuckDB's postgres_execute function runs arbitrary queries within PostgreSQL, including statements that update the schema and content of the database.^[3] Alteryx Designer's Data Stream In tool brings data from Designer into an In-Database workflow, and its Data Stream Out tool streams data from an In-Database workflow back out to Designer.^[6][9] Alteryx Designer's Data Stream In tool can write the incoming data to a temporary table, created with a CREATE TABLE statement and removed by the Alteryx engine when the workflow completes.^[6]

In-database capabilities were first offered commercially in the mid-1990s by vendors including IBM, Illustra/Informix and Oracle, but the technology did not begin to catch on until the mid-2000s.^[1] Thomas Tileston first introduced the idea of migrating analytics from the analytical workstation into the enterprise data warehouse at the Teradata Partners conference in Orlando in September 2005.^[1]

Compared with the alternatives

In DuckDB, the tables of an attached PostgreSQL database can be read as if they were DuckDB tables, with the underlying data read directly from PostgreSQL at query time.^[3] As of DuckDB 2.0, the CONNECT statement routes a whole session to an attached PostgreSQL database, and queries are then shipped to the PostgreSQL server instead of pulling tables over the wire.^[3] Reading an attached table and letting filters travel is the light touch; a hand-written query sent with postgres_query or postgres_execute suits work the analyst wants written in PostgreSQL's own SQL. (Meridian's view)

Power Query's Table.Buffer function buffers a table in memory, isolating it from external changes during evaluation, while Table.StopFolding prevents any downstream operations from being run against the original source of the data.^[10] Choose between buffering a table and stopping folding by the effect wanted: holding the data still, or keeping later steps away from the source. (Meridian's view)

Sources

1. [1]In-database processingsecondary source
2. [2]Understanding Query Evaluation and Query Folding in Power Querydocumentation
3. [3]PostgreSQL Extensiondocumentation
4. [4]Table.StopFoldingdocumentation
5. [5]In Database Nestdocumentation
6. [6]Data Stream In Tooldocumentation
7. [7]DB Querydocumentation
8. [8]Value.NativeQuerydocumentation
9. [9]Data Stream Out Tooldocumentation
10. [10]Table functionsdocumentation