how to connect onprem hadoop hive to azure databricks

Question

how to connect onprem hadoop hive to azure databricks

Prasad Sandu 0

Smaran Thoomu 24,750 Reputation points Microsoft External Staff Moderator

2025-07-08T11:33:08.3766667+00:00

Prasad Sandu Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Smaran Thoomu 24,750 Reputation points Microsoft External Staff Moderator

2025-07-09T07:54:23.27+00:00

Prasad Sandu Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

Your answer

Smaran Thoomu 24,750 Reputation points Microsoft External Staff Moderator

2025-07-08T11:33:08.3766667+00:00

Prasad Sandu Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Smaran Thoomu 24,750 Reputation points Microsoft External Staff Moderator

2025-07-09T07:54:23.27+00:00

Prasad Sandu Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Answer 1

Smaran Thoomu 24,750 Microsoft External Staff Moderator

Hi Prasad Sandu
To connect your on-premises Hadoop Hive to Azure Databricks, you can follow one of these approaches depending on your setup and security constraints:

Option 1: Connect via JDBC (recommended for read access)

Install Hive JDBC Driver on your Databricks cluster:

Upload the Hive JDBC JAR (e.g., hive-jdbc-uber.jar) to DBFS or install it using %sh or cluster libraries.

Example install using DBFS:

  %sh wget https://repo1.maven.org/maven2/org/apache/hive/hive-jdbc/3.1.2/hive-jdbc-3.1.2-standalone.jar -P /databricks/jars/

Establish JDBC Connection: Use the following Spark read command in a notebook:

jdbc_url = "jdbc:hive2://<onprem-host>:10000/default"
df = spark.read.format("jdbc") \
    .option("url", jdbc_url) \
    .option("driver", "org.apache.hive.jdbc.HiveDriver") \
    .option("dbtable", "<your_table>") \     .load() 
df.show()

Network Requirements:

Ensure that Azure Databricks can reach your on-prem Hive server.
Typically done using Azure VPN Gateway or ExpressRoute, and a self-hosted integration runtime or private endpoint setup.

Option 2: Access via Azure Data Factory + SHIR

Use ADF with a Self-Hosted Integration Runtime (SHIR) on your on-prem environment to:

Ingest data from Hive to Azure (e.g., Blob or ADLS).
Then load into Databricks for processing.

Option 3: Use DistCp or Hive export

If JDBC is not ideal, you can:

Export Hive data to HDFS/CSV/parquet.
Use DistCp or AzCopy to move it to Azure Data Lake Storage.
Load it into Databricks via Delta Lake or Spark read.

Notes:

JDBC is best for small to medium data pulls or live lookups.
For large-scale data, it's more efficient to stage in storage (ADLS) and process via Databricks.

For more information you can refer the below articles:

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

Prasad Sandu 0 Reputation points

2025-07-06T13:48:11.5566667+00:00

How to do this access connector of databricks to onprem hadoop hive
Smaran Thoomu 24,750 Reputation points Microsoft External Staff Moderator

2025-07-07T06:06:06.71+00:00
Hi @Prasad Sandu

Thanks for your follow-up question.

Just to clarify - the Access Connector in Azure is used to connect Databricks to Azure Data Lake (ADLS) using managed identity. It does not work for connecting to on-prem Hadoop Hive.

To connect your on-prem Hadoop Hive to Azure Databricks, you’ll need to:

Set up network access Use VPN Gateway or ExpressRoute so your Databricks workspace can talk to your on-prem Hive server. (Your Databricks workspace should be deployed with VNet injection for this.)

Install the Hive JDBC driver Upload the JDBC .jar file to your cluster, for example:
%sh wget https://repo1.maven.org/maven2/org/apache/hive/hive-jdbc/3.1.2/hive-jdbc-3.1.2-standalone.jar -P /databricks/jars/

