Page Comparison

...

This section explains how you can 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

Anchor
ODBC ODBC

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.

...

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

Anchor
JDBC JDBC

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.

   Code Block
   language py
   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