Musteen uusi FILE SQL

Olen tehnyt kokeiluja aiheesta, jota suunnittelin jo aikaisemmin eli olen koittanut saada Musteeseen mukaan mahdollisuuden tehdä SQL-kyselyjä suoraan SVO-tiedostoista.

Kokeilut vaikuttavat erittäin lupaavilta ja prototyyppi on jo olemassa. Toteutuksessa hyödynnetään SQLite-tietokantaa sen R-liittymän välityksellä, mutta kuitenkin niin, että luku SVO-tiedostoista tapahtuu suoraan ns. virtuaalitaulujen (virtual tables) avulla. Tätä varten konepeltiä piti avata uudella tavalla, kun homman "käynnistämiseksi" joudutaan tukeutumaan ihan linkkaustasolla muihin paketteihin. Toteutus vaikutti helpoimmalta ja joustavimmalta hyödyntäen uutta (Musteesta riippuvaa) pakettia, joka sai nimen virta. Vaikka homma on siis teknisesti aika monimutkainen viritelmä, niin käyttäjän puolella monimutkaisuutta ei pitäisi näkyä (kuin tarvittavien pakettien asentamisen vaivana).

Käytännössä riittää, että asennetaan:
- Musteen versio 0.5.36 tai uudempi ja virta-paketti

R>install.packages(c("muste","virta"),contriburl="http://www.survo.fi/muste")

- RSQLiten R-liittymän käyttöön tarvittavat paketit:

R>install.packages(c("RSQLite","RSQLite.extfuns","DBI"))

Kun pakettien asennusrumbasta on selvitty, homman pitäisi toimia seuraavaan tapaan:

FILE SQL KYSELY TO NEW KYS1
QUERY KYSELY
 select Kunta,SYNT,Väestö
 from MUSTE.<Survo>/D/KUNNAT
 where SYNT>15
 order by SYNT;
END

FILE SHOW KYS1

................................

FILE SQL YHDISTA TO NEW KYS2 / DATABASE=KANTA
QUERY YHDISTA
 select a.Name,b.Kunta,a.Weight,b.Asuin
 from MUSTE.<Survo>/D/DECA a,
      MUSTE.<Survo>/D/KUNNAT b
 where a.Weight=b.Asuin
 order by b.Ala;
END

FILE SHOW KYS2

Eli SELECT-tyyppisten kyselyiden tulokset siirtyvät suoraan SVO-tiedostoon. DATABASE-täsmennyksellä voi antaa pysyvän "tietokantatiedoston" SQLitelle eli sellaiseen voi siirtää omaa dataa tyyliin:

FILE SQL "create table kunnat as select * from MUSTE.<Survo>/D/KUNNAT" / DATABASE=KANTA

ja tehdä tuolta sitten poimintoja tarvittaessa:

FILE SQL "select * from kunnat where Maamet=1" TO NEW KYS3 / DATABASE=KANTA

FILE SHOW KYS3

Tietojen säilyttäminen SQLiten tietokannassa voi olla hyödyllistä erityisesti isojen aineistojen kanssa (SQLiten tiedostokoko mahdollisesti pienenpi eikä sen pitäisi olla rajoitettu 2 gigaan ainakaan 64-bittisissä ympäristöissä) eikä rajoita toimintaa, kun sopivan kokoisen palasen voi sieltä aina helposti poimia käsiteltäväksi Musteen puolella.

Koska nuo taulut ovat "pysyviä", niin poistaminen tapahtuu SQL:n drop table -komennolla ja lisää tavaraa samaan tauluun saa normaaliin tapaan insert into -komennolla.

Joitain lastentauteja on vielä, mutta kokeilkaa ihmeessä. Täyttääkö odotuksia vai tarvitaanko jotain vielä lisää?

Esim. nopeutta en ole testaillut vielä yhtään - kokeilkaapa tekin voisiko tämä tarjota riittävän nopean tavan vaikkapa monimukaisempien MATCHien tekemiseen ilman kikkailuja omien datojenne kanssa. Tuloksista voi kertoa tänne keskusteluun..

