I have followed the below steps to read the hive table from spark side with credential store:
FROM MYSQL
CREATE USER IF NOT EXISTS 'gopi'@'%' IDENTIFIED BY 'gopi';
GRANT ALL PRIVILEGES ON * . * TO 'gopi'@'%';
FLUSH PRIVILEGES;
create database if not exists test;
use test;
CREATE TABLE test.EMPLOYEE(
id INT,
name varchar(255),
salary DECIMAL,
dob DATE NOT NULL DEFAULT '2021-05-01',
doj TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP(),
PRIMARY KEY (id)
);
INSERT INTO test.EMPLOYEE (id, name, salary, dob, doj) VALUES (1, "gopi", 10000.00, '1988-06-01', '2020-03-16 09:00:01.000000');
INSERT INTO test.EMPLOYEE (id, name, salary,dob) VALUES (2, "Nishanth", 50000.00, '2018-05-29');
INSERT INTO test.EMPLOYEE (id, name, salary) VALUES (3, "Raja", 30000.00);
Create credential store:
sudo -u hive hadoop credential create gopi_user.password -v gopi -provider jceks://hdfs/user/hive/gopi_user.jceks
hadoop credential list -provider jceks://hdfs/user/hive/gopi_user.jceks
sudo -u hive hdfs dfs -chmod 400 /user/hive/gopi_user.jceks
FROM HIVE
USE db_test;
drop table if exists db_test.employee2;
CREATE EXTERNAL TABLE db_test.employee2(
id INT,
name STRING,
salary DOUBLE,
dob DATE,
doj TIMESTAMP
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
"hive.sql.database.type" = "MYSQL",
"hive.sql.jdbc.driver" = "com.mysql.cj.jdbc.Driver",
"hive.sql.jdbc.url" = "jdbc:mysql://ccycloud-1.nightly7x-us-gr.root.comops.site:3306/test",
"hive.sql.dbcp.username" = "gopi",
"hive.sql.dbcp.password.keystore" ="jceks://hdfs/user/hive/gopi_user.jceks",
"hive.sql.dbcp.password.key" = "gopi_user.password",
"hive.sql.query" = "select * from test.EMPLOYEE"
);
FROM SPARK
sudo -u hive spark-shell \
--jars /opt/cloudera/parcels/CDH/jars/hive-jdbc-handler-3.1.3000.7.2.18.0-622.jar,/usr/share/java/mysql-connector-java.jar
scala> spark.sql("SELECT * FROM db_test.employee1").show()
+---+--------+-------+----------+-------------------+
| id| name| salary| dob| doj|
+---+--------+-------+----------+-------------------+
| 1| gopi|10000.0|1988-06-01|2020-03-16 09:00:01|
| 2|Nishanth|50000.0|2018-05-29|2024-02-27 10:39:22|
| 3| Raja|30000.0|2021-05-01|2024-02-27 10:39:30|
+---+--------+-------+----------+-------------------+
scala> spark.sql("SELECT * FROM db_test.employee2").show()
+---+--------+-------+----------+-------------------+
| id| name| salary| dob| doj|
+---+--------+-------+----------+-------------------+
| 1| gopi|10000.0|1988-06-01|2020-03-16 09:00:01|
| 2|Nishanth|50000.0|2018-05-29|2024-02-27 10:39:22|
| 3| Raja|30000.0|2021-05-01|2024-02-27 10:39:30|
+---+--------+-------+----------+-------------------+
