Rychlý start: Datové struktury, datové typy a objekty pomocí jazyka R s využitím strojového učení SQL

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

V tomto rychlém startu se dozvíte, jak používat datové struktury a datové typy při použití jazyka R ve službě SQL Server Machine Learning Services nebo v clusterech s velkými objemy dat. Dozvíte se o přesouvání dat mezi R a SQL Serverem a o běžných problémech, ke kterým může dojít.

Mezi běžné problémy, které je potřeba znát předem, patří:

• Datové typy se někdy neshodují
• Implicitní převody mohou proběhnout
• Někdy se vyžadují přetypování a konverzní operace.
• R a SQL používají různé datové objekty

Požadavky

Ke spuštění tohoto rychlého startu potřebujete následující požadavky.

• SQL Server Machine Learning Services. Informace o instalaci služby Machine Learning Services najdete v průvodci instalací systému Windows nebo v průvodci instalací pro Linux. Službu Machine Learning Services můžete také povolit v clusterech s velkými objemy dat SQL Serveru.

• Nástroj pro spouštění dotazů SQL, které obsahují skripty jazyka R. V tomto rychlém startu se používá rozšíření MSSQL pro Visual Studio Code.

Vždy vracejte datový rámec

Když skript vrátí výsledky z R na SQL Server, musí vrátit data jako data.frame. Jakýkoli jiný typ objektu, který ve skriptu vygenerujete – ať už jde o seznam, faktor, vektor nebo binární data – musí být převeden na datový rámec, pokud ho chcete vypsat jako součást výsledků uložené procedury. Naštěstí existuje několik funkcí jazyka R, které podporují změnu jiných objektů na datový rámec. Binární model můžete dokonce serializovat a vrátit ho do datového rámce, což uděláte později v tomto rychlém startu.

Nejprve experimentujme s některými základními objekty R jazyka R – vektory, maticemi a seznamy – a podívejme se, jak převod na datový rámec změní výstup předaný SQL Serveru.

Porovnejte tyto dva skripty "Hello World" v jazyce R. Skripty vypadají téměř stejně, ale první vrátí jeden sloupec tří hodnot, zatímco druhý vrátí tři sloupce s jednou hodnotou.

Příklad 1

EXECUTE sp_execute_external_script
       @language = N'R'
     , @script = N' mytextvariable <- c("hello", " ", "world");
       OutputDataSet <- as.data.frame(mytextvariable);'
     , @input_data_1 = N' ';

Příklad 2

EXECUTE sp_execute_external_script
        @language = N'R'
      , @script = N' OutputDataSet<- data.frame(c("hello"), " ", c("world"));'
      , @input_data_1 = N'  ';

Identifikace schématu a datových typů

Proč se výsledky liší?

Odpověď se obvykle najde pomocí příkazu R str() . Přidejte funkci str(object_name) kamkoli do skriptu jazyka R, aby bylo datové schéma zadaného objektu jazyka R vráceno jako informační zpráva.

Pokud chcete zjistit, proč mají příklady 1 a Příklad 2 takové různé výsledky, vložte řádek str(OutputDataSet) na konec @script definice proměnné do každého příkazu, například takto:

Příklad 1 s přidanou funkcí str

EXECUTE sp_execute_external_script
        @language = N'R'
      , @script = N' mytextvariable <- c("hello", " ", "world");
      OutputDataSet <- as.data.frame(mytextvariable);
      str(OutputDataSet);'
      , @input_data_1 = N'  '
;

Příklad 2 s přidanou funkcí str

EXECUTE sp_execute_external_script
  @language = N'R', 
  @script = N' OutputDataSet <- data.frame(c("hello"), " ", c("world"));
    str(OutputDataSet);' , 
  @input_data_1 = N'  ';

Teď si prohlédněte text ve zprávách a zjistěte, proč se výstup liší.

Výsledky – příklad 1

STDOUT message(s) from external script:
'data.frame':	3 obs. of  1 variable:
$ mytextvariable: Factor w/ 3 levels " ","hello","world": 2 1 3

Výsledky – příklad 2

STDOUT message(s) from external script:
'data.frame':	1 obs. of  3 variables:
$ c..hello..: Factor w/ 1 level "hello": 1
$ X...      : Factor w/ 1 level " ": 1
$ c..world..: Factor w/ 1 level "world": 1

Jak vidíte, mírná změna syntaxe jazyka R měla velký vliv na schéma výsledků. Nebudeme se zabývat tím, proč, ale rozdíly v datových typech R jsou vysvětleny v podrobnostech v části Datové struktury v části "Advanced R" od Hadley Wickham.

