DOCSEARCHbyAlgolia
Submit Docs

shillelagh

repository·main·Indexed 19 days ago

https://github.com/betodealmeida/shillelagh

A Python library and CLI that enables querying diverse resources—such as APIs (Google Sheets, Socrata), files (S3 Parquet, CSV, JSON), and in-memory objects—using standard SQL. It acts as a bridge by treating non-SQL data sources as relational database tables through a system of adapters and virtual tables in SQLite.

Tokens
23.5K
Snippets
81
Records
104
Agent score
65%

What's inside shillelagh

  1. How Shillelagh automatically registers adapters

    main

    Shillelagh uses a transparent mechanism to handle virtual tables in SQLite. Instead of requiring users to manually register modules and create virtual tables, Shillelagh intercepts SQLError: no such table errors.

    When you execute a query against a table name that doesn't exist (e.g., a URI like s3://bucket/path/to/file), Shillelagh:

    1. Parses the error to extract the table name.
    2. Iterates through all registered adapters to find one that supports the table name.
    3. If found, it automatically runs the CREATE VIRTUAL TABLE command using arguments provided by the adapter's parse_uri method.
    4. Re-runs your original query.

    To the user, this means you can query remote data sources directly using SQL as if they were local tables.

    -- You can query a remote resource directly without manual setup
SELECT * FROM "s3://bucket/path/to/file";

  3. Define Fields and Filters for Adapters

    main

    Fields represent columns in Shillelagh and control how data is typed, filtered, and sorted. When defining a field, you specify its type, which filters the adapter handles (vs. SQLite), whether filtering is exact or inexact, and how sorting is managed.

    Type Conversion

    Shillelagh handles type conversion between Python types and the adapter's raw format (often strings). For example, an ISODateTime field allows users to work with datetime.datetime objects in Python, while the adapter only sees ISO strings.

    Filtering Logic

    • Adapter Filtering: If a filter type (e.g., IsNotNull) is included in the field's filters list, Shillelagh passes the predicate to the adapter's get_data method via a bounds dictionary.
    • Inexact Filtering: If a field is marked with exact=False, the adapter can perform 'coarse' filtering (e.g., fetching a whole day of data from an API) and return the results to SQLite, which then performs the 'fine' filtering (e.g., narrowing down to a specific hour).
    event_time = ISODateTime(
    filters=[Range, Equal, NotEqual, IsNull, IsNotNull],
    exact=True,
    order=Order.ANY,
)

  4. Handle data conversion using Custom Fields

    main

    When an adapter's underlying data source uses a different format than native Python types (e.g., an API returning ISO strings for timestamps), you have two implementation patterns:

    1. Manual Conversion (get_rows/insert_row)

    You can perform conversions manually inside get_rows (to return Python objects) and insert_row (to convert Python objects back to the source format).

    2. Custom Field Mapping (get_data/insert_data)

    Alternatively, you can define a custom Field for specific columns. This allows the adapter to work with the raw/internal format while Shillelagh handles the conversion automatically.

    When using custom fields, you must implement the get_data, insert_data, delete_data, and update_data methods instead of the get_rows variants.

    Field Methods:

    • parse(value): Converts the internal format to a native Python type.
    • format(value): Converts a native Python type to the internal format.
    • quote(value) (optional): Used if the adapter uses the build_sql helper.
    from shillelagh.fields import ISODateTime

class ISOAdapter(Adapter):
    # 'time' will be represented internally as an ISO string
    time = ISODateTime()

    def get_data(
        self,
        bounds: Dict[str, Filter],
        order: List[Tuple[str, RequestedOrder]],
        **kwargs: Any,
    ) -> Iterator[Dict[str, Any]]:
        yield {
            "rowid": 1,
            "time": "2021-01-01T12:00:00+00:00",
        }

  5. How adapter discovery works with `supports` and `fast`

    main

    Shillelagh performs adapter discovery in two distinct phases to balance speed and capability:

    1. Fast Pass (fast=True): Shillelagh calls the supports method of all registered adapters. Adapters should return a boolean quickly. If an adapter needs to perform expensive operations (like network requests) to determine support, it should return None to indicate it may support the URI.
    2. Slow Pass (fast=False): If no adapter returned True in the first pass, Shillelagh performs a second pass where adapters are allowed to perform expensive introspection (e.g., network requests) to confirm support.

    Example supports implementation:

    @staticmethod
