Notes on accessing dashDB from python

This is a memo about how to access dashDB for Analytics, which is grouped in IBM Watson Data Platform, from Python, which is the mainstream for numerical data processing.

What is dashDB

dashDB is an analytics service that combines in-memory analytics with "DB2 BLU" and in-database analytics from PureData for Analytics (Netezza) and can be used as a cloud service. It is available on Bluemix, a cloud platform provided by IBM. dashDB uses the BLU acceleration built into DB2 as its core analytics engine. BLU Acceleration is a completely new SQL processing engine developed based on the research results of IBM's Basic Research Laboratories. BLU acceleration is packed with innovations, as introduced in the features and benefits of DB2 for LUW. The most important point among them is that the tuning that was inherent in the analysis process is no longer necessary. All the user needs to do to take advantage of BLU acceleration is populate the data. High-speed analysis can be performed immediately after data is input. This is an essential element for dashDB to be offered as an analytics service in the cloud. To put it the other way around, it can be said that dashDB was made possible because of the introduction of BLU acceleration, which is an analysis engine that does not require tuning. And dashDB is provided as a service, so you can start using it quickly without any initial investment or infrastructure construction. (1)

Python is a language for analytics

Python provides libraries for numerical data processing, high-level scientific computing, machine learning, etc. Maybe Python is more popular as an analytics language than R language. There is enough information to think that it is. (2), (3), (4) So let's see how to use dashDB for Analytics from Python.

Python & dashDB You can use ODBC and JDBC to connect to dashDB from Python. Instructions on the settings and links to download drivers can be found on the Bluemix dashDB admin screen.

ODBC connection

The following screenshot shows one of Bluemix's dashDB admin screens, with Connect selected and SSL selected. To connect to dashDB from Python via an ODBC driver, you need to download and install the driver. スクリーンショット 2017-06-01 21.42.46.png Reference (8) will help you to install and configure the ODBC driver. However, the DSN name cannot be connected unless the name displayed on the page corresponding to the above screen copy is used. The sample code below works on Ubuntu 14.04, which runs on my MacBook's vagrant environment.

!/usr/bin/env python
-*- coding:utf-8 -*-
ODBC

from ibmdbpy.base import IdaDataBase
idadb = IdaDataBase(dsn="DASHDB", uid="******", pwd="***********")
print "current_schema = ", idadb.current_schema
idadb.close();
ubuntu@ubuntu-xenial:~/dashDB$ ./test1.py 
current_schema =  DASH9824
Connection closed.

JDBC connection

If you download the above-mentioned ODBC driver, the JDBC driver is also included, so you can also set the connection at http://pythonhosted.org/ibmdbpy/start.html#jdbc- on the reference material (5) site. Located in connection, the JDBC driver can also be downloaded from Reference (9). This is also the code that runs on the disguise server Ubuntu14.04 that runs on the MacBook.

!/usr/bin/env python
-*- coding:utf-8 -*-
JDBC
from ibmdbpy.base import IdaDataBase

jdbc = 'jdbc:db2://dashdb-entry-yp-dal09-09.services.dal.bluemix.net:50001/BLUDB:user=dash982;password=**********'
idadb = IdaDataBase(jdbc)
print idadb.current_schema
idadb.close();

However, as of June 1, 2017, this code doesn't work. The cause is that the version of JayDeBeApi has been upgraded. You can refer to this issue at the following URL: RuntimeError: No matching overloads found. at native/common/jp_method.cpp:117 #18 https://github.com/ibmdbanalytics/ibmdbpy/issues/18

ibmdbpy interactive interface

The bmdbpy project provides a Python interactive interface for IBM dash DB and IBM DB2 database manipulation algorithms and data manipulation. Accelerate Python parsing by seamlessly pushing operations written in Python to a connected database to perform, thereby benefiting from in-database performance-enhancing features such as columnar storage and parallelism. receive. The ibmdbpy project can be used by Python developers without learning. This is because it mimics the well-known user interface of the Pandas library for manipulating data and the Scikit-learn library for machine learning algorithms. The ibmdbpy project is available in Python releases 2.7-3.4. You can connect to a dashDB or DB2 instance via ODBC or JDBC. The project is still in its infancy and many of its features are still under development. However, some experiments have already demonstrated significant performance benefits when working with medium or large amounts of data, that is, tables with more than a million rows. (Five) The Pandas user interface can be intuitively understood by looking at 10 Minutes to pandas http://pandas.pydata.org/pandas-docs/stable/10min.html.

Install the ibmdbpy library

There is ibmdbpy as a library for operating dashDB from Python, and you can drop it into your own execution environment with pip install. (5) (6) (7)

ubuntu@ubuntu-xenial:~/dashDB$ pip install ibmdbpy

Execution example of ibmdbpy

It works as below, but it seems that it is necessary to take a closer look because it is still under development.

ubuntu@ubuntu-xenial:~/dashDB$ python
Python 2.7.12 (default, Feb 15 2017, 05:35:22) 
[GCC 5.4.0 20160609] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> from ibmdbpy.base import IdaDataBase
>>> idadb = IdaDataBase(dsn="DASHDB", uid="******", pwd="*******")
>>> idadb.ida_query("SELECT* FROM SAMPLES.CITSTATUS")
 䥃 彔 佃 䕄 䥃 彔 䕄 䍓
