Exporting Data Using Sqoop

Sqoop can also be used to export data from Hive to relational tables. The table to contain the data must exist in the relational database, or the Sqoop will fail. You can use the Generate DDL wizard to create DDL statements to create the table (adjusting column lengths and types as required), and then execute the DDL statements from the Data Source Explorer. For more information, see Using the Generate DDL Wizard.

You can then create and execute the Sqoop export.

Run the DDL script to create the table on the database server. Check the Console to ensure that no errors have occurred and then look in the Data Source Explorer for the table. Select Refresh or press F5 before checking the Data Source Explorer.

After the table is confirmed in the target database, create a new Sqoop configuration as described in How to Create a Sqoop Configuration. You can also click the following icon on the toolbar:

The Sqoop opens as a new tab in the iBDI workspace.

The Target Data Source will be the structured relational data source (such as MySQL, Postgres, and so on). Use the table schema defined during the configuration of the data source connection. The Source Tables will be Hive tables.

Click Add (the green plus sign icon) to select the table(s) you want to add. To remove a table, select the table and then click Delete (the red X icon).

Once a table is added to the list, click a table name, and then click the following Edit icon to only copy the selected columns:

This option allows the possibility of creating tables based on sub-selections of main tables. The number of columns in the Output target schema and the columns in the target database must match. If you are performing a sub-select, ensure that the columns have the correct alignment.

Click the Save icon or use the Ctrl+S shortcut to save your work. Use the Run Configurations facility to deploy or publish the Sqoop configuration to the edge node. For more information, see Defining Run Configurations.