Writing custom queries against an Azure table

While not as powerful as SQL, Azure tables do allow you to do minimal querying. With the native SDK, you’d do this using the table_query object’s set_filter_string function. Here’s a modified ReadTableData method from the previous blog entries.

void ReadTableData(string_t filter)
{
  auto storage_account = cloud_storage_account::parse(
      U("UseDevelopmentStorage=true"));
  auto table_client = storage_account.create_cloud_table_client();
  auto table = table_client.get_table_reference(U("Clients"));
  bool created = table.create_if_not_exists();

  table_query query;
  query.set_filter_string(filter);

  auto results = table.execute_query(query);

  for (auto item : results)
  {
    auto properties = item.properties();

    for (auto property : properties)
    {
      ucout << property.first << U(" = ") 
            << property.second.str() << U("\t");
    }

    ucout << endl;
  }
}

Here’s an example query that gets all rows within a range of RowKey values.

void ReadTableData(string_t rowKeyStart, string_t rowKeyEnd)
{
  auto filter = table_query::combine_filter_conditions(
    table_query::generate_filter_condition(
        U("RowKey"), 
        query_comparison_operator::greater_than_or_equal, 
        rowKeyStart),
    query_logical_operator::and,
    table_query::generate_filter_condition(
        U("RowKey"), 
        query_comparison_operator::less_than_or_equal, 
        rowKeyEnd));

  ReadTableData(filter);
}

The combine_filter_conditions function is used to create a query string. The query_comparison_operator class allows you to set comparison operators and the query_logical_operator class lets you set logical operators. In case you are wondering, that gets converted to the following string.

(RowKey ge ‘100’) and (RowKey le ‘104’)

Here’s a similar method, that queries against the Name column.

void ReadTableDataStartingWith(string_t letter1, string_t letter2)
{
  auto filter = table_query::combine_filter_conditions(
    table_query::generate_filter_condition(
        U("Name"), 
        query_comparison_operator::greater_than_or_equal, 
        letter1),
    query_logical_operator::and,
    table_query::generate_filter_condition(
        U("Name"), 
        query_comparison_operator::less_than, 
        letter2));

  ReadTableData(filter);
}

The generated query filter looks like this.

(Name ge ‘D’) and (Name lt ‘E’)

You can call it as follows :

ReadTableDataStartingWith(U("D"), U("E"));

That’d return all rows with names starting with a D. Querying against partition and row keys would be a faster approach. Also, for repeated querying against a set of data, you may want to fetch a larger subset of the data and then query it in memory using standard STL.

Advertisements