Analiza datelor folosind R și SQL. Analiza datelor în limbaj SQL

Irina Nikiforova și-a luat doctoratul în sociologie la Institutul de Tehnologie din Georgia. În prezent ea este coleg de cercetare Universitatea Naţională de Cercetare Şcoala Superioară de Economie. Ea este, de asemenea, profesor asociat la Departamentul de Metode și Tehnologii de Cercetare Sociologică, Facultatea de Sociologie, Campusul HSE din Sankt Petersburg.

Adnotare:

Programe cu sursă deschisă, cum ar fi sistemul de gestionare a bazelor de date Date MySQL iar limbajul de programare R, conceput pentru calculul statistic, a devenit mijloace standard analiza datelor în cercetarea academică și aplicată. Aceste instrumente pot fi utilizate pe o varietate de sisteme de operare, inclusiv Windows, Linux și Mac OS. Acest curs este destinat cercetătorilor cu competențe R și specializare în domeniu analiza statistica date din diverse discipline care ar dori, de asemenea, să stăpânească bazele de date MySQL, cum să le integreze cu R și cum să rezolve problemele tehnice care apar.

Cursul va introduce tehnici și instrumente de bază pentru gestionarea bazelor de date relaționale, inclusiv limbajul MySQL, reguli de formare a interogărilor în SQL, precum și data mining folosind R. În plus, vor fi discutate aspecte legate de compatibilitatea Excel, SPSS și MySQL. , și utilizarea instrumentelor statistice în limbajul R.

Se presupune că participanții la curs sunt familiarizați cu limbajul de programare R și au abilități de analiză statistică în R, precum și experiență de lucru cu baze de date. Pentru a participa la curs trebuie să aveți un laptop personal programe instalate R, Excel și SPSS.

Cursul include sarcini pentru muncă independentă.

• Programul cursului

Structura cursului:

• Seminar 1: Principii de lucru cu baze de date și sisteme de gestionare a bazelor de date; instalarea și elementele de bază ale utilizării MySQL și SQLite
• Atelierul 2: Navigarea bazei de date: Noțiuni de bază Limbajul SQLși generarea de interogări.
• Atelierul 3: Interogări complexe în SQL
• Atelierul 4: Managementul datelor și interoperabilitate
• Atelierul 5: Exploatarea datelor în R.

• Bessant, Conrad, Shadforth, Ian și Oakley, Darren. (2009). Construirea de soluții de bioinformatică: cu Perl, R și MySQL. Oxford, Marea Britanie: Oxford University Press.
• Spector, Phil. (2008). Manipularea datelor cu R (Folosiți R!). New York: Springer.
• Torgo, Luis. (2010). Exploatarea datelor cu R: Învățarea cu studii de caz. Chapman & Hall/CRC.
• Welling, Luke și Thomson, Laura. (2003). Tutorial MySQL. Indianapolis, Indiana, SUA: MySQL Press.

Combinație rapidă: DB2 10.5 cu BLU Acceleration și R

Grant Hutchison
Publicat 06.11.2014

R este un limbaj de programare open source care este excelent pentru analiza datelor și grafică. R a fost dezvoltat inițial de Ross Ihaka și Robert Gentleman de la Universitatea din Auckland. Aiheka și Gentleman au vrut să creeze un limbaj care să-i ajute în predarea studenților curs introductiv conform statisticilor. Limbajul R a fost conceput sub influența limbajului S, care a fost creat de Bell Labs în anii 1970. R este considerat un limbaj specific domeniului, deoarece a fost conceput în primul rând pentru analiza datelor - și îndeplinește această sarcină foarte bine. R poate fi folosit atât ca mediu interactiv, cât și ca scripturi și modele încorporate în pachete și integrate cu alte module software.

Limbajul R poate fi folosit pentru a analiza date dintr-o varietate de diverse surse date, inclusiv fișiere externe sau baze de date. Acest articol explică cum să vă conectați la date, cum să interogați datele și cum să actualizați datele care se află pe serverele de baze de date relaționale. Veți afla, de asemenea, de ce DB2 v10.5 cu BLU Acceleration este o alegere excelentă pentru analiză. În cele din urmă, articolul explică modul în care BLU Acceleration for Cloud facilitează încărcarea și analizarea seturilor de date folosind limbajul R.

Abrevieri frecvent utilizate

• CRAN: Comprehensive R Archive Network
• CSV: valori separate prin virgulă
• DSN: Numele sursei de date
• ODBC: Open Database Connectivity
• RODBC: Open Database Connectivity pentru R
• SIMD: date multiple cu o singură instrucțiune
• SQL: limbaj de interogare structurat

Date externe

În R, obiectele sunt create și gestionate într-o singură zonă de memorie. În cele mai multe cazuri, sarcinile de analiză a datelor necesită ca datele să fie disponibile sub forma unui cadru de date. Un cadru de date poate fi considerat ca matrice bidimensională date eterogene sau ca tabel în RAM (tabel în memorie). Dacă datele există deja ca fișier text delimitat, un utilizator R poate încărca datele în memorie pentru analiză folosind una dintre multele funcții precum read.xxx() , cum ar fi funcția read.csv() pentru fișierele CSV. De asemenea, dacă un cadru de date R trebuie să fie „externalizat” ca fișier, pot fi utilizate multe funcții precum write.xxx().

