Microsoft Azure Media Indexer basics – Part 2

In my previous post I’ve described the Azure Media indexer at high level and walked through the steps required to produce the AIB file (amongst other things) for a video or audio asset.

Whilst AIB files can be processed individually (as will be touched upon in the next post), the real power comes from using them in conjunction with the Media Indexer SQL Server add-on , allowing the result of the media indexing job to be integrated into SQL Server’s full text search capability.

To do that a SQL Server instance needs to be prepared with the SQL add-on and this short post will discuss the steps taken to prepare a database to be searchable.

The first step is to download the Azure Media Indexer SQL Add-on which, at the time of writing, can be found here

Once downloaded run either the x86 or x64 installer as required which will perform the installation steps and expand the SDK files (by default into C:\Program Files\Azure Media Services Indexer SDK)

The best next step at this point, in my opinion at least, is to read the user guide delivered with the SDK which can be found under the [Install Dir]\Indexer_SQL_AddOn\Docs folder within the installation location as this explains quite a lot about how the add-on works and how it should be used as well as describing the capabilities and usage of the classes available within the MSRA_AudioIndexing_Retrieval.dll assembly that ships with the SDK.

The installer would have configured the full text search filter within SQL Server and so the next step is to have a database in which the AIB data will be stored and to ensure that full text search is enabled on the database –

image

Within the database, you can create a table to hold the AIB data. The minimum requirement, as outlined by the user guide is to have two columns – a column called ‘AIB’ of type varbinary(max) to hold the bytes of the AIB file and a column called ‘Ext’ of type varchar(4) to hold the extension which should always be ‘.aib’.

This could be a dedicated table or columns added to an existing table. Obviously you would want to include some sore of reference to the original content and metadata so that this information can be available alongside the search results.

The AIB column needs to be configured for full-text-search, a SQL script to create a sample table exists in the SDK in the location – [InstallDir] \code\Setup.sql, below is the part of the script that creates the full text search catalogue and associates the AIB and FilePath columns with it

CREATE FULLTEXT CATALOG AudioSearchFTCat

CREATE FULLTEXT INDEX ON
	files (AIB TYPE COLUMN ext LANGUAGE 43017, 
			FilePath LANGUAGE English)
			KEY INDEX IX_Files
			ON AudioSearchFTCat
	WITH CHANGE_TRACKING AUTO

and that is it!

Following these steps the SQL server and table created are ready to be used to index media files, a simple insert, such as the example below can then add data to the table which will then be query able

buffer = System.IO.File.ReadAllBytes(filePath); using (SqlConnection con = new SqlConnection(m.ConnectionString)) { con.Open(); using (SqlCommand cmd = new SqlCommand("insert into files
(Title, Description,Duration, FilePath, Ext,AIB) values
(@Title, @Description, @Duration, @FilePath,@Ext, @AIB)", con)) { cmd.Parameters.Add(new SqlParameter("@Title", "test title")); cmd.Parameters.Add(new SqlParameter("@Description", "test description")); cmd.Parameters.Add(new SqlParameter("@Duration", duration)); cmd.Parameters.Add(new SqlParameter("@FilePath", "assets\\" + assetName)); cmd.Parameters.Add(new SqlParameter("@Ext", ".aib")); cmd.Parameters.Add(new SqlParameter("@AIB", SqlDbType.VarBinary,
buffer.Length, ParameterDirection.Input, false, 0, 0,
"AIB", DataRowVersion.Current, (SqlBinary)buffer)); int result = cmd.ExecuteNonQuery(); tick4.Visibility = System.Windows.Visibility.Visible; } }

In the third post in this series I go over the basics of querying the table for spoken words in the source media file.

About Yossi Dahan
I work as a cloud solutions architect in the Azure team at Microsoft UK. I spend my days working with customers helping be successful in the cloud with Microsoft Azure.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: