Connecting to Hive using local spark jdbc

@pranav007 Welcome to the Cloudera Community!

To help you get the best possible solution, I have tagged our Hive expert @cravani  who may be able to assist you further.

Please keep us updated on your post, and we hope you find a satisfactory solution to your query.

You seem to be using wrong driver to connect to HiveServer2. MySQL JDBC driver does not work to connect to HS2.

You would need Apache Hive JDBC Driver or Cloudera Hive JDBC Driver to connect to Hive. You can download latest Apache Hive JDBC Driver from: https://repo1.maven.org/maven2/org/apache/hive/hive-jdbc/4.0.0-alpha-2/

File: hive-jdbc-4.0.0-alpha-2-standalone.jar 

Add the same jar to your spark.jars.

I have the same issue. Any tips for solution?

@Bharati @Shmoo Do you have any insights here? Thanks!

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|
+---+--------+-------+----------+-------------------+

Any tips for solution?