Limbajul R vă permite să salvați cadre de date pe disc ca obiecte (adică, în afara memoriei sale) folosind funcțiile încorporate saveRDS(), save() și save.image(). Aceste obiecte pot fi apoi citite înapoi în memorie pentru analize ulterioare. Cu toate acestea, acest mecanism de persistență are număr mare neajunsuri, inclusiv dimensiuni limitate spațiu pentru cadre de date. În plus, există probleme asociate cu utilizarea fișiere text pentru analiza datelor; necesită un efort semnificativ în curățarea datelor și în transferul seturilor de date către alți utilizatori.

Se pune întrebarea: dacă datele dumneavoastră pentru analiză sunt generate folosind sisteme de operare existente și baze de date relaționale, atunci de ce să nu extrageți pur și simplu datele din sistem de operareși nu populați o bază de date analitică cu aceste date în scopul analizării acesteia folosind R?

Care sunt beneficiile utilizării unei baze de date relaționale cu limbajul R?

R poate fi folosit pentru a explora date și pentru a crea modele predictive. Serverele de baze de date relaționale sunt proiectate să proceseze volume mari date și contribuie la asigurarea coerenței datelor pentru utilizatorii concurenți. Deoarece datele din bazele de date relaționale sunt de obicei stocate într-o formă normalizată, atunci când efectuați sarcini de analiză exploratorie a datelor (EDA), va trebui probabil să utilizați unele abilități SQL pentru a combina atribute relevante în mai multe tabele. Dacă lucrați cu un administrator de baze de date sau un analist de date care este calificat în baze de date relaționale, puteți crea vizualizări auxiliare numai pentru citire pentru a accelera analiza datelor sursă. Când lucrați cu date într-un mediu DB2, puteți utiliza instrumentul IBM Data Studio sau consola web IBM BLU Acceleration for Cloud pentru a explora schema bazei de date sau pentru a defini noi vederi pentru a facilita accesarea datelor din scripturile dvs. R.

În mod implicit, înainte de a efectua orice analiză, mediul R încarcă toate datele în RAM. Când procesarea interogărilor este gestionată de un server de baze de date relaționale, cum ar fi DB2, sistemul nu va încărca pur și simplu toate datele de pe disc în memorie. Serverul bazei de date va analiza cererea în ansamblu și va determina cel mai mult metoda eficienta obtinerea rezultatelor solicitate.

DB2 cu accelerare BLU

DB2 10.5 cu BLU Acceleration este optimizat pentru sarcini de lucru analitice și, prin urmare, este alegere excelenta pentru a analiza datele folosind R. Există multe motive pentru a accepta DB2 cu BLU Acceleration, inclusiv următoarele:

• Simplitate
  • Analiza datelor poate fi efectuată imediat după descărcare. Funcționalitatea BLU este optimizată pentru analiză, eliminând necesitatea de a crea indici sau de a efectua orice sarcini de configurare.
• Performanţă
  • BLU folosește un model de stocare în coloană, care reduce numărul de citiri pe disc și minimizează cantitatea de memorie folosită pentru a suporta foarte multe procesare rapidă cereri.
  • Metodele avansate de comprimare a datelor aplicate implicit reduc utilizarea memoriei și a spațiului pe disc.

Spre deosebire de mediul R, DB2 cu BLU Acceleration explorează toate capabilitățile hardware ale serverului său și optimizează utilizarea resurselor. De exemplu, atunci când este posibil, DB2 utilizează instrucțiuni SIMD ale procesorului pe mai multe nuclee. În plus, DB2 utilizează diverse mecanisme de memorare în cache pentru a reduce numărul de citiri de date de pe disc. Această optimizare se realizează automat. Platforma este acum disponibilă și în cloud ca BLU Acceleration for Cloud.

BLU Acceleration for Cloud

BLU Acceleration for Cloud este un server de baze de date bazat pe web optimizat pentru simplitate maximă. În doar câteva minute, puteți crea tabele, puteți încărca date și puteți începe analiza. Utilizatorilor li se oferă instrumente adecvate (IBM Data Studio și IBM InfoSphere Data Architect) pentru a simplifica crearea și întreținerea modelelor și obiectelor bazei de date (cum ar fi tabelele). Odată ce schema dvs. este creată, puteți utiliza consola web a soluției BLU Acceleration for Cloud pentru a vă încărca datele. Există multe modalități de a descărca date, inclusiv fișiere locale, servicii stocare în cloud(de exemplu, Amazon S3), IBM InfoSphere DataStage. În consola web puteți efectua analize de la folosind Excel, SQL, Cognos Business Insight (BI), precum și scripturi și modele în R.

În fig. Figura 1 prezintă două scenarii diferite pentru un utilizator R care lucrează cu date stocate în mediul BLU Acceleration for Cloud. Un analist de date își poate folosi mediul R, cum ar fi RStudio, pentru a extrage date și a le analiza pe el propriul calculator. Ca varianta alternativa analistul poate crea și executa scripturi R pe un server cloud printr-un browser.

Figura 1. Explorarea datelor folosind R (la distanță)

