Sdílet prostřednictvím

Úprava kódu jazyka R/Python pro spuštění v instancích SQL Serveru (In-Database)

Platí pro: SQL Server 2016 (13.x) a novější verze Azure SQL Managed Instance

Tento článek obsahuje základní pokyny, jak upravit kód R nebo Python tak, aby běžel jako uložená procedura SQL Serveru, aby se zlepšil výkon při přístupu k datům SQL.

Když přesunete kód R/Python z místního integrovaného vývojového prostředí nebo jiného prostředí do SQL Serveru, kód obecně funguje bez dalších úprav. To platí zejména pro jednoduchý kód, například pro funkci, která přebírá některé vstupy a vrací hodnotu. Je také jednodušší portovat řešení, která používají balíčky RevoScaleR/revoscalepy , které podporují spouštění v různých kontextech provádění s minimálními změnami. Všimněte si, že MicrosoftML platí pro SQL Server 2016 (13.x), SQL Server 2017 (14.x) a SQL Server 2019 (15.x) a nezobrazuje se v SQL Serveru 2022 (16.x).

Pokud ale použijete některou z následujících možností, může váš kód vyžadovat dodatečné změny:

  • Používáte knihovny, které přistupují k síti nebo které se nemohou nainstalovat na SQL Server.
  • Kód provádí samostatná volání zdrojů dat mimo SQL Server, jako jsou excelové listy, soubory ve sdílených složkách a další databáze.
  • Chcete parametrizovat uloženou proceduru a spustit kód v @scriptparametru sp_execute_external_script.
  • Vaše původní řešení zahrnuje několik kroků, které můžou být efektivnější v produkčním prostředí, pokud se provádí nezávisle, jako je příprava dat nebo příprava funkcí vs. trénování modelu, bodování nebo generování sestav.
  • Chcete optimalizovat výkon změnou knihoven, paralelním spuštěním nebo přesměrováním zpracování na SQL Server.

Krok 1. Plánování požadavků a zdrojů

Packages

  • Určete, které balíčky jsou potřeba, a ujistěte se, že fungují na SQL Serveru.

  • Nainstalujte balíčky předem ve výchozí knihovně balíčků, kterou používá služba Machine Learning Services. Uživatelské knihovny nejsou podporovány.

Zdroje dat

  • Pokud chcete kód vložit do sp_execute_external_script, identifikujte primární a sekundární zdroje dat.

    • Primární zdroje dat jsou velké datové sady, jako jsou trénovací data modelu nebo vstupní data pro předpovědi. Naplánujte mapování největší datové sady na vstupní parametr sp_execute_external_script.

    • Sekundární zdroje dat jsou obvykle menší datové sady, například seznamy faktorů nebo další proměnné seskupení.

    V současné době sp_execute_external_script jako vstup uložené procedury podporuje pouze jednu datovou sadu. Můžete ale přidat více skalárních nebo binárních vstupů.

    Volání uložené procedury předcházející příkazu EXECUTE nelze použít jako vstup pro sp_execute_external_script. Můžete použít dotazy, zobrazení nebo jakýkoli jiný platný příkaz SELECT.

  • Určete potřebné výstupy. Pokud spustíte kód pomocí sp_execute_external_script, uložená procedura může jako výsledek vypíše pouze jeden datový rámec. Můžete ale také vypíšete několik skalárních výstupů, včetně grafů a modelů v binárním formátu, a také dalších skalárních hodnot odvozených z kódu nebo parametrů SQL.

Datové typy

Podrobný přehled mapování datových typů mezi R/Pythonem a SQL Serverem najdete v těchto článcích:

Podívejte se na datové typy používané v kódu jazyka R/Python a postupujte takto:

  • Vytvořte kontrolní seznam možných problémů s datovým typem.

    Sql Server Machine Learning Services podporuje všechny datové typy R/Python. SQL Server ale podporuje větší škálu datových typů než R nebo Python. Proto se při přesunu dat SQL Serveru do a z vašeho kódu provádějí některé implicitní převody datových typů. Je možné, že budete muset explicitně přetypovat nebo převést některá data.

    Podporují se hodnoty NULL. Jazyk R však používá na datový konstruktor k reprezentaci chybějící hodnoty, která se podobá hodnotě null.

  • Zvažte odstranění závislosti na datech, která nelze použít v R, například datové typy rowid a GUID ze SQL Serveru nelze v R použít a budou generovat chyby.

