Member since
07-24-2023
7
Posts
5
Kudos Received
0
Solutions
08-22-2024
04:03 AM
1 Kudo
I am currently migrating data from MSSQL to PostgreSQL. While fetching the data from MSSQL, I use the following query to ensure the time is in UTC: SELECT "time column" AT TIME ZONE 'UTC' AS "time column" This gives me the correct result in UTC. However, when I ingest the data into PostgreSQL using NiFi's PutDatabaseprocessor, I notice that the time is being increased by 5 hours. The destination column in PostgreSQL is of type timestamp with time zone. when I manually run an INSERT command in PostgreSQL with the same time value, the time remains unchanged. The time shift only occurs during the NiFi ingestion process. Any ideas on what could be causing this discrepancy?
... View more
Labels:
- Labels:
-
Apache NiFi
04-15-2024
09:34 AM
1 Kudo
@Ytch All components on the NiFi canvas are executed as the NiFi service user and not as the user currently authenticated into the NiFi service. So what you should do is from each host in your NiFi cluster (do on every host since any one of the hosts can be elected as the primary node at any given time), open a command prompt window/console window, become the user that owns the NiFi process, and manually ssh/sftp to the target SFTP server. You will likely be prompted to add the target SFTP server to your known_hosts file for the NiFi service user. NiFi SFTP processor has no way of doing this interactive step. After successfully adding the SFTP to the known_hosts file for the NiFi service user, go back and try to start the GetSFTP or ListSFTP processors again to see if your issue is resolved. If not, please share your GetSFTP and ListSFTP processor component configurations. Also check the nifi-app.log for any exceptions or log output related to these processors. If no log output, you could also try enabling debug in the NiFi logback.xml for these processor classes to see what additional log output may be produced that could be useful here. classes for these processors are: org.apache.nifi.processors.standard.GetSFTP
org.apache.nifi.processors.standard.ListSFTP new log lines would look like this that you would add to logback.xml: <logger name="org.apache.nifi.processors.standard.GetSFTP" level="DEBUG"/>
<logger name="org.apache.nifi.processors.standard.ListSFTP" level="DEBUG"/> Simply add them in logback.xml where you see similar lines already. Please help our community thrive. If you found any of the suggestions/solutions provided helped you with solving your issue or answering your question, please take a moment to login and click "Accept as Solution" on one or more of them that helped. Thank you, Matt
... View more
04-04-2024
10:31 AM
Hi @Ytch , The way I was able to get to work is through the following instruction: 1- After you have download the jdbc drive from here: https://learn.microsoft.com/en-us/sql/connect/jdbc/download-microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15 you should be able to find the following dll "mssql-jdbc_auth-8.2.2.x64.dll" under the path: ..\sqljdbc_8.2\enu\auth\x64 2- Copy the dll and place under the JAVA Home path bin folder: .. \Java\jdk-21\bin 3- Adjust the DB Connection URL in the DBCPconnectionpool controller service by adding and setting "integrated security" flag to true: jdbc:sqlserver://{SQL SEVER NAME};databaseName={DB NAme};integratedSecurity=true; 4- Restart Nifi If that helps please accept solution. Thanks
... View more
09-19-2023
02:34 AM
I am using executesqlrecord processor to run select sql queries. I use the CSVRecordsetwriter to write the results. In the CSVRecordsetwriter properties, I have mentioned the date format as MM/dd/.yyyy but still the CSVrecordsetwriter is converting the date into epoch timestamps. How do i resolve this issue? This is a screenshot of my csvrecordsetwriter settings.
... View more
Labels:
- Labels:
-
Apache NiFi
-
NiFi Registry
07-24-2023
02:12 PM
hi @Ytch , To use the PutDatabaseRecord you need to have the column names defined in whatever input format you have (CSV, Json....etc.) and the column name should match what is in the DB table ( see also the "Translate Field Option" if not exactly the same). If you know the column order in the CSV file you can add a header using the ReplaceText processor as described here: https://community.cloudera.com/t5/Support-Questions/How-to-add-customer-headers-to-csv-file-in-NIFI/m-p/285457 If you find this is helpful please accept solution. Thanks
... View more