Commands

Sqoop Commands

To run the Sqoop commands, you need shell access on the Invariant edge servers.

Sqoop Help

To access help and see details of commands use the following command

[devuser@inv121 ~]$ Sqoop help
Output
usage: sqoop COMMAND [ARGS]

Available commands:

  codegen            Generate code to interact with database records
  create-hive-table  Import a table definition into Hive
  eval               Evaluate a SQL statement and display the results
  export             Export an HDFS directory to a database table
  help               List available commands
  import             Import a table from a database to HDFS
  import-all-tables  Import tables from a database to HDFS
  import-mainframe   Import datasets from a mainframe server to HDFS
  job                Work with saved jobs
  list-databases     List available databases on a server
  list-tables        List available tables in a database
  merge              Merge results of incremental imports
  metastore          Run a standalone Sqoop metastore
  version            Display version information

See 'sqoop help COMMAND' for information on a specific command.

Each command has specific arguments. The help command can display these arguments when you use the commands as a parameter. For example here is the syntax for displaying the arguments for the frequently used import command.

[devuser@inv121 ~]$ sqoop help list-databases

output

usage: sqoop list-databases [GENERIC-ARGS] [TOOL-ARGS]
Common arguments:
   --connect <jdbc-uri>              Specify JDBC
                                     Connect string
   --connection-manager <class-name> Specify connection manager class name
   --connection-param-file <properties-file>   Specify connection
                                               Parameters file Manually
                                               specify JDBC driver class
                                               to use
   --hadoop-home <hdir>             Override $HADOOP_MAPRED_HOME_ARG
   --hadoop-mapred-home <dir>       Override $HADOOP_MAPRED_HOME_ARG
   --help                           Print usage instructions
   --metadata-transaction-isolation-level <isolationlevel>    Defines the
                                                              transaction
                                                              isolation
                                                              level for
                                                              metadata
                                                              queries. For
                                                              more details
                                                              check
                                                              java.sql.Con
                                                              nection
                                                              javadoc or JDBC
                                                              specification
-P                                                            Read
                                                              password
                                                              from console
   --password <password>                                      Set
                                                              authenticati
                                                              on password
   --password-alias <password-alias>                          Credential
                                                              provider
                                                              password
                                                              alias
   --password-file <password-file>                            Set
                                                              authenticati
                                                              on password
                                                              file path
   --relaxed-isolation                                        Use
                                                              read-uncommi
                                                              tted
                                                              isolation
                                                              for imports
   --skip-dist-cache                                          Skip copying
                                                              jars to
                                                              distributed
                                                              cache
   --temporary-rootdir <rootdir>                              Defines the
                                                              temporary
                                                              root
                                                              directory
                                                              for the
                                                              import
   --username <username>                                      Set
                                                              authenticati
                                                              on username
   --verbose                                                  Print more
                                                              information
                                                              while
                                                              working
                                                              
Generic Hadoop command-line arguments:
(must preceed any tool-specific arguments)
Generic options supported are
-conf <configuration file>     specify an application configuration file
-D <property=value>            use value for given property
-fs <local|namenode:port>      specify a namenode
-jt <local|resourcemanager:port>    specify a ResourceManager
-files <comma separated list of files>    specify comma separated files to be copied to the map reduce cluster
-libjars <comma separated list of jars>    specify comma separated jar files to include in the classpath.
-archives <comma separated list of archives>    specify comma separated archives to be unarchived on the compute machines.

The general command line syntax is
bin/hadoop command [genericOptions] [commandOptions]

List databases

This command lists the databases that your account is allowed to access.

sqoop list-databases \
    -Dhadoop.security.credential.provider.path=jceks://hdfs/user/devuser/q1.password.jceks \ 
    --connect jdbc:db2://localhost:52330/TESTP30 \
    --username devuser --password-alias qrep.password.alias

Output
    ASN
    ASNP2R
    DSADM
    BPM
    SQLJ
    SYSCAT
    SYSFUN
    SYSIBM
    SYSIBMADM
    SYSIBMINTERNAL
    SYSIBMTS
    SYSPROC
    SYSPUBLIC
    SYSSTAT
    SYSTOOLS

List Tables

This command lists the tables available.

sqoop list-tables \
     -Dhadoop.security.credential.provider.path=jceks://hdfs/user/devuser/qr.password.jceks \
     --connect jdbc:db2://localhost:52330/DBBPM30 \
     --username devuser --password-alias qr.password.alias
     
INDEX_ASSIGNMENTSKILLS
INDEX_CORRESPONDENCE_TYPE
INDEX_ENTITIES
WORK_CATEGORY
OPERATORS
OPERATOR_SKILLS     

Again the syntax is straightforward requiring just a connect argument. But in this case you need to specify the database in the connection string as well.