Převod kódu R na uloženou proceduru pomocí sqlrutils

Tento článek popisuje kroky pro použití balíčku sqlrutils k převodu kódu jazyka R tak, aby běžel jako uložená procedura T-SQL. Aby bylo možné dosáhnout nejlepších výsledků, může být nutné kód poněkud upravit, aby se zajistilo, že se všechny vstupy dají parametrizovat.

Krok 1. Přepsání skriptu jazyka R

Nejlepších výsledků dosáhnete tak, že kód R přepíšete tak, aby byl zapouzdřený jako jedna funkce.

Všechny proměnné používané funkcí by měly být definovány uvnitř funkce nebo by měly být definovány jako vstupní parametry. Podívejte se na ukázkový kód v tomto článku.

Vzhledem k tomu, že vstupní parametry funkce R se stanou vstupními parametry uložené procedury SQL, musíte zajistit, aby vstupy a výstupy odpovídaly následujícím požadavkům typu:

Vstupy

Mezi vstupními parametry může být maximálně jeden datový rámec.

Objekty uvnitř datového rámce a všechny ostatní vstupní parametry funkce musí být z následujících datových typů R:

  • POSIXct
  • číselný
  • znak
  • integer
  • logical
  • raw

Pokud vstupní typ není jedním z výše uvedených typů, musí být serializován a předán do funkce jako nezpracované. V tomto případě musí funkce obsahovat také kód pro deserializaci vstupu.

Výstupy

Funkce může vypíše jednu z následujících možností:

  • Datový rámec obsahující podporované datové typy. Všechny objekty v datovém rámci musí používat jeden z podporovaných datových typů.
  • Pojmenovaný seznam, který obsahuje maximálně jeden datový rámec. Všichni členové seznamu by měli používat jeden z podporovaných datových typů.
  • Hodnota NULL, pokud vaše funkce nevrací žádný výsledek

Krok 2. Generování požadovaných objektů

Jakmile se kód R vyčistí a dá se volat jako jedna funkce, použijete funkce v balíčku sqlrutils k přípravě vstupů a výstupů ve formuláři, který lze předat konstruktoru, který ve skutečnosti sestaví uloženou proceduru.

sqlrutils poskytuje funkce, které definují schéma a typ vstupních dat a definují schéma a typ výstupních dat. Obsahuje také funkce, které mohou převést objekty R na požadovaný výstupní typ. V závislosti na datových typech, které váš kód používá, můžete volat více volání funkcí k vytvoření požadovaných objektů.

Vstupy

Pokud vaše funkce přebírá vstupy pro každý vstup, zavolejte následující funkce:

  • setInputData Pokud je vstup datový rámec
  • setInputParameter pro všechny ostatní vstupní typy

Když provedete každé volání funkce, vytvoří se objekt R, který později předáte jako argument StoredProcedure, k vytvoření úplné uložené procedury.

Výstupy

sqlrutils poskytuje více funkcí pro převod objektů jazyka R, jako jsou seznamy na data.frame vyžadované SQL Server. Pokud vaše funkce vypíše datový rámec přímo, aniž by ho nejprve zabalil do seznamu, můžete tento krok přeskočit. Převod můžete také přeskočit, pokud vaše funkce vrátí hodnotu NULL.

Při převodu seznamu nebo získání určité položky ze seznamu vyberte z těchto funkcí:

  • setOutputData pokud je proměnná, která má být získána ze seznamu, datový rámec
  • setOutputParameter pro všechny ostatní členy seznamu

Když provedete každé volání funkce, vytvoří se objekt R, který později předáte jako argument StoredProcedure, k vytvoření úplné uložené procedury.

Krok 3. Vygenerování uložené procedury

Jakmile jsou všechny vstupní a výstupní parametry připravené, proveďte volání konstruktoru StoredProcedure .

Usage

StoredProcedure (func, spName, ..., filePath = NULL ,dbName = NULL, connectionString = NULL, batchSeparator = "GO")

Pro ilustraci předpokládejme, že chcete vytvořit uloženou proceduru s názvem sp_rsample s těmito parametry:

  • Používá existující funkci foosql. Funkce byla založena na existujícím kódu funkce foo v jazyce R, ale přepsali jste ji tak, aby splňovala požadavky uvedené v této části, a aktualizovanou funkci jste pojmenovali jako foosql.
  • Používá datový rámec queryinput jako vstup.
  • Vygeneruje jako výstup datový rámec s názvem proměnné R, sqloutput.
  • Chcete vytvořit kód T-SQL jako soubor ve C:\Temp složce, abyste ho mohli spustit pomocí SQL Server Management Studio později.
StoredProcedure (foosql, sp_rsample, queryinput, sqloutput, filePath = "C:\\Temp")

Note

Protože soubor zapisujete do systému souborů, můžete vynechat argumenty, které definují připojení k databázi.

Výstupem funkce je uložená procedura T-SQL, která se dá spustit v instanci SQL Server 2016 (vyžaduje R Services) nebo SQL Server 2017 (vyžaduje Machine Learning Services s jazykem R).

Další příklady najdete v nápovědě k balíčku voláním help(StoredProcedure) z prostředí R.

Krok 4. Registrace a spuštění uložené procedury

Uloženou proceduru můžete spustit dvěma způsoby:

  • Pomocí T-SQL z libovolného klienta, který podporuje připojení k instanci SQL Server 2016 nebo SQL Server 2017
  • Z prostředí R

Obě metody vyžadují, aby uložená procedura byla zaregistrována v databázi, ve které chcete uloženou proceduru použít.

