Hi I’m Philip from the HANA Academy. In
this series of videos were looking at
the External Machine Learning Library
which was introduced with HANA 2 SPS02.
In this video we're going to look at
an alternative way that we might
populate a table in HANA. Now
previously we just did a simple import
of a table that I prepared previously.
However this data, the test data we're
using for a scenario originates from
MNIST. So what might be interesting is we
could choose what data we want to import
for our testing and we could maybe use
Python because that's the glue that all
data scientists and folks are using
these days to to join things together. We
could use the Python driver for HANA
to actually have a Python script that
would load the data directly into a HANA
table. So that's what we're going to do.
We're going to look at the new Python
driver with HANA 2.0 SPS02. Which is 24 00:00:54,510 —-> 00:00:59,940 part of the HANA
client tools package. So the first thing
to do is to just check where we'll find
the reference guide. For that there's the
installation site. That's in the bottom
left of HANA. We've got the client
installation update guide. We’ll go
through the steps that are contained in
there actually and do the installation
in a moment. And then in the reference
section if you choose view all and then
scroll you will find that there's the
client interface programming reference
and this is where you get the reference
on all the client tools and in
particular for us here we've got support
for Python. So you can see that we've got
the whole section here explaining our
support for Python. Now the new Python
driver in the HANA 2.0 SPS02 now
supports Python 3.4 as well as 2.7 and
it says as close as possible to the PEP
249 specification. All the details
are here in the client interface
programming reference. So let's go ahead
and start to install that. Now I'm using
HANA Express so for my purposes. Now I'm
using HANA Express so I can just simply
download the client for the wherever I
want to install them using the installer.
If you're a regular SAP customer you're
not using HANA Express then of course
you can get the client tools from SWDC.
Now what I'm going to do is just
download the clients for the machine
where I want to use them. I'm going to
put them actually onto our Ubuntu
server where we are actually having
TensorFlow running. So I'm going to
download the Linux clients but the
process is pretty much the same wherever,
whichever flavor of Linux or Mac or
Windows you want to install them on.
Wherever you've got your Python systems
running. So let's go ahead and download.
So the download is complete. We can go
ahead and close that. Now what we also
might want to do is just have a look at
the example Python script we're going to
use once we get the client installed. So
we can download that now as well. So for
that we go to the HANA Academy GitHub
page and the EML repository and in the
code snippets section you'll find there
is EML 11 Python2HANA which is our
Python script. So what we're going to do
is open this up and then we will make
some changes to it. So let's copy the
script. It’s only 26 lines and we'll
paste it into a text editor. I happen to
use notepad++ but you can use
whatever you want and we'll just quickly
save it for example as Python2HANA.py
because it's a Python script. Now we get
some nice code coloring. Now we'll come
back to that. Now there's just one thing
we need to do before we can actually use
this script. We’ll come back to look at
the details of it later. We need to
specify the address of the HANA server
that we're going to use. So if you just
go back to compute engine because we're
running in Google Cloud Platform with
the free trial. We can see we've got our
HANA Express server as our TensorFlow
server is in the same zone, in the same
network. We can use the internal IP
otherwise you'd use your external IP
address for your HANA machine. So that
the TensorFlow system can connect to it
and obviously make sure that the
firewall has the ports, the HANA ports
are open that you need to access. So
let's paste that into our script. The
other thing we're going to need to do is
specify the password that you used when
you created your EML user in the
previous video.
So I'm gonna put in the password that I
use. You should put in your password and
then we can save the file. So I've
entered the password and I've now saved
it. Right we've got those two files. We
need to transfer them to the system
where we're going to run our scripts and
what we want to have the driver
installed which is TensorFlow. So for
that I've already got a an FTP program
set up. It’s FileZilla you could use a
WinSCP or whatever you want.
Let me just now refresh. We can see we've
got our clients. So let's transfer the
clients and we've also got in our
Documents folder this script that we
just modified. So let's transfer that
over there as well. So the transfers are
done. Now if we go back to SSH session
and have a quick look we can see we've
got the clients file and we've got our
script as well. So what we now need to do
to install the Python driver is install
a HANA client tools and then install the
driver into our Python environment.
Remember we're using a specific
TensorFlow environment we set up in a
previous video. So first for the client
tools themselves we're going to do a
tar - xvf to
decompress the distribution file. So let’s
copy and paste the name and let that run
and we're going to get a few different
files are created. One of which is called
HTTP _ client _ limits.
Which is the file we want. In your case
if you're not using this version you
might get a slightly different file name
but it's the client tools that we're
looking at specifically for HANA
database. So again we will do a tar and
we will copy and paste so that we can
decompress that. It should create your
HDB client Linux folder. So now we can
change into that folder and you should
see that we've got some files including
an hdbinst to do our installation. So
we're going to use that command line but
before we do it we just need to make
sure we've got the correct rights. So
we're going to do a quick
chmod on this and subfolders 755. We want
and we can do that on all files. Be very
careful that you do it in the folder you
mean. Don't for example do that in your root
folder by accident because it will cause
you a lot of trouble. Now we've done that
we can actually go and run it. We’ll need
to Super User rights to do this and make
sure it's in this directory. We’re gonna
run the hdbinst program. So let's paste
that in and run through the process. So
it detected the client tools
installation kit. We can go for the
default path notice it's User SAP HDB
client. We’re going to need that in a
moment
and it will go ahead and install. That's
now done. Let's just have a quick look
at that client. In fact we can see it's
up here. So just copy and paste it. So if
there's a little bit of typing. We can
see that this is where the client tools
are stored but there's also a HDB CLI
file and this is what we're going to
want to actually now installed into our
Python environment and you will do that
for any or each of the Python
environments you've got. Remember we've
got one here called TensorFlow.
So to install that we're gonna use pip.
So I'm just going to type in pip install
and then give it the same name of the
folder and then the actual name of the
file that I'm gonna want to install. So
copy and paste it will now install that
into our environment. So that's good. Now
one thing we could do just to test that
is things up and running. We could just
quickly go into Python and then we could
go to our code and just use this import
from HDB CLI import dbapi because this
is the core of how you access the client
tools. Let's just now paste that in and
if this runs okay, which it does no
errors. It means that you've got your
Python driver installed and up and
running. Basically everything's good.
If you get an error message here then it
means something's not right that it
hasn't been installed correctly. Okay so
we can now get out of there.
We're basically ready to think about how
we’re gonna
run the code. So what have we got in
terms of our script here. Well if we have
a look we see we've got quite a few
lines of code.
Well not that many. 25. We’ve got imports
for TensorFlow itself because we want to
get the demo data the MNIST
demo data which TensorFlow has a nice
procedure that will download that for
you our on-demand and then we are going
to access HANA.
So for HANA we’ve got our
line seven from HDB CLI import dbapi.
We've got our connection string looks
pretty familiar. Notice about the address
we already set. The IP address in this
case which could be the host name. We’ve
got the database name which is our
tenant database where we're going to
load the data to, where we're connecting
to. Which in our case with HANA Express
the default tenant we're using is HXE.
Then the port number and that's 3
followed by the instance number which in
our case is 9 0 that default with
on our Express and then 1 3. Use
1 3 when you're going to connect
to a tenant database and then we've got
our user name
EML user and we've got the password that
you already saved earlier before we
transferred the file. Now we're going to
work with cursor. So we need to create a
cursor against our connection and then
everything we do will be against that
cursor. Now we don't need to do commits
explicitly because auto commit basically
is turned on when we're working with the
HANA Python driver. Which is slightly
different from the standard
specification that we talked about and
with the reference guide. So the first
thing we're going to do is to create our
table. So create table MNIST with two
columns. Label and image. Then we're going
to go ahead and read in the MNIST datasets
using that function we've got. We’re
going to use one hot is false that way
we'll get a number between 0 and 9 to
represent the the handwriting number
that it's going to be that we're looking
at and how many rows are we going to
bring in. Well previously we did it with
5 that maybe I'll say let's go ahead
and bring in ten rows this time. Totally
up to you how you want to make that work.
Then we're going to loop around ten
times and we're going to get the
features which basically is the image.
The binary image
and we're also going to get the
label which is this number between 0
and 9 which is basically the answer
of which handwritten digit it's supposed
to be that allows us just to verify
what we're actually testing at the time
and then we're going to insert both of
those into our tables. So just a simple
insert into. So it's very straightforward.
If you use SQL. If you've used any of
the drivers like JDBC ODBC or even
Python in the past then it's very
straightforward how we can do all sorts
of work with HANA. We can call, we create
stored procedures. Lots and lots of
capabilities in this latest Python
driver with HANA 2.0 SPS02. Okay so
that's what the code is actually doing.
Why don't we go ahead and run it. So
let's go into our SSH session. Fefore we
do that we should just make sure we
delete the existing table because I
haven't written into the script to
actually do any delete and the table
already exists. So just go ahead and
remove that table. So let's go there and
choose delete. There's actually a view
depending on it. So we need to make sure
that we drop the table and any other
objects and that table is now gone. So
we're in a good place let's go back to
SSH. So for that I need to just go back
home. So home and now we've got our
Python to HANA script. So I'm going to
run that Python and put in the name of
the script. Paste that in and we should
be good to go.
So it's run. It’s downloaded the data
into a temporary folder which is part of
that script and it should have loaded
some rows into a HANA table that it
created. So let's now just have a quick
refresh here. Let's look at the tables
again. Yep we've got a new MNIST table and
if we open the data. Okay this time we've
got actually five rows. It’s actually
looks like the very same data because we
didn't actually change it on the server
we just changed it locally here on
notepad but do you get the idea. If you
change that you will get a different
number of rows you can do whatever you
want. So that's it that's how we can
actually work using the Python driver. There’s
an alternative way that we can get data
into HANA or run anything in HANA from
Python calling out to HANA.
Which of course is quite different from
where HANA is calling out to
TensorFlow serving which might also be
running within a Python landscape. You
can find more HANA video tutorials on
our YouTube channel. If you'd like to be
informed as soon as new video tutorials
are published please subscribe to the
channel. You can also connect with us on
LinkedIn, follow us on Twitter and we
have pages on Facebook and Google+. Code
snippets and demo data for all of our
tutorials are published to GitHub. Thanks
for watching.