Prozatím mějte na paměti, že při vynucení objektů R do datových rámců potřebujete zkontrolovat očekávané výsledky.

Návod

K vrácení informací o interní datové struktuře můžete použít funkce identity jazyka R, jako například is.matrix, is.vector.

Implicitní převod datových objektů

Každý datový objekt R má vlastní pravidla pro způsob zpracování hodnot v kombinaci s jinými datovými objekty, pokud mají dva datové objekty stejný počet dimenzí nebo pokud některý datový objekt obsahuje heterogenní datové typy.

Nejprve vytvořte malou tabulku testovacích dat.

CREATE TABLE RTestData (col1 INT NOT NULL)

INSERT INTO RTestData
VALUES (1);

INSERT INTO RTestData
VALUES (10);

INSERT INTO RTestData
VALUES (100);
GO

Předpokládejme například, že spuštěním následujícího příkazu provedete násobení matic pomocí R. Matici s jedním sloupcem vynásobíte třemi hodnotami pomocí matice se čtyřmi hodnotami a jako výsledek očekáváte matici 4x3.

EXECUTE sp_execute_external_script
    @language = N'R'
    , @script = N'
        x <- as.matrix(InputDataSet);
        y <- array(12:15);
    OutputDataSet <- as.data.frame(x %*% y);'
    , @input_data_1 = N' SELECT [Col1]  from RTestData;'
    WITH RESULT SETS (([Col1] int, [Col2] int, [Col3] int, Col4 int));

Pod kryty se sloupec tří hodnot převede na matici s jedním sloupcem. Vzhledem k tomu, že matice je pouze speciálním případem matice v jazyce R, je pole y implicitně přetěžováno na matici s jedním sloupcem, aby se oba argumenty shodovaly.

Results

Sloupec 1	Sl 2	Sloupec 3	Sloupec 4
12	13	14	15
120	130	140	sto padesát
1200	1300	1400	1500

Všimněte si však, co se stane, když změníte velikost pole y.

execute sp_execute_external_script
   @language = N'R'
   , @script = N'
        x <- as.matrix(InputDataSet);
        y <- array(12:14);
   OutputDataSet <- as.data.frame(y %*% x);'
   , @input_data_1 = N' SELECT [Col1]  from RTestData;'
   WITH RESULT SETS (([Col1] int ));

R teď jako výsledek vrátí jednu hodnotu.

Results

Sloupec 1
1542

Why? V tomto případě, protože tyto dva argumenty lze zpracovat jako vektory stejné délky, vrátí R vnitřní součin jako matici. Toto je očekávané chování podle pravidel lineární algebra; Pokud ale vaše podřízená aplikace očekává, že se výstupní schéma nikdy nezmění, může to způsobit problémy.

Návod

Zobrazuje se vám chyba? Ujistěte se, že spouštíte uloženou proceduru v kontextu databáze, která obsahuje tabulku, a ne v hlavní nebo jiné databázi.

Také doporučujeme, abyste se v těchto příkladech vyhnuli používání dočasných tabulek. Někteří klienti R ukončí připojení mezi dávkami a odstraní dočasné tabulky.

Sloučení nebo násobení sloupců s jinou délkou

R poskytuje velkou flexibilitu pro práci s vektory různých velikostí a pro kombinování těchto struktur podobných sloupců do datových rámců. Seznamy vektorů můžou vypadat jako tabulka, ale nedodržují všechna pravidla, která řídí databázové tabulky.

Následující skript například definuje číselnou matici o délce 6 a uloží ji do proměnné df1R . Číselná matice se pak zkombinuje s celými čísly tabulky RTestData, která obsahuje tři (3) hodnoty a vytvoří nový datový rámec df2.

EXECUTE sp_execute_external_script
    @language = N'R'
    , @script = N'
               df1 <- as.data.frame( array(1:6) );
               df2 <- as.data.frame( c( InputDataSet , df1 ));
               OutputDataSet <- df2'
    , @input_data_1 = N' SELECT [Col1]  from RTestData;'
    WITH RESULT SETS (( [Col2] int not null, [Col3] int not null ));

Chcete-li vyplnit datový rámec, R opakuje prvky načtené z RTestData tolikrát, kolikrát je potřeba, aby odpovídaly počtu prvků v poli df1.

Results

Sloupec 2	Sloupec 3
1	1
10	2
100	3
1	4
10	5
100	6

Mějte na paměti, že datový rámec vypadá jenom jako tabulka a ve skutečnosti je seznam vektorů.

Přetypování nebo převod dat

