22.SAP HANA Vora - Functions and Integration.pdf

Aron MacDonald

SAP HANA Vora": Functions and Integration

® Rheinw inwerk® rk® Publishing Bonn • Boston

Trusted by over 1 million members

Try Scribd FREE for 30 days to access over 125 million titles without ads or interruptions! Start Free Trial Cancel Anytime.























What You'll Learn Learn to to use SAP HANA Vora to exte extend nd Apache Spark for SAP data. data. Start Start out by discovering basic operations and how to use them. Then explore how SAP HANA Vora enables you to enhance business functions in your applications, consume Hadoop data in SAP HANA, and consume SAP HANA data data in Hadoop. Hadoop. 1

G ettin g Started w ith SA SAP HANA Vora and Apache S p a rk ............

5

2

Transform Data w ith Apache Spark Spark .................................................

10

2.1

Using R D D s .................................... ............................................................................ .............................................. ......

12

2.2

Usin Using g Da taF ram es ......................................................................

24

Consum ing Data w ith Vora ...............................................................

35

3.1

Consum ing Hadoop Data ........................................................

35

3.2

Co nsu min g SAP SAP HA HANA NA D a t a ...... ........... .......... .......... ........... ........... ........... ........... .......... ......... ....

49

3.3

Cross-Consumption: Cross-Consumption: Join Join SAP HANA and Vora D a ta .........

58

3.4

Add itional Bus Busines iness s F u n ctio n s .................................................

61

3.5

Additional Information Information on V o ra ...............................................

63

3

4

5

..........................

64

4.1

Dow nload Oil Pric Price e Data Using Using Apache S p a rk .....................

64

4.2

Dow nload NY Times Times Articles Using Using Apache Spark Spark ..............

68

4.3

Store Ne t Incom e Result Results s Using Using SAP H A N A ...... ......... ...... ....... ....... ...... ...... .....

75

4 .4

Analyze Oil Data Using V o r a ...... ........... .......... .......... ........... ........... ........... ........... .......... ......... ....

77

W h a t's t' s N e x t ? ..... ........... ........... ........... ........... .......... .......... ........... ........... .......... ........... ........... ........... ........... .......... .......... ......... ....

83

Co m bining Apache Spark, Spark, SAP HANA, and Vora























1

Getting Started with SAP HANA Vora and Apache Spark

