Share via

Wijzig R/Python-code om te draaien binnen SQL Server-exemplaren (In-Database)

Van toepassing op: SQL Server 2016 (13.x) en latere versies van Azure SQL Managed Instance

Dit artikel bevat richtlijnen op hoog niveau voor het wijzigen van R- of Python-code die moet worden uitgevoerd als een opgeslagen SQL Server-procedure om de prestaties te verbeteren bij het openen van SQL-gegevens.

Wanneer u R/Python-code verplaatst van een lokale IDE of een andere omgeving naar SQL Server, werkt de code over het algemeen zonder verdere aanpassingen. Dit geldt met name voor eenvoudige code, zoals een functie die enkele invoer gebruikt en een waarde retourneert. Het is ook eenvoudiger om oplossingen over te zetten die gebruikmaken van deRevoScaleR-revoscalepy-pakketten/, die ondersteuning bieden voor uitvoering in verschillende uitvoeringscontexten met minimale wijzigingen. MicrosoftML is van toepassing op SQL Server 2016 (13.x), SQL Server 2017 (14.x) en SQL Server 2019 (15.x) en wordt niet weergegeven in SQL Server 2022 (16.x).

Uw code kan echter aanzienlijke wijzigingen vereisen als een van de volgende van toepassing is:

  • U gebruikt bibliotheken die toegang hebben tot het netwerk of die niet kunnen worden geïnstalleerd op SQL Server.
  • De code roept afzonderlijke aanroepen uit naar gegevensbronnen buiten SQL Server, zoals Excel-werkbladen, bestanden op shares en andere databases.
  • U wilt de opgeslagen procedure parameteriseren en de code uitvoeren in de parameter @script van sp_execute_external_script.
  • Uw oorspronkelijke oplossing bevat meerdere stappen die efficiënter kunnen zijn in een productieomgeving als ze onafhankelijk worden uitgevoerd, zoals gegevensvoorbereiding of functie-engineering versus modeltraining, score of rapportage.
  • U wilt de prestaties optimaliseren door bibliotheken te wijzigen, parallelle uitvoering te gebruiken of een bepaalde verwerking naar SQL Server te offloaden.

Stap 1. Het plannen van vereisten en middelen

Pakketten

  • Bepaal welke pakketten nodig zijn en zorg ervoor dat ze werken op SQL Server.

  • Installeer vooraf pakketten in de standaardpakketbibliotheek die wordt gebruikt door Machine Learning Services. Gebruikersbibliotheken worden niet ondersteund.

Gegevensbronnen

  • Als u uw code wilt insluiten in sp_execute_external_script, moet u primaire en secundaire gegevensbronnen identificeren.

    • Primaire gegevensbronnen zijn grote gegevenssets, zoals modeltrainingsgegevens of invoergegevens voor voorspellingen. Richt uw grootste gegevensset op de invoerparameter van sp_execute_external_script.

    • Secundaire gegevensbronnen zijn doorgaans kleinere gegevenssets, zoals lijsten met factoren of aanvullende groeperingsvariabelen.

    Op dit moment ondersteunt sp_execute_external_script slechts één gegevensset als invoer voor de opgeslagen procedure. U kunt echter meerdere scalaire of binaire invoer toevoegen.

    Opgeslagen procedureaanroepen voorafgegaan door EXECUTE kunnen niet worden gebruikt als invoer voor sp_execute_external_script. U kunt query's, weergaven of een andere geldige SELECT-instructie gebruiken.

  • Bepaal de uitvoer die u nodig hebt. Als u code uitvoert met behulp van sp_execute_external_script, kan de opgeslagen procedure slechts één gegevensframe uitvoeren. U kunt echter ook meerdere scalaire uitvoer uitvoeren, waaronder plots en modellen in binaire indeling, evenals andere scalaire waarden die zijn afgeleid van code- of SQL-parameters.

Gegevenstypen

Zie de volgende artikelen voor een gedetailleerd overzicht van de toewijzingen van gegevenstypen tussen R/Python en SQL Server:

Bekijk de gegevenstypen die worden gebruikt in uw R/Python-code en ga als volgt te werk:

  • Maak een controlelijst met mogelijke problemen met het gegevenstype.

    Alle R/Python-gegevenstypen worden ondersteund door SQL Server Machine Learning Services. SQL Server ondersteunt echter een grotere verscheidenheid aan gegevenstypen dan R of Python. Daarom worden sommige impliciete conversies van gegevenstypen uitgevoerd bij het verplaatsen van SQL Server-gegevens naar en van uw code. Mogelijk moet u expliciet gegevens casten of converteren.

    NULL-waarden worden ondersteund. R gebruikt echter de na gegevensconstructie om een ontbrekende waarde weer te geven, wat vergelijkbaar is met een null.

  • Overweeg om afhankelijkheid te elimineren van gegevens die niet kunnen worden gebruikt door R: bijvoorbeeld: gegevenstypen rowid en GUID van SQL Server kunnen niet worden gebruikt door R en genereren fouten.

Stap 2. Code converteren of opnieuw verpakken

Hoeveel u de code wijzigt, is afhankelijk van of u de code wilt verzenden vanaf een externe client die moet worden uitgevoerd in de SQL Server-rekencontext of de code wilt implementeren als onderdeel van een opgeslagen procedure. De laatste kan betere prestaties en gegevensbeveiliging bieden, hoewel er enkele aanvullende vereisten worden opgelegd.

  • Definieer waar mogelijk uw primaire invoergegevens als een SQL-query om gegevensverplaatsing te voorkomen.

  • Wanneer u code uitvoert in een opgeslagen procedure, kunt u meerdere scalaire invoer doorgeven. Voor parameters die u in de uitvoer wilt gebruiken, voegt u het trefwoord UITVOER toe.

    De volgende scalaire invoer @model_name bevat bijvoorbeeld de modelnaam, die later ook wordt gewijzigd door het R-script en uitvoer in een eigen kolom in de resultaten:

    -- 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;

  • Variabelen die u doorgeeft als parameters van de opgeslagen procedure sp_execute_external_script moeten worden toegewezen aan variabelen in de code. Variabelen worden standaard toegewezen op naam. Alle kolommen in de invoergegevensset moeten ook worden toegewezen aan variabelen in het script.

    Stel dat uw R-script een formule bevat zoals deze:

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

    Er wordt een fout gegenereerd als de invoergegevensset geen kolommen bevat met de overeenkomende namen ArrDelay, CRSDepTime, DayOfWeek, CRSDepHour en DayOfWeek.

  • In sommige gevallen moet een uitvoerschema vooraf worden gedefinieerd voor de resultaten.

    Als u bijvoorbeeld de gegevens in een tabel wilt invoegen, moet u de component WITH RESULT SET gebruiken om het schema op te geven.

    Het uitvoerschema is ook vereist als het script het argument @parallel=1gebruikt. De reden hiervoor is dat er meerdere processen kunnen worden gemaakt door SQL Server om de query parallel uit te voeren, met de resultaten die aan het einde zijn verzameld. Daarom moet het uitvoerschema worden voorbereid voordat de parallelle processen kunnen worden gemaakt.

    In andere gevallen kunt u het resultaatschema weglaten met behulp van de optie WITH RESULT SETS UNDEFINED. Met deze instructie wordt de gegevensset uit het script geretourneerd zonder de kolommen een naam te geven of de SQL-gegevenstypen op te geven.

  • Overweeg tijdsinstellingen of traceringsgegevens te genereren met behulp van T-SQL in plaats van R/Python.

    U kunt bijvoorbeeld de systeemtijd of andere informatie doorgeven die wordt gebruikt voor controle en opslag door een T-SQL-aanroep toe te voegen die wordt doorgegeven aan de resultaten, in plaats van vergelijkbare gegevens in het script te genereren.

