让NiFi从Oracle视图中读取正确格式化的JSON，而无需对其进行转换/重新格式化

简要概述：尝试使用Oracle -> NiFi -> Solr。
我一直在尝试将数据从Oracle视图中以JSON的形式导入NiFi，以便最终流到Solr中。对于普通列（varchar 2、integer等），这不是问题。ExecuteSQLRecord处理器中的一个简单的select * from view工作得很好; Nifi中的数据是正确形成的JSON，并且被完美地流式传输到Solr中。
问题是，我有一些表示1：N数据的列，即数组。我正在替换一个过时的进程，用NiFi替换一个过时的ETL工具。对于这些1：N列，旧的ETL工具将创建以下格式的XML：

<results>
    <row>
        <field name="town">sleepy town</field>
    </row>
    <row>
        <field name="town">boring town</field>
    </row>
</results>

显然，在这个旧的ETL工具中，内置的Solr组件中有一些魔力，这个XML格式被转换，因此它最终看起来像Solr中的JSON字符串数组：

"array_column":
    ["sleepy town",
     "boring town"]

我最初在Oracle视图中创建XML是为了模仿旧的ETL工具，后来我意识到这种神奇的转换正在消失。由于所有其他普通列（varchar 2，integer等）都是通过NiFi正确地传递到Solr的，所以我想我应该重新格式化这些1：N列，使它们看起来像JSON数组，我做到了。
问题是，无论我在NiFi中（或在Oracle中的视图中）做什么，NiFi都只会将来自Oracle的数据作为一个整体字符串处理。换句话说，在Oracle中，视图返回的是：

select array_column from view

ARRAY_COLUMN
------------
["sleepy_town","boring town"]

但是在NiFi中，当查看ExecuteSQLRecord处理器的数据来源时，它显示了以下内容：

[
  {
    "SOME_ORDINARY_COLUMNS":"Everything looks great!",
    "ARRAY_COLUMN": "[\"sleepy town\",\"boring town\"]",
    "MORE_ORDINARY_COLUMNS":"Everything looks great!",
  }
]

当我让Avro参与进来时，它看起来像这样：

{
  "SOME_ORDINARY_COLUMNS" : {
    "string" : "Everything looks great!"
  },
  "ARRAY_COLUMN" : {
    "string" :  "[\"sleepy town\",\"boring town\"]"
  },
  "MORE_ORDINARY_COLUMNS" : {
    "string" : "Everything looks great!"
  }
}

我尝试了很多东西：

将JsonRecordSetWriter上的Schema Write Strategy更改为Set 'avro.schema' Attribute，这样我就可以查看模式，然后使用“Use 'SchemaText' Property”尝试强制ARRAY_COLUMN成为字符串数组（有很多变体）
使用Avro编写器并使用Avro或JSON读取器将输出发送到QueryRecord处理器的许多变体，同时指定了推断和显式模式
将QueryRecord处理器的JsonPathReader中的JSONPath设置为$.ARRAY_COLUMN[*]和/或$.ARRAY_COLUMN.[*]
将ExecuteSQLRecord中的查询更改为select JSON_OBJECT(...)，而不是select *（还尝试使用JSON_OBJECT作为子查询的点表示法）
研究了Oracle中的VARRAY和嵌套表，但这似乎是错误的方向

所有这些都导致了NiFi中的“无法将字符串转换为数组”错误，或者1：N列仍然是一个带有双引号的单体字符串。
我确信我在这两行之间尝试过其他的变化，但是我已经和这个斗争了几天，它变得模糊了。我知道在Oracle中，JSON_OBJECT有一个“FORMAT JSON”选项，它告诉Oracle，“嘿，这是JSON，所以不要重新格式化它！“我希望在Nifi中有这样的东西，某种方式告诉NiFi将来自Oracle的内容视为格式正确的JSON，而不需要进行篡改。
我还没有做过任何事情，但我想我可以得到真正的幻想，写一些groovy或什么，但我真的试图做到这一点，只是本机NiFi处理器，而不必依靠自定义脚本。

虽然我曾考虑过使用集合，但Alex Poole的第一个评论将我推回了那个方向，这就是我完成的事情。我将视图切换为使用嵌套表：
create or replace type my_schema.clob_nested_table as table of clob
然后在视图中：

with main_query as
(
    select case_id, other_fields, more_fields, transformation1, transformation2
      from parent_table
      join lookup_table
      join another_lookup_table_etc
),
child_a as
(
    select a.case_id
           cast(multiset(select to_clob(nvl(b.column1, '') || nvl(b.column2, '') || nvl(b.etc, ''))
                           from child_table_a b
                          where a.case_id = b.case_id) as my_schema.clob_nested_table) as child_a_data
      from main_query a
)
select a.case_id, a.other_fields, a.more_fields, a.transformation1, a.transformation2,
       b.child_a_data
  from main_query a
  left outer join child_a b on a.case_id = b.case_id

当然，这是伪代码，在语法上不是100%正确，但它显示了魔力。有54个这样的“子”表，所以这是一个相当长的视图，并且有很多转换正在进行。
解决方案是使用嵌套表，数据在NiFi中显示为格式正确的JSON数组，然后发送到Solr，在那里它看起来完全一样。谢谢亚历克斯

让NiFi从Oracle视图中读取正确格式化的JSON，而无需对其进行转换/重新格式化

1条答案

相关问题

热门标签

最新问答