Description and usage of ExecuteSQL processor:
Executes provided SQL select query. Query result will be converted to Avro format. Streaming is used so arbitrarily large result sets are supported. This processor can be scheduled to run on a timer, or cron expression, using the standard scheduling methods, or it can be triggered by an incoming FlowFile. If it is triggered by an incoming FlowFile, then attributes of that FlowFile will be available when evaluating the select query, and the query may use the ? to escape parameters. In this case, the parameters to use must exist as FlowFile attributes with the naming convention sql.args.N.type and sql.args.N.value, where N is a positive integer. The sql.args.N.type is expected to be a number indicating the JDBC Type. The content of the FlowFile is expected to be in UTF-8 format. FlowFile attribute ‘executesql.row.count’ indicates how many rows were selected.
Tags:
sql, select, jdbc, query, database
Properties:
In the list below, the names of required properties appear in bold. Any other properties (not in bold) are considered optional. The table also indicates any default values, and whether a property supports the Expression Language Guide.
| Name | Default Value | Allowable Values | Description |
| Database Connection Pooling Service |
Controller Service API: DBCPService Implementation: DBCPConnectionPool DBCPConnectionPoolLookup HiveConnectionPool |
The Controller Service that is used to obtain connection to database | |
| SQL Pre-Query |
A semicolon-delimited list of queries executed before the main SQL query is executed. For example, set session properties before main query. Results/outputs from these queries will be suppressed if there are no errors. Supports Expression Language: true (will be evaluated using flow file attributes and variable registry) |
||
| SQL select query |
The SQL select query to execute. The query can be empty, a constant value, or built from attributes using Expression Language. If this property is specified, it will be used regardless of the content of incoming flowfiles. If this property is empty, the content of the incoming flow file is expected to contain a valid SQL select query, to be issued by the processor to the database. Note that Expression Language is not evaluated for flow file contents. Supports Expression Language: true (will be evaluated using flow file attributes and variable registry) |
||
| SQL Post-Query |
A semicolon-delimited list of queries executed after the main SQL query is executed. Example like setting session properties after main query. Results/outputs from these queries will be suppressed if there are no errors. Supports Expression Language: true (will be evaluated using flow file attributes and variable registry) |
||
| Max Wait Time | 0 seconds | 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. | |
| Normalize Table/Column Names | false |
|
Whether to change non-Avro-compatible characters in column names to Avro-compatible characters. For example, colons and periods will be changed to underscores in order to build a valid Avro record. |
| Use Avro Logical Types | false |
|
Whether to use Avro Logical Types for DECIMAL/NUMBER, DATE, TIME and TIMESTAMP columns. If disabled, written as string. If enabled, Logical types are used and written as its underlying type, specifically, DECIMAL/NUMBER as logical 'decimal': written as bytes with additional precision and scale meta data, DATE as logical 'date-millis': written as int denoting days since Unix epoch (1970-01-01), TIME as logical 'time-millis': written as int denoting milliseconds since Unix epoch, and TIMESTAMP as logical 'timestamp-millis': written as long denoting milliseconds since Unix epoch. If a reader of written Avro records also knows these logical types, then these values can be deserialized with more context depending on reader implementation. |
| Compression Format | NONE |
|
Compression type to use when writing Avro files. Default is None. |
| Default Decimal Precision | 10 |
When a DECIMAL/NUMBER value is written as a 'decimal' Avro logical type, a specific 'precision' denoting number of available digits is required. Generally, precision is defined by column data type definition or database engines default. However undefined precision (0) can be returned from some database engines. 'Default Decimal Precision' is used when writing those undefined precision numbers. Supports Expression Language: true (will be evaluated using flow file attributes and variable registry) |
|
| Default Decimal Scale | 0 |
When a DECIMAL/NUMBER value is written as a 'decimal' Avro logical type, a specific 'scale' denoting number of available decimal digits is required. Generally, scale is defined by column data type definition or database engines default. However when undefined precision (0) is returned, scale can also be uncertain with some database engines. 'Default Decimal Scale' is used when writing those undefined numbers. If a value has more decimals than specified scale, then the value will be rounded-up, e.g. 1.53 becomes 2 with scale 0, and 1.5 with scale 1. Supports Expression Language: true (will be evaluated using flow file attributes and variable registry) |
|
| Max Rows Per Flow File | 0 |
The maximum number of result rows that will be included in a single FlowFile. This will allow you to break up very large result sets into multiple FlowFiles. If the value specified is zero, then all rows are returned in a single FlowFile. Supports Expression Language: true (will be evaluated using variable registry only) |
|
| Output Batch Size | 0 |
The number of output FlowFiles to queue before committing the process session. When set to zero, the session will be committed when all result set rows have been processed and the output FlowFiles are ready for transfer to the downstream relationship. For large result sets, this can cause a large burst of FlowFiles to be transferred at the end of processor execution. If this property is set, then when the specified number of FlowFiles are ready for transfer, then the session will be committed, thus releasing the FlowFiles to the downstream relationship. NOTE: The fragment.count attribute will not be set on FlowFiles when this property is set. Supports Expression Language: true (will be evaluated using variable registry only) |
Relationships:
| Name | Description |
| success | Successfully created FlowFile from SQL query result set. |
| failure | SQL query execution failed. Incoming FlowFile will be penalized and routed to this relationship |
Reads Attributes:
| Name | Description |
| sql.args.N.type | Incoming FlowFiles are expected to be parametrized SQL statements. The type of each Parameter is specified as an integer that represents the JDBC Type of the parameter. |
| sql.args.N.value | Incoming FlowFiles are expected to be parametrized SQL statements. The value of the Parameters are specified as sql.args.1.value, sql.args.2.value, sql.args.3.value, and so on. The type of the sql.args.1.value Parameter is specified by the sql.args.1.type attribute. |
| sql.args.N.format | This attribute is always optional, but default options may not always work for your data. Incoming FlowFiles are expected to be parametrized SQL statements. In some cases a format option needs to be specified, currently this is only applicable for binary data types, dates, times and timestamps. Binary Data Types (defaults to 'ascii') - ascii: each string character in your attribute value represents a single byte. This is the format provided by Avro Processors. base64: the string is a Base64 encoded string that can be decoded to bytes. hex: the string is hex encoded with all letters in upper case and no '0x' at the beginning. Dates/Times/Timestamps - Date, Time and Timestamp formats all support both custom formats or named format ('yyyy-MM-dd','ISO_OFFSET_DATE_TIME') as specified according to java.time.format.DateTimeFormatter. If not specified, a long value input is expected to be an unix epoch (milliseconds from 1970/1/1), or a string value in 'yyyy-MM-dd' format for Date, 'HH:mm:ss.SSS' for Time (some database engines e.g. Derby or MySQL do not support milliseconds and will truncate milliseconds), 'yyyy-MM-dd HH:mm:ss.SSS' for Timestamp is used. |
Writes Attributes:
| Name | Description |
| executesql.row.count | Contains the number of rows returned by the query. If 'Max Rows Per Flow File' is set, then this number will reflect the number of rows in the Flow File instead of the entire result set. |
| executesql.query.duration | Combined duration of the query execution time and fetch time in milliseconds. If 'Max Rows Per Flow File' is set, then this number will reflect only the fetch time for the rows in the Flow File instead of the entire result set. |
| executesql.query.executiontime | Duration of the query execution time in milliseconds. This number will reflect the query execution time regardless of the 'Max Rows Per Flow File' setting. |
| executesql.query.fetchtime | Duration of the result set fetch time in milliseconds. If 'Max Rows Per Flow File' is set, then this number will reflect only the fetch time for the rows in the Flow File instead of the entire result set. |
| executesql.resultset.index | Assuming multiple result sets are returned, the zero based index of this result set. |
| executesql.error.message | If processing an incoming flow file causes an Exception, the Flow File is routed to failure and this attribute is set to the exception message. |
| fragment.identifier | If 'Max Rows Per Flow File' is set then all FlowFiles 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. |
| fragment.count | If 'Max Rows Per Flow File' is set then 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. If Output Batch Size is set, then this attribute will not be populated. |
| fragment.index | If 'Max Rows Per Flow File' is set then the position of this FlowFile in the list of outgoing FlowFiles that were all derived from the same result set FlowFile. This can be used in conjunction with the fragment.identifier attribute to know which FlowFiles originated from the same query result set and in what order FlowFiles were produced |
State management:
This component does not store state.
Restricted:
This component is not restricted.
Input requirement:
This component allows an incoming relationship.
System Resource Considerations:
None specified.
How to configure?
Step 1: Drag and drop the ExecuteSQL processor to canvas.
Step 2: Double click the processor to configure, the configuration dialog will be opened as follows,
Step 3: Check the usage of each property and update those values.
Properties and usage
Database Connection Pooling Service: Enters the JDBC connection pool to be used.
SQL select query: Enters the select query to retrieve data from table.
Max Wait Time: It is used to specify maximum wait time for running the SQL select query.
Normalize Table/Column Names: It is used to specify whether change non- Avro compatible column names to Avro compatible names.
UseAvro Logical Types: Used to specify whether avro logical types can be used for decimal or number, date, time, and timestamp. By default, these types are disabled, so it can be written as string.
Default Decimal Precision: Used to specify ‘precision’ which denotes number of available digits when decimal or number value in flow file is written as a ‘decimal’ avro logical type.
Default Decimal Scale: Used to specify ‘scale’ which denotes number of available decimal digits when decimal or number value in flow file is written as a ‘decimal’ avro logical type. If available decimal digits are more than the scale value then it will be rounded up. For example, 1.53 becomes 2 with scale 0.
Example:
For example, to get updated employees details from ‘employees’ table, configure ExecuteSQL shown in the following screenshot,
