DBCPService
Implementations: Hive3ConnectionPool
HikariCPConnectionPool
HadoopDBCPConnectionPool
DBCPConnectionPool
DBCPConnectionPoolLookup
SnowflakeComputingConnectionPool
The Controller Service that is used to obtain connection to database | Catalog | list-db-tables-catalog | | | The name of a catalog from which to list database tables. The name must match the catalog name as it is stored in the database. If the property is not set, the catalog name will not be used to narrow the search for tables. If the property is set to an empty string, tables without a catalog will be listed. |
Schema Pattern | list-db-tables-schema-pattern | | | A pattern for matching schemas in the database. Within a pattern, "%" means match any substring of 0 or more characters, and "_" means match any one character. The pattern must match the schema name as it is stored in the database. If the property is not set, the schema name will not be used to narrow the search for tables. If the property is set to an empty string, tables without a schema will be listed. |
Table Name Pattern | list-db-tables-name-pattern | | | A pattern for matching tables in the database. Within a pattern, "%" means match any substring of 0 or more characters, and "_" means match any one character. The pattern must match the table name as it is stored in the database. If the property is not set, all tables will be retrieved. |
Table Types | list-db-tables-types | TABLE | | A comma-separated list of table types to include. For example, some databases support TABLE and VIEW types. If the property is not set, tables of all types will be returned. |
Include Count | list-db-include-count | false | | Whether to include the table's row count as a flow file attribute. This affects performance as a database query will be generated for each table in the retrieved list. |
Record Writer | record-writer | | Controller Service API: RecordSetWriterFactory Implementations: XMLRecordSetWriter FreeFormTextRecordSetWriter AvroRecordSetWriter ScriptedRecordSetWriter JsonRecordSetWriter ParquetRecordSetWriter RecordSetWriterLookup CSVRecordSetWriter | Specifies the Record Writer to use for creating the listing. If not specified, one FlowFile will be created for each entity that is listed. If the Record Writer is specified, all entities will be written to a single FlowFile instead of adding attributes to individual FlowFiles. |
Refresh Interval | list-db-refresh-interval | 0 sec | | The amount of time to elapse before resetting the processor state, thereby causing all current tables to be listed. During this interval, the processor may continue to run, but tables that have already been listed will not be re-listed. However new/added tables will be listed as the processor runs. A value of zero means the state will never be automatically reset, the user must Clear State manually. |
Relationships:
Name | Description |
---|
success | All FlowFiles that are received are routed to success |
Reads Attributes:
None specified.Writes Attributes:
Name | Description |
---|
db.table.name | Contains the name of a database table from the connection |
db.table.catalog | Contains the name of the catalog to which the table belongs (may be null) |
db.table.schema | Contains the name of the schema to which the table belongs (may be null) |
db.table.fullname | Contains the fully-qualifed table name (possibly including catalog, schema, etc.) |
db.table.type | Contains the type of the database table from the connection. Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM" |
db.table.remarks | Contains the name of a database table from the connection |
db.table.count | Contains the number of rows in the table |
State management:
Scope | Description |
---|
CLUSTER | After performing a listing of tables, the timestamp of the query is stored. This allows the Processor to not re-list tables the next time that the Processor is run. Specifying the refresh interval in the processor properties will indicate that when the processor detects the interval has elapsed, the state will be reset and tables will be re-listed as a result. This processor is meant to be run on the primary node only. |
Restricted:
This component is not restricted.Input requirement:
This component does not allow an incoming relationship.Example Use Cases Involving Other Components:
Use Case:
Perform a full load of a database, retrieving all rows from all tables, or a specific set of tables.
Keywords:
full load, rdbms, jdbc, databaseComponents involved:
Component Type: org.apache.nifi.processors.standard.ListDatabaseTablesConfiguration:
Configure the "Database Connection Pooling Service" property to specify a Connection Pool that is applicable for interacting with your database.
Leave the RecordWriter property unset.
Set the "Catalog" property to the name of the database Catalog; leave it empty to include all catalogs.
Set the "Schema Pattern" property to a Java Regular Expression that matches all database Schemas that should be included; leave it empty to include all Schemas.
Set the "Table Name Pattern" property to a Java Regular Expression that matches the names of all tables that should be included; leave it empty to include all Tables.
Connect the "success" relationship to GenerateTableFetch.
Component Type: org.apache.nifi.processors.standard.GenerateTableFetchConfiguration:
Configure the "Database Connection Pooling Service" property to specify the same Connection Pool that was used in ListDatabaseTables.
Set the "Database Type" property to match the appropriate value for your RDBMS vendor.
Set "Table Name" to ${db.table.fullname}
Leave the RecordWriter property unset.
Connect the "success" relationship to ExecuteSQLRecord.
Component Type: org.apache.nifi.processors.standard.ExecuteSQLRecordConfiguration:
Configure the "Database Connection Pooling Service" property to specify the same Connection Pool that was used in ListDatabaseTables.
Configure the "Record Writer" property to specify a Record Writer that is appropriate for the desired output data type.
Leave the "SQL select query" unset.
Connect the "success" relationship to the next Processor in the flow.
System Resource Considerations:
None specified.