Document toolboxDocument toolbox

Connecting Kyvos semantic models to Python using SQL connection

Owned by Sarabjeet Kaur
Jul 17, 2024
2 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)

This section explains how to connect Python to Kyvos semantic models using SQL connection. Use the ODBC or JDBC Driver to establish a connection.

ODBC Driver

Prerequisites

To connect Kyvos using Python, you must have the following.

  1. Python (3 and above) must be hosted either on an analyst’s machine or on Hadoop Cluster (Cloud/On-Premises).

  2. PYODBC module 

  3. Kyvos ODBC Driver

Steps to connect

  1. Download the Kyvos ODBC driver from https://www.kyvosinsights.com/kyvos-odbc-driver/
    You will be asked to register yourself to download the driver and the Installation Guide to set up the driver.

  2. Install the Kyvos ODBC driver using the system administrator credentials.

  3. Create a System DSN named KyvosDSN, as explained:

    1. For Windows

    2. For Mac OS

  4. Test the connectivity with Kyvos, and save the System DSN.

  5. Now you can start browsing the Kyvos semantic models in Python Notebook.

For example:

import pyodbc #connection string cnxn = pyodbc.connect('DSN=KyvosDSN;UID=xxxx;PWD=xxxx', autocommit=True) cursor = cnxn.cursor() #execute the prepared SQL statement cursor.execute("SELECT `ssb`.`brand1` AS `brand1`, SUM(`ssb`.`profit`) AS `sum_profit_ok` FROM `kyvos_browsing_automationcubes`.`ssb` `ssb` WHERE ((`ssb`.`mfgr` = 'MFGR#3') AND (`ssb`.`region` = 'ASIA')) GROUP BY `ssb`.`brand1`") #iterate the results D1=cursor.fetchall() for showD1 in D1:

JDBC Driver

Prerequisites

To connect Kyvos using Python, you must have the following.

  1. Python must be installed in the machine, where Python code will execute.

  2. JayDeBeApi module

  3. Hive JDBC Driver

Steps to connect

  1. To download the Hive JDBC Driver, click here.

  2. Save the Driver at any location. For example, /data/hdisk5/put_data/HiveJDBC/hive-jdbc-uber-2.6.5.0-292.jar

  3. To install Python, execute the yum install python3 command if not installed.

  4. To install Pip, execute the curl https://bootstrap.pypa.io/get-pip.py -o get-pip.py command.

  5. To install JayDeBeApi, execute the pip3 install JayDeBeApi command.

  6. Set JAVA_HOME, if not set.

  7. Create a Python file that contains the script code used to run the query.
    For example, Test.py is a sample file to demonstrate how to create a file and use a script code. In the script code, you can make required changes.
    Following is a sample script code.

    import jaydebeapi from contextlib import closing jclassname='org.apache.hive.jdbc.HiveDriver' jdbc_driver_loc = '/data/hdisk5/put_data/HiveJDBC/hive-jdbc-uber-2.6.5.0-292.jar' jdbc_driver_name = 'org.apache.hive.jdbc.HiveDriver' url='jdbc:hive2://<IP Address>:<Port>/;transportMode=http;httpPath=kyvos/sql' login="userid" pwd="password" sql = "select country, sum(sale_price) as SUM_sale_price from Kyvos_RawDataAccuracyCubes.RawData_CompanysaleSF as rawdata_companysalesf group by country order by country asc limit 10000" conn = jaydebeapi.connect(jclassname=jdbc_driver_name, url=url, driver_args=[login, pwd], jars=jdbc_driver_loc.split(",")) with closing(conn) as conn: with closing(conn.cursor()) as cur: cur.execute(sql) print(cur.fetchall())

  8. Run the python file by executing the python3 <name of the file> command. For example, the command would be python3 Test.py

 

Copyright Kyvos, Inc. All rights reserved.