You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@nifi.apache.org by Karsten Zakarias <KZ...@foss.dk> on 2017/06/20 09:52:02 UTC
Problems getting invokeHTTP/PutSQL to work
Hi all,
I'm new to Apache Nifi and having problems writing a POST/PUT request to an external db. Any advice/links to material and prior threats would be greatly appreciated.
My setup is the following:
- I have BatchIQ Apache Nifi running on an AWS EC2 instance.
- A GetFTP processor ingests csv files
- Each value is extracted using regex and combined into a JSON object using an AttributesToJSON processor
- I now want to use a InvokeHTTP to write the json object to an airtable db and a PutSQL to write to a SQL db.
- How do I setup InvokeHTTP correctly.
- How do I configure PutSQL correctly on a remote instance with a database driver.
Thanks a lot in advance!
Karsten.
AttributesToJSON:
[cid:image003.png@01D2E9B9.92741B70]
InvokeHTTP:
[cid:image001.png@01D2E9BA.47594FB0][cid:image002.png@01D2E9BA.47594FB0]
[cid:image004.png@01D2E9BB.9D1C27F0]
PutSQL:
[cid:image006.png@01D2E9BA.C409EF60]
[cid:image007.png@01D2E9BA.C409EF60]
Best regards / Med venlig hilsen
Karsten Zakarias
Product Manager, M.Sc.
IT & Digital Business
[cid:image005.jpg@01D2E9BB.9D1C27F0]
Phone:
Mobile:
E-mail:
+45 4820 8514
+45 2761 6920
KZA@foss.dk
[cid:image008.jpg@01D2E9BB.9D1C27F0]
FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
________________________________
FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers' business and better food quality for consumers.
RE: Problems getting invokeHTTP/PutSQL to work
Posted by Karsten Zakarias <KZ...@foss.dk>.
Hi Andy,
It worked. Thanks a lot.
In my invokeHTTP request I need to configure the header to include the Authentication and the body to include a JSON object.
I have an AttributesToJSON that delivers a JSON object to my invokeHTTP:
[cid:image004.png@01D2EB3D.9EA372D0]
[cid:image005.png@01D2EB3D.9EA372D0]
[cid:image001.png@01D2EB3D.5A474BC0]
Best regards / Med venlig hilsen
Karsten Zakarias
Product Manager, M.Sc.
IT & Digital Business
[cid:image002.jpg@01D2EB3D.9EACE8B0]
Phone:
Mobile:
E-mail:
+45 4820 8514
+45 2761 6920
KZA@foss.dk
[cid:image003.jpg@01D2EB3D.9EACE8B0]
FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
________________________________
FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.
From: Andy LoPresto [mailto:alopresto@apache.org]
Sent: 22. juni 2017 01:05
To: users@nifi.apache.org
Subject: Re: Problems getting invokeHTTP/PutSQL to work
Can you please share a screenshot of the SSLContextService properties again? There should be only four values populated:
Truststore Filename: /etc/pki/java/cacerts
Truststore Password: “changeit”
Truststore Type: JKS
SSL Protocol: TLS
I do not believe Amazon changes the default password for cacerts, but you can verify it is correct by running this command:
$ keytool -keystore /etc/pki/java/cacerts -list -v -storepass changeit
It will either succeed and display the trusted certificate entries or warn you that the password is incorrect.
Andy LoPresto
alopresto@apache.org<ma...@apache.org>
alopresto.apache@gmail.com<ma...@gmail.com>
PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4 BACE 3C6E F65B 2F7D EF69
On Jun 21, 2017, at 5:02 PM, Karsten Zakarias <KZ...@foss.dk>> wrote:
Thanks James and Andy,
I located the cacerts. So far so good.
I still get an error.
Br,
Karsten.
<image001.jpg>
<image002.jpg>
Best regards / Med venlig hilsen
Karsten Zakarias
Product Manager, M.Sc.
IT & Digital Business
<image003.jpg>
Phone:
Mobile:
E-mail:
+45 4820 8514
+45 2761 6920
KZA@foss.dk<ma...@foss.dk>
<image004.jpg>
FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
________________________________
FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.
From: James Wing [mailto:jvwing@gmail.com]
Sent: 21. juni 2017 15:27
To: users@nifi.apache.org<ma...@nifi.apache.org>
Subject: Re: Problems getting invokeHTTP/PutSQL to work
As Andy mentioned below, the StandardSSLContextService's Truststore Filename should probably be configured to point to a file named 'cacerts', unless you have a custom truststore to use. I believe /etc/pki/java/cacerts is typical path for an EC2 instance. The path to the JRE is not sufficient.
Thanks,
James
On Wed, Jun 21, 2017 at 1:54 AM, Karsten Zakarias <KZ...@foss.dk>> wrote:
Hi Andy,
Thanks for your reply. This is how far I’ve gotten.
My invokeHTTP configuration looks like this:
<image007.png>
My ContextService looks like this:
<image008.png>
As my Apache Nifi is on an EC2 instance I ssh in and tried to locate the Java Runtime Environment and this is what I found under /usr/lib/jvm. I don’t know if that is the correct location:
<image010.png>
This is the ContextService error:
<image011.jpg>
Where am I getting it wrong?
Best regards / Med venlig hilsen
Karsten Zakarias
Product Manager, M.Sc.
IT & Digital Business
<image003.jpg>
Phone:
Mobile:
E-mail:
+45 4820 8514
+45 2761 6920
KZA@foss.dk<ma...@foss.dk>
<image004.jpg>
FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
________________________________
FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.
From: Andy LoPresto [mailto:alopresto@apache.org<ma...@apache.org>]
Sent: 20. juni 2017 15:37
To: users@nifi.apache.org<ma...@nifi.apache.org>
Subject: Re: Problems getting invokeHTTP/PutSQL to work
Hi Karsten,
Are you getting a specific error message when using InvokeHTTP or PutSQL? I don’t have an Airtable account, but it looks like a pretty straightforward REST API over HTTPS. From your screenshot, I believe you need to configure an SSLContextService in order to validate the certificate presented by Airtable. You should configure it as shown below. The reason is that NiFi by default does not trust any certificates. When it tries to make a request to Airtable, Airtable tries to negotiate an encrypted TLS connection and NiFi attempts to verify the server’s identity using the presented certificate chain. Without a “truststore” (a collection of trusted certificates), it fails to do this. Java provides a default truststore to allow these connections to succeed.
Steps for creating & linking SSLContextService:
1. From the Operate palette, click the gear icon (“Configuration”) and switch to the “Controller Services” tab. You can also skip directly there from the InvokeHTTP properties tab by opening the dropdown for “SSL Context Service” and selecting “Create new service…"
2. Create a new StandardSSLContextService. This controller service provides a common interface to the TLS (nee SSL) settings that various components use.
3. You do not need to populate the first four values (the “keystore” values) unless Airtable requires client authentication for TLS, which I highly doubt. Instead, populate the following fields:
Truststore Filename: <path to your Java Runtime Environment’s “cacerts” file>
Truststore Password: “changeit”
Truststore Type: JKS
SSL Protocol: TLS
4. Save the controller service and “enable” it by clicking the lightning icon on the far right.
5. Once this service is enabled, your processor should be valid (as shown by a red “stopped” icon rather than a yellow “caution” icon).
6. For debugging, I would also recommend the following settings:
Put Response Body in Attribute: “response” <— This will put the response body into an attribute so you can view it with a LogAttribute processor or by examining the flowfile in the queue
Always Output Response: true <— This will force a flowfile to be generated even if the HTTP status is one that does not send a response body
7. You provided a custom request header with your API key, but you named it “Authentication” instead of “Authorization”. You’ll need to change this to “Authorization”.
8. You may need to set the Content Type header to explicitly be “application/json”. The mime type is correctly set coming out of the AttributesToJSON processor, so I doubt this is the issue.
There may be an Airtable tool for viewing the incoming requests and determining why the request body could not be parsed. If not, I suggest setting up a simple HTTP server on your local device and temporarily pointing the request there (or copying the InvokeHTTP processor and doing it in parallel) to debug the request. You can also use tools like Paw or Postman to craft POST calls to Airtable manually until you are successful and compare to the output of the InvokeHTTP processor — you can enable DEBUG level logging for it by modifying your $NIFI_HOME/conf/logback.xml file and adding the line:
<logger name=“org.apache.nifi.processors.standard.InvokeHTTP" level="DEBUG"/>
Hope this helps. Please let us know if this solves your issues and if you encounter any further trouble.
<image012.png>
Andy LoPresto
alopresto@apache.org<ma...@apache.org>
alopresto.apache@gmail.com<ma...@gmail.com>
PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4 BACE 3C6E F65B 2F7D EF69
On Jun 20, 2017, at 5:52 AM, Karsten Zakarias <KZ...@foss.dk>> wrote:
Hi all,
I’m new to Apache Nifi and having problems writing a POST/PUT request to an external db. Any advice/links to material and prior threats would be greatly appreciated.
My setup is the following:
- I have BatchIQ Apache Nifi running on an AWS EC2 instance.
- A GetFTP processor ingests csv files
- Each value is extracted using regex and combined into a JSON object using an AttributesToJSON processor
- I now want to use a InvokeHTTP to write the json object to an airtable db and a PutSQL to write to a SQL db.
- How do I setup InvokeHTTP correctly.
- How do I configure PutSQL correctly on a remote instance with a database driver.
Thanks a lot in advance!
Karsten.
AttributesToJSON:
<image003.png>
InvokeHTTP:
<image001.png><image002.png>
<image004.png>
PutSQL:
<image006.png>
<image007.png>
Best regards / Med venlig hilsen
Karsten Zakarias
Product Manager, M.Sc.
IT & Digital Business
<image005.jpg>
Phone:
Mobile:
E-mail:
+45 4820 8514
+45 2761 6920
KZA@foss.dk<ma...@foss.dk>
<image008.jpg>
FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
________________________________
FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.
RE: Problems getting invokeHTTP/PutSQL to work
Posted by Karsten Zakarias <KZ...@foss.dk>.
Hi Andy,
It worked. Thanks a lot. Sorry for the prior incomplete mail.
In my invokeHTTP request I need to configure the header to include the Authentication and the body to include a JSON object.
I have an AttributesToJSON that delivers a JSON object to my invokeHTTP:
[cid:image004.png@01D2EB3E.38F70C20]
AttributesToJSON:
[cid:image005.png@01D2EB3E.38F70C20]
InvokeHTTP:
As I understand it the AttributesToJSON is automatically added to the body and the header information needs to go into the Attributes To Send:
[cid:image006.png@01D2EB3E.38F70C20]
According to the Airtable API the header needs to include a ‘content-type’ and ‘authentication’ and the body a ‘fields’ object.
[cid:image001.png@01D2EB3E.CA9640B0]
How is that configured?
Best regards / Med venlig hilsen
Karsten Zakarias
Product Manager, M.Sc.
IT & Digital Business
[cid:image007.jpg@01D2EB3E.38F70C20]
Phone:
Mobile:
E-mail:
+45 4820 8514
+45 2761 6920
KZA@foss.dk<ma...@foss.dk>
[cid:image008.jpg@01D2EB3E.38F70C20]
FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
________________________________
FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.
From: Andy LoPresto [mailto:alopresto@apache.org]
Sent: 22. juni 2017 01:05
To: users@nifi.apache.org<ma...@nifi.apache.org>
Subject: Re: Problems getting invokeHTTP/PutSQL to work
Can you please share a screenshot of the SSLContextService properties again? There should be only four values populated:
Truststore Filename: /etc/pki/java/cacerts
Truststore Password: “changeit”
Truststore Type: JKS
SSL Protocol: TLS
I do not believe Amazon changes the default password for cacerts, but you can verify it is correct by running this command:
$ keytool -keystore /etc/pki/java/cacerts -list -v -storepass changeit
It will either succeed and display the trusted certificate entries or warn you that the password is incorrect.
Andy LoPresto
alopresto@apache.org<ma...@apache.org>
alopresto.apache@gmail.com<ma...@gmail.com>
PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4 BACE 3C6E F65B 2F7D EF69
On Jun 21, 2017, at 5:02 PM, Karsten Zakarias <KZ...@foss.dk>> wrote:
Thanks James and Andy,
I located the cacerts. So far so good.
I still get an error.
Br,
Karsten.
<image001.jpg>
<image002.jpg>
Best regards / Med venlig hilsen
Karsten Zakarias
Product Manager, M.Sc.
IT & Digital Business
<image003.jpg>
Phone:
Mobile:
E-mail:
+45 4820 8514
+45 2761 6920
KZA@foss.dk<ma...@foss.dk>
<image004.jpg>
FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
________________________________
FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.
From: James Wing [mailto:jvwing@gmail.com]
Sent: 21. juni 2017 15:27
To: users@nifi.apache.org<ma...@nifi.apache.org>
Subject: Re: Problems getting invokeHTTP/PutSQL to work
As Andy mentioned below, the StandardSSLContextService's Truststore Filename should probably be configured to point to a file named 'cacerts', unless you have a custom truststore to use. I believe /etc/pki/java/cacerts is typical path for an EC2 instance. The path to the JRE is not sufficient.
Thanks,
James
On Wed, Jun 21, 2017 at 1:54 AM, Karsten Zakarias <KZ...@foss.dk>> wrote:
Hi Andy,
Thanks for your reply. This is how far I’ve gotten.
My invokeHTTP configuration looks like this:
<image007.png>
My ContextService looks like this:
<image008.png>
As my Apache Nifi is on an EC2 instance I ssh in and tried to locate the Java Runtime Environment and this is what I found under /usr/lib/jvm. I don’t know if that is the correct location:
<image010.png>
This is the ContextService error:
<image011.jpg>
Where am I getting it wrong?
Best regards / Med venlig hilsen
Karsten Zakarias
Product Manager, M.Sc.
IT & Digital Business
<image003.jpg>
Phone:
Mobile:
E-mail:
+45 4820 8514
+45 2761 6920
KZA@foss.dk<ma...@foss.dk>
<image004.jpg>
FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
________________________________
FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.
From: Andy LoPresto [mailto:alopresto@apache.org<ma...@apache.org>]
Sent: 20. juni 2017 15:37
To: users@nifi.apache.org<ma...@nifi.apache.org>
Subject: Re: Problems getting invokeHTTP/PutSQL to work
Hi Karsten,
Are you getting a specific error message when using InvokeHTTP or PutSQL? I don’t have an Airtable account, but it looks like a pretty straightforward REST API over HTTPS. From your screenshot, I believe you need to configure an SSLContextService in order to validate the certificate presented by Airtable. You should configure it as shown below. The reason is that NiFi by default does not trust any certificates. When it tries to make a request to Airtable, Airtable tries to negotiate an encrypted TLS connection and NiFi attempts to verify the server’s identity using the presented certificate chain. Without a “truststore” (a collection of trusted certificates), it fails to do this. Java provides a default truststore to allow these connections to succeed.
Steps for creating & linking SSLContextService:
1. From the Operate palette, click the gear icon (“Configuration”) and switch to the “Controller Services” tab. You can also skip directly there from the InvokeHTTP properties tab by opening the dropdown for “SSL Context Service” and selecting “Create new service…"
2. Create a new StandardSSLContextService. This controller service provides a common interface to the TLS (nee SSL) settings that various components use.
3. You do not need to populate the first four values (the “keystore” values) unless Airtable requires client authentication for TLS, which I highly doubt. Instead, populate the following fields:
Truststore Filename: <path to your Java Runtime Environment’s “cacerts” file>
Truststore Password: “changeit”
Truststore Type: JKS
SSL Protocol: TLS
4. Save the controller service and “enable” it by clicking the lightning icon on the far right.
5. Once this service is enabled, your processor should be valid (as shown by a red “stopped” icon rather than a yellow “caution” icon).
6. For debugging, I would also recommend the following settings:
Put Response Body in Attribute: “response” <— This will put the response body into an attribute so you can view it with a LogAttribute processor or by examining the flowfile in the queue
Always Output Response: true <— This will force a flowfile to be generated even if the HTTP status is one that does not send a response body
7. You provided a custom request header with your API key, but you named it “Authentication” instead of “Authorization”. You’ll need to change this to “Authorization”.
8. You may need to set the Content Type header to explicitly be “application/json”. The mime type is correctly set coming out of the AttributesToJSON processor, so I doubt this is the issue.
There may be an Airtable tool for viewing the incoming requests and determining why the request body could not be parsed. If not, I suggest setting up a simple HTTP server on your local device and temporarily pointing the request there (or copying the InvokeHTTP processor and doing it in parallel) to debug the request. You can also use tools like Paw or Postman to craft POST calls to Airtable manually until you are successful and compare to the output of the InvokeHTTP processor — you can enable DEBUG level logging for it by modifying your $NIFI_HOME/conf/logback.xml file and adding the line:
<logger name=“org.apache.nifi.processors.standard.InvokeHTTP" level="DEBUG"/>
Hope this helps. Please let us know if this solves your issues and if you encounter any further trouble.
<image012.png>
Andy LoPresto
alopresto@apache.org<ma...@apache.org>
alopresto.apache@gmail.com<ma...@gmail.com>
PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4 BACE 3C6E F65B 2F7D EF69
On Jun 20, 2017, at 5:52 AM, Karsten Zakarias <KZ...@foss.dk>> wrote:
Hi all,
I’m new to Apache Nifi and having problems writing a POST/PUT request to an external db. Any advice/links to material and prior threats would be greatly appreciated.
My setup is the following:
- I have BatchIQ Apache Nifi running on an AWS EC2 instance.
- A GetFTP processor ingests csv files
- Each value is extracted using regex and combined into a JSON object using an AttributesToJSON processor
- I now want to use a InvokeHTTP to write the json object to an airtable db and a PutSQL to write to a SQL db.
- How do I setup InvokeHTTP correctly.
- How do I configure PutSQL correctly on a remote instance with a database driver.
Thanks a lot in advance!
Karsten.
AttributesToJSON:
<image003.png>
InvokeHTTP:
<image001.png><image002.png>
<image004.png>
PutSQL:
<image006.png>
<image007.png>
Best regards / Med venlig hilsen
Karsten Zakarias
Product Manager, M.Sc.
IT & Digital Business
<image005.jpg>
Phone:
Mobile:
E-mail:
+45 4820 8514
+45 2761 6920
KZA@foss.dk<ma...@foss.dk>
<image008.jpg>
FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
________________________________
FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.
Re: Problems getting invokeHTTP/PutSQL to work
Posted by Andy LoPresto <al...@apache.org>.
Can you please share a screenshot of the SSLContextService properties again? There should be only four values populated:
> Truststore Filename: /etc/pki/java/cacerts
> Truststore Password: “changeit”
> Truststore Type: JKS
> SSL Protocol: TLS
I do not believe Amazon changes the default password for cacerts, but you can verify it is correct by running this command:
$ keytool -keystore /etc/pki/java/cacerts -list -v -storepass changeit
It will either succeed and display the trusted certificate entries or warn you that the password is incorrect.
Andy LoPresto
alopresto@apache.org
alopresto.apache@gmail.com
PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4 BACE 3C6E F65B 2F7D EF69
> On Jun 21, 2017, at 5:02 PM, Karsten Zakarias <KZ...@foss.dk> wrote:
>
> Thanks James and Andy,
>
> I located the cacerts. So far so good.
> I still get an error.
>
> Br,
> Karsten.
> <image001.jpg>
>
> <image002.jpg>
>
> Best regards / Med venlig hilsen
>
> Karsten Zakarias
> Product Manager, M.Sc.
> IT & Digital Business
>
>
>
> <image003.jpg>
> Phone:
> Mobile:
> E-mail:
> +45 4820 8514
> +45 2761 6920
> KZA@foss.dk <ma...@foss.dk>
>
>
> <image004.jpg>
>
> FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
> FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.
>
>
> From: James Wing [mailto:jvwing@gmail.com <ma...@gmail.com>]
> Sent: 21. juni 2017 15:27
> To: users@nifi.apache.org <ma...@nifi.apache.org>
> Subject: Re: Problems getting invokeHTTP/PutSQL to work
>
> As Andy mentioned below, the StandardSSLContextService's Truststore Filename should probably be configured to point to a file named 'cacerts', unless you have a custom truststore to use. I believe /etc/pki/java/cacerts is typical path for an EC2 instance. The path to the JRE is not sufficient.
>
> Thanks,
>
> James
>
> On Wed, Jun 21, 2017 at 1:54 AM, Karsten Zakarias <KZA@foss.dk <ma...@foss.dk>> wrote:
> Hi Andy,
>
> Thanks for your reply. This is how far I’ve gotten.
>
> My invokeHTTP configuration looks like this:
> <image007.png>
>
> My ContextService looks like this:
> <image008.png>
>
> As my Apache Nifi is on an EC2 instance I ssh in and tried to locate the Java Runtime Environment and this is what I found under /usr/lib/jvm. I don’t know if that is the correct location:
> <image010.png>
>
> This is the ContextService error:
> <image011.jpg>
>
> Where am I getting it wrong?
>
>
> Best regards / Med venlig hilsen
>
> Karsten Zakarias
> Product Manager, M.Sc.
> IT & Digital Business
>
>
>
> <image003.jpg>
> Phone:
> Mobile:
> E-mail:
> +45 4820 8514
> +45 2761 6920
> KZA@foss.dk <ma...@foss.dk>
>
>
> <image004.jpg>
>
> FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
> FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.
>
>
> From: Andy LoPresto [mailto:alopresto@apache.org <ma...@apache.org>]
> Sent: 20. juni 2017 15:37
> To: users@nifi.apache.org <ma...@nifi.apache.org>
> Subject: Re: Problems getting invokeHTTP/PutSQL to work
>
> Hi Karsten,
>
> Are you getting a specific error message when using InvokeHTTP or PutSQL? I don’t have an Airtable account, but it looks like a pretty straightforward REST API over HTTPS. From your screenshot, I believe you need to configure an SSLContextService in order to validate the certificate presented by Airtable. You should configure it as shown below. The reason is that NiFi by default does not trust any certificates. When it tries to make a request to Airtable, Airtable tries to negotiate an encrypted TLS connection and NiFi attempts to verify the server’s identity using the presented certificate chain. Without a “truststore” (a collection of trusted certificates), it fails to do this. Java provides a default truststore to allow these connections to succeed.
>
> Steps for creating & linking SSLContextService:
>
> 1. From the Operate palette, click the gear icon (“Configuration”) and switch to the “Controller Services” tab. You can also skip directly there from the InvokeHTTP properties tab by opening the dropdown for “SSL Context Service” and selecting “Create new service…"
> 2. Create a new StandardSSLContextService. This controller service provides a common interface to the TLS (nee SSL) settings that various components use.
> 3. You do not need to populate the first four values (the “keystore” values) unless Airtable requires client authentication for TLS, which I highly doubt. Instead, populate the following fields:
> Truststore Filename: <path to your Java Runtime Environment’s “cacerts” file>
> Truststore Password: “changeit”
> Truststore Type: JKS
> SSL Protocol: TLS
> 4. Save the controller service and “enable” it by clicking the lightning icon on the far right.
> 5. Once this service is enabled, your processor should be valid (as shown by a red “stopped” icon rather than a yellow “caution” icon).
> 6. For debugging, I would also recommend the following settings:
> Put Response Body in Attribute: “response” <— This will put the response body into an attribute so you can view it with a LogAttribute processor or by examining the flowfile in the queue
> Always Output Response: true <— This will force a flowfile to be generated even if the HTTP status is one that does not send a response body
> 7. You provided a custom request header with your API key, but you named it “Authentication” instead of “Authorization”. You’ll need to change this to “Authorization”.
> 8. You may need to set the Content Type header to explicitly be “application/json”. The mime type is correctly set coming out of the AttributesToJSON processor, so I doubt this is the issue.
>
> There may be an Airtable tool for viewing the incoming requests and determining why the request body could not be parsed. If not, I suggest setting up a simple HTTP server on your local device and temporarily pointing the request there (or copying the InvokeHTTP processor and doing it in parallel) to debug the request. You can also use tools like Paw or Postman to craft POST calls to Airtable manually until you are successful and compare to the output of the InvokeHTTP processor — you can enable DEBUG level logging for it by modifying your $NIFI_HOME/conf/logback.xml file and adding the line:
>
> <logger name=“org.apache.nifi.processors.standard.InvokeHTTP" level="DEBUG"/>
>
> Hope this helps. Please let us know if this solves your issues and if you encounter any further trouble.
>
>
> <image012.png>
>
>
> Andy LoPresto
> alopresto@apache.org <ma...@apache.org>
> alopresto.apache@gmail.com <ma...@gmail.com>
> PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4 BACE 3C6E F65B 2F7D EF69
>
> On Jun 20, 2017, at 5:52 AM, Karsten Zakarias <KZA@foss.dk <ma...@foss.dk>> wrote:
>
> Hi all,
>
> I’m new to Apache Nifi and having problems writing a POST/PUT request to an external db. Any advice/links to material and prior threats would be greatly appreciated.
>
> My setup is the following:
> - I have BatchIQ Apache Nifi running on an AWS EC2 instance.
> - A GetFTP processor ingests csv files
> - Each value is extracted using regex and combined into a JSON object using an AttributesToJSON processor
> - I now want to use a InvokeHTTP to write the json object to an airtable db and a PutSQL to write to a SQL db.
>
> - How do I setup InvokeHTTP correctly.
>
> - How do I configure PutSQL correctly on a remote instance with a database driver.
>
>
> Thanks a lot in advance!
> Karsten.
>
>
> AttributesToJSON:
> <image003.png>
>
> InvokeHTTP:
> <image001.png><image002.png>
> <image004.png>
>
>
> PutSQL:
> <image006.png>
> <image007.png>
>
>
>
> Best regards / Med venlig hilsen
>
> Karsten Zakarias
> Product Manager, M.Sc.
> IT & Digital Business
>
>
>
> <image005.jpg>
> Phone:
> Mobile:
> E-mail:
> +45 4820 8514
> +45 2761 6920
> KZA@foss.dk <ma...@foss.dk>
>
>
> <image008.jpg>
>
> FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
> FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.
>
>
RE: Problems getting invokeHTTP/PutSQL to work
Posted by Karsten Zakarias <KZ...@foss.dk>.
Thanks James and Andy,
I located the cacerts. So far so good.
I still get an error.
Br,
Karsten.
[cid:image001.jpg@01D2EAE2.8431A440]
[cid:image002.jpg@01D2EAE2.8431A440]
Best regards / Med venlig hilsen
Karsten Zakarias
Product Manager, M.Sc.
IT & Digital Business
[cid:image003.jpg@01D2EAE2.8431A440]
Phone:
Mobile:
E-mail:
+45 4820 8514
+45 2761 6920
KZA@foss.dk
[cid:image004.jpg@01D2EAE2.8431A440]
FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
________________________________
FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.
From: James Wing [mailto:jvwing@gmail.com]
Sent: 21. juni 2017 15:27
To: users@nifi.apache.org
Subject: Re: Problems getting invokeHTTP/PutSQL to work
As Andy mentioned below, the StandardSSLContextService's Truststore Filename should probably be configured to point to a file named 'cacerts', unless you have a custom truststore to use. I believe /etc/pki/java/cacerts is typical path for an EC2 instance. The path to the JRE is not sufficient.
Thanks,
James
On Wed, Jun 21, 2017 at 1:54 AM, Karsten Zakarias <KZ...@foss.dk>> wrote:
Hi Andy,
Thanks for your reply. This is how far I’ve gotten.
My invokeHTTP configuration looks like this:
[cid:image007.png@01D2EAE1.EB17C370]
My ContextService looks like this:
[cid:image008.png@01D2EAE1.EB17C370]
As my Apache Nifi is on an EC2 instance I ssh in and tried to locate the Java Runtime Environment and this is what I found under /usr/lib/jvm. I don’t know if that is the correct location:
[cid:image010.png@01D2EAE1.EB17C370]
This is the ContextService error:
[cid:image011.jpg@01D2EAE1.EB17C370]
Where am I getting it wrong?
Best regards / Med venlig hilsen
Karsten Zakarias
Product Manager, M.Sc.
IT & Digital Business
[cid:image003.jpg@01D2EAE2.8431A440]
Phone:
Mobile:
E-mail:
+45 4820 8514
+45 2761 6920
KZA@foss.dk<ma...@foss.dk>
[cid:image004.jpg@01D2EAE2.8431A440]
FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
________________________________
FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.
From: Andy LoPresto [mailto:alopresto@apache.org<ma...@apache.org>]
Sent: 20. juni 2017 15:37
To: users@nifi.apache.org<ma...@nifi.apache.org>
Subject: Re: Problems getting invokeHTTP/PutSQL to work
Hi Karsten,
Are you getting a specific error message when using InvokeHTTP or PutSQL? I don’t have an Airtable account, but it looks like a pretty straightforward REST API over HTTPS. From your screenshot, I believe you need to configure an SSLContextService in order to validate the certificate presented by Airtable. You should configure it as shown below. The reason is that NiFi by default does not trust any certificates. When it tries to make a request to Airtable, Airtable tries to negotiate an encrypted TLS connection and NiFi attempts to verify the server’s identity using the presented certificate chain. Without a “truststore” (a collection of trusted certificates), it fails to do this. Java provides a default truststore to allow these connections to succeed.
Steps for creating & linking SSLContextService:
1. From the Operate palette, click the gear icon (“Configuration”) and switch to the “Controller Services” tab. You can also skip directly there from the InvokeHTTP properties tab by opening the dropdown for “SSL Context Service” and selecting “Create new service…"
2. Create a new StandardSSLContextService. This controller service provides a common interface to the TLS (nee SSL) settings that various components use.
3. You do not need to populate the first four values (the “keystore” values) unless Airtable requires client authentication for TLS, which I highly doubt. Instead, populate the following fields:
Truststore Filename: <path to your Java Runtime Environment’s “cacerts” file>
Truststore Password: “changeit”
Truststore Type: JKS
SSL Protocol: TLS
4. Save the controller service and “enable” it by clicking the lightning icon on the far right.
5. Once this service is enabled, your processor should be valid (as shown by a red “stopped” icon rather than a yellow “caution” icon).
6. For debugging, I would also recommend the following settings:
Put Response Body in Attribute: “response” <— This will put the response body into an attribute so you can view it with a LogAttribute processor or by examining the flowfile in the queue
Always Output Response: true <— This will force a flowfile to be generated even if the HTTP status is one that does not send a response body
7. You provided a custom request header with your API key, but you named it “Authentication” instead of “Authorization”. You’ll need to change this to “Authorization”.
8. You may need to set the Content Type header to explicitly be “application/json”. The mime type is correctly set coming out of the AttributesToJSON processor, so I doubt this is the issue.
There may be an Airtable tool for viewing the incoming requests and determining why the request body could not be parsed. If not, I suggest setting up a simple HTTP server on your local device and temporarily pointing the request there (or copying the InvokeHTTP processor and doing it in parallel) to debug the request. You can also use tools like Paw or Postman to craft POST calls to Airtable manually until you are successful and compare to the output of the InvokeHTTP processor — you can enable DEBUG level logging for it by modifying your $NIFI_HOME/conf/logback.xml file and adding the line:
<logger name=“org.apache.nifi.processors.standard.InvokeHTTP" level="DEBUG"/>
Hope this helps. Please let us know if this solves your issues and if you encounter any further trouble.
[cid:image012.png@01D2EAE1.EB17C370]
Andy LoPresto
alopresto@apache.org<ma...@apache.org>
alopresto.apache@gmail.com<ma...@gmail.com>
PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4 BACE 3C6E F65B 2F7D EF69
On Jun 20, 2017, at 5:52 AM, Karsten Zakarias <KZ...@foss.dk>> wrote:
Hi all,
I’m new to Apache Nifi and having problems writing a POST/PUT request to an external db. Any advice/links to material and prior threats would be greatly appreciated.
My setup is the following:
- I have BatchIQ Apache Nifi running on an AWS EC2 instance.
- A GetFTP processor ingests csv files
- Each value is extracted using regex and combined into a JSON object using an AttributesToJSON processor
- I now want to use a InvokeHTTP to write the json object to an airtable db and a PutSQL to write to a SQL db.
- How do I setup InvokeHTTP correctly.
- How do I configure PutSQL correctly on a remote instance with a database driver.
Thanks a lot in advance!
Karsten.
AttributesToJSON:
<image003.png>
InvokeHTTP:
<image001.png><image002.png>
<image004.png>
PutSQL:
<image006.png>
<image007.png>
Best regards / Med venlig hilsen
Karsten Zakarias
Product Manager, M.Sc.
IT & Digital Business
<image005.jpg>
Phone:
Mobile:
E-mail:
+45 4820 8514
+45 2761 6920
KZA@foss.dk<ma...@foss.dk>
<image008.jpg>
FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
________________________________
FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.
Re: Problems getting invokeHTTP/PutSQL to work
Posted by Andy LoPresto <al...@apache.org>.
Thanks for jumping in James. My screenshot truncated the path so it was very unclear that the full path is necessary. Much appreciated.
Andy LoPresto
alopresto@apache.org
alopresto.apache@gmail.com
PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4 BACE 3C6E F65B 2F7D EF69
> On Jun 21, 2017, at 9:27 AM, James Wing <jv...@gmail.com> wrote:
>
> As Andy mentioned below, the StandardSSLContextService's Truststore Filename should probably be configured to point to a file named 'cacerts', unless you have a custom truststore to use. I believe /etc/pki/java/cacerts is typical path for an EC2 instance. The path to the JRE is not sufficient.
>
> Thanks,
>
> James
>
> On Wed, Jun 21, 2017 at 1:54 AM, Karsten Zakarias <KZA@foss.dk <ma...@foss.dk>> wrote:
> Hi Andy,
>
>
>
> Thanks for your reply. This is how far I’ve gotten.
>
>
>
> My invokeHTTP configuration looks like this:
>
> <image001.png>
>
>
>
> My ContextService looks like this:
>
> <image002.png>
>
>
>
> As my Apache Nifi is on an EC2 instance I ssh in and tried to locate the Java Runtime Environment and this is what I found under /usr/lib/jvm. I don’t know if that is the correct location:
>
> <image006.png>
>
>
>
> This is the ContextService error:
>
> <image009.jpg>
>
>
>
> Where am I getting it wrong?
>
>
>
>
> Best regards / Med venlig hilsen
>
> Karsten Zakarias
> Product Manager, M.Sc.
>
> IT & Digital Business
>
>
>
>
> <image010.jpg>
>
> Phone:
> Mobile:
> E-mail:
>
> +45 4820 8514
> +45 2761 6920
> KZA@foss.dk <ma...@foss.dk>
>
>
>
> <image011.jpg>
>
> FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
>
> FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.
>
>
>
>
>
> From: Andy LoPresto [mailto:alopresto@apache.org <ma...@apache.org>]
> Sent: 20. juni 2017 15:37
> To: users@nifi.apache.org <ma...@nifi.apache.org>
> Subject: Re: Problems getting invokeHTTP/PutSQL to work
>
>
>
> Hi Karsten,
>
>
>
> Are you getting a specific error message when using InvokeHTTP or PutSQL? I don’t have an Airtable account, but it looks like a pretty straightforward REST API over HTTPS. From your screenshot, I believe you need to configure an SSLContextService in order to validate the certificate presented by Airtable. You should configure it as shown below. The reason is that NiFi by default does not trust any certificates. When it tries to make a request to Airtable, Airtable tries to negotiate an encrypted TLS connection and NiFi attempts to verify the server’s identity using the presented certificate chain. Without a “truststore” (a collection of trusted certificates), it fails to do this. Java provides a default truststore to allow these connections to succeed.
>
>
>
> Steps for creating & linking SSLContextService:
>
>
>
> 1. From the Operate palette, click the gear icon (“Configuration”) and switch to the “Controller Services” tab. You can also skip directly there from the InvokeHTTP properties tab by opening the dropdown for “SSL Context Service” and selecting “Create new service…"
>
> 2. Create a new StandardSSLContextService. This controller service provides a common interface to the TLS (nee SSL) settings that various components use.
>
> 3. You do not need to populate the first four values (the “keystore” values) unless Airtable requires client authentication for TLS, which I highly doubt. Instead, populate the following fields:
>
> Truststore Filename: <path to your Java Runtime Environment’s “cacerts” file>
>
> Truststore Password: “changeit”
>
> Truststore Type: JKS
>
> SSL Protocol: TLS
>
> 4. Save the controller service and “enable” it by clicking the lightning icon on the far right.
>
> 5. Once this service is enabled, your processor should be valid (as shown by a red “stopped” icon rather than a yellow “caution” icon).
>
> 6. For debugging, I would also recommend the following settings:
>
> Put Response Body in Attribute: “response” <— This will put the response body into an attribute so you can view it with a LogAttribute processor or by examining the flowfile in the queue
>
> Always Output Response: true <— This will force a flowfile to be generated even if the HTTP status is one that does not send a response body
>
> 7. You provided a custom request header with your API key, but you named it “Authentication” instead of “Authorization”. You’ll need to change this to “Authorization”.
>
> 8. You may need to set the Content Type header to explicitly be “application/json”. The mime type is correctly set coming out of the AttributesToJSON processor, so I doubt this is the issue.
>
>
>
> There may be an Airtable tool for viewing the incoming requests and determining why the request body could not be parsed. If not, I suggest setting up a simple HTTP server on your local device and temporarily pointing the request there (or copying the InvokeHTTP processor and doing it in parallel) to debug the request. You can also use tools like Paw or Postman to craft POST calls to Airtable manually until you are successful and compare to the output of the InvokeHTTP processor — you can enable DEBUG level logging for it by modifying your $NIFI_HOME/conf/logback.xml file and adding the line:
>
>
>
> <logger name=“org.apache.nifi.processors.standard.InvokeHTTP" level="DEBUG"/>
>
>
>
> Hope this helps. Please let us know if this solves your issues and if you encounter any further trouble.
>
>
>
>
>
> <image005.png>
>
>
>
>
>
> Andy LoPresto
>
> alopresto@apache.org <ma...@apache.org>
> alopresto.apache@gmail.com <ma...@gmail.com>
> PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4 BACE 3C6E F65B 2F7D EF69
>
>
>
> On Jun 20, 2017, at 5:52 AM, Karsten Zakarias <KZA@foss.dk <ma...@foss.dk>> wrote:
>
>
>
> Hi all,
>
>
>
> I’m new to Apache Nifi and having problems writing a POST/PUT request to an external db. Any advice/links to material and prior threats would be greatly appreciated.
>
>
>
> My setup is the following:
>
> - I have BatchIQ Apache Nifi running on an AWS EC2 instance.
>
> - A GetFTP processor ingests csv files
>
> - Each value is extracted using regex and combined into a JSON object using an AttributesToJSON processor
>
> - I now want to use a InvokeHTTP to write the json object to an airtable db and a PutSQL to write to a SQL db.
>
>
>
> - How do I setup InvokeHTTP correctly.
>
>
>
> - How do I configure PutSQL correctly on a remote instance with a database driver.
>
>
>
>
>
> Thanks a lot in advance!
>
> Karsten.
>
>
>
>
>
> AttributesToJSON:
>
> <image003.png>
>
>
>
> InvokeHTTP:
>
> <image001.png><image002.png>
>
> <image004.png>
>
>
>
>
>
> PutSQL:
>
> <image006.png>
>
> <image007.png>
>
>
>
>
>
> Best regards / Med venlig hilsen
>
> Karsten Zakarias
> Product Manager, M.Sc.
>
> IT & Digital Business
>
>
>
>
> <image005.jpg>
>
> Phone:
> Mobile:
> E-mail:
>
> +45 4820 8514
> +45 2761 6920
> KZA@foss.dk <ma...@foss.dk>
>
>
>
> <image008.jpg>
>
> FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
>
> FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.
>
>
>
>
Re: Problems getting invokeHTTP/PutSQL to work
Posted by James Wing <jv...@gmail.com>.
As Andy mentioned below, the StandardSSLContextService's Truststore
Filename should probably be configured to point to a file named 'cacerts',
unless you have a custom truststore to use. I believe
/etc/pki/java/cacerts is typical path for an EC2 instance. The path to the
JRE is not sufficient.
Thanks,
James
On Wed, Jun 21, 2017 at 1:54 AM, Karsten Zakarias <KZ...@foss.dk> wrote:
> Hi Andy,
>
>
>
> Thanks for your reply. This is how far I’ve gotten.
>
>
>
> My invokeHTTP configuration looks like this:
>
>
>
> My ContextService looks like this:
>
>
>
> As my Apache Nifi is on an EC2 instance I ssh in and tried to locate the
> Java Runtime Environment and this is what I found under /usr/lib/jvm. I
> don’t know if that is the correct location:
>
>
>
> This is the ContextService error:
>
>
>
> Where am I getting it wrong?
>
>
>
>
> Best regards / Med venlig hilsen
>
>
> *Karsten Zakarias *Product Manager, M.Sc.
>
> IT & Digital Business
>
>
>
>
> Phone:
> Mobile:
> E-mail:
>
> +45 4820 8514
> +45 2761 6920
> KZA@foss.dk
>
>
>
>
>
>
> FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
> ------------------------------
>
> FOSS provides and supports dedicated, rapid and accurate analytical
> solutions, which analyze and control the quality and production of
> agricultural and food products, for the enhancement of our customers’
> business and better food quality for consumers.
>
>
>
>
>
> *From:* Andy LoPresto [mailto:alopresto@apache.org]
> *Sent:* 20. juni 2017 15:37
> *To:* users@nifi.apache.org
> *Subject:* Re: Problems getting invokeHTTP/PutSQL to work
>
>
>
> Hi Karsten,
>
>
>
> Are you getting a specific error message when using InvokeHTTP or PutSQL?
> I don’t have an Airtable account, but it looks like a pretty
> straightforward REST API over HTTPS. From your screenshot, I believe you
> need to configure an SSLContextService in order to validate the certificate
> presented by Airtable. You should configure it as shown below. The reason
> is that NiFi by default does not trust any certificates. When it tries to
> make a request to Airtable, Airtable tries to negotiate an encrypted TLS
> connection and NiFi attempts to verify the server’s identity using the
> presented certificate chain. Without a “truststore” (a collection of
> trusted certificates), it fails to do this. Java provides a default
> truststore to allow these connections to succeed.
>
>
>
> Steps for creating & linking SSLContextService:
>
>
>
> 1. From the Operate palette, click the gear icon (“Configuration”) and
> switch to the “Controller Services” tab. You can also skip directly there
> from the InvokeHTTP properties tab by opening the dropdown for “SSL Context
> Service” and selecting “Create new service…"
>
> 2. Create a new StandardSSLContextService. This controller service
> provides a common interface to the TLS (nee SSL) settings that various
> components use.
>
> 3. You do not need to populate the first four values (the “keystore”
> values) unless Airtable requires client authentication for TLS, which I
> highly doubt. Instead, populate the following fields:
>
> Truststore Filename: <path to your Java Runtime Environment’s
> “cacerts” file>
>
> Truststore Password: “changeit”
>
> Truststore Type: JKS
>
> SSL Protocol: TLS
>
> 4. Save the controller service and “enable” it by clicking the lightning
> icon on the far right.
>
> 5. Once this service is enabled, your processor should be valid (as shown
> by a red “stopped” icon rather than a yellow “caution” icon).
>
> 6. For debugging, I would also recommend the following settings:
>
> Put Response Body in Attribute: “response” <— This will put
> the response body into an attribute so you can view it with a LogAttribute
> processor or by examining the flowfile in the queue
>
> Always Output Response: true <— This will force a flowfile to
> be generated even if the HTTP status is one that does not send a response
> body
>
> 7. You provided a custom request header with your API key, but you named
> it “Authentication” instead of “Authorization”. You’ll need to change this
> to “Authorization”.
>
> 8. You may need to set the Content Type header to explicitly be
> “application/json”. The mime type is correctly set coming out of the
> AttributesToJSON processor, so I doubt this is the issue.
>
>
>
> There may be an Airtable tool for viewing the incoming requests and
> determining why the request body could not be parsed. If not, I suggest
> setting up a simple HTTP server on your local device and temporarily
> pointing the request there (or copying the InvokeHTTP processor and doing
> it in parallel) to debug the request. You can also use tools like Paw or
> Postman to craft POST calls to Airtable manually until you are successful
> and compare to the output of the InvokeHTTP processor — you can enable
> DEBUG level logging for it by modifying your $NIFI_HOME/conf/logback.xml
> file and adding the line:
>
>
>
> <logger name=“org.apache.nifi.processors.standard.InvokeHTTP"
> level="DEBUG"/>
>
>
>
> Hope this helps. Please let us know if this solves your issues and if you
> encounter any further trouble.
>
>
>
>
>
>
>
>
>
> Andy LoPresto
>
> alopresto@apache.org
>
> *alopresto.apache@gmail.com <al...@gmail.com>*
>
> PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4 BACE 3C6E F65B 2F7D EF69
>
>
>
> On Jun 20, 2017, at 5:52 AM, Karsten Zakarias <KZ...@foss.dk> wrote:
>
>
>
> Hi all,
>
>
>
> I’m new to Apache Nifi and having problems writing a POST/PUT request to
> an external db. Any advice/links to material and prior threats would be
> greatly appreciated.
>
>
>
> My setup is the following:
>
> - I have BatchIQ Apache Nifi running on an AWS EC2 instance.
>
> - A GetFTP processor ingests csv files
>
> - Each value is extracted using regex and combined into a JSON
> object using an AttributesToJSON processor
>
> - I now want to use a InvokeHTTP to write the json object to an
> airtable db and a PutSQL to write to a SQL db.
>
>
>
> - How do I setup InvokeHTTP correctly.
>
>
>
> - How do I configure PutSQL correctly on a remote instance with
> a database driver.
>
>
>
>
>
> Thanks a lot in advance!
>
> Karsten.
>
>
>
>
>
> AttributesToJSON:
>
> <image003.png>
>
>
>
> InvokeHTTP:
>
> <image001.png><image002.png>
>
> <image004.png>
>
>
>
>
>
> PutSQL:
>
> <image006.png>
>
> <image007.png>
>
>
>
>
>
> Best regards / Med venlig hilsen
>
>
> *Karsten Zakarias *Product Manager, M.Sc.
>
> IT & Digital Business
>
>
>
>
> <image005.jpg>
>
> Phone:
> Mobile:
> E-mail:
>
> +45 4820 8514
> +45 2761 6920
> KZA@foss.dk
>
>
>
>
> <image008.jpg>
>
> FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
> ------------------------------
>
> FOSS provides and supports dedicated, rapid and accurate analytical
> solutions, which analyze and control the quality and production of
> agricultural and food products, for the enhancement of our customers’
> business and better food quality for consumers.
>
>
>
RE: Problems getting invokeHTTP/PutSQL to work
Posted by Karsten Zakarias <KZ...@foss.dk>.
Hi Andy,
Thanks for your reply. This is how far I’ve gotten.
My invokeHTTP configuration looks like this:
[cid:image001.png@01D2EA7B.79DBF160]
My ContextService looks like this:
[cid:image002.png@01D2EA7B.79DBF160]
As my Apache Nifi is on an EC2 instance I ssh in and tried to locate the Java Runtime Environment and this is what I found under /usr/lib/jvm. I don’t know if that is the correct location:
[cid:image006.png@01D2EA7B.B4DF8470]
This is the ContextService error:
[cid:image009.jpg@01D2EA7C.C1654710]
Where am I getting it wrong?
Best regards / Med venlig hilsen
Karsten Zakarias
Product Manager, M.Sc.
IT & Digital Business
[cid:image010.jpg@01D2EA7C.C1654710]
Phone:
Mobile:
E-mail:
+45 4820 8514
+45 2761 6920
KZA@foss.dk
[cid:image011.jpg@01D2EA7C.C1654710]
FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
________________________________
FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.
From: Andy LoPresto [mailto:alopresto@apache.org]
Sent: 20. juni 2017 15:37
To: users@nifi.apache.org
Subject: Re: Problems getting invokeHTTP/PutSQL to work
Hi Karsten,
Are you getting a specific error message when using InvokeHTTP or PutSQL? I don’t have an Airtable account, but it looks like a pretty straightforward REST API over HTTPS. From your screenshot, I believe you need to configure an SSLContextService in order to validate the certificate presented by Airtable. You should configure it as shown below. The reason is that NiFi by default does not trust any certificates. When it tries to make a request to Airtable, Airtable tries to negotiate an encrypted TLS connection and NiFi attempts to verify the server’s identity using the presented certificate chain. Without a “truststore” (a collection of trusted certificates), it fails to do this. Java provides a default truststore to allow these connections to succeed.
Steps for creating & linking SSLContextService:
1. From the Operate palette, click the gear icon (“Configuration”) and switch to the “Controller Services” tab. You can also skip directly there from the InvokeHTTP properties tab by opening the dropdown for “SSL Context Service” and selecting “Create new service…"
2. Create a new StandardSSLContextService. This controller service provides a common interface to the TLS (nee SSL) settings that various components use.
3. You do not need to populate the first four values (the “keystore” values) unless Airtable requires client authentication for TLS, which I highly doubt. Instead, populate the following fields:
Truststore Filename: <path to your Java Runtime Environment’s “cacerts” file>
Truststore Password: “changeit”
Truststore Type: JKS
SSL Protocol: TLS
4. Save the controller service and “enable” it by clicking the lightning icon on the far right.
5. Once this service is enabled, your processor should be valid (as shown by a red “stopped” icon rather than a yellow “caution” icon).
6. For debugging, I would also recommend the following settings:
Put Response Body in Attribute: “response” <— This will put the response body into an attribute so you can view it with a LogAttribute processor or by examining the flowfile in the queue
Always Output Response: true <— This will force a flowfile to be generated even if the HTTP status is one that does not send a response body
7. You provided a custom request header with your API key, but you named it “Authentication” instead of “Authorization”. You’ll need to change this to “Authorization”.
8. You may need to set the Content Type header to explicitly be “application/json”. The mime type is correctly set coming out of the AttributesToJSON processor, so I doubt this is the issue.
There may be an Airtable tool for viewing the incoming requests and determining why the request body could not be parsed. If not, I suggest setting up a simple HTTP server on your local device and temporarily pointing the request there (or copying the InvokeHTTP processor and doing it in parallel) to debug the request. You can also use tools like Paw or Postman to craft POST calls to Airtable manually until you are successful and compare to the output of the InvokeHTTP processor — you can enable DEBUG level logging for it by modifying your $NIFI_HOME/conf/logback.xml file and adding the line:
<logger name=“org.apache.nifi.processors.standard.InvokeHTTP" level="DEBUG"/>
Hope this helps. Please let us know if this solves your issues and if you encounter any further trouble.
[cid:image005.png@01D2EA7B.79DBF160]
Andy LoPresto
alopresto@apache.org<ma...@apache.org>
alopresto.apache@gmail.com<ma...@gmail.com>
PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4 BACE 3C6E F65B 2F7D EF69
On Jun 20, 2017, at 5:52 AM, Karsten Zakarias <KZ...@foss.dk>> wrote:
Hi all,
I’m new to Apache Nifi and having problems writing a POST/PUT request to an external db. Any advice/links to material and prior threats would be greatly appreciated.
My setup is the following:
- I have BatchIQ Apache Nifi running on an AWS EC2 instance.
- A GetFTP processor ingests csv files
- Each value is extracted using regex and combined into a JSON object using an AttributesToJSON processor
- I now want to use a InvokeHTTP to write the json object to an airtable db and a PutSQL to write to a SQL db.
- How do I setup InvokeHTTP correctly.
- How do I configure PutSQL correctly on a remote instance with a database driver.
Thanks a lot in advance!
Karsten.
AttributesToJSON:
<image003.png>
InvokeHTTP:
<image001.png><image002.png>
<image004.png>
PutSQL:
<image006.png>
<image007.png>
Best regards / Med venlig hilsen
Karsten Zakarias
Product Manager, M.Sc.
IT & Digital Business
<image005.jpg>
Phone:
Mobile:
E-mail:
+45 4820 8514
+45 2761 6920
KZA@foss.dk<ma...@foss.dk>
<image008.jpg>
FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
________________________________
FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.
Re: Problems getting invokeHTTP/PutSQL to work
Posted by Andy LoPresto <al...@apache.org>.
Hi Karsten,
Are you getting a specific error message when using InvokeHTTP or PutSQL? I don’t have an Airtable account, but it looks like a pretty straightforward REST API over HTTPS. From your screenshot, I believe you need to configure an SSLContextService in order to validate the certificate presented by Airtable. You should configure it as shown below. The reason is that NiFi by default does not trust any certificates. When it tries to make a request to Airtable, Airtable tries to negotiate an encrypted TLS connection and NiFi attempts to verify the server’s identity using the presented certificate chain. Without a “truststore” (a collection of trusted certificates), it fails to do this. Java provides a default truststore to allow these connections to succeed.
Steps for creating & linking SSLContextService:
1. From the Operate palette, click the gear icon (“Configuration”) and switch to the “Controller Services” tab. You can also skip directly there from the InvokeHTTP properties tab by opening the dropdown for “SSL Context Service” and selecting “Create new service…"
2. Create a new StandardSSLContextService. This controller service provides a common interface to the TLS (nee SSL) settings that various components use.
3. You do not need to populate the first four values (the “keystore” values) unless Airtable requires client authentication for TLS, which I highly doubt. Instead, populate the following fields:
Truststore Filename: <path to your Java Runtime Environment’s “cacerts” file>
Truststore Password: “changeit”
Truststore Type: JKS
SSL Protocol: TLS
4. Save the controller service and “enable” it by clicking the lightning icon on the far right.
5. Once this service is enabled, your processor should be valid (as shown by a red “stopped” icon rather than a yellow “caution” icon).
6. For debugging, I would also recommend the following settings:
Put Response Body in Attribute: “response” <— This will put the response body into an attribute so you can view it with a LogAttribute processor or by examining the flowfile in the queue
Always Output Response: true <— This will force a flowfile to be generated even if the HTTP status is one that does not send a response body
7. You provided a custom request header with your API key, but you named it “Authentication” instead of “Authorization”. You’ll need to change this to “Authorization”.
8. You may need to set the Content Type header to explicitly be “application/json”. The mime type is correctly set coming out of the AttributesToJSON processor, so I doubt this is the issue.
There may be an Airtable tool for viewing the incoming requests and determining why the request body could not be parsed. If not, I suggest setting up a simple HTTP server on your local device and temporarily pointing the request there (or copying the InvokeHTTP processor and doing it in parallel) to debug the request. You can also use tools like Paw or Postman to craft POST calls to Airtable manually until you are successful and compare to the output of the InvokeHTTP processor — you can enable DEBUG level logging for it by modifying your $NIFI_HOME/conf/logback.xml file and adding the line:
<logger name=“org.apache.nifi.processors.standard.InvokeHTTP" level="DEBUG"/>
Hope this helps. Please let us know if this solves your issues and if you encounter any further trouble.
Andy LoPresto
alopresto@apache.org
alopresto.apache@gmail.com
PGP Fingerprint: 70EC B3E5 98A6 5A3F D3C4 BACE 3C6E F65B 2F7D EF69
> On Jun 20, 2017, at 5:52 AM, Karsten Zakarias <KZ...@foss.dk> wrote:
>
> Hi all,
>
> I’m new to Apache Nifi and having problems writing a POST/PUT request to an external db. Any advice/links to material and prior threats would be greatly appreciated.
>
> My setup is the following:
> - I have BatchIQ Apache Nifi running on an AWS EC2 instance.
> - A GetFTP processor ingests csv files
> - Each value is extracted using regex and combined into a JSON object using an AttributesToJSON processor
> - I now want to use a InvokeHTTP to write the json object to an airtable db and a PutSQL to write to a SQL db.
>
> - How do I setup InvokeHTTP correctly.
>
> - How do I configure PutSQL correctly on a remote instance with a database driver.
>
>
> Thanks a lot in advance!
> Karsten.
>
>
> AttributesToJSON:
> <image003.png>
>
> InvokeHTTP:
> <image001.png><image002.png>
> <image004.png>
>
>
> PutSQL:
> <image006.png>
> <image007.png>
>
>
>
> Best regards / Med venlig hilsen
>
> Karsten Zakarias
> Product Manager, M.Sc.
> IT & Digital Business
>
>
>
> <image005.jpg>
> Phone:
> Mobile:
> E-mail:
> +45 4820 8514
> +45 2761 6920
> KZA@foss.dk <ma...@foss.dk>
>
>
> <image008.jpg>
>
> FOSS Analytical A/S - Foss Allé 1 DK-3400 Hillerød DK
> FOSS provides and supports dedicated, rapid and accurate analytical solutions, which analyze and control the quality and production of agricultural and food products, for the enhancement of our customers’ business and better food quality for consumers.