def supports(uri: str, fast: bool = True, **kwargs: Any) -> Optional[bool]:
    parsed = urllib.parse.urlparse(uri)
    query_string = urllib.parse.parse_qs(parsed.query)
    return (
        parsed.netloc == "api.weatherapi.com"
        and parsed.path == "/v1/history.json"
        and "q" in query_string
        and ("key" in query_string or "api_key" in kwargs)
    )

  6. What are Adapters in Shillelagh

    main
    Adapters are plugins that enable Shillelagh to query non-SQL resources, such as APIs (Google Sheets, Socrata) or files (S3, CSV), by treating them as if they were SQL tables. This allows you to use standard SQL syntax to interact with diverse data sources.

  7. How the adapter discovery process works

    main

    When Shillelagh searches for an adapter to handle a missing table, it performs a two-phase discovery process using the supports class method of available adapters:

    1. Fast Pass (fast=True): Shillelagh calls supports(table_name, fast=True) on all adapters. Adapters should only perform cheap operations here (no network calls). If an adapter is unsure, it should return None (meaning "maybe") rather than False.
    2. Slow Pass (fast=False): If no adapter returned True in the first pass, but some returned None, Shillelagh calls supports(table_name, fast=False) only on those that returned None. This allows adapters to perform expensive operations (like network HEAD requests) to confirm support.

  8. Adapter Implementation Strategies

    main

    When writing an adapter, you can choose between two main strategies:

    1. Minimalist Adapter: Implements no filtering, sorting, or limit/offset. It returns all data for every request, delegating all processing to SQLite. This is the simplest to write but least efficient.
    2. Optimized Adapter: Implements all data processing (filtering, sorting, limit, and offset) within the get_data method. This is the most efficient as it minimizes the amount of data transferred from the source to Shillelagh.

  9. Query Socrata APIs

    main

    The Socrata adapter allows you to query Socrata Open Data API endpoints directly using their JSON resource URLs. The adapter is currently read-only.

    SELECT date, administered_dose1_recip_4
FROM "https://data.cdc.gov/resource/unsk-b7fc.json"
WHERE location = 'US'
ORDER BY date DESC
LIMIT 10

  10. Query system resources via system://

    main

    Shillelagh includes a built-in adapter to query system resources (based on psutil). Currently, it supports querying CPU usage per processor using the system://cpu endpoint.

    Important: Streaming Data This adapter streams data. If you do not specify a LIMIT in your SQL query, the client might hang if it expects all data to be returned at once. While Python cursors handle this via iteration, the Shillelagh CLI may hang.

    You can control the polling frequency by passing an interval parameter in the URL query string.

    -- Query CPU usage
SELECT cpu0 FROM "system://cpu" LIMIT 1

-- Query with a custom polling interval (0.1 seconds)
SELECT cpu0 FROM "system://cpu?interval=0.1"

  11. Use Safe Adapters for Shared Environments

    main

    Adapters that interact with the filesystem should be marked as unsafe. This allows Shillelagh to protect users in shared environments.

    To ensure only secure plugins are loaded, use the special SQLAlchemy dialect: shillelagh+safe://.

    Dialect: shillelagh+safe://

  12. Test the Postgres backend with Docker

    main

    The postgres/ directory contains a Docker configuration designed for testing the Shillelagh Postgres backend or serving as an installation template.

    To spin up the environment, run:

    docker compose -f postgres/docker-compose.yml up

    Once the containers are running, you can verify the installation by executing the example script located at examples/postgres.py.

    docker compose -f postgres/docker-compose.yml up