September 15, 2021

Sqoop Commands

Sqoop command line (CLI)

sqoop help
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

sqoop help import
sqoop import --help


sqoop version

sqoop-version

sqoop import --connect jdbc:mysql://database.example.com/employees --username satya --password sat345
$ sqoop import --connect jdbc:mysql://localhost:3306/hadoop --driver com.mysql.jdbc.Driver --username root --password r00t --table bse
$ sqoop import --connect jdbc:mysql://localhost:3306/hadoop --driver com.mysql.jdbc.Driver --username root --P --table bsmcg --hbase-row-key date --hbase-create-table --column-family firstdecade --hbase-table bsmcg
sqoop import --query 'SELECT a.*, b.* FROM a JOIN b on (a.id == b.id) WHERE $CONDITIONS' --split-by a.id --target-dir /user/foo/joinresults
sqoop import --connect <connect-str> --table SomeTable --package-name com.foocorp
sqoop import --connect jdbc:mysql://db.foo.com/corp --table EMPLOYEES --username SomeUser -P
sqoop import --connect jdbc:mysql://db.foo.com/corp --table EMPLOYEES --fields-terminated-by '\t' --lines-terminated-by '\n' --optionally-enclosed-by '\"'
sqoop import --connect jdbc:mysql://db.foo.com/corp --table EMPLOYEES --hive-import

sqoop import-all-tables --connect jdbc:mysql://db.foo.com/corp

$ sqoop export --connect jdbc:mysql://localhost:3306/hadoop --driver com.mysql.jdbc.Driver --username root --password root --table bsmcg --export-dir /user/satya/bsmcg
$ sqoop export --connect jdbc:mysql://localhost:3306/hadoop --driver com.mysql.jdbc.Driver --username root --password root --table bse --export-dir /user/hive/warehouse/bse
sqoop export --connect jdbc:mysql://db.example.com/foo --table bar --export-dir /results/bar_data

$ sqoop create-hive-table --connect jdbc:mysql://localhost:3306/hadoop --driver com.mysql.jdbc.Driver --username root --P --table bse
sqoop create-hive-table --connect jdbc:mysql://db.example.com/corp --table employees --hive-table emp

sqoop list-databases --connect jdbc:mysql://database.example.com/
sqoop list-databases --connect jdbc:mysql://sqoop......rds.amazonaws.com:3306 --username admin -P

sqoop list-tables --connect jdbc:postgresql://database.example.com/corp
sqoop list-tables --connect jdbc:mysql://sqoop......rds.amazonaws.com:3306/ssDB --username admin -P

sqoop job --list
sqoop job --show myjob
sqoop job --create myjob -- import --connect jdbc:mysql://example.com/db --table mytable
sqoop job --exec myjob -- --username someuser -P

sqoop merge --new-data newer --onto older --target-dir merged  --jar-file datatypes.jar --class-name Foo --merge-key id

sqoop codegen --connect jdbc:mysql://db.example.com/corp --table employees

sqoop eval --connect jdbc:mysql://db.example.com/corp --query "SELECT * FROM employees LIMIT 10"
sqoop eval --connect jdbc:mysql://db.example.com/corp -e "INSERT INTO foo VALUES(42, 'bar')"

Related Articles:  Spark Interview Questions


4 comments:

  1. Hello,
    1. SQOOP is a tool designed to transfer data between Hadoop and relational databases.
    2. Use sqoop to import data from a relational database management system such as Mysql or oracle into the HADOOP DISTRIBUTED FILE SYSTEM (HDFS).
    3. we can use sqoop to transform the data in Hadoop Map Reduce and the export the data back to on RDBMS
    4. Sqoop automate most of this process, relying on the database to describe the schema for the data to be imported.
    5. Sqoop uses map reduce to import and export the data which provides parallel operation as well as fault conditions.

    ReplyDelete
  2. thank you for sharing such a good and useful information, please keep on share like this
    hadoop training in hyderabad
    hadoop online training
    hadoop training in ameerpet

    ReplyDelete
  3. I found your blog very interesting and very informative.I like your way of writing and explaining the topics.Apache Hadoop is an 100% open source framework for distributed storage and processing of large sets of data Readmore..

    ReplyDelete