如何使用NiFi中新的DBCPConnectionPoolLookup列出一组数据库中的数据库表? [英] How can I list database tables in a set of databases using the new DBCPConnectionPoolLookup in NiFi?
问题描述
As of NiFi 1.7.1, the new DBCPConnectionPoolLookup enables dynamic selection of database connections: set an attribute database.name on a FlowFile and when a consuming processor accesses a configured DBCPConnectionPoolLookup controller service, the content of that attribute will be used to get a connection through this lookup's configured properties, which contain a mapping of potential values to DBCPConnectionPool controller service.
I'd like to list the tables in each database that I've configured in the lookup, but the ListDatabaseTables processor does not accept incoming FlowFiles. This seems to mean that it's not usable for listing tables in a dynamic set of databases.
完成此任务的最佳方法是什么?
What is the best way to accomplish this?
推荐答案
ListDatabaseTables使用JDBC API从已建立的JDBC连接的元数据中获取表信息.这隐藏了如何从特定数据库实际获取表的基本方法.
ListDatabaseTables uses the JDBC API for getting table info from the metadata of an established JDBC connection. This hides the underlying method of how to actually get tables from a particular database.
如果所有数据库都属于同一类，那么如果您有一个数据库列表，则可以生成每个数据库一个流文件，并填充database.name属性，然后将ExecuteSQL与DBCPConnectionPoolLookup一起使用以执行相应的操作SQL语句，用于获取该数据库的表，例如SHOW TABLES.您可以使用任何记录感知处理器(例如QueryRecord，UpdateRecord，ConvertRecord等)来解析记录，如果每个流文件需要一个表，则可以使用SplitRecord.如果输出为JSON或CSV或XML，则可以分别使用EvaluateJsonPath，ExtractText或EvaluateXPath将表名转换为属性，然后从那里继续.
If all your databases are of the same ilk, then if you have a list of databases, you could generate flow files with one per database, filling in the database.name attribute, then using ExecuteSQL with the DBCPConnectionPoolLookup to execute the corresponding SQL statement to get the tables for that database, such as SHOW TABLES. You can parse the records using any of the record-aware processors such as QueryRecord, UpdateRecord, ConvertRecord, etc. and if you need one table per flow file you can use SplitRecord. If the output is JSON or CSV or XML, you could use EvaluateJsonPath, ExtractText, or EvaluateXPath respectively to get the table name into an attribute, and continue on from there.
我写了 NIFI-5519 来涵盖ListDatabaseTables提案(可选)接受传入的连接，与此同时，您需要1个ListDatabaseTables实例才能与每个DBCPConnectionPool实例相对应.
I wrote up NIFI-5519 to cover the proposal for ListDatabaseTables to optionally accept incoming connections, in the meantime you'd need 1 ListDatabaseTables instance to correspond to each of your DBCPConnectionPool instances.
这篇关于如何使用NiFi中新的DBCPConnectionPoolLookup列出一组数据库中的数据库表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
