Document toolboxDocument toolbox

Kyvos ODBC driver

Owned by Sarabjeet Kaur
Sep 06, 2024
9 min read

Applies to: Kyvos Enterprise  Kyvos Cloud (SaaS on AWS) Kyvos AWS Marketplace

Kyvos Azure Marketplace   Kyvos GCP Marketplace Kyvos Single Node Installation (Kyvos SNI)

ODBC is one of the most established and widely supported for connecting and working with databases. The Kyvos ODBC Driver is used for Spark SQL access to the Kyvos BI Server, enabling business intelligence, analytics, and reporting.

Click here to download the Kyvos ODBC driver.

Features

The Kyvos ODBC driver comes with the following features.

  • Catalog and schema support: The Kyvos ODBC Driver supports both catalogs and schemas to make it easy for the driver to work with various ODBC applications.

  • Security and authentication: Some Kyvos datastores require connections authentication with Secure Sockets Layer (SSL) protocol to protect data from unauthorized access. SSL encryption protects data and credentials when they are transferred over the network and provides stronger security. We recommended enabling SSL whenever you connect to a server that is configured to support it.
    For more information, refer to the Configuring SSL Verification on Windows section below.

Supported data types

The Kyvos ODBC Driver supports various standard data formats, converting between Spark data types and SQL data types.

The following table lists the supported data type mappings.

Spark Type

SQL Type

BIGINT

SQL_BIGINT

CHAR

SQL_CHAR

DATE

SQL_TYPE_DATE

DOUBLE

SQL_DOUBLE

INT

SQL_INTEGER

SMALLINT

SQL_SMALLINT

STRING

SQL_VARCHAR

TIMESTAMP

SQL_TYPE_TIMESTAMP

TINYINT

SQL_TINYINT

VARCHAR

SQL_VARCHAR

BOOLEAN

SQL_BIT

BINARY

SQL_VARBINARY

Kyvos ODBC Driver

Prerequisites for Windows

Each computer on which you install the driver must meet the following minimum system requirements:

  • One of the following operating systems:

    • Windows 8.1 or Windows 10

    • Windows Server 2012 and 2016

  • Visual C++ Redistributable for Visual Studio 2015 installed (with the same bitness as the driver that you are installing)

Note

The installer for Visual C++ Redistributable for Visual Studio 2015 is available in the Kyvos ODBC Driver package

  • Available disk space: 100 MB

  • To install the driver, you must have Administrator privileges on the computer.

Installing and configuring Kyvos ODBC Driver on Windows

Note

When installing Kyvos ODBC driver on Windows operating systems, ensure that driver bitness matches with bitness of your BI tool

To install the Kyvos ODBC Driver on Windows:

  1. Double-click the Kyvos ODBC Installer executable file to start the installation.

  2. If prompted, enter Administrator credentials and click OK.

  3. Click Next.

  4. Read License Agreement and click Yes if you agree to the terms; to continue with the installation. Else, click No to exit.

  5. Enter Username and Company Name in the corresponding fields and click Next.

  6. Choose the Setup Type. We recommend selecting the Complete

  7. If you have selected the Custom setup type, you can change the installation location, click Change to choose the folder on your computer, and then click OK. To accept the installation location, click Next.

  8. Click Install.

  9. When the installation completes, click Finish.

Verifying driver installation on Windows

You can find the details of the Kyvos ODBC Driver installed on your Windows computer in the ODBC Data Source Administrator.

  1. Open the ODBC Administrator:

  2. For Windows 8.1 or Windows 10, type ODBC Administrator in the Start menu and click on ODBC Data Source Administrator (64-bit) to open.

  3. Click the Drivers tab and verify that Kyvos ODBC Driver is available in the list of ODBC drivers installed on your system. The version number is displayed in the Version column corresponding to Kyvos ODBC Driver.


Creating a data source name (DSN) on Windows