R a SQL Server nepoužívají stejné datové typy, takže když spustíte dotaz v SQL Serveru pro získání dat a pak je předáte modulu runtime jazyka R, obvykle se provede určitý typ implicitního převodu. Při vracení dat z R na SQL Server probíhá jiná sada převodů.

• SQL Server nasdílí data z dotazu do procesu R spravovaného službou Launchpad a převede je na interní reprezentaci za účelem vyšší efektivity.
• Modul runtime R načte data do proměnné data.frame a provádí vlastní operace s daty.
• Databázový stroj vrátí data do SQL Serveru pomocí zabezpečeného interního připojení a zobrazí data z hlediska datových typů SQL Serveru.
• Data získáte připojením k SQL Serveru pomocí klienta nebo síťové knihovny, která může vydávat dotazy SQL a zpracovávat tabulkové datové sady. Tato klientská aplikace může potenciálně ovlivnit data jinými způsoby.

Pokud chcete zjistit, jak to funguje, spusťte dotaz, jako je tento, v datovém skladu AdventureWorksDW . Toto zobrazení vrátí prodejní data použitá při vytváření prognóz.

USE AdventureWorksDW
GO

SELECT ReportingDate
         , CAST(ModelRegion as varchar(50)) as ProductSeries
         , Amount
           FROM [AdventureWorksDW].[dbo].[vTimeSeries]
           WHERE [ModelRegion] = 'M200 Europe'
           ORDER BY ReportingDate ASC

Poznámka:

Můžete použít libovolnou verzi AdventureWorks nebo vytvořit jiný dotaz pomocí vlastní databáze. Cílem je zkusit zpracovat některá data, která obsahují text, datum a čas a číselné hodnoty.

Teď zkuste vložit tento dotaz jako vstup do uložené procedury.

EXECUTE sp_execute_external_script
       @language = N'R'
      , @script = N' str(InputDataSet);
      OutputDataSet <- InputDataSet;'
      , @input_data_1 = N'
           SELECT ReportingDate
         , CAST(ModelRegion as varchar(50)) as ProductSeries
         , Amount
           FROM [AdventureWorksDW].[dbo].[vTimeSeries]
           WHERE [ModelRegion] = ''M200 Europe''
           ORDER BY ReportingDate ASC ;'
WITH RESULT SETS undefined;

Pokud dojde k chybě, budete pravděpodobně muset provést některé úpravy textu dotazu. Například predikát řetězce v klauzuli WHERE musí být uzavřen dvěma sadami jednoduchých uvozovek.

Jakmile dotaz zpracuje, zkontrolujte výsledky str funkce a podívejte se, jak R zpracovává vstupní data.

Results

STDOUT message(s) from external script: 'data.frame':    37 obs. of  3 variables:
STDOUT message(s) from external script: $ ReportingDate: POSIXct, format: "2010-12-24 23:00:00" "2010-12-24 23:00:00"
STDOUT message(s) from external script: $ ProductSeries: Factor w/ 1 levels "M200 Europe",..: 1 1 1 1 1 1 1 1 1 1
STDOUT message(s) from external script: $ Amount       : num  3400 16925 20350 16950 16950

• Sloupec datetime byl zpracován pomocí datového typu R POSIXct.
• Textový sloupec ProductSeries byl identifikován jako faktor, což znamená kategorickou proměnnou. Řetězcové hodnoty se ve výchozím nastavení zpracovávají jako faktory. Pokud předáte řetězec R, převede se na celé číslo pro interní použití a pak se namapuje zpět na řetězec ve výstupu.

Shrnutí

Z těchto krátkých příkladů vidíte, že při předávání dotazů SQL jako vstupu je potřeba zkontrolovat účinky převodu dat. Vzhledem k tomu, že R nepodporuje některé datové typy SQL Serveru, zvažte tyto způsoby, jak se vyhnout chybám:

• Otestujte data předem a ověřte sloupce nebo hodnoty ve schématu, které by mohly být při předání kódu R problémem.
• Určete sloupce ve vstupním zdroji dat jednotlivě místo použití SELECT *a zjistěte, jak se bude každý sloupec zpracovávat.
• Při přípravě vstupních dat proveďte explicitní přetypování, abyste se vyhnuli neočekávaným problémům.
• Vyhněte se předávání sloupců dat (například identifikátorů GUID nebo rowguids), které způsobují chyby a nejsou užitečné pro modelování.

Další informace o podporovaných a nepodporovaných datových typech najdete v tématech Knihovny jazyka R a datové typy.

Další kroky

Pokud se chcete dozvědět o psaní pokročilých funkcí jazyka R pomocí strojového učení SQL, postupujte podle tohoto rychlého startu:

Psaní pokročilých funkcí R pomocí strojového učení SQL