Tags

,

Accessing Blackboard Data through the web UI provided by Snowflake is very useful.  You can save your scripts and explore the environment.  However, sometimes you may find that a thick client connection is also needed.  Perhaps you want to automate scripts that download data or integrate an application.  You can do that via ODBC or JODBC.  Snowflake also provides Python Components, Node.js Driver, Spark Connector, and a Command Line Client (CLI snowsql).

In this article I’ll demonstrate connection via JODBC to SQLWorkbench/J.  There is a variety of tools you could use for SQL access such as Oracle SQLDeveloper, MySQL Workbench, etc.

Step 1. Download SQLWorkbench/J

You will first download a .zip file  from sql-workbench.edu.  Next, extract the .zip file to a folder where your application will live.  On my Mac I right-click on the sqlworkbench.jar and select Open to run it.

[tip: this part will be of course different if you use a different SQL tool or if you’re on a Windows system]

Screen Shot 2020-01-29 at 8.57.05 AM

Step 2.  Add the Snowflake JODBC driver to SQL Workbench/J

Login to Blackboard Data.  For this article I will use a non-production demo installation.

Login URL: https://ft26643.snowflakecomputing.com

Navigate to the Help menu and click on Download.

Screen Shot 2020-01-29 at 8.41.23 AM

Screen Shot 2020-01-29 at 8.41.50 AM

For JODBC you will click on Maven Repository and after browsing through versions, download a .jar file such as: snowflake-jdbc-3.9.2.jar

Screen Shot 2020-01-29 at 8.43.35 AM

For the file I used in the demo here is the direct link: snowflake-jdbc-3.9.2.jar

Next, use this jar file to create a new driver (click on Manage Drivers in bottom left).  Give the driver a name like Snowflake.

Screen Shot 2020-01-29 at 8.46.47 AM

Step 3.  Create a connection to Blackboard Data with your new driver.

Now it’s time to make use of the JODBC driver.  Let’s collect some configuration information about your data warehouse.  We need to find: database name, warehouse name, account name.

In the web interface, in my case under the URL https://ft26643.snowflakecomputing.com/, navigate in upper right to the Select Database menu.  Take note of the details.  Account name is the first part of your login web URL. In my system:

Warehouse name: BLACKBOARD_DATA_DEMO

Database name: BLACKBOARD_DATA_DEMO

Account name: ft26643

Screen Shot 2020-01-29 at 9.12.24 AM

Back in SQL Workbench/J click new profile to set connectivity information for your Blackboard Data account.  The JODBC URL in the connection profile will have the following format:

jdbc:snowflake://account_id.snowflakecomputing.com/?account=account_id

[tip: no semicolon at the end of the URL]

jdbc:snowflake://ft26643.snowflakecomputing.com/?account=ft26643

Screen Shot 2020-01-29 at 9.03.34 AM

One improvement that you can make now is to pre-select the database and warehouse for your connection.  You would include this improved JODBC URL.

jdbc:snowflake://ft26643.snowflakecomputing.com/?account=ft26643&warehouse=BLACKBOARD_DATA_DEMO&db=BLACKBOARD_DATA_DEMO

Screen Shot 2020-01-29 at 9.17.42 AM

If you skip this part, you can always select the warehouse and database in the SQL code as the first statement per login session:

use warehouse BLACKBOARD_DATA_DEMO;

use database BLACKBOARD_DATA_DEMO;

select * from cdm_LMS.course where ID=4

 

Step 4. Run SQL against Blackboard Data in SQL Workbench/J

You will find a database explorer and SQL worksheet.  The blue run button will execute the statements.  You can include multiple statements per worksheet.

Screen Shot 2020-01-29 at 9.19.52 AM

The same SQL in web Snowflake interface:

Screen Shot 2020-01-29 at 9.14.31 AM

The SQL Workbench/J database explorer:

Screen Shot 2020-01-29 at 9.04.25 AM

If you like SQL Workbench/J, that’s great.  If you need to connect a script or another tool, hopefully this article will help you in building your connection configuration.