SAP HANA Vora (hereaf (hereafte terr ref refer erre red d to to as "Vora") "Vora") is SAP's new in-memor in-memory y query engine, which plugs into the Apache Spark and Hadoop frame works to provide interactive analysis of big data. Hadoop is an open-source software, designed to run on inexpensive com modity hardware, enabling businesses to store and process data at the petabyte scale. Hadoop is made up of a large collection of projects or libraries, covering areas like real-time data ingestion, document storage, and in-memory reporting engines. Hadoop is constantly evolving, and choosing the right project for your current needs requires careful consid eration. At Hadoop's core are several projects: »The Hadoop Distributed File System (HDFS) » YARN, a second-generation map/reduce framework and resource man ager » Apache Spark, an in-memory data-processing engine Over recent years, Apache Spark has evolved to become the new de facto standard data-processing engine for Hadoop; it’s fast and flexible and operates "in-memory" (when the dataset can fit). Vora expands upon Apache Spark by providing added business features as well as best-in class integration with SAP HANA (using an organization's live corporate data), enabling cross-consumption reporting and advanced analytics, at scale. Figure 1 illustra illustrates tes how how Hadoop, Hadoop, Vora, Vora, and SAP HANA might appear appear within an enterprise's big data architecture to manage transactional and behavioral datasets.























Ge tting Started Started with SAP SAP HA NA Vora and Apache Spark Spark

|

1

s l o o T c i t y l a n A y t r a P d r i h T

Transactional Figure Figure i

Behavioral

SAP HANA and Vora/Hadoop Reference Architecture

This E-Bite demonstrates, step by step, basic data transformation opera tions using Apache Spark and Vora's data consumption operations to get you started on your big data data journey. For a more detai detaile led d overv overvie iew w of Vora, Vora, please please check out SAP HANA Vora; An Overview, http://www.slideshare.net/SAPT http://www.slideshare.net/SAPTechnolog echnology/sap-hana-vo y/sap-hana-voraraan-overview. an-overview. All examples shown in this E-Bite are running SAP HANA Vora Developer Edition. SAP HANA Vora Developer Edition is free to try by registering at https://go.sap.com/cmp/syb/crm-xul5-int-voratrdm/index.html . Before Before we we jump jump into technical and and practical details, let's let's briefly briefly discuss discuss this software.























Ge tting Started w ith SAP HA NA Vora and Apache Spark Spark

|

1

Once you have followed the AWS installation steps for Vora, you'll be presented with the screen shown in Figure 2. This Vora launchpad pro vides a brief brief overview of your your newly newly created Hadoop Hadoop cluster, runnin r unning g the Hortonworks distribution of Hadoop (HDP) and Vora. The Vora launchpad provides a user-friendly way of adding and removing nodes on your test cluster and also has important links to other installed services, includ ing: » Apache Ambari A tool for provisioning, managing, and monitoring Apache Hadoop clusters » Apache Zeppelin A web-based notebook that enables interactive data analytics using SQL, Scala, Vora, and more Figure 2 shows the Vora launchpad. Click on the links in the top-right cor ner to access the installed services. HANA Vora

Cluster =

SAP HANA Vora Messages

Resources

22 hrs ago on Feb 21: 21:

Configured Configured cluster with with status suc ce ss

Ambari administration

AppBOOk

Spark

Zeppelin

MASTER

11

End User License Agreement

Figure 2

Vora Launchpad























Ge tting Started Started w ith SAP SAP H AN A Vora and Apache Spark Spark

|

Figure 3 shows the Ambari console, where more complex configuration and fine-t fine-tuning uning of your your Hadoop Hadoop cluster cluster can take take place. place. # !j l Amba Ambari ri

SAPHA NAV ora

Metrics

□ © MapReduce2 © YARN u .

__ __ 6 alerts Heatmaps Heat maps

| Dashboard | Services

Hosts 2

Alerts

Admin

config History History

Metric Actions * Q HDFS Disk Usage

Tez

DataNodes Live

Memory Usage Nameficde

© Hive

n Pig

0

© ZooKeeper

2/2 2/2

S e c o oS oS a r y K s m e N o d e

lif lift., t., il il,

2Data?lodes Data?lodes

PWrirlfj

Moce *

© Ambari Metrics © SAPHANAVora

CPUUsage

Actions

Cluster Clust er Load

NameNode Heap eap

NameNode RPC

r/* ------- 1-----r/*

10*. 5*

NameNode Uptime

22.2 hr Figure 3

Network Usage

ii%

ResourceManager Heap 4

f %

ResourceManager Uptime

22.1 hr

0.05

ms

NodeManagers Ltve

1/2

NameNode CPU

W IO

o.o%

YARN Memory

33^

Ambari Console

In this E-Bite, Zeppelin is the primary tool used for interacting with Apache Spark and Vora. When you first launch Zeppelin, you are pre sented with a list of the notebooks (documents) you are working on, as shown in Figure 4. Within each notebook, code can be created, saved, and executed. Figure 5 shows an example of a basic Apache Spark function in a Zeppelin note book. book. The The top half half of a notebook notebook tile, highlighted highlighted in in orange, orange, shows shows your your own custom Apache Spark or Vora code. The bottom half of the tile, high lighted in green, shows the results of executing the code.

1

























Ge tting Started Started w ith SAP H AN A Vora and Apache Spark Spark

Zeppelin

Notebook -

|

Interpreter

Welcome to Zeppelin! Zeppelin is web-based notebook that enables enable s interactive data analytics You can make beautiful data-dr data-driven iven interactive interactive collaborative document with SOL code 3nd even morel

Notebook

Help Get staled with Zeppelin documentation

Create new note DOOGOAWSTPCH OOOOOBmgFT 0 0000 000 0 DFBasics OOOOOHDFS QOOCOJSOMBasi OJSOMBasicc 0OOCOJSONOikla OJSON Oiklata ta O0000 NYTimes OOOCORDOBasi ORDOBasics cs O0001 Vora 1 0 Note 26CPSVBC4 0 Create Table 0 Session 1 0 Session 2 NOPE 0 ShedockHolmes 0 Table search 0 Word Count 0 Zeppelin Tutonai Tutonai Otabte2

Figure Figure 4

Community Please feel free to help us to improve Zeppelin Anycontribution are welcome' V Mail ng list A Issues tracking O Grthub

Zeppelin Main Screen

EZeppelin

Notebook -

0000 DF Basics Bas ics

Interprete r

c>:: uj 0

©

©

f in ish eo

s« 5( ( * 6s e r , ‘ R e v en e n u e *,*, i e e e . e e ) ,

('u se r,■»ever*«', weo.eo), weo.eo), < * v s e i v i x p « r , i « - , - j e e e .e .e e > ,

(**uei","Revenue", see.ee)).toCf('Co^«ny-,'Accounterouo“

CC*c»n>fl»:a n>fl»:aD-: D-: crg.» Mcl-<. $Nrl.s al.R* t*?r»n e - [foeperv,: st rin g, icccunt Jroup: st rin g, 1Ceepany| Accountsr Accoun tsroupl oupl Aacunt.uSOI |

cseil useij useil 1 *uei| 1 1

Revenoel Revenuel Ixpensel Revenuel

leee.ei seee.ei -jeee.ei see. see.e| e|

rfeublej rfeublej

O

o

oefav# -

uj

1























Transform Data w ith Apache Spark

Within each Zeppelin notebook, SQL can also be executed and visualized, avoiding the need to jump jump to external report reporting ing tools, especially during testing. Figure 6 shows an example of an oil price analysis performed using Apache Spark and Vora. This example will be discussed further in Section 4.

2

Transform Data with Apache Spark

Vora is an add-on to to Apache Spark, Spark, provi providin ding g enhanc enhanced ed SQL logic and business functionality. Apache Spark will continue to be used in conjunc

|

2
























engine that that supports general execution graphs. graphs. It also supports a rich set of higher-level tools including Spark SQL for SQL and structured data process ing, MLlib MLlib for machine learning, learning, GraphX for graph graph processing, processing, and and Spark Spark Streaming. Apache Spark has many features, but at its core are the three main fea tures covered in this E-Bite: » Resilient distributed dataset (RDD) RDDs are fault-tolerant collections of elements that can be operated on in parallel across your Hadoop cluster. There are two ways to create RDDs: paralleliz parallelizing ing an existi existing ng collection in your yo ur custom program program or referencing a dataset in an external storage system, such as a shared file system, HDFS, HBase, ase, or any data source source offeri offering ng an Hadoop input input for for mat. » Spa rk SQL SQL

Spark SQL is a module for structured data processing. Unlike the basic Spark RDD API, PI, the interf interfaces aces provi provided ded by Spark SQL provi provide de Spark with more information about the structure of both the data and the computation being performed. Internally, Spark SQL uses this extra information to perform extra optimizations. » DataFrames A DataFrame is a distributed collection of data organized into named columns. It is conceptually equivalent to a table in a relational database or a DataFrame in R/Python but with richer optimizations under the hood. DataFrames can be constructed from a wide array of sources, such as structured data files, tables in Hive, external databases, or exist ing RDDs.

|

2
























2.1

|

2

Usin g RD Ds

RDDs have a long long list of of functions for manipulating manipulating and transfor transforming ming data. Table 1 gives gives a brief brief overview of some some of o f the common common functions. functions. Essential Apache Spark Operations General

Math/ Statistical

map

sample

f il il t e r

randomSplit

Set Theory/ Relational

Data Structure/ I/O

union

i nt e r s e c t i on

keyBy zipWithIndex

fl f latMap

s ubt r a c t

zipWithllniquelD

ma mapPart i t i ons

disti nct

z i pP a r t i t i ons

cartesian

mapPartitionsWith it h Index

zip

groupBy

coalesce

repartition

repartitionAndSort Wit hi nParti tions

so sortBy

pipe

reduce

co co l l e c t

count

takeOrdered

saveAsTextFile

takeSample

saveAsSequenceFile

aggregate

max

fold

min

saveAsObjectFile

first

sum

saveAsHadoopDataset

take

histogram

fo forEach

mean

saveAsHadoopFile

to top

v a r i a nc e

treeA treeAg ggreg regate ate

std stdev

tr t reeReduce

sample-

saveAsNewAPIHadoopDataset

saveAsNewAPIHadoopFile
























|

2

For a more complete list of the functions, with examples, see http://training.databricks.com/visualapi.pdf .. ing.databricks.com/visualapi.pdf This This E-Bite -Bite uses several examples of of these functions functions to help get you started on your Vora ora journey. map Function

You can use the map function to transform a row of data from one type to another. another. For example, example, with with the the code in Listing Listing 1, 1, you can transform transform an RDD with with a single column, column, containing containing a list list of company codes, into a twotwocolumn structure. The first column will contain company code, and the second will contain an integer integer of of 1, 1, as shown in Figure Figure 7* val val companyCodeRDD companyCodeRDDx x = sc.paralleiize(Array("GB01", "USOl", "USOl", "UK01")) val companyC panyCode odeR RDDy = companyC companyCodeRD odeRDDx.m Dx.map(obj ap(obj => ((o obj.l)) Listing 1 map Function

//W P

FINISHED FINISHED t> « III ©

v«l ccnp» ccnp»ny nyc:i c:i<5 <5c© c©« « sc. paral lei i:e ( array ("o eor , 'u se r, "usei*, "um i’)) va l ccnpanjCofleRDOy » corpan><©eeRDOx.nap(oPj >
printlnC"\nsesults\n........... ') pr in tInC'cc tInC'ccnp npen enyC yCOPcRCO* : * ♦ cc*pan>'C oPeRC Ox.co llectO. «kStr ir.g( ")) prlntlnCccepany prlntlnCccepanyCcPeRC CcPeRCOy Oy : ’ ♦ ccnpanyCoPeROOy .c ol le ct () .n kS tr ir .g r, ")) coep coepan anyt ytoC oCeROOx: erg.apache.spark erg.apache.spark .re:M corp corpar arty tyCo CoaeRC aeRCOy: erg.ap ache.sp ark.rpo .»oo[ (String, :n t)] rapPartltionsKOOlses] rapPartltionsKOOlses] at nap at e>-:8i :8i Results ccrpanyCoeeROOx : ssei, USM, usei, u

Figure 7 map Results

filter Function

You can use the

f i l te

function, as shown in Listing

2,

to filter rows of


























I //Filter val coapa coapanyco nycodesc descox ox . s« .p er all eli :e( Array<‘6 60 i*, "usei’, 'user, 'utcox')) *usei*) 'Keep j s b i entries val coapanycooesooy coapanycooesooy coflcenycoo coflcenycooeRCtw eRCtw.fi It er (op) > 06 J

|

FINISHED t>

2

m €»

println("\nResults\n ........... ”) prlntln("coBpan>coaei»30x prlntln("coBpan>coaei»30x : - ♦ :83 cerean cereanyCoOeROOy: org.apach e,spark .roo.RO O{String] MepPertiticnsR MepPertiticnsROO[3« OO[3«7] 7] at fi lt e r at :8l Results CCflp*nyCC*0«“O «“OOx : 5801, USOl, uSM, IK01 cwanyCooeRCOy : usei, usei

Figure 8

filter Results

KeyBy Function

Following the example code in Listing 3, you can use the K e y B y function to create a new key column in the RDD. As shown in Figure 9, the first two digits of the company code are used to create a key. val val companyCodeRDD companyCodeRDDx x = sc.paralleiize(Array("GBOl", "USOl", "USOl", "UK01")) val com company panyC CodeR odeRD DDy = compan companyC yCode odeR RDDx. keyB keyBy(w => w . s u b s t r i n g s , 2)) 2) ) println(company println(companyCo CodeR deRDD DDy.col y.col 1e c t ( ) .mkString( ", ")) Listing

3 KeyBy eyBy Function Funct ion

ID
























|

2

val val companyCodeRDD companyCodeRDDx x = s c . pa r ai l el i ze(A ze (Arr rr ay( ay ( "GB "GB01". 01". "US0 "US01", 1", "US0 "US01" 1",, "UK "UK01" 01" ) ) . keyB keyBy( y(w w -> w.substringC0,2)) val company companyCo CodeR deRD DDy = companyCodeRDDx.coun companyCodeRDDx. countt ByKey() Listing Listing 4

countByKey countByKey Function

val coapanyCodeROOx ■ sc.para lleli:e(*rra y(*eeei *, "u se r, "usei*, ”UKei'».ke)«y(K UKei'».ke)«y(K ■> H.su tstringCe,2» va l
FINISHED 0

printlr( '\nResults\n .......... *)| pr ln tln r eospanycwesccx eospanycwesccx : ’ ♦ ee»pan>cocoCoPeROOy) conpany panyCe Cede deRO ROOx: erg.apac he.spa rk.r[ (Strin g, St rin g)] . Pape artltlensRCO [37i] at keyBy at :80 ccc.p ccc.panyco l, us -> 2 , 66 -> 1 ) Results COnpanyCOXteRDOx : ;, (US,US01), ( US,US01), (US,US01), (US,US0 1), (l*,UK01) (l*, UK01) CCnpanyCCdeRfiOy : Kap(UK -> 1, US -> 2, 66 -> 1)

Took 1MCondi

Figure to

countByKey Results

sample Function

Following the example in Listing 5, you can use the s a m p l e function to create a randomized subset of rows from the RDD. This sample subset, shown in Figure 11, might be particularly useful if you need to create rep resentative subsets to test predictive analytic functions when working with very large datasets.

Efl
























|

2

distinct Function

Following the example code in Listing 6, you can use the di s t i n e t func tion to create a new RDD containing only distinct (different) values. The results are shown in Figure 12. val va l companyCodeRDDx companyCodeRDDx = s c . pa pa r a i l el i ze ( Array Arra y ( "GB01", "GB01", "USOl", "USOl", "USO "USOl", l", ,,UK ,,UK01M 01M)) val companyC panyCod odeR eRD DDy = compan companyC yCod odeR eRD DDx.d x. d i s t i n c t ( ) Listing 6 distinct Function

//DISTINCT

val conp conpanyCcOfBOOx • sc.para lleli: e(Ar ra>< “G&«r, "u se r, 'u s e r, “uicer » val co«pan>Code»OOy • conpanyCodeROOx.distlmtO

FINISHED t> J I 81 ©

println ( "\nRes<«sei <«sei*O *OOx Ox : " . CcCcCcde*»y.ccll«t<).BkStrlrgr, ”)) ccr-panyc anycod odeR eROOx: org.apacK.s park.Pdd .s®o[str ing] > ParallelCo ParallelColle:w cc*ipafty': org.apache.spark.rdd.eco[Strmg] - i!apPartitionsROO[379] at distinct at :8l Results cc*p cc*pan an>
Figure t2 distinct Results

reduceByKey Function

You can use the r e d u c e B y K e y function, as shown in Listing 7, to create a

r
























I //KCOU Ce BY KlY val coapa oapany nyCo CodeRD deRDOx • sc.par allell :e(Ar ray (("G 60i" ,iee) , ('u s e r, va l coapanyCcdeRDOy • conpanyCodeR conpanyCodeRK>x.reduceB K>x.reduceByKey(_ yKey(_ • _ ). co ll ec t

10 0 ),

(-user, (-us er ,

3e e ) , ( ' A u e i " , i ee ee > ) >

FINISHED t> JK

prlntln( '\nftesults\n ........... •) pr in tl n( "ccapan apanyC yCod odeR eROOx : ' • coapanyCcdeRC©x.collect< coapanyCcdeRC©x.collect<).akString<", ).akString<", - )) pr in tl n( "ccap "ccapan anyC yCod odeR eRCO COy : * • ccapanyCcdeR ccapanyCcdeROC^.a OC^.akStnng kStnngC’, C’, *» conp conpan anyC yCod odeR eRPO POx: erg.apache.spark.rdd.RDO[(String, erg.apache.spark.rdd.RDO[(String, int>] ParallelCollection*oo[383] a t pa ral lel ise at :se :se ccnpan ccnpany y CodeRD deRDCy: Array(( Str ing , In t>] > Array <(C8e i,iee) , ( Au ei,l M), ( uS»1,a M)) Results cenpanycodeRXx : (6sei,iee>, (usei,iee>, (usei,3ee), (Auei,iee) coapanycodeROOy : «*eei,iee>, (a m i , tee), (usei,«ee)

Figure 13

reduceByKey Results

union Function

Following the example code in Listing 8, you can use the u n i o n function to create a new RDD containing the union of rows based on two identical RDD RDDs, as show shown n in in Figur Figuree 14. This fun funct ctiion is is simi simila larr to an an SQL UNION statement. val val companyCodeRDD companyCodeRDDx x = sc. pa ra il el i ze(Arra y( ( "GB "GB01",100), 01",100), ("US ("US01", 01", 100), ("US01\ 300))) val com company panyC CodeR odeRD DDy = s c . p a r a l l e i i z e ( A r r a y ( ( "AU "AU01", 01", 100)) ) val companyCo companyCodeRD deRDDz Dz - companyCodeRDDx. companyCodeRDDx. union( uni on(compan companyCode yCodeRDD RDDy) y) Listing 8

union Function

|

2

BP O
























|

2

and a master data table containing reporting attributes. This function is similar to a SQL join statement. In Listing 9, two RDDs are joined based on a shared column "company code" to lookup the country code attribute contained in the second RDD. The results are shown in Figure 15. val val companyCodeRD companyCodeRDD D = sc .p ar al l ei i ze (A rr ay ( ("G ("GB01”, 01”, 100), ("US ("USOl", 100), ("USO ("USOl". l". 300), ( "AU "AU01", 01", 100)) ) val companyCodeAtt companyCodeAttrRDD rRDD = s c . p a r a l 1el i z e (Arr ay( ( "GB "GB01", 01", "GB "GB"), ( M US01", 01", "US"))) "US"))) val companyCodeJoinRDD companyCodeRDD.join(companyCodeAttrRDD).col 1ect val companyCodeLeftJoinRDD = companyCodeRDD.1eftOuterJoin(companyCodeAttrRDD).collect Listing 9

join Functio Function n

val coapa coapanyc nycaj ajeso esoo o . sc.paraiieii:e(Arra>«"G Ber,iee), (“us er, lee), ('u se r, 3ee),(*Auer, 3ee),(*Auer, iee)j) val coapa coapan> n><© <©de deAt Attra traoo oo • s e.pa relleli:e( Arra >(CG eer,“G8"), eer,“G8"), (“u se r, *us*) *us*))> )> val coapanjCodeloirftOO ■ co«eer.yCo :8* :8* nyCoêAttrRO AttrROO: erg.apache.s park.r« .ROO [(String, str ing )] ■ PerellelCcllectionR PerellelCcllectionRDO[389] DO[389] at pa ral lel ize a t :79 :79

FINISHED t> X ffl ©


























I // fi a t nap - Split a coma deliatteO rot val saccatasoc • sc.paraliellie(Arra,("os«i,usoi,usei,Auei*) ) sapoataftco.flatMap:8e rei936: Arrayl Strlng] • Array(GW1, usei, use i, usoi, «J01) «J01)

Figure 16

map and split Functions

Listing 11 11 uses the map and sp s p l i t functions functions to transf transform orm a single column column of data that contains comma-delimited information into two separate col umns, as shown in Figure 17. "US01.100"

2

FINISHEO t> 5S ro

flatMap Results

val sapDataRDD = 11 el i z e ( A r r a y ( "GB01 100"

|

"US01,300" "AU01.100") )
























val data = sc.parallelize(Array("US01 2016 01 00100000001 20160115" "US01 2016 01 00100000002 20160115" "US01 2016 01 00100000003 20160115" )) val Pattern = """(\S+) (\S+) (\S+) (\S+) (\S+) ...... r / / Crea Cr eate te a cas e c l as s fo r Fi nanc na ncia ia l Docum ocumen entt Header. case cl as s financialDocum financialDocumentHe entHeader(com ader(companyC panyCode: ode: St ri ng , year: String, month: String, documentNumber: String, postingDate: String

)

def parseFinancialDocumentHeaderLineCline: String): financialDocumentHe ader ader - { val patternMatch = Pattern.findFirstMatchIn(1ine)

|

2
























Execute Linux Commands

In some cases, executing Linux commands directly from Apache Spark, which would make it unnecessary to open additional sessions to search for files in HDFS, can be useful. The example code in Listing 13 shows a basic Hadoop Hadoop listing listing command, command, the results results of of which which arc shown in Figure 19. i m p or t seal sea l a .s y s .p r o c e s s ._ val IsResult = S e q ( " h a d o o p " , " f s " 1s " ,"hd fs :/ /ma st er .clus t e r :8020/ u s e r/ vo ra /" ).!! ).!! Listing 13

Execute Linux Command from Hadoop to Search for HDFS File

|

2
























status = fs.1istStatus(new Path("/user/vora")) println("\nDirectory Listing After:") status.foreach(x=> println(x.getPath)) Listing 14

Delete and Create HDFS Directories

Directory Listing Before: Mfs: // M ttc r. cl uste r: 802®/use 802®/user/vcr» r/vcr»/. /. Tr*s Tr*sh h Mfs ://MS ter. c lust er: 802®/user/vcra/. 2®/user/vcra/. hive Jars Jars Mfs: //M Ster. d us ter : 8020/user/ 8020/user/vcc*/. vcc*/. sp»rkst» sp»rkst»ging ging Mfs ://MS ter. clu ster :SO20/user/vc :SO20/user/vcra/. ra/. staging staging Mfs://MSter. duster:802©/uscr/vcr»/lir>eiten Mfs://Mster.cluster:8020/use Mfs://Mster.cluster:8020/user/^d/line r/^d/lineite*i3SON ite*i3SON Mfs: //m aster. c lust er: 802®/user/vcr»/li 802®/user/vcr»/lineite«0 neite«0 Mf s: //MSter. cluster: 8 0 2 8 / user /ve ra/1 lnel tenfarquet Mfs://n aster. cluster : 8 ®20 /user /vcra /natlo n Mfs ://MS ter. du ste r :8®2®/u :8®2®/user/ ser/vcra vcra/r> /r>eti eti©n. ©n.g:.o g:.ore re Mf s: //aa ste r. clu ster :8020/user/vcra/peop 8020/user/vcra/people. le. parquet parquet Mfs: / /M Ster. cluster: 8020/user/vora/regio 8020/user/vora/region. n. srappy. parque parquett Mfs ://MS ter. cluster :8®20/u :8®20/user/ ser/vcra vcra/sap /sap&at &ataa Mfs ://MS ter. d us ter: 8020/user/ 8020/user/vcre vcre/sapOe /sapOetePer tePerquet quet Mfs://M Ster. cluster:8020/user/vcra/te cluster:8020/user/vcra/test st .csv .csv Mfs: //M s ter . clus ter: 8020/user/vera/tes t.parquet parquet Mf s: / / jee r. clus ter MJe/user/veea/Bera.eeuAt

|

2
























|

2

import import org . apache.hadoop.f apache.hadoop.f s . FileSy FileSystem stem import org.apache.hadoop.fs.Path val fs - FileSystem.get(sc.hadoopConfiguration) fs.delete(new Path("/user/vora/sapData"), true) val sapDataRDD sapDataRDD = s c . para 11 el i z e( Ar ra y( "GB "GB01, 01, 100", "USO "USOl.lOO l.lOO”” , "USO "USOl, l, 30 0" , HAU01.100") 01.100") ) sapDa sapDataR taRDD DD.repa .reparti rti ti o n (1 ) .saveAsTextF .saveAsTextFi1 i1e("sap e("sap Data") Listing 15

saveAsTextFile Function

I //sa ve two to HOfS inport erg. apache,haccop.fs.FileSyster apache,haccop.fs.FileSyster inport erg.apache.racoop.fs.Path val fs F*leSysten.get(sc.hadoop*, e>*, ’uS»i,iM*, ’uS»i,iM*, sapoataROO. repartition 1 ). saveAsTe*tFile(" saveAsTe*tFile(" sapoata")

FINISHED C> JJ « @

)


























2.2 2.2

Using Da taFram es

DataFrames work with structured data, similar to an SQL table but have a much much shor shorte terr list of available functions when compared with with RD RDDs. Data

|

2
























Print Schema

After creating creating an RD RDDf it may may be be useful to see its definition definition using using the p r i n t S c h e m a function (Listing 18), similar to an SQL D E S C R I B E table. The results are shown in Figure 24.

|

2
























filter Function

The f i 1ter function can be used to filter rows. Listing 20 shows different syntaxes for performing the same filter operation. The results are shown in Figure 26.

|

2























Transform Data w ith Apache Spark Spark

//co mb ini ng f in e r t end s ele ct

| Coreany Coreany |Anc
I useil usei l

seeel

I uSdll uSdll

-3© -3©0«l 0«l

|

Fi n i s h e d D> D>

2

HJ ©
























//C oun t O is tin ct c o a p a r jc jc a t a O f . « « ( < o w i t o i i t i n c ? ( * c o n pa pa n y" y" ) ) . s ho ho w( )

|CCUNT(OESTINCT Conpany Conp any)|)| Jl

