About cotopaul

cotopaul · ‎10-09-2023

@s300570, If I understood your requirement correctly, you might try something like: WITH WorkingDaysCTE AS ( SELECT ref_date, ROW_NUMBER() OVER (ORDER BY ref_date) AS row_num FROM cd_estruturais.calendario_datas WHERE ref_date >= CURRENT_DATE -- You can adjust the starting date as needed AND civil_util = 1 ) SELECT c1.*, w1.ref_date AS next_wkday, DATEDIFF(w1.ref_date, c1.ref_date) AS Num_Days FROM cd_estruturais.calendario_datas c1 JOIN WorkingDaysCTE w1 ON c1.ref_date <= w1.ref_date AND w1.row_num = ( SELECT MIN(row_num) FROM WorkingDaysCTE w2 WHERE w2.row_num > w1.row_num ) WHERE c1.ref_date >= CURRENT_DATE AND c1.civil_util = 1 LIMIT 1; Now, just as info, as I no longer have access to an Impala/Hive system, the above query was written as a standard SQL, so you might want to double check the syntax. In terms of explanations: The common table expression (CTE) named WorkingDaysCTE is used to filter the dates with civil_util = 1 and assign a row number to each date based on their order. The main query then joins the calendario_datas table with the CTE on the condition that the date in the calendar table is less than or equal to the date in the CTE. The subquery in the SELECT clause is used to find the minimum row number greater than the current row number in the CTE. This helps in getting the next working day. The DATEDIFF function is used to calculate the number of days between the ref_date and the next_wkday.

cotopaul · ‎10-09-2023

Well the only advise I can give you is to write your processor and see what errors you have and come back with them. Nobody can write your processor if only you know your requirements. What I can suggest you though, is to have a look at the following examples, as they might assist you with what you are trying to achieve: https://community.cloudera.com/t5/Community-Articles/ExecuteScript-Cookbook-part-1/ta-p/248922 https://community.cloudera.com/t5/Community-Articles/ExecuteScript-Cookbook-part-2/ta-p/249018 https://community.cloudera.com/t5/Community-Articles/ExecuteScript-Cookbook-part-3/ta-p/249148

cotopaul · ‎10-06-2023

@manishg, what exactly are you trying to achieve? Can you be a little bit more specific? In NiFi you have plenty of processors which will split your data but it depends on what you are trying to split and how. For example you could use an SplitRecord Processors which basically does exactly what you need, it splits an input FlowFilw into multiple smaller FlowFiles. All you have to do is configure a Reader and a Writer, so that NiFi knows how to read and how to write the data. Afterwards, you set how many record you want on each new flowfile .... and that is all. You can also you SplitAvro, SplitJSON, SplitContent, SplitText and even SplitXML, depending on what you are actually trying to achieve with your entire Flow. Another option would be to use a Scripting processor like ExecuteStreamCommand or ExecuteScript, in which you define and configure a script to perform the logic you are looking for. And there are many other options which you could also try. So, what I am trying to say is that if you want a specific answer to your question, you need to say exactly what you are trying to do. Otherwise, the above lines should be sufficient to understand that what you are trying to achieve in NiFi is possible 🙂 PS: don't try and split a CSV File with million of lines into individual flow files containing 1 line, because you will regret doing that 🙂

cotopaul · ‎10-03-2023

@hkh, your post lacks all the minimum information required for anybody to help you out. Nevertheless, based on your error message, it seems that you are trying to connect to a PostgreSQL Database. Now, as you mentioned, you did an upgrade at the database level, meaning that some aspects have changed in terms of configurations. Your error message stats clearly what the issue is where you need to look to have it solved. Unfortunately, this has nothing to do with NiFi, but to how your DBA Team has configured your PostgreSQL Instance. Have a look here: https://stackoverflow.com/questions/67588076/why-do-i-get-error-type-10-authentication-not-supported-for-postgresql-13-even https://stackoverflow.com/questions/64210167/unable-to-connect-to-postgres-db-due-to-the-authentication-type-10-is-not-suppor

cotopaul · ‎10-02-2023

@EddyChan, if I understood correctly, you want to set an already created Parameter Context in a Process Group, right? If so, you can do that using RestAPI with PUT as Request Method and the payload as: { "revision": { "clientId": "6df050ee-0002-1de6-27e5-05edebf761b0", "version": 0 }, "disconnectedNodeAcknowledged": false, "component": { "id": "6dbf7e4e-849e-39ad-a2dd-dbe3f890a1d0", "name": "test", "comments": "", "parameterContext": { "id": "b2ce1579-0187-1000-ffff-ffffb8598d34" }, "flowfileConcurrency": "UNBOUNDED", "flowfileOutboundPolicy": "STREAM_WHEN_AVAILABLE", "defaultFlowFileExpiration": "0 sec", "defaultBackPressureObjectThreshold": "10000", "defaultBackPressureDataSizeThreshold": "1 GB" } } What you need to know is the ID of the parameter context you would like to set in your process group and you are all done. Of course you will need to know the ID of the Process Group you are trying to modify as well.

cotopaul · ‎09-26-2023

@lafi_oussama, First of all, what is the compression format of the files you are trying to unzip? What error did you receive when using UnpackContent? Have you tried using CompressContent ( https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi/nifi-standard-nar/1.12.1/org.apache.nifi.processors.standard.CompressContent/ ) You can configure the processed to either compress or decompress your files. You have multiple compression formats from which you can choose from.

cotopaul · ‎09-25-2023

