Connecting Apache Zeppelin to your SQL Server

access_time 3 years ago visibility2255 comment 7

This page demonstrates the steps you need to connect to SQL Server in Zeppelin. There are many ways to implement this, for example SQL Server interpreters in GitHub. In this page, I am going to use the JDBC driver to connect to SQL Server instead of using third party interpreters.

For authentication, I am using database login (SQL Server authentication) instead of Windows login since my machine is not part of any domain. If you want to setup Kerberos authentication, please refer the following link about how to configure ktab file name and SPN:

Java Kerberos Authentication Configuration Sample & SQL Server Connection Practice

Prerequisites

If you have not installed Zeppelin, follow the link below to setup your local instance:

Install Zeppelin 0.7.3 in Windows

Java JDK is also required, which is included as part of the above installation guide.

Connect to SQL Server

Setup SQL Server account

In my machine, I’ve setup one SQL Server login in my local SQL Server instance:

Login: zeppelin

Password: zeppelin

The above credential is going to be used when setting up interpreter.

Setup interpreter

In Zeppelin website, click Interpreter menu.

image

Click create button.

Input the following:

  • Interpreter Name: tsql
  • Interpreter group: jdbc

image

Setup the following properties:

  • default.driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
  • default.password: zeppelin
  • default.url: jdbc:sqlserver://localhost
  • default.user : zeppelin
  • zeppelin.jdbc.auth.type: SIMPLE
  • artifact: com.microsoft.sqlserver:mssql-jdbc:6.5.1.jre8-preview

image
You can change the property values to your own environment setup.

For the artifact setting, you can use local lib file path or using groupid:artifact id:version. In my case, I am using the following SQL Server JDBC library.

https://search.maven.org/#artifactdetails%7Ccom.microsoft.sqlserver%7Cmssql-jdbc%7C6.5.1.jre8-preview%7Cjar

Click Save button to save this new interpreter.

Create note

Now you can create a new note with the new interpreter (%tsql).

The following is a sample code I use:

%tsql

select * from sys.all_objects where type='U'

The screenshot below shows the result:

image

Summary

It’s very easy to use JDBC to connect to SQL Server or any other JDBC compatible databases, for example, Oracle and Teradata.

Once you can connect to the database, you can then do analytics easily in Zeppelin.

info Last modified by Raymond at 13 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Want to publish your article on Kontext?

Learn more

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer SQL Server

visibility 261
thumb_up 0
access_time 3 years ago

If you have used Visual Studio Code, you probably will also enjoy a new client tool SQL Operations Studio. Currently it is available as preview version and not formally released yet. SQL Operations Studio is a data management tool that enables working with SQL Server, Azure SQL DB and SQL DW from ...

local_offer python local_offer SQL Server local_offer python-database

visibility 2187
thumb_up 0
access_time 8 months ago

Python JayDeBeApi module allows you to connect from Python to databases using Java JDBC drivers.

Azure SQL Database Automated Backup Strategy

local_offer Azure local_offer SQL Server

visibility 237
thumb_up 0
access_time 6 months ago

When designing the architecture of Kontext platform, Azure SQL Database is chosen as the storage for relational data. TDE and other advanced security features are always enabled to protect the database. Backup plans are also employed to ensure I can always restore the database for as point of time ...

About column

Blog posts about Zeppelin

rss_feed Subscribe RSS