This article explains how to connect to Snowflake using ODBC Driver for Snowflake and make a trace log on Windows, macOS, and Linux operating systems. Besides, you will also know how to access and view Snowflake data using third-party tools on the examples of such BI and analytics instruments as Power BI and Tableau.
Devart ODBC Driver for Snowflake is a high-performance solution with enterprise-level functionality that allows connecting to Snowflake easily. Using our driver, you can connect fast to Snowflake from different analytics, reporting, BI, or ETL applications that are ODBC-compliant from both 32-bit and 64-bit Windows. ODBC driver fully covers standard ODBC API methods and data types and offers safe and simple access to real-time Snowflake data from any location.
Let’s first take a look at the features of the ODBC driver for Snowflake.
Key Features
Connection to Snowflake
ODBC-enabled programs may establish secure connections to Snowflake via the Internet using our connectivity solution. Connecting through a proxy server is an alternative to connecting directly to Snowflake.
The Syntax of Extended SQL
Utilizing our ODBC driver, you may integrate Snowflake objects like regular SQL tables into your data infrastructure. All the advantages of SQL may be used in SELECT queries that are compatible with SQL-92 thanks to the enhanced SQL syntax:
- Complex JOINs;
- WHERE conditions;
- Subqueries;
- GROUP statements;
- Aggregation functions;
- ORDER statements;
- and more.
Bulk Updates
The driver enables bulk updates to Snowflake by uniting SQL queries into batches, which streamlines and speeds up the process of updating massive amounts of data in Snowflake.
Compliance with ODBC
This driver fully supports the standard ODBC interface:
- ODBC API Functions support
- ODBC Data Types support
Additionally, Advanced Connection String options are supported. This means Snowflake may be accessed from any ODBC-compatible environment, including desktop and online apps.
Snowflake Compatibility
The ODBC driver for Snowflake entirely supports all of the data formats provided by the Snowflake API.
In addition, the driver works well with the official Snowflake API.
Superior Conversion of Data
Our sophisticated Data Conversion methods allow reversible mapping between any Snowflake and ODBC data type.
Integration
The driver supports third-party data analysis tools like Microsoft Excel, which may be used in conjunction with Visual Studio and other integrated development environments (IDEs).
Refer to the Compatibility page for a comprehensive rundown of supported software and hardware.
Multiplicity of Systems
There is no need to further set up the driver, programs, or environment to utilize the Devart ODBC Driver for Snowflake with either 32-bit or 64-bit software on either x32 or x64 systems.
Fully Unicode Driver
The ODBC driver for Snowflake is completely Unicode. You can accurately extract and deal with data from multilingual Snowflake databases, regardless of the charset used (Latin, Cyrillic, Hebrew, Chinese, etc.) or the localization of your working environment.
Exceptional Efficiency
Our driver’s features, which include local data caching, connection pooling, query optimization, and more, significantly quicken the performance of any operation when working with Snowflake.
Support
For immediate assistance from trained experts, speedy issue solving, and nightly builds with hotfixes, please visit the Support page.
Connecting to Snowflake with the ODBC Driver
After the driver has been installed, a Data Source Name (DSN) should be set up in the ODBC Data Source Administrator for use with Snowflake.
- Open the ODBC Data Source Administrator.
- Please note that before Windows 8, this icon was referred to as Data Sources (ODBC). In Windows, go to the search box and type ODBC Data Sources; then, choose the program whose bitness is compatible with the external program (32-bit or 64-bit). Additionally, ODBC Data Sources may be accessed under Administrative Tools in the Control Panel.
- You may also create a 32-bit DSN by running C:WindowsSystem32odbcad32.exe, or a 64-bit DSN by running C:WindowsSysWOW64odbcad32.exe.
2. Choose either the User DSN or the System DSN menu. While both kinds of DSNs are generally compatible, specific programs may only operate only with one.
3. Click Add button. You’ll see a window that prompts you to Create a New Data Source.
4. Choose the Devart ODBC Driver for Snowflake and press Finish. The Driver Setup Dialog window will pop up.
5. To connect to Snowflake, fill up the fields with the relevant data and click Ok.
Making an ODBC Trace Log on Windows
Tracing is synchronized across the 32-bit and 64-bit ODBC Administrators, so if you turn it on or off in one, it does the same thing in the other.
Select Machine-Wide tracing for all user identities if the ODBC client program you want to monitor runs under the Local System account or any other user login than your own. For SSMS, this is a potential need.
Using Windows ODBC Source Administrator, create a trace file. Follow the steps below.
1. In Windows 10, type ODBC Data Sources into the search box (in older versions of Windows, choose Control Panel > Administrative Tools) and select the appropriate bitness for your application.
2. Go to the Tracing tab.
3. Alter the location of the log files if required. Verify that the program has to write access to the selected path, and then hit the Apply button.
4. Simply press the Trace Now button.
5. It’s time to force a reload of every program.
6. To verify the driver’s connectivity, go to the DSN settings and click the Test Connection button.
7. Try to recreate the issue.
8. On the Tracing menu, choose Stop Tracing Now.
9. Please provide the collected log (for example, devart.log).
Making an ODBC Trace Log on macOS
Use the ODBC Administrator’s Tracing tab to activate the tracing feature on macOS.
1. Bring up the ODBC Administrator.
2. Go to the Tracing tab.
3. Modify the location of the Log file if required.
4. In the When to trace drop-down, choose Always.
Creating an ODBC Trace Log on Linux
In Linux, you may monitor ODBC calls by configuring the Trace and TraceFile keyword/value pairs in the [ODBC] section of the /etc/odbcinst.ini file, as shown below:
[ODBC]
Trace=Yes
TraceFile=/home/test/devart.logAfter receiving a log file, you should deactivate logging since it slows down to read/write operations.
Using Third-Party Tools with ODBC Driver for Snowflake
ODBC Driver for Snowflake is compliant with the following tools:
- DBeaver
- Oracle Database Link
- Microsoft Access
- Microsoft Excel
- OpenOffice and LibreOffice
- PHP
- Power BI
- Python
- QlikView
- SQL Server Management Studio
- SSIS
- Tableau
For a detailed description of every tool, please follow this link.
Connecting Power BI to Snowflake using ODBC Driver
Power BI is a famous solution that includes services, applications, and connectors for aggregating data from diverse sources and generating insights. A compatible ODBC driver allows Power BI to communicate with various databases.
This comprehensive guide will teach how to use an ODBC driver to connect to Snowflake and get your data into Power BI Desktop. That will mean you have a Snowflake DSN for ODBC driver set up and ready to go.
1. Start Power BI Desktop and select Get Data.
2. To use an ODBC connection, open the Get Data dialogue box and choose the Other tab. Confirm your selection by clicking the Connect button.
3. If you’re using Snowflake, you may choose it from the preexisting DSN list in the From ODBC dialog box by expanding the drop-down menu and clicking on it.
4. Select the Advanced options arrow to see an expanded dialogue box where you may write or paste a SQL query to filter the results.
5. Power BI will ask you for those details if the connection to your data source requires authentication. To log in, fill out the blanks with your Username and Password, then press the OK button.
6. Your data source’s underlying structures should now be visible. Simply clicking on a database item will provide a glimpse of its contents.
7. Select the table you’ll use for analysis, and then click Load to import the Snowflake data into Power BI.
Using an ODBC Connection to Bring Snowflake Data into Tableau
Tableau is a data visualization application, that also allows for importing raw data, conducting analyses, and generating reports. You can connect to a wide variety of cloud and on-premise relational and non-relational databases using Tableau Desktop and our ODBC drivers.
Let’s see how to connect Tableau Desktop to Snowflake using the ODBC driver for Snowflake.
1. Start Tableau Desktop.
2. Select More… in the Connect section of the homepage to access additional features.
3. Pick Different Information Sources (ODBC).
4. Select the Snowflake-specific DSN you set up earlier from the resulting drop-down menu. Instead of creating a DSN, you may pick Driver and then the Devart ODBC Driver for Snowflake from the drop-down menu.
5. Hit the Link button.
6. Please choose Sign in when your connection has been established successfully.
7. Choose the appropriate Snowflake database and schema.
8. There should be a list of all tables to which you have access in the linked data source.
9. Write the table’s name into the corresponding field. Then drop tables here to access their contents, or use the New Custom SQL button to craft a query that will get only the necessary information.
10. To see the most recent information, choose Update Now.
Integrating the ODBC Driver for Snowflake into an SSIS Data Flow
Data migration is only one of the many tasks that SQL Server Integration Services (SSIS) can perform. Microsoft ODBC 3.x is used for communication between the Devart ODBC Driver for Snowflake and SSIS, which acts as a translation layer between the data source and SSIS.
A problem may arise if you use the SQLExecDirect function to extract data from an ODBC data source since SSIS anticipates the ODBC 2.x behavior while the ODBC driver keeps retrieving data from a data source using ODBC version 3.x. Open the DSN settings, go to the Advanced Settings tab, and then pick Ver 2.x from the ODBC Behavior drop-down to ensure that SQLExecDirect functions correctly.
