Welcome to the SAP HANA Academy. My
name is Bob and in this series of videos
will be looking at SAP S/4HANA. In this
video series will be looking at how to
extend S/4HANA with external data.
In this video we're going to look at
loading some data used in this video
series into Hive. Please note that this
video set shows certain features in
the SAP HANA database which may
require specific licenses even if you
are using S/4HANA. If you are using
your own S/4HANA system and are not using
the S/4HANA trial please contact
your account executive or partner
manager to see if you are licensed to
use these SAP HANA Database features:
They are SAP HANA Smart Data Access, SAP
HANA Smart Data Integration, creating a
virtual table in SAP HANA and
creating a Calculation View in SAP
HANA. So the assumption with this
series of videos is that you have access
to a Hive system running in Hadoop. So if
you don't you can follow along with the
SAP Academy’s series of videos on
installing Hadoop and Hive and you
should see a link appear on the screen
or down below in the video description
on where that playlist is. So if you
don't have access to Hive within Hadoop
and you can't provision a server in AWS
or any other cloud provider another
option is instead of connecting to an
external data source which is Hadoop
what we're going to cover in part 4 of
the series is connecting to an external
data source but which is a CSV and which
resides on your Windows remote desktop.
However it's a good idea for you to
watch the complete series of videos to
understand how we actually connect to
external data systems within SAP S/4
HANA. So in terms of using your S/4
HANA 1511 partner image to access Hadoop
that you've built in my example
I've built that Hadoop on an image in
AWS. What you'll need to do is add two IP
addresses to your
security group in AWS. Just to recap what
you have in your partner image is three
images essentially a HANA image, a Java
image both running on Linux and lastly
you have a Windows remote desktop. So the
only IPs that you have to add which you
can get from the right hand side are your
external IPs for your HANA and your
Windows remote desktop. Now if I go to my
AWS console you can see my Hadoop's
running and if I go to my security
groups and edit my security groups you
can see here I've added the two external
IP addresses. Again for the HANA
machine on the partner image and also
the Windows remote desktop and for me
those external IPs starts with 52.
Additionally on the partner image itself
here you can see I've installed chrome
and I'm accessing my Hadoop system on
port 8080. I can see that all the various
servers are up I've also included Spark
because I'm doing this for another
installation for a SAP HANA Vora.
Although you don't actually need Spark
and of course additionally how did I
install this? Well I installed PuTTY on
the partner image. Now if you're not sure
how to use PuTTY and PuTTYgen all you
have to do is go to Google and do a
search for SAP HANA Academy PuTTY and
a video will appear which is SAP HANA
Academy Live2: 2a Linux connectivity
to SAP HANA One but you can actually
follow through that video and it'll go
through an install of both PuTTYgen,
PuTTY and it will also show you how to
convert a PEM file to a PPK. Of course
this ultimately means that when I
launched and log into my Hadoop system I
can then access the system as you can
see here. So how do I get some test data
to do with airports into my Hadoop
system? Well we're going to use Hive. So
what I'll do is I'll run a clear and
I’ll do a sudo su and then I’ll login as
a
specific user by doing a clear. So I’m
going to log in as the Hive user. So I’m
going to do an su - hive. Again all of
these commands are available within our
GitHub repository and what we need to do,
if I do an LS you can see that there's
no file. What we’ll firstly need to do
is get a small CSV which is going to
contain some data. Of course eventually
you're going to join that to data within
S/4HANA. So here's a URL. Again
this URL will be available within our
GitHub repository and we’re doing is
running a w-get. We’re connecting to our
team's dropbox and we're taking a CSV
file called AirportWebServices and
we're basically storing it as a file
called AirportWebServices.dat. So
that's the first thing you'll need to do
so if I do an LS will see that the files
there. So what is contained within the
file? Well all you need to do essentially
is run a cat and then type AirportWeb
Services and you'll see exactly what's
in the file. So if I run it you can see
just to show you the last line.
Essentially you have an airport code, the
airport. You have an address. Then you
have the lat/long and a URL of that
specific airport. So again later on we're
going to do a very simple join based on
this airport code from data in Hadoop to
data which exists in S/4HANA. So how
do we get this into our Hadoop system or
into Hive? But what we need to do firstly
is run some commands. So I'll run a clear.
So the first thing you need to do is
just to show you is firstly we need to
create a folder within our Hive system
and I'm going to create essentially a
database called s4sha for SAP HANA
Academy, SAP HANA Academy. So HDFS space
DFS make der, which is a typical Linux
command. I’m going to make it in this
folder. How can we see if it's there? Well
if I just do an up and go back to the
make der, it's just a normal Linux command
within what's called
the tool HDFS. If you Google HDFS DFS
there's lots of company info on it
available on Google. All I'm going to do
is an LS to see does the folder exist.
So if I do an LS it should give us a
response that there's actually nothing
in the folder which is correct and if I
do an LS of the warehouse folder we
should see that we've got that s4sha
.db. Which essentially is a database.
So what we need to do now is create a
table in Hive. So if I do an LS we can
see that we've got that AirportWeb
Services.dat file but what we need to do
is create an empty table in Hive. Now
there are different ways to do this. This
is just the way I'm doing it. So what I'm
going to do is I'm going to launch Hive
by simply typing Hive and then we should
be prompted with Hive. Now of course the
assumption here is that your Hive is
running here and that you've got
no errors or alerts. So the first thing
you might want to look at is what
existing databases do you have. So if you
just type a show databases command. We
get the default database which exists
but we want to actually create a
database. So all I'm going to do is type
create database and then I'm going to
call it s4sha like so. So now when I
create that database all I need to do is
if I again run the same command as
before show databases. We can see now
that we've created that database and now
I can type we use s4sha and that
means we're actually we’re in
that database now. So of course now that
we've done that we want to create the
table. So this is the command to create
the table. I'll just paste it in and
essentially we're creating a table if it
doesn't exist in the s4sha database
called airportwebservices. Of course it
will contain those seven columns and the
CSV file that we're using or the DAT
file is the filter terminated by a comma.
So now if I run a show tables we should
see that we've created that table
airportwebservices but if I do a
select * form airport
webservices we should see that the
tables actually empty because there's no
data loaded into that table. So how can
we get data in? Well if I exit out of
Hive we need to put that DAT file into
HDFS. So the command that we do,
we use to do that is as follows. We’re
running a HDFS DFS. We’re putting a
- put. We're taking that DAT file which
is at the root folder for Hive and we're
placing it in the s4sha.db folder in
the airportwebservices folder like so.
Now just like before what we might want
to do is check to see if the factual
file remains. So if I run this a similar
command but with a - LS we're
just going to check that that folder
exists. So if I just remove airportweb
services and we go into the s4sha.db we
should see that we've got that sub
folder which is airportwebservices
like so. Now this last command really
depends on how you've installed your
Hadoop. So for me all I need to do is
make this folder accessible by a
specific user. So depending again on what
users you're using and what users you've
installed Hadoop with. For me I’m using
the I use the default settings when I
installed Hadoop. So I'm giving access to
the Hive HDFS user of that folder like
so. So when we've done that we can then
relaunch Hive and what we can do now is
select on that same table to
see if we've got data and then I'll run
a select * from that same table but
of course this time we should see that
we've got data and there we go that's
the data that we've loaded. Ok now that
we've done that and we've made sure that
we've actually loaded some data into
Hive. What we're going to do in the next
video is look at installing the SAP
components in order for us to connect
our Hadoop to our actual S/4HANA
system.