Diagrama din fig. 2 prezintă un exemplu de mediu R integrat în mediul BLU Acceleration for Cloud. BLU Acceleration for Cloud oferă R runtime și stocare de date în infrastructura cloud. BLU Acceleration for Cloud oferă o consolă bazată pe web care vă permite să descărcați date și să efectuați analize în doar câteva minute. Analiza datelor poate fi efectuată folosind instrumente SQL, instrumente BI, scripturi R și modele R. Aveți la dispoziție atât un simplu editor de script R, cât și posibilitatea de a lansa RStudio direct din browser. În fig. Figura 2 prezintă un exemplu de astfel de integrare, inclusiv rezultatele grafice din analiza datelor recensământului din SUA. Pentru a crea scripturi noi, faceți clic pe semnul (+) evidențiat în stânga colțul de sus ferestrele din fig. 2. Scripturile R sunt gestionate în cloud în propriul spațiu de lucru. De asemenea, puteți importa scripturi pentru a lucra cu ele în cloud.

Figura 2. Explorarea datelor în cloud folosind R

BLU Acceleration for Cloud - Plan de încercare

Cea mai bună modalitate de a câștiga experiență folosind R pentru baze de date este utilizarea versiune de încercare BLU Acceleration for Cloud Trial Plan ca parte a programului open beta. Pentru a începe, accesați site-ul web BLU Acceleration for Cloud. Faceți clic pe butonul Încercați acum BLU Acceleration; veți fi direcționat către pagina Planuri și prețuri. Planul Solo include furnizarea unui mediu de analiză BLU cu furnizorul ales de dvs servicii cloud(IBM SoftLayer sau Amazon Servicii Web), iar planul de încercare vă va oferi acces instantaneu la propriul mediu pentru ora 5.

În fig. Figura 3 arată Accelerația BLU pentru widget pentru planul de încercare. Faceți clic pe butonul început. Sistemul dumneavoastră va începe inițializarea; când serverul dvs. este gata, faceți clic pe Porniți accelerarea BLU. Acum vă puteți conecta la consola web cu ID-ul dvs. de utilizator blueadminși parola furnizată. Pentru a verifica integrarea R, accesați zona Dezvoltarea de scripturi Rși creați un script de testare folosind codul prezentat mai jos în Lista 1. Din moment ce ați inițializat mediu nou, probabil vi se va solicita să instalați R și RStudio (această procedură nu durează mai mult de două minute). În Lista 1, veți vedea că un pachet numit bluR este încărcat în script. Acest pachet (pe în acest moment este disponibil numai ca parte a BLU Acceleration for Cloud) folosește RODBC ca interfață de acces la date.

Figura 3. BLU Acceleration for Cloud - Plan de încercare

Tipuri de date și considerente de proiectare

Datele din mediul R și datele stocate în baze de date relaționale au anumit tip sau structura. Trebuie să înțelegeți diferențele dintre tipurile de date pentru a afișa valorile în mod corespunzător.

R are destul set limitat tipuri de bază date: caractere, numerice (numere întregi și numere cu virgulă mobilă cu precizie dublă), logice, brute (date binare). Vectorii de caractere din R se mapa pur și simplu la date SQL de tip CHARACTER sau VARCHAR. Tipuri numerice Datele serverului de baze de date sunt de obicei mapate la tipurile de date INTEGER și DOUBLE în mediul R Deoarece tipul de date logic în R nu are un echivalent evident între tipuri date SQL, acest tip este de obicei mapat la un câmp text din tabelele bazei de date. Când lucrați cu date din seria temporală, trebuie să luați în considerare cu atenție modul în care datele vor fi afișate între R și serverul bazei de date.

Din punct de vedere metode statistice Datele continue și categorice sunt procesate foarte diferit. Utilizatorii R sunt conștienți de aceste diferențe și codifică datele categorice folosind un tip de date precum factor Odată ce datele au fost extrase dintr-o bază de date, trebuie să se ia în considerare dacă datele ar trebui tratate ca date în formă continuă sau ca date categorice. Dacă datele sunt cu adevărat categorice, atunci variabila corespunzătoare trebuie recodificată sau turnată folosind funcția factor().

Tabelul 1 oferă o mapare a termenilor dintre R și SGBD relațional.

Tabelul 1. Comparația terminologiei (R și RDBMS)

Opțiuni de conectare

IBM Netezza și R

IBM Netezza Analytics este un sistem încorporat platforma specializata pentru analize aprofundate. Sistemele hardware și software Netezza acceptă mai multe în diverse moduri utilizarea lui R, inclusiv analiza în baza de date și analiza clientului. cu informații despre utilizarea R pe platforma Netezza.

Limbajul R nu are capabilități încorporate de acces baze de date relaționale date, dar astăzi există un număr mare de interfețe de acces la date care pot fi obținute de la furnizori sau de pe site-ul web CRAN (Comprehensive R Archive Network).

În continuare, veți învăța cum să utilizați RJDBC sau RODBC pentru a accesa datele stocate în serverele de baze de date DB2 din mediul R. Dacă intenționați să accesați alte servere de baze de date, puteți utiliza aceleași interfețe sau puteți explora alte pachete pentru a accesa anumite baze de date.

Conexiune folosind RJDBC

