Similar to Sqoop Import, there is another tool named Sqoop Export in Sqoop, which is used for exporting a set of files from the HDFS back to the RDBMS. In this Sqoop Export article, you will explore the entire concept of Sqoop Export.<\/p>\n
The article will explain what Sqoop Export is, modes in which Sqoop Export works, its syntax, arguments, and many more. The article also covers the difference between the insert mode and update mode.<\/p>\n
Moreover, we will learn the Sqoop Export syntax with an example invocation to understand it better.<\/p>\n
Let us first start with an introduction to Sqoop Export.<\/p>\n
The Sqoop export tool is used for exporting a set of files from the Hadoop Distributed File System back to the RDBMS. For performing export, the target table must exist on the target database.<\/p>\n
The files given as an input to Apache Sqoop contain the records, which are called as rows in the table. These files are read and parsed into the set of records and delimited with the user-specified delimiter.
\nThe export command works in two modes- insert mode and update mode.<\/p>\n
1. Insert mode:<\/strong> It is the default mode. In this mode, the records from the input files are inserted into the database table by using the INSERT statement. The Syntax for Sqoop Export are:<\/p>\n The Hadoop generic arguments should be passed before any export arguments, and we can enter export arguments in any order with respect to each other.<\/p>\n The common arguments are:<\/strong><\/p>\n The Export control arguments are:<\/strong><\/p>\n The Sqoop-export, by default, appends the new rows to the table. Each input record is transformed into the INSERT statement, which adds a row to the target database table.<\/p>\n If our table contains some constraints like the primary key column and already contains the data, then you have to take care to avoid the insertion of those records, which can violate these constraints.<\/p>\n If an INSERT statement fails, then the export process will fail. This mode is primarily intended for exporting the records to the new, empty table, which is intended to receive these results.<\/p>\n If we specify the argument –update-key<\/strong>, then Sqoop will instead modify the existing dataset in a database. Each input record is then treated as the UPDATE statement, which modifies the existing row.<\/p>\n The row which needs to be modified is identified by using the column name(s) specified with the –update-key<\/strong>. Also, consider the dataset in the HDFS which contains the records like these: will run the export job which executes the SQL statements based on the data like so:<\/p>\n If in case the UPDATE statement modifies no rows, then it is not considered as an error. Instead, the export will silently continue. This means that the update-based export will not insert the new rows into a database).<\/p>\n Also, if the column specified via –-update-key<\/strong> doesn\u2019t uniquely identify the rows and the multiple rows get updated by the single statement, then this condition is also undetected.<\/p>\n The Input parsing arguments are:<\/strong><\/p>\n The Output line formatting arguments are:<\/strong><\/p>\n The Code generation arguments are:<\/strong><\/p>\n Sqoop automatically generates the code for parsing and interpreting the records of the files which contain the data which is to be exported back to the database.<\/p>\n If these files are created with the non-default delimiters, then you have to specify the same delimiters again so that Sqoop can parse your files.<\/p>\n In case if you specify the incorrect delimiters, then Sqoop fails to find enough columns per line. This causes export map tasks to fail by throwing the ParseExceptions.<\/p>\n In Sqoop, the exports are performed by multiple writers in parallel. Each writer uses a separate connection with the database. These separate connections will have separate transactions from one another.<\/p>\n Apache Sqoop uses multi-row INSERT syntax for inserting up to 100 records per statement. This ensures that the transaction buffers will not go out-of-bound, and thus does not cause out-of-memory conditions.<\/p>\n Hence, a Sqoop export is not an atomic process. The partial results from export are visible before export is complete.<\/p>\n The Sqoop exports may get failed because of the following reasons:<\/p>\n If the export map task fails due to any of these reasons, then it will result in export job failure. The failed export job results are undefined. Each sqoop export map task operates in the separate transaction.<\/p>\n The individual map tasks commit their current transaction periodically. If the task fails, then the current transaction is rolled back. Any of the previously-committed transactions remains durable in a database, which leads to the partially-complete export.<\/p>\n Example 1:<\/strong> In this example, we are trying a basic export to populate the table emp:<\/p>\n This example takes files in the \/results\/emp_data<\/strong> and injects their contents into the emp table in the demo database on localhost. Make sure that the target table already exists in a database.<\/p>\n Example 2:<\/strong> In this example, we are trying to perform basic export in order to populate a table named emp with the validation enabled:<\/p>\n I hope after reading this article, you clearly understand Sqoop Export. The Sqoop export tool is useful for exporting a set of files from the Hadoop Distributed File System back to the RDBMS. The article had clearly explained its syntax, arguments along with an example.<\/p>\n","protected":false},"excerpt":{"rendered":" Similar to Sqoop Import, there is another tool named Sqoop Export in Sqoop, which is used for exporting a set of files from the HDFS back to the RDBMS. In this Sqoop Export article,...<\/p>\n","protected":false},"author":1,"featured_media":79663,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3163],"tags":[3175,3176,3177,3178],"class_list":["post-79619","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqoop","tag-failed-sqoop-export","tag-introduction-to-sqoop-export","tag-sqoop-export","tag-sqoop-export-syntax"],"yoast_head":"\n
\n2. Update mode:<\/strong> In the update mode, Sqoop generates an UPDATE statement that replaces existing records into the database.<\/p>\nSyntax for Sqoop Export<\/h4>\n
$ sqoop export (generic-args) (export-args)<\/pre>\n
$ sqoop-export (generic-args) (export-args)<\/pre>\n
\n\n
\n Argument<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n –connect <jdbc-uri><\/span><\/td>\n It specifies the JDBC connect string<\/span><\/td>\n<\/tr>\n \n –connection-manager <class-name><\/span><\/td>\n It specifies the connection manager class to be used<\/span><\/td>\n<\/tr>\n \n –driver <class-name><\/span><\/td>\n Manually specify JDBC driver class to use<\/span><\/td>\n<\/tr>\n \n –hadoop-home <dir><\/span><\/td>\n Override $HADOOP_HOME<\/span><\/td>\n<\/tr>\n \n –help<\/span><\/td>\n Print usage instructions<\/span><\/td>\n<\/tr>\n \n -P<\/span><\/td>\n Read the password from console<\/span><\/td>\n<\/tr>\n \n –password <password><\/span><\/td>\n Set authentication password<\/span><\/td>\n<\/tr>\n \n –username <username><\/span><\/td>\n Set authentication username<\/span><\/td>\n<\/tr>\n \n –verbose<\/span><\/td>\n Print more information while working<\/span><\/td>\n<\/tr>\n \n –connection-param-file <filename><\/span><\/td>\n Optional properties file that provides connection parameters<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\n
\n Argument<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n –direct<\/span><\/td>\n Use direct export fast path<\/span><\/td>\n<\/tr>\n \n –export-dir <dir><\/span><\/td>\n It specifies the HDFS source path for export<\/span><\/td>\n<\/tr>\n \n -m,–num-mappers <n><\/span><\/td>\n Use <\/span>n<\/span><\/i> map tasks to export in parallel<\/span><\/td>\n<\/tr>\n \n –table <table-name><\/span><\/td>\n Table to populate<\/span><\/td>\n<\/tr>\n \n –update-key <col-name><\/span><\/td>\n It specifies the anchor column to be used for updates. If there are more than one column, then we use the comma-separated list of columns.<\/span><\/td>\n<\/tr>\n \n –update-mode <mode><\/span><\/td>\n It will specify how the updates were performed when the new rows were found with the non-matching keys in a database. The updateonly and the allowinsert are the legal values for mode.<\/span><\/td>\n<\/tr>\n \n –input-null-string <null-string><\/span><\/td>\n It specify the string which is to be interpreted as null for string columns<\/span><\/td>\n<\/tr>\n \n –input-null-non-string <null-string><\/span><\/td>\n It specify the string which is to be interpreted as null for non-string columns<\/span><\/td>\n<\/tr>\n \n –staging-table <staging-table-name><\/span><\/td>\n It specifies the table in which the data will be staged before getting inserted into a destination table.<\/span><\/td>\n<\/tr>\n \n –clear-staging-table<\/span><\/td>\n It indicates that any data which is present in the staging table can be deleted.<\/span><\/td>\n<\/tr>\n \n –batch<\/span><\/td>\n It specifies to use the batch mode for the underlying statement execution.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n
Sqoop Inserts vs. Updates<\/h3>\n
\nFor example, we have the following table definition:<\/p>\nCREATE TABLE emp(\n emp_id NOT NULL PRIMARY KEY, \nemp_name VARCHAR(32), salary INT);\n<\/pre>\n
\n0,Ajay,40000
\n1,Raj,10000
\n…
\nOn running the command<\/p>\nsqoop-export --table emp --update-key emp_id --export-dir \/path\/to\/data --connect \u2026<\/pre>\n
UPDATE emp SET emp_name='Ajay', salary=40000 WHERE emp_id=0;\nUPDATE emp SET emp_name='Raj', salary=10000 WHERE emp_id=0;\n...\n<\/pre>\n
Other Arguments<\/h3>\n
\n\n
\n Argument<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n –input-enclosed-by <char><\/span><\/td>\n Sets a required field encloser<\/span><\/td>\n<\/tr>\n \n –input-escaped-by <char><\/span><\/td>\n Sets the input escape character<\/span><\/td>\n<\/tr>\n \n –input-fields-terminated-by <char><\/span><\/td>\n Sets the input field separator<\/span><\/td>\n<\/tr>\n \n –input-lines-terminated-by <char><\/span><\/td>\n Sets the input end-of-line character<\/span><\/td>\n<\/tr>\n \n –input-optionally-enclosed-by <char><\/span><\/td>\n Sets a field enclosing character<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\n
\n Argument<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n –enclosed-by <char><\/span><\/td>\n It will set the required field enclosing character<\/span><\/td>\n<\/tr>\n \n –escaped-by <char><\/span><\/td>\n It will set the escape character<\/span><\/td>\n<\/tr>\n \n –fields-terminated-by <char><\/span><\/td>\n It will set the field separator character<\/span><\/td>\n<\/tr>\n \n –lines-terminated-by <char><\/span><\/td>\n It will set the end-of-line character<\/span><\/td>\n<\/tr>\n \n –mysql-delimiters<\/span><\/td>\n Uses the MySQL\u2019s default delimiter set: fields: , lines: \\n escaped-by: \\ optionally-enclosed-by: ‘<\/span><\/td>\n<\/tr>\n \n –optionally-enclosed-by <char><\/span><\/td>\n It will set the field enclosing character<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n \n\n
\n Argument<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n –bindir <dir><\/span><\/td>\n It will specify the output directory for the compiled objects<\/span><\/td>\n<\/tr>\n \n –class-name <name><\/span><\/td>\n It will set the generated class name.\u00a0<\/span><\/td>\n<\/tr>\n \n –jar-file <file><\/span><\/td>\n It will disable the code generation; use specified jar<\/span><\/td>\n<\/tr>\n \n –outdir <dir><\/span><\/td>\n It will specify the output directory for the generated code<\/span><\/td>\n<\/tr>\n \n –package-name <name><\/span><\/td>\n It will put the auto-generated classes in the package specified<\/span><\/td>\n<\/tr>\n \n –map-column-java <m><\/span><\/td>\n Override the default mapping from the SQL type to the Java type for the configured columns.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n How does Sqoop Export work?<\/h3>\n
Sqoop Failed Exports<\/h3>\n
\n
Sqoop Export Example Invocations<\/h3>\n
$ sqoop export --connect jdbc:mysql:\/\/localhost\/demo_db --table emp \\\n --export-dir \/results\/emp_data\n<\/pre>\n
$ sqoop export --connect jdbc:mysql:\/\/localhost\/demo --table emp \\\n --export-dir \/results\/emp_data --validate\n<\/pre>\n
Summary<\/h3>\n