|

2

FINISMEO t> 51 QJ ®































































































































































































































































































































































































































































































































































































































Co mb ining Apache Spark, Spark, SAP SAP HA NA, and Vora

» SAP HANA Vora— Vora—Troub Trouble lesh shoot ootiing http://scn.sap.com/blogs/vora/2015/12 http://scn.sap.com/blogs/vo ra/2015/12/09/sap-hana-v /09/sap-hana-vora--troubleora--troubleshooting » Tagged Quest Questions, ions, Stack Stack Overfl Overflow ow http://stackoverflow.com/questions/tagged http://stackoverflow.com /questions/tagged/vora /vora

4

Combining Apache Spark, SAP HANA, and Vora

With some basic knowledge of Apache Spark functions and Vora, we are n o w

r e a d y

to

u s e

t h e m

t o g e t h e r

to

t r a n s f o r m

a n d

a n a ly z e

d a t a .

In the following section, Apache Spark will be used to load and transform New York Times news articles relating to oil over the past oil price data and Ne ten years. SAP HANA will also be populated with net income figures from several major oil companies for the same years. Finally, Vora will be used to combine and analyze these datasets, which are stored in Hadoop HDFS and SAP HANA.

4 .1 .1

D o w n l o a d O i l P r ic ic e D a t a U s i n g A p a c h e S p a r k

