{"id":6946,"date":"2018-02-01T09:26:30","date_gmt":"2018-02-01T09:26:30","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=6946"},"modified":"2021-12-07T13:46:51","modified_gmt":"2021-12-07T08:16:51","slug":"sqoop-import","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sqoop-import\/","title":{"rendered":"Sqoop Import- Importing Data From RDBMS to HDFS"},"content":{"rendered":"

In the last article, we discussed\u00a0Sqoop Export<\/strong><\/a>. In this article we will discuss Sqoop import,\u00a0<\/strong>a\u00a0tool which we use for importing tables from RDBMS to HDFS<\/strong><\/a> is the Sqoop Import tool. Here, we will learn how to Sqoop import multiple tables from RDBMS database to Hadoop <\/a><\/strong>HDFS.<\/p>\n

Moreover, we will learn the purpose of importing in Sqoop, Sqoop import syntax as well as Sqoop import query examples to understand it well.<\/p>\n

So, let’s start the Sqoop Import tutorial.<\/p>\n

Sqoop Import and its Purpose<\/h3>\n

A tool, which we use for importing tables from RDBMS to HDFS is the Sqoop Import tool. Basically, here each row in a table is considered as a record in HDFS. Moreover, when we talk about text files all records are stored as text data.<\/p>\n

Whereas when we talk about Avro and sequence files all records are stored as binary data here. Basically, we can say the Sqoop Import all tables as individual tables from RDBMS to HDFS.<\/p>\n

Let’s discuss HDFS Features<\/strong><\/a><\/p>\n

Sqoop Import Syntax<\/h3>\n

To import data into HDFS we use the following syntax for importing in Sqoop. Such as:
\n$ sqoop import (generic-args) (import-args)<\/b>
\n$ sqoop-import (generic-args) (import-args)<\/b>
\nThe very advantage is we can type the sqoop import arguments in any order with respect to one another. However, when it comes to the Hadoop<\/strong><\/a> generic arguments, those must precede any import arguments only.
\nBasically, here all the arguments are grouped into collections which are organized by function. However, some collections are present in several tools here. For example, the “common” arguments.
\nTable 1. Sqoop Import – Common arguments<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Argument<\/b><\/td>\nDescription<\/b><\/td>\n<\/tr>\n
–connect <jdbc-uri><\/span><\/td>\nSpecify JDBC connect string<\/span><\/td>\n<\/tr>\n
–connection-manager <class-name><\/span><\/td>\nSpecify connection manager class to use<\/span><\/td>\n<\/tr>\n
–driver <class-name><\/span><\/td>\nManually specify JDBC driver class to use<\/span><\/td>\n<\/tr>\n
–hadoop-mapped-home <dir><\/span><\/td>\nOverride $HADOOP_MAPRED_HOME<\/span><\/td>\n<\/tr>\n
–help<\/span><\/td>\nPrint usage instructions<\/span><\/td>\n<\/tr>\n
–password-file<\/span><\/td>\nSet path for a file containing the authentication password<\/span><\/td>\n<\/tr>\n
-P<\/span><\/td>\nRead password from console<\/span><\/td>\n<\/tr>\n
–password <password><\/span><\/td>\nSet authentication password<\/span><\/td>\n<\/tr>\n
–username <username><\/span><\/td>\nSet authentication username<\/span><\/td>\n<\/tr>\n
–verbose<\/span><\/td>\nPrint more information while working<\/span><\/td>\n<\/tr>\n
–connection-param-file <filename><\/span><\/td>\nOptional properties file that provides connection parameters<\/span><\/td>\n<\/tr>\n
–relaxed-isolation<\/span><\/td>\nSet connection transaction isolation to read uncommitted for the mappers.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

a. Connecting to a Database Server
\nSqoop<\/strong><\/a> is designed to import tables from a database into HDFS<\/strong><\/a>. To do so, you must specify a connect string that describes how to connect to the database. The connect string is similar to a URL, and is communicated to Sqoop with the –connect argument. That defines the server and database to connect to; also specify the port.<\/span><\/h4>\n

For example:<\/strong>
\n$ sqoop import –connect jdbc:mysql:\/\/database.example.com\/employees
\nTable 2. Sqoop Import – Validation arguments More Details<\/strong><\/h4>\n\n\n\n\n\n\n\n
Argument<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
–validate<\/span><\/td>\nEnable validation of data copied, supports single table copy only.<\/span><\/td>\n<\/tr>\n
–validator <class-name><\/span><\/td>\nSpecify validator class to use.<\/span><\/td>\n<\/tr>\n
–validation-threshold <class-name><\/span><\/td>\nSpecify validation threshold class to use.<\/span><\/td>\n<\/tr>\n
–validation-failurehandler <class-name><\/span><\/td>\nSpecify validation failure handler class to use.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Table 3. Sqoop Import – Import control arguments<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Argument<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
–append<\/span><\/td>\nAppend data to an existing dataset in HDFS<\/span><\/td>\n<\/tr>\n
–as-avrodatafile<\/span><\/td>\nImports data to Avro Data Files<\/span><\/td>\n<\/tr>\n
–as-sequencefile<\/span><\/td>\nImports data to SequenceFiles<\/span><\/td>\n<\/tr>\n
–as-textfile<\/span><\/td>\nImports data as plain text (default)<\/span><\/td>\n<\/tr>\n
–as-parquetfile<\/span><\/td>\nImports data to Parquet Files<\/span><\/td>\n<\/tr>\n
–boundary-query <statement><\/span><\/td>\nBoundary query to use for creating splits<\/span><\/td>\n<\/tr>\n
–columns <col,col,col\u2026><\/span><\/td>\nColumns to import from table<\/span><\/td>\n<\/tr>\n
–delete-target-dir<\/span><\/td>\nDelete the import target directory if it exists<\/span><\/td>\n<\/tr>\n
–direct<\/span><\/td>\nUse direct connector if exists for the database<\/span><\/td>\n<\/tr>\n
–fetch-size <n><\/span><\/td>\nNumber of entries to read from database at once.<\/span><\/td>\n<\/tr>\n
–inline-lob-limit <n><\/span><\/td>\nSet the maximum size for an inline LOB<\/span><\/td>\n<\/tr>\n
-m,–num-mappers <n><\/span><\/td>\nUse n map tasks to import in parallel<\/span><\/td>\n<\/tr>\n
-e,–query <statement><\/span><\/td>\nImport the results of statement.<\/span><\/td>\n<\/tr>\n
–split-by <column-name><\/span><\/td>\nColumn of the table used to split work units. Cannot be used with –autoreset-to-one-mapperoption.<\/span><\/td>\n<\/tr>\n
–autoreset-to-one-mapper<\/span><\/td>\nImport should use one mapper if a table has no primary key and no split-by column is provided. Cannot be used with –split-by <col> option.<\/span><\/td>\n<\/tr>\n
–table <table-name><\/span><\/td>\nTable to read<\/span><\/td>\n<\/tr>\n
–target-dir <dir><\/span><\/td>\nHDFS destination dir<\/span><\/td>\n<\/tr>\n
–warehouse-dir <dir><\/span><\/td>\nHDFS parent for table destination<\/span><\/td>\n<\/tr>\n
–where <where clause><\/span><\/td>\nWHERE clause to use during import<\/span><\/td>\n<\/tr>\n
-z,–compress<\/span><\/td>\nEnable compression<\/span><\/td>\n<\/tr>\n
–compression-codec <c><\/span><\/td>\nUse Hadoop codec (default gzip)<\/span><\/td>\n<\/tr>\n
–null-string <null-string><\/span><\/td>\nThe string to be written for a null value for string columns<\/span><\/td>\n<\/tr>\n
–null-non-string <null-string><\/span><\/td>\nThe string to be written for a null value for non-string columns<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Although, both –null-string and –null-non-string arguments are optional.However, we use the string “null” if not specified.<\/p>\n

b. Selecting the Data to Import
\nBasically, Sqoop imports data in a table-centric fashion. we generally use the –table argument while selecting the table to import. like, –table employees. However, this argument in a database can also identify a VIEW or other table-like entity.<\/span>
\nHowever, all the data is written to HDFS<\/strong><\/a> in its “natural order”. That is a table containing columns A, B, and C results in an import of data in Sqoop. Such as:<\/span>
\nA1,B1,C1<\/strong>
\nA2,B2,C2<\/strong>
\n…<\/strong>
\nBy selecting a subset of columns, with\u00a0–columns argument we can control their ordering. The only condition is that it should include a comma-delimited list of columns to import. Like: –columns “name,employee_id,jobtitle”.<\/span><\/h4>\n

c. Free-form Query Imports
\nWe can also import the result set of an arbitrary SQL query in Sqoop. Also, we\u00a0can specify a SQL statement with the –query argument. Despite using the –table, –columns and –where arguments.<\/span><\/h4>\n

While we import a free-form query,\u00a0we need to specify a destination directory with –target-dir.
\nIn addition, we can import the results of a query in parallel. Afterwards,\u00a0 each map task will need to execute a copy of the query, with results partitioned by bounding conditions inferred by Sqoop. However, our query must include the token $CONDITIONS. That each Sqoop process will replace with a unique condition expression. Also important to select a splitting column with –split-by.<\/h4>\n

For example:<\/strong>
\n$ sqoop import \\
\n–query ‘SELECT a.*, b.* FROM a JOIN b on (a.id == b.id) WHERE $CONDITIONS’ \\
\n–split-by a.id –target-dir \/user\/foo\/joinresults
\nBy specifying a single map task with -m 1, the query can be executed once and imported serially.<\/span>
\n$ sqoop import \\
\n–query ‘SELECT a.*, b.* FROM a JOIN b on (a.id == b.id) WHERE $CONDITIONS’ \\
\n-m 1 –target-dir \/user\/foo\/joinresults<\/h4>\n

d. Controlling Parallelism
\nFrom most database sources, Sqoop imports data in parallel. Also, to perform the import in sqoop by using the -m or –num-mappers argument. Moreover, we can specify the number of map tasks (parallel processes) to use each of these arguments takes an integer value which corresponds to the degree of parallelism to employ.\u00a0<\/span><\/h4>\n

e. Controlling Distributed Cache
\nBasically, in $SQOOP_HOME\/lib folder, Sqoop will copy the jars to job cache every time when starting a Sqoop job. However,\u00a0 when\u00a0 Oozie launched it, this is unnecessary since Oozie uses its own Sqoop share lib which keeps Sqoop dependencies in the distributed cache. Although,\u00a0for the Sqoop dependencies Oozie will do the localization on each worker node only once during the first Sqoop job. Also, reuse the jars on worker node for subsequential jobs.\u00a0<\/span><\/h4>\n

f. Controlling the Sqoop Import Process
\nThe import process in sqoop will use JDBC, by default. That provides a reasonable cross-vendor import channel. However, by using database-specific data movement tools, some databases can perform imports in a more high-performance fashion.<\/span><\/h4>\n

In addition, inside your home directory in HDFS<\/a><\/strong>, Sqoop will import a table named foo to a directory named foo. For example, the Sqoop import tool will write to \/user\/someuser\/foo\/(files) if your username is some user. However, we can adjust the parent directory of the import with the –warehouse-dir argument. For example:
\n$ sqoop import –connnect <connect-str> –table foo –warehouse-dir \/shared \\
\n \u00a0\u00a0\u00a0…<\/span><\/h4>\n

g. Controlling transaction isolation
\nBasically,\u00a0to import data the read committed transaction isolation in the mappers are used in Sqoop. In all ETL workflows, this may not be the ideal. Yet it may desire to reduce the isolation guarantees. However, to instruct Sqoop to use read uncommitted isolation level we can use the –relaxed-isolation option.<\/span><\/h4>\n

Although, on all databases, the read-uncommitted isolation level is not supported. For example, Oracle. So specifying the option –relaxed-isolation may not be supported on all databases.<\/h4>\n

h. Controlling type mapping
\nBasically, to map most SQL types to appropriate Java<\/strong><\/a> or Hive<\/strong><\/a> representatives, Sqoop is preconfigured.\u00a0Although, here also\u00a0the default mapping might not be suitable for everyone. Also, might be overridden. Either by –map-column-java (for changing the mapping to Java) or –map-column-hive (for changing Hive mapping).<\/span>
\nTable 4. Parameters for overriding mapping<\/strong><\/h4>\n\n\n\n\n\n
Argument<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
–map-column-java <mapping><\/span><\/td>\nOverride mapping from SQL to Java type for configured columns.<\/span><\/td>\n<\/tr>\n
–map-column-hive <mapping><\/span><\/td>\nOverride mapping from SQL to Hive type for configured columns.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Basically, Sqoop is expecting the comma-separated list of mapping in the form <name of column>=<new type>. For example:
\n$ sqoop import … –map-column-java id=String,value=Integer
\nAlso, Sqoop will raise the exception in case that some configured mapping will not be used.<\/span><\/p>\n

i. Incremental Imports
\nThere is an incremental import mode offered by Sqoop. That can be used to retrieve only rows newer than some previously imported set of rows.<\/span>
\nThe following arguments control incremental imports in sqoop:
\nTable 5. Sqoop Import – Incremental import arguments<\/strong><\/h4>\n\n\n\n\n\n\n
Argument<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
–check-column (col)<\/span><\/td>\nSpecifies the column to be examined when determining which rows to import. (the column should not be of type CHAR\/NCHAR\/VARCHAR\/VARNCHAR\/ LONGVARCHAR\/LONGNVARCHAR)<\/span><\/td>\n<\/tr>\n
–incremental (mode)<\/span><\/td>\nSpecifies how Sqoop determines which rows are new. Legal values for mode include append and lastmodified.<\/span><\/td>\n<\/tr>\n
–last-value (value)<\/span><\/td>\nSpecifies the maximum value of the check column from the previous import.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Basically, there are two types of incremental imports in Sqoop.One is appended and second is last modified.\u00a0Moreover, to specify the type of incremental import to perform, we can also use the –incremental argument.<\/p>\n

j. File Formats
\nBasically, there are two file formats in which we can import data. One is delimited text or other is SequenceFiles.<\/span><\/h4>\n

k. Large Objects
\nIn particular ways, Sqoop handles large objects (BLOB and CLOB columns). However, if this data is truly large, then these columns should not be fully materialized in memory for manipulation, as most columns are. Despite, their data is handled in a streaming fashion.\u00a0<\/span>
\nTable 6. Sqoop Import –\u00a0Output line formatting arguments<\/strong><\/h4>\n\n\n\n\n\n\n\n\n\n
Argument<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
–enclosed-by <char><\/span><\/td>\nSets a required field enclosing character<\/span><\/td>\n<\/tr>\n
–escaped-by <char><\/span><\/td>\nSets the escape character<\/span><\/td>\n<\/tr>\n
–fields-terminated-by <char><\/span><\/td>\nSets the field separator character<\/span><\/td>\n<\/tr>\n
–lines-terminated-by <char><\/span><\/td>\nSets the end-of-line character<\/span><\/td>\n<\/tr>\n
–mysql-delimiters<\/span><\/td>\nUses MySQL\u2019s default delimiter set: fields: , lines: \\n escaped-by: \\ optionally-enclosed-by: ‘<\/span><\/td>\n<\/tr>\n
–optionally-enclosed-by <char><\/span><\/td>\nSets a field enclosing character<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Table 7. Sqoop Import –\u00a0Input parsing arguments<\/strong><\/p>\n\n\n\n\n\n\n\n\n
Argument<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
–input-enclosed-by <char><\/span><\/td>\nSets a required field encloser<\/span><\/td>\n<\/tr>\n
–input-escaped-by <char><\/span><\/td>\nSets the input escape character<\/span><\/td>\n<\/tr>\n
–input-fields-terminated-by <char><\/span><\/td>\nSets the input field separator<\/span><\/td>\n<\/tr>\n
–input-lines-terminated-by <char><\/span><\/td>\nSets the input end-of-line character<\/span><\/td>\n<\/tr>\n
–input-optionally-enclosed-by <char><\/span><\/td>\nSets a field enclosing character<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Table 8. Sqoop Import –\u00a0Hive arguments<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Argument<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
–hive-home <dir><\/span><\/td>\nOverride $HIVE_HOME<\/span><\/td>\n<\/tr>\n
–hive-import<\/span><\/td>\nImport tables into Hive (Uses Hive\u2019s default delimiters if none are set.)<\/span><\/td>\n<\/tr>\n
–hive-overwrite<\/span><\/td>\nOverwrite existing data in the Hive table.<\/span><\/td>\n<\/tr>\n
–create-hive-table<\/span><\/td>\nIf set, then the job will fail if the target hive table exits. By default this property is false.<\/span><\/td>\n<\/tr>\n
<\/td>\n<\/td>\n<\/tr>\n
–hive-table <table-name><\/span><\/td>\nSets the table name to use when importing to Hive.<\/span><\/td>\n<\/tr>\n
–hive-drop-import-delims<\/span><\/td>\nDrops \\n, \\r, and \\01 from string fields when importing to Hive.<\/span><\/td>\n<\/tr>\n
–hive-delims-replacement<\/span><\/td>\nReplace \\n, \\r, and \\01 from string fields with user defined string when importing to Hive.<\/span><\/td>\n<\/tr>\n
–hive-partition-key<\/span><\/td>\nName of a hive field to partition are sharded on<\/span><\/td>\n<\/tr>\n
–hive-partition-value <v><\/span><\/td>\nString-value that serves as partition key for this imported into hive in this job.<\/span><\/td>\n<\/tr>\n
–map-column-hive <map><\/span><\/td>\nOverride default mapping from SQL type to Hive type for configured columns.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

l. Importing Data Into Hive
\nUploading our data into files in HDFS<\/strong><\/a> is Sqoop\u2019s import tool\u2019s main function. However, if we have a Hive<\/strong> metastore<\/strong> <\/a>associated with our HDFS cluster, Sqoop can also import the data into Hive. It is possible by generating and executing a CREATE TABLE statement to define the data\u2019s layout in Hive. Also, it is the very simple method to import data into Hive<\/strong><\/a>, like adding the –hive-import option to your Sqoop command line.<\/span><\/h4>\n

Table 9. Sqoop Import –\u00a0HBase arguments<\/strong><\/h4>\n\n\n\n\n\n\n\n\n
Argument<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
–column-family <family><\/span><\/td>\nSets the target column family for the import<\/span><\/td>\n<\/tr>\n
–hbase-create-table<\/span><\/td>\nIf specified, create missing HBase tables<\/span><\/td>\n<\/tr>\n
–hbase-row-key <col><\/span><\/td>\nSpecifies which input column to use as the row key. In case, if input table contains composite key, then <col> must be in the form of a\u00a0comma-separated list of composite key\u00a0attributes.<\/span><\/td>\n<\/tr>\n
–hbase-table <table-name><\/span><\/td>\nSpecifies an HBase table to use as the target instead of HDFS<\/span><\/td>\n<\/tr>\n
–hbase-bulkload<\/span><\/td>\nEnables bulk loading<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

m. Importing Data Into HBase
\nBeyond HDFS<\/strong><\/a> and Hive<\/strong><\/a>, Sqoop supports additional import targets. Like Sqoop can also import records into a table in HBase<\/strong>.<\/a><\/span><\/h4>\n

Table 10. Sqoop Import –\u00a0Accumulo arguments<\/strong><\/h4>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
Argument<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
–accumulo-table <table-nam><\/span><\/td>\nSpecifies an Accumulo table to use as the target instead of HDFS<\/span><\/td>\n<\/tr>\n
–accumulo-column-family <family><\/span><\/td>\nSets the target column family for the import<\/span><\/td>\n<\/tr>\n
–accumulo-create-table<\/span><\/td>\nIf specified, create missing Accumulo tables<\/span><\/td>\n<\/tr>\n
–accumulo-row-key <col><\/span><\/td>\nSpecifies which input column to use as the row key<\/span><\/td>\n<\/tr>\n
–accumulo-visibility <vis><\/span><\/td>\n(Optional) Specifies a visibility token to apply to all rows inserted into Accumulo. Default is the empty string.<\/span><\/td>\n<\/tr>\n
–accumulo-batch-size <size><\/span><\/td>\n(Optional) Sets the size in bytes of Accumulo\u2019s write buffer. Default is 4MB.<\/span><\/td>\n<\/tr>\n
–accumulo-max-latency <ms><\/span><\/td>\n(Optional) Sets the max latency in milliseconds for the Accumulo batch writer. Default is 0.<\/span><\/td>\n<\/tr>\n
–accumulo-zookeepers <host:port><\/span><\/td>\nComma-separated list of Zookeeper servers used by the Accumulo instance<\/span><\/td>\n<\/tr>\n
–accumulo-instance <table-name><\/span><\/td>\nName of the target Accumulo instance<\/span><\/td>\n<\/tr>\n
–accumulo-user <username><\/span><\/td>\nName of the Accumulo user to import as<\/span><\/td>\n<\/tr>\n
–accumulo-password <password><\/span><\/td>\nPassword for the Accumulo user<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

n. Importing Data Into Accumulo
\nAlso,\u00a0in Accumulo, Sqoop supports importing records into a table.<\/span><\/h4>\n

Table 11. Sqoop Import –\u00a0Code generation arguments<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n
Argument<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
–bindir <dir><\/span><\/td>\nOutput directory for compiled objects<\/span><\/td>\n<\/tr>\n
–class-name <name><\/span><\/td>\nSets the generated class name. This overrides –package-name. When combined with –jar-file, sets the input class.<\/span><\/td>\n<\/tr>\n
–jar-file <file><\/span><\/td>\nDisable code generation; use specified jar<\/span><\/td>\n<\/tr>\n
–outdir <dir><\/span><\/td>\nOutput directory for generated code<\/span><\/td>\n<\/tr>\n
–package-name <name><\/span><\/td>\nPut auto-generated classes in this package<\/span><\/td>\n<\/tr>\n
–map-column-java <m><\/span><\/td>\nOverride default mapping from SQL type to Java type for configured columns.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

o. Additional Import Configuration Properties
\nSome additional properties which can be configured by modifying conf\/sqoop-site.xml. However, Properties can be specified the same as in Hadoop<\/strong> <\/a>configuration files.<\/span><\/h4>\n

For example:<\/strong><\/h4>\n
<property>\r\n<name>property.name<\/name>\r\n<value>property.value<\/value>\r\n<\/property><\/pre>\n
On the command line in the generic arguments,\u00a0they can also be specified.\u00a0 For example:<\/span>
\nsqoop import -D property.name=property.value …
\nTable 12. Sqoop Import –\u00a0Additional import configuration properties<\/strong><\/p>\n\n\n\n\n\n
Argument<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
sqoop.bigdecimal.format.string<\/span><\/td>\nControls how BigDecimal columns will formatted when stored as a String. A value of true (default) will use toPlainString to store them without an exponent component (0.0000001); while a value of false will use toString which may include an exponent (1E-7)<\/span><\/td>\n<\/tr>\n
sqoop.hbase.add.row.key<\/span><\/td>\nWhen set to false (default), Sqoop will not add the column used as a row key into the row data in HBase. When set to true, the column used as a row key will be added to the row data in HBase.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Sqoop Import Example<\/h3>\n
Basically, we will understand how to use the import tool in a variety of situations by the following examples.<\/span>
\nIn addition, a basic import of a table named EMPLOYEES in the corp database:<\/strong>
\n$ sqoop import –connect jdbc:mysql:\/\/db.foo.com\/corp –table EMPLOYEES
\nAlso, a basic import requiring a login:<\/strong>
\n$ sqoop import –connect jdbc:mysql:\/\/db.foo.com\/corp –table EMPLOYEES \\
\n–username SomeUser -P
\nEnter password: (hidden)
\nSo selecting specific columns from the EMPLOYEES table:<\/strong>
\n$ sqoop import –connect jdbc:mysql:\/\/db.foo.com\/corp –table EMPLOYEES \\
\n–columns “employee_id,first_name,last_name,job_title”
\nControlling the import parallelism (using 8 parallel tasks):<\/strong>
\n$ sqoop import –connect jdbc:mysql:\/\/db.foo.com\/corp –table EMPLOYEES \\<\/span>
\n \u00a0\u00a0\u00a0-m 8<\/span>
\nStoring data in SequenceFiles, and setting the generated class name to com.foocorp.Employee:<\/strong>
\n$ sqoop import –connect jdbc:mysql:\/\/db.foo.com\/corp –table EMPLOYEES \\
\n–class-name com.foocorp.Employee –as-sequencefile
\nAlso, specifying the delimiters to use in a text-mode import:<\/strong>
\n$ sqoop import –connect jdbc:mysql:\/\/db.foo.com\/corp –table EMPLOYEES \\
\n–fields-terminated-by ‘\\t’ –lines-terminated-by ‘\\n’ \\
\n–optionally-enclosed-by ‘\\”‘
\nBasically here, importing the data to Hive:<\/strong>
\n$ sqoop import –connect jdbc:mysql:\/\/db.foo.com\/corp –table EMPLOYEES \\
\n–hive-import
\nAlso, here, only importing new employees:<\/strong>
\n$ sqoop import –connect jdbc:mysql:\/\/db.foo.com\/corp –table EMPLOYEES \\
\n–where “start_date > ‘2010-01-01′”
\nAfterwards, changing the splitting column from the default:<\/strong>
\n$ sqoop import –connect jdbc:mysql:\/\/db.foo.com\/corp –table EMPLOYEES \\
\n–split-by dept_id
\nThen, we are verifying that an import was successful:<\/strong>
\n$ hadoop fs -ls EMPLOYEES
\nFound 5 items
\ndrwxr-xr-x \u00a0\u00a0– someuser somegrp \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a00 2010-04-27 16:40 \/user\/someuser\/EMPLOYEES\/_logs
\n-rw-r–r– \u00a0\u00a01 someuser somegrp \u00a0\u00a0\u00a02913511 2010-04-27 16:40 \/user\/someuser\/EMPLOYEES\/part-m-00000<\/span>
\n-rw-r–r– \u00a0\u00a01 someuser somegrp \u00a0\u00a0\u00a01683938 2010-04-27 16:40 \/user\/someuser\/EMPLOYEES\/part-m-00001
\n-rw-r–r– \u00a0\u00a01 someuser somegrp \u00a0\u00a0\u00a07245839 2010-04-27 16:40 \/user\/someuser\/EMPLOYEES\/part-m-00002
\n-rw-r–r– \u00a0\u00a01 someuser somegrp \u00a0\u00a0\u00a07842523 2010-04-27 16:40 \/user\/someuser\/EMPLOYEES\/part-m-00003
\n$ hadoop fs -cat EMPLOYEES\/part-m-00000 | head -n 10
\n0,joe,smith,engineering
\n1,jane,doe,marketing
\n…
\nAfter having already imported the first 100,000 rows of a table, Here performing an incremental import of new data:<\/strong>
\n$ sqoop import –connect jdbc:mysql:\/\/db.foo.com\/somedb –table sometable \\
\n–where “id > 100000” –target-dir \/incremental_dataset –append
\nIn the corp database, there is an import of a table named EMPLOYEES. That uses validation to validate the import. By using the table row count and the number of rows copied into HDFS.\u00a0<\/strong><\/span>
\n$ sqoop import –connect jdbc:mysql:\/\/db.foo.com\/corp \\
\n–table EMPLOYEES –validate<\/p>\n
So, this was all in Sqoop Import tutorial. Hope you like our explanation.<\/p>\n
5.Conclusion<\/h3>\n
Hence, in this article, we have learned the whole concept of Sqoop Import. Also, we have seen various Sqoop Import examples and Sqoop import syntax. However, if you want to ask any query regarding, please ask in the comment section below. We will definitely get back to you.
\nSee also – Sqoop Mainframe Tool<\/a> <\/strong>& why Should learn Sqoop<\/a><\/strong>
\nFor reference<\/strong><\/a>
In the last article, we discussed\u00a0Sqoop Export. In this article we will discuss Sqoop import,\u00a0a\u00a0tool which we use for importing tables from RDBMS to HDFS is the Sqoop Import tool. Here, we will learn...<\/p>\n","protected":false},"author":9,"featured_media":35179,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[68],"tags":[6641,7122,13630,13632,13633,13637],"class_list":["post-6946","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqoop","tag-importing-data-into-hive","tag-introduction-to-sqoop-import","tag-sqoop-import-arguments","tag-sqoop-import-examples","tag-sqoop-import-in-sqoop","tag-sqoop-import-syntax"],"yoast_head":"\nSqoop Import- Importing Data From RDBMS to HDFS - DataFlair<\/title>\n<meta name=\"description\" content=\"Sqoop Import- Importing data from RDBMS to Hadoop HDFS Sqoop Import syntax and Examples of Sqoop Import, how to import data from RDBMS to HDFS using Sqoop\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/sqoop-import\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Sqoop Import- Importing Data From RDBMS to HDFS - DataFlair\" \/>\n<meta property=\"og:description\" content=\"Sqoop Import- Importing data from RDBMS to Hadoop HDFS Sqoop Import syntax and Examples of Sqoop Import, how to import data from RDBMS to HDFS using Sqoop\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/sqoop-import\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-02-01T09:26:30+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-12-07T08:16:51+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/02\/Sqoop-Import-Tutorial-01-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1202\" \/>\n\t<meta property=\"og:image:height\" content=\"630\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"13 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Sqoop Import- Importing Data From RDBMS to HDFS - DataFlair","description":"Sqoop Import- Importing data from RDBMS to Hadoop HDFS Sqoop Import syntax and Examples of Sqoop Import, how to import data from RDBMS to HDFS using Sqoop","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/sqoop-import\/","og_locale":"en_US","og_type":"article","og_title":"Sqoop Import- Importing Data From RDBMS to HDFS - DataFlair","og_description":"Sqoop Import- Importing data from RDBMS to Hadoop HDFS Sqoop Import syntax and Examples of Sqoop Import, how to import data from RDBMS to HDFS using Sqoop","og_url":"https:\/\/data-flair.training\/blogs\/sqoop-import\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-02-01T09:26:30+00:00","article_modified_time":"2021-12-07T08:16:51+00:00","og_image":[{"width":1202,"height":630,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/02\/Sqoop-Import-Tutorial-01-1.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"13 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/sqoop-import\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/sqoop-import\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/6e11a88eea98179e861a0933f8959f8f"},"headline":"Sqoop Import- Importing Data From RDBMS to HDFS","datePublished":"2018-02-01T09:26:30+00:00","dateModified":"2021-12-07T08:16:51+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sqoop-import\/"},"wordCount":3066,"commentCount":0,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sqoop-import\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/02\/Sqoop-Import-Tutorial-01-1.jpg","keywords":["Importing Data Into Hive","Introduction to Sqoop Import","Sqoop import arguments","Sqoop Import Examples","Sqoop Import in Sqoop","Sqoop Import syntax"],"articleSection":["Sqoop Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/sqoop-import\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/sqoop-import\/","url":"https:\/\/data-flair.training\/blogs\/sqoop-import\/","name":"Sqoop Import- Importing Data From RDBMS to HDFS - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sqoop-import\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sqoop-import\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/02\/Sqoop-Import-Tutorial-01-1.jpg","datePublished":"2018-02-01T09:26:30+00:00","dateModified":"2021-12-07T08:16:51+00:00","description":"Sqoop Import- Importing data from RDBMS to Hadoop HDFS Sqoop Import syntax and Examples of Sqoop Import, how to import data from RDBMS to HDFS using Sqoop","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/sqoop-import\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/sqoop-import\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/sqoop-import\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/02\/Sqoop-Import-Tutorial-01-1.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/02\/Sqoop-Import-Tutorial-01-1.jpg","width":1202,"height":630,"caption":"Sqoop Import - Importing Data From RDBMS to HDFS"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/sqoop-import\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Sqoop Tutorials","item":"https:\/\/data-flair.training\/blogs\/category\/sqoop\/"},{"@type":"ListItem","position":3,"name":"Sqoop Import- Importing Data From RDBMS to HDFS"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/6e11a88eea98179e861a0933f8959f8f","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/54363d8635c383dcd7e3be0ef00dbeef4217c45bf323d27bc2c92366b724df08?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/54363d8635c383dcd7e3be0ef00dbeef4217c45bf323d27bc2c92366b724df08?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/54363d8635c383dcd7e3be0ef00dbeef4217c45bf323d27bc2c92366b724df08?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"At DataFlair, our team of experts is committed to offering high-quality tutorials on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. We simplify learning to help you advance your career in the tech world.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam5\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/6946","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/9"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=6946"}],"version-history":[{"count":9,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/6946\/revisions"}],"predecessor-version":[{"id":105369,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/6946\/revisions\/105369"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/35179"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=6946"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=6946"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=6946"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}