Book a Demo Free Trial

Upload Relational Data in Azure Table Storage Using Cloud Storage Studio

Gaurav Mantri

Nov 10, 2009

Category: Azure Storage

This version of Cloud Storage Studio supports a new feature wherein you can export data from a Microsoft SQL Server or MySQL server and import it in Azure Table Storage. To start the upload process, just click on the “Upload Icon” from one of the following places:

  • Table context menu (in the treeview).
  • Table icon buttons (above treeview).
  • Entities details window

It will open a pop up window “Table Upload Source” from where you have to select “Relational Database” and click on “OK”. This will take you on connection window. TableUploadSource

Here are the steps that you would need to follow: 1.Connect to database: First thing you will be asked to do is to connect to the desired server. Please select appropriate server type (Microsoft SQL Server is the default), provide necessary information and then click on “Get Databases” button to connect and retrieve available databases. Once the list is retrieved successfully, you can select desired database from the database dropdown and click on “Connect” button to proceed.

ConnectWindow

2. Connect to database: Once the connection is established successfully, you will see a list of available tables & views in a tree view format on the left hand side. To select a particular table or view to import, just click on the check box next to it. To further customize that table, click on the table. You will see column details on the right hand side. By default all columns will be included in the import process. If you don’t want to import a certain column, just uncheck the checkbox next to that column. You can also change the name of the column when it gets imported in Azure as well as its Azure data type.

TablesViewsSelection3 

2. a) Data Type Mapping: When the metadata is retrieved from the database, we set the mapping between database data type & Azure data type. For example, Varchar data type in SQL Server is mapped to String data type in Azure. If you wish to change this mapping for the entire import, just click on “Set Datatype Mapping” button. Make appropriate changes and then click on “Apply” button to apply those changes. [Please note that this will override any changes you have made at the column level].

DataTypeMapping

2. b) Reset Data Type Mapping: To reset the data type mappings back to default, just click on “Reset Data Type Mapping” button. Note: ”Reset Datatype Mapping” button set the default mapping in all tables.

3.Table/Key Settings: Next window will show you details about the tables you have chosen in Step 2. Here you will have an option of specifying the PartitionKey value, RowKey value and the Azure Table name. By default the PartitionKey value will be the name of the database table and the RowKey value will be GUID. Name of the Azure table will be the table selected from the main application window. You can change the values of individual tables.

TableMapping3 

3. a) Global Table/Key Settings: If you wish to make changes which need to be applied to all tables selected, you would need to click on “Table/Key Setting” button. It will open up a window like the one shown below.

TableKeySettings

For PartitionKey settings, you have two options:

  • Choose database table name
  • Manually specify

For RowKey settings, you have three options:

  • Guid: Application will use Guid.NewGuid() value for RowKey
  • Ticks: Application will use DateTime.Now.Ticks for RowKey
  • Table Unique Key: Application will use the database table unique key values for RowKey. For tables which don’t have a Unique key constraint you can either choose Guid or Ticks value for RowKey.

For Azure table name, you have four options:

  • Database schema name
  • Database table name
  • Existing table in Azure storage
  • Manually specify: If this option is selected and the table is not specified then the table is created for you.

Furthermore you have an option to specify what the application should do if it encounters a conflict (i.e. the entity you’re trying to upload already exists). You can decide to replace the existing entity with the new value.

4.Summary: On this screen you will see a summary of what will be uploaded. To make any changes, just click on the back button. To start upload, just click on the “Upload” button.

 SummaryWindow3

5.Upload: This screen will show the upload process. It will tell you for each table how many rows are fetched from the database, how many were uploaded to Azure Table Storage and how many have failed. To view failed entities in a table, just click on “View Failed” button for that table. List of failed entities will be displayed in the bottom part of the screen. For each entity, you can view the reason why it failed and you can edit the entity (including PartitionKey/RowKey). UploadWindow3

6.Retry Failed: If any of the entities have failed, you can retry uploading them by clicking on the “Retry Failed” button.

RetryWindow3

To upload another set of tables from the same database, just click on “Start Over” button.

To connect to another database in the same database server, just click on “Change Database” button.

To connect to a different database, just click on “Disconnect” button.