{"id":7013,"date":"2018-02-01T12:20:42","date_gmt":"2018-02-01T12:20:42","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=7013"},"modified":"2021-12-07T13:33:58","modified_gmt":"2021-12-07T08:03:58","slug":"sqoop-export","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sqoop-export\/","title":{"rendered":"Sqoop Export – Exporting From HDFS to RDBMS"},"content":{"rendered":"

After Sqoop Import<\/strong><\/a>, there is a tool which exports a set of files from HDFS<\/strong> <\/a>back to RDBMS, that tool is what we call an Export Tool in Apache Sqoop<\/strong><\/a>. In this article, we will learn the whole concept of Sqoop Export. Along with Sqoop Export introduction and Purpose. Moreover, we will learn Sqoop Export syntax with example invocations to understand it well.<\/p>\n

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

Introduction to Sqoop Export and its Purpose<\/h3>\n

A tool which exports a set of files from HDFS<\/strong> <\/a>back to RDBMS. That tool is what we call a Sqooop Export Tool. There is one condition for it, that in the database, target\u00a0the table must already exist. However,\u00a0the input files are read and parsed according to the user-specified delimiters into a set of records.<\/p>\n

In addition, the default operation is to transform these into a set of INSERT statements that inject the records into the database. In “update mode,” sqoop will generate UPDATE statements that replace existing records in the database, and in “call mode” sqoop will make a stored procedure call for each record.<\/p>\n

Sqoop Export Syntax<\/h3>\n

$ sqoop export (generic-args) (export-args)
\n$ sqoop-export (generic-args) (export-args)
\nHowever, the export arguments can be entered in any order with respect to one another, but the Hadoop <\/a><\/strong>generic arguments must precede any export arguments.
\nTable 1. Common arguments<\/b><\/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
–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-mapred-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<\/tbody>\n<\/table>\n

Table 2. Validation arguments More Details<\/b><\/p>\n\n\n\n\n\n\n\n
Argument<\/strong><\/td>\nDescription<\/strong><\/td>\n<\/tr>\n
–validate<\/td>\nEnable validation of data copied, supports single table copy only.<\/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. Export control arguments:<\/b><\/p>\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
–columns <col,col,col\u2026><\/span><\/td>\nColumns to export to table<\/span><\/td>\n<\/tr>\n
–direct<\/span><\/td>\nUse direct export fast path<\/span><\/td>\n<\/tr>\n
–export-dir <dir><\/span><\/td>\nHDFS source path for the export<\/span><\/td>\n<\/tr>\n
-m,–num-mappers <n><\/span><\/td>\nUse n map tasks to export in parallel<\/span><\/td>\n<\/tr>\n
–table <table-name><\/span><\/td>\nTable to populate<\/span><\/td>\n<\/tr>\n
–call <stored-proc-name><\/span><\/td>\nStored Procedure to call<\/span><\/td>\n<\/tr>\n
–update-key <col-name><\/span><\/td>\nAnchor column to use for updates. Use a comma separated list of columns if there are more than one column.<\/span><\/td>\n<\/tr>\n
–update-mode <mode><\/span><\/td>\nSpecify how updates are performed when new rows are found with non-matching keys in database.<\/span><\/td>\n<\/tr>\n
<\/td>\nLegal values for mode include updateonly (default) and allowinsert.<\/span><\/td>\n<\/tr>\n
–input-null-string <null-string><\/span><\/td>\nThe string to be interpreted as null for string columns<\/span><\/td>\n<\/tr>\n
–input-null-non-string <null-string><\/span><\/td>\nThe string to be interpreted as null for non-string columns<\/span><\/td>\n<\/tr>\n
–staging-table <staging-table-name><\/span><\/td>\nThe table in which data will be staged before being inserted into the destination table.<\/span><\/td>\n<\/tr>\n
–clear-staging-table<\/span><\/td>\nIndicates that any data present in the staging table can be deleted.<\/span><\/td>\n<\/tr>\n
–batch<\/span><\/td>\nUse batch mode for underlying statement execution.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Basically, we need both the –export-dir argument and one of –table or –call. Although, it specifies the table to populate in the database (or the stored procedure to call). Also, the directory in HDFS<\/strong> <\/a>that contains the source data.<\/p>\n

In addition, all columns within a table are selected for export in Sqoop, by default. Moreover, by using the –columns argument we can select a subset of columns and control their ordering. Although, it should include a comma-delimited list of columns to export. Like: –columns “col1,col2,col3”.<\/p>\n

Note:<\/strong>\u00a0Columns that are not included in the –columns parameter need to have either defined default value or allow NULL values.<\/p>\n

Difference between Inserts vs. Updates in Sqoop<\/h3>\n

As we know, sqoop export appends new rows to a table. Here each input record is transformed into an INSERT statement that adds a row to the target database table. However, if our table\u00a0already contains data and has constraints, we must take care to avoid inserting records. <\/span><\/p>\n

Since, that violate these constraints. Make sure that if an INSERT statement fails the export process will fail. However, for exporting records to a new this mode is primarily intended. Since, empty table intended to receive these results.<\/span><\/p>\n

In addition,\u00a0Sqoop will modify an existing dataset in the database, if we specify the –update-key argument. Although, here each input record is considered as an UPDATE statement. Basically, that modifies an existing row.\u00a0<\/span>
\nCREATE TABLE foo(
\nid INT NOT NULL PRIMARY KEY,
\nmsg VARCHAR(32),
\nbar INT);
\nIn HDFS<\/strong><\/a>, consider also a dataset containing records like these:<\/span>
\n0,this is a test,42
\n1,some more data,100
\n…
\nAlso, Running sqoop-export –table foo –update-key id –export-dir \/path\/to\/data –connect \u2026 will run an export job that executes SQL statements based on the data like so:<\/span>
\nUPDATE foo SET msg=’this is a test’, bar=42 WHERE id=0;
\nUPDATE foo SET msg=’some more data’, bar=100 WHERE id=1;
\n…
\nWe can also specify the –update-mode argument with allowinsert mode depending on the target database, if we want to update rows, if they exist in the database already or insert rows if they do not exist yet.
\nTable 4. Input parsing arguments:<\/b><\/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 5. Output line formatting arguments:<\/b><\/p>\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

Moreover, Sqoop will fail to find enough columns per line, if we specify incorrect delimiters. Basically, that will cause export map tasks to fail by throwing ParseExceptions.
\nTable 6. Code 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

Exports and Transactions in Sqoop<\/h3>\n

However, by multiple writers in parallel Exports are performed. Basically, each writer uses a separate connection to the database. These have separate transactions from one another. Although, to insert up to 100 records per statement Sqoop uses the multi-row INSERT syntax. The current transaction within a writing task is committed, every 100 statements, that is causing a commit every 10,000 rows. <\/span><\/p>\n

That ensures that transaction buffers do not grow without bound. Also, cause out-of-memory conditions. Hence, a Sqoop export is not an atomic process. Note that, before the export is complete Partial results from the export will become visible.<\/span><\/p>\n

Failed Sqoop Export<\/h3>\n

There are various reasons due to which export in sqoop may fail such as:<\/span><\/p>\n