An Introduction to the QUIS Language Javad Chamanara

An Introduction to the QUIS Language Javad Chamanara University of Jena, Germany Mo. SD course 2016

Motivation • Various tools – R, Matlab, … • Various languages – SQL, Python, C, … • Different data sources and formats – Tables, sheets, flat files, … • Transforming data for tool integration • Similar concepts different presentations • Sharing, reproduction, …

Objectives To define the query in a uniform way and Execute it Equally on various Data Sources and have a proper Result back

Target Persistent Container Memory variable Joined Container Source SELECT How does it work? Select Persistent Container Memory variable Plot Operation Transform Aggregate Limit Group Order Filter

Query The shortest query statement Data Source: Database, Folder, Book, graph SELECT FROM ds 1. 0 Data Container: Table, file, sheet, …

Query A statement with a Data Source BIND ds 1 CONNECTION=cnn 1 SCOPE=xdata_10, udata SELECT FROM ds 1. 0 INTO var 1

Query A statement with a CONNECTION cnn 1 ADAPTER=CSV SOURCE_URI="D: ProjectsMo. SDData" BIND ds 1 CONNECTION=cnn 1 SCOPE=xdata_10, udata SELECT FROM ds 1. 0 INTO var 1

Query Connection Parameters CONNECTION cnn 1 ADAPTER=CSV SOURCE_URI="D: ProjectsSoilData" PARAMETERS= delimiter: comma, file. Extension: xlsx, first. Row. Is. Header: true, external. Header: true, dialect: MSExcel

Query Using result sets of previous queries CONNECTION cnn 1 ADAPTER=CSV SOURCE_URI="D: ProjectsSoilData" BIND ds 1 CONNECTION=cnn 1 SCOPE=xdata_10, udata SELECT FROM ds 1. 0 INTO var 1 SELECT FROM var 1 INTO var 2 ORDER BY temp

Query Joining two files (containers) CONNECTION cnn 1 ADAPTER=CSV SOURCE_URI="D: ProjectsSoilData" BIND ds 1 CONNECTION=cnn 1 SCOPE=data 1, data 2, udata SELECT FROM ds 1. data 1 INNER JOIN ds 1. data 2 ON xk == yk INTO joined

Query Projecting the Join Results CONNECTION cnn 1 ADAPTER=CSV SOURCE_URI="D: ProjectsSoilData" BIND ds 1 CONNECTION=cnn 1 SCOPE=data 1, data 2, udata SELECT USING INLINE x, t AS y FROM ds 1. data 1 INNER JOIN ds 1. data 2 ON xk == yk INTO joined

Query Adding virtual schemas PERSPECTIVE observation { ATTRIBUTE Temperature: Real Map. To = 1. 8 * temperature + 32, ATTRIBUTE SN: Real Map. To = soil. Ni / 1000, } SELECT USING PERS observation FROM ds 1. 0 INTO var 1

Query Hierarchical virtual schemas PERSPECTIVE location { ATTRIBUTE Longitude: Real Map. To = longitude, ATTRIBUTE Latitude: Real Map. To = latitude, ATTRIBUTE Elevation: Real Map. To = elevation / 0. 3048, } PERSPECTIVE observation EXTENDS location { ATTRIBUTE Temperature: Real Map. To = 1. 8 * temperature + 32, ATTRIBUTE SN: Real Map. To = soil. Ni / 1000, } SELECT USING PERSPECTIVE observation FROM ds 1. 0 INTO var 1

Query The SELECT clauses SELECT USING PERSPECTIVE observation FROM ds 1. 0 INTO var 1 WHERE (Temperature > 50) ORDER BY Elevation ASC LIMIT SKIP 1 TAKE 20

Query Utilizing functions ATTRIBUTE Obs. Time: Date Map. To = str. to. Date(obs_time, "HH: mm"), ATTRIBUTE mean. Temp: Real Map. To = avg(temperature), SELECT USING PERS INLINE sum(rank) as t. Rank, employee as e FROM ds 1. 0 INTO var 1 WHERE (math. log(math. abs(p. Index))> 3)

Query Streaming SELECT USING PERS observation FROM ds 1. 0 INTO ds 1. 1 WHERE (Temperature > 50 AND Elevation >= 32 AND Elevation < 50)

Query SELECT FROM b 2. 0 INNER JOIN b 2. 1 ON PK <> FK INTO PLOT p 3 hax: Elevation vaxis: R. Temperature pt: l hl: "Elevation (F)" vl: "Temperature (F)" pl: "Temperature and Elevation" WHERE (R. Temperature > 0) ORDER BY Elevation

Plots SELECT FROM b 2. 0 PERS PLeft INNER JOIN b 2. 1 PERS PRight ON PK <> FK INTO PLOT p 3 hax: Elevation v-axis: R. Temperature pt: l hl: "Elevation (F)" vl: "Temperature (F)" pl: "Temperature and Elevation" WHERE (R. Temperature > 0) ORDER BY Elevation

RQUIS • • A package for R users To access QUIS functionalities To facilitate heterogeneous data access From inside R

download. file(url = "http: //fusion. cs. unijena. de/javad/quis/040/rquis/RQUI S_0. 4. 0. zip", destfile = "d: /temp/r/RQUIS_0. 4. 0. zip")

remove. packages("RQUIS") install. packages("d: /temp/r/RQUIS_ 0. 4. 0. zip", , repos = NULL)

library(RQUIS) library(ggplot 2) library(scales) library(plyr)

demo(package=“RQUIS”, Test 1) demo(package="RQUIS", Weather. Q) demo(package="RQUIS", Weather. R)

engine <- quis. get. Engine()

file <- system. file("extdata", "ex 1. xqt", package="RQUIS") ld <- quis. load. Process(engine, file)

rn <- quis. run. Process(engine) err <- quis. get. Run. Report(engine)

data <- quis. get. Variable(engine, "mean. Daily. Temp") schema <quis. get. Variable. Schema(engine, "mean. Daily. Temp")

ggplot(data, aes(dayindex, meantemp)) + geom_line() + xlab("") + ylab("Mean Temperature C°") + ggtitle("2014 Average Daily Temperature at SFO")

ggplot(data, aes(dayindex, meantemp)) + geom_line() + xlab("") + ylab("Mean Temperature C°") + ggtitle("2014 Average Daily Temperature at SFO")

Sum Up SELECT features FROM scope WHERE (status == implemented) Source Data Source Variable Operations Projection Filtering Ordering Grouping Limiting Target Variable Data Source Plot Adapters CSV/Excel Memory DBMS

Feedback Thanks for your attention Source is available at: github. com/javadch Binary is available at: http: //fusion. cs. uni-jena. de/javad/quis/