In this section, we will use Apache Spark to retrieve oil price data and store this data in Hadoop HDFS for subsequent processing by Vora. As shown in Figure 70, the U.S. Energy Information Administration (http://www.eia.gov/opendata/ http://www.eia.gov/opendata/ )) has an API for downloading open data sets, such as oil price data, at http://api.eia.gov/series/?api_key=YOUR_ API_KEY_HERE&series_id=PET.RBRTE.D.

|

4
























iK&pmbnl Statute! i t An*i>fU U . S . E n e r g y

I n fo r m a t io n

Sources & Uses »

A d m i n i s t r a t io io n

Topics Topics »

Geography *

OPEN DATA API BROWSER

A

P I

Q

u e r y

B r o w

r»ON»

EXCEL ACO-IN■

s e r

GRAPHS &MA &MAPS

BULKI

Search by: by: • K e y w o rd rd s )

0 Senes ID

BA Data Sets > Petroleum > Prices > Spot Prices

APICAL*.

hltp: api.eia.oov *«riet'?ap1_key-YOUR_API_K *«riet'?ap1_key-YOUR_API_K EY_HERE**erie»Jd-PET.RBRTE.O Click Click Children Ca tegories to

TO USE

txcv.se data sets Click through the tree of SERIES

Europ e Brent Spot Price FOB. Oail Oaily y

NAME

am iab le sene s until until you reach a Child Child Sen es Click Click Child Sen es to dsplay

SERIES

10:

PET.RBRTE.O PET.RBRTE.O » Show me ho* to en-bed en-bed a

a chart of the data .

API can and sene s information information is listed at

--Figure 70 U.S Energy Energy Information Administration Administrat ion AP API

To access this data, you you must must get an AP API key. Sign up for free free access at http://www.eia.gov/opendata/register.cfm.. You'll receive an email with http://www.eia.gov/opendata/register.cfm your your API API key, key, as shown in Figure 71. 71. InM ftmimtl .ft— ftmimtl .ft— .On i t A m t tf tf m U .S .

E n e r g y I n f o rm a tio n

A d m i n is tr a ti o n

Thank you for registering The following is your unique EIAAPI key to use with the EIAAPI.

Figure Fig ure 71 71 U.S Energy Information Informat ion Administ Admin istrat ration ion API API Key

The example code shown in Listing 58 can be used to call the API, down load the JSON response, explode the first level of the JSON, and finally display these results to the screen, as shown in Figure 72. // / / Fu nc t i on conv co nver er ts Remote emote JSON URL Response t o a Data Frame rame def getJsonUrlAsDF(url: String): org.apache.spark.sql.DataFrame = { val client

|

4
























org.apache.http impl.client.HttpClientBui impl.client.HttpClientBui Ide r.c rea te ( ) . bui 1d() val request = new or g.apache .htt p.clie nt.metho ds. HttpGet(url) val response - cl ient.execute(request) val handler = new org. apache.ht tp.i mpl.cli ent.Basi cResponseHan dler() / / Ge t Response St ri ng var responseString = handler.handleResponse(response).trim // / / Co nv er t to RDD var resp respo onseR nseRD DD