Connect using Spark JDBC Then use a code snippet like this in your notebook:
jdbc_url = "jdbc:hive2://<onprem-host>:10000/default" df = spark.read.format("jdbc") \ .option("url", jdbc_url) \ .option("driver", "org.apache.hive.jdbc.HiveDriver") \ .option("dbtable", "<your_table>") \ .load() df.show()

If you’re facing any specific issue with one of these steps, feel free to share and I’ll be happy to guide further.

Also, if the previous answer helped or makes more sense now, I’d really appreciate it if you could resubmit the survey response to reflect that.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Prasad Sandu 0 Reputation points

2025-07-07T12:14:26.3+00:00
here's code without vpn setup to connect onprem dadoop hive to azure databricks, I did not understand this, pls advise,is this correct way

org.apache.spark.sql.jdbc.MySQLDialect

org.apache.spark.sql.jdbc.HiveDialect

org.apache.spark.sql.jdbc.PostgresDialect

org.apache.spark.sql.jdbc.DB2Dialect

org.apache.spark.sql.jdbc.MsSqlServerDialect

/*

Licensed to the Apache Software Foundation (ASF) under one or more

contributor license agreements. See the NOTICE file distributed with

this work for additional information regarding copyright ownership.

The ASF licenses this file to You under the Apache License, Version 2.0

(the "License"); you may not use this file except in compliance with

the License. You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software

distributed under the License is distributed on an "AS IS" BASIS,

WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.

See the License for the specific language governing permissions and

limitations under the License.

*/

package org.apache.spark.sql.jdbc

import java.util.Locale

private case class HiveDialect() extends JdbcDialect {

override def canHandle(url: String): Boolean =

url.toLowerCase(Locale.ROOT).startsWith("jdbc:hive2")

override def quoteIdentifier(colName: String): String = {

s"`$colName`"

}

}

test("Default jdbc dialect registration") {

assert(JdbcDialects.get("jdbc:mysql://127.0.0.1/db") === MySQLDialect()) assert(JdbcDialects.get("jdbc:hive2://127.0.0.1/db") === HiveDialect()) assert(JdbcDialects.get("jdbc:postgresql://127.0.0.1/db") === PostgresDialect()) assert(JdbcDialects.get("jdbc:db2://127.0.0.1/db") === DB2Dialect()) assert(JdbcDialects.get("jdbc:sqlserver://127.0.0.1/db") === MsSqlServerDialect())
Smaran Thoomu 24,750 Reputation points Microsoft External Staff Moderator

2025-07-07T13:19:22.44+00:00
Prasad Sandu The code you posted looks like part of the Spark source code related to JDBC dialects (like MySQLDialect, HiveDialect, etc.) - but this is not a working method to connect Databricks directly to your on-prem Hive.

Why this won't work:

That code is part of how Spark internally maps JDBC URLs to the correct dialect - it's not meant to be used directly by users.

Also, without a VPN or private network setup, your Databricks workspace will not be able to reach your on-prem Hive server, even if the JDBC URL is correct.

What you need instead:

To securely and successfully connect Azure Databricks to your on-prem Hive, please follow this supported approach:

Set up network access Use VPN Gateway or ExpressRoute so your Databricks workspace can connect to your on-prem environment. Ensure your workspace is deployed with VNet injection.

Install the Hive JDBC driver on your cluster (e.g.):
%sh wget https://repo1.maven.org/maven2/org/apache/hive/hive-jdbc/3.1.2/hive-jdbc-3.1.2-standalone.jar -P /databricks/jars/

Use this kind of code in a notebook:
jdbc_url = "jdbc:hive2://<onprem-hive-host>:10000/default" df = spark.read.format("jdbc") \ .option("url", jdbc_url) \ .option("driver", "org.apache.hive.jdbc.HiveDriver") \ .option("dbtable", "your_table") \ .load() df.show()

Also, if the earlier guidance now makes more sense, I’d appreciate it if you could consider updating the survey feedback. 😊

Happy to assist further!

Share via

how to connect onprem hadoop hive to azure databricks

1 answer

Your answer