SAP HANA和R 快速上手使用案例

Vote_Generator.py

import os

import sys

import random

names = [“Anne”, “Gigi”, “Juergen”, “Ingo”, “Inga”, “Alvaro”, “Mario”, “Julien”,

“Mike”, “Michael”, “Karin”, “Rui”, “John”, “Rocky”, “Sebastian”, “Kai-Yin”,

“Hester”, “Katrin”, “Uwe”, “Vitaliy”]

last_names = [“Hardy”, “Read”, “Schmerder”, “Sauerzapf”, “Bereza”, “Tejada”,

“Herger”, “Vayssiere”, “Flynn”, “Byczkowski”, “Schattka”,

“Nogueira”, “Mayerhofer”, “Ongkowidjojo”, “Wieczorek”, “Gau”, “Hilbrecht”,

“Staehr”, “Kylau”, “Rudnytskiy”]

age = [24, 34, 40, 38, 28, 36, 35, 42, 30, 37]

*** = [“M”, “F”]

country = [“Germany”, “France”, “Polland”, “Peru”, “Russia”, “USA”, “China”,

“Philippines”, “Portugal”, “Spain”]

vote = [“ABAP”, “Node”, “Ruby”, “Python”, “R”, “PHP”, “ActionScript”,

“Euphoria”, “Java”, “C++”]

def Generate_File(pSchema, pNumber):

    iNumber = 0

    pathname = os.path.dirname(sys.argv[0])

    pathname = os.path.abspath(pathname) + “\HANA_File.txt”

    myfile = open(pathname, “a”)

    while iNumber < pNumber:

        r_names = random.randrange(0, 20)

        r_lastnames = random.randrange(0, 20)

        r_age = random.randrange(0, 10)

        r_*** = random.randrange(0, 2)

        r_country = random.randrange(0, 10)

        r_vote = random.randrange(0, 10)

        iNumber += 1

        myfile.write(“insert into ” + pSchema + ” values(‘” +

        names[r_names] + “‘,'” + last_names[r_lastnames] + “‘,” +

        str(age[r_age]) + “,'” + ***[r_***] + “‘,'” + country[r_country] +

        “‘,'” + vote[r_vote] + “‘);\n”)

    myfile.close()

    print ‘The file ‘ + pathname + ‘ was written successfully’

schema = raw_input(“Schema: \n”)

num_files = input(“How many records?: \n”)

Generate_File(schema, num_files)

HANA_R_Votes.sql

DROP TYPE T_VOTES_DETAIL;

CREATE TYPE T_VOTES_DETAIL AS TABLE (

COUNTRY VARCHAR(15),

AGE CHAR(2),

*** CHAR(1),

FREQUENCY INTEGER

);

DROP PROCEDURE CALCULATE_VOTES;

DROP PROCEDURE GET_VOTES;

CREATE PROCEDURE CALCULATE_VOTES(IN votes VOTES, out votes_detail T_VOTES_DETAIL)

LANGUAGE RLANG AS

BEGIN

Country<-votes$COUNTRY

Age<-votes$AGE

***<-votes$***

Votes<-table(Country, Age, ***)

Stats<-data.frame(Votes)

Stats<-subset(Stats,(Stats$Freq>0))

Stats<-Stats[order(Stats$Country,Stats$Age),]

votes_detail<-data.frame(COUNTRY=Stats$Country,AGE=Stats$Age,***=Stats$***,FREQUENCY=Stats$Freq)

END;

CREATE PROCEDURE GET_VOTES()

LANGUAGE SQLSCRIPT AS

BEGIN

tab_votes = SELECT * FROM VOTES;

CALL CALCULATE_VOTES(:tab_votes, T_VOTES_DETAIL);

INSERT INTO “VOTES_DETAIL” SELECT * FROM :T_VOTES_DETAIL;

END;

CALL GET_VOTES();

SELECT * FROM VOTES_DETAIL