= s c. p ar al 1eli ze ( res pon ses tr ing :: Nil )

// / / Re t ur n Data Frame rame return sql Context .read. js on( respo respons nseR eRD DD).toD ).toDF() F() //Download // Download Lat La t es t Oil Data var responseDF = getJsonUrlAsDF("h getJsonUrlAsDF(" h t t p : / / a p i . e i a . g o v / s e r i e s / ? a p i _ k e y = &seri KEY>&ser i es_i es _id=P d=PET. ET. RBRTE. DM) // Exp lo de JSO JSON and and Se l ec t Relevant Rele vant Coluns var o i 1DataD 1DataDF F responseD responseDF.w F.withC ithColum olumn("D n("Data", ata", ex plo de( $"s eri es . da ta ") ) .withC .withColu olum mn("Ele n("Ele ment", explode($"Data")).select("Element") oilDataDF.show(5) Listing

58 Download Downlo ad Oil Oil Price Price Data

//F un ct io n co nv er ts Remote JSC* UK Resp onse to o D Oto Frame Cel get3S®rvrlAJ©*'aae • {

FINISHEO t>

val client val val r e s p o n s e

. © r g . apache.nttp.iapl.client.Httpclientajilse'.createO.Ovliao new e r g . apache.http.client. etheCs.wttpGettu’l)

val handler

- ne ne w o r g . a p a c h e . h t t p . i n p l . c l i e n t . e a s i c ? e s p o n s e H e n d l e r ( )

II W ©

client.execute(refiuest)

//Set Respon sponse se String v a r r e s p e n s e i x r x r g . h a n d l e r . n * n a l e * t s e c n s e ( r « s e c rs rs 0 . t n i a //C on ve rt

to

var reiso nit'rc

s c . p a r « l l e l i : e ( r e s s o n i e s t r l r * : : M i l) l)

// Re tu rn oo to Frame

>

r e t u r n s a l C o n t e x t . r e a d . ; so so n < r e s p « r .s .s e « O C ) . t o O '( )

//oounl //oounlood ood lotest Oil ooto Y*r r
a pj

r«v>»jeriej_ip^tT.tg*Te,C>*) r«v>»jeriej_ip^tT.tg*Te,C>*)

// tx pl oO e JSC* ono Se le ct Re lev an t Col ins

var o ilratsof

. r e s p o n s e s . A l t h c o l i a w r c a ta ta * , e * p : e © e ( $ '; e r ie ie s . < J at at a - )) )) . « il il t h co co l u B n { - E : e» e» e n f t e * p l O «e «e { $' $' O at at a‘ a‘ » . s e l e c t ! ' E l e r i e n f )

oitoatecc-»•» »< »< ) g e t l s o o u r i A s o a : ( i r l : S t r i n g ) © r g . a p e cn cn e . s p e r l:l: . s q l .O .O d T e « r e a e r e s p o n s e s : c c g . » p a : n e . s p * * < . s a : . o » t » ! ' » - * « . [ r e q u e s t : s t r v c t < e c * » a n d : s t r i n g , s e r i e s _ i e : s t r i n g > , s e r i e s: e r r e > < s t r v e t < c o p > r i g n t : s t r l n g , d e t e : e r r e y < e r r a > < s tr l r t g » > , © e s < r i ptlen:strlng,en d:strln< ,f:string,ni«e:strln g,serles.l(l:strlng,s« ijê:strlng,start:strlng ,unlts:strln*,u nU ssftert:strlng,up Jeted:strlng ») o i l D at at a O * : c r g . a p a c h e . s p a r k . s q l . O a t a i r a a e ( E l e m en en t : a r r a y < s t r i n g > ) llenerrtl | L is is t !2 !2 e i 6 e2 e2 « e , n . . . | lList:2exee2es, 32...I | L l S t:t: 2 e i6 i6 © 2 © * , 3 2 . . . |

U:st::ei6«2e3, IUSt:2»l«02»2,

Figure 72

M—| . .i 32

Oil Data Initial Results

|

4























Co m binin g Apach e Spark, Spark, SAP SAP HA NA , and Vora

|

4

t>

Q|J

From the above results, we aren't able to make sense of the row listing. We can check the schema again with the command shown in Listing 59. The results are shown in Figure 73. oilDataDF.printSchemaC) Listing 59

Check the Schema

I D * . pr pr iin n tS tS ch ch eM eM () ()

FINISHED

root I-array (nullable ■ true) I I-- elene elenent nt:: strinj «onta «ontain injH jHul ulll ■ true) true)

Figure 73

printSchema Results

In this case, we aren't able to explode or select the columns any further. We need to perform some RDD map operations to split the row into mul tiple columns and then save the reorganized data to HDFS by using the code in Listing 60. The results are shown in Figure 74. case cl as s o i 1Price St ruc t( Date: j ava . s q l . Date, Date, Price Price_B _BB BL: Double ouble)) val o i 1DataFix 1DataFixedD edDF F = o i 1DataDF 1DataDF.rdd.map( .rdd.map( row row => < var rowStr ing = row row(O) (O) . to St ri ng var rowSplit = rowStr rowStrii ng. subs tri ng( 5, row rowStri Stri ng. length -1). -1). spl spl i t ( ",") // / / Dat Da t e SQL fo rmat rm at t i ng var dateStr = rowSplit(O) var simpleDateFormat: jav j ava. a. te xt . Si mple mp le Dat Da t eF or mat ma t = new j ava .t ext .S imp le Dat eFo rma t( "yyy "yyyy yMMdd"); "); j a v a . u t i 1 .Date j a v a . s q l . Date Date

var ut il Date = si mpleDat mpl eDat eFormat eFo rmat . p a r s e ( d a t e S t r ) ; var sqlDate = new j a v a . s q l . D a t e ( u t i 1Dat e. get ge t Ti me( )) ;

: :

//

Return in Structured Format

oi1PriceStruct(sqlDat e , rowSplit(1).toDouble) }

) . to t oDF()

@
























|

4

o i 1DataFi ataFi xedDF. rep art i ti o n (1 ) .s ave( "oi 1Data Par quet ", "par quet ") oi1DataFi xedDF.sort($"Date".desc)show(50) Split a Row into Multiple Columns with RDD Operations

Listing 6o

FINISHED C> ; ; ffl ©

case class oilPriceStruct( Oate: java.sql.Date, Frice_BBl: Double) val oiLoataFtxed oiLoataFtxedDF DF ■ eilcata©= .reo.naji row »> { var rowstring po«<:>.testring var ro.*Spli ro.*Splitt - rowstring.substrings,r©«iString. length-l) .s pl it( “,*) fo rm at tin g //Cote var datestr var sinpleoateFemat:java.text.slr.pieoat sinpleoateFemat:java.text.slr.pieoatesorf!at esorf!at var utiloate :Java.util.Oate var sqlOate sqlOate :java.sol.Oate

■ ro»split( ) ■ new new java.text.siôleoateFematCyyyjmJd’ ); - sl«pleOateForfta sl«pleOateForftat.parse( t.parse(dateStr); dateStr); • newjava .sql.Cate(uolOate.getT iaeO);

//Return t n structured format oilPriceStruct(sqlDate , rowSplit(i).tcOocble) ) ).tOOF()

oilOataFixedOF.registerTenpTaele(“oilOata’) oilDataFi
Odte|Price_8Bi|

12 81 81 6 -8 -8 2 -8 -8 8 1 I2816-82-8S| 12816-82-841 12816-82-831 |2816-82-82 j

Figure 74

3 1. 1. 6* 6* | 32.3S| 32.76| 32.38| 38.9sj

Oil Data Final Results

We now have the results saved in HDFS in the Parquet format.

4.2

D o w n l o a d N Y T i m e s A r t ic ic l e s U s i n g A p a c h e S p a r k

The New New Yo York Times has an API that will return ten articles (per page), based on a specific search criteria. A maximum of 100 pages for a specific search can be returned. In the following steps, we will use Apache Spark to automate the down load of all Ne New York Times articles with "oil" in the title over the past ten years and store the results in Hadoop HDFS for subsequent processing by Vora.


























|

4

Sign Up for API

First, as shown in Figure 75 and Figure 76, sign up for access to the API to search for articles at http://developer.nytimes.com/ http://developer.nytimes.com/ .. W C*rXn»|MkCI»» v D e v e lo lo p e rs rs