Pachetul RJDBC se bazează pe interfața DBI (interfață de bază de date), care este utilizată pe scară largă în comunitatea R. Pachetul DBI conține clase virtuale pe care driverul de furnizare este responsabil pentru implementare. RJDBC utilizează o combinație între un driver de bază de date compatibil JDBC și un JRE (Java Runtime Environment) pentru a comunica între R și serverul bazei de date. Vom folosi driverul IBM JDBC (tip 4) pentru a menține conexiunea. Acest driver IBM JDBC poate fi utilizat pentru a accesa bazele de date din familia DB2, inclusiv DB2 pentru Linux, Unix, Windows, DB2 pentru z/OS, DB2 i. Dacă nu aveți deja pachetul RJDBC instalat în mediul dvs. R, instalați-l din depozitul CRAN cu comanda install.packages("RJDBC") .

În Lista 1, pachetul RJDBC încarcă un obiect R care reprezintă driverul JDBC. Ruta către fișierul db2jcct4.jar trebuie specificată în variabila CLASSPATH; și JAVA_PATH ar trebui să indice locația JRE care rulează. Funcția dbConnect() alocă un obiect serverului de baze de date canal(canal sau conexiune). Conexiunea se face la o bază de date numită SAMPLEDB, care se află pe un server cu numele de gazdă blueforcloud.imdemocloud.com. Vă rugăm să rețineți că conexiunea necesită o parolă acest scenariu nu este salvat.

Lista 1. BLU pentru Cloud - script R

Petreceți câteva minute lucrând cu planul de încercare pentru BLU Acceleration for Cloud și cu scriptul afișat în Lista 1. Restul acestui articol va oferi mai multe informații despre accesarea datelor de la R.

Lista 2 arată cum să utilizați funcția dbSendQuery() pentru a trimite o interogare SQL către server printr-un obiect de conexiune. Datele reale sunt returnate clientului numai atunci când funcția fetch() este executată. Acest script returnează toate rândurile din setul de rezultate deoarece al doilea parametru este setat la -1.

Lista 2. RJDBC - regăsirea datelor

În Lista 3, cadrul de date df conține rezultatele interogării. Scopul acestui script este de a identifica tipurile de vehicule cu cea mai bună eficiență a consumului de combustibil și modelul corespunzător. Rezultatele arată că, pe baza tabelului nostru de 1.165 de vehicule, Toyota Prius are cea mai bună economie de combustibil combinată.

Lista 3. RJDBC - Dezactivare

Dacă valorile NULL sunt returnate dintr-un tabel al bazei de date, acestea apar în cadrul de date R ca fiind indisponibile (NA). Funcția na.omit() din prima linie a Lista 3 elimină toate cazurile cu valori lipsă. Variabilele R cadru de date sunt preluate din DB2 ca șiruri de caractere majuscule. Pentru a elibera resursele bazei de date pe server, nu uitați să rulați funcția dbDisconnect() la sfârșitul scripturilor dvs. R.

De asemenea, puteți lega obiectele R la instrucțiuni SQL folosind funcția dbSendQuery() (Listarea 4).

Lista 4. RJDBC - folosind parametri

În Lista 4, producătorul valorii nu face parte din organismul de solicitare; este trimis ca parte a cererii atunci când funcția dbSendQuery() este executată.

În fig. Figura 4 arată boxplot-ul pe care l-am creat cu ușurință din cadrul de date returnat.

Figura 4. Exemplu de diagramă cu casete

Conexiune folosind RODBC

RODBC este o interfață de acces la date DB2 mai eficientă și mai rapidă pentru utilizatorii R. Pachetul RODBC este disponibil în depozitul CRAN și este utilizat de mulți membri ai comunității R. Dacă pachetul RODBC nu este deja instalat în mediul dumneavoastră R cu comanda install.packages ("RODBC ").

Interfața ODBC a fost dezvoltată inițial de Microsoft la începutul anilor 1990. De atunci, a devenit standardul oficial pentru accesul la date sub denumirea generală SQL/CLI (Call Level Interface). Mediul ODBC constă din componenta ODBC Driver Manager și un driver compatibil ODBC pentru un anumit server de bază de date. Pe sistemele de operare Windows, componenta ODBC Driver Manager este încorporată în platformă însăși, dar pe Linux sau alte platforme, componenta ODBC Driver Manager trebuie instalată separat.

Pentru a vă conecta la o bază de date utilizând driverul RODBC, trebuie să specificați locația serverului, numele bazei de date și informațiile de acces (de exemplu, numele de utilizator și parola). Numele bazei de date este de obicei specificat ca un DSN ODBC. Un nume DSN este o legătură detaliată către o bază de date, care poate fi fie locală, fie la distanță în raport cu computerul client. Un DSN poate fi considerat ca un alias pentru baza de date - nu trebuie să se potrivească cu numele actual al bazei de date configurat pe server.

Pentru a crea nume DSN pe platforma Windows, utilizați instrumentul ODBC Data Source Administrator, disponibil în meniul Panou de control->Instrumente de administrare. În fig. Figura 5 prezintă mai multe DSN-uri definite pe acest computer client.

Figura 5. Instrumentul Windows ODBC Data Source Administrator

Dacă intenționați să utilizați R și DB2 Express-C pentru Windows cu baze de date locale, DSN-urile pentru bazele de date trebuie să existe deja pe sistem. Dacă doriți să accesați o bază de date la distanță, trebuie să faceți următoarele: obțineți detaliile conexiunii de la furnizorul de server de baze de date, catalogați conexiunea și setați DSN-ul în instrumentul ODBC Data Source Administrator.

În plus, există unul pe care îl puteți folosi dacă doriți să faceți fără nume DSN.

Pachetul RODBC oferă mai multe funcții legate de conexiuni (Tabelul 2).

