Azure Tables input bindings for Azure Functions

Use the Azure Tables input binding to read a table in an Azure Storage or Cosmos DB account.

For information on setup and configuration details, see the overview.

Example

The usage of the binding depends on the extension package version and the C# modality used in your function app, which can be one of the following:

An in-process class library is a compiled C# function runs in the same process as the Functions runtime.

Choose a version to see examples for the mode and version.

The following example shows a C# function that reads a single table row. For every message sent to the queue, the function will be triggered.

The row key value {queueTrigger} binds the row key to the message metadata, which is the message string.

public class TableStorage
{
    public class MyPoco
    {
        public string PartitionKey { get; set; }
        public string RowKey { get; set; }
        public string Text { get; set; }
    }

    [FunctionName("TableInput")]
    public static void TableInput(
        [QueueTrigger("table-items")] string input, 
        [Table("MyTable", "MyPartition", "{queueTrigger}")] MyPoco poco, 
        ILogger log)
    {
        log.LogInformation($"PK={poco.PartitionKey}, RK={poco.RowKey}, Text={poco.Text}");
    }
}

Use a CloudTable method parameter to read the table by using the Azure Storage SDK. Here's an example of a function that queries an Azure Functions log table:

using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Host;
using Microsoft.Extensions.Logging;
using Microsoft.Azure.Cosmos.Table;
using System;
using System.Threading.Tasks;

namespace FunctionAppCloudTable2
{
    public class LogEntity : TableEntity
    {
        public string OriginalName { get; set; }
    }
    public static class CloudTableDemo
    {
        [FunctionName("CloudTableDemo")]
        public static async Task Run(
            [TimerTrigger("0 */1 * * * *")] TimerInfo myTimer, 
            [Table("AzureWebJobsHostLogscommon")] CloudTable cloudTable,
            ILogger log)
        {
            log.LogInformation($"C# Timer trigger function executed at: {DateTime.Now}");

            TableQuery<LogEntity> rangeQuery = new TableQuery<LogEntity>().Where(
                TableQuery.CombineFilters(
                    TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, 
                        "FD2"),
                    TableOperators.And,
                    TableQuery.GenerateFilterCondition("RowKey", QueryComparisons.GreaterThan, 
                        "t")));

            // Execute the query and loop through the results
            foreach (LogEntity entity in 
                await cloudTable.ExecuteQuerySegmentedAsync(rangeQuery, null))
            {
                log.LogInformation(
                    $"{entity.PartitionKey}\t{entity.RowKey}\t{entity.Timestamp}\t{entity.OriginalName}");
            }
        }
    }
}

For more information about how to use CloudTable, see Get started with Azure Table storage.

If you try to bind to CloudTable and get an error message, make sure that you have a reference to the correct Storage SDK version.

The following example shows an HTTP triggered function which returns a list of person objects who are in a specified partition in Table storage. In the example, the partition key is extracted from the http route, and the tableName and connection are from the function settings.

public class Person {
    private String PartitionKey;
    private String RowKey;
    private String Name;

    public String getPartitionKey() { return this.PartitionKey; }
    public void setPartitionKey(String key) { this.PartitionKey = key; }
    public String getRowKey() { return this.RowKey; }
    public void setRowKey(String key) { this.RowKey = key; }
    public String getName() { return this.Name; }
    public void setName(String name) { this.Name = name; }
}

@FunctionName("getPersonsByPartitionKey")
public Person[] get(
        @HttpTrigger(name = "getPersons", methods = {HttpMethod.GET}, authLevel = AuthorizationLevel.FUNCTION, route="persons/{partitionKey}") HttpRequestMessage<Optional<String>> request,
        @BindingName("partitionKey") String partitionKey,
        @TableInput(name="persons", partitionKey="{partitionKey}", tableName="%MyTableName%", connection="MyConnectionString") Person[] persons,
        final ExecutionContext context) {

    context.getLogger().info("Got query for person related to persons with partition key: " + partitionKey);

    return persons;
}

The TableInput annotation can also extract the bindings from the json body of the request, like the following example shows.