Krok 2. Převést nebo znovu zabalit kód

Kolik kódu změníte, závisí na tom, jestli chcete odeslat kód ze vzdáleného klienta ke spuštění ve výpočetním kontextu SQL Serveru, nebo chcete kód nasadit jako součást uložené procedury. Druhý z nich může poskytovat lepší výkon a zabezpečení dat, i když má některé další požadavky.

  • Definujte primární vstupní data jako dotaz SQL, pokud je to možné, abyste se vyhnuli přesunu dat.

  • Při spouštění kódu v uložené proceduře můžete projít více skalárními vstupy. Pro všechny parametry, které chcete použít ve výstupu, přidejte klíčové slovo OUTPUT .

    Například následující skalární vstup @model_name obsahuje název modelu, který je později upraven skriptem jazyka R, a výstup ve vlastním sloupci ve výsledcích:

    -- declare a local scalar variable which will be passed into the R script
DECLARE @local_model_name AS NVARCHAR (50) = 'DefaultModel';

-- The below defines an OUTPUT variable in the scope of the R script, called model_name
-- Syntactically, it is defined by using the @model_name name. Be aware that the sequence
-- of these parameters is very important. Mandatory parameters to sp_execute_external_script
-- must appear first, followed by the additional parameter definitions like @params, etc.
EXECUTE sp_execute_external_script @language = N'R', @script = N'
  model_name <- "Model name from R script"
  OutputDataSet <- data.frame(InputDataSet$c1, model_name)'
  , @input_data_1 = N'SELECT 1 AS c1'
  , @params = N'@model_name nvarchar(50) OUTPUT'
  , @model_name = @local_model_name OUTPUT;

-- optionally, examine the new value for the local variable:
SELECT @local_model_name;

  • Všechny proměnné, které předáte jako parametry uložené procedury , sp_execute_external_script musí být namapovány na proměnné v kódu. Ve výchozím nastavení se proměnné mapují podle názvu. Všechny sloupce ve vstupní datové sadě musí být také mapovány na proměnné ve skriptu.

    Předpokládejme například, že skript jazyka R obsahuje vzorec podobný tomuto:

    formula <- ArrDelay ~ CRSDepTime + DayOfWeek + CRSDepHour:DayOfWeek

    Pokud vstupní datová sada neobsahuje sloupce s odpovídajícími názvy ArrDelay, CRSDepTime, DayOfWeek, CRSDepHour a DayOfWeek, vyvolá se chyba.

  • V některých případech musí být výstupní schéma definováno předem pro výsledky.

    Chcete-li například vložit data do tabulky, musíte k určení schématu použít klauzuli WITH RESULT SET .

    Výstupní schéma je také vyžadováno, pokud skript používá argument @parallel=1. Důvodem je, že SQL Server může vytvořit několik procesů pro paralelní spuštění dotazu s výsledky shromážděnými na konci. Proto musí být výstupní schéma připravené před vytvořením paralelních procesů.

    V jiných případech můžete vynechat schéma výsledků pomocí možnosti WITH RESULT SETS UNDEFINED. Tento příkaz vrátí datovou sadu ze skriptu bez pojmenování sloupců nebo zadání datových typů SQL.

  • Zvažte generování časování nebo sledování dat pomocí jazyka T-SQL místo R/Pythonu.

    Můžete například předat systémový čas nebo jiné informace používané pro auditování a úložiště přidáním volání T-SQL, které se předává výsledkům, místo generování podobných dat ve skriptu.

