如何使用 NiFi 中的新 DBCPConnectionPoolLookup 列出一组数据库中的数据库表? [英] How can I list database tables in a set of databases using the new DBCPConnectionPoolLookup in NiFi?

问题描述

从 NiFi 1.7.1 开始，新的 DBCPConnectionPoolLookup 启用数据库连接的动态选择:设置属性 database.name onFlowFile 并且当消费处理器访问配置的 DBCPConnectionPoolLookup 控制器服务时，该属性的内容将用于通过此查找的配置属性获取连接，其中包含潜在值到 DBCPConnectionPool 控制器服务.

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.

我想列出我在查找中配置的每个数据库中的表，但 ListDatabaseTables 处理器不接受传入的流文件.这似乎意味着它不能用于列出动态数据库集中的表.

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屋！