After installing Kyvos ODBC Driver, a default System DSN is created with the name Kyvos ODBC for Kyvos ODBC Driver. You can use this default System DSN to connect to Kyvos BI Server from your favorite BI Tool. However, if you wish to create some other DSN, you can do so using the following steps.

  1. Open the ODBC Administrator.

    1. For Windows 8.1 or Windows 10, type ODBC Administrator in the Start menu and click on ODBC Data Source Administrator (64-bit) to open.

  2. Choose one:

    1. To create a DSN that only the user currently logged into Windows can use, click the User DSN

    2. Or, to create a DSN that all users who log in to Windows can use, click the System DSN However, you need Administrator credentials to Add/Modify System DSN.
      Note: It is recommended to create a System DSN instead of a User DSN. This is because, some applications load data using a different user account, and may not be able to detect User DSNs that are created under another user account.

  3. Click Add.

  4. In the Create New Data Source dialog box, select Kyvos ODBC Driver, and click Finish. This displays the Kyvos ODBC Driver DSN Setup dialog box.
    Username and Password Authentication

     

    Kerberos Authentication

  5. Enter details as:

    1. In the Data Source field, enter a name for your data source.

    2. In the Host field, enter the IP address or hostname of the Kyvos Web App Server.

    3. In the Port field, enter the TCP port number that Kyvos Web App Server uses to listen for client connections.

    4. In the HTTP Path field, enter the web application path in the <webapp name>/sql format. For example, if the application name is kyvos, enter kyvos/sql.

    5. In the Authentication, do the following:
      For Username and password,
      In the User Name field, enter your Kyvos Web App Server user name. 
      In the Password field, enter your Kyvos Web App Server password. Optionally, choose to save the password in an encrypted form using the corresponding checkbox.
      For Kerberos,

      1. In the Type field, select Kerberos.

      2. Specify Realm, Host FQDN, and Service Name.

  6. To configure client-server verification over SSL, select the Use SSL checkbox in the Setup Options For more information, see the Configuring SSL Verification on Windows section below.

  7. To configure logging behavior for the driver, click Advance Options. For more information, refer to the Configuring Logging Options on Windows section below.

  8. To test the connection, click the Test. If the connection fails, then recheck settings. Contact your Kyvos BI Server administrator if needed. Review results, if required, and then click OK.

  9. To save your settings and close the Kyvos ODBC Driver DSN Setup dialog box, click OK.

  10. To close the ODBC Data Source Administrator, click OK.

Note

It is recommended to create a System DSN instead of a User DSN. This is because, some applications load data using a different user account, and may not be able to detect User DSNs that are created under another user account.

Note

It is recommended to create a System DSN instead of a User DSN. This is because, some applications load data using a different user account, and may not be able to detect User DSNs that are created under another user account.

Configuring SSL verification on Windows

You can configure the driver to connect to a Secure Sockets Layer (SSL) enabled Kyvos BI Server. When using SSL to connect to a server, the driver supports identity verification between the client and the server.

To configure SSL verification on Windows:

  1. To access SSL options for a DSN, open the ODBC Data Source Administrator, where you created the DSN. Select the DSN and click Configure.

  2. On the Kyvos ODBC Driver DSN Setup dialog box, select the Use SSL checkbox in the Setup.
    Username and password Authentication

    Kerberos Authentication

  3. To specify the CA certificates that you want to use to verify the server, do one of the following:

    1. To configure the driver to load certificates from a specific PEM file, specify the full path to the file in the Trusted Certificate Path.

    2. To use the Windows Trust Store, select the Use System Trust Store checkbox.

Important

If using Windows Trust Store, you MUST import the trusted CA certificates to the Trust Store.

Configuring logging options on Windows

To help troubleshoot issues, you can configure logging settings for the driver. In addition to the functionality provided in the Kyvos ODBC Driver, the ODBC Data Source Administrator provides tracing functionality.

Important

Logging or tracing decreases performance and can consume a large quantity of disk space, so make sure to keep log level as FATAL which will have a negligible impact on performance and disk space.