@FunctionName("GetPersonsByKeysFromRequest")
public HttpResponseMessage get(
        @HttpTrigger(name = "getPerson", methods = {HttpMethod.GET}, authLevel = AuthorizationLevel.FUNCTION, route="query") HttpRequestMessage<Optional<String>> request,
        @TableInput(name="persons", partitionKey="{partitionKey}", rowKey = "{rowKey}", tableName="%MyTableName%", connection="MyConnectionString") Person person,
        final ExecutionContext context) {

    if (person == null) {
        return request.createResponseBuilder(HttpStatus.NOT_FOUND)
                    .body("Person not found.")
                    .build();
    }

    return request.createResponseBuilder(HttpStatus.OK)
                    .header("Content-Type", "application/json")
                    .body(person)
                    .build();
}

The following example uses a filter to query for persons with a specific name in an Azure Table, and limits the number of possible matches to 10 results.

@FunctionName("getPersonsByName")
public Person[] get(
        @HttpTrigger(name = "getPersons", methods = {HttpMethod.GET}, authLevel = AuthorizationLevel.FUNCTION, route="filter/{name}") HttpRequestMessage<Optional<String>> request,
        @BindingName("name") String name,
        @TableInput(name="persons", filter="Name eq '{name}'", take = "10", tableName="%MyTableName%", connection="MyConnectionString") Person[] persons,
        final ExecutionContext context) {

    context.getLogger().info("Got query for person related to persons with name: " + name);

    return persons;
}

The following example shows a table input binding in a function.json file and JavaScript code that uses the binding. The function uses a queue trigger to read a single table row.

The function.json file specifies a partitionKey and a rowKey. The rowKey value "{queueTrigger}" indicates that the row key comes from the queue message string.

{
  "bindings": [
    {
      "queueName": "myqueue-items",
      "connection": "MyStorageConnectionAppSetting",
      "name": "myQueueItem",
      "type": "queueTrigger",
      "direction": "in"
    },
    {
      "name": "personEntity",
      "type": "table",
      "tableName": "Person",
      "partitionKey": "Test",
      "rowKey": "{queueTrigger}",
      "connection": "MyStorageConnectionAppSetting",
      "direction": "in"
    }
  ],
  "disabled": false
}

The configuration section explains these properties.

Here's the JavaScript code:

module.exports = async function (context, myQueueItem) {
    context.log('Node.js queue trigger function processed work item', myQueueItem);
    context.log('Person entity name: ' + context.bindings.personEntity.Name);
};

The following function uses a queue trigger to read a single table row as input to a function.

In this example, the binding configuration specifies an explicit value for the table's partitionKey and uses an expression to pass to the rowKey. The rowKey expression, {queueTrigger}, indicates that the row key comes from the queue message string.

Binding configuration in function.json:

{
  "bindings": [
    {
      "queueName": "myqueue-items",
      "connection": "MyStorageConnectionAppSetting",
      "name": "MyQueueItem",
      "type": "queueTrigger",
      "direction": "in"
    },
    {
      "name": "PersonEntity",
      "type": "table",
      "tableName": "Person",
      "partitionKey": "Test",
      "rowKey": "{queueTrigger}",
      "connection": "MyStorageConnectionAppSetting",
      "direction": "in"
    }
  ],
  "disabled": false
}

PowerShell code in run.ps1:

param($MyQueueItem, $PersonEntity, $TriggerMetadata)
Write-Host "PowerShell queue trigger function processed work item: $MyQueueItem"
Write-Host "Person entity name: $($PersonEntity.Name)"

The following function uses an HTTP trigger to read a single table row as input to a function.

In this example, binding configuration specifies an explicit value for the table's partitionKey and uses an expression to pass to the rowKey. The rowKey expression, {id} indicates that the row key comes from the {id} part of the route in the request.

Binding configuration in the function.json file:

{
  "scriptFile": "__init__.py",
  "bindings": [
    {
      "name": "messageJSON",
      "type": "table",
      "tableName": "messages",
      "partitionKey": "message",
      "rowKey": "{id}",
      "connection": "AzureWebJobsStorage",
      "direction": "in"
    },
    {
      "authLevel": "function",
      "type": "httpTrigger",
      "direction": "in",
      "name": "req",
      "methods": [
        "get",
        "post"
      ],
      "route": "messages/{id}"
    },
    {
      "type": "http",
      "direction": "out",
      "name": "$return"
    }
  ],
  "disabled": false
}

