@rafy    You shouldn't need to extract the content as an attribute. Instead, use ReplaceText to replace the contents of the flowfile with the SQL template, like this: INSERT INTO my_table (xml_col) VALUES ('$1')   Where $1 is a reference to the default regex capture group that captures the entire content. In reality, it requires a bit more labour, since you have to escape the single quotes in the XML first.   Cheers, André   ... View more

Hi, @code ,   I don't think this is actually possible. Even if there was a way to enter a literal value of NULL for the lookup value, the controller service is probably comparing the lookup value with an "equals" operation (e.g. mytable.mykey = lookup_value) and in relational databases the comparison NULL = NULL is always evaluated to FALSE. (the only way to compare values with a NULL is to use the operator IS).   What you can try to do is to create a view on top of that table that converts NULLs to some string that you can use to compare to lookup values in NiFi. Then you can use the view name in the controller service configuration instead of the table name.   For example:     CREATE VIEW v_mytable AS SELECT NVL(mykey, '<NULL>') as key_without_nulls, * FROM mytable     Be aware of potential performance implications of this, since this could prevent existing table indexes from being used for lookups.   Cheers, André     ... View more

@Markyd ,   Functions used in Hive queries are usually passed to the Hive service as is. I don't think the ODBC driver does any particular processing or validation of those functions. At the end of the day it's a matter of whether the Hive version you're using supports that function or not, and this depends on the Hive version being used, not the driver version.   Here you can see a comprehensive list of Hive functions and the associated Hive version where they were introduced: https://cwiki.apache.org/confluence/display/hive/languagemanual+udf   Which version of Hive are you using? Are you using the Cloudera ODBC Drivers for Hive? Which version?   Cheers, André   ... View more

@syntax_ ,   It seems to me that your source Kafka is a Confluent Kafka cluster and the producer uses schema registry to source the schema from it. In this case, the KafkaAvroSerializer prepends 5 bytes to every message produced to indicate the id of the schema that was used (in you case, schema id 34). If you try to read this message as a pure Avro payload the deserialization will fail because those 5 bytes are not part of the Avro payload.   So, the best way to handle this in NiFi is to also use Schema Registry to deserialize Avro messages. With this, NiFi will get the schema ID from the message 5-byte prefix, use that ID to retrieve the correct schema from Schema Registry and then correctly deserialize the Avro payload.   Considering that my guess is correct and you're using a Confluent Schema Registry, you should create a new ConfluentSchemaRegistry controller service and configure it with the details of your Schema Registry. Once this is done, edit the configuration of the Avro Reader controller service and set the following:   After you do this, your flow should be able to correctly process the messages that you're reading from Kafka.   I read the binary message that you send me with NiFi and loaded the schema in my local schema registry service (making sure it got assigned the right ID 34), and I was able to successfully convert the message from Avro to JSON using a ConvertRecord processor:   Cheers, André ... View more