Connecting Kyvos semantic models to Python using SQL connection

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

• JDBC Driver

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. To create the Test.py file, use the following script code and make the required changes.

   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 Test.py command.