Execute provided HiveQL SELECT query against a Hive database connection. Query result will be converted to Avro or CSV 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. FlowFile attribute 'selecthiveql.row.count' indicates how many rows were selected.
hive, sql, select, jdbc, query, database
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 NiFi Expression Language.
| Display Name | API Name | Default Value | Allowable Values | Description |
|---|---|---|---|---|
| Hive Database Connection Pooling Service | hive3-dbcp-service | Controller Service API: Hive3DBCPService Implementation: Hive3ConnectionPool | The Hive Controller Service that is used to obtain connection(s) to the Hive database | |
| HiveQL Pre-Query | hive-pre-query | HiveQL pre-query to execute. Semicolon-delimited list of queries. Example: 'set tez.queue.name=queue1; set hive.exec.orc.split.strategy=ETL; set hive.exec.reducers.bytes.per.reducer=1073741824'. Note, the results/outputs of these queries will be suppressed if successfully executed. Supports Expression Language: true (will be evaluated using flow file attributes and Environment variables) | ||
| HiveQL Select Query | hive-query | HiveQL SELECT query to execute. If this is not set, the query is assumed to be in the content of an incoming FlowFile. Supports Expression Language: true (will be evaluated using flow file attributes and Environment variables) | ||
| HiveQL Post-Query | hive-post-query | HiveQL post-query to execute. Semicolon-delimited list of queries. Note, the results/outputs of these queries will be suppressed if successfully executed. Supports Expression Language: true (will be evaluated using flow file attributes and Environment variables) | ||
| Fetch Size | hive-fetch-size | 0 | The number of result rows to be fetched from the result set at a time. This is a hint to the driver and may not be honored and/or exact. If the value specified is zero, then the hint is ignored. Supports Expression Language: true (will be evaluated using flow file attributes and Environment variables) | |
| Query timeout | hive3-query-timeout | 0 | Sets the number of seconds the driver will wait for a query to execute. A value of 0 means no timeout. NOTE: Non-zero values may not be supported by the driver. Supports Expression Language: true (will be evaluated using flow file attributes and Environment variables) | |
| Max Rows Per Flow File | hive-max-rows | 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 flow file attributes and Environment variables) | |
| Maximum Number of Fragments | hive-max-frags | 0 | The maximum number of fragments. If the value specified is zero, then all fragments are returned. This prevents OutOfMemoryError when this processor ingests huge table. Supports Expression Language: true (will be evaluated using flow file attributes and Environment variables) | |
| Output Format | hive-output-format | Avro |
| How to represent the records coming from Hive (Avro, CSV, e.g.) |
| Normalize Table/Column Names | hive-normalize-avro | 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 | use-logical-types | false |
| Whether to use Avro Logical Types for DECIMAL, DATE and TIMESTAMP columns. If disabled, written as string. If enabled, Logical types are used and written as its underlying type, specifically, DECIMAL as logical 'decimal': written as bytes with additional precision and scale meta data, DATE as logical 'date': written as int denoting days since Unix epoch (1970-01-01), 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. |
| CSV Header | csv-header | true |
| Include Header in Output |
| Alternate CSV Header | csv-alt-header | Comma separated list of header fields Supports Expression Language: true (will be evaluated using flow file attributes and Environment variables) | ||
| CSV Delimiter | csv-delimiter | , | CSV Delimiter used to separate fields Supports Expression Language: true (will be evaluated using flow file attributes and Environment variables) | |
| CSV Quote | csv-quote | true |
| Whether to force quoting of CSV fields. Note that this might conflict with the setting for CSV Escape. |
| CSV Escape | csv-escape | true |
| Whether to escape CSV strings in output. Note that this might conflict with the setting for CSV Quote. |
| Character Set | hive3-charset | UTF-8 | Specifies the character set of the record data. |
| Name | Description |
|---|---|
| success | Successfully created FlowFile from HiveQL query result set. |
| failure | HiveQL query execution failed. Incoming FlowFile will be penalized and routed to this relationship. |
| Name | Description |
|---|---|
| mime.type | Sets the MIME type for the outgoing flowfile to application/avro-binary for Avro or text/csv for CSV. |
| filename | Adds .avro or .csv to the filename attribute depending on which output format is selected. |
| selecthiveql.row.count | Indicates how many rows were selected/returned by the query. |
| selecthiveql.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. |
| selecthiveql.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. |
| selecthiveql.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. |
| 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. |
| 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 |
| query.input.tables | Contains input table names in comma delimited 'databaseName.tableName' format. |