Agreed, you do not have access to the fields in either the incoming or outgoing JSON objects using Expression Language in the spec. ... View more

I believe the type checking for logical types is more strict now as of https://issues.apache.org/jira/browse/AVRO-2493 and NiFi 1.17.0 (when we upgraded to Avro 1.11.1). Are you using "int" or "string" as the normal Avro type? According the spec (https://avro.apache.org/docs/1.11.1/specification/#timestamp-millisecond-precision) it must be "long". ... View more

What are the column names in your table? Assuming "carId" and "carType", you can use JoltTransformJson or JoltTransformRecord with the following spec:   [ { "operation": "shift", "spec": { "*": { "$": "carId", "@": "carType" } } }, { "operation": "shift", "spec": { "carId": { "*": { "@": "[&0].carId" } }, "carType": { "*": { "@": "[&0].carType" } } } } ]     ... View more

If you use GrokReader you can use the same kv filter from logstash: https://community.cloudera.com/t5/Support-Questions/Grok-Patterns-Expressions-for-capturing-comma-separated-key/td-p/311126   ... View more

Is there anything in the logs before/after the "already marked for transfer" entry? Trying to figure out how a flow file can get transferred and then something goes wrong (where we'd try to also send it to failure) ... View more

You can use Expression Language in the Max-Value Columns property to set them per-flowfile, but there currently isn't any way to fetch the primary key column(s) from the database and use those as the max-value columns. You could do that in upstream processors though, then set an attribute to those columns and pass that into GenerateTableFetch. ... View more

Can you share a snippet of what the original XML looks like? I'm wondering if you need the SplitXML or not. Also it looks like you have a number of "records" in each split XML file, so you probably don't want to put all the values into attributes. Instead, consider PutDatabaseRecord with an XMLReader, if there are multiple entries in the XML that each correspond to a row in the database, PutDatabaseRecord with XMLReader should do all the work for you. ... View more

You can call a REST API from Nashorn/Javascript in ExecuteScript, but since Nashorn doesn't have access to a DOM per se, you'll need to use Java classes for the API call, check this link for an example: https://gist.github.com/billybong/a462152889b6616deb02 ... View more

For small XML files, you could use ExtractText to get the entire content into an attribute, then UpdateRecord with an XMLReader, adding a property for your new field (let's say "/content") and whatever writer you wish. You will have to specify the output schema for the writer, to include all the fields parsed by the XMLReader in addition to a CLOB/BLOB/String "content" field. If you want to exclude fields from the XML then just exclude them from the output schema. Then you can use a similar Reader (AvroReader if you use an AvroRecordSetWriter, e.g.) in PutDatabaseRecord.   If this doesn't work for your use case, you may need to use SplitXml and work on individual records. This will degrade the performance of PutDatabaseRecord unless you merge the records back together later (using MergeRecord for example). ... View more

That error indicates that your CSVReader is not treating the first line as a header, and thus even if you specify an explicit schema, if you don't set Treat First Line As Header to true, the reader will think the header line is a data line, and when it tries to parse it as data (numbers, e.g.) it will fail. ... View more

The PutCassandraRecord processor was added for this purpose. If you only have one line of data per flow file, it will be slower than it needs to be; instead you could use MergeContent or MergeRecord to consolidate a number of these rows into a single flow file, then use PutCassandraRecord (with a CSVReader that has a schema with your 3 fields in it), and it will load all the records in one execution. ... View more

You can put an ExecuteScript processor before SelectHiveQL, and use that to check the time. If it must not execute then it can simply return without fetching any FlowFiles from the queue; otherwise you can simply fetch and transfer all available FlowFiles. ... View more

PutDatabaseRecord does not with work Hive (at least with the Apache Hive or HDP Hive JDBC drivers) as it uses methods of the JDBC API that are not supported by the driver. I'm not sure if the Simba driver works or not. A workaround for managed ACID tables is to use PutHiveStreaming instead, or if you have Hive 3, you can use PutHive3Streaming. The latter is a lot more flexible in terms of input, and relaxes some other requirements for the target table. For external tables, you could put the files directly into HDFS and create a table atop the target directory. If that "temp table" is to be inserted into a managed Hive table, you can then do that with PutHiveQL (after setting the flowfile content to the appropriate INSERT FROM SELECT HiveQL statement). Another (less-than-ideal) workaround is to convert individual records to SQL, update it to HiveQL as necessary (not always needed but sometimes), then send individual statements to PutHiveQL. This is very slow and ineffiencent ... View more

Can you share the configuration of your HortonworksSchemaRegistry controller service? ... View more

I don't think there's much you can do in NiFi, unless you can tune parameters in the JDBC URL, or use PartitionRecord to ensure each FlowFile has only one unique value for the partition column, but even then it looks like you'd need some special logic on the PostgreSQL side. I found this article about maybe this being more performant in later or upcoming versions of PostgreSQL, and this one about doing partitioning via child tables, not sure how your table(s) are set up but maybe some of this will help. ... View more

PutSQL actually doesn't want the semicolon at the end (that's for command-line and other stuff that allow multiple statements), do you get the same error if you leave the semicolon off? ... View more

In the script, you're creating a variable `objList` that (for the first input format) points at the top-level array of objects, so you can call max() directly on that array (I think technically it's a List under the hood). In the second input format, objList will be pointing to the top-level object, so you'll need to get the array member `table` out of the object. Update the "def max" line to this: def max = objList.table.max {Date.parse("yyyyMMddHHmmss",it.elem_stand)} ... View more

The JsonPath Expression is meant to identify an array, then SplitJson will split each element into its own flow file. Try "$.data" as your JsonPath Expression, and use the "splits" relationship to send things downstream. The "original" relationship will contain the incoming flow file, which doesn't sound like what you want. ... View more

What does your query look like, what are the results, and what would you like to see in the single row/record? ... View more

To address your comment below, I missed the part where you want to call the outgoing field "color". Change this line (8): "$": "colorsLove[].&2" To this: "$": "colorsLove[].color" ... View more

The user-defined properties are available in your ProcessContext like all properties are, but they have a flag for being dynamic . Here's a snippet for iterating over the user-defined properties: context.getProperties().keySet().stream().filter(PropertyDescriptor::isDynamic).forEach() Fill in the lambda in the forEach() with your logic for handling the properties. (This is taken from my SO answer for the same question) ... View more

What does the generated SQL coming from ConvertJSONToSQL look like? Are the fields correctly uppercased? Does your database lowercase the column names? Did you try setting the "Translate Field Names" property to "true" in ConvertJSONToSQL? Does the case of the table name in the SQL match the case of the table name in the DB? If you're using "dbo.xxxx" as the Table Name property in ConvertJSONToSQL, instead try using just "xxxx" as the Table Name, and setting either Catalog Name or Schema Name (depending on your DB) to "dbo" (or DBO if necessary). ... View more