Filter data by using Azure Data Lake Storage query acceleration (preview)

This article shows you how to use query acceleration (preview) to retrieve a subset of data from your storage account.

Query acceleration (preview) is a new capability for Azure Data Lake Storage that enables applications and analytics frameworks to dramatically optimize data processing by retrieving only the data that they require to perform a given operation. To learn more, see Azure Data Lake Storage Query Acceleration (preview).

Note

The query acceleration feature is in public preview, and is available in the Canada Central and France Central regions. To review limitations, see the Known issues article. To enroll in the preview, see this form.

Prerequisites

  • To access Azure Storage, you'll need an Azure subscription. If you don't already have a subscription, create a free account before you begin.

  • A general-purpose v2 storage account. see Create a storage account.

  • .NET SDK.

Install packages

  1. Download the query acceleration packages. You can obtain a compressed .zip file that contains these packages by using this link: https://aka.ms/adls/qqsdk/.net.

  2. Extract the contents of this file to your project directory.

  3. Open your project file (.csproj) in a text editor, and add these package references inside of the <Project> element.

    <ItemGroup>
        <PackageReference Include="Azure.Storage.Blobs" Version="12.5.0-preview.1" />
        <PackageReference Include="Azure.Storage.Common" Version="12.4.0-preview.1" />
        <PackageReference Include="Azure.Storage.QuickQuery" Version="12.0.0-preview.1" />
    </ItemGroup>
    
  4. Restore the preview SDK packages. This example command restores the preview SDK packages by using the dotnet restore command.

    dotnet restore --source C:\Users\contoso\myProject
    
  5. Restore all other dependencies from the public NuGet repository.

    dotnet restore
    

Add statements

Add these using statements to the top of your code file.

using Azure.Storage.Blobs;
using Azure.Storage.Blobs.Models;
using Azure.Storage.Blobs.Specialized;
using Azure.Storage.QuickQuery;
using Azure.Storage.QuickQuery.Models;

Query acceleration retrieves CSV and Json formatted data. Therefore, make sure to add using statements for any CSV or Json parsing libraries that you choose to use. The examples that appear in this article parse a CSV file by using the CsvHelper library that is available on NuGet. Therefore, we'd add these using statements to the top of the code file.

using CsvHelper;
using CsvHelper.Configuration;

To compile examples presented in this article, you'll also need to add these using statements as well.

using System.Threading.Tasks;
using System.IO;
using System.Globalization;
using System.Threading;
using System.Linq;

Retrieve data by using a filter

You can use SQL to specify the row filter predicates and column projections in a query acceleration request. The following code queries a CSV file in storage and returns all rows of data where the third column matches the value Hemingway, Ernest.

  • In the SQL query, the keyword BlobStorage is used to denote the file that is being queried.

  • Column references are specified as _N where the first column is _1. If the source file contains a header row, then you can refer to columns by the name that is specified in the header row.

The async method BlobQuickQueryClient.QueryAsync sends the query to the query acceleration API, and then streams the results back to the application as a Stream object.

static async Task QueryHemingway(BlockBlobClient blob)
{
    string query = @"SELECT * FROM BlobStorage WHERE _3 = 'Hemingway, Ernest'";
    await DumpQueryCsv(blob, query, false);
}

private static async Task DumpQueryCsv(BlockBlobClient blob, string query, bool headers)
{
    try
    {
        using (var reader = new StreamReader((await blob.GetQuickQueryClient().QueryAsync(query,
                new CsvTextConfiguration() { HasHeaders = headers }, 
                new CsvTextConfiguration() { HasHeaders = false }, 
                new ErrorHandler(),
                new BlobRequestConditions(), 
                new ProgressHandler(),
                CancellationToken.None)).Value.Content))
        {
            using (var parser = new CsvReader(reader, new CsvConfiguration(CultureInfo.CurrentCulture) 
            { HasHeaderRecord = false }))
            {
                while (await parser.ReadAsync())
                {
                    parser.Context.Record.All(cell =>
                    {
                        Console.Out.Write(cell + "  ");
                        return true;
                    });
                    Console.Out.WriteLine();
                }
            }
        }
    }
    catch (Exception ex)
    {
        Console.Error.WriteLine("Exception: " + ex.ToString());
    }
}

class ErrorHandler : IBlobQueryErrorReceiver
{
    public void ReportError(BlobQueryError err)
    {
        Console.Error.WriteLine($"Error: {err.Name}:{ err.Description }");
    }
}

class ProgressHandler : IProgress<long>
{
    public void Report(long value)
    {
        Console.Error.WriteLine("Bytes scanned: " + value.ToString());
    }
}

Retrieve specific columns

You can scope your results to a subset of columns. That way you retrieve only the columns needed to perform a given calculation. This improves application performance and reduces cost because less data is transferred over the network.

This code retrieves only the PublicationYear column for all books in the data set. It also uses the information from the header row in the source file to reference columns in the query.

static async Task QueryPublishDates(BlockBlobClient blob)
{
    string query = @"SELECT PublicationYear FROM BlobStorage";
    await DumpQueryCsv(blob, query, true);
}

The following code combines row filtering and column projections into the same query.

static async Task QueryMysteryBooks(BlockBlobClient blob)
{
    string query = @"SELECT BibNum, Title, Author, ISBN, Publisher FROM BlobStorage WHERE Subjects LIKE '%Mystery%'";
    await DumpQueryCsv(blob, query, true);
}

Next steps