Zaregistrujte uloženou proceduru

Uloženou proceduru můžete zaregistrovat pomocí jazyka R nebo můžete příkaz spustit CREATE PROCEDURE v jazyce T-SQL.

  • Použití jazyka T-SQL Pokud jste s T-SQL pohodlnější, otevřete SQL Server Management Studio (nebo jakýkoli jiný klient, který může spouštět příkazy SQL DDL) a spusťte CREATE PROCEDURE příkaz pomocí kódu připraveného funkcíStoredProcedure.

  • Použití jazyka R. I když jste stále ve svém prostředí R, můžete použít registerStoredProcedure funkci v nástroji sqlrutils k registraci uložené procedury v databázi.

    Například můžete zaregistrovat uloženou proceduru sp_rsample v instanci a databázi definované v sqlConnStr, a to provedením tohoto volání R:

    registerStoredProcedure(sp_rsample, sqlConnStr)

Important

Bez ohledu na to, jestli používáte R nebo SQL, musíte příkaz spustit pomocí účtu, který má oprávnění k vytvoření nových databázových objektů.

Spustit pomocí SQL

Po vytvoření uložené procedury otevřete připojení k databázi SQL pomocí libovolného klienta, který podporuje T-SQL, a předejte hodnoty pro všechny parametry požadované uloženou procedurou.

Spuštění pomocí jazyka R

Pokud chcete spustit uloženou proceduru z kódu jazyka R, je potřeba provést další přípravu, a ne z SQL Server. Pokud například uložená procedura vyžaduje vstupní hodnoty, musíte tyto vstupní parametry nastavit před spuštěním funkce a pak tyto objekty předat uložené proceduře v kódu R.

Celkový proces volání připravené uložené procedury SQL je následující:

  1. Zavolejte getInputParameters, chcete-li získat seznam objektů vstupních parametrů.
  2. $query Definujte nebo nastavte $value pro každý vstupní parametr.
  3. Umožňuje executeStoredProcedure spustit uloženou proceduru z vývojového prostředí jazyka R a předat seznam objektů vstupních parametrů, které jste nastavili.

Příklad

Tento příklad ukazuje před a po verzích skriptu jazyka R, který získává data z SQL Server databáze, provádí některé transformace dat a ukládá je do jiné databáze.

Tento jednoduchý příklad slouží pouze k předvedení toho, jak můžete změnit uspořádání kódu R, aby bylo snazší převést na uloženou proceduru.

Před přípravou kódu

sqlConnFrom <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer01;Database=AirlineSrc;Trusted_Connection=Yes;"
  
sqlConnTo <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer01;Database=AirlineTest;Trusted_Connection=Yes;"
  
sqlQueryAirline <- "SELECT TOP 10000 ArrDelay, CRSDepTime, DayOfWeek FROM [AirlineDemoSmall]"
  
dsSqlFrom <- RxSqlServerData(sqlQuery = sqlQueryAirline, connectionString = sqlConnFrom)
  
dsSqlTo <- RxSqlServerData(table = "cleanData", connectionString = sqlConnTo)
  
xFunc <- function(data) {
    data$CRSDepHour <- as.integer(trunc(data$CRSDepTime))
    return(data)
    }
  
xVars <- c("CRSDepTime")
  
sqlCompute <- RxInSqlServer(numTasks = 4, connectionString = sqlConnTo)
  
rxOpen(dsSqlFrom)
rxOpen(dsSqlTo)
  
if (rxSqlServerTableExists("cleanData", connectionString = sqlConnTo))   {
    rxSqlServerDropTable("cleanData")}
  
rxDataStep(inData = dsSqlFrom, 
     outFile = dsSqlTo,
     transformFunc = xFunc,
     transformVars = xVars,
     overwrite = TRUE)

Note

Pokud místo vyvolání funkce RxSqlServerData použijete připojení ODBC, musíte před provedením operací s databází otevřít připojení pomocí rxOpen .

Po přípravě kódu

V aktualizované verzi první řádek definuje název funkce. Všechny ostatní kódy z původního řešení R se stanou součástí této funkce.

myetl1function <- function() { 
   sqlConnFrom <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer01;Database=Airline01;Trusted_Connection=Yes;"
   sqlConnTo <- "Driver={ODBC Driver 13 for SQL Server};Server=MyServer02;Database=Airline02;Trusted_Connection=Yes;"
    
   sqlQueryAirline <- "SELECT TOP 10000 ArrDelay, CRSDepTime, DayOfWeek FROM [AirlineDemoSmall]"

   dsSqlFrom <- RxSqlServerData(sqlQuery = sqlQueryAirline, connectionString = sqlConnFrom)
  
   dsSqlTo <- RxSqlServerData(table = "cleanData", connectionString = sqlConnTo)
  
   xFunc <- function(data) {
     data$CRSDepHour <- as.integer(trunc(data$CRSDepTime))
     return(data)}
  
   xVars <- c("CRSDepTime")
  
   sqlCompute <- RxInSqlServer(numTasks = 4, connectionString = sqlConnTo)
  
   if (rxSqlServerTableExists("cleanData", connectionString = sqlConnTo)) {rxSqlServerDropTable("cleanData")}
  
   rxDataStep(inData = dsSqlFrom, 
        outFile = dsSqlTo,
        transformFunc = xFunc,
        transformVars = xVars,
        overwrite = TRUE)
   return(NULL)
}

Note

I když připojení ODBC není nutné explicitně otevírat jako součást kódu, připojení ODBC je stále nutné k použití nástroje sqlrutils.

Související obsah

  • Poslední aktualizace v