To configure driver logging on Windows:

  1. To access logging options, open the ODBC Data Source Administrator where you created the DSN, then select the DSN, click Configure.

  2. On the Kyvos ODBC Driver DSN Setup dialog box, click the Advance Options tab.

  3. From the Log Level drop-down list, select the logging level corresponding to the amount of information that you want to include in log files.

  4. This table enlists the information corresponding to each log level.

Logging level Number
(for Mac)

Logging level

Description

Logging level Number
(for Mac)

Logging level

Description

1

FATAL 

Logs severe error events that lead the driver to stop processing.

2

ERROR

Logs error events that might allow the driver to continue running.

3

WARNING

Logs events that might result in an error if no action is taken.

4

INFO

Logs general information that describes the progress of the driver.

5

DEBUG

Logs detailed information that is useful for debugging the driver.

6

TRACE

Logs all driver activity, including result set.

  1. In the Log Path field, specify the full path to the folder where you want to save log files.

  2. In the Max Number Files field, type the maximum number of log files to keep.

Note
After the maximum number of log files is reached, each time an additional file is created, the driver deletes the oldest log file.

  1. In the Max File Size field, type the maximum size of each log file in megabytes (MB).

Note

After the maximum file size is reached, the driver creates a new file and continues logging.

  1. Click OK.

  2. Restart your BI application for new settings to come in effect. The Kyvos ODBC Driver creates a log file named log at the location specified in the Log Path field.

  3. Click OK.

  4. Restart your BI application for new settings to come in effect. The Kyvos ODBC Driver creates a log file named log at the location specified in the Log Path field.

Important

After Connecting using Kyvos ODBC Driver to the server, a folder named SQLLog is created at the logging path location. Under this folder, new folders are created for the current date(s), which contains the Kyvos_odbc_client_SQLLog.log file. This file captures records for all the queries sent to the Kyvos BI server.

Note

The default log level is set as FATAL, and the default location for log files is C:\ProgramData\Kyvos\Kyvos ODBC Driver\Logs. You cannot disable logging for Kyvos ODBC Driver.

Creating a data source name (DSN) on Mac OS

After installing the Kyvos ODBC driver, create a System DSN named KyvosDSN, as explained below.
You can create the DSN from the odbc.ini file or using the iODBC Administrator.

Note

MAC does not supports Kerberos authentication.

Using odbc.ini file

Navigate to /Library/ODBC folder, and edit the odbc.ini file as:

[ODBC Data Sources] KyvosDSN = Kyvos ODBC Driver [KyvosODBC] Description = KyvosDSN Driver = Kyvos ODBC Driver Host = start.trial.kyvosinsights.com Port = 8081 AuthMech =3 UserName = admin HTTPPath = kyvos/sql SSL = 0

Using iODBC Administrator
Enter details as mentioned above, and create the DSN as shown in the following figure.

Configuring logging options on MAC

  1. Open kyvos.odbcclient.ini file

  2. Configure the logging parameters as needed.

  3. Save the file and close.

Note

For log level related parameters, see the Configuring logging options on Windows section.

Configuring rows fetched per block

Important

This is applicable to Windows and Mac.

On the Kyvos ODBC Driver DSN Setup dialog, in the Advance Options section, for the Rows Fetched Per Block property, you can specify the number of rows fetched in each chunk. The default value of the Rows Fetched Per Block property is 200000.

Windows

Mac

To increase the limit for the chunk size at Kyvos BI Server, you need to add the sql.hive.server2.thrift.resultset.max.fetch.size property at the connection level.

To add a property, perform the following steps.

  1. Go to any created connection, and then click Properties.
    Following is the sample to explain how to add a new property at the connection level.

  2. Click the Properties link.

  3. Add a required limit in the Value field.

You can see this property in Miscellaneous section, click Apply.

Copyright Kyvos, Inc. All rights reserved.