• close

Kellogg Data Center


Access

The Kellogg Database Server (KDC02) is available to all Kellogg researchers (faculty, PhD students, post-docs). Kellogg researchers can request access to KDC02 by emailing Kellogg Research Support.


Overview

The Kellogg SQL Database Server (KDC02) offers an enterprise-level Microsoft SQL Server database engine for storing and querying vast amounts of structured relational data.

Queries to databases on this platform may be executed directly from Python, R, STATA, SAS, or MATLAB code and results retrieved directly to local workspace. Connections to KDC02 via WiFi or off-campus networks using these software installed on your personal computer require a connection to the NU VPN. The database server can also be accessed from client software on Windows operating systems (i.e. SQL Server Management Studio) and Linux/macOS (i.e. DBeaver).


Connection Details

Server Name: kdc02.kellogg.northwestern.edu



Review connection methods below:
Stata

Connecting from Windows

Prior to running your code, the Stata executable should be opened either by right-clicking the application and selecting "Run as different user" or using the "runas" command from the command line (ex. runas /netonly /user:KELLOGG\YOUR_NETID "c:\path\Stata.exe").

The following code will open connection to KDWH02 database on KDC02 server and execute enclosed query code. The results will be downloaded to STATA and saved as local variables:


clear
odbc load, connectionstring("DRIVER={SQL Server};SERVER=kdc02\kdwh02;DATABASE=RAC;") exec("select * from Exam where year(datetaken)=2006")

This code may be adjusted for any other database (replace RAC with your name). /

Below version allows braking line in SQL section.


clear
#delimit ;
odbc load, connectionstring("DRIVER={SQL Server};SERVER=kdc02\kdwh02;DATABASE=Dataquick;")
exec("
select top 100 * from History
");
#delimit cr

Connecting from Linux/macOS

Execute the freetds_config script to download and build FreeTDS.

Connecting from KLC

KLC users enter the following:

$. /kellogg/bin/freetds_config

Set the "ODBCSYSINI" environmental variable, then launch Stata and establish an ODBC connection:

$ export ODBCSYSINI=~/.odbc/
$ module load stata/15  #compatible with Stata 15+ on KLC
$ xstata-mp
. set odbcmgr unixodbc
. odbc load, dsn("kdc-tds") user("kellogg\YOUR_NETID") password("PASSWORD_HERE") exec("SELECT TOP 1000 * FROM Census.dbo.CensusTract")

Python

Connecting from Windows

Prior to running your code, the Python executable should be opened either by right-clicking the application and selecting "Run as different user" or using the "runas" command from the command line (ex. runas /netonly /user:KELLOGG\YOUR_NETID "c:\path\python.exe").

import pyodbc
 
cnxn = pyodbc.connect(
    r'Trusted_Connection=yes;'
    r'DRIVER={SQL Server};'
    r'SERVER=kdc02.kellogg.northwestern.edu;'
    r'PORT=1443;'
    r'DATABASE=CME;'
    )

cursor = cnxn.cursor()
cursor.execute("select * from information_schema.tables")
row = cursor.fetchone()
print(row)

Connecting from Linux/macOS

Execute the freetds_config script to download and build FreeTDS.

Connecting from KLC

KLC users enter the following:

$. /kellogg/bin/freetds_config

Set the "ODBCSYSINI" environmental variable, then launch python and establish an ODBC connection:

$ export ODBCSYSINI=~/.odbc/
$ python
import pyodbc
USER='kellogg\\YOUR_NETID'
PASSWORD = 'PASSWORD_HERE'
cnxn = pyodbc.connect('DSN=kdc-tds;Database=CME;UID='+USER+';PWD='+PASSWORD)
cursor = cnxn.cursor()
cursor.execute("select * from information_schema.tables")
row = cursor.fetchone()
print(row)

MATLAB

Connecting from Windows

Configure Microsoft SQL Server JDBC for Windows.

Prior to running your code, the MATLAB executable should be opened either by right-clicking the application and selecting "Run as different user" or using the "runas" command from the command line (ex. runas /netonly /user:KELLOGG\YOUR_NETID "c:\path\matlab.exe").


% Connect to KDC02 database server and use CME database
con = database('CME','','','com.microsoft.sqlserver.jdbc.SQLServerDriver','jdbc:sqlserver://kdc02.kellogg.northwestern.edu\kdwh02;databaseName=CME;integratedSecurity=true;');

% Fetch data from the database.
e = exec(con,'select top 100 * from dbo.T_FEOD');
x = fetch(e);

%result will be in data variable
data = x.Data;

% Close the connection.
close(con);

Connecting from KLC/Linux/macOS

KLC: include the following line in ~/.matlab/VERSION/javaclasspath.txt:


/kellogg/bin/jtds-1.3.1.jar

Linux: configure Microsoft SQL Server JDBC for Linux.

Mac: configure Microsoft SQL Server JDBC for macOS.

Within MATLAB:


>> password=input('Enter your NetId password: ', 's');
>> # to protect your password, right click and delete this entry from the command history

>> # the following example uses the Census database:
>> conn=database('Census', 'YOUR_NETID', password, 'net.sourceforge.jtds.jdbc.Driver', 'jdbc:jtds:sqlserver://kdc01.kellogg.northwestern.edu:1433;databaseName=Census;domain=kellogg;useNTLMv2=true;')
>> cursor = exec(conn, 'SELECT TOP 1000 * FROM Census.dbo.CensusTract')
>> cursor = fetch(cursor);
>> fetch(cursor).Data

R

Connecting from Windows

Prior to running your code, the R executable should be opened either by right-clicking the application and selecting "Run as different user" or using the "runas" command from the command line (ex. runas /netonly /user:KELLOGG\YOUR_NETID "c:\path\R.exe").

install.packages("RODBC")
library(RODBC)
dbhandle <- odbcDriverConnect('driver={SQL Server};server=kdc02\\kdwh02;database=CME;trusted_connection=true')
res <- sqlQuery(dbhandle, 'select * from information_schema.tables')
odbcClose(dbhandle)

Connecting from Linux/macOS

Execute the freetds_config script to download and build FreeTDS.

Connecting from KLC

KLC users enter the following:

$. /kellogg/bin/freetds_config

Set the "ODBCSYSINI" environmental variable, then launch R and establish an ODBC connection:

$ export ODBCSYSINI=~/.odbc/
$ R
 install.packages("RODBC")
 library(RODBC)
 conn <- odbcConnect("kdc-tds", uid="kellogg\\YOUR_NETID", pwd="YOUR_PASSWORD")
 res <- sqlQuery(conn, "SELECT TOP 10 * FROM Census.dbo.CensusTract")
odbcClose(conn)