Up: Component summary
Up: Component summary
Component
TableQueryExecutes an SQL query on CSV tables and creates a result table. TableQuery uses HSQLDB 2.3.1 (default), H2 1.3.173 or SQLite 3.7.2 for executing the query. Consequently, the syntax of the query is defined by the selected SQL engine. The input script may include multiple SQL statements delimited with '--<statement break>--' strings and the results of the last statement are returned.
Each defined input table is inserted into a temporary in-memory (default) or on-disk database. The name of the SQL table corresponds to the name of the input file, e.g. table1 or the key in the array. If you have a column named MyColumn in table1, you can refer to it using table1."MyColumn" in the SQL query. The result SQL relation is written to the output table. You can define names of output columns using SELECT ... AS "MyName".
Note: currently, you should surround all column names with quotes (e.g. table1."MyColumn").
HSQLDB can use static Java methods as stored procedures. Some stored procedures are provided in the class Functions supplied with the component. For example, "Functions.date"(2009, 12, 31) in the query creates an SQL date instance from three numeric values (date, month, day). See test case 7 for an example.
| Version | 1.3 |
|---|---|
| Bundle | tools |
| Categories | Convert Filter |
| Authors | Kristian Ovaska (kristian.ovaska@helsinki.fi), Marko Laakso (Marko.Laakso@Helsinki.FI) |
| Issue tracker | View/Report issues |
| Requires | hsqldb.jar (jar) ; h2-1.3.173.jar (jar) ; sqlite-jdbc-3.7.2.jar (jar) |
| Source files | component.xml TableQuery.java Functions.java |
| Usage | Example with default values |
Inputs| Name | Type | Mandatory | Description |
|---|---|---|---|
| table1 | CSV | Optional | CSV table 1. The table is referred to as 'table1' in the SQL query. |
| table2 | CSV | Optional | CSV table 2. The table is referred to as 'table2' in the SQL query. |
| table3 | CSV | Optional | CSV table 3. The table is referred to as 'table3' in the SQL query. |
| table4 | CSV | Optional | CSV table 4. The table is referred to as 'table4' in the SQL query. |
| table5 | CSV | Optional | CSV table 5. The table is referred to as 'table5' in the SQL query. |
| table6 | CSV | Optional | CSV table 6. The table is referred to as 'table6' in the SQL query. |
| table7 | CSV | Optional | CSV table 7. The table is referred to as 'table7' in the SQL query. |
| table8 | CSV | Optional | CSV table 8. The table is referred to as 'table8' in the SQL query. |
| table9 | CSV | Optional | CSV table 9. The table is referred to as 'table9' in the SQL query. |
| table10 | CSV | Optional | CSV table 10. The table is referred to as 'table10' in the SQL query. |
| table11 | CSV | Optional | CSV table 11. The table is referred to as 'table11' in the SQL query. |
| table12 | CSV | Optional | CSV table 12. The table is referred to as 'table12' in the SQL query. |
| table13 | CSV | Optional | CSV table 13. The table is referred to as 'table13' in the SQL query. |
| table14 | CSV | Optional | CSV table 14. The table is referred to as 'table14' in the SQL query. |
| table15 | CSV | Optional | CSV table 15. The table is referred to as 'table15' in the SQL query. |
| tables | Array<CSV> | Optional | List of relations each referred by its key. |
| queryFile | SQL | Optional | SQL query. Either this file or the query parameter must be provided, but not both. |
| columnTypes | CSV | Optional | Contains SQL types for individual columns. If the file is not provided, the type is inferred from the contents of the columns. This can be used to force the use of VARCHAR for values that are also valid numerics. The file contains the columns Table (refers to one of table1 to table15), Column (refers to a column name in the table), Type (contains an SQL type). A row with Table='result', Column=X and Type='STRING' forces the use string values for result column X. |
Outputs| Name | Type | Description |
|---|---|---|
| table | CSV | Result table that contains the rows from the query. |
Parameters| Name | Type | Default | Description |
|---|---|---|---|
| engine | string | "hsqldb" | Database engine. Legal values: hsqldb, h2, sqlite. |
| memoryDB | boolean | true | If true, the temporary database is stored in memory for fast access. If false, it is stored on disk to allow processing large data sets. |
| numIndices | string | "" | Comma-separated list of index counts for input tables. All indices are single-column indices, running from column 1 to N, where N is retrieved from this parameter. If empty, the default number (1) is used. For example, ",2,,2" sets two indices for table2 and table4 and one index for the rest. |
| query | string | "" | SQL query. Either this parameter or the query input must be provided, but not both. |
Test cases| Test case | Parameters▼ | IN table1 |
IN table2 |
IN table3 |
IN table4 |
IN table5 |
IN table6 |
IN table7 |
IN table8 |
IN table9 |
IN table10 |
IN table11 |
IN table12 |
IN table13 |
IN table14 |
IN table15 |
IN tables |
IN queryFile |
IN columnTypes |
OUT table |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| case1 | (missing) | table1 | table2 | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | queryFile | (missing) | table |
| case10_sequence | (missing) | table1 | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | queryFile | (missing) | table |
| case11_h2 | properties | table1 | table2 | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | queryFile | (missing) | table |
|
engine = h2 |
||||||||||||||||||||
| case12_sqlite | properties | table1 | table2 | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | queryFile | (missing) | table |
|
engine = sqlite |
||||||||||||||||||||
| case2 | (missing) | table1 | table2 | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | queryFile | (missing) | table |
| case3_big | properties | table1 | table2 | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | queryFile | (missing) | table |
|
numIndices=2,2, |
||||||||||||||||||||
| case4_types | (missing) | table1 | table2 | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | queryFile | columnTypes | table |
| case5_param | properties | table1 | table2 | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | table |
|
query=SELECT table1.K1 AS "col1", table1.A2+table2.B1 AS "col2", table2.B2 AS "col3" FROM table1, table2 WHERE table1.K1 = table2.K1 AND table1.K2 > 0 ORDER BY "col1", "col2", "col3"; |
||||||||||||||||||||
| case6_invalid | properties | table1 | table2 | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | queryFile | (missing) | (expecting failure) |
|
query=SELECT table1.K1 AS "col1", table1.A2+table2.B1 AS "col2", table2.B2 AS "col3" FROM table1, table2 WHERE table1.K1 = table2.K1 AND table1.K2 > 0 ORDER BY "col1", "col2", "col3"; |
||||||||||||||||||||
| case7_function | (missing) | table1 | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | queryFile | (missing) | table |
| case8_inputs | (missing) | table1 | (missing) | table3 | table4 | table5 | table6 | table7 | table8 | table9 | table10 | table11 | table12 | table13 | table14 | table15 | (missing) | queryFile | (missing) | table |
| case9_array | properties | table1 | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | (missing) | tables | (missing) | (missing) | table |
|
query = SELECT A.*, B.* FROM foo A, bar B WHERE (A."id" = B."id"), |
||||||||||||||||||||