Apache Sqoop Cookbook

Preface

Whether moving a small collection of personal vacation photos between applications or moving petabytes of data between corporate warehouse systems, integrating data from multiple sources remains a struggle. Data storage is more accessible thanks to the availability of a number of widely used storage systems and accompanying tools. Core to that are relational databases (e.g., Oracle, MySQL, SQL Server, Teradata, and Netezza) that have been used for decades to serve and store huge amounts of data across all industries.

Relational database systems often store valuable data in a company. If made available, that data can be managed and processed by Apache Hadoop, which is fast becoming the standard for big data processing. Several relational database vendors championed developing integration with Hadoop within one or more of their products.

Transferring data to and from relational databases is challenging and laborious. Because data transfer requires careful handling, Apache Sqoop, short for “SQL to Hadoop,” was created to perform bidirectional data transfer between Hadoop and almost any external structured datastore. Taking advantage of MapReduce, Hadoop’s execution engine, Sqoop performs the transfers in a parallel manner.

If you’re reading this book, you may have some prior exposure to Sqoop—especially from Aaron Kimball’s Sqoop section in Hadoop: The Definitive Guide by Tom White (O’Reilly) or from Hadoop Operations by Eric Sammer (O’Reilly).

From that exposure, you’ve seen how Sqoop optimizes data transfers between Hadoop and databases. Clearly it’s a tool optimized for power users. A command-line interface providing 60 parameters is both powerful and bewildering. In this book, we’ll focus on applying the parameters in common use cases to help you deploy and use Sqoop in your environment.

Chapter 1 guides you through the basic prerequisites of using Sqoop. You will learn how to download, install, and configure the Sqoop tool on any node of your Hadoop cluster.

Chapters 2, 3, and 4 are devoted to the various use cases of getting your data from a database server into the Hadoop ecosystem. If you need to transfer generated, processed, or backed up data from Hadoop to your database, you’ll want to read Chapter 5.

In Chapter 6, we focus on integrating Sqoop with the rest of the Hadoop ecosystem. We will show you how to run Sqoop from within a specialized Hadoop scheduler called Apache Oozie and how to load your data into Hadoop’s data warehouse system Apache Hive and Hadoop’s database Apache HBase.

For even greater performance, Sqoop supports database-specific connectors that use native features of the particular DBMS. Sqoop includes native connectors for MySQL and PostgreSQL. Available for download are connectors for Teradata, Netezza, Couchbase, and Oracle (from Dell). Chapter 7 walks you through using them.

Sqoop 2

The motivation behind Sqoop 2 was to make Sqoop easier to use by having a web application run Sqoop. This allows you to install Sqoop and use it from anywhere. In addition, having a REST API for operation and management enables Sqoop to integrate better with external systems such as Apache Oozie. As further discussion of Sqoop 2 is beyond the scope of this book, we encourage you to download the bits and docs from the Apache Sqoop website and then try it out!

Conventions Used in This Book

The following typographical conventions are used in this book:

Italic

Indicates new terms, URLs, email addresses, filenames, and file extensions.

Constant width

Used for program listings, as well as within paragraphs to refer to program elements such as variable or function names, databases, data types, environment variables, statements, and keywords.

Tip

This icon signifies a tip, suggestion, or general note.

Warning

This icon indicates a warning or caution.

Using Code Examples

This book is here to help you get your job done. In general, if this book includes code examples, you may use the code in this book in your programs and documentation. You do not need to contact us for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.

Supplemental material (code examples, exercises, etc.) is available for download at https://github.com/jarcec/Apache-Sqoop-Cookbook.

We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: “Apache Sqoop Cookbook by Kathleen Ting and Jarek Jarcec Cecho (O’Reilly). Copyright 2013 Kathleen Ting and Jarek Jarcec Cecho, 978-1-449-36462-5.”

If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at [e-mail].

Safari® Books Online

Note

Safari Books Online is an on-demand digital library that delivers expert content in both book and video form from the world’s leading authors in technology and business.

