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

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.

Have Julia installed on your machine

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.
   
   
   

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

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

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

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.