Getting started with Azure Table Storage

Overview

Azure Table Storage is a No-SQL database available on the Azure Cloud platform. It uses the following terminology:

  • Table: A table is a collection of entities to store structured data. It is similar to traditional database tables, except that there is no enforced schema. Each entity can be structured differently.

  • Entity: An entity is similar to a row in a traditional database. An entity contains data structured with properties, the properties being possibly different for each entity.

  • Property: A property is similar to a column in a traditional database. It allows giving a name to a value.

Create a metadata

To create an Azure Table Storage metadata:

  1. Launch the metadata creation wizard and select Azure Table Storage.

  2. Select the Azure Table Storage Module and click Finish

  3. Define the Azure Table Storage Account properties

    1. Choose a Name for this metadata.

    2. Enter the credentials string.
      This string contains the credentials and necessary information to connect to an account. It must match the Microsoft expected syntax. See Configure Azure Storage connection strings.

    3. Enter the Reverse Sample Size.
      This defines the number of sample entities to parse to reverse-engineer the properties.

  4. Click Next, then select the Tables to reverse-engineer.

  5. Click Next, then select the changes to apply to the metadata.

  6. Click Finish.

The metadata is created with the default Account node.

Metadata structure

When you create the metadata, the Account node is created by default. This node contains the connection information. Tables and queries are created below this node.

Table 1. Account Node Properties
Property Description

Connection String

Address used to connect to Azure Table Storage. This string contains the credentials and necessary information to connect to an account. It must match the Microsoft expected syntax. See Configure Azure Storage connection strings.

Reverse Engineering Sample Size

Number of entities to parse to reverse-engineer the structure of tables. By default, this size is set to 10.

Tables and queries

Tables are containers that store data. They can be reverse-engineered at the time of creating the metadata or they can be created manually by right-clicking the Account node, then New > Table.

Queries allow returning a filtered set of tables. A query can be created by right-clicking the Account node, then New > Query.

Both tables and queries contain:

  • Partition Key: Mandatory field used by Azure Table Storage to partition the table for load balancing purposes. Together with the row key they form the unique identifier of the entity in the table.

  • Row Key: Mandatory field used together with the Partition Key to uniquely identify an entity. Both fields must be defined by the client. It is similar to a row identifier or primary key in traditional databases.

  • Property: Field that contains data values. It is similar to a column in traditional databases. There can be as many property nodes as needed per table.

Query properties

Table 2. Query Properties
Property Description

Table

Name of the table to perform the query on.

Filter

Filters the list of returned entities. Refer to Supported Common Operators for more information on the syntax to use.

Top

Number of top tables or entities to return from the set.

Select

Returns the desired properties of an entity from the set. Refer to Select for more information on the syntax to use.

Timeout

Optional timeout of the query, in milliseconds.

Reverse-engineer tables

To reverse-engineer the tables:

  1. Right-click the Account node and from the contextual menu, click Reverse-engineer.

    The Reverse-engineer metadata wizard appears.

  2. See Create a metadata for the wizard page details.

The selected tables are reverse-engineered.

You can also reverse engineer a table by:

  • Creating the table manually and then right-clicking the table node and clicking Actions > Reverse-Engineer.

  • Creating a query and then right-clicking the query node and clicking Actions > Reverse-Engineer.

Create mappings

Mappings using tables

The Integration RDBMS to Azure Table Storage (CRUD) template performs the upsert, insert, update, get, and remove operations on a target table.

To create a mapping using a table:

  1. Drag and drop the following into the mapping:

    • Azure Table Storage table on which to perform operations.

    • One or several source tables containing the values for the partition key, row key and properties.

    • One or several target tables, to load the result.

  2. Map the fields:

    • Map the root node of the Azure table with source data.
      The root node is used as a repetition key. Semarchy xDI performs one operation per value.

    • Map the partition key, row key and property from the sources to the Azure table.

    • Map the fields from the Azure table to the fields in the target table.

  3. Depending on whether you wish to create a consume or a produce mapping, in the integration template, set the Azure Table Operation field with an appropriate value:

    • upsert

    • create

    • update

    • delete

    • get

Mappings using queries

The Integration RDBMS to Azure Storage (Query) template performs queries designed in the Azure Table Storage metadata. The resulting data set can be used further in a mapping.

To create a mapping using a query:

  1. Drag and drop the following into the mapping:

    • Query from the Azure Table Storage metadata.

    • One or several source tables containing the values to iterate the query.

    • One or several target tables, to load the result.

  2. Map the fields:

    • Map the root node of the Azure query with source data.
      The root node is used as a repetition key. Semarchy xDI performs one query per value.

    • Map the fields from the Azure query to the fields in the target table.