@charliekat, this discussion is far from easy and most likely it will be a quite general one, with no specific answer to your question 🙂 There are plenty of things you could monitor and automate, starting from the CPU Usage, RAM Usage, CPU and RAM Temperature, I/O on your SSD, network packages and so on. Directly from NiFi you won't be able to perform many tasks, as it was not created for such things. However, if you are good enough with PowerShell (Gamer=Windows in 95% cases) you can create some scripts and execute them directly in NiFi. Now, depending on your script Output, you will call other scripts which will perform the desire action: like killing long running processes, or processes which are consuming to much CPU while you are gaming and so on. These actions only represent around 5-10% of what you can do. The other 90-95% are actually related to your hardware: GPU, OS, SSD, Mouse(DPI), Screen(resolution+frame rate), Keyboard, Ethernet Connection, CPU and the overall Cooling System, Windows Updates , Game settings, GPU Updates and so on .... and unfortunately, for these actions, NiFi won't be much of a friend 😞 Nevertheless, don't take my answer as a discouragement, but take it as an action to prove me wrong and go play with NiFi and make something amazing 🙂

cotopaul · ‎09-25-2023

I managed to solve my issue using UpdateRecord with Literal Value replacement strategy. I have defined the following EL: ${field.value:toDate('dd-MMM-yy'):format('yyyy-MM-dd')} On the other hand, the avro schema remained the same, "type": "int" and "logicalType": "date". As for replacing the month letters, I have hard coded a very ugly IF-ELSE statement: ${field.value:contains('IAN'):ifElse(${field.value:replace('IAN','JAN')},${field.value:contains('IUN'):ifElse(${field.value:replace('IUN','JUN')},${field.value:contains('IUL'):ifElse(${field.value:replace('IUL','JUL')},${field.value})})}):toDate('dd-MMM-yy'):format('yyyy-MM-dd')} PS: this link helped me a lot: https://docs.oracle.com/javase/8/docs/api/java/text/SimpleDateFormat.html

cotopaul · ‎09-25-2023

@BerniHacker, I did not even take into consideration the state 🙂 I figured you were trying to execute it for the first time and I assumed from the start that you had nothing saved in your state. Congrats on solving your issue.

cotopaul · ‎09-25-2023

Hi guys, So I have been struggling with a data conversion and I can't really figure out how to achieve what I am trying to achieve. I have a CSV File which comes into my flow as follows: COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COLUMN6 MONTH 0182 Tel set W27 0 2200 31-IAN-22 0183 Apa cai W27 0 2200 30-SEP-22 0185 HDM set MT8 1 2200 28-FEB-22 0185 Apa alo MT8 0 2200 31-OCT-22 0186 HDM set HG5 1 2200 30-IUN-22 0188 Fus alo HG5 1 2200 30-APR-22 I am using afterwards an ConvertRecord to transform the CSV into an AVRO File, using the following schema: { "type": "record", "name": "nifiRecord", "namespace": "org.apache.nifi", "fields": [ { "name": "COLUMN1", "type": [ "string", "null" ] }, { "name": "COLUMN2", "type": [ "string", "null" ] }, { "name": "COLUMN3", "type": [ "string", "null" ] }, { "name": "COLUMN4", "type": [ "string", "null" ] }, { "name": "COLUMN5", "type": [ "string", "null" ] }, { "name": "COLUMN6", "type": [ "int", "null" ] }, { "name": "MONTH", "type": [ "string", "null" ] } ] } Now, in the next step, I would like to transform the MONTH from STRING into DATE, so I could insert this value into a BigQuery Table (target column is DATE). For that, I am using an UpdateRecord Processor in which I tried several NiFi Expression Language tests, but neither work for me. Basically, the schema will have to change into: { "name": "MONTH", "type": [ "null", { "type": "int", "logicalType": "date" } ] } Unfortunately, when trying to convert that string date into a normal date, i keep on encountering strange errors. What I am trying to have is 31-IAN-22 as 31-JAN-22 (or 31-01-22) in the generated AVRO File as an INT-DATE. As you can see, the month itself is not necessary written in english. I have tried several ELs: ${field.value:replace('SEP', '09'):toDate('dd-MM-yy'):format('dd-MMM-yy'):toDate('dd-MMM-yy'):toNumber()} ${field.value:toDate('dd-MM-yy'):format('dd-MMM-yy'):toDate('dd-MMM-yy'):toNumber()} ${field.value:toDate('dd-MM-yy'):format('dd-MMM-yy'):toNumber()} ${field.value:toDate('dd-MM-yy'):toNumber()} Every time I receives some strange errors: org.apache.nifi.attribute.expression.language.exception.IllegalAttributeException: Cannot parse attribute value as a date; date format: yyyy-mm-dd; attribute value: 30-SEP-23 java.lang.NullPointerException: null org.apache.nifi.serialization.record.util.IllegalTypeConversionException: Failed Conversion of Field [MONTH] from String [1696021200000] to LocalDate Does anybody know how I could achieve this? Thanks 🙂

Online	Offline
Last Visited	‎03-14-2024 06:37 AM

Member Since	‎01-27-2023 08:25 AM
Last Visited	‎03-14-2024 06:37 AM
Posts	229
Kudos received	74

Cloudera Community

Re: About mergecontent question

Re: how can get the content of Json record and val...

Re: DBCP Connection Pool can't connect to "Progres...

Re: terminate kafka connection if publish kafka pr...

Re: Not able to delete an inifinite loop built wit...

Re: Get an end date, (n) workdays from a given dat...

Re: Creating a new Record from input record

Re: Creating a new Record from input record

Re: Nifi throwing SQLException after DB version up...

Re: Set the Global Parameter Processor Configurati...

Re: Nifi Unzip files

Re: Optimizing Gaming Performance with Apache NiFi...

Re: Conversion from STRING to DATE - CSV to AVRO c...

Re: Ni-Fi GenerateTableFetch produces no output if...

Conversion from STRING to DATE - CSV to AVRO conve...