Uploaded image for project: 'Apache NiFi'
  1. Apache NiFi
  2. NIFI-3596

GenerateTableFetch - Add attributes to generated flow files to ease SQL query overwrite

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Trivial
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.2.0
    • Component/s: Extensions
    • Labels:
      None

      Description

      The GenerateTableFetch processor will generate a SQL query based on the provided parameters but, if the specific DB adapter is not available, it might be necessary to overwrite the SQL query in the ExecuteSQL processor. To do that it would be nice to have each part of the query as attributes to take advantage of expression language.

      Current workaround (when using GenerateTableFetch against a DB2 database for example) is to have an intermediary ExtractText processor and have a regex extracting each part of the generated query.

        Issue Links

          Activity

          Hide
          githubbot ASF GitHub Bot added a comment -

          GitHub user pvillard31 opened a pull request:

          https://github.com/apache/nifi/pull/1596

          NIFI-3596 - added attributes to GenerateTableFetch processor

          Thank you for submitting a contribution to Apache NiFi.

          In order to streamline the review of the contribution we ask you
          to ensure the following steps have been taken:

              1. For all changes:
          • [X] Is there a JIRA ticket associated with this PR? Is it referenced
            in the commit message?
          • [X] Does your PR title start with NIFI-XXXX where XXXX is the JIRA number you are trying to resolve? Pay particular attention to the hyphen "-" character.
          • [X] Has your PR been rebased against the latest commit within the target branch (typically master)?
          • [X] Is your initial contribution a single, squashed commit?
              1. For code changes:
          • [X] Have you ensured that the full suite of tests is executed via mvn -Pcontrib-check clean install at the root nifi folder?
          • [X] Have you written or updated unit tests to verify your changes?
              1. For documentation related changes:
          • [X] Have you ensured that format looks appropriate for the output in which it is rendered?
              1. Note:
                Please ensure that once the PR is submitted, you check travis-ci for build issues and submit an update to your PR as soon as possible.

          You can merge this pull request into a Git repository by running:

          $ git pull https://github.com/pvillard31/nifi NIFI-3596

          Alternatively you can review and apply these changes as the patch at:

          https://github.com/apache/nifi/pull/1596.patch

          To close this pull request, make a commit to your master/trunk branch
          with (at least) the following in the commit message:

          This closes #1596


          commit 6367110cf620ac765ba5f556e6f11fbe0545e639
          Author: Pierre Villard <pierre.villard.fr@gmail.com>
          Date: 2017-03-14T22:19:05Z

          NIFI-3596 - added attributes to GenerateTableFetch processor


          Show
          githubbot ASF GitHub Bot added a comment - GitHub user pvillard31 opened a pull request: https://github.com/apache/nifi/pull/1596 NIFI-3596 - added attributes to GenerateTableFetch processor Thank you for submitting a contribution to Apache NiFi. In order to streamline the review of the contribution we ask you to ensure the following steps have been taken: For all changes: [X] Is there a JIRA ticket associated with this PR? Is it referenced in the commit message? [X] Does your PR title start with NIFI-XXXX where XXXX is the JIRA number you are trying to resolve? Pay particular attention to the hyphen "-" character. [X] Has your PR been rebased against the latest commit within the target branch (typically master)? [X] Is your initial contribution a single, squashed commit? For code changes: [X] Have you ensured that the full suite of tests is executed via mvn -Pcontrib-check clean install at the root nifi folder? [X] Have you written or updated unit tests to verify your changes? For documentation related changes: [X] Have you ensured that format looks appropriate for the output in which it is rendered? Note: Please ensure that once the PR is submitted, you check travis-ci for build issues and submit an update to your PR as soon as possible. You can merge this pull request into a Git repository by running: $ git pull https://github.com/pvillard31/nifi NIFI-3596 Alternatively you can review and apply these changes as the patch at: https://github.com/apache/nifi/pull/1596.patch To close this pull request, make a commit to your master/trunk branch with (at least) the following in the commit message: This closes #1596 commit 6367110cf620ac765ba5f556e6f11fbe0545e639 Author: Pierre Villard <pierre.villard.fr@gmail.com> Date: 2017-03-14T22:19:05Z NIFI-3596 - added attributes to GenerateTableFetch processor
          Hide
          githubbot ASF GitHub Bot added a comment -

          Github user mattyb149 commented on a diff in the pull request:

          https://github.com/apache/nifi/pull/1596#discussion_r107984019

          — Diff: nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/test/java/org/apache/nifi/processors/standard/TestGenerateTableFetch.java —
          @@ -207,6 +207,12 @@ public void testAddedRows() throws ClassNotFoundException, SQLException, Initial
          assertEquals("SELECT * FROM TEST_QUERY_DB_TABLE ORDER BY name OFFSET 4 ROWS FETCH NEXT 2 ROWS ONLY", new String(flowFile.toByteArray()));
          flowFile = runner.getFlowFilesForRelationship(REL_SUCCESS).get(3);
          assertEquals("SELECT * FROM TEST_QUERY_DB_TABLE ORDER BY name OFFSET 6 ROWS FETCH NEXT 2 ROWS ONLY", new String(flowFile.toByteArray()));
          + assertEquals("TEST_QUERY_DB_TABLE", flowFile.getAttribute("generatetablefetch.tableName"));
          + assertEquals(null, flowFile.getAttribute("generatetablefetch.columnNames"));
          — End diff –

          All added asserts for columns are checking for null, can you update a test (or add one) that sets the Columns To Return property? Otherwise, looks good to me, will build and run on a real NiFi in the meantime

          Show
          githubbot ASF GitHub Bot added a comment - Github user mattyb149 commented on a diff in the pull request: https://github.com/apache/nifi/pull/1596#discussion_r107984019 — Diff: nifi-nar-bundles/nifi-standard-bundle/nifi-standard-processors/src/test/java/org/apache/nifi/processors/standard/TestGenerateTableFetch.java — @@ -207,6 +207,12 @@ public void testAddedRows() throws ClassNotFoundException, SQLException, Initial assertEquals("SELECT * FROM TEST_QUERY_DB_TABLE ORDER BY name OFFSET 4 ROWS FETCH NEXT 2 ROWS ONLY", new String(flowFile.toByteArray())); flowFile = runner.getFlowFilesForRelationship(REL_SUCCESS).get(3); assertEquals("SELECT * FROM TEST_QUERY_DB_TABLE ORDER BY name OFFSET 6 ROWS FETCH NEXT 2 ROWS ONLY", new String(flowFile.toByteArray())); + assertEquals("TEST_QUERY_DB_TABLE", flowFile.getAttribute("generatetablefetch.tableName")); + assertEquals(null, flowFile.getAttribute("generatetablefetch.columnNames")); — End diff – All added asserts for columns are checking for null, can you update a test (or add one) that sets the Columns To Return property? Otherwise, looks good to me, will build and run on a real NiFi in the meantime
          Hide
          githubbot ASF GitHub Bot added a comment -

          Github user pvillard31 commented on the issue:

          https://github.com/apache/nifi/pull/1596

          Thanks for the review @mattyb149.
          I updated a test to check the selected column names.

          Show
          githubbot ASF GitHub Bot added a comment - Github user pvillard31 commented on the issue: https://github.com/apache/nifi/pull/1596 Thanks for the review @mattyb149. I updated a test to check the selected column names.
          Hide
          githubbot ASF GitHub Bot added a comment -

          Github user mattyb149 commented on the issue:

          https://github.com/apache/nifi/pull/1596

          +1 LGTM, built and ran unit tests, also ran with a real NiFi, verified all the attributes were added and correct. Thanks for the contribution! Merging to master

          Show
          githubbot ASF GitHub Bot added a comment - Github user mattyb149 commented on the issue: https://github.com/apache/nifi/pull/1596 +1 LGTM, built and ran unit tests, also ran with a real NiFi, verified all the attributes were added and correct. Thanks for the contribution! Merging to master
          Hide
          jira-bot ASF subversion and git services added a comment -

          Commit ced6708d4bca6d179e5f9fbc0b2324102879d21e in nifi's branch refs/heads/master from Pierre Villard
          [ https://git-wip-us.apache.org/repos/asf?p=nifi.git;h=ced6708 ]

          NIFI-3596 - added attributes to GenerateTableFetch processor

          Signed-off-by: Matt Burgess <mattyb149@apache.org>

          Updated test to check selected column names

          Signed-off-by: Matt Burgess <mattyb149@apache.org>

          This closes #1596

          Show
          jira-bot ASF subversion and git services added a comment - Commit ced6708d4bca6d179e5f9fbc0b2324102879d21e in nifi's branch refs/heads/master from Pierre Villard [ https://git-wip-us.apache.org/repos/asf?p=nifi.git;h=ced6708 ] NIFI-3596 - added attributes to GenerateTableFetch processor Signed-off-by: Matt Burgess <mattyb149@apache.org> Updated test to check selected column names Signed-off-by: Matt Burgess <mattyb149@apache.org> This closes #1596
          Hide
          githubbot ASF GitHub Bot added a comment -

          Github user asfgit closed the pull request at:

          https://github.com/apache/nifi/pull/1596

          Show
          githubbot ASF GitHub Bot added a comment - Github user asfgit closed the pull request at: https://github.com/apache/nifi/pull/1596

            People

            • Assignee:
              pvillard Pierre Villard
              Reporter:
              pvillard Pierre Villard
            • Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development