Prestaties en beveiliging verbeteren

  • Vermijd het schrijven van voorspellingen of tussenliggende resultaten naar een bestand. Schrijf in plaats daarvan voorspellingen naar een tabel om gegevensverplaatsing te voorkomen.

  • Voer alle query's van tevoren uit en bekijk de SQL Server-queryplannen om taken te identificeren die parallel kunnen worden uitgevoerd.

    Als de invoerquery kan worden geparallelliseerd, gebruikt u @parallel=1 als onderdeel van uw argumenten aan sp_execute_external_script.

    Parallelle verwerking met deze vlag is doorgaans mogelijk wanneer SQL Server kan werken met gepartitioneerde tabellen of een query over meerdere processen kan distribueren en de resultaten aan het einde kan aggregeren. Parallelle verwerking met deze vlag is doorgaans niet mogelijk als u modellen traint met behulp van algoritmen waarvoor alle gegevens moeten worden gelezen of als u aggregaties moet maken.

  • Controleer uw code om te bepalen of er stappen zijn die onafhankelijk kunnen worden uitgevoerd of efficiënter kunnen worden uitgevoerd met behulp van een afzonderlijke opgeslagen procedure-aanroep. U kunt bijvoorbeeld betere prestaties krijgen door functie-engineering of functieextractie afzonderlijk uit te voeren en de waarden op te slaan in een tabel.

  • Zoek naar manieren om T-SQL te gebruiken in plaats van R/Python-code voor op set gebaseerde berekeningen.

    Deze R-oplossing laat bijvoorbeeld zien hoe door de gebruiker gedefinieerde T-SQL-functies en R dezelfde functie-engineeringtaak kunnen uitvoeren: End-to-End-walkthrough voor Data Science.

  • Neem contact op met een databaseontwikkelaar om manieren te bepalen om de prestaties te verbeteren met behulp van SQL Server-functies zoals tabellen die zijn geoptimaliseerd voor geheugen of, als u Enterprise Edition hebt, Resource Governor.

  • Als u R gebruikt, vervangt u indien mogelijk conventionele R-functies door RevoScaleR-functies die gedistribueerde uitvoering ondersteunen. Zie Vergelijking van de functies Base R en RevoScaleR voor meer informatie.

Stap 3. Implementatie voorbereiden

  • Informeer de beheerder zodat pakketten kunnen worden geïnstalleerd en getest voordat u uw code implementeert.

    In een ontwikkelomgeving is het misschien geen probleem om pakketten te installeren als onderdeel van uw code, maar dit is een slechte gewoonte in een productieomgeving.

    Gebruikersbibliotheken worden niet ondersteund, ongeacht of u een opgeslagen procedure gebruikt of R/Python-code uitvoert in de SQL Server-rekencontext.

Uw R/Python-code inpakken in een opgeslagen procedure

  • Maak een door de gebruiker gedefinieerde T-SQL-functie en sluit uw code in met behulp van de sp-execute-external-script-instructie .

  • Als u complexe R-code hebt, gebruikt u het R-pakket sqlrutils om uw code te converteren. Dit pakket is ontworpen om ervaren R-gebruikers te helpen goede opgeslagen procedurecode te schrijven. U herschrijft uw R-code als één functie met duidelijk gedefinieerde invoer en uitvoer en gebruikt vervolgens het sqlrutils-pakket om de invoer en uitvoer in de juiste indeling te genereren. Het sqlrutils-pakket genereert de volledige opgeslagen procedurecode voor u en kan ook de opgeslagen procedure registreren in de database.

    Zie sqlrutils (SQL)voor meer informatie en voorbeelden.

Integreren met andere werkstromen

  • Maak gebruik van T-SQL-hulpprogramma's en ETL-processen. Voer functie-engineering, functieextractie en gegevensopschoning vooraf uit als onderdeel van gegevenswerkstromen.

    Wanneer u in een toegewezen ontwikkelomgeving werkt, kunt u gegevens naar uw computer halen, de gegevens iteratief analyseren en vervolgens de resultaten wegschrijven of weergeven. Wanneer zelfstandige code echter naar SQL Server wordt gemigreerd, kan veel van dit proces worden vereenvoudigd of gedelegeerd naar andere SQL Server-hulpprogramma's.

  • Gebruik veilige, asynchrone visualisatiestrategieën.

    Gebruikers van SQL Server hebben vaak geen toegang tot bestanden op de server en SQL-clienthulpprogramma's bieden doorgaans geen ondersteuning voor de R/Python-grafische apparaten. Als u plots of andere afbeeldingen genereert als onderdeel van de oplossing, kunt u overwegen de plots als binaire gegevens te exporteren en op te slaan in een tabel of schrijven.

  • Wikkel voorspellings- en scorefuncties in opgeslagen procedures voor directe toegang door toepassingen.

Volgende stappen

Als u voorbeelden wilt bekijken van hoe R- en Python-oplossingen kunnen worden geïmplementeerd in SQL Server, raadpleegt u deze zelfstudies:

R-handleidingen

Python-handleidingen

  • Last updated on