How to make Mysql Database
as Hive’s instance: Install Mysql if not available: brew update brew doctor brew upgrade brew install
mysql mysql.server
restart mysql_secure_installation login to mysql
-> mysql –u root –p Enter password: Happy Mysql
learning…. Mysql is
already installed on Hortonworks sandbox. Steps: Confirm with mysql –u root –p Import an already
available database into Mysql: Ref: https://dev.mysql.com/doc/employee/en/employees-installation.html shell> tar -xjf
$HOME/Downloads/employees_db-full-1.0.6.tar.bz2 shell> cd employees_db/ shell> mysql -t <
employees.sql With this installation of
employee db in mysql is complete. Configuration
of Mysql Instance with Hive: From
HIVE create Mysql metastore [root@host]#
mysqladmin -u root create hivedb mysql>
USE hivedb; mysql>
CREATE USER 'hive'@'localhost' IDENTIFIED BY 'hive'; mysql>
GRANT ALL PRIVILEGES ON *.* TO 'hive'@'localhost'; With
this we confirm that mysql database is the Hive’s new metastore. Suppose to perform a full import of the ‘employees’
and ‘salaries’ tables into HDP: Tables created in
Hive Create database employees; Use employees; CREATE EXTERNAL TABLE IF NOT EXISTS employees ( emp_no INT, birth_date DATE, first_name
VARCHAR(14), last_name
VARCHAR(16), gender STRING, hire_date DATE ) STORED AS TEXTFILE; CREATE TABLE IF NOT EXISTS salaries ( emp_no INT, salary INT, from_date DATE, to_date DATE ) STORED AS TEXTFILE; sqoop import --connect
jdbc:mysql://172.16.16.128:3306/employees --username=hive --password=hive
--driver com.mysql.jdbc.Driver --table=employees --hive-import
--hive-table=empl.employees --target-dir=wp_users_import –direct sqoop import --connect
jdbc:mysql://172.16.16.128:3306/employees --username=hive --password=hive
--driver com.mysql.jdbc.Driver --table=employees --hive-import
--hive-table=empl.salaries --target-dir=wp_users_import –direct Suppose we need to perform some cleansing of data
using Regex expressions of Hive: use empl; drop table
empl.empl_clean; show tables; create table
empl.empl_clean(emp_no INT, birth_date STRING, first_name STRING, last_name STRING,gender
STRING, hire_date STRING ); insert overwrite table
empl.empl_clean SELECT regexp_replace(employees.emp_no,
'\t', '')emp_no, regexp_replace(employees.birth_date,
'\t', '')birth_date, regexp_replace(employees.first_name,
'\t', '')first_name, regexp_replace(employees.last_name,
'\t', '')last_name, regexp_replace(employees.gender,
'\t', '')gender, regexp_replace(employees.hire_date,
'\t', '')hire_date from empl.employees; select * from
empl.empl_clean limit 100; Cleansing the
salaries table: use empl; drop table
empl.salary_clean; create table
empl.salary_clean(emp_no INT,salary INT, from_date STRING, to_date STRING); insert overwrite table
empl.salary_clean SELECT regexp_replace(salaries.emp_no,
'\t', '')emp_no, regexp_replace(salaries.salary,
'\t', '')salary, regexp_replace(salaries.from_date,
'\t', '')from_date, regexp_replace(salaries.to_date,
'\t', '')to_date from empl.salaries; select * from
empl.salary_clean limit 100; Happy Learning….
... View more
