Connecting to SQL Server, Hive etc., from Julia using ODBC drivers

Introduction

In this tutorial we will be walking you through how you can connect to your databases whether it might be Relational – SQL Server, Oracle, DB2, MySQL, PostgreSQL or Big Data – Hive, Apache Spark, or SaaS – Salesforce, Eloqua etc., using an ODBC driver. As an example, we will show you how you can connect to SQL Server using DataDirect ODBC driver.

Pre-requisites

Have Julia installed on your machine

Install DataDirect SQL Server ODBC driver

  1. Go to our website and download the 64-bit ODBC driver for the MS SQL Server database.
  2. Extract the package and install the ODBC driver by running the installer.
  3. Now Open ODBC Administrator (64-bit) and Click on Add, to add a new datasource.

    create dsn

  4. Choose “DataDirect 8.0 SQL Server Wire Protocol” as your driver

    Choose sql server
Now fill in the connection information such as hostname, port, database name and click on Test Connect to verify connection details.

Querying SQL Server ODBC from Julia

  1. Before you start querying SQL Server via ODBC, you would have to install ODBC package for Julia. To install the ODBC package, run the following command in Julia terminal.

     

    julia> Pkg.add("ODBC")


  2. Let’s start with the basics. To list all the ODBC drivers installed on your machine, you can run the below command.

     

    julia> ODBC.drivers()


  3. To List all the data sources, you have configured in ODBC Administrator (DSN) or your odbc.ini file on Linux, you can run the following command.

     

    julia> ODBC.dsns()


     

  4. Now to connect to the DSN you have created in the above chapter, run the below command which returns a connection object.

     

    db = ODBC.DSN("SQLServer", "username", "password")

     

  5. Now you can use this connection object to query your tables in SQL Server database.

     

    df = ODBC.query(db, "SELECT * FROM Chinook.dbo.Artist")
    println(df)

     

  6. You can also export the result set to a CSV file, by running the following code.


    csv = ODBC.query(db, "select * from Chinook.dbo.Artist", CSV.Sink, "Artists.csv")



  7. You can create a Table, by running the following code.


    ODBC.execute!(db, "CREATE TABLE [Chinook].[dbo].[Artist2]([ID] [INT] NOT NULL,[Name] [nvarchar](120) NULL)")


  8. And then insert data in to the table using the below code


    ODBC.execute!(db, "INSERT INTO Artist2 VALUES (472, 'Saikrishna Bobba')");


  9. After you are done, you can now disconnect by running the below command.
    ODBC.disconnect!(db)


     

     

  10. Here is the complete program for your reference
using ODBC
using CSV
 
 
#list all drivers
println(ODBC.drivers())
 
#list all ODBC DSNS
println(ODBC.dsns())
 
#Connect to ODBC DSN
db = ODBC.DSN("SQLServer", "username", "password")
 
#Query your Database, return result as dataframe
df = ODBC.query(db, "SELECT * FROM Chinook.dbo.Artist")
println(df)
 
# Query your database, return result as CSV file
csv = ODBC.query(db, "select * from Chinook.dbo.Artist", CSV.Sink, "Artists.csv")
 
#Create Table
ODBC.execute!(db, "CREATE TABLE [Chinook].[dbo].[Artist2]([ID] [INT] NOT NULL,[Name] [nvarchar](120) NULL)")
 
#Load data
ODBC.execute!(db, "INSERT INTO Artist2 VALUES (472, 'Saikrishna Bobba')");
 
 
#disconnect
ODBC.disconnect!(db)


Feel free to try any of our ODBC drivers to connect to your databases from Julia. If you have any questions or issues, please contact us.

ODBC TUTORIAL

Connecting to SQL Server, Hive etc., from Julia using ODBC drivers

DataDirect
connectors

Connect any data source to any application

DataDirect
Tutorials

Discussions, tips and tricks for
DataDirect Connect drivers