Signed n as aron. mscoona’o Events

&£!s &£!s

Bog

Forum

Getting Getting Starte d

Gallery

"he Times Developer Network is our API clearinghouse and community. Here's how to get started

API Ccnsde

1 Reou R eou esta n API kev 2 Read the a f i t i t t u m t m a m £a q an d Terms of Use Use 3. Use the A&I Tod to exoenmen t without writing writing cede A Browse the appi&atigflfiallgf t 5. Connect with with other developers in the fcrurr

Overview

Keys Forum

Gallery

Reque Request st a NYTimes API API Key

Times Developer Network Application Registration Thank you for registering. Your API Keys:

Article Search API

API Console K

e

y

Careers

You already know that NYTimes.com NYTimes.com is an unparal leled sour ce of news and information. But now it's it' s a oremter sou rce cf da ta, too — why why just read th e news when you can hack it?

Keys

Available APIs

Open Source

Log Out

Welcome

Overview Available APIs

Figure 7 5

My Account

:

Application Koy: Status: Registered

Key Rate Limits 10

Calls per second
























Note The NY Times API is free to use; however, there are limits on the number of searches made per second and per day, so take care when using Spark logic.

Perform a Single Search

Next, we will use Spark to perform a single search for articles where "oil" is mentione mentioned d in the article title. title. We also need to perform some date type manipulation to allow the results to be compared with the oil price data loaded earlier in Section 4.1. Execute the the code shown in Listing 61 61 to use the the AP API to perfo perform rm a single page search. The results are shown in Figure 77 and Figure 78. import org.apache.spark.sql.functions.explode import org.apache.spark.sql.types.DataTypes._

//

Conver t TimeS TimeStam tamp p St ri ng to Date Date . . . (NOTE: New cas t( Dat eType) eTy pe) work s f r o m Spark 1.5) def def dtStrToDate(date Str: String) String) : ja va . s q l .Date .Date = { i f (da te Str != nul 1) { var UTCDateFormat:java.text.SimpleDateFormat new java. ja va. tex t. Simpl eDat eFormat( eFor mat( "yyy y-MM-dd*T' y-MM-dd*T' HH:mm :mm:ss"); UTCD TCDateForm ateFormat. at. setTi meZone(java. uti l. TimeZone.get TimeZone("UTC")) ; var var util Date :j ava . u ti 1. Date UTCDateFormat.parse(dateStr); var sqlDate sqlDate :j av a. sq l. Date new ja v a. sq l. Date(u Date(u t i1Date.ge i1Date.getTim tTime()); e()); return sqlDate } else return return null null val val dtSt rToDate_udf = udf( dtSt rToDat e _)

|

4
























val val bingUr1 bingUr1_p1 _p1 = "https://api.datamarket.azure.com/Bing/Search/vl/ Composi omposi te?Sources=% te?Sources =%27new 27news%27 s%27&Q &Query=% uery=%27i 27i n t i tle% tl e%3A" 3A" val bingUrl bi ngUrl_p2 _p2 = "%27& "%27&$forma $format=json&$skip=" t=json&$skip=" / / v a r bingll bin gll rl Ful 1 = bi ngUr l_pl l_ pl + sear se arch ch + bingUr bi ngUrl_ l_p2 p2 + page page / / v a r bingll bin gll rl Ful 1 = "h t t p s : / / a p i . i . datamarket.azure.com/Bing/Search/vl/ datamarket.azure.com/Bing/Search/vl/ Composi te?Sources=%27news%27&Query=%27i nti tle%3Aoi1%27&$format=json& $s ki p = l " var bingUrlFull = exampleUrl val client = org.a pache.http.impl.client.HttpClientBuiIder.create().bui 1d() val r equest = new org. apache. ht tp .c li en t. me th od s. HttpGet(bingUrlFul1 HttpGet(bingUrlFul1)) / / request.setHeader("Authorization", "Basic UXROSUlvUXNDTjJXMnlIdzdPazZYU mk2VXNrV3dwMWt3UFpwcnVvZlpabzpRdE5JTW9RcONOMlcyeWV3N09rNlhSaTZVc2tXd3Ax a3dQWnBydW9mWlpv"); val respons responsee = cli ent .ex ecut e(re ques t) val handler = new or g. ap ac he .h tt p. im pl .c li en t. BasicRes BasicResponseH ponseHandler() andler() var bingResults = handler.handleResponse(response).trim / / r e t u r n bingUr bi ngUr lFull lFu ll var bingResultsRDD = sc.paralleiize( bingResults :: Nil) val val resultsJSON resultsJSON = sqlCont ext. read.j son(bi ngResult sRDD) var resultsExpDF = resultsJSON.toDF() .withColumn("Docs", explode($"response.docs")) if ( resultsEx resultsExpD pDF.coun F.coun t() > 0 ) { resul tsExp tsExpD DF =res ult sExpDF. withColumn("web_url", withColumn("web_url", $"Docs.web_url") .wit hColumn("source", $"D $"Docs.s ocs.s our ce" ) .withColumn("pub_date",dtStrToDate_ udf($"Docs.pub_date")) / / .wi t hCol umn( "headl "he adl ine_ in e_ main", $"Docs.headline.main") .withColumn("news_desk", $"Docs.news desk") .wi .wi thCol umn("sec ti on_ on_ nam name", e", $"Docs. sect ion_ name") .wi thColumn("subsection

|

4
























return resultsExpDF

var x = searchNYTimes("q=oi1+price&fq=news_ desk:(%22B desk: (%22Busine$$%22,%22E usine$$%22,%22Energy%22 nergy%22,%22Envi ,%22Envi ronment%22,%22Financial %22)& begi n_date=20070101&end_dat n_dat e=20070101&end_dat e=20071231" , 1) x . show() x.count() Listing 61

Single Page Search for "Oil" in Article Title

rms *€d

i n p o r t e r g . a s ac ac * e . s p a r . . s a l . * . r e t i e n : e p l o e e i n p o r t c ^ £ . * s ac ac » « - s p a r k . s a l . t y p e s . C a t a T y p e s . _ //C on ve ne TimeStamp S tri n g to Dat e . . . (MOTT: Mew c a s t ( D o t e T / p e ) m a r k s f r o m S p o t * 1 . 3 ) def etJtnTsDace(dateit' - ..- g ) :a a :ql.Ba ti * ( if (dateStr ! null) { v a r iT iT C O a t e ' e r ra ra t ; a a . t e - t S i n p le le O a t e ’ e r r a t ew ; a a . t e . t . S i n p l e O a i e * e r * e t ( * ) yy y - ‘ I ' - s s *T (♦< w * n ew U T C D a t e 'e r r ae ae s e t T i « e l s * e ( j a . a . u t i l.l. T i n e J © " e . g e t T i n e I c n e ( ’ C " ) ) ; var wiilDate :Java.util.Oa t* U T C O a t e * o n n a t .p .p e ' s e( e( d a t e S t r) r) ; var sal?ate : J a v a . s q l. l. D a t e - n ew ew j a v a . s q l . C a t e ( u t i l D a t e . g e t T i r e ( ) ) ; return sqlCate ) ele* return null

>

val dtItrTpDate_udf * udf(dtStrTcCate _)

d « f a* a* a r« r« « .V .V r T ja ja e * ( s e a r c h - S t - i - r , p a | e Thread.sleep(196) val e-arpleU rl * ’http:

val bi rf .r l^ l i val MrgUrl_p2 •

http :

J n t ) : e ' g a p a ch ch e s p a r e . s q l . C a t e ' r a n e

(

s p i . n y t i n e s . c o n s c . s e a r c h / 2 . a r t i e l e s e a r c h . ; s cn cn ' - ' * s e a r c h

’ l a p i-i- '« e y »< »< > O JP JP ' 5 ' > i s a » e = ' ♦

p ag ag e

. a s i . « a t » n a r i . e t a : w '-'- e .c .c o n . J i n j /S /S e a r c l v 'v l / C o a p o s i t e ? Jo Jo u < - c e s « % 2 7 n e v . s M 7 l ^ w e r y ^ t t 7 i n t it it l e \J \J i

// v a r O in p O if w tt • b \n fjri _ p l » s e a r c h r kinp
c l i e n t * e r g a p ac ac he he n t t p . i n p l . c l i e n t . r tt tt t p C l i e n t 8 u i ld ld e r . c r c a t e ( ). ). b u i 2 d ( ) r c s . c a t • n ew ew o r j . a p a c r e . h t e p . c l i e n t . n e t n ed ed s . h t t p 6 e t ( b i n j ' .r .r l * J l l ) q u e st . s e tM eo d er fA /r h or tr o rt o n * , 'S a sic UXKOSUl\<&X) TjJXHrUd:SPazn‘lM*2VXHrV3P*trc3orp*cn)K:Lpob:pXtS!}]TV9M TjJXHrUd:SPazn‘lM*2VXHrV3P*trc3orp*cn)K:Lpob:pXtS!}]TV9M H0nLcyeikf3MP9r*UiSaT?\/c2TXi3A xaSdQUn6yiiU9rtap-.’~), respprje * client.erecute(reqiM St) h a r a l t ' e n ew ew e r g . a ; ac ac * » . h t t p s n p l . c l i t ' t . 3 s : i : 5 e :p :p c ' : tM tM a * d l e r ( ) bingics-ilts a handler.*»«3le5e:pe-n:«(res;s-se).trin

