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.