5.1.16 TQL
Overview
This chapter covers the TQL query language for accessing GridDB. Only the SELECT statement is supported in TQL so no changes can be made to the database or schema. Please refer to the GridDB API reference for more information.
Conditional Search
The TQL search functionality is the same as SQL.
List.1 Conditional Search(TqlSeach.java)
// Get TimeSeries Container
TimeSeries<InstrumentLog> logTs =
store.getTimeSeries("weather_station_1", InstrumentLog.class);
// Seach by temperture
String tql = String.format("SELECT * WHERE temperture > %s", temperture);
Query<InstrumentLog> query = logTs.query(tql, InstrumentLog.class);
RowSet<InstrumentLog> rows = query.fetch();
- Line 103: Use Container.query class to input the TQL statement
- Line 104: Fetch results from TQL statement
The result of the TQL statement is stored in a RowSet and can be retrieved as follows:
List.2 Get the results of the Conditional Search(TqlSeach.java)
while (rows.hasNext()) {
InstrumentLog log = rows.next();
System.out.println("Timestamp\t\t\tWeatherStation ID\tTemperture");
System.out.println(String.format("%s\t%-20s\t%-10s", log.timestamp,
log.weatherStationId, log.temperture));
}
- Line 38: Use RowSet.hasNext() to see if there any more results
- Line 39: Use RowSet.next() to retrieve the next row.
The execution results follow:
List.3 Results of the Conditional Search(TqlSeach.java)TQL:SELECT * WHERE temperture > 70.0 Timestamp WeatherStation ID Temperture Fri Jul 01 09:00:00 JST 2016 weather_station_1 75.0 Fri Jul 01 12:00:00 JST 2016 weather_station_1 80.0 Fri Jul 01 15:00:00 JST 2016 weather_station_1 75.0 Sat Jul 02 09:00:00 JST 2016 weather_station_1 75.0 Sat Jul 02 12:00:00 JST 2016 weather_station_1 80.0 Sat Jul 02 15:00:00 JST 2016 weather_station_1 75.0
Like Search
You can use the LIKE keyword search like SQL.
List.4 Like Search(TqlSeach.java)
// Get Collection
Collection<String, WeatherStation> weatherStationCol =
store.getCollection("weather_station", WeatherStation.class);
// Like Search
String tql = "SELECT * WHERE name LIKE '%" + name + "%'";
// Search By TQL
Query query = weatherStationCol.query(tql, WeatherStation.class);
RowSet rows = query.fetch();
- Line 120: Specifies LIKE in the query string using patterns.
- %: Matches a string of one or more characters.
- _: Matches a single character.
Retrieving the results from the RowSet does not change.
List.5 Get the results from the Like Query(TqlSeach.java)
// Search WeatherStation by name
RowSet<WeatherStation> wsRows = searchByName(store, "kyo");
// Show search WeatherStation results
System.out.println("ID\tName\t\t\tLongitude\tLatitude\tCamera");
while (wsRows.hasNext()) {
WeatherStation weatherStation = wsRows.next();
System.out.println(String.format("%-3s\t%-20s\t%-10s\t%-10s\t%-5s",
weatherStation.id, weatherStation.name, weatherStation.latitude,
weatherStation.longitude, weatherStation.hasCamera));
(snip)
}
Execution results are as follows:
List.6 Result(TqlSeach.java)TQL:SELECT * WHERE name LIKE '%kyo%' ID Name Longitude Latitude Camera 13 Tokyo-Shinjuku 35.68944 139.69167 true
Time specified Search
In the case of a TimeSeries container, TimeSeries.query() can be used with TQL.
List.7 TQL and TimeSeries(TqlSeach.java)
// Get TimeSeries Container
TimeSeries<InstrumentLog> logTs;
logTs = store.getTimeSeries("weather_station_" + weatherStationId, InstrumentLog.class);
Query<InstrumentLog> query = logTs.query(String.format(
"SELECT * WHERE TIMESTAMP('%s') >= timestamp AND timestamp <= TIMESTAMP('%s')",
TimestampUtils.format(start), TimestampUtils.format(end)));
RowSet<InstrumentLog> rows = query.fetch();
- Line 144-146: Use TimestampUtils.date to specify the time with in the TQL search string.
List.8 Get results from a TimeSeries TQL Query(TqlSeach.java)
// Search WeatherStation by name
RowSet<WeatherStation> wsRows = searchByName(store, "kyo");
// Show search WeatherStation results
System.out.println("ID\tName\t\t\tLongitude\tLatitude\tCamera");
while (wsRows.hasNext()) {
WeatherStation weatherStation = wsRows.next();
System.out.println(String.format("%-3s\t%-20s\t%-10s\t%-10s\t%-5s",
weatherStation.id, weatherStation.name, weatherStation.latitude,
weatherStation.longitude, weatherStation.hasCamera));
(snip)
}
List.9 Results (TqlSeach.java)
TQL:SELECT * WHERE TIMESTAMP('2016-07-01T21:00:00.000Z') <= timestamp AND timestamp <= TIMESTAMP('2016-07-02T03:00:00.000Z')
Timestamp WeatherStation ID Temperture
Sat Jul 02 06:00:00 JST 2016 weather_station_13 70.0
Sat Jul 02 09:00:00 JST 2016 weather_station_13 75.0
Sat Jul 02 12:00:00 JST 2016 weather_station_13 80.0
Search with forUpdate
It is possible to update rows retrieved using TQL.
List.10 Search with forUpdate(TqlForUpdate.java)
// Get TimeSeries
Collection<String, WeatherStation> weatherStationCol =
store.getCollection("weather_station", WeatherStation.class);
// When using forUpdate, there is a need to disable the Auto Commit
weatherStationCol.setAutoCommit(false);
// In the case of boolean columns, NOT means false.
String tql = "SELECT * WHERE NOT hasCamera";
// Search By TQL
Query<WeatherStation> query = weatherStationCol.query(tql, WeatherStation.class);
// Using forUpdate option.
RowSet<WeatherStation> rows = query.fetch(true);
// Install a camera in all of WeatherStation.
while (rows.hasNext()) {
System.out.println("not exists camera");
WeatherStation weatherStation = rows.next();
System.out.println("ID:" + weatherStation.id);
weatherStation.hasCamera = true;
// update row of WeatherStation
rows.update(weatherStation);
}
// Commit
weatherStationCol.commit();
TQL EXPLAIN and ANALYZE
Like SQL, it is possible to use the EXPLAIN and ANALYZE keywords to get execution plans for TQL statemntes.
List.11 TQL Execution plans(TqlExplain.java)
// Get InstrumentLog TimeSeries
TimeSeries<InstrumentLog> logTs =
store.getTimeSeries("weather_station_1", InstrumentLog.class);
// Analyzing the execution plan of the TQL
String tql = "EXPLAIN ANALYZE SELECT * WHERE 50.0 <= temperture AND temperture <= 70.0"
+ " AND TIMESTAMP('2016-07-01T06:00:00Z') <= timestamp";
Query<QueryAnalysisEntry> query = logTs.query(tql, QueryAnalysisEntry.class);
RowSet<QueryAnalysisEntry> rows = query.fetch();
// output the execution plan of the TQL
System.out.println("ID\tDepth\tType\t\t\t\tValueType\tValue\t\t\tStatement");
while (rows.hasNext()) {
QueryAnalysisEntry analysis = rows.next();
System.out.println(String.format("%s\t%s\t%-24s\t%-10s\t%-20s\t%s",
analysis.getId(), analysis.getDepth(), analysis.getType(),
analysis.getValueType(), analysis.getValue(), analysis.getStatement()));
}
- Line 31-32: Add EXPLAIN ANALYSZE to the start of the TQL statement.
- Line 33: Run the query with QueryAnalysisEntry.class as a parameter
List.12 Result(TqlExplain.java)
ID Depth Type ValueType Value Statement 0 1 TIMESERIES_EXPIRE TIMESTAMP 1970-01-01T00:00:00Z 1 0 QUERY_LOOP_NUMBER INTEGER 0 2 1 NOT_INDEX_USABLE COLUMN temperture 3 1 NOT_INDEX_USABLE COLUMN temperture 4 1 INDEX_FOUND INDEX_TYPE ROWKEY timestamp 5 1 SET_INDEX_KEY STRING START_KEY 2016-07-01T06:00:00.000Z 6 1 SEARCH_EXECUTE MAP_TYPE BTREE 7 2 SEARCH_MAP STRING TIME_SERIES_ROW_MAP 8 1 SEARCH_RESULT_ROWS INTEGER 6 9 0 QUERY_EXECUTE_RESULT_ROWS INTEGER 6 10 0 QUERY_RESULT_TYPE STRING RESULT_ROW_ID_SET 11 0 QUERY_RESULT_ROWS INTEGER 6
Source Code
Complete source code used in this sample can be downloaded from the following.
Download: tql.zip