About DarkStar

DarkStar · ‎12-10-2020

Hello Akash, Thanks for your feedback. Actually, its work fine if I change my query as below: SELECT DISTINCT T1.block_id AS block_id, T1.block_name AS block_name, T1.block_kp_begin AS block_kp_begin, T1.block_kp_end AS block_kp_end, T2.ap_track_id AS ap_track_id, T2.ap_kp_in_track AS ap_kp_in_track, T2.ap_physical_name AS ap_physical_name, T3.track_name AS track_name, T3.track_type AS track_type FROM amsterdam.amd_block_info T1 LEFT JOIN amsterdam.amd_ap_info T2 ON T1.track_id = T2.ap_track_id LEFT JOIN amsterdam.amd_track_info T3 ON T1.track_id = T3.track_id; Here all I needed to define my variable as Alias again then the problem fixed.

DarkStar · ‎12-07-2020

Dear All, Currently I am using below SQL query in Execute SQL processor (SQL select query) to join 3 different tables that exist within my PostgreSQL database. SELECT T1.*,T2.*,T3.* FROM amsterdam.amd_block_info T1 LEFT JOIN amsterdam.amd_ap_info T2 ON T2.ap_track_id = T1.track_id LEFT JOIN amsterdam.amd_track_info T3 ON T2.ap_track_id = T3.track_id; But I am getting below error response. ExecuteSQL[id=3c9f01f3-0176-1000-0000-000032d6da19] Unable to execute SQL select query SELECT T1.*,T2.*,T3.* FROM amsterdam.amd_block_info T1 LEFT JOIN amsterdam.amd_ap_info T2 ON T2.ap_track_id = T1.track_id LEFT JOIN amsterdam.amd_track_info T3 ON T2.ap_track_id = T3.track_id; due to org.apache.avro.AvroRuntimeException: Duplicate field track_id in record any.data.amd_block_info: track_id type:UNION pos:11 and track_id type:UNION pos:3.. No FlowFile to route to failure: org.apache.nifi.processor.exception.ProcessException: org.apache.avro.AvroRuntimeException: Duplicate field track_id in record any.data.amd_block_info: track_id type:UNION pos:11 and track_id type:UNION pos:3. Am I making any mistake in the query? So,can anyone please tell me what kind of error is this and how can I get rid of this error?

DarkStar · ‎09-29-2020

Used below formula to resolve my problem ${field.value:substring(0,21):toDate("MMM dd,yyyy HH:mm:ss"):format("yyyy-MM-dd HH:mm:ss")}.${field.value:substring(22,28)} We separately added last 6 digit using .${field.value:substring(22,28)} after the time format.Otherwise, nifi can not process 6 digit microsecond.

DarkStar · ‎09-21-2020

Hi @PVVK, thanks for your feedback. I trued your approach but its giving me the same result. Is NiFi (java) only support 3 digit in milliseconds? Do you have any idea? Is there any other alternative that I should try?

DarkStar · ‎09-21-2020

Hello All and @Shu_ashu,@MattWho I have a CSV file with a time column "Apr 15, 2020 02:02:16.388267000 Paris, Madrid (heure d’été)".... "Apr 15, 2020 02:02:16.986879000 Paris, Madrid (heure d’été)"...etc. Then I to convert the field time using UpdateRecord processor by the following code: ${field.value:substring(0,28):toDate("MMM dd,yyyy HH:mm:ss.SSSSSS"):format("yyyy-MM-dd HH:mm:ss.SSSSSS")} But after conversion, the output I get is: nb time delta 1 2020-04-15 02:08:44.000267 0.0 2 2020-04-15 02:18:42.000879 0.598612 But the output should be: nb time delta 1 Apr 15, 2020 02:02:16.388267 0.0 2 Apr 15, 2020 02:02:16.986879 0.598612 So, I always get 000 before the milliseconds. As per the example above, I got 000267 instead of 388267 and 000879 instead of 986879. Why I am getting the 000 instead of the real value? How can I modify me code to resolve this problem? Any help on this issue will be highly appreciated.

DarkStar · ‎04-18-2020

