Filtering and aggregating data with QueryRecord in NiFi: when SQL inside the flow is the right tool
QueryRecord applies SQL to record-based FlowFiles and sends each query result to its own relationship. If your team needs to filter rows, route records by a condition, rename fields or summarize values mid-flow, and you would rather not write and maintain a custom processor, this is usually the first component to evaluate. Each query you add becomes a dynamic relationship on the processor, so one incoming FlowFile can branch into several outputs in a single step. The NiFi component documentation for QueryRecord describes it as a processor that reads records through a configured Record Reader, runs the SQL statements you define against them, and writes the results through a Record Writer. Confirm the exact property names and reader and writer requirements in that documentation for the NiFi version you run.
Teams usually reach for QueryRecord when the logic is easier to state as a query than as a chain of field-level operations. A filter such as keeping only rows where a status field is an error, a rename that maps source columns to the names a target table expects, or a grouped count per hour are all compact in SQL and awkward to express across several record processors. Other record processors remain the better fit for simple field edits, reshaping JSON structure, or per-record lookups, which are covered in the sibling sections on transformation and enrichment. Before adopting the pattern, check the NiFi documentation for which SQL features the embedded engine supports, since your queries need to be written against that dialect rather than a general database.
The decision to make early is whether the query logic belongs in the flow at all. Queries embedded in processor properties are visible to operators and easy to change, but they are harder to unit test than a standalone script and they are reviewed together with the flow definition. Ask your team who will own the SQL, how changes will be tested against sample records, and how the query will be versioned alongside the flow. Limits on large inputs also belong in that conversation, because a query that aggregates across a very large FlowFile places memory and processing demand on the node that runs it. Plan a representative test with realistic record volumes in your own environment before you commit to the design.
Which QueryRecord pattern fits each job: filter, route, rename or aggregate
Filtering is the simplest QueryRecord job. You define one dynamic property whose SQL selects the rows to keep, and the processor writes those rows to a new FlowFile on the matching relationship. The SQL shape is a plain SELECT with a WHERE clause over the fields of the incoming record, with no grouping. Illustrative example: SELECT * FROM FLOWFILE WHERE status = 'active'. The query depends on the reader's schema: the field named in the WHERE clause must exist, and its type must match the comparison, so a string field compared to a number can silently exclude rows or fail. Before relying on a filter, confirm the field names and types in the schema your Record Reader uses, test with records that include nulls and empty strings, and decide what happens to rows that match neither condition.
- Field presence: Confirm that every referenced field exists in the schema, including nested or optional fields.
- Type agreement: Compare like types, and check how dates and timestamps arrive from your source.
- Null handling: Test rows where the filter field is null, since null comparisons do not behave like values.
- Projection: Keep
SELECT *only when you need every field, otherwise list the fields you want downstream. - Unmatched rows: Confirm whether rows that do not match are intended to be dropped or routed elsewhere.
Before relying on a filter, run it against a sample file that includes the malformed and edge-case rows your sources actually produce.
QueryRecord can define several dynamic properties, and each one creates its own outgoing relationship named after the property. A single incoming FlowFile can therefore be split into several outputs in one pass, for example high-priority and standard records. The SQL shape is one SELECT per route, each with its own WHERE clause. Illustrative example: one route selects WHERE amount >= 10000 and another selects WHERE amount < 10000. The routes are evaluated independently, so a record can match more than one route and be duplicated, and a record matching none is not sent anywhere unless you add a catch-all route. The dependency here is the same schema across all routes, and the relationship names become part of the flow design that downstream connections rely on.
- Overlap: Decide whether a record may appear in more than one route, and write conditions that are mutually exclusive when it may not.
- Catch-all route: Add an explicit route for records that match no condition, so they do not disappear.
- Relationship naming: Use stable, descriptive property names, since renaming changes the relationship that connections attach to.
- Failure path: Confirm what happens when a query fails, which is separate from the matched and unmatched relationships.
- Route count: Keep the number of routes modest, because each one is a separate query over the same input.
Before relying on routing, verify with a test file that each record lands in exactly the relationship you intended.
Renaming and reshaping use the SELECT list to alias columns and compute new ones, so the output record has the field names that the target expects. The SQL shape is a SELECT that lists each source field with an alias, and may include simple expressions such as concatenation or CASE. Illustrative example: SELECT cust_id AS customer_id, UPPER(country) AS country_code FROM FLOWFILE. The output schema is determined by the query and the writer, so the names and types in the SELECT list become the contract for the next processor and for the database or API at the end of the flow. Because the reader schema drives what the SQL can see, a renamed field that is missing in one batch will break the query for that batch.
- Explicit aliases: Alias every output field, so the schema does not depend on the source's naming.
- Type of computed fields: Check the type that expressions return, since a computed value may not match the target column.
- Writer schema: Confirm the Record Writer produces the field names and types the target requires.
- Missing fields: Decide how the flow handles a batch where an expected source field is absent.
- Nested data: Confirm how nested objects and arrays are flattened or accessed, which varies with the reader.
Before relying on reshaping, compare the output schema with the target's schema using a representative sample.
Aggregation uses GROUP BY with functions such as COUNT, SUM, AVG, MIN and MAX to summarize many records into fewer rows. The SQL shape groups by one or more fields and computes aggregates over each group. Illustrative example: SELECT region, COUNT(*) AS orders, SUM(total) AS revenue FROM FLOWFILE GROUP BY region. The result is computed only over the records present in that single incoming FlowFile, so the group totals are correct only if the FlowFile contains every record for each group. If your source splits a dataset across many FlowFiles, each one produces partial totals, and the flow must combine them with a separate step. Aggregates also depend on the numeric types in the schema, since a string-typed amount will not sum correctly.
- Completeness: Confirm each FlowFile holds the full population of every group you summarize, or plan a later merge step.
- Numeric types: Verify that measures are numeric in the reader schema, and check how nulls are treated by each function.
- Group keys: Confirm the grouping fields are consistently populated, since null or inconsistent keys create unexpected groups.
- Memory on large inputs: Test with realistic batch sizes, because aggregation over very large FlowFiles can exhaust the resources the processor uses. Ask your team to confirm the limits for your NiFi version in the NiFi component documentation.
- Output shape: Confirm downstream consumers expect one row per group and the field names you produce.
Before relying on aggregation, check the batch boundaries with a test that splits a dataset across FlowFiles and compare the totals.
QueryRecord configuration options and when each one fits
This table compares the QueryRecord settings that shape how SQL reads, writes and routes records, and when each setting fits. Read it before you design the queries, then confirm exact property names in the NiFi 2 component documentation for your running version. The main takeaway is that the reader, the schema source and the failure path decide most of the results you see.
| Option | What it controls | When it fits | Trade-off |
|---|---|---|---|
| Record Reader | Parses the incoming FlowFile into records that SQL can query | Structured input such as JSON, CSV or Avro with a schema the reader can supply | Field types come from the reader, so a wrong inference changes filter results |
| Record Writer | Serializes each query result into the output format | Matching the format that the next processor or target expects | Changing the format changes the schema that downstream steps must accept |
| Dynamic properties | Each property name becomes an outgoing relationship, and its SQL value selects rows or aggregates | Several filters or summaries from one pass over the same input | Each query scans the input again, and every relationship needs a connection or termination |
| Schema access (via the reader) | Where the field definitions come from, such as inference or a schema registry | Inference for exploratory flows; a registry for stable production schemas | Inference can read sparse fields as null or string and shift query behavior |
| Cache Schema | Reuses the parsed schema across FlowFiles instead of parsing it each time | High volume with a stable upstream schema | A cached schema goes stale when upstream fields change, so reset it after drift |
| Include Zero Record FlowFiles | Controls whether an empty query result still produces an output FlowFile | Downstream steps that expect a file even when no rows match, such as audit or heartbeat paths | Empty files reach targets and add noise; if off, downstream checks must handle missing outputs |
| Default decimal precision and scale | Sets the precision and scale for decimal results such as SUM or AVG | Aggregating money or measurement fields where totals must stay exact | Too little precision truncates totals; confirm defaults and test with real magnitudes |
| Failure path | Sends the original FlowFile to failure when a query has a syntax error, unknown column or type mismatch | Always wiring failure to an alert or quarantine path | A failed FlowFile retries forever if it routes back into the same query unchanged |
| Original relationship | Passes the unmodified input when queries complete | Keeping raw data for provenance, replay or audit | The raw copy stays in the queue, adding storage use and back pressure exposure |
How one QueryRecord processor fans out to several relationships
Each dynamic property you add to a QueryRecord processor becomes its own outgoing relationship, named after the property. The property name is the relationship name, and its value is the SQL statement that runs against the incoming FlowFile's records. A single processor can therefore split one input into several result sets, for example one relationship for valid orders, one for high-value orders and one for a per-region aggregate. Each matching query writes its output as a new FlowFile on its own relationship. Name the properties for the downstream consumer, because those names will appear in connection labels, bulletins and the flow diagram that your operators read.
Records that match no query need an explicit decision. Each query only sends the rows its SQL selects, so any record outside every WHERE clause does not appear on a query relationship. Designers who expect every row to land somewhere should add a catch-all query, such as one selecting the complement of the other conditions, and wire it to a holding path. Confirm in your NiFi version's component documentation for QueryRecord how the processor reports a query that fails to parse or evaluate, since that failure path is separate from empty results. Wire the failure relationship to a dead-letter or alerting branch, and do not leave it unconnected, because an unconnected relationship causes NiFi to drop FlowFiles routed to it.
For provenance and FlowFile counts, expect one input FlowFile to produce several output FlowFiles, and expect an output FlowFile to be created even when a query returns no rows, depending on the processor settings you confirm. Provenance then shows a fork event rather than a one-to-one transformation, so audit queries and lineage checks should follow every child. Keep a count reconciliation step in the design: compare records in against records across all query relationships plus the catch-all, and alert on any difference. Plan back pressure on each query connection separately, because a slow downstream path can hold up one relationship while others drain. Verify these behaviors with a representative test flow before production.
How to set up QueryRecord in a NiFi 2 flow, step by step
This sequence takes a flow from a confirmed NiFi 2 runtime to a tested QueryRecord processor with one SQL query per output, so each query result can be traced through provenance before the flow reaches production targets.
Confirm the NiFi 2 runtime
Check that the cluster or standalone instance runs a NiFi 2 release on a supported Java runtime. The NiFi administration guide lists Java 21 as the minimum runtime, so confirm the JDK version on every node before you add the processor. Open the About dialog in the UI or read the version from the installation, and confirm that the target environment matches what your team has documented. Done when every node reports the same NiFi 2 version and the processor type QueryRecord appears in the Add Processor dialog.
Choose record readers and writers
Decide the input format (for example JSON, CSV or Avro) and the format each output should carry. Add a RecordReader controller service that matches the input and a RecordWriter controller service for each output format, then enable both services. Set schema access to a schema the team has agreed, or to schema inference if the data is well understood and stable. Done when each controller service shows the enabled state and a sample file parses without errors in a test flow, which you can check by viewing the record count in the processor statistics.
Add QueryRecord and set the reader and writer
Place a QueryRecord processor on the canvas, connect it to the upstream processor that delivers FlowFiles, and set its Record Reader and Record Writer properties to the controller services from the previous step. Leave the query properties empty until the next step so that validation shows only the configuration you intend to add. Done when the processor's validation panel shows no errors for the reader and writer, and the upstream connection is not blocked.
Define one SQL query per output
Add a dynamic property for each output you need. The property name becomes the relationship name, and the value is a SQL statement that reads from the FlowFile, typically from a table named FLOWFILE. Write one filter, route or aggregation per property, and rename fields with aliases where downstream systems expect different names. Done when every dynamic property appears as its own relationship in the processor configuration and the validator accepts each statement. Confirm the exact SQL dialect and function support for your NiFi 2 release in the component documentation before relying on a function.
Wire each query relationship
Connect each named query relationship to its own downstream path, such as a processor that writes to a target or a PutFile for review. Also connect the original failure relationship to an error path so malformed records are not lost. Give each connection a clear name or label so that operators can tell the outputs apart. Done when every query relationship and the failure relationship has a destination, and the processor shows no unconnected relationships in the canvas status.
Test with sample files
Run a set of representative sample files through the flow, including rows that should match each query, rows that should match none, and at least one malformed record. Compare the record counts on each output with the counts you expect from the source data, and inspect the content of a few FlowFiles to confirm field names, types and aggregation results. Done when the counts and sample contents match your expectations for every query, and the malformed input reaches the failure path. Treat any gap as a defect in the query or the schema before moving on.
Check provenance and record the outcome
Open the data provenance view for a sample run and confirm that each FlowFile shows a ROUTE or equivalent event to the expected query relationship, with the parent and child lineage visible. Note the query text, reader and writer versions, and the sample data used, so the test can be repeated after changes. Done when provenance shows the expected routing for each sample and the test record is stored with the flow change. The exact steps depend on your environment, including your cluster topology, access policies and how your team deploys flows, so confirm each step against your own configuration and documentation.
Where QueryRecord hits limits on large inputs and what to confirm
QueryRecord runs SQL over each FlowFile's records in memory-bound batches, so its cost depends on input size, key cardinality, query shape and schema handling. The cards below are limits to confirm by testing with representative data in your own environment, not fixed thresholds.
Memory pressure from large FlowFiles
QueryRecord reads the incoming FlowFile's records to evaluate SQL, so a very large FlowFile can push heap use up on the node that runs it. Test with the largest FlowFiles you expect, including peak days and bursts, and watch JVM heap and garbage collection while the processor runs. If inputs are large, consider splitting or merging upstream so each FlowFile stays within a size you have measured.
Aggregation on high-cardinality keys
A GROUP BY over a key with many distinct values keeps one aggregate state per key in memory until the query finishes. Measure how many distinct keys a typical and peak FlowFile contains, and compare the group count with the heap you have allocated. Keys such as session or device identifiers often behave very differently from category fields, so test with real key distributions.
Query complexity and nested logic
Joins, subqueries and multiple aggregates in one statement add work per record and make failures harder to trace. A single query that combines several filters and aggregates can be slower than the same logic split across processors. Benchmark the full query against a simplified version, and record how long each relationship takes on the same input.
Schema inference on variable input
When the reader infers schema, every FlowFile may be scanned again, and inconsistent field types can cause unexpected casts or failures. Fixing an explicit schema in the record reader usually makes behavior predictable and cheaper to test. Confirm how your reader handles missing fields, nulls and type changes in the sources you actually receive.
Per-record cost against other record processors
SQL evaluation adds overhead per record compared with simpler record processors such as those that filter by a single expression or update fields in place. For simple renames or single-condition routing, a lighter processor may be enough. Run the same input through QueryRecord and the alternative you are considering, and compare throughput and heap use before you choose.
How to test QueryRecord on representative data before production
Test QueryRecord on a sample that matches production record shape, not on a few hand-written rows. Build the test set from a real export or a masked copy of production data, and keep three properties intact: average record size, the number of fields, and the cardinality of the columns your WHERE clauses and GROUP BY statements use. A filter on a field with ten distinct values behaves very differently in aggregation from one on a field with ten million, and a test file with a few hundred records will hide heap pressure that appears only when a FlowFile carries tens of thousands of rows. Confirm with your data owners which fields are sensitive and how they will be masked before any extract leaves its source system.
Measure throughput and heap use on a cluster sized like production, with the same Record Reader and Record Writer controller services, the same number of query relationships on the processor, and the same upstream batching. Watch the JVM heap and garbage collection during peak load, the time each FlowFile spends in the processor, and the queue depth in front of it. Run the test with at least one realistic input size above the point where your merge settings produce outputs, since a large single FlowFile is the case that most often exhausts memory. Record your own results, because published throughput figures depend heavily on hardware, record width and writer choice, and should be treated as a starting point only.
Compare QueryRecord with the alternatives on the same data before you choose it. Run the same reshaping through UpdateRecord, which is better suited to per-field changes, and through JoltTransformRecord if the job is mainly restructuring nested JSON. Compare output correctness, not only speed: check that renamed fields, null handling and type coercion match the expected schema. Keep the results in a short decision record that names the test data version, NiFi version, cluster size and measured numbers, so a later change in volume can be checked against the same baseline.
Security and operational considerations for SQL in NiFi flows
A QueryRecord SQL statement is code that reads and reshapes live data, so it needs the same controls as any other flow logic. The cards below cover who can change query text, what queries expose in provenance and logs, how malformed input is handled, how query changes move through versioned flows, and how to watch for queries that fail or route unexpectedly.
Who can edit query text
Query text is part of the flow definition, so anyone with permission to modify a processor can change the SQL that runs against production records. Review which NiFi users hold write access to process groups that contain QueryRecord, and confirm that the authentication provider in use (single-user, LDAP, OpenID Connect, SAML 2.0 or X.509 client certificates, per the administration guide) maps each person to a named account. Check that edit rights are separated from rights to start, stop and view data.
Sensitive fields in provenance
When a query selects or filters on personal or financial fields, the values pass through FlowFile content and can appear in provenance events and in data viewers. Decide which fields a query may read, and confirm who can view provenance and FlowFile content for that process group. Where a query only needs a subset of columns, project those columns in the first query so that later steps and queues never hold the rest.
Logs that echo query results
Processor bulletins and application logs can record error messages that include row values, especially when a type conversion or a failed predicate names the offending field. Review the logging level for the processor's logger and the content of bulletins that operators see on the canvas. Treat log retention as part of the data handling decision, because a failed query may write the same values that the flow was designed to keep out of logs.
Malformed and unexpected input
QueryRecord depends on the record reader's schema. A FlowFile with missing fields, unexpected types or nested structures may fail to parse or may match no query at all, depending on the reader settings. Decide whether a parse failure goes to the failure relationship or is retried, and confirm that a FlowFile matching no query is routed somewhere visible rather than silently dropped. Test the flow with deliberately broken samples before promotion.
Change control for SQL in versioned flows
When flows are versioned in a Git repository through a Flow Registry Client (GitHub and GitLab are supported in NiFi 2), each change to a query becomes a commit that can be reviewed and reverted. Require review of every SQL change, keep test samples with the flow, and tag the version that runs in production. Confirm which parameters or variables hold environment-specific values so that a query change is not mixed with a change to a connection string.
Monitoring failed and misrouted queries
Route counts are the first signal that a query has changed behavior. Track FlowFiles per relationship for each QueryRecord processor, alert when the failure or unmatched output rises above its normal level, and compare record counts between input and each query output over time. Build these checks before the first production run, since a predicate that quietly matches fewer rows is harder to find after the data has been loaded downstream.
Questions buyers ask about QueryRecord cost, effort and fit
Does NiFi itself carry a license cost for a QueryRecord build?
No. Apache NiFi is an Apache Software Foundation project released under the Apache License 2.0, and the project does not offer a paid edition. Your costs sit elsewhere: the infrastructure NiFi runs on, the people who build and operate flows, and any support retainer you choose. Check the license of any extension or commercial component you add separately, since the license of NiFi does not cover those. You can confirm the license terms on the NiFi download page.
What drives the effort of a QueryRecord build?
The main driver is how many distinct queries the flow needs and how well the input is understood. Each query relationship is a SQL statement that must be written, reviewed and tested against real records. Effort rises with the number of output routes, the number of fields that need renaming or type casting, aggregations that group across records, and the number of source formats feeding the processor. Schema definition also matters: a clean, documented schema shortens the build, while a source that changes shape without notice adds rework. A quote should list the queries, the input formats, the target systems and the aggregation logic as separate line items.
Can our internal team build and run QueryRecord flows without outside help?
Often yes, if the team already knows NiFi and SQL and has time to test. The skills that matter most are record readers and writers, schema management, SQL aggregation behavior and NiFi operations such as back pressure and provenance review. Teams new to NiFi usually need a few weeks of learning before they can judge whether a query is correct, so the decision depends on existing skills and capacity. A useful self-assessment is whether someone on the team can write a test plan with known good and known bad inputs and explain the output of each query. A partner can fill the gap for the build, for handover training, or for a review of a flow your team has already written.
How long does testing take, and what changes it?
Testing time follows input variety more than query count. A flow fed by one well-behaved CSV export needs a short test set. A flow that receives JSON from several sources, with optional fields, nulls, mixed number formats and late-arriving records, needs a larger matrix of sample inputs and expected outputs. Aggregations need extra checks for empty groups, duplicate keys and boundary values. Testing also covers the limits of the processor on large inputs, which are measured with volumes that match production, not with small samples. Ask for the test matrix and the acceptance criteria in the written scope, so the duration is tied to named cases rather than a general estimate.
What do we need to provide before the work starts?
The build needs representative sample data, the schema or a description of each source format, the destination definitions and the rules that decide routing and aggregation. Samples should include normal records, malformed records, missing fields and the largest volume you expect in one batch. You also need to name the people who approve the output and the test environment where flows will run. Security requirements, such as which systems may receive data and how credentials are supplied, should be written down before build starts. Provide these early, because missing samples are the most common cause of rework in SQL-based flows.
Is QueryRecord a good fit for very large inputs?
It depends on the size of each FlowFile, the complexity of the SQL and the memory available to the NiFi nodes. QueryRecord processes records in a FlowFile, so very large single files and heavy aggregations can strain heap and slow the flow. Before committing, measure the largest realistic batch on hardware that matches production, and check whether a split or merge step should bound batch size. The NiFi administration guide covers JVM and repository settings that affect throughput. Confirm the processor's current behavior and property limits in the component documentation for your NiFi version, since these details change across releases.
Should we plan an upgrade to NiFi 2 before building QueryRecord flows?
Yes, if the flows will run for years. NiFi 1.28 is the last minor release of the version 1 series, and the project strongly encourages users to upgrade to NiFi 2. The NiFi project explains that 1.x cannot be maintained further because it depends on Jetty 9.4, Spring Framework 5.3 and AngularJS 1.8, which cannot be upgraded in that line. Building new SQL flows on a version that is leaving support adds a second project later. Ask the partner to confirm the target NiFi version and the flow configuration format in the scope, and to list any processor behavior that differs between versions.
How DeployOpen scopes and delivers QueryRecord flows
A QueryRecord build starts with a written scope that names each query, its input schema, its output schema and the relationship each result feeds. DeployOpen agrees that scope before work begins, so the reader can see which filters, renamed fields and aggregations are in the build and which are not. Before writing any SQL, DeployOpen asks the client for representative sample FlowFiles and the schemas behind them, including the Record Reader setting, the field types and how null values appear. Those samples decide whether a query such as SELECT ... GROUP BY is practical on the real volume, and they show where the type coercion in the reader will differ from what the source system sends.
Queries are then built and tested in the client's own NiFi environment, not in a copy held by DeployOpen. The team checks each dynamic relationship in QueryRecord against its expected rows, confirms that a failed query routes to the intended failure path, and runs the flow against samples that include malformed and empty inputs. Where a query aggregates across a large FlowFile, the test set includes a volume close to production so the memory and run time of the processor are measured rather than assumed. Ask for the test evidence as part of the handover, and confirm which Record Reader and Record Writer versions the client's NiFi release supports.
DeployOpen documents the flow with the query text, the schema assumptions, the relationship map, the controller service settings and the known limits, so an internal team can change a query later without reverse-engineering it. Ongoing support, upgrades and testing of the flow can be taken on through a retainer, which suits queries that will change as source fields change. Ask any provider to state in the quote which items are one-time (scoping, build, testing, documentation) and which recur (support hours, schema review and updates after a NiFi upgrade), and to name who in your organization approves changes to the queries.