0    1                                   Born in the U.S.
1    2  Born in Puerto Rico/Guam/Virgin Islands/Northe...
2    3                  Born abroad of American parent(s)
3    4                                Naturalized Citizen
4    5                          Not a citizen of the U.S.

SQL execution by ibm_db

Ibm_db for Python can be installed with pip install ibm_db. When I verified it, it seemed that there was a problem connecting with the DSN name, so I succeeded in accessing it when I accessed it with the connection character string without using the catalog. It seems that data containing Japanese can be used without problems.

!/usr/bin/env python                                                                                                         
import ibm_db
url = "DATABASE=BLUDB;HOSTNAME=dashdb-entry-yp-dal09-09.services.dal.bluemix.net;PORT=50001;PROTOCOL=TCPIP;UID=******; PWD=*****;SECURITY=ssl"
sql  = "SELECT* FROM SAMPLES.CITSTATUS"
conn = ibm_db.connect(url, "", "")
stmt = ibm_db.prepare(conn, sql)
ibm_db.execute(stmt)
data = ibm_db.fetch_tuple(stmt)
while(data):
    print data[0], data[1]
    data = ibm_db.fetch_tuple(stmt)

ibm_db.free_stmt(stmt)
ibm_db.close(conn)

The execution result is as follows, which is the same as normal ODBC access.

ubuntu@ubuntu-xenial:~/dashDB$ ./test5.py
1 Born in the U.S.
2 Born in Puerto Rico/Guam/Virgin Islands/Northern M
3 Born abroad of American parent(s)
4 Naturalized Citizen
5 Not a citizen of the U.S.

Summary

I tried accessing Bluemix's dashDB for analytics with Python. The ibmdbpy project applies a UI compatible with Pandas, and I have high expectations, but since it is in the early stages of development, I would like to keep an eye on future results. Also, the Python version of the legacy ibm_db is stable and can be used smoothly.

There is also a sequel How to write code to access python dash DB on Blumix or local.

Reference material

(1) Let's touch dashDB https://www.ibm.com/developerworks/jp/data/library/dashdb/dm-dashdb1-bluemix/ (2) Data science with Python http://pythondatascience.plavox.info/ (3) Why Python is chosen for machine learning http://qiita.com/yaju/items/5502115d7e3d06e6bbdd (4) Scipy Lecture Notes http://www.turbare.net/transl/scipy-lecture-notes/index.html (5) ibmdbpy Accelerating Python Analytics by In-Database Processing http://pythonhosted.org/ibmdbpy/index.html (6) Python package index ibmdbpy 0.1.4 A Pandas-like SQL-wrapper for in-database analytics with IBM dashDB/DB2. https://pypi.python.org/pypi/ibmdbpy (7) GitHUB ibmdbanalytics/ibmdbpy https://github.com/ibmdbanalytics/ibmdbpy (8) Data analysis with Bluemix "dashDB": I tried to set ODBC on the Linux server http://qiita.com/azzeten/items/55dc4ad1535bf709403b (9) DB2 JDBC Driver Versions and Downloads http://www-01.ibm.com/support/docview.wss?uid=swg21363866 (10) ibm_db 2.0.7 https://pypi.python.org/pypi/ibm_db/

Recommended Posts

Notes on accessing dashDB from python
Notes on oct2py calling Octave scripts from Python
[Python] Notes on data analysis
Notes on installing Python on Mac
Notes on installing Python on CentOS
Notes on Python and dictionary types
Notes on importing data from MySQL or CSV with Python
Notes on accessing SQS from AWS VPC Lambda via endpoint
Notes on installing Python using PyEnv
Call C / C ++ from Python on Mac
Update Python on Mac from 2 to 3
Notes for using OpenCV on Windows10 Python 3.8.3.
Notes on nfc.ContactlessFrontend () for nfcpy in python
Learning notes from the beginning of Python 1
Notes on doing Japanese OCR with Python
Connecting from python to MySQL on CentOS 6.4
Notes on building Python and pyenv on Mac
Learning notes from the beginning of Python 2
Notes on using code formatter in Python
Python scraping notes
Python study notes _000
Python learning notes
Python on Windows
twitter on python3
Python beginner notes
Python study notes_006
sql from python
python C ++ notes
python on mac
MeCab from Python
Python grammar notes
Python Library notes
Python on Windbg
python personal notes
python pandas notes
Python study notes_001
python learning notes
Python3.4 installation notes
Notes on installing Python3 and using pip on Windows7
Notes on using dict in python [Competition Pro]
PHP and Python integration from scratch on Laravel
ABC125_C --GCD on Blackboard [Notes solved in Python]
Try accessing the YQL API directly from Python 3
[Python] Notes on accelerating genetic algorithms using multiprocessing
Use thingsspeak from python
Python conda on cygwin
Install python on WSL
Touch MySQL from Python 3
missingintegers python personal notes
PyOpenGL setup on Python 3
Operate Filemaker from Python
Use fluentd from python
Install Python on Pidora.
Install Scrapy on python3
Changes from Python 2 to Python 3.0
Notes on HDR and RAW image processing with Python
Python package development notes
Python from or import
Notes on creating a python development environment on macOS Catalina
Manipulate excel files from python with xlrd (personal notes)
Celery notes on Django