Can anyone please tell me why the value is returning null when I converting xml to json? [ { "trackTopology" : { "trackBegin" : { "connection" : null }, "trackEnd" : { "connection" : null }, "connections" : { "switch" : [ { "connection" : null }, { "connection" : null }, { "connection" : null }, { "connection" : null }, { "connection" : null }, { "connection" : null }, { "connection" : null }, { "connection" : null }, { "connection" : null }, { "connection" : null }, { "connection" : null }, { "connection" : null }, { "connection" : null } ] }, "vertexesWithKP" : null, "trackTopologyInfos" : { "trackTopologyInfo" : { "UEVOL_Territory" : true, "type" : "Mainline", "Driverless_Horn_Activation" : false, "Loop" : false } }

DarkStar · ‎03-24-2020

Hello mburgess, I am able to extract attributes from using GetFile>SplitXml> EvaluateXQuery processor. Now, can you please tell me how can I store these dynamic attributes to the PostgreSQL database using PutSQL processor ? Is this the correct expression I am putting in PutSQL? INSERT INTO alstom_radioscopy_amsterdam_blue."xml_log"(block_id,kp_begin,kp_end) VALUES (${block_id.text()},${kp_begin.text()},${kp_end.text()});

DarkStar · ‎03-17-2020

Hello all, The problem has been resolved. First, I ingested the all the attributes as a text in the PostgreSQL table. Then converted the text data to integer type with flowing command. ALTER TABLE public."tab" ALTER COLUMN col type int using nullif(col,'')::int Hope this will be helpful for others.

DarkStar · ‎03-16-2020

Hello guys, I am extracting attributes from the flow file and putting it to the PostgreSQL. 2017-04-27 09:15:04.794761 10.69.1.138 -> 10.69.1.100 <4>Apr 27 03:44:58 kernel: [ 542.579606] TS|242268|00:14:5a:03:29:72|RSSI_BCN|56 2017-04-27 09:47:59.351047 10.69.1.138 -> 10.69.1.100 <4>Apr 27 04:17:43 kernel: [ 2517.159922] TS|2216852|00:14:5a:03:23:01|RSSI_BCN|53 I am trying to extract integer value 56,53... end of the line and put it to the column<col> of PostgreSQL table<tab> where the <col>datatype is integer. I used GetFile->SplitText->ExtractText ->putSQL in my process flow. I used (\d*)\z regular expression in ExtractText processor and its seems working well for extracting the value 56. But after a certain time the putSQL processor shows error ERROR: invalid input syntx for integer:"". If I change the datatype from integer -> text in the <col> of my PostgreSQL table then I don't face this problem but I need to store the data in integer. So can anyone please help me on finding the root cause of it, or what can I do to resolve it?

DarkStar · ‎03-12-2020

Hello Steve, Thanks for your answer. Don't know why your provided answer is not working in my case. But I solved it in another way which I am sharing here for others timestamp : ([0-9]{4})-([0-1][0-9])-([0-3][0-9])\s([0-1][0-9]|[2][0-3]):([0-5][0-9]):([0-5][0-9]).(\d*) source_ip : (?:[0-9]{1,3}\.){3}[0-9]{1,3} Mac : ([0-9A-Fa-f]{2}[:]){5}([0-9A-Fa-f]{2}) dbm : (\d*)\z Thanks!

Online	Offline
Last Visited	‎02-17-2021 04:41 PM

Member Since	‎03-11-2020 02:04 PM
Last Visited	‎02-17-2021 04:41 PM
Posts	18

Cloudera Community

Re: Joining multiple tables in PostgreSQL using Ex...

Re: Can not convert 6 digit milliseconds in NiFi. ...

Re: Facing ERROR: invalid input syntx for integer:...

Re: How do I write the Regular Expression from the...

Re: Joining multiple tables in PostgreSQL using Ex...

Joining multiple tables in PostgreSQL using Execut...

Re: Can not convert 6 digit milliseconds in NiFi. ...

Re: Can not convert 6 digit milliseconds in NiFi. ...

Can not convert 6 digit milliseconds in NiFi. Stri...

Re: Issue with ConvertRecord or ConvertJSONToAvro ...

Re: Managing multiple insert queries

Re: Facing ERROR: invalid input syntx for integer:...

Facing ERROR: invalid input syntx for integer:&quo...

Re: How do I write the Regular Expression from the...