Sqoop
  1. Sqoop
  2. SQOOP-749

Exports Using Stored Procedures (Functions)

    Details

      Description

      It'd be useful if you could use stored procedures (or functions) to insert data - currently you can only use insert or update statements (or upsert / merges, depending on the SqlManager you're using). This would help sqoop adoption / migration into environments which have existing, SQL-based data import workflows. Thanks!

      1. SQOOP-749.6.patch
        49 kB
        Nick White
      2. SQOOP-749.3.patch
        56 kB
        Nick White
      3. SQOOP-749.2.patch
        47 kB
        Nick White
      4. SQOOP-749.1.patch
        35 kB
        Nick White
      5. SQOOP-749.0.patch
        33 kB
        Nick White

        Issue Links

          Activity

          Hide
          Nick White added a comment -

          I've attached a patch that adds a --call argument to the export tool, which can be used in place of a --table argument. I'm using a subclass of TestExport to test it, but my tests require HSQLDB version 2.x (as 1.8 doesn't have support for stored procedures), but I gather from SQOOP-571 that SQOOP can't upgrade to this version yet.

          Show
          Nick White added a comment - I've attached a patch that adds a --call argument to the export tool, which can be used in place of a --table argument. I'm using a subclass of TestExport to test it, but my tests require HSQLDB version 2.x (as 1.8 doesn't have support for stored procedures), but I gather from SQOOP-571 that SQOOP can't upgrade to this version yet.
          Hide
          Jarek Jarcec Cecho added a comment -

          Hi Nick,
          thank you very much for taking up this ticket. Would you mind uploading your patch to Apache Review board (https://reviews.apache.org)?

          Jarcec

          Show
          Jarek Jarcec Cecho added a comment - Hi Nick, thank you very much for taking up this ticket. Would you mind uploading your patch to Apache Review board ( https://reviews.apache.org)? Jarcec
          Hide
          Nick White added a comment -

          Thanks - I've revised the patch slightly and have attached it here and uploaded it to https://reviews.apache.org/r/8806/ . Thanks!

          Show
          Nick White added a comment - Thanks - I've revised the patch slightly and have attached it here and uploaded it to https://reviews.apache.org/r/8806/ . Thanks!
          Hide
          Venkat Ranganathan added a comment -

          I was looking at the patch. It is a good feature to add. Is there a requirement as to how the columns are handled if we have out columns or inout columns? Are there specific requirements on the type of procedures we support (all params are in only)?

          Show
          Venkat Ranganathan added a comment - I was looking at the patch. It is a good feature to add. Is there a requirement as to how the columns are handled if we have out columns or inout columns? Are there specific requirements on the type of procedures we support (all params are in only)?
          Hide
          Nick White added a comment -

          Hi Venkat - with this patch sqoop will attempt to find one stored procedure parameter for each column in the input data (matching by column name == parameter name). It only ignores return parameters - i.e. it pass values as in, in/out and out parameters (although it ignores the out values).

          Show
          Nick White added a comment - Hi Venkat - with this patch sqoop will attempt to find one stored procedure parameter for each column in the input data (matching by column name == parameter name). It only ignores return parameters - i.e. it pass values as in, in/out and out parameters (although it ignores the out values).
          Hide
          Venkat Ranganathan added a comment -

          Thanks Nick. Don't we have to register the out only parameter types of out params? Do you think it will be better to
          1) Throw an error if we encounter out params during export to DB while getting procedure columns
          and check if DB supports procedures and throw an error if DBmetadata for procedure support is missing?

          BTW, you have added a patch to this JIRA meant for another JIRA - (SQOOP-808)

          Thanks

          Show
          Venkat Ranganathan added a comment - Thanks Nick. Don't we have to register the out only parameter types of out params? Do you think it will be better to 1) Throw an error if we encounter out params during export to DB while getting procedure columns and check if DB supports procedures and throw an error if DBmetadata for procedure support is missing? BTW, you have added a patch to this JIRA meant for another JIRA - ( SQOOP-808 ) Thanks
          Hide
          Jarek Jarcec Cecho added a comment -
          Show
          Jarek Jarcec Cecho added a comment - The patch is in: https://git-wip-us.apache.org/repos/asf?p=sqoop.git;a=commit;h=20af67ef60096b17e1d9585670e5ec787eb760e2 Thank you Nick for your hard work on this! Jarcec
          Hide
          Hudson added a comment -

          Integrated in Sqoop-ant-jdk-1.6-hadoop200 #522 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop200/522/)
          SQOOP-749: Exports Using Stored Procedures (Functions) (Revision 20af67ef60096b17e1d9585670e5ec787eb760e2)

          Result = SUCCESS
          jarcec : https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=20af67ef60096b17e1d9585670e5ec787eb760e2
          Files :

          • src/test/org/apache/sqoop/TestExportUsingProcedure.java
          • src/test/com/cloudera/sqoop/testutil/ExportJobTestCase.java
          • src/java/org/apache/sqoop/mapreduce/JdbcCallExportJob.java
          • src/java/org/apache/sqoop/mapreduce/ExportCallOutputFormat.java
          • ivy.xml
          • src/java/org/apache/sqoop/tool/ExportTool.java
          • src/java/org/apache/sqoop/manager/ConnManager.java
          • src/java/org/apache/sqoop/manager/SqlManager.java
          • src/docs/user/export.txt
          • src/java/org/apache/sqoop/mapreduce/JdbcExportJob.java
          • ivy/libraries.properties
          • src/java/org/apache/sqoop/orm/ClassWriter.java
          • src/java/org/apache/sqoop/SqoopOptions.java
          • src/test/com/cloudera/sqoop/TestConnFactory.java
          • src/docs/user/export-purpose.txt
          • src/test/com/cloudera/sqoop/manager/PostgresqlExportTest.java
          • src/test/com/cloudera/sqoop/TestExport.java
          • src/java/org/apache/sqoop/tool/BaseSqoopTool.java
          • src/test/com/cloudera/sqoop/SmokeTests.java
          Show
          Hudson added a comment - Integrated in Sqoop-ant-jdk-1.6-hadoop200 #522 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop200/522/ ) SQOOP-749 : Exports Using Stored Procedures (Functions) (Revision 20af67ef60096b17e1d9585670e5ec787eb760e2) Result = SUCCESS jarcec : https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=20af67ef60096b17e1d9585670e5ec787eb760e2 Files : src/test/org/apache/sqoop/TestExportUsingProcedure.java src/test/com/cloudera/sqoop/testutil/ExportJobTestCase.java src/java/org/apache/sqoop/mapreduce/JdbcCallExportJob.java src/java/org/apache/sqoop/mapreduce/ExportCallOutputFormat.java ivy.xml src/java/org/apache/sqoop/tool/ExportTool.java src/java/org/apache/sqoop/manager/ConnManager.java src/java/org/apache/sqoop/manager/SqlManager.java src/docs/user/export.txt src/java/org/apache/sqoop/mapreduce/JdbcExportJob.java ivy/libraries.properties src/java/org/apache/sqoop/orm/ClassWriter.java src/java/org/apache/sqoop/SqoopOptions.java src/test/com/cloudera/sqoop/TestConnFactory.java src/docs/user/export-purpose.txt src/test/com/cloudera/sqoop/manager/PostgresqlExportTest.java src/test/com/cloudera/sqoop/TestExport.java src/java/org/apache/sqoop/tool/BaseSqoopTool.java src/test/com/cloudera/sqoop/SmokeTests.java
          Hide
          Hudson added a comment -

          Integrated in Sqoop-ant-jdk-1.6-hadoop20 #520 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop20/520/)
          SQOOP-749: Exports Using Stored Procedures (Functions) (Revision 20af67ef60096b17e1d9585670e5ec787eb760e2)

          Result = SUCCESS
          jarcec : https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=20af67ef60096b17e1d9585670e5ec787eb760e2
          Files :

          • src/java/org/apache/sqoop/mapreduce/ExportCallOutputFormat.java
          • src/java/org/apache/sqoop/mapreduce/JdbcCallExportJob.java
          • src/test/com/cloudera/sqoop/manager/PostgresqlExportTest.java
          • ivy.xml
          • src/java/org/apache/sqoop/manager/ConnManager.java
          • src/java/org/apache/sqoop/tool/BaseSqoopTool.java
          • src/docs/user/export.txt
          • src/test/org/apache/sqoop/TestExportUsingProcedure.java
          • src/java/org/apache/sqoop/mapreduce/JdbcExportJob.java
          • ivy/libraries.properties
          • src/java/org/apache/sqoop/SqoopOptions.java
          • src/docs/user/export-purpose.txt
          • src/java/org/apache/sqoop/manager/SqlManager.java
          • src/test/com/cloudera/sqoop/TestConnFactory.java
          • src/test/com/cloudera/sqoop/SmokeTests.java
          • src/java/org/apache/sqoop/orm/ClassWriter.java
          • src/test/com/cloudera/sqoop/testutil/ExportJobTestCase.java
          • src/java/org/apache/sqoop/tool/ExportTool.java
          • src/test/com/cloudera/sqoop/TestExport.java
          Show
          Hudson added a comment - Integrated in Sqoop-ant-jdk-1.6-hadoop20 #520 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop20/520/ ) SQOOP-749 : Exports Using Stored Procedures (Functions) (Revision 20af67ef60096b17e1d9585670e5ec787eb760e2) Result = SUCCESS jarcec : https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=20af67ef60096b17e1d9585670e5ec787eb760e2 Files : src/java/org/apache/sqoop/mapreduce/ExportCallOutputFormat.java src/java/org/apache/sqoop/mapreduce/JdbcCallExportJob.java src/test/com/cloudera/sqoop/manager/PostgresqlExportTest.java ivy.xml src/java/org/apache/sqoop/manager/ConnManager.java src/java/org/apache/sqoop/tool/BaseSqoopTool.java src/docs/user/export.txt src/test/org/apache/sqoop/TestExportUsingProcedure.java src/java/org/apache/sqoop/mapreduce/JdbcExportJob.java ivy/libraries.properties src/java/org/apache/sqoop/SqoopOptions.java src/docs/user/export-purpose.txt src/java/org/apache/sqoop/manager/SqlManager.java src/test/com/cloudera/sqoop/TestConnFactory.java src/test/com/cloudera/sqoop/SmokeTests.java src/java/org/apache/sqoop/orm/ClassWriter.java src/test/com/cloudera/sqoop/testutil/ExportJobTestCase.java src/java/org/apache/sqoop/tool/ExportTool.java src/test/com/cloudera/sqoop/TestExport.java
          Hide
          Hudson added a comment -

          Integrated in Sqoop-ant-jdk-1.6-hadoop23 #715 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop23/715/)
          SQOOP-749: Exports Using Stored Procedures (Functions) (Revision 20af67ef60096b17e1d9585670e5ec787eb760e2)

          Result = ABORTED
          jarcec : https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=20af67ef60096b17e1d9585670e5ec787eb760e2
          Files :

          • ivy.xml
          • src/java/org/apache/sqoop/tool/BaseSqoopTool.java
          • ivy/libraries.properties
          • src/java/org/apache/sqoop/manager/SqlManager.java
          • src/test/com/cloudera/sqoop/testutil/ExportJobTestCase.java
          • src/java/org/apache/sqoop/manager/ConnManager.java
          • src/java/org/apache/sqoop/mapreduce/JdbcExportJob.java
          • src/java/org/apache/sqoop/SqoopOptions.java
          • src/test/org/apache/sqoop/TestExportUsingProcedure.java
          • src/test/com/cloudera/sqoop/TestExport.java
          • src/test/com/cloudera/sqoop/TestConnFactory.java
          • src/java/org/apache/sqoop/mapreduce/ExportCallOutputFormat.java
          • src/test/com/cloudera/sqoop/manager/PostgresqlExportTest.java
          • src/java/org/apache/sqoop/mapreduce/JdbcCallExportJob.java
          • src/java/org/apache/sqoop/tool/ExportTool.java
          • src/test/com/cloudera/sqoop/SmokeTests.java
          • src/docs/user/export-purpose.txt
          • src/java/org/apache/sqoop/orm/ClassWriter.java
          • src/docs/user/export.txt
          Show
          Hudson added a comment - Integrated in Sqoop-ant-jdk-1.6-hadoop23 #715 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop23/715/ ) SQOOP-749 : Exports Using Stored Procedures (Functions) (Revision 20af67ef60096b17e1d9585670e5ec787eb760e2) Result = ABORTED jarcec : https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=20af67ef60096b17e1d9585670e5ec787eb760e2 Files : ivy.xml src/java/org/apache/sqoop/tool/BaseSqoopTool.java ivy/libraries.properties src/java/org/apache/sqoop/manager/SqlManager.java src/test/com/cloudera/sqoop/testutil/ExportJobTestCase.java src/java/org/apache/sqoop/manager/ConnManager.java src/java/org/apache/sqoop/mapreduce/JdbcExportJob.java src/java/org/apache/sqoop/SqoopOptions.java src/test/org/apache/sqoop/TestExportUsingProcedure.java src/test/com/cloudera/sqoop/TestExport.java src/test/com/cloudera/sqoop/TestConnFactory.java src/java/org/apache/sqoop/mapreduce/ExportCallOutputFormat.java src/test/com/cloudera/sqoop/manager/PostgresqlExportTest.java src/java/org/apache/sqoop/mapreduce/JdbcCallExportJob.java src/java/org/apache/sqoop/tool/ExportTool.java src/test/com/cloudera/sqoop/SmokeTests.java src/docs/user/export-purpose.txt src/java/org/apache/sqoop/orm/ClassWriter.java src/docs/user/export.txt
          Hide
          Hudson added a comment -

          Integrated in Sqoop-ant-jdk-1.6-hadoop100 #513 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop100/513/)
          SQOOP-749: Exports Using Stored Procedures (Functions) (Revision 20af67ef60096b17e1d9585670e5ec787eb760e2)

          Result = ABORTED
          jarcec : https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=20af67ef60096b17e1d9585670e5ec787eb760e2
          Files :

          • src/java/org/apache/sqoop/mapreduce/JdbcCallExportJob.java
          • src/test/com/cloudera/sqoop/TestExport.java
          • src/test/com/cloudera/sqoop/SmokeTests.java
          • src/java/org/apache/sqoop/manager/ConnManager.java
          • src/java/org/apache/sqoop/mapreduce/JdbcExportJob.java
          • src/java/org/apache/sqoop/mapreduce/ExportCallOutputFormat.java
          • src/java/org/apache/sqoop/manager/SqlManager.java
          • src/test/org/apache/sqoop/TestExportUsingProcedure.java
          • src/java/org/apache/sqoop/tool/BaseSqoopTool.java
          • ivy/libraries.properties
          • src/java/org/apache/sqoop/orm/ClassWriter.java
          • src/test/com/cloudera/sqoop/testutil/ExportJobTestCase.java
          • ivy.xml
          • src/test/com/cloudera/sqoop/manager/PostgresqlExportTest.java
          • src/test/com/cloudera/sqoop/TestConnFactory.java
          • src/docs/user/export.txt
          • src/java/org/apache/sqoop/SqoopOptions.java
          • src/docs/user/export-purpose.txt
          • src/java/org/apache/sqoop/tool/ExportTool.java
          Show
          Hudson added a comment - Integrated in Sqoop-ant-jdk-1.6-hadoop100 #513 (See https://builds.apache.org/job/Sqoop-ant-jdk-1.6-hadoop100/513/ ) SQOOP-749 : Exports Using Stored Procedures (Functions) (Revision 20af67ef60096b17e1d9585670e5ec787eb760e2) Result = ABORTED jarcec : https://git-wip-us.apache.org/repos/asf?p=sqoop.git&a=commit&h=20af67ef60096b17e1d9585670e5ec787eb760e2 Files : src/java/org/apache/sqoop/mapreduce/JdbcCallExportJob.java src/test/com/cloudera/sqoop/TestExport.java src/test/com/cloudera/sqoop/SmokeTests.java src/java/org/apache/sqoop/manager/ConnManager.java src/java/org/apache/sqoop/mapreduce/JdbcExportJob.java src/java/org/apache/sqoop/mapreduce/ExportCallOutputFormat.java src/java/org/apache/sqoop/manager/SqlManager.java src/test/org/apache/sqoop/TestExportUsingProcedure.java src/java/org/apache/sqoop/tool/BaseSqoopTool.java ivy/libraries.properties src/java/org/apache/sqoop/orm/ClassWriter.java src/test/com/cloudera/sqoop/testutil/ExportJobTestCase.java ivy.xml src/test/com/cloudera/sqoop/manager/PostgresqlExportTest.java src/test/com/cloudera/sqoop/TestConnFactory.java src/docs/user/export.txt src/java/org/apache/sqoop/SqoopOptions.java src/docs/user/export-purpose.txt src/java/org/apache/sqoop/tool/ExportTool.java

            People

            • Assignee:
              Nick White
              Reporter:
              Nick White
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development