Technology professionals, software developers, web designers, and business and creative professionals use Safari Books Online as their primary resource for research, problem solving, learning, and certification training.

Safari Books Online offers a range of product mixes and pricing programs for organizations, government agencies, and individuals. Subscribers have access to thousands of books, training videos, and prepublication manuscripts in one fully searchable database from publishers like O’Reilly Media, Prentice Hall Professional, Addison-Wesley Professional, Microsoft Press, Sams, Que, Peachpit Press, Focal Press, Cisco Press, John Wiley & Sons, Syngress, Morgan Kaufmann, IBM Redbooks, Packt, Adobe Press, FT Press, Apress, Manning, New Riders, McGraw-Hill, Jones & Bartlett, Course Technology, and dozens more. For more information about Safari Books Online, please visit us online.

How to Contact Us

Please address comments and questions concerning this book to the publisher:

O’Reilly Media, Inc.

1005 Gravenstein Highway North

Sebastopol, CA 95472

800-998-9938 (in the United States or Canada)

707-829-0515 (international or local)

707-829-0104 (fax)

We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at http://oreil.ly/Apache_Sqoop.

To comment or ask technical questions about this book, send email to [e-mail].

For more information about our books, courses, conferences, and news, see our website at http://www.oreilly.com.

Find us on Facebook: http://facebook.com/oreilly

Follow us on Twitter: http://twitter.com/oreillymedia

Watch us on YouTube: http://www.youtube.com/oreillymedia

Acknowledgments

Without the contributions and support from the Apache Sqoop community, this book would not exist. Without that support, there would be no Sqoop, nor would Sqoop be successfully deployed in production at companies worldwide. The unwavering support doled out by the committers, contributors, and the community at large on the mailing lists speaks to the power of open source.

Thank you to the Sqoop committers (as of this writing): Andrew Bayer, Abhijeet Gaikwad, Ahmed Radwan, Arvind Prabhakar, Bilung Lee, Cheolsoo Park, Greg Cottman, Guy le Mar, Jonathan Hsieh, Aaron Kimball, Olivier Lamy, Alex Newman, Paul Zimdars, and Roman Shaposhnik.

Thank you, Eric Sammer and O’Reilly, for giving us the opportunity to write this book.

Mike Olson, Amr Awadallah, Peter Cooper-Ellis, Arvind Prabhakar, and the rest of the Cloudera management team made sure we had the breathing room and the caffeine intake to get this done.

Many people provided valuable feedback and input throughout the entire process, but especially Rob Weltman, Arvind Prabhakar, Eric Sammer, Mark Grover, Abraham Elmahrek, Tom Wheeler, and Aaron Kimball. Special thanks to the creator of Sqoop, Aaron Kimball, for penning the foreword. To those whom we may have omitted from this list, our deepest apologies.

Thanks to our O’Reilly editor, Courtney Nash, for her professional advice and assistance in polishing the Sqoop Cookbook.

We would like to thank all the contributors to Sqoop. Every patch you contributed improved Sqoop’s ease of use, ease of extension, and security. Please keep contributing!

Jarcec Thanks

I would like to thank my parents, Lenka Cehova and Petr Cecho, for raising my sister, Petra Cechova, and me. Together we’ve created a nice and open environment that encouraged me to explore the newly created world of computers. I would also like to thank my girlfriend, Aneta Ziakova, for not being mad at me for spending excessive amounts of time working on cool stuff for Apache Software Foundation. Special thanks to Arvind Prabhakar for adroitly maneuvering between serious guidance and comic relief.

Kathleen Thanks

This book is gratefully dedicated to my parents, Betty and Arthur Ting, who had a great deal of trouble with me, but I think they enjoyed it.

My brother, Oliver Ting, taught me to tell the truth, so I don’t have to remember anything. I’ve never stopped looking up to him.

When I needed to hunker down, Wen, William, Bryan, and Derek Young provided me with a home away from home.

Special thanks to Omer Trajman for giving me an opportunity at Cloudera.

I am in debt to Arvind Prabhakar for taking a chance on mentoring me in the Apache way.