Python code in the __init__.py file:

import json

import azure.functions as func

def main(req: func.HttpRequest, messageJSON) -> func.HttpResponse:

    message = json.loads(messageJSON)
    return func.HttpResponse(f"Table row: {messageJSON}")

With this simple binding, you can't programmatically handle a case in which no row that has a row key ID is found. For more fine-grained data selection, use the storage SDK.


Attributes

Both in-process and isolated process C# libraries use attributes to define the function. C# script instead uses a function.json configuration file.

In C# class libraries, the TableAttribute supports the following properties:

Attribute property Description
TableName The name of the table.
PartitionKey Optional. The partition key of the table entity to read. See the usage section for guidance on how to use this property.
RowKey Optional. The row key of a single table entity to read. Can't be used with Take or Filter.
Take Optional. The maximum number of entities to return. Can't be used with RowKey.
Filter Optional. An OData filter expression for the entities to return from the table. Can't be used with RowKey.
Connection The name of an app setting or setting collection that specifies how to connect to the table service. See Connections.

The attribute's constructor takes the table name, partition key, and row key, as shown in the following example:

[FunctionName("TableInput")]
public static void Run(
    [QueueTrigger("table-items")] string input, 
    [Table("MyTable", "Http", "{queueTrigger}")] MyPoco poco, 
    ILogger log)
{
    ...
}

You can set the Connection property to specify the connection to the table service, as shown in the following example:

[FunctionName("TableInput")]
public static void Run(
    [QueueTrigger("table-items")] string input, 
    [Table("MyTable", "Http", "{queueTrigger}", Connection = "StorageConnectionAppSetting")] MyPoco poco, 
    ILogger log)
{
    ...
}

While the attribute takes a Connection property, you can also use the StorageAccountAttribute to specify a storage account connection. You can do this when you need to use a different storage account than other functions in the library. The constructor takes the name of an app setting that contains a storage connection string. The attribute can be applied at the parameter, method, or class level. The following example shows class level and method level:

