Apache Sqoop Cookbook

Chapter 4. Free-Form Query Import

The previous chapters covered the use cases where you had an input table on the source database system and you needed to transfer the table as a whole or one part at a time into the Hadoop ecosystem. This chapter, on the other hand, will focus on more advanced use cases where you need to import data from more than one table or where you need to customize the transferred data by calling various database functions.

For this chapter we’ve slightly altered the test table cities (see Table 4-1), normalizing the country name to a standalone table called countries (see Table 4-2). The normalized variant of the table cities is called normcities and will be created and populated automatically via the script mysql.tables.sql as described in Chapter 2.

Table 4-1. Normalized cities

id	country_id	city
1	1	Palo Alto
2	2	Brno
3	1	Sunnyvale

Table 4-2. Countries

country_id	country
1	USA
2	Czech Republic

Importing Data from Two Tables

Problem

You need to import one main table; however, this table is normalized. The important values are stored in the referenced dictionary tables, and the main table contains only numeric foreign keys pointing to the values in the dictionaries rather than to natural keys as in the original cities table. You would prefer to resolve the values prior to running Sqoop and import the real values rather than the numerical keys for the countries.

Solution

Instead of using table import, use free-form query import. In this mode, Sqoop will allow you to specify any query ...