Tabelul 2. RODBC - Funcții de conexiune și metadate

După ce am stabilit o conexiune ( canal) cu baza noastră de date, putem folosi funcții de metadate pentru a examina tipurile de date acceptate, definițiile tabelelor și coloanele definite în acestea.

Tabelul 3 prezintă trei funcții utile de metadate a bazei de date disponibile în RODBC.

Tabelul 3. RODBC - Funcții de bază de date și metadate de tabel

După ce ați determinat cu ce tabel doriți să lucrați, utilizați funcția sqlFetch() pentru a prelua datele din R. RODBC va mapa acele date la tipul de date R adecvat pe baza mapărilor specificate conținute în rezultatul getSqlTypeInfo("DB2 /NT ").

Luați în considerare un script R simplu care stabilește o conexiune la o bază de date folosind un DSN și specifică câteva informații de bază. Programul va determina numărul de obiecte și coloane din tabel din tabelul specificat. Funcția sqlFetch() preia toate datele din R și arată primul rând care a fost returnat în cadrul de date.

În Lista 5, funcțiile odbcConnect() și metadate arată că există 27 de tabele în schema „DB2INST1” și că tabelul de economie de combustibil are 18 coloane definite. Funcția sqlFetch() interogează conținutul unui tabel și creează un cadru de date numit cars. Primele patru variabile ale primei observații din cadrul de date sunt afișate utilizatorului; conexiunea la serverul bazei de date este apoi închisă folosind funcția odbcCloseAll().

Lista 5. RODBC - stabilirea unei conexiuni și utilizarea funcțiilor de metadate

O altă metodă pentru stabilirea conexiunilor la bazele de date DB2 utilizând RODBC este utilizarea funcţiei odbcDriverConnect(). În Lista 6, observați că db.name este numele real al bazei de date de pe serverul bazei de date, care Nu trebuie să se potrivească cu numele DSN creat pe computerul client.

Lista 6. RODBC - metoda de conectare directă

Extragerea datelor

Principala motivație pentru utilizarea R pentru a analiza datele stocate pe serverele de baze de date în loc de analiza fișierelor este că această abordare ajută la depășirea provocărilor asociate cu seturile mari de date. După cum s-a menționat mai sus, în mod implicit, R încarcă toate datele în memorie, astfel încât lucrul cu seturi mari de date va fi mai eficient dacă recuperați doar bucăți de date folosind interogări SQL condiționate.

După cum se arată în Tabelul 4, funcția sqlQuery() vă permite să trimiteți orice interogare SQL validă către serverul bazei de date. Dacă aveți autoritate de a DROP tabele sau de a șterge date, sunt disponibile și funcțiile sqlDrop() și sqlClear().

Tabelul 4. RODBC - funcții de extragere și ștergere a datelor

Funcția sqlQuery() extrem de versatilă poate fi utilizată pentru a emite instrucțiuni SQL DML (Limbaj de manipulare a datelor) precum SELECT, INSERT, UPDATE, DELETE și pentru a emite instrucțiuni SQL DDL (Limbaj de definire a datelor), cum ar fi CREATE TABLE. Declarația SQL din Lista 7 conține o eroare. Tipul de date este specificat incorect - fraza INTEGR trebuie să aibă forma INTEGER. Din fericire, avem la dispoziție funcția odbcGetErrMsg() pentru a obține și afișa un mesaj de eroare detaliat. Această caracteristică este foarte utilă atunci când editați și testați scripturile R.

Lista 7. RODBC - diagnosticarea erorilor

Stocarea datelor

Salvarea datelor în baze de date relaționale se face de obicei folosind instrucțiunea SQL INSERT. Dacă aveți un cadru de date R și doriți să stocați acele date într-o bază de date relațională, utilizați funcția sqlSave(). În mod implicit, această funcție creează un nou tabel cu aceleași date conținute în variabila cadrul de date. Numele coloanelor se vor potrivi, de asemenea, cu numele coloanelor din cadrul de date. Dacă doriți, este posibil să nu doriți să salvați numele rândurilor R din cadrul de date din tabelul salvat, deoarece există de obicei o cheie naturală în setul dvs. de date.

Funcția sqlSave() populează un tabel cu date dintr-un cadru de date folosind instrucțiunea SQL INSERT. Dacă doriți să stocați un set mare de date, poate fi de preferat să creați un fișier de date delimitat extern și apoi să utilizați un încărcător de mare viteză specific bazei de date pentru a publica datele și a le partaja cu alți analiști de date. Odată ce datele dvs. sunt stocate într-o bază de date relațională, nu mai trebuie să vă faceți griji cu privire la menținerea integrității și disponibilității acestora - serverul de baze de date se ocupă de ele.

În Lista 8, funcția sqlSave() creează și populează un tabel nou. Deoarece cadrul de date este numit CLASSMARKS, tabelul va avea același nume. În acest scenariu, opțiunea mai sigură permite scriptului să înlocuiască un tabel existent cu același nume. În mod implicit, este returnată o eroare când se încearcă salvarea datelor într-un tabel existent.

Lista 8. RODBC - salvarea datelor