v a r b i -j -j S e s w lt lt s S C O a s c . p a r a l l e l i s e ! b i r g f i es es w l t s : M i l ) v a l r e s . lt lt s 3 5 C « a s q l C o rt rt e i t .r .r e a d . ; : © n ! t i r » S s s . l t : S 3 0 )

v a r r e s w l t s! s! ' .p .p C * » r e s u l ts ts 3 S O h . t e O * ( ) . w l lh lh C o l i e es es ( " D o c s ' , e > p l o d e ( $ ~ r e s p o n s e. e. d o c s ' ) ) sC £ c o u n t ( ) V i ) { i f ( r e s . l t s E - sC r e s w l t s S i p C * a r a s u l t t l > » 5 * . ' .a .a t h C o l u n n ( , e b _ w r l ' / $ D o c s. s. w e O _u _u r 1 ) . > . i t h C o l w n r ( " s : > . r c e* e* , J ' O o c s . s o w ' t e " ) . . i th th C o l u n r ( ; . s _ « a t e / d t S t r T © 0 a t e _ - 4 ' '('( J C © ; s . p u b _ s at at * ' ) ) / / .w ith C ol ua r( "h eo dl in e_ ao in *, S ' D o c t . h p o d l i n p . m o i t i ' ) . V ' itit h C o l u a n (~ (~ r « w s _ d e s> s> S'Decs neus.de s- ) . ' » i th th C o l i r a n ( ' s e c titi o " _ « a « e ' , J 3 : c : s e e t i e n _ n a n e ' ) . w i t h C o l ' r » ' ( " a w b s e s t i a n _ n a n e ' , $*Oo c j . s u S s e e t i e n _ n * n e ')') .withColuBn^sn ippet", i'Ds cs.snipp et")

|

4
























|

4

i n p o r t e * - g . «; «; »£ »£ > !« !« . - p » c . . s q l . ■ * . “ c « i e « s . « ■ p l c s « i n p o r t o - g . a p a : " « . s p a i -k -k . : « 1 . 3 y p « s . O a t a T y p « s . _ d t 5 t« t« - T © D a t« t« : ( d » t « 5 t r . S t r i n g ) j * a . s q l . O a t « i e n ■ U s er er O e< e< i n e S , j « e ti ti o n ( < < w r c t i p r l > , C a t e T / p e ■ p a r k . s q l . D a t a 'r a r e

d t S t r T p D a t« t« _ « S ' / e r g a p a c h * . s p a r . , . s q l . U : « " C t f i ' a i P u " « ; « a r c h ? iv iv T i n « o . ( s e a r c h . S t r i n g , s a g e I n t ) p r g . a p a c h e erg.apaehq.spark .sqS.Satafrare

■

[ « « b _ u rl rl :

string.

i.es_ur http http

Ktlae / ti r e

http http http

"ytine / ti r e

http http http

/t i r e / t i r e

/t i r e

http

"/tire / ti r e

http

,-tire

re:899

Figure

s c u r «e «e

string ,

pw pw s _ d at at e

P ate, "rus_d ts.

» «U «U 3. 3. .d .d e s k s e t t l e « _ n a i T e I s « b s e c t i o n _ r a n e | rhe New Yen. T i n e : fhe New Yor. T i n e : fhe Hew Yor. T i n e : rhe Hew Yor. T i n e : rhe Hew Yor. T i n e :

•11-MI

rhe Hew Yor. T i n e : rhe Hew Yor. T i n e : rhe Hew Yor. T i n e :

-12-181 ■19-241 ■

strin g,

s e e ti ti e n _ « a n e

string ,

s w® w® s e« e« t io io r >_ >_ n an an e : s t r i n g ,

snippet

snipp et I

s: Cay| nu ll|Cil and fa : price. 5 6 u s i ^ « : : D a y| y| n o r l d B u s i n e s s| s| T h e p r i c e o f e r u d .

■11-131 ■1*091 11 - 1*1

S3 Oay| s s Da Day I

-* 1-281

ss Day| ss Oay| . „m

rhe Hew Yor. T i n e :

■12-931 -19-181 S< j s

rhe Hew Yor. T i n e :

•94-231

i a «

mm s s O a y |

nu ll|Cr uPe Oil futu res, n u l l | A s le le a d e r : o f t h e . null|S aud i Arabia -as . n u l l | ? e r y e a r s , c h ea ea p . null|S9 is prepanmg t.

s s D a y| y| n o r l d b u s l ' e s s | A ^ t e r a y e a r o * a . : 8 u s i « < s s Da Da y] y] n o r l d 6 u s i » e s s I T e n s i o n : i n t « e s s D ay ay | n o r l d B u s i n e s s I C t h e r f u r p p e a - o i .

k:-g * 19

78 Results of Single Single Page Search of "Oil" "Oil" in Article Header, Part 2

For a single search criterion, the NY Times API only supports up to 100 pages of results. To broaden the search, a narrower selection criteria is needed (e.g., by year), and the results then need to be combined using union statements. Download a Range Range o f New York York Times Times Articles

Now, we will use Spark to download all news articles related to "oil” cre ated over the last ten years. The code shown in Listing 62 illustrates how the API can be called multiple times and the results be combined and saved into Hadoop HDFS, as shown in Figure 79. import org.apache.spark.sql var unionResultsDF: DataFrame - null; var tempD tempDF F : DataFrame DataFrame = n u l l ; var to ta lP age s: Int = 100; 100; / /c u rr en tl y max 100 supported var searchTerm: String = "q=oi1+price&fq=news_ d e s k: ( %22Busines$% 22Busines$%22,% 22,%22E 22Energy% nergy%22,% 22,%22F 22Financi inanci al%22)&begin_date al%22)&begin _date= = YEARNUM0101&end_date=YEARNUM1231";

string;


























|

4

tempD tempDF F = $earc $ea rchNYTi hNYTimes( mes(se sear arch ch , pageNum pageNum)) if (unionResultsDF!= null) { unionResult sDF = unionRes uni onResult ult sDF. uni onAl1( onAl 1(tem tempD pDF F) / / p r i n t l n ( " N e x t : " + page pageN Num) um) } e ls ls e { / / p r i n t l n ( "1 st : " + page pageN Num) unionResultsDF = tempDF

}

}

uni onR esultsDF. show( show( 4 0 ) un i o n R e su su l ts ts D F . r e p a r t i t i o n (1 ) . s a v e (" (" n y ti ti m e s P a r q u e t " , " p a r q u e t " ) Download Ten Years of Articles

Listing 62

import import org.ap ache.sp ark.sq l._ FIN FINISH ISHED t> « HJ © var unlonResultSD': DataFrare • null; var te«©2f te«©2f : oataFrare noil; 'currently «ax 100 lupported var totalF-ages: totalF-ages: :n t - leo; var searchTer a: str in g -
tetcCf

«.

2 0 0

7

to

2017) {

m null

for (pageHua <• e to (tctalPages • //prMtln(pag //prMtln(page*turn e*turn))

1

)){

if (cageujo .. 0 0 || tenpO*.ccun tenpO*.ccunt<) t<) >©> >©> < var search search Tem.repl ace(TE«i A*r, yearfkai.tostring) terpOf • searchVYTii»es(search , page***) if (unionftesultsOF!« noil) { uniortfesultsDF uhionBesultsOF.urionAllEtenpOF) //pr intI n(" »ex t: * ♦ pogefnm pogefnm) ) else <
























We have now successfully saved a summary of ten years of articles on Hadoop HDFS.

4.3

S t o r e N e t I n c o m e R e s u l ts ts U s i n g S A P H A N A

Now, Now, we will create create a table table in in SA SAP HANA to store store the average average net net income (in billions) for several of the world's largest oil companies over the past ten years. Execute the SQL code in in Listing Listing 63 in SAP HANA Studi Studio o to cre creat atee a table table and populate it with the average, average, publicly publicly listed, listed, quarterly quarterly results. DROP TABLE CODEJAM CODEJAMMER.NETI ER.NETINCOM NCOME; CREATE COLUMN TABLE CODEJAM EJAMMER.NET ER.NET INCOME( CALQUARTER VARCHARC5) , NETINCOME DECIM CIMAL( 1 8 , 5 )

); i ns n s er er t i ns n s er er t i ns n s er er t i ns n s er er t

i nt o i nt o i nt o i nt o

CODEJAM CODEJAMMER.NETI ER.NETINCOM NCOME CODEJAM CODEJAMMER.NETI ER.NETINCOM NCOME CODEJAM CODEJAMMER.NETI ER.NETINCOM NCOME CODEJAM CODEJAMMER.NETI ER.NETINCOM NCOME

valuesC'20154',2.36); values('20071'.26.72) values(’20072’,28.03) values('20073',28.19)

|

4
























insert i n se s e rt rt i n se s e rt rt i n se s e rt rt i ns n s er er t i n se s e rt rt i n se s e rt rt insert i ns n s er er t i n se s e rt rt i n se s e rt rt i n se s e rt rt

into i nt o i nt o i nt o i nt o i nt o i nt o into i nt o i nt o i nt o i nt o

CODEJAM CODEJAMMER.NETI ER.NETINCOM NCOME CODEJAM CODEJAMMER.NETI ER.NETINCOM NCOME CODEJAM CODEJAMMER.NETI ER.NETINCOM NCOME CODEJAM CODEJAMMER.NETI ER.NETINCOM NCOME CODEJAM CODEJAMMER.NETI ER.NETINCOM NCOME CODEJAM CODEJAMMER.NETI ER.NETINCOM NCOME CODEJAM CODEJAMMER.NETI ER.NETINCOM NCOME CODEJAM CODEJAMMER.NETI ER.NETINCOM NCOME CODEJAM CODEJAMMER.NETI ER.NETINCOM NCOME CODEJAM CODEJAMMER.NETI ER.NETINCOM NCOME CODEJAM CODEJAMMER.NETI ER.NETINCOM NCOME CODEJAM CODEJAMMER.NETI ER.NETINCOM NCOME

|

4

values(’20131',26.76) val ues (*20132 (*20132',24.85) ',24.85) values(’20133',24.59) values(’20134’,22.91) values(’20141’,21.37) values(’20142’,23.32) values(’20143’,22.69) values(’20144’,19.43) values(’20151’,16.26) values(’20152’.14.06) values('20153',6.29); values(’20154’,2.36);

Listi Listing ng 63 6 3 SQL SQL to Gen G ener erat atee NETINCO NETINCOME ME Results Resul ts in SAP SAP HANA

Next, create a calculation view that that combines combines the t he new new N E T I N C O M E table with the time dimension table (joined on the CALQUARTER column) to enable date-specific searches to be executed, as shown in Figure 80. ^ 3

S y s te te m s S S

a

*SQL *SQL Con sole 3

■ft v 0 S9 * C3 iS R %

(CODEJAMMER) t>

C a t al o g Content -

® 4

Scenario

CODEJA CODEJAMMER MMER

& Calculation 2 2

Views

(2)

CA_NET1NCOME_01 CA_SLFIGHT_001

£1 *SQL *SQL Co nso le 1

CODEJAMMER::CA_NETINCOME_01 HDB

a (§> HDB (CODEJA (CODEJAMMER MMER)) [Prod uction : a

-T? -T? ’SQL Co nso le2

& . .. ..

(2)

Join

:[J ’CODEJAMM ’CODEJAMMER; ER;... ...

\£ ® \£ - ft | ©© q? d ©+ © ♦

Î

Details Details Columns(3) View View Properties

:

local Show: [All

- I

w
























4.4

Analyze Oil Da ta Using Vora

Finally, we will use Vora to access each of the datasets created in the ear lier steps. We will then use Vora to combine and visualize the results. Oil Price Data

Create a Vora table using the code in Listing 64 to access the oil price Parquet file (/user/vora/oilDataParquet/part-r-00O00-4a549f81-9b0f-4f0abel3-15ba7213a52e.gz.parquet) cr creat eated ed by Apache Spark Spark in Section 4.1. The resulting price table is shown in Figure 81. %vo %vora CREATE TABLE OILDATA ( PRICE. PRICE.DATE DATE DATE, PRICE BBL DOUBLE

)

USING com.sap.spark.vora OPTIONS OPTIONS ( tableName "OILDATA", paths "/user/vora/oi1DataParquet/part-r-00000-4a549f81-9b0f-4f0a-bel315ba7213a52e.gz.parquet", hosts "maste "master.cl r.cl ust er,s econd ary. clus ter" , zkurls "master.cluster:2181.secondary.cluster:2181", nameNodeUrl "master.cluster:8020", format "parquet"

|

4
























%vora s e l e c t * from from OILD ILDATA o r d e r by PR PRICE_ ICE_D DATE asc Listing 65 Check Oil Price Fluctuations Fluctuati ons

Figure 82 Chart Showing Showing Oil Oil Price Changes Changes

Oil News Articles

Create a Vora table using the code in Listing 66 to access the news article Parquet file (/user/vora/nytimesParquet/part-r-00000-82a4db8e-a59d-4fec-

|

4


























format "parquet"

)

Listing 66 Create a Vora Table Linked to Oil News Articles

Xvcra CREATE TASLE OILOATA ( PRXCEJMTE DATE.

paiCE.eei

)

oousl e

USING cos.sap.spark.vora

OPTIONS (

tablef.-aiw "OILDATA", patns •/user/vor«/oiiOat8*>arquet/part-r-©08e0-4e54 arquet/part-r-©08e0-4e549*81-9B8?-4f0a 9*81-9B8?-4f0a-&el3-15oa -&el3-15oa721Ja52e.g:.pe 721Ja52e.g:.perqueV, rqueV, nosts '«aster. cluster, secon seconda dary, ry, du ste r', zkurls "easter.cluster:2181,secondary.cluster:2181“, nafteNodeUrl "easier.cluster: 8 8 2 0 “, forrat “parquet"

)

Command processed successfully with no results

Figure 83 Vora Table Linked to Oil News Articles

FINISHED D>

Efl ©

|

4
























Net Income Results

Using the code in Listing 68, you can create a Vora table to access the net income results stored in SAP HANA, which we created in Section 4.3. The results are shown in Figure 85. %vo %vora CREATE TABLE HANA_NETINCOME USING com.sap.spark.hana OPTIONS OPTIONS ( pat h "CODE "CODEJAMMER/CA_NET ER/CA_NETINCO INCOME_01", E_01", dbschema "_SYS_BIC\ host " \ instance "0 0 ” , us er "CODEJAM EJAMMER", passwd "CodeJam2015"

)

C

Vora Table Table Linked Linked to Net Income Result Result

|

4
























|

4
























|

4























What' Wha t's s Next? | 5

5

What's Next?

Now that you're familiar with SAP HANA Vora and its basic functions and configuration, what next? Vora helps you process big data faster and more efficiently than ever, and simplifies data management for SAP HANA and Hadoop. But But the there re''s more to SAP HANA data data tha than n Vora! Vora! Recommendation from Our Editors

I LvH H H H HESSSHl I

if you're looking to get knee-deep into data model design, SAP H A N A A d v a n c e d D a ta ta M o d e l i n g is is the book for you. With stepby-st by-step ep iinst nstruct ructions ions and and sam sampl ple e codi coding, ng, this this book book will teac teach h you how to build and and desig design n predictive, predictive, simulation, simulation, and and optimization models. From information views to AFL models,

























More from SAP PRESS Ready to get familiar with SAP HANA? From initial setup to connecting to a business intelligence platform, follow steby- step instructions and detailed examples for a complete SAP HANA Implementing SAP HANA:























S A P P R E S S E - B i te te s

SAP PRESS E-Bites provide you with a high-quality response to your specific project need. If you're you're looking for detailed instructions instruct ions on a specific specific task; or if you need to become become familiar with a small, small, but crucial subsub-comp compone onent nt of an SA SAP product; product ; or or if you

22.SAP HANA Vora - Functions and Integration.pdf

Recommend Documents