cyclefusion/presto-odbc

Presto ODBC Driver

Presto ODBC Driver

ODBC is a C API that provides a standard way of communicating with anything that accepts SQL-like input; the Presto ODBC Driver lets you communicate with Presto via this standard. The high-level goal is to be able to communicate with Presto from MS Query, MS Excel, and Tableau.

This driver is written in the D Programming Language

Current Status

• Only works on Windows
• Many functions are not implemented, the driver does not meet the "Core" level of ODBC conformance
• The only error handling in place is a "catch and log" strategy
• Most queries will work as expected
• Tableau works correctly for the cases we have tried
• MS Query is partially tested and may work if you create a Presto File DSN
• The driver is single-threaded
• You cannot run multiple instances of the driver on the same machine due to limitations in D's support for DLLs

Goals

• Full ODBC 3.51 conformance
• Full support on Windows/Mac/Linux
• Seamless integration with Tableau

Development Environment Setup

Installation Prerequisites

1. Cygwin with the GNU make package
2. MSVC 64-bit linker (download and install the free Express 2013 edition for Windows Desktop)
3. dmd (D Language Compiler), tested with dmd 2.065 (note: this must be the installer version and it must be installed after Visual Studio)
4. Access to a running Presto instance

Building and Registering the Driver

1. Build the Presto ODBC Driver
2. Launch the Cygwin terminal
3. Navigate to your checkout of this repo (e.g. cd /cygdrive/c/presto-odbc)
4. make clean install -- builds the driver, runs tests, copies the driver and libcurl to C:\temp and backs up the log files
5. Register the Presto ODBC Driver by double clicking the register_driver.reg file in the main directory of this repo
6. Setup a data source for the driver
7. Open Control Panel and choose Set up ODBC data sources (64-bit)
8. Sanity Check: Look at the Drivers tab, make sure you see Presto ODBC Driver
9. Enable the Driver Manager Logfile (from the ODBC Data Sources window) 1. Go to the Tracing tab 1. Set the Log File Path to C:\temp\SQL.LOG 1. Click Start Tracing Now 1. Click Ok to close the program

Using the driver with Tableau

1. Open Tableau
2. Click Connect to data
3. At the bottom, select Other Database (ODBC)
4. Click the radio button for Driver
5. Select Presto ODBC Driver
6. Click Connect
7. Enter a connection parameters string of the form key=value;key=value to describe your connection. A description of the supported keys and values can be found in the Connection Parameters file
8. Tableau will then partially load the driver and redundantly ask for the same information in Tableau's UI: 1. Replace the contents of the String Extras box with the string you entered in notepad. Ignore the rest. 1. Click OK
9. Tableau will perform a bunch of fake queries to analyze the ODBC driver, this may take a while to complete but only needs to happen once
10. On the new screen:
11. Click Select Schema, then press the search icon, then select the schema you entered on the previous screen
12. Search for tables (or press enter to see all of them) and drag any table you wish to use to the right
13. Click Go to Worksheet
14. Click OK to go past the warning dialog
15. Analyze!

Coding Conventions

Not all of the conventions have been applied to the source yet.

• 4 space indentation
• Try to limit to 120 columns
• Prefer myHttpFunction to myHTTPFunction
• All ODBC functions must have their contents wrapped with a call to exceptionBoundary
• As appropriate, change integer types to enum types (use StatementAttribute instead of SQLSMALLINT, etc)
• Always wrap C types with safer D abstractions (see toDString and OutputWChar); prefix the C-style variables with an underscore
• Also prefix variables with an underscore to express that the variable should be cast/converted to or encapsulated in another type before use
• Use dllEnforce instead of assert
• Avoid fully qualifying enum values (MyEnumType.MyEnumValue); use a with statement instead
• Always use a with statement when accessing ODBC handles
• Always specify whether lengths are in bytes or characters as part of a variable's name
• At the top of the file, separate imports from different packages with a blank line. In particular, there should be a blank line between imports from std and presto.client as well as between presto.client and presto.driver
• Always group imports from the same packages together

References

• ODBC 3.x Requirements
• ODBC Function Summary
• ODBC C Data Types Table
• ODBC SQL Data Types Table
• Interfacing from D to C