![\"Sqoop](\"https:\/\/techvidvan.com\/tutorials\/wp-content\/uploads\/sites\/2\/2020\/08\/Sqoop-Connectors-Drivers-TV.jpg\")
<\/a><\/p>\nWhat is a Sqoop Driver?<\/h3>\n
In Apache Sqoop, the word driver refers to the JDBC Driver. Basically, JDBC is the standard Java API for accessing RDBMS and some data warehouses. The Java language prescribes only about classes and the methods contained in this interface and JDK does not have any default implementat<\/p>\n
ion.<\/p>\n
The database vendors have to write their own implementation, which will communicate with the corresponding database with its native protocol.<\/p>\n
Since the database vendors create the drivers due to which they are usually offered with the restrictive licenses. This prohibits their shipping with the Apache Sqoop distribution. Thus, the user has to separately download the driver and install them into the Sqoop prior to its use.<\/p>\n
What is Sqoop Connector?<\/h3>\n
For communicating with the RDBMS, there is a programming language known as Structured Query Language (SQL).<\/p>\n
There is a standard that prescribes how the language SQL should look like, but every database has some of its own dialect of SQL. The basics were usually the same across all the databases, but there are some changes in some conditions.<\/p>\n
Sqoop connectors allow Apache Sqoop to overcome the differences in the SQL dialects supported by the different relational databases along with providing the optimized data transfer.<\/p>\n
A connector in Apache Sqoop is a pluggable piece which is used for fetching metadata about the transferred data (such as columns, data types, …) and to drive data transfer itself in a most efficient manner.<\/p>\n
The connector will work on various databases out of the box. Apache Sqoop also ships with the specialized connectors for databases like MySQL, Microsoft SQL Server, PostgreSQL, Oracle, DB2 and Netezza.<\/p>\n
Thus, generally we don\u2019t have to download the extra connectors for starting data transfer. However, some special connectors are available on the internet that add support for the additional database systems or improve the performance of built-in connectors.<\/p>\n
Let us explore special connectors for various databases.<\/p>\n
Sqoop shipped Specialized Connectors for various Databases<\/h3>\n1. MySQL JDBC Connector<\/h4>\n
It is the most basic connector shipped with Apache Sqoop. This connector uses only the JDBC interface for accessing metadata and transferring data. The MySQL JDBC Connector supports the upsert functionality by using the argument –update-mode allow insert.<\/p>\n
2. MySQL Direct Connector<\/h4>\n
This connector allows the faster import and export to or from the MySQL by using the mysqldump and the mysqlimport tools functionality instead of using the SQL selects and inserts. For using the MySQL Direct Connector, we have to specify the –direct argument for our import\/export job.<\/p>\n
For example:<\/strong><\/p>\n Limitations<\/strong><\/p>\n The Microsoft SQL Connector is the plugin provided by Microsoft for connecting with the SQL instance.<\/p>\n The connection string for PostgreSQL Connector is:<\/p>\n For eg:<\/strong><\/p>\n The extra arguments supported by the PostgreSQL Connector is:<\/p>\n The PostgreSQL Direct Connector allows for the faster import and export to and from the PostgreSQL “COPY” command.<\/p>\n For using PostgreSQL Direct Connector, we have to specify the –direct argument for our import\/export job.<\/p>\n The additional PostgreSQL argument supported by direct connector are:<\/p>\n Limitations<\/strong><\/p>\n The pg_bulkload connector is the direct connector for exporting data into PostgreSQL. The pg_bulkload connector uses pg_bulkload.<\/p>\n The benefits of this connector is:<\/strong><\/p>\n For export job execution the pg_bulkload connector requires the following conditions:<\/p>\n The Netezza connector provides support for an optimized data transfer facility by using Netezza external tables feature.<\/p>\n Every map task of the Netezza connector\u2019s import job works on the subset of the Netezza partitions and creates and uses the external table for data transport. For export jobs, it will use an external table for pushing data fast into the Netezza system.<\/p>\n Example of Sqoop import by using the Netezza external table feature.<\/strong><\/p>\n Example of Sqoop export with tab as the field terminator character.<\/strong><\/p>\n The Data Connector for Hadoop and Oracle is now included in Apache Sqoop. We can enable it by specifying the argument –direct for our import\/export job.<\/p>\n The Data Connector for Oracle and Hadoop accepts the responsibility for following Sqoop Job types:<\/p>\n The Data Connector for Oracle and Hadoop doesn\u2019t accept the responsibility for the other Sqoop job types like eval jobs etc.<\/p>\n This Connector accepts the responsibility for the Sqoop Jobs having the following attributes:<\/p>\n The Sqoop parameter –connect<\/strong> parameter defines the Oracle instance or the Oracle RAC to be connected to. This parameter is required with all the Sqoop import and export commands.<\/p>\n The Data Connector for Oracle and Hadoop requires the associated connection string to be in a specific format which is dependent on whether the Oracle SID, Service or TNS name is defined or not.<\/p>\n We can use the TNS name based URL scheme for enabling the authentication using the Oracle wallets.<\/p>\n The driver argument in JDBC connection specifies the JDBC driver class that Sqoop must use for connecting to the database.<\/p>\n Syntax:<\/strong><\/p>\n The syntax which we can use depending on the type of the database we want to connect to:<\/p>\n Microsoft SQL Server:<\/strong> –driver com.microsoft.sqlserver.jdbc.SQLServerDriver The connect argument in Apache Sqoop specifies the JDBC connection string that Apache Sqoop must use in order to connect to the database. The connection string must be based on the driver that we define in the driver argument.<\/p>\n Syntax:<\/strong><\/p>\n The Syntax which we can use depending on the type of the database we want to connect to:<\/p>\n Microsoft SQL Server:<\/strong> –connect jdbc:sqlserver:\/\/<host_name>:<port or named_instance>;databaseName=<database_name> The article had explained what are Sqoop drivers and connectors. In this article, you had also explored the different connectors shipped with Sqoop for connecting to various databases.<\/p>\n You had learned about the connectors like MySQL JDBC connector, MySQL Direct connector, Microsoft SQL connector, PostgreSQL connector, Netezza connector, and some more.<\/p>\n The article had also enlisted the drivers and connectors argument which we can use depending on the database we want to connect to.<\/p>\n","protected":false},"excerpt":{"rendered":" Sqoop is a tool which is designed for the data transfers between the RDBMS and the Hadoop ecosystem. One of the major advantages of Apache Sqoop is that it is easy to use, and...<\/p>\n","protected":false},"author":1,"featured_media":79678,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3163],"tags":[3199,3200],"class_list":["post-79637","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sqoop","tag-connectors-and-drivers-in-apache-sqoop","tag-sqoop-connectors-and-drivers"],"yoast_head":"\n$ sqoop import --connect jdbc:mysql:\/\/localhost\/demodb --table EMPLOYEES \\\n --direct\n<\/pre>\n
\n
3. Microsoft SQL Connector<\/h4>\n
4. PostgreSQL Connector<\/h4>\n
jdbc:postgresql:\/\/<HOST>:<PORT>\/<DATABASE_NAME>\n<\/pre>\n
jdbc:postgresql:\/\/my_postgres_server_hostname:5432\/my_database_name\n<\/pre>\n
\n\n
\n Argument<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n –schema <name><\/span><\/td>\n This argument specifies the Scheme name that Apache Sqoop should use. The default is “public”.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n 5. PostgreSQL Direct Connector<\/h4>\n
Argument\nDescription\n--boolean-true-string <str>\nSpecify the string to be used for encoding the true value of the boolean columns. The default is \"TRUE\".\n--boolean-false-string <str>\nSpecify the string to be used for encoding the false value of the boolean columns. The default value is \"FALSE\".\n<\/pre>\n
\n
6. pg_bulkload connector<\/h4>\n
\n
\n
7. Netezza Connector<\/h4>\n
$ sqoop import \\\n --direct \\\n --connect jdbc:netezza:\/\/nzhost:5480\/sqoop \\\n --table nztable \\\n --username nzuser \\\n --password nzpass \\\n --target-dir hdfsdir\n<\/pre>\n
$ sqoop export \\\n --direct \\\n --connect jdbc:netezza:\/\/nzhost:5480\/sqoop \\\n --table nztable \\\n --username nzuser \\\n --password nzpass \\\n --export-dir hdfsdir \\\n --input-fields-terminated-by \"\\t\"\n<\/pre>\n
8. Data Connector for Oracle and Hadoop<\/h4>\n
\n
\n
--connect jdbc:oracle:thin:@OracleServer:OraclePort:OracleSID\n--connect jdbc:oracle:thin:@\/\/OracleServer:OraclePort\/OracleService\n--connect jdbc:oracle:thin:@TNSName\n<\/pre>\n
Driver Sqoop arguments in the JDBC connection for various Databases<\/h3>\n
--driver <JDBC driver class>\n<\/pre>\n
\nGreenplum:<\/strong> –driver org.postgresql.Driver
\nNetezza:<\/strong> –driver org.netezza.Driver
\nOracle:<\/strong> –driver oracle.jdbc.driver.OracleDriver
\nIBM DB2:<\/strong> –driver com.ibm.db2.jcc.DB2Driver
\nIBM DB2 z\/OS:<\/strong> –driver com.ibm.db2.jcc.DB2Driver
\nTeradata:<\/strong> –driver com.teradata.jdbc.TeraDriver
\nAurora:<\/strong> –driver com.mysql.jdbc.Driver<\/p>\nConnect Sqoop arguments in the JDBC connection for various Databases<\/h3>\n
--connect <JDBC connection string>\n<\/pre>\n
\nGreenplum:<\/strong> –connect jdbc:postgresql:\/\/<host_name>:<port>\/<database_name>
\nNetezza:<\/strong> –connect “jdbc:netezza:\/\/<database_server_name>:<port>\/<database_name>;schema=<schema_name>”
\nOracle:<\/strong> –connect jdbc:oracle:thin:@<database_host_name>:<database_port>:<database_SID>
\nIBM DB2:<\/strong> –connect jdbc:db2:\/\/<host_name>:<port>\/<database_name>
\nIBM DB2 z\/OS:<\/strong> –connect jdbc:db2:\/\/<host_name>:<port>\/<database_name>
\nTeradata:<\/strong> –connect jdbc:teradata:\/\/<host_name>\/database=<database_name>
\nAurora:<\/strong> –connect “jdbc:mysql:\/\/<host_name>:<port>\/<schema_name>”<\/p>\nSummary<\/h3>\n