sqoop - Using Sqoop with MySQL as metastore - apache sqoop - sqoop tutorial - sqoop hadoop



How to use Sqoop with MySQL as metastore?

  • In order to set up MySQL to use with SQOOP:
  • On the SQOOP Server host, we need to install the connector.

RHEL/CentOS/Oracle Linux

  • The syntax which is given below is done for RHEL/CentOS/Oracle Linux
yum install mysql-connector-java
Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team
Sqoop related tags : sqoop import , sqoop interview questions , sqoop export , sqoop commands , sqoop user guide , sqoop documentation

SLES

  • The syntax which is given below is done for SLES
  zypper install mysql-connector-java
Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team
  • We need to confirm that .jar is in the Java share directory.
ls /usr/share/java/mysql-connector-java.jar
Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team
  • We need to make sure the .jar file has the appropriate permissions to install the connector - 644.

Create a user for SQOOP and grant it permissions

  • We need to create a user for Sqoop and grant it permissions using the MySQL database admin utility:
  # mysql -u root -p  
CREATE USER '<SQOOPUSER>'@'%' IDENTIFIED BY '<SQOOPPASSWORD>';  
GRANT ALL PRIVILEGES ON *.* TO '<SQOOPUSER>'@'%';  
CREATE USER '<SQOOPUSER>'@'localhost' IDENTIFIED BY '<SQOOPPASSWORD>';  
GRANT ALL PRIVILEGES ON *.* TO '<SQOOPUSER>'@'localhost';  
CREATE USER '<SQOOPUSER>'@'<SQOOPSERVERFQDN>' IDENTIFIED BY '<SQOOPPASSWORD>';  
GRANT ALL PRIVILEGES ON *.* TO '<SQOOPUSER>'@'<SQOOPSERVERFQDN>';  
FLUSH PRIVILEGES;
Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team
  • Where the <SQOOPUSER> is the SQOOP user name, <SQOOPPASSWORD> is the SQOOP user password and <SQOOPSERVERFQDN> is given as the Fully Qualified Domain Name of the SQOOP Server host.
  • We need to configure the sqoop-site.xml to create the sqoop database and we need to load the SQOOP Server database schema.
<configuration>  
<property>  
<name>sqoop.metastore.client.enable.autoconnect</name>  
<value>true</value>  
</property>  
<property>  
<name>sqoop.metastore.client.autoconnect.url</name>  <value>jdbc:mysql://<<MYSQLHOSTNAME>>/sqoop?createDatabaseIfNotExist=true</value>  </property>  
<property>  
<name>sqoop.metastore.client.autoconnect.username</name>  
<value>$$SQOOPUSER$$</value>  
</property>  
<property>  
<name>sqoop.metastore.client.autoconnect.password</name>  
<value>$$$SQOOPPASSWORD$$$</value>  
</property>  
<property>  
<name>sqoop.metastore.client.record.password</name>  
<value>true</value> 
</property>  
<property>  
<name>sqoop.metastore.server.location</name>  
<value>/usr/lib/sqoop/metastore/</value>  
</property>  
<property>  
<name>sqoop.metastore.server.port</name>  
<value>16000</value>  
</property>  
</configuration>
Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team
  • We need to execute the following command to create the initial database and the tables which is given.
sqoop job --list
Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team
  • If we get any error or an exception which is given then we must pre-load the SQOOP tables with the mandatory values.
mysql -u <SQOOPUSER> -p   USE <SQOOPDATABASE>;
  -- Inserted the following row  
INSERT INTO SQOOP_ROOT   VALUES(     NULL,     'sqoop.hsqldb.job.storage.version',     '0'  );
Click "Copy code" button to copy into clipboard - By wikitechy - sqoop tutorial - team
  • Where the <SQOOPUSER> is the SQOOP user name and <SQOOPDATABASE> is the SQOOP database name.
  • Once all the necessary sqoop tables are created, then the Sqoop job will use the meta store for the SQOOP job execution.

Related Searches to Using Sqoop with MySQL as metastore