Configuring an External Database for Sqoop 2
Sqoop 2 has a built-in Derby database, but Cloudera recommends that you use a PostgreSQL database instead, for the following reasons:
- Derby runs in embedded mode and it is not possible to monitor its health.
- Though it might be possible, Cloudera currently has no live backup strategy for the embedded Derby database.
- Under load, Cloudera has observed locks and rollbacks with the embedded Derby database that do not happen with server-based databases.
Use the procedure that follows to configure Sqoop 2 to use PostgreSQL instead of Apache Derby.
Create the Sqoop 2 User and Sqoop 2 Database
$ psql -U postgres Password for user postgres: ***** postgres=# CREATE ROLE sqoop LOGIN ENCRYPTED PASSWORD 'sqoop' NOSUPERUSER INHERIT CREATEDB NOCREATEROLE; CREATE ROLE postgres=# CREATE DATABASE "sqoop" WITH OWNER = sqoop ENCODING = 'UTF8' TABLESPACE = pg_default LC_COLLATE = 'en_US.UTF8' LC_CTYPE = 'en_US.UTF8' CONNECTION LIMIT = -1; CREATE DATABASE postgres=# \q
Configure Sqoop 2 to use PostgreSQL
Minimum Required Role: Configurator (also provided by Cluster Administrator, Full Administrator)
- Go to the Sqoop 2 service.
- Click the Configuration tab.
- Select .
- Select .
- Set the following properties:
- Sqoop Repository Database Type - postgresql
- Sqoop Repository Database Host - the hostname on which you installed the PostgreSQL server. If the port is non-default for your database type, use host:port notation.
- Sqoop Repository Database Name, User, Password - the properties you specified in Create the Sqoop 2 User and Sqoop 2 Database.
- Enter a Reason for change, and then click Save Changes to commit the changes.
- Restart the service.