Querying with the CLI

Overview

Azure AirMettle Select provides two command-line clients for querying your storage accounts. Both support the same query capabilities and accept the same request payload format.

airmettle-select-client – the native C++ client.
amselect – a Python client distributed as the pyamselect package.

Either client can be used interactively or in a pipeline. They produce identical query results and can be used interchangeably.

Native Client

Compatibility

airmettle-select-client is compatible with x86 GNU/Linux and has been tested on Ubuntu 22.04.

Options

airmettle-select-client accepts the following options:

-H, --host

Hostname or address of the Azure AirMettle Select endpoint, defaulting to production host.

-P, --port

Port number of the service. Public query endpoints listen on 443.

-r, --request

Configuration options for the query in JSON. See AWS S3’s SelectObjectContentRequest for details.

The root object must additionally include the subscription_id, api_key, storage_account, container, and blob.

The next sections provide examples of valid request payloads.

-o, --output

Local file path to save the query results

--ca-cert

Path to a CA certificate bundle for TLS verification.

--insecure-skip-tls-verify

Disable TLS certificate verification. Use only for local development endpoints with self-signed certificates.

The hostname and port are configurable to allow you to specify an Azure AirMettle Select endpoint in a specific region for reduced latency or costs, or to comply with regulatory requirements. TLS certificate verification is enabled by default. Public endpoints must present a certificate whose chain is trusted and whose hostname matches the query endpoint.

Python Client

Requirements

pyamselect requires Python 3.10 or later. It runs on any platform supported by Python, including Linux, macOS, and Windows.

Installation

Install pyamselect from the provided wheel using pip:

pip install pyamselect-1.1.2-py3-none-any.whl

After installation, the amselect command will be available on your PATH. Alternatively, you can invoke the client as a Python module:

python -m pyamselect

You can verify the installation by running:

amselect --version

The explicit query subcommand is recommended. For backward compatibility, query options can also be passed directly to amselect without the subcommand.

Options

amselect query accepts the following options:

-H, --host

Hostname or address of the Azure AirMettle Select endpoint

-P, --port

Port number of the service. Public query endpoints listen on 443.

-r, --request

Query request as an inline JSON string. Mutually exclusive with --request-file.

--request-file

Path to a file containing the JSON request. Mutually exclusive with -r.

-o, --output

Local file path to save the query results. When omitted, results are written to standard output.

--ca-cert

Path to a CA certificate bundle for TLS verification.

--insecure-skip-tls-verify

Disable TLS certificate verification. Use only for local development endpoints with self-signed certificates.

--version

Print the client version and exit.

Request Payload

Both clients accept the same JSON request payload. The payload is wrapped in a select_request object and must include the following fields: subscription_id, api_key, storage_account, container, blob, expression_type, expression, input_options, and output_options.

If a subscription has Marketplace contract status, that status must be active before queries can run. Inactive Marketplace contracts return 403 Forbidden.

See AWS S3’s SelectObjectContentRequest for details on the input_options and output_options fields.

The hostname and port are configurable to allow you to specify an Azure AirMettle Select endpoint in a specific region for reduced latency or costs, or to comply with regulatory requirements.

Query Language Reference

Detailed SQL syntax and expression behavior are documented in the query language reference:

Query Language
- S3 SelectObjectContent
- SQL Reference

Examples

Azure AirMettle Select supports querying blobs in various formats, including compressed and uncompressed CSV and JSON.

Querying CSV

In this example, we will query a CSV file stored in an Azure Blob Storage account. It is stored in a container named container in the storage account airmettlestore123 with the blob name data.csv.

The metadata for this blob has already been generated using the POST /prepare endpoint.

Contents of data.csv:

name,age,city,state,country
Alice,30,New York,New York,USA
Bob,25,Los Angeles,California,USA
Charlie,35,Chicago,Illinois,USA

We will run a simple SQL query to count the number of records in the file, excluding the header row:

SELECT count(_1) FROM Object

To specify the query we want to run and information about the format of both the input and output data, we will use the following JSON and save it in a local file named request.json:

{
  "select_request": {
    "subscription_id": "SUBSCRIPTION_ID",
    "api_key": "API_KEY",
    "storage_account": "airmettlestore123",
    "container": "container",
    "blob": "data.csv",
    "expression_type": "sql",
    "expression": "SELECT count(_1) FROM Object",
    "input_options": {
      "type": "csv",
      "value": {
        "csv_header_config": "ignore",
        "csv_field_delimiter": ","
      }
    },
    "output_options": {
      "type": "csv",
      "value": {
        "record_delimiter": "\n"
      }
    }
  }
}

Using the native client:

airmettle-select-client \
  -H <host> -P 443 -r "$(cat request.json)" -o results.csv

Using the Python client:

amselect query -H <host> -P 443 --request-file request.json -o results.csv

Both commands send the query and save the results to results.csv. In this case the query will return 3.

Querying Compressed JSONL

In this example, we will query a GZIP-compressed JSON lines file containing transaction logs. The blob name is transactions.jsonl.gz.

Each record in transactions.jsonl.gz contains the following fields: date, city, and amt.

The uncompressed contents of transactions.jsonl.gz are as follows:

{"date": "2024-02-08", "city": "Seattle", "amt": 50}
{"date": "2024-06-01", "city": "Portland", "amt": 200}
{"date": "2024-06-01", "city": "San Francisco", "amt": 150}
{"date": "2024-09-01", "city": "Los Angeles", "amt": 80}
{"date": "2025-01-01", "city": "Portland", "amt": 90}

We will use the following JSON to specify the query and save it in a local file named request.json:

{
  "select_request": {
    "subscription_id": "SUBSCRIPTION_ID",
    "api_key": "API_KEY",
    "storage_account": "airmettlestore123",
    "container": "container",
    "blob": "transactions.jsonl.gz",
    "expression_type": "sql",
    "expression": "SELECT city, date FROM Object WHERE amt > 100",
    "input_options": {
      "type": "json",
      "value": {
        "json_type": "lines",
        "json_compression_type": "gzip"
      }
    },
    "output_options": {
      "type": "json",
      "value": {
        "record_delimiter": "\n"
      }
    }
  }
}

Using the native client:

airmettle-select-client \
  -H <host> -P 443 -r "$(cat request.json)" -o results.jsonl

Using the Python client:

amselect query -H <host> -P 443 --request-file request.json -o results.jsonl

Both commands send the query and save the results to results.jsonl. In this case the query will return:

{"city": "Portland", "date": "2024-06-01"}
{"city": "San Francisco", "date": "2024-06-01"}

Piping Results to Standard Output

The Python client can write query results directly to standard output when the -o option is omitted. This is useful for piping results into other tools:

amselect query -H <host> -P 443 --request-file request.json | head -n 5

When writing to standard output, status messages are suppressed to avoid mixing with query data.