Zvýšení výkonu a zabezpečení

  • Vyhněte se zápisu předpovědí nebo průběžných výsledků do souboru. Místo toho zapište předpovědi do tabulky, abyste se vyhnuli přesunu dat.

  • Spusťte všechny dotazy předem a zkontrolujte plány dotazů SQL Serveru a identifikujte úlohy, které je možné provádět paralelně.

    Pokud je možné vstupní dotaz paralelizovat, nastavte @parallel=1 jako součást argumentů sp_execute_external_script.

    Paralelní zpracování s tímto příznakem je obvykle možné kdykoli, když SQL Server může pracovat s dělenými tabulkami nebo distribuovat dotaz mezi více procesů a agregovat výsledky na konci. Paralelní zpracování s tímto příznakem obvykle není možné, pokud trénujete modely pomocí algoritmů, které vyžadují čtení všech dat, nebo pokud potřebujete vytvořit agregace.

  • Zkontrolujte kód a zjistěte, jestli existují kroky, které je možné provést nezávisle nebo efektivněji, pomocí samostatného volání uložené procedury. Například můžete dosáhnout lepšího výkonu tím, že samostatně provedete přípravu vlastností nebo extrakci vlastností a uložíte hodnoty do tabulky.

  • Hledejte způsoby použití jazyka T-SQL místo kódu jazyka R/Python pro výpočty založené na sadách.

    Toto řešení jazyka R například ukazuje, jak uživatelem definované funkce T-SQL a R můžou provádět stejnou úlohu přípravy funkcí: Kompletní návod pro datové vědy.

  • Obraťte se na vývojáře databází a zjistěte způsoby, jak zlepšit výkon pomocí funkcí SQL Serveru, jako jsou tabulky optimalizované pro paměť, nebo pokud máte Enterprise Edition, Správce prostředků.

  • Pokud používáte R, pokud je to možné, nahraďte konvenční funkce R funkcemi RevoScaleR , které podporují distribuované spouštění. Další informace naleznete v tématu Porovnání funkcí Base R a RevoScaleR.

Krok 3. Příprava nasazení

  • Informujte správce, aby bylo možné balíčky nainstalovat a otestovat před nasazením kódu.

    Ve vývojovém prostředí může být v pořádku instalovat balíčky jako součást vašeho kódu, ale to je v produkčním prostředí špatný postup.

    Uživatelské knihovny se nepodporují bez ohledu na to, jestli používáte uloženou proceduru nebo spouštíte kód R/Python ve výpočetním kontextu SQL Serveru.

Zabalení kódu R/Pythonu do uložené procedury

  • Vytvořte uživatelem definovanou funkci T-SQL, která kód vloží pomocí příkazu sp-execute-external-script .

  • Pokud máte složitý kód jazyka R, použijte balíček R sqlrutils k převodu kódu. Tento balíček je navržený tak, aby pomohl zkušeným uživatelům jazyka R psát správný uložený kód procedury. Kód R přepíšete jako jednu funkci s jasně definovanými vstupy a výstupy a pak pomocí balíčku sqlrutils vygenerujete vstup a výstupy ve správném formátu. Balíček sqlrutils vygeneruje úplný kód uložené procedury za vás a může také zaregistrovat uloženou proceduru v databázi.

    Další informace a příklady najdete v tématu sqlrutils (SQL).

Integrace s jinými pracovními postupy

  • Využití nástrojů T-SQL a procesů ETL V rámci pracovních postupů dat můžete předem provádět přípravu funkcí, extrakci funkcí a čištění dat.

    Při práci ve vyhrazeném vývojovém prostředí můžete načíst data do počítače, analyzovat data iterativním způsobem a pak výsledky zapsat nebo zobrazit. Pokud se ale samostatný kód migruje na SQL Server, může být většina tohoto procesu zjednodušená nebo delegována na jiné nástroje SQL Serveru.

  • Používejte zabezpečené asynchronní strategie vizualizace.

    Uživatelé SQL Serveru často nemají přístup k souborům na serveru a klientské nástroje SQL obvykle nepodporují grafická zařízení R/Python. Pokud v rámci řešení vygenerujete grafy nebo jinou grafiku, zvažte export grafů jako binárních dat a uložení do tabulky nebo zápisu.

  • Zabalte funkce predikce a bodování do uložených procedur pro přímý přístup aplikací.

Další kroky

Příklady nasazení řešení R a Pythonu na SQL Serveru najdete v těchto kurzech:

Kurzy jazyka R

Návody pro Python

  • Last updated on