DOCSEARCHbyAlgolia
Submit Docs

Querybook Documentation

repository·master·Indexed 25 days ago

https://github.com/pinterest/querybook

Querybook is a Big Data IDE and webapp (version 3.42.1) for discovering, creating, and sharing data analyses, queries, and tables. It features a centralized platform for query composition, documentation, and data lineage, supporting integrations with query engines like Presto, Hive, Snowflake, and BigQuery. Key capabilities include DataDocs with Python cells via Pyodide, AI-powered SQL assistance, advanced charting, and collaborative query reviews.

Tokens
40.9K
Snippets
72
Records
258
Agent score
80%

What's inside Querybook

  1. Overview of Querybook plugin types

    master

    Querybook supports a wide variety of plugin types to extend its functionality. Common plugin types include:

    • Query Engine Plugin: Add custom query engines or extend default ones by inheriting from existing executors.
    • Auth Plugin: Implement custom authentication methods or post-authentication logic (e.g., auto-assigning users to environments).
    • Engine Status Checker: Customize how backend query engine information is exposed to the frontend.
    • Exporter Plugin: Provide ways for users to move query results to external sites (e.g., Python or R exporters).
    • Notifier Plugin: Send messages upon query completion (e.g., Email or Slack notifications).
    • Job Plugin: Add new job schedules or schedule tasks from the Task plugin.
    • Metastore Plugin: Configure how a metastore is populated or add new ways to load table information.
    • Result Store Plugin: Add custom storage for query results/logs (e.g., local file system or Google Cloud).
    • Task Plugin: Implement custom asynchronous tasks.
    • Web Page Plugin: Inject custom JS/CSS into the webapp via webpage_plugin/custom_script.ts.
    • Lineage Plugin: Fetch lineage information from custom backends.
    • Event Logger Plugin: Store event logs (API requests, etc.) in custom locations.
    • DAG Exporter Plugin (Experimental): Create workflows from Query Cells in DataDocs.
    • API Plugin: Create or modify API endpoints.
    • Monkey Patch Plugin: Override or modify existing modules or functions.
    • AI Assistant Plugin: Enable LLM-powered features like title generation and text-to-SQL.

  3. Overview of Querybook features

    master

    Querybook is a Big Data IDE designed for discovering, creating, and sharing data analyses, queries, and tables. Key capabilities include:

    • Analyses Organization: Use rich text, queries, and charts to organize work.
    • Query Composition: Features autocompletion and hovering tooltips.
    • Dashboards: Build dashboards using scheduling and charting in DataDocs.
    • Collaboration: Live query collaboration with other users.
    • Documentation: Add metadata and documentation to tables.
    • Analytics: View lineage, sample queries, frequent users, and search rankings based on historical query runs.

  4. Core features of Querybook

    master

    Querybook provides several key capabilities for data workflows:

    • Analyses: Organize rich text, queries, and charts into cohesive analyses.
    • Query Composition: Compose queries with autocompletion and hovering tooltips.
    • Dashboards: Build dashboards using scheduling and charting within DataDocs.
    • Collaboration: Live query collaboration with other users.
    • Documentation: Add metadata and documentation directly to tables.
    • Data Insights: Leverage past query runs to view lineage, sample queries, frequent users, and search rankings.

  5. Default Out-of-box Integrations in Querybook

    master

    By default, the Querybook image build only includes core packages. The following integrations are supported out-of-the-box without additional package installation:

    • Query Engines: Firebird, Mysql, Sqlite, Postgresql, Oracle, Mssql
    • Metastore: MysqlMetastore, SqlalchemyMetastore
    • Authentication: Username/Password
    • Exporter: python exporter
    • Result Store (Persisting query result): db, file
    • Elasticsearch: custom hosted
    • Parsing (transpilation): SQLGlot

  7. What are the default Engine Status Checkers?

    master

    Querybook provides three built-in engine checkers to monitor the health of backend query engines in the UI:

    1. SelectOneChecker: Sends a select 1 query to the engine every minute and verifies the returned value.
    2. ConnectionChecker: Attempts to create a cursor connection with the engine (similar to select 1 but without issuing a query).
    3. NullChecker: Always returns null (this is the default behavior).

    You can select which checker to use for a specific engine via the Admin Tools > Query Engine section in the Querybook UI.

  8. Share Datadocs and Lists with User Groups

    master
    Starting in version 3.40.0, you can grant access to Datadocs and lists directly to entire user groups. Members of the group automatically inherit the assigned permissions (such as read or edit). This simplifies access management, as group membership changes automatically update user access to all shared resources within that group.

  9. Understand the Querybook security entity hierarchy

    master

    Querybook implements access control using a tree-based hierarchy where Environments act as the root nodes. Access is determined by walking up this tree from a specific entity to the environment level.

    The Hierarchy:

    1. Environments (Root)
    2. DataDocs (Must reside within a single environment)
    3. Query Engines (Can have many-to-many relationships with environments)
    4. Metastore (Each query engine belongs to a single metastore)
    5. Tables & Schemas (Associated with 0 or more metastores)
    6. Query Executions (Must belong to a query engine)

    Access Logic: When checking permissions, Querybook traverses the relationship tree up to the Environment. Because of many-to-many relationships, an entity may be linked to multiple environments. If a user has access to any of the environments associated with an entity, they are granted access to that entity.

  10. How Query Review works

    master

    Query Review is an experimental governance mechanism that implements a controlled workflow for query validation:

    1. Submission: A user submits a query with peer review parameters. The query is saved with a PENDING_REVIEW status.
    2. Assignment: The system creates review records and assigns them to specified reviewers.
    3. Review Process:
      • Approval: When approved, the query automatically transitions to a running state and executes.
      • Rejection: If rejected, execution remains blocked until the query is revised and resubmitted.
    4. Notification: Notifications are sent to relevant parties throughout the process.

  11. Customize Authentication for Query Engines

    master

    Querybook's default engines use a fixed SQL connection string. To implement advanced authentication (like proxy users, auth tokens, or service discovery via Zookeeper), you must implement a custom client and executor.

    Proxy Users: When Querybook initializes a client, it passes a proxy_user parameter. This string is the unique username from the Querybook database's Users table. You can use this to fetch user metadata (like email) or pass it to the engine (e.g., writing it to a Proxy-Authorization header).

    Service Discovery: To use service discovery (e.g., Zookeeper), modify the EXECUTOR_TEMPLATE to accept connection details or modify how the connection string is parsed (see the Hive executor for an example).

  12. How query validation works in Querybook

    master

    Query validation is available in Adhoc query and DataDoc query cells. It works by sending the entire query to the engine every 2 seconds (debounced) after a user edits the text.

    Validation is automatically skipped in two scenarios:

    1. The query is empty.
    2. The query contains templating syntax, such as {{ or {%.

    Validation results (errors and warnings) are displayed in the line number gutter, inside the editor, and on the run button.