Microsoft Azure Media Indexer basics – Part 3

In this part of the series covering the basics of using the Azure Media Indexer to make speech in audio and/or video assets searchable with SQL Server Full-text-search I will cover the basics of querying the database to find assets based on spoken words and even list the locations within these assets where the words had been spoken.

If you haven’t already, I suggest that you look at the previous two posts – part 1 covers the basics of uploading and processing an asset to produce the audio index (AIB) and closed captions files and part 2 covers how to prepare the SQL environment and load the AIB into it to enable searching.

With AIB files loaded into a table enabled for full-text-search and with the Media Indexer SQL-Add-on installed, searching for spoken words in assets using the database is nearly as simple as using standard SQL full-text-search constructs, but not quite, some differences and best practices exist and I’ll try to cover the main ones here.

The first thing to note is that when querying over AIB files the terms placed within CONTAINS or CONTAINSTABLE need to be encoded. This encoding can be done manually but it is easier (and useful for other purposes, more on this next) to use the Retrieval class provided with the SQL add-on within the MSRA_AudioIndexing_Retrieval assemly. you can find both x86 and x64 versions in the [InstallDir]\Indexer_SQL_AddOn\bin folder.

A ‘standard’ full-text-search query that looks for the word ‘microsoft’ in the title column of the File table looks like

SELECT FileID FROM Files WHERE CONTAINS(Title,' FORMSOF (INFLECTIONAL,"microsoft") ')

Having been expanded by the Retrieval class a similar query looking for the spoken word ‘microsoft’ in an AIB file contained in the AIB column of the table would look like

SELECT FileID FROM Files WHERE CONTAINS(AIB,' FORMSOF (INFLECTIONAL,"spoken:microsoft@@@") ')

The number of @ influences the confidence level in the matching the SQL filter will apply during query execution, the more @ you have the more inclusive the query will be.

As indicated, thankfully, one does not have to perform the encoding manually, a call to the ExpandQuery or BuildContainsTableClause static methods of the Retrieval class can be used –

string queryText = "microsoft speech"
string sqlContainsClause = Retrieval.ExpandQuery(queryText);
string sqlText = "select FileID ID from Files where contains(AIB,'" +
 sqlContainsClause + "')";

Running this SQL query against the database will return all rows where the terms requested appear within the row’s AIB data (the query can contain one more more words separated by a space or a double-quoted phrase strings), but the magic does not stop there –

If you use ContainsTable the result set will include an initial ranking score for the match –

string queryText = "microsoft speech"
string sqlContainsTableClause =
 Retrieval.BuildContainsTableClause(queryText, "Files", "AIB", 5);
string sqlText = "select FileID ID from Files inner join " +
 sqlContainsTableClause +
 " AS CT on Files.FileID = CT.[KEY] ORDER BY Rank";

This initial ranking is a useful measure to the quality and number of the matches found and so it is useful to sort the records returns by descending order of this ranking.

The initial ranking is approximate, but the Retrieval class also provides a ComputeScore method which takes a query and the AIB (and two other parameters I’ll touch upon shortly) and provides a more thorough ranking.

Because this requires the entire AIB’s contents, it makes sense to not run this on the entire set and this is exactly where the approximate ranking pays off, so the initial flow of a query would be to run the query using CONTAINSTABLE, sort by ranking and take the top n results.

Then one can choose to run the deeper scoring on these selected results by retrieving the AIBs for them from the database and calling the ComputeScore method.

Another thing the ComputeScore method can do is generate a set of snippets from the AIB . if snippets are requested  (by setting the third parameter of the ComputeScore method to true) the process will return with the score a list of snippets, each representing a section of the media file where one or more of the terms sought were found. for each snippet a start and end time is supplied (in seconds from start of media) as well as the score and even the extracted text as a string.

This allows calling applications to be very detailed – an application can display the snippets of text where query terms were found and ‘deep link’ into the media allowing the user to play the relevant section of the media.

The fourth parameter to the ComputeScore method is the maximum number of seconds to include in either side of a term found when extracting the snippet.

a call to the method looks like this

Retrieval.ScoringResult scoringResult = Retrieval.ComputeScore(queryText, result.AIB, false, snippetContext);

Again – this computation can be comparatively extensive and so it would make sense to do that either as a second step after the initial database query and filtering on the approximate score or even, in some cases, it might be worth further limiting the initial result set by getting the details scoring, without the snippets, and only generate snippets for records actually rendered on screen

The power is that one has all the options.

Overall, the process looks like this –

image

Naturally – the size of the AIB file can be quite large (a 40 minute mp3 file I processed resulted in 991kb AIB file) and so transferring this across the network every time a user runs a query may not be wise.

For this reason I think the right pattern is to implemented the query logic within a simple Web API which can be placed on the SQL server itself. This allows for the most efficient transfer of the AIB data and only returning the analysis result, which is a much smaller data set, to the client.

A client can call the API, passing in the term to look for and return a list of matches with snippet information and because this snippet (and hits) information contains time positions within the asset (in seconds) it can use these in the client to start playback of the asset at the relevant location (and even display the text relevant to the match)

An example of a response from a WebAPI I created looks like this (I searched for ‘money’ and ‘criminal’ in a You and Yours BBC Radio 4 podcast) –

[
-{
    Title: "test title"
    URL: "assets\\yyhighlights_20141010-1400a.mp3"
Rank: 3
Duration: 2375.840002
-Snippets: [
-{
    -Hits: [
    -{
        TermScore: 0
        EndPos: 8
StartPos: 0
EndTime: 102.210007
StartTime: 101.57
QueryTerm: "criminal"
}
-{
    TermScore: 0
    EndPos: 31
StartPos: 26
EndTime: 104.01
StartTime: 103.670006
QueryTerm: "money"
}
]
EndTime: 104.25
StartTime: 101.57
Text: "criminal using her to get money out "
}
-{
    -Hits: [
    -{
        TermScore: 0
        EndPos: 70
StartPos: 65
EndTime: 130.17

Another possible approach is to use SQLCLR to run the scoring method directly in the database. This will certainly be more efficient in terms of data transfer but everyone likes this approach. again – its good to have options.

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: