跳到主要内容
版本：v3

PostgreSQL

发送SQL查询以与PostgreSQL数据库交互。

特定设置​

在配置SQL连接器以发送SQL查询到PostgreSQL服务器时，您需要提供以下细节：

  • 主机：PostgreSQL服务器的地址。
  • 端口：PostgreSQL服务器端口（默认是5432）。
  • 数据库：要连接的数据库名称。
  • 用户名：用于认证的用户名。
  • 密码：用于认证的密码。
  • 连接超时：建立连接的超时设置。
  • 请求超时：每个SQL查询的超时时间。
数据库访问

强烈建议在连接数据库时使用只读用户，以确保安全和数据完整性。

项目设置​

在 South 连接器中，每个项目都可以根据选择的扫描模式进行配置以被查询。同一个 South 连接器内可以配置对同一数据库的多个查询。OIBus 将依次执行这些查询， 准备输出文件，然后将其发送到 North 连接器。

查询​

South 连接器中的查询字段接受 SQL 语法，并且可以利用几个内部变量。这些变量用于多种目的，包括在连接失败时增强数据流的韧性以及将大时间间隔分解为更小的块，这有助于减轻服务器和网络的负载。更多信息，请参考大查询部分。

查询变量​

在 OIBus 中，你可以使用以下内部变量，系统将解释这些变量：

  • @StartTime：最初，@StartTime 变量设置为查询第一次执行的日期。当从数据库检索到结果时，@StartTime 值更新为这些结果中用作参考的字段的最新时间戳（参考日期时间字段部分）。
  • @EndTime：@EndTime 变量设置为当前时间（now()）或者如果查询被分割，则为子间隔的结束时间。
包含 @StartTime 和 @EndTime 的 SQL 查询
SELECT data_name AS dataName, value, timestamp FROM table WHERE timestamp > @StartTime AND timestamp < @EndTime

日期时间字段​

在 South 连接器中，你可以指定一个由 datetime 类型字段组成的数组。每行指示 OIBus 将如何解析此字段以将其转换为内部 UTC 日期。这里是配置日期时间字段的详细信息：

  • 字段名称：查询的 SELECT 部分中的字段名称。
  • 参考字段：使用此字段作为下一个 @StartTime 值的参考（参考查询变量）。
  • 类型：结果中的数据类型。
  • 时区（对于字符串、Date、DateTime、DateTime2、SmallDateTime 类型）：数据库中存储的日期时间的时区。
  • 格式（仅限字符串）：数据库中存储的日期时间的字符串格式。
  • 区域设置（仅限字符串）：当格式包含特定于区域的字符串时使用的区域设置（如月份的 MMM 格式）。

OIBus 内部使用这些日期的 ISO UTC 格式，并且可以在序列化部分中设置输出日期时间格式。所有日期时间字段都使用相同的序列化设置进行转换。

SQL 查询中的转换

如果使用字符串类型的 timestamp 字段作为参考，格式为 yyyy-MM-dd HH:mm:ss，则 @StartTime 和 @EndTime 将被注入查询中为 yyyy-MM-dd HH:mm:ss 格式的字符串。

如果 timestamp 字段以字符串格式被用作参考，格式为 yyyy-MM-dd HH:mm:ss，则 @StartTime 和 @EndTime 将被注入查询中为 yyyy-MM-dd HH:mm:ss 格式的字符串。

在后续查询中，从数据库检索的 datetime 字段（转换为字符串 timestamp）是一个 DateTime 对象。OIBus 将从查询中解释 timestamp（字符串）字段作为参考字段。然而，以相同格式注入 @StartTime 和 @EndTime 作为字符串变量可能会由于注入的 @StartTime 和 @EndTime 变量（字符串类型）与数据库中的日期时间字段（格式化为 DateTime）之间的不一致而导致意外行为。

包含 @StartTime 和 @EndTime 以及转换的错误 SQL 查询
SELECT data_name AS dataName, value, convert(datetime, DATETIME) AS timestamp FROM table
WHERE datetime > @StartTime AND datetime < @EndTime

在以下情况中，格式的统一保证了适当的功能。

包含 @StartTime 和 @EndTime 的正确 SQL 查询
SELECT data_name AS dataName, value, convert(datetime, DATETIME) AS timestamp FROM table
WHERE convert(datetime, DATETIME) > @StartTime AND convert(datetime, DATETIME) < @EndTime

CSV序列化​

OIBus提供了将检索到的数据序列化为CSV文件的选项，并且您可以使用以下设置自定义序列化过程：

  • 文件名：结果将被存储的文件的名称。您可以使用若干内部变量，如@ConnectorName（连接器的名称）和@CurrentDate（当前日期，格式为yyyy_MM_dd_HH_mm_ss_SSS - 这与序列化的日期时间格式不相关，用于日期时间字段）。
  • 分隔符：CSV中使用的分隔符。
  • 压缩：选择是否使用gzip压缩文件。
  • 输出日期时间格式：指定CSV中的日期时间格式。OIBus将仅转换指定的日期时间字段。在输出文件名中使用的@CurrentDate变量不会受到影响。
  • 输出时区：用于存储日期时间的时区。

拆分大型查询​

在请求大时间间隔时，特别是当使用@StartTime和@EndTime查询变量可能对服务器造成显著负载的情况下，您可以将查询拆分为若干个具有更小间隔的子查询。这可以通过配置历史设置中的最大读取间隔字段来实现。