| <!DOCTYPE html><html lang="en"><head><meta charset="utf-8"></meta><title>GenerateTableFetch</title><link rel="stylesheet" href="../../../../../css/component-usage.css" type="text/css"></link></head><script type="text/javascript">window.onload = function(){if(self==top) { document.getElementById('nameHeader').style.display = "inherit"; } }</script><body><h1 id="nameHeader" style="display: none;">GenerateTableFetch</h1><h2>Description: </h2><p>Generates SQL select queries that fetch "pages" of rows from a table. The partition size property, along with the table's row count, determine the size and number of pages and generated FlowFiles. In addition, incremental fetching can be achieved by setting Maximum-Value Columns, which causes the processor to track the columns' maximum values, thus only fetching rows whose columns' values exceed the observed maximums. This processor is intended to be run on the Primary Node only. |
| This processor can accept incoming connections; the behavior of the processor is different whether incoming connections are provided: |
| - If no incoming connection(s) are specified, the processor will generate SQL queries on the specified processor schedule. Expression Language is supported for many fields, but no FlowFile attributes are available. However the properties will be evaluated using the Variable Registry. |
| - If incoming connection(s) are specified and no FlowFile is available to a processor task, no work will be performed. |
| - If incoming connection(s) are specified and a FlowFile is available to a processor task, the FlowFile's attributes may be used in Expression Language for such fields as Table Name and others. However, the Max-Value Columns and Columns to Return fields must be empty or refer to columns that are available in each specified table.</p><p><a href="additionalDetails.html">Additional Details...</a></p><h3>Tags: </h3><p>sql, select, jdbc, query, database, fetch, generate</p><h3>Properties: </h3><p>In the list below, the names of required properties appear in <strong>bold</strong>. Any other properties (not in bold) are considered optional. The table also indicates any default values, and whether a property supports the <a href="../../../../../html/expression-language-guide.html">NiFi Expression Language</a>.</p><table id="properties"><tr><th>Display Name</th><th>API Name</th><th>Default Value</th><th>Allowable Values</th><th>Description</th></tr><tr><td id="name"><strong>Database Connection Pooling Service</strong></td><td>Database Connection Pooling Service</td><td></td><td id="allowable-values"><strong>Controller Service API: </strong><br/>DBCPService<br/><strong>Implementations: </strong><a href="../../../nifi-hadoop-dbcp-service-nar/1.19.1/org.apache.nifi.dbcp.HadoopDBCPConnectionPool/index.html">HadoopDBCPConnectionPool</a><br/><a href="../../../nifi-dbcp-service-nar/1.19.1/org.apache.nifi.dbcp.DBCPConnectionPoolLookup/index.html">DBCPConnectionPoolLookup</a><br/><a href="../../../nifi-dbcp-service-nar/1.19.1/org.apache.nifi.dbcp.DBCPConnectionPool/index.html">DBCPConnectionPool</a><br/><a href="../../../nifi-dbcp-service-nar/1.19.1/org.apache.nifi.dbcp.HikariCPConnectionPool/index.html">HikariCPConnectionPool</a></td><td id="description">The Controller Service that is used to obtain a connection to the database.</td></tr><tr><td id="name"><strong>Database Type</strong></td><td>db-fetch-db-type</td><td id="default-value">Generic</td><td id="allowable-values"><ul><li>Generic <img src="../../../../../html/images/iconInfo.png" alt="Generates ANSI SQL" title="Generates ANSI SQL"></img></li><li>Oracle <img src="../../../../../html/images/iconInfo.png" alt="Generates Oracle compliant SQL" title="Generates Oracle compliant SQL"></img></li><li>Oracle 12+ <img src="../../../../../html/images/iconInfo.png" alt="Generates Oracle compliant SQL for version 12 or greater" title="Generates Oracle compliant SQL for version 12 or greater"></img></li><li>MS SQL 2012+ <img src="../../../../../html/images/iconInfo.png" alt="Generates MS SQL Compatible SQL, for version 2012 or greater" title="Generates MS SQL Compatible SQL, for version 2012 or greater"></img></li><li>MS SQL 2008 <img src="../../../../../html/images/iconInfo.png" alt="Generates MS SQL Compatible SQL for version 2008" title="Generates MS SQL Compatible SQL for version 2008"></img></li><li>MySQL <img src="../../../../../html/images/iconInfo.png" alt="Generates MySQL compatible SQL" title="Generates MySQL compatible SQL"></img></li><li>PostgreSQL <img src="../../../../../html/images/iconInfo.png" alt="Generates PostgreSQL compatible SQL" title="Generates PostgreSQL compatible SQL"></img></li></ul></td><td id="description">The type/flavor of database, used for generating database-specific code. In many cases the Generic type should suffice, but some databases (such as Oracle) require custom SQL clauses. </td></tr><tr><td id="name"><strong>Table Name</strong></td><td>Table Name</td><td></td><td id="allowable-values"></td><td id="description">The name of the database table to be queried.<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong></td></tr><tr><td id="name">Columns to Return</td><td>Columns to Return</td><td></td><td id="allowable-values"></td><td id="description">A comma-separated list of column names to be used in the query. If your database requires special treatment of the names (quoting, e.g.), each name should include such treatment. If no column names are supplied, all columns in the specified table will be returned. NOTE: It is important to use consistent column names for a given table for incremental fetch to work properly.<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong></td></tr><tr><td id="name">Maximum-value Columns</td><td>Maximum-value Columns</td><td></td><td id="allowable-values"></td><td id="description">A comma-separated list of column names. The processor will keep track of the maximum value for each column that has been returned since the processor started running. Using multiple columns implies an order to the column list, and each column's values are expected to increase more slowly than the previous columns' values. Thus, using multiple columns implies a hierarchical structure of columns, which is usually used for partitioning tables. This processor can be used to retrieve only those rows that have been added/updated since the last retrieval. Note that some JDBC types such as bit/boolean are not conducive to maintaining maximum value, so columns of these types should not be listed in this property, and will result in error(s) during processing. If no columns are provided, all rows from the table will be considered, which could have a performance impact. NOTE: It is important to use consistent max-value column names for a given table for incremental fetch to work properly.<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong></td></tr><tr><td id="name"><strong>Max Wait Time</strong></td><td>Max Wait Time</td><td id="default-value">0 seconds</td><td id="allowable-values"></td><td id="description">The maximum amount of time allowed for a running SQL select query , zero means there is no limit. Max time less than 1 second will be equal to zero.<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong></td></tr><tr><td id="name"><strong>Partition Size</strong></td><td>gen-table-fetch-partition-size</td><td id="default-value">10000</td><td id="allowable-values"></td><td id="description">The number of result rows to be fetched by each generated SQL statement. The total number of rows in the table divided by the partition size gives the number of SQL statements (i.e. FlowFiles) generated. A value of zero indicates that a single FlowFile is to be generated whose SQL statement will fetch all rows in the table.<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong></td></tr><tr><td id="name">Column for Value Partitioning</td><td>gen-table-column-for-val-partitioning</td><td></td><td id="allowable-values"></td><td id="description">The name of a column whose values will be used for partitioning. The default behavior is to use row numbers on the result set for partitioning into 'pages' to be fetched from the database, using an offset/limit strategy. However for certain databases, it can be more efficient under the right circumstances to use the column values themselves to define the 'pages'. This property should only be used when the default queries are not performing well, when there is no maximum-value column or a single maximum-value column whose type can be coerced to a long integer (i.e. not date or timestamp), and the column values are evenly distributed and not sparse, for best performance.<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong></td></tr><tr><td id="name">Additional WHERE clause</td><td>db-fetch-where-clause</td><td></td><td id="allowable-values"></td><td id="description">A custom clause to be added in the WHERE condition when building SQL queries.<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong></td></tr><tr><td id="name">Custom ORDER BY Column</td><td>gen-table-custom-orderby-column</td><td></td><td id="allowable-values"></td><td id="description">The name of a column to be used for ordering the results if Max-Value Columns are not provided and partitioning is enabled. This property is ignored if either Max-Value Columns is set or Partition Size = 0. NOTE: If neither Max-Value Columns nor Custom ORDER BY Column is set, then depending on the the database/driver, the processor may report an error and/or the generated SQL may result in missing and/or duplicate rows. This is because without an explicit ordering, fetching each partition is done using an arbitrary ordering.<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong></td></tr><tr><td id="name"><strong>Output Empty FlowFile on Zero Results</strong></td><td>gen-table-output-flowfile-on-zero-results</td><td id="default-value">false</td><td id="allowable-values"><ul><li>true</li><li>false</li></ul></td><td id="description">Depending on the specified properties, an execution of this processor may not result in any SQL statements generated. When this property is true, an empty FlowFile will be generated (having the parent of the incoming FlowFile if present) and transferred to the 'success' relationship. When this property is false, no output FlowFiles will be generated.</td></tr></table><h3>Dynamic Properties: </h3><p>Supports Sensitive Dynamic Properties: <strong>No</strong></p><p>Dynamic Properties allow the user to specify both the name and value of a property.<table id="dynamic-properties"><tr><th>Name</th><th>Value</th><th>Description</th></tr><tr><td id="name">initial.maxvalue.<max_value_column></td><td id="value">Initial maximum value for the specified column</td><td>Specifies an initial max value for max value columns. Properties should be added in the format `initial.maxvalue.<max_value_column>`. This value is only used the first time the table is accessed (when a Maximum Value Column is specified). In the case of incoming connections, the value is only used the first time for each table specified in the FlowFiles.<br/><strong>Supports Expression Language: true (will be evaluated using flow file attributes and variable registry)</strong></td></tr></table></p><h3>Relationships: </h3><table id="relationships"><tr><th>Name</th><th>Description</th></tr><tr><td>success</td><td>Successfully created FlowFile from SQL query result set.</td></tr><tr><td>failure</td><td>This relationship is only used when SQL query execution (using an incoming FlowFile) failed. The incoming FlowFile will be penalized and routed to this relationship. If no incoming connection(s) are specified, this relationship is unused.</td></tr></table><h3>Reads Attributes: </h3>None specified.<h3>Writes Attributes: </h3><table id="writes-attributes"><tr><th>Name</th><th>Description</th></tr><tr><td>generatetablefetch.sql.error</td><td>If the processor has incoming connections, and processing an incoming FlowFile causes a SQL Exception, the FlowFile is routed to failure and this attribute is set to the exception message.</td></tr><tr><td>generatetablefetch.tableName</td><td>The name of the database table to be queried.</td></tr><tr><td>generatetablefetch.columnNames</td><td>The comma-separated list of column names used in the query.</td></tr><tr><td>generatetablefetch.whereClause</td><td>Where clause used in the query to get the expected rows.</td></tr><tr><td>generatetablefetch.maxColumnNames</td><td>The comma-separated list of column names used to keep track of data that has been returned since the processor started running.</td></tr><tr><td>generatetablefetch.limit</td><td>The number of result rows to be fetched by the SQL statement.</td></tr><tr><td>generatetablefetch.offset</td><td>Offset to be used to retrieve the corresponding partition.</td></tr><tr><td>fragment.identifier</td><td>All FlowFiles generated from the same query result set will have the same value for the fragment.identifier attribute. This can then be used to correlate the results.</td></tr><tr><td>fragment.count</td><td>This is the total number of FlowFiles produced by a single ResultSet. This can be used in conjunction with the fragment.identifier attribute in order to know how many FlowFiles belonged to the same incoming ResultSet.</td></tr><tr><td>fragment.index</td><td>This is the position of this FlowFile in the list of outgoing FlowFiles that were all generated from the same execution. This can be used in conjunction with the fragment.identifier attribute to know which FlowFiles originated from the same execution and in what order FlowFiles were produced</td></tr></table><h3>State management: </h3><table id="stateful"><tr><th>Scope</th><th>Description</th></tr><tr><td>CLUSTER</td><td>After performing a query on the specified table, the maximum values for the specified column(s) will be retained for use in future executions of the query. This allows the Processor to fetch only those records that have max values greater than the retained values. This can be used for incremental fetching, fetching of newly added rows, etc. To clear the maximum values, clear the state of the processor per the State Management documentation</td></tr></table><h3>Restricted: </h3>This component is not restricted.<h3>Input requirement: </h3>This component allows an incoming relationship.<h3>System Resource Considerations:</h3>None specified.<h3>See Also:</h3><p><a href="../org.apache.nifi.processors.standard.QueryDatabaseTable/index.html">QueryDatabaseTable</a>, <a href="../org.apache.nifi.processors.standard.ExecuteSQL/index.html">ExecuteSQL</a>, <a href="../org.apache.nifi.processors.standard.ListDatabaseTables/index.html">ListDatabaseTables</a></p></body></html> |
