Igor Igor - 4 months ago 15
SQL Question

How we can perform bulk SQL SELECT with google fusion tables?

I have list of ROWIDs and want to get rows with such Ids. So I do:

for (Integer id : duplicateIds) {
String sqlQuery = "SELECT ROWID, date, containerId FROM " + TABLE + " WHERE ROWID = '" + id + "'";
Fusiontables.Query.Sql sql = db.query().sql(sqlQuery);
List<List<Object>> row = sql.execute().getRows();
allRows.addAll(row);
}


But size of the duplicateIds list is not small, and I think this approach is not good because it is time consuming to do SQL query.
So, I wonder if it is possible to perform something like bulk query or maybe there exists another approach? How to do it more efficient?

UPD. I found that there is a batch request for such things. But I still can't get how to do it and get response with object of List> type.

Thanks in advance.

Answer

Here is the example of how you can perform batch API request on fusion tables. You should note that there is quota for requests per 100 seconds per user and I've used Thread.sleep(TIME) function to avoid errors.

    private static List<List<Object>> buildRows(Fusiontables db, Set<String> rowIds, List<List<Object>> allRows)
        throws Exception {
    BatchRequest batchRequest = db.batch();
    int c = 1;
    BatchCallback<Sqlresponse, GoogleJsonErrorContainer> batchCallback = getBatchCallback(allRows);

    for (String id : rowIds) {
        String sqlQuery = "SELECT * FROM " + TABLE_ID + " WHERE ROWID = '" + id + "'";

        HttpRequest request = db.query().sqlGet(sqlQuery).buildHttpRequest();
        batchRequest.queue(request, Sqlresponse.class, GoogleJsonErrorContainer.class, batchCallback);

        c++;
        if (c == 200) {
            batchRequest.execute();
            Thread.sleep(REQUEST_QUOTAS_LIMIT);
        }
    }

    private static BatchCallback<Sqlresponse, GoogleJsonErrorContainer> getBatchCallback(final List<List<Object>> out) {
    return new BatchCallback<Sqlresponse, GoogleJsonErrorContainer>() {
        @Override
        public void onSuccess(Sqlresponse sqlresponse, HttpHeaders httpHeaders) throws IOException {
            out.addAll(sqlresponse.getRows());
        }
        @Override
        public void onFailure(GoogleJsonErrorContainer googleJsonErrorContainer, HttpHeaders httpHeaders) throws IOException {
            System.err.println("Batching fails");
            System.err.println(googleJsonErrorContainer.getError());
        }
    };
}
Comments