De asemenea, puteți actualiza datele din tabelele existente folosind funcția sqlUpdate() RODBC. Ca o alternativă mai eficientă, recomand să folosiți instrucțiunile SQL UPDATE sau pur și simplu să salvați noile date într-un tabel temporar folosind funcția sqlSave() și apoi să efectuați o instrucțiune SQL UPSERT pe acele tabele. Funcția sqlUpdate() necesită un parametru index pentru a identifica în mod unic fiecare rând dintr-un tabel existent.

Utilizarea procedurilor stocate

Procedurile stocate sunt module software gestionate și executate de serverul bazei de date. Deoarece datele se află pe același server ca și programul, procedurile stocate pot fi foarte eficiente și pot efectua unele sarcini mult mai rapid decât aplicațiile de pe partea client. Procedurile stocate DB2 pot fi scrise folosind o varietate de limbaje de programare, cum ar fi Java, C, PL/SQL și SQL PL. Dacă aveți proceduri stocate gata făcute sau dacă munca dvs. analitică poate fi simplificată sau îmbunătățită folosind proceduri stocate, acestea pot fi apelate cu ușurință dintr-un script R folosind RJDBC sau RODBC.

În Lista 9, procedura stocată GETMEDIAN este apelată folosind funcția sqlQuery(). În acest scenariu, datele returnate sunt un cadru de date care conține o singură valoare. Subiectul de interes este trecut la procedura stocată ca parametru de intrare. În prezent, nu există un mecanism disponibil pentru preluarea mai multor seturi de rezultate sau parametri de ieşire dintr-o procedură stocată DB2.

Lista 9. RODBC - proceduri stocate

Concluzie

Limbajul R este un instrument puternic open-source de analiză a datelor care vă ajută să explorați datele și să construiți modele predictive. Când datele dvs. depășesc limitele de memorie R, puteți recurge la încărcarea datelor într-un server de baze de date, cum ar fi IBM DB2 cu BLU Acceleration sau IBM BLU Acceleration for Cloud. În acest articol, am explorat beneficiile utilizării R cu baze de date în loc să analizăm datele în fișiere text delimitate. De asemenea, ați fost familiarizat cu utilizarea pachetelor RJDBC și RODBC pentru a analiza datele mari stocate în DB2.

Dacă vrei să devii un cercetător al datelor, nu te poți descurca fără SQL. Acest limbaj poate fi folosit nu numai de analiști sau oameni de știință, ci și de oricine poate petrece câteva zile studiind interogările SQL:

SQL pentru analiza datelor? Ce este asta? Ce folos are?

SQL înseamnă Structured Query Language, care se traduce prin „ limbaj de interogare structurat».

Dar îmi place o altă descriere: este Excel pe steroizi. Iată cum arată același set de date în Excel și SQL:

După cum putem vedea, SQL și Excel sunt destul de asemănătoare. Ambele prezintă datele ca un tabel bidimensional cu coloane și rânduri. Ambele sunt extrem de structurate și ușor de înțeles.

Dar există o serie de diferențe între ele:

1. Primul este performanţă. Excel funcționează excelent cu cantități mici de date, dar atunci când aveți 100.000 de rânduri, utilizarea formulelor devine un exercițiu consumator de timp și ineficient.
2. Al doilea este mod de a interacționa cu baza de date. Excel este în primul rând o interfață grafică de utilizator ( GUI). Vă permite să derulați tabelul, să introduceți formule și să utilizați cursorul pentru a muta zona dorită. SQL nu are o GUI - folosește ceea ce se numesc interogări SQL.

Când stăpânești Interogări SQL pentru începători, veți înțelege că lucrul cu acesta este mai transparent și mai eficient decât cu Excel. În plus, în SQL este mult mai convenabil să vă automatizați munca și să utilizați dezvoltările anterioare.

În analiza datelor, veți folosi SQL pentru sarcini destul de simple: rezumat, combinarea seturi de date, metode simple statistice și matematice. Dar cu SQL, munca ta va deveni mai eficientă și vei putea folosi seturi de date mai mari decât înainte.

Ce zici de Python, R sau bash?

Când începeți să utilizați aceste limbaje pentru analiza datelor, vă veți da seama că Python și R sunt bune pentru un lucru, iar SQL pentru altul. Principala diferență dintre aceste limbi este în sintaxă, „ funcții„și productivitatea. Ei bine, acum să trecem la partea practică!

Pasul 0 – Instalați sistemul de gestionare a bazelor de date SQL

Voi folosi postgreSQL. Există și alte tipuri de SGBD SQL. Dar toate sunt similare, așa că dacă înveți postgreSQL, adaptarea la o altă limbă va dura doar câteva ore (sau chiar minute).

În acest moment, ar trebui să aveți trei lucruri:

1. Server de date cu acces prin terminal sau iTerm;
2. PostgreSQL instalat pe serverul dvs.;
3. Pgadmin4 instalat pe computer ( sau SQL Workbench).

Notă: voi folosi SQL Workbench ca instrument SQL.

Pasul 1 – Conectați-vă la baza de date SQL prin linia de comandă

Mai întâi trebuie să accesați baza de date din linia de comandă. Vă voi arăta cum să faceți acest lucru, iar pe viitor va trebui să faceți același lucru:

1. Deschideți terminalul ( sau iTerm):
2. Conectați-vă la server prin SSH.

In cazul meu intru:

ssh tomi@[adresa_ip_mea]

1. Prin logarea pe server am acces la baza de date postgreSQL. Deoarece am permis deja accesul utilizatorului meu, introdu următoarea comandă:

psql este comanda în sine, -U indică numele de utilizator ( în cazul meu este „tomi”), și -d specifică numele bazei de date ( in cazul meu postgres, ca al tau). Linia de comandă ar trebui să se schimbe în următoarele:

Postgres =>

Gata! Acum aveți acces complet la baza de date SQL .

1. Pentru a verifica interogarea SQL, puteți introduce:

Pe ecran va apărea o listă cu toate tabelele de date. Până acum există doar unul. Dar asta se va schimba în curând:

Nota: bazele de date SQL se mai numesc și „ baze de date relaționale ».

Pasul 2 – Încărcarea datelor

În această secțiune, vom lucra cu un mic set de date numit zoo(" grădină zoologică"). Îl puteți descărca de aici în format text .tsv.

Dar să continuăm cu exemplul de interogare SQL:

1) Creați un tabel:

CREATE TABLE zoo(animal varchar(10), uniq_id integer PRIMARY KEY, water_need integer);

2) Verificați dacă tabelul a fost creat cu siguranță:

Avem un nou tabel de date: zoo!

3) Se încarcă date!

Copiați cererea:

INSERT INTO zoo (animal,uniq_id,water_need) VALORI ("elephant",1001,500), ("elephant",1002,600), ("elephant",1003,550), ("tigru",1004,300) , ("tigru",1005,320), ("tigru",1006,330), ("tigru",1007,290), ("tigru",1008,310), ("zebra",1009,200) , ("zebra",1010.220), ("zebra",1011.240), ("zebra",1012.230), ("zebra",1013.220), ("zebra",1014.100) , ("zebra",1015,80), ("leu",1016,420), ("leu",1017,600), ("leu",1018,500), ("leu",1019,390) , ("cangur", 1020,410), ("cangur", 1021,430), ("cangur", 1022,410);

Dacă totul a mers bine, veți primi următorul mesaj:

INSERT 0 22

Cea mai importantă instrucțiune SQL: SELECT

Este timpul să studiem operatorul principal Limbajul de interogare SQL. Iată-l:

SELECT * FROM table_name;

Vom folosi SELECT de fiecare dată când vom citi, filtra, transforma și rezuma datele. Pentru început, să selectăm ( Cuvântul în engleză select este tradus exact ca „alege”) tot ce este în masa de la grădina zoologică.

SELECTAȚI * FROM grădina zoologică;

Operatorul mi-a returnat întreaga masă.

SELECTAȚI * FROM grădina zoologică;

SELECT este instrucțiunea de bază care îi spune SQL că vrem să citim date dintr-un tabel.
* - indică faptul că dorim să selectăm toate coloanele.
FROM îi spune SQL că vom specifica un tabel din care să citim datele.
zoo este numele mesei. Poate fi înlocuit cu oricare altul dacă aveți mai multe mese.
;

Vom folosi expresia discutată mai sus în mod regulat atunci când lucrăm cu interogări SQL complexe.

Selectarea coloanelor

Semnul * poate fi înlocuit cu nume de coloane. Luați în considerare următoarea interogare ca exemplu:

SELECT animal, water_need FROM zoo;

Obținem exact ceea ce ne așteptam: pe ecran sunt coloanele „animal” și „water_need”, dar nu mai „uniq_id”.

În același mod, puteți selecta orice coloană, doar specificați numele acestora, separate prin virgulă. Ai putea sa faci si asa ceva:

SELECT animal, animal, animal FROM zoo;

Aceeași coloană se va repeta de mai multe ori... Dar din moment ce nu are rost în asta, nu merită să faci asta.

Afișează primele câteva rânduri de date - instrucțiune LIMIT

Momentan lucrăm cu un tabel de date format din 22 de rânduri. Pentru a limita selecția la primele câteva rânduri, vom folosi instrucțiunea LIMIT - un mic " expansiune» la interogarea de bază:

SELECTAȚI * DIN Grădina Zoologică LIMITĂ 10;

Doar primele 10 linii vor fi afișate pe ecran.

Filtrarea rândurilor - Declarație WHERE

Folosind clauza WHERE, puteți selecta anumite rânduri pe baza valorilor acestora. Exemplu de interogare SQL:

SELECT * FROM zoo WHERE animal = „elefant”;

SELECTAȚI * DIN Grădina Zoologică – „acest” cerere de bază»
WHERE - spune SQL că doriți să filtrați ceva.
animal = „elefant” –» animal este numele coloanei în care căutăm valoarea dorită, iar elefant este valoarea în sine. În SQL trebuie întotdeauna să adăugăm o coloană în care căutăm valoarea dorită.
; – „Nu uitați de punctul și virgulă!

Autotest #1

Acesta este un articol introductiv, deci prima sarcină va fi, de asemenea, destul de simplă:

Selectați primele trei zebre ( zebră) de la grădina zoologică de masă .

Soluția la această problemă va fi, de fapt, rezultatul articolului de astăzi.

Sunteţi gata?

Iată soluția mea:

SELECT * FROM zoo WHERE animal = "zebra" LIMIT 3;

De acord, SQL este ușor .

Si inca ceva: sintaxa...

1. Toate interogările SQL trebuie să se încheie cu punct și virgulă (; ). Dacă o săriți din greșeală, SQL va presupune că interogarea nu este finalizată și nu va fi afișat nimic. De exemplu:

Cumva nu foarte... Va fi mai bine așa:

Postgres => SELECT * FROM zoo;

1. SQL nu face distincție între majuscule și minuscule când vine vorba de cuvinte cheie ( SELECT, UNDE, LIMIT etc..). De exemplu:

functioneaza la fel ca

Selectați * din grădina zoologică;

Sensibilitatea numelor de tabel, a coloanelor și a valorilor acestora depinde de setări. In situatia noastra ( când utilizați postgreSQL) numele tabelelor și coloanelor nu țin cont de majuscule, spre deosebire de valorile câmpurilor. De exemplu:

SELECT * FROM zoo WHERE animal = „elefant”; –" WORKS SELECT * FROM ZOO WHERE ANIMAL = 'elefant'; –» LUCRĂRI SELECT * DIN Grădina Zoologică WHERE ANIMAL = „ELEFANT”; -" NU FUNCTIONEAZA

Rețineți că majoritatea oamenilor scriu cuvinte cheie cu majuscule în limbajul de interogare SQL ( SELECT, UNDE, LIMIT, etc... ), ceea ce crește lizibilitatea codului.

Concluzie

Am făcut primul pas către utilizarea SQL pentru analiza datelor! Felicitări! Acum puteți scrie interogări de bază. Dar acesta este doar începutul!

Traducerea articolului „ SQL pentru analiza datelor – Tutorial pentru începători – ep1” a fost pregătit de echipa prietenoasă a proiectului Website Building from A to Z.

Sursa Wiki

(se poate da clic)

Importul și analiza e-mailurilor în Outlook

(se poate da clic)

Pro: PowerQuery este unul dintre cele mai puternice instrumente, care, pe de o parte, are o funcționalitate largă pentru analiză, dar, pe de altă parte, este destul de dificil de învățat și este folosit în principal de analiști. Funcționează atât cu modele tabulare, cât și cu modele multidimensionale. Capabil să conecteze surse suplimentare

Contra: Nu este potrivit pentru utilizatorii „obișnuiți”, greu de învățat, destul de lent. Nu există nicio posibilitate de partajare a accesului, restricții privind dimensiunea fișierelor/înregistrărilor etc.

Materiale suplimentare

• Deep Dive în limbajul de interogare(video)
• Selectare video datăŞi două
• O selecție de articole de la Microsoft

MS Power BI

Caracteristici principale:

• Dezvoltarea rapidă a rapoartelor informative de afaceri și a tablourilor de bord (online) - cu capacitatea de a interacționa și de a explora datele.
• Actualizare automată a rapoartelor și vizualizărilor BI atunci când datele se modifică
• Suport pentru limbajul de interogare, inclusiv Power Query. Posibilitatea de interacțiune între participanți la nivelul cererii.
• Crearea unui catalog de date cu indici de căutare.
• Limbajul de interogare este aproape de natural (pentru un analist de afaceri) și de posibilitatea de lucru interactiv.
• Suport pentru dispozitive mobile

Exemplu cu interogări și combinarea surselor: combinarea surselor de la Bursa de Valori din New York și indicele financiar S&P 500.

(se poate da clic)

Pro: produs nou modern, interfață prietenoasă, ușor de învățat, soluție online.

Contra: soluția este „brută” (unele componente pot fi instabile), nu funcționează cu cuburi OLAP, funcționalitate redusă în comparație cu concurenții.

Materiale suplimentare:

• Video introductivîn Power BI
• Exemplu de utilizare a Power BI pentru analiza financiară
• Prezentare generală a caracteristicilor(video)
• Analiză Istoricul Cupei Mondiale folosind Excel și Power BI

Pyramid Analytics

• Data Mining - Descoperirea datelor
• Lucru interactiv cu date și vizualizare - Tablouri de bord
• Prezentarea datelor publicului dvs. - editor

Video introductiv:

(Canalul YouTube Pyramid Analytics)

Platforma are capacitatea de a analiza și modela în comun datele, precum și o serie de alte caracteristici utile:

PyramidAnalytics este cel mai puternic instrument pe care îl folosim.

Pro: ușor de învățat, funcționează cu un număr mare de surse, funcționalitate foarte largă.

Contra: pret.

Componente de analiză a serverului MS SQL (MDS, SSIS, SSAS)

Servicii de integrare SQL Server- migrarea și integrarea datelor. Citeşte mai mult.

Servicii de analiză SQL Server OLAP și data mining în interiorul serverului SQL. Citeşte mai mult.

Instrumentul principal este creierul

Instrument bonus

Datazen este o soluție multi-platformă care vă permite să construiți rapoarte, indiferent de platformă (PC, iOS, Android, Windows Phone). O caracteristică distinctivă a produsului este integrarea sa largă și capacitatea de a opera pe deplin platforma analitică prin intermediul dispozitivelor mobile.

Pro: acceptă o gamă largă de platforme, este susținut și dezvoltat de MS, funcționează cu un număr mare de surse.

Contra: Nu funcționează direct cu OLAP (trebuie să creați singur interogări MDX), are o serie de limitări (de la capacitățile de interfață la funcționalitatea de personalizare), nu se vinde separat, vine la pachet cu MS SQL Enterprise Edition.

În următorul articol vom analiza în detaliu Datazen și Pyramid Analytics și, de asemenea, vom înțelege cum să cream rapoarte bazate pe acestea.