[StorageAccount("ClassLevelStorageAppSetting")]
public static class AzureFunctions
{
    [FunctionName("StorageTrigger")]
    [StorageAccount("FunctionLevelStorageAppSetting")]
    public static void Run( //...
{
    ...
}

The storage account to use is determined in the following order:

  • The trigger or binding attribute's Connection property.
  • The StorageAccount attribute applied to the same parameter as the trigger or binding attribute.
  • The StorageAccount attribute applied to the function.
  • The StorageAccount attribute applied to the class.
  • The default storage account for the function app, which is defined in the AzureWebJobsStorage application setting.

Annotations

In the Java functions runtime library, use the @TableInput annotation on parameters whose value would come from Table storage. This annotation can be used with native Java types, POJOs, or nullable values using Optional<T>. This annotation supports the following elements:

Element Description
TableInputName The name of the table.
PartitionKey Optional. The partition key of the table entity to read.
RowKey The row key of the table entity to read.
Take Optional. The maximum number of entities to read.
Filter Optional. An OData filter expression for table input.
Connection The name of an app setting or setting collection that specifies how to connect to the table service. See Connections.

Configuration

The following table explains the binding configuration properties that you set in the function.json file.

function.json property Description
type Must be set to table. This property is set automatically when you create the binding in the Azure portal.
direction Must be set to in. This property is set automatically when you create the binding in the Azure portal.
name The name of the variable that represents the table or entity in function code.
tableName The name of the table.
partitionKey Optional. The partition key of the table entity to read.
rowKey Optional. The row key of the table entity to read. Can't be used with take or filter.
take Optional. The maximum number of entities to return. Can't be used with rowKey.
filter Optional. An OData filter expression for the entities to return from the table. Can't be used with rowKey.
connection The name of an app setting or setting collection that specifies how to connect to the table service. See Connections.

When you're developing locally, add your application settings in the local.settings.json file in the Values collection.

Connections

The connection property is a reference to environment configuration that specifies how the app should connect to your table service. It may specify:

If the configured value is both an exact match for a single setting and a prefix match for other settings, the exact match is used.

Connection string

To obtain a connection string for tables in Azure Storage, follow the steps shown at Manage storage account access keys. To obtain a connection string for tables in Cosmos DB (when using the Tables API extension), follow the steps shown at the Table API FAQ.

This connection string should be stored in an application setting with a name matching the value specified by the connection property of the binding configuration.

If the app setting name begins with "AzureWebJobs", you can specify only the remainder of the name here. For example, if you set connection to "MyStorage", the Functions runtime looks for an app setting that is named "AzureWebJobsMyStorage". If you leave connection empty, the Functions runtime uses the default Storage connection string in the app setting that is named AzureWebJobsStorage.

Identity-based connections

If you are using the Tables API extension, instead of using a connection string with a secret, you can have the app use an Azure Active Directory identity. This only applies when accessing tables in Azure Storage. To do this, you would define settings under a common prefix which maps to the connection property in the trigger and binding configuration.

If you are setting connection to "AzureWebJobsStorage", see Connecting to host storage with an identity. For all other connections, the extension requires the following properties:

Property Environment variable template Description Example value
Table Service URI <CONNECTION_NAME_PREFIX>__tableServiceUri1 The data plane URI of the Azure Storage table service to which you are connecting, using the HTTPS scheme. https://<storage_account_name>.table.core.windows.net

1 <CONNECTION_NAME_PREFIX>__serviceUri can be used as an alias. If both forms are provided, the tableServiceUri form will be used. The serviceUri form cannot be used when the overall connection configuration is to be used across blobs, queues, and/or tables.

Additional properties may be set to customize the connection. See Common properties for identity-based connections.

The serviceUri form cannot be used when the overall connection configuration is to be used across blobs, queues, and/or tables in Azure Storage. The URI itself can only designate the table service. As an alternative, you can provide a URI specifically for each service under the same prefix, allowing a single connection to be used.

When hosted in the Azure Functions service, identity-based connections use a managed identity. The system-assigned identity is used by default, although a user-assigned identity can be specified with the credential and clientID properties. Note that configuring a user-assigned identity with a resource ID is not supported. When run in other contexts, such as local development, your developer identity is used instead, although this can be customized. See Local development with identity-based connections.

Grant permission to the identity

Whatever identity is being used must have permissions to perform the intended actions. You will need to assign a role in Azure RBAC, using either built-in or custom roles which provide those permissions.

Important

Some permissions might be exposed by the target service that are not necessary for all contexts. Where possible, adhere to the principle of least privilege, granting the identity only required privileges. For example, if the app only needs to be able to read from a data source, use a role that only has permission to read. It would be inappropriate to assign a role that also allows writing to that service, as this would be excessive permission for a read operation. Similarly, you would want to ensure the role assignment is scoped only over the resources that need to be read.

You will need to create a role assignment that provides access to your Azure Storage table service at runtime. Management roles like Owner are not sufficient. The following table shows built-in roles that are recommended when using the Table API extension against Azure Storage in normal operation. Your application may require additional permissions based on the code you write.

Binding type Example built-in roles (Azure Storage1)
Input binding Storage Table Data Reader
Output binding Storage Table Data Contributor

1 If your app is instead connecting to tables in Cosmos DB, using an identity is not supported, and the connection must use a connection string.

Usage

The usage of the binding depends on the extension package version, and the C# modality used in your function app, which can be one of the following:

An in-process class library is a compiled C# function that runs in the same process as the Functions runtime.

Choose a version to see usage details for the mode and version.

To return a specific entity by key, use a binding parameter that derives from TableEntity.

To execute queries that return multiple entities, bind to a CloudTable object. You can then use this object to create and execute queries against the bound table. Note that CloudTable and related APIs belong to the Microsoft.Azure.Cosmos.Table namespace.

The TableInput attribute gives you access to the table row that triggered the function.

Set the filter and take properties. Don't set partitionKey or rowKey. Access the input table entity (or entities) using context.bindings.<BINDING_NAME>. The deserialized objects have RowKey and PartitionKey properties.

Data is passed to the input parameter as specified by the name key in the function.json file. Specifying The partitionKey and rowKey allows you to filter to specific records.

Table data is passed to the function as a JSON string. De-serialize the message by calling json.loads as shown in the input example.

For specific usage details, see Example.

Next steps