Kokeilin tätä Windowsillakin ja muutamia seuraavaan versioon korjattavia hankaluuksia tuli vastaan:

- Musteen lyhennyspolkujen (esimerkeissä <Survo>) käytön takia tulee virhettä, kun jostain syystä taulun nimeen jää levytunnuksen kaksoispiste, joka sattuu olemaan "kielletty" merkki. Pitäisi toimia, kun datat ovat samassa hakemistossa ja lyhennyspolut tiputtaa pois esimerkeistä.

- Kakkosesimerkissä löytyy kummallisia 0-tapauksia eli luultavasti ainakin toista tiedostoa luetaan "onnistuneesti" havainnon verran liian pitkälle tms..

Minulla ainakin näyttää tilttaan Rgui (R + Muste kaatuu ilman tarkempia bugitietoja):

>COPY <Survo>/D/DECA.SVO ./
>COPY <Survo>/D/KUNNAT.SVO ./

R>install.packages(c("RSQLite","RSQLite.extfuns","DBI"))

Kun pakettien asennusrumbasta on selvitty, homman pitäisi toimia s
FILE SQL KYSELY TO NEW KYS1
QUERY KYSELY
 select Kunta,SYNT,Väestö
 from MUSTE.KUNNAT
 where SYNT>15
 order by SYNT;
END

Mutta homma sinänsä on erinomainen. Isot aineistot ovat tietty riesa osin MATCH hitauden takia, mutta vielä enemmän tuon uhkaavan 2Gb .SVO-rajoituksen takia. Meillä on mm. SQL Server ja Oracle käytössä, niissä tulee tehtyä tietyt perushaut. Mutta heti kun tarvitsi mennä rivi riviltä esim. LAG/LEAD toiminnoilla, niin en tiedä miten SQL:llä voisi tehdä tämän tyyppistä (mutta Survolla/Musteella/R:llä riviluuppaus on toki luonnollista)...

Petri Palmu wrote

Minulla ainakin näyttää tilttaan Rgui (R + Muste kaatuu ilman tarkempia bugitietoja):

Tämä ei ole kovin informatiivista. Tarkoititko, että kaatuu win64:ssä? Toimiko win32:ssa?

Joka tapauksessa päivitetty versio (josta edellisessä viestissäni mainitsemat ongelmat ja pari muutakin toivottavasti korjattu):

install.packages("virta",contriburl="http://www.survo.fi/muste")

 

Petri Palmu wrote

Mutta homma sinänsä on erinomainen. Isot aineistot ovat tietty riesa osin MATCH hitauden takia, mutta vielä enemmän tuon uhkaavan 2Gb .SVO-rajoituksen takia.

Ei ole mitenkään varmaa, että homma nopeutuu pelkkiä virtuaalitauluja käyttämällä, koska ilman (hankalasti implementoitavia) indeksejä tai suodattimia datat joudutaan käymään kuitenkin kokonaan läpi. Jos ensin kuitenkin luodaan create table -tekniikalla (sopivasti indeksoidut) taulut suoraan kantaan, niin sitten saataneen nopeutta huomattavasti lisää.

Varmaan siis syytä keräillä kokemuksia ja vinkkejä tänne, niin sitten selviää myös miten dokumentaatio tällä operaatiolle kannattaa kirjoittaa..

Ihan pikaisella kokeilulla ei mitään ongelmia tullut vastaan (win32).

Tässähän pitääkin palauttaa muutaman vuoden telakalla olleet sql-opit
käyttöön.

:JVa

Minulla oli 64-bittinen, jolla tein kokeilun. Mielestäni tein asennukset ym. oikein. Mutta tosiaan tilttasi niin etten pystynyt ottamaan mitään prosessitietoa talteen. Kokeilen uudella versiolla...

Harmi, joudun lähtemään Turkuun työreissulle enkä pääse tänään seminaariin (että olisi voinut keskustella tästäkin...). Mutta täällä sitten... :)

Kokeilujeni perusteella indeksoimattomien virtuaalitaulujen käyttäminen oli erittäin hidasta, joten virittelin hommaa niin, että nyt virtuaalitaulut luetaan kerralla SQLiteen, jonka jälkeen homma toimii huomattavasti nopeammin.

Versio 0.9.2 virta-paketista saatavilla.

Esimerkiksi seuraavanlaisessa tilanteessa, jossa piti linkkailla ja yhdistellä muutamia tutkimusaineiston osia (TRUEDIAB.SVO noin 620000 havaintoa, SAAME.SVO noin 6400 havaintoa ja DIANRO.SVO noin 880 havaintoa) homma hoituu koneellani nyt noin 8 sekunnissa, kun indeksoimattomien virtuaalitaulujen kanssa meni useampi minuutti.

FILE SQL DIP TO NEW SA01

QUERY DIP
  CREATE TABLE DIASA AS
  SELECT a.jno,b.alku,b.type
  FROM MUSTE.DIANRO a,MUSTE.TRUEDIAB b
  WHERE a.dianro=b.dianro;

  SELECT a.jno,a.saame,a.sex,a.syntpvm,a.salku AS alkupvm,
         a.kuolpvm,b.alku AS diabpvm,b.type AS diabtype
  FROM MUSTE.SAAME a
  LEFT JOIN DIASA AS b ON a.jno=b.jno
  ORDER BY a.jno;
END

Tässä nimenomaisessa tapauksessa (TRUEDIAB ja DIANRO järjestetty valmiiksi dianro:n mukaisesti ja SAAME jno:n mukaisesti eikä missään noissa ole tuplia eli SORTteja ei juurikaan tarvita ja MATCHit menevät kätevästi) saman toiminnallisuuden saa aikaan ihan "perinteisinkin" keinoin ja esim. seuraavalla tavalla aika tippui alle 4 sekuntiin:

FILE SELECT DIASA FROM TRUEDIAB BY DIANRO / KEYS=dianro
VAR jno:2=MISSING TO DIASA
......................................................................
FILE COPY DIANRO TO DIASA / MATCH=dianro VARS=jno ODD=NUL
FILE SORT DIASA BY jno TO DIASAS / VARS=ALL
......................................................................
FILE COPY SAAME TO NEW SA01 / VARS=jno,saame,sex,syntpvm,salku,kuolpvm
VAR alku:S8,type:1 TO SA01 / alku=MISSING type=MISSING
......................................................................
FILE COPY DIASAS TO SA01 / MATCH=jno VARS=alku,type ODD=NUL
FILE UPDATE SA01
FIELDS:
   5 SA_   8 alkupvm
   7 SA-   8 diabpvm
   8 NA-   1 diabtype
END

Vaikka tuossa ei varsinaisesti mennä minkään kikkailun puolelle, niin enemmän (ajatus)työtä tarvitaan kuin SQL:n kanssa..

Tuo indata-tekniikka esim. VAR X=D1:Y TO K / INDATA=DATA1

on varmasti hyödyllinen esim. laajojen datojen kanssa (kunhan datat ovat yhteensopivia rivisuunnassa). Tuskailen "liian" suurten datojen kanssa. Yksi tapahan on jakaa aineisto sarakesuunnassa kahtia tai kolmeen osaan, sitten tehdä ainakin VAR operaatioita eri datojen kanssa. FILE SQL korvannee aika hyvin FILE AGGR (siis jos kolmeen osaan pilkottua dataa pitäisi aggregoida samanaikaisesti.)..

Jos koneessa on tarpeeksi muistia, niin periaatteessa R:n objekteihin mahtuu aika isojakin (.SVO-tiedostoa isompia) datoja (lisäksi merkkijonomuuttujat "tuntuvat" olevan tehokkaasti pakattuja). Toki R:ssä on paljon muita ongelmia muistinkäytön suhteen...

Mutta isoista datoista voisi puhua seminaareissa, mielestäni yksi varteenotettava iso teema (+ niihin liittyvät kaikenlaiset tilastoanalyysit ja temput)... oikeastaan tämä oli tämän viestini varsinainen "kysymys"... :)

Tuo INDATA-tekniikka ei toimi S -tyyppisillä muuttujilla (siis merkkijono). Minusta S-ominaisuus olisi hyvä lisätä, koska tuolla INDATA-tekniikalla voi yhteensovittaa R:n omien muuttujamuunnosten ja Survon VARia. JA INDATA:lla on helppo siirtää joukko R:llä työstettyjä muuttujia .SVO:hon.

Toki
FILE COPY .... / MATCH=# ...

toimii, mutta INDATA:ssa on mm. etuna, ettei muuttujien nimet tarvitse olla samoja eri aineistoissa.
Hyvä toiminto (INDATA) siis, joka tarttisi pienen lisäyksen... :)

Petri Palmu wrote

Tuo INDATA-tekniikka ei toimi S -tyyppisillä muuttujilla (siis merkkijono).

Laittaisitko muistin virkistämiseksi ja ko. tekniikkaa tuntemattomien vinkiksi jonkun (pelkistetyn mutta toimivan) esimerkin INDATAn käytöstä ensin ilman merkkijonotyyppisiä muuttujia ja sitten kuvauksen tilanteesta, jossa merkkijonomuuttujille olisi tarvetta..

Reijo Sund wrote

Petri Palmu wrote

Tuo INDATA-tekniikka ei toimi S -tyyppisillä muuttujilla (siis merkkijono).

Laittaisitko muistin virkistämiseksi ja ko. tekniikkaa tuntemattomien vinkiksi jonkun (pelkistetyn mutta toimivan) esimerkin INDATAn käytöstä ensin ilman merkkijonotyyppisiä muuttujia ja sitten kuvauksen tilanteesta, jossa merkkijonomuuttujille olisi tarvetta..

Alla on idea, tosin esimerkki on ihan tuulesta tempaistu:

DATA _K
X Y
1 kissa
2 kulkukissa
3 kissakulkue


................
FILE COPY _K TO NEW K / DELIMITER=;

FILE SHOW K
.....................
FILE COPY K TO R>D / VARS=Y
FILE DEL K2 / Tuhotaan mahd. tulosfile

R CUR+1,CUR+4
## Poimitaan vain kissat merkkijonosta Y (säännöllisten lausekkeiden funktiolla)
D$Y2 <- gsub(".*kissa.*","kissa", D$Y)
write.svo(D, "K2.SVO");
#################

................

#FILE LOAD +K2,CUR+1
 Y           Y2
 kissa       kissa
 kulkukissa  kissa
 kissakulkue kissa

########################

.................

Siirretään Y2 alkuperäiseen K-dataan:

VAR Y2:S5=MISSING TO K /
......................
 Tuon merkkijonon pituuden voisi ehkä automatisoida INDATAssa, vaikka sitten sukrolla?

Indata-tekniikka:
 INDATA=K2
Tapa 1.
VAR Y2=D1:Y2 TO K / Tämä ei ole ok, toimii vain numeerisilla

Tapa 2. VARin logiikan mukaisesti (str())
VAR str(Y2)=str(D1:Y2) TO K /
 VirheIlmoitus "D1:Y2 not found!

 Tämä kokeiltu tavalla 1.
#FILE LOAD +K,CUR+1 /
 X Y           Y2
 1 kissa        0.00
 2 kulkukissa   0.00
 3 kissakulkue  0.00

#####################

........................
Mutta täsmäytyksellä toimii:
FILE COPY K2 TO K / VARS=Y2 MATCH=#

...................
#FILE LOAD +K,CUR+1
 X Y           Y2
 1 kissa       kissa
 2 kulkukissa  kissa
 3 kissakulkue kissa

#################

Testasin, että saisiko .SVO tiedoston luotua sellaisenaan SQLite-kantaan (FILE SQL:n  avulla), josta sitten käyttäisin dataa osana R-skriptiä

Tietokanta kyllä tuli luoduksi, mutta en pääse R:n funktioin tauluun käsiksi. Samat R:n funktiot
(sqldf, RSQLite alla) kyllä toimivat, kun vien esim. R:n data framen .db-kantaan.

En nyt keksi, että mikä tässä menettelyssä ontuu, lopussa kuitenkin tapa, joka onnistuu (mutta se ei ole se mitä tavoittelen, jos haluan esim. pienemmistä .SVO-tiedostoista koota > 2Gb loppudatan).

DATA _TEST1
X    Y
A    1
B    2
C    3

................
FILE COPY _TEST1 TO NEW TEST1
...................
FILE DEL TEST2
FILE SQL HAKU1 TO NEW TEST2   / DATABASE=DB1


QUERY HAKU1
SELECT * FROM MUSTE.TEST1;
END


FILE SHOW TEST2

DD DB1.db
>DIR DB1* > t
#LOADP t,CUR+1
20.08.2015  11:35             2 048 DB1.db
               1 tiedosto(a)          2 048 tavua
               0 kansio(ta)  580 449 693 696 tavua vapaana

##############

Eli tietokanta on luotu ja syntyi myös .SVO-tiedosto TEST2.

R CUR+1,CUR+5
library(sqldf)
db <- dbConnect(SQLite(), dbname="DB1.db");
db
dbListTables(db); # tietokannan taulut
dbListFields(db, "TEST2")


Näistä tulee ilmoitukset:

library(sqldf)
> db <- dbConnect(SQLite(), dbname="DB1.db");
> db
<SQLiteConnection>
> dbListTables(db); # tietokannan taulut
character(0)
> dbListFields(db, "TEST2")
Error in R code!
<simpleError in sqliteSendQuery(conn, statement): error in statement: no such table: TEST2>
> 






#FILE LOAD +TEST2,CUR+1
 X      Y
 A      1
 B      2
 C      3


################

Tallennetaan R:n data.frame db kantaan
FILE COPY TEST2 TO R>TEST2B

### KIRJOITETAAN ###
dbWriteTable(conn = db, name = "TEST2B",
      value = TEST2B,overwrite=TRUE, row.names = TRUE)

dbListTables(db); # tietokannan taulut
dbListFields(db, "TEST2B")

Survo data file TEST2: record=23 bytes, M1=6 L=64  M=2 N=3
> ### KIRJOITETAAN ###
> dbWriteTable(conn = db, name = "TEST2B",
+ value = TEST2B,overwrite=TRUE, row.names = TRUE)
[1] TRUE

> dbListTables(db); # tietokannan taulut
[1] "TEST2B"

> dbListFields(db, "TEST2B")
[1] "row_names" "X"         "Y"        
> 

Pienellä viiveellä (mitä nyt pari vuotta....) kommentoin tuota Petrin viimeistä kohtaa.

Jos tuon queryn:

QUERY HAKU1
SELECT * FROM MUSTE.TEST1;
END


muuttaa muotoon

QUERY HAKU1
CREATE TABLE TEST2 AS SELECT * FROM MUSTE.TEST1;
END

Niin pelittää kyllä, ja ilmoitukset tulee

> db <- dbConnect(SQLite(), dbname="DB1.db");

> dbListTables(db)
[1] "TEST2"
>
 
Siitä taulusta voi sitten ajaa joko
sql-scripteillä tai  sitten
Survon

FILE SQL -komennoilla.

Näppärää - kokeilin aika isollakin datalla - jotain > 10 G,
periaatteessa mielestäni sain toimimaan. Eli kyllä minusta tuon
Petrin esittämä tavoite toteutuu...