Up: Component summary Component

TableQuery

Executes 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,
memoryDB = false

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"),
numIndices = 1,2,2


Generated 2018-12-11 07:42:07 by Anduril 2.0.0