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``: .. code:: bash 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: .. code:: bash python -m pyamselect You can verify the installation by running: .. code:: bash amselect --version The explicit ``query`` subcommand is recommended. For backward compatibility, query options can also be passed directly to ``amselect`` without the subcommand. .. _options-1: 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: .. toctree:: :maxdepth: 2 query-language/index 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``: .. code:: text 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: .. code:: sql 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``: .. code:: 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: .. code:: bash airmettle-select-client \ -H -P 443 -r "$(cat request.json)" -o results.csv Using the Python client: .. code:: bash amselect query -H -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: .. code:: json {"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``: .. code:: 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: .. code:: bash airmettle-select-client \ -H -P 443 -r "$(cat request.json)" -o results.jsonl Using the Python client: .. code:: bash amselect query -H -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: .. code:: json {"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: .. code:: bash amselect query -H -P 443 --request-file request.json | head -n 5 When writing to standard output, status messages are suppressed to avoid mixing with query data.