Query Provider Pools with SQL

​

Sources

• a Workspace with at least one connected Provider;
• a Workspace Administrator who can give you a role;
• DBeaver, or Python 3.10 or later.

​

1. Get the Provider Pools Reader role

1. Opens People → Users.
2. Opens the actions on your row and chooses Edit Roles.
3. Selects Provider Pools Reader and chooses Save.

​

2. Create a SQL token

1. Sign in to your Workspace and open Protection → Access Tokens. Its address is https://<workspace-slug>.parable.work/platform/protection/access-tokens. A Provider Pools Reader or a Data Access member lands there when they open the Workspace.
2. In Your tokens, choose Create token.
3. Under Use it for, keep SQL. The other choice, MCP, creates a token for an MCP host; SQL refuses it.
4. Enter a Name that says where you will use it, such as DBeaver on my laptop.
5. Under Expires in, choose 30 days, 90 days, 180 days, or 1 year. The default is 90 days.
6. Choose Create token.
7. Copy the token and store it in your password manager. Parable shows it once and cannot show it again. Then choose I have saved it.

• works only with SQL, and only in the Workspace where you created it;
• runs each query with your roles at that moment, so it stops working when your role or your membership is removed;
• stops working when it expires or is revoked. After a revocation or a removed role, SQL can keep answering for up to 30 seconds.

​

3. Find your Workspace slug

​

4. Connect DBeaver

​

Run DBeaver on Java 21

1. Install Java 21, for example Eclipse Temurin 21 from Adoptium, or run brew install openjdk@21 on macOS.
2. Quit DBeaver.
3. Open dbeaver.ini in a text editor. It is in the DBeaver installation:

   System	File
   macOS	/Applications/DBeaver.app/Contents/Eclipse/dbeaver.ini
   Windows	C:\Program Files\DBeaver\dbeaver.ini
   Linux	/usr/share/dbeaver-ce/dbeaver.ini

4. Find the line that reads -vm. Replace the line below it with the path to Java 21’s java program (javaw.exe on Windows). If the file has no -vm line, add -vm and the path as two lines at the top of the file. Both lines must come before -vmargs. With Homebrew on macOS, this command prints the path:
   echo "$(brew --prefix openjdk@21)/libexec/openjdk.jdk/Contents/Home/bin/java"
   
5. On the line after -vmargs, add --add-opens=java.base/java.nio=ALL-UNNAMED if the file does not already have it.
6. Save the file and start DBeaver.

-vm
/opt/homebrew/opt/openjdk@21/libexec/openjdk.jdk/Contents/Home/bin/java

​

Add the driver

1. Download the flight-sql-jdbc-driver jar, version 18 or 19, from Maven Central.
2. In DBeaver, open Database → Driver Manager and choose New.
3. On Settings, enter:

   Field	Value
   Driver Name	Arrow Flight SQL
   Class Name	org.apache.arrow.driver.jdbc.ArrowFlightJdbcDriver
   URL Template	jdbc:arrow-flight-sql://{host}:{port}
   Default Port	443

4. On Libraries, choose Add File and pick the jar. If DBeaver offers a list of driver classes, choose org.apache.arrow.driver.jdbc.ArrowFlightJdbcDriver.
5. Choose OK.

​

Create the connection

1. Choose Database → New Database Connection, pick Arrow Flight SQL, and choose Next.
2. Set Host to sql.parable.work and Port to 443. Leave Username and Password empty.
3. Open Driver properties. Right-click in the list, choose Add new property, and add these two properties:

   Property	Value
   token	Your SQL token, pat_..., without Bearer
   x-tenant	Your Workspace slug

4. Choose Test Connection. DBeaver reports that it is connected.
5. Choose Finish.

​

5. Connect with Python

pip install adbc-driver-flightsql pyarrow

export PARABLE_SQL_TOKEN='pat_...'
export PARABLE_WORKSPACE='acme'

import os

import adbc_driver_flightsql.dbapi as flightsql

url = os.environ.get("PARABLE_SQL_URL", "grpc+tls://sql.parable.work:443")
token = os.environ["PARABLE_SQL_TOKEN"]
workspace = os.environ["PARABLE_WORKSPACE"]

with flightsql.connect(
    url,
    db_kwargs={
        "adbc.flight.sql.authorization_header": f"Bearer {token}",
        "adbc.flight.sql.rpc.call_header.x-tenant": workspace,
    },
    autocommit=True,
) as connection:
    with connection.cursor() as cursor:
        cursor.execute(
            """
            SELECT table_schema, table_name
            FROM information_schema.tables
            WHERE table_catalog = 'providers'
              AND table_schema <> 'information_schema'
            ORDER BY table_schema, table_name
            """
        )
        tables = cursor.fetchall()
        for schema, table in tables:
            print(f"providers.{schema}.{table}")

        if not tables:
            print("No Provider tables yet. Connect a Provider and let it sync.")
        else:
            schema, table = tables[0]
            cursor.execute(f"SELECT * FROM providers.{schema}.{table} LIMIT 10")
            print(cursor.fetch_arrow_table())

python first_query.py

​

6. Run a first query

providers.{connector}.{table}

SELECT *
FROM providers.<connector>.<table>
LIMIT 10

​

7. Find tables and columns

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_catalog = 'providers'
  AND table_schema = 'google'
  AND table_name = 'users'
ORDER BY ordinal_position

​

What you can query

​

System columns

SELECT *
FROM providers.google.users
WHERE _parable_deleted = false
  AND _parable_observed = true

​

Limits

• Read-only. Only SELECT statements run. Any other statement fails with Only read-only SELECT queries are supported. There are no transactions.
• Plain EXPLAIN. EXPLAIN followed by a SELECT returns the query’s plan and runs nothing: one logical_plan row and one physical_plan row. The physical plan names each step, its partitions and its keys. It shows no storage path and no filter condition, because a filter can hold a data policy. EXPLAIN ANALYZE runs the query, so it fails with error code QL-SQ-008. EXPLAIN VERBOSE and the other forms fail with QL-SQ-009.
• One hour per query. The hour covers planning and the whole result, including the time your tool spends reading it. A longer query fails with DEADLINE_EXCEEDED, error code QL-TO-002, and query <id> exceeded timeout of 3600s. Set your tool’s own timeout to at least an hour if you run long queries.
• 2,000,000 rows per result. A query whose result has more rows fails with RESOURCE_EXHAUSTED and error code QL-SQ-005; nothing is cut off silently. The error can arrive after some rows, so discard any rows your tool read before it. Add LIMIT, filter with WHERE, or aggregate in SQL. To read more rows than that, run one query per range of a column, such as one month at a time.
• Paid AI calls. One query may send up to 1,000,000 calls of ponder(), embed() and the Jev functions, and spend up to $1,000 on them. A call that would pass a limit is not sent, and the query fails with RESOURCE_EXHAUSTED and error code QL-SQ-010 (calls) or QL-SQ-011 (cost). Call the function over fewer rows, or publish the SQL as a Plot. See the AI call limits.
• Shared memory. Queries that run at the same time can share memory and temporary disk. A query that asks for more than is left fails with RESOURCE_EXHAUSTED and error code QL-IN-009, whether its own size or the queries beside it used the space. Provider tables are partitioned by dt, the day a load wrote the row, so a WHERE dt bound skips the files of other days. Grouping, DISTINCT, joins, and sorts over every day of a large table need the most memory: bound dt first, and run large queries one at a time rather than together.
• 600 requests a minute from one IP address. A query takes at least two requests, and DBeaver sends more while you browse. Requests over the limit are refused with HTTP status 429, which most tools report as UNAVAILABLE.
• The Provider Pools only. The Provider Pools Reader role reaches the three catalogs above and nothing else.
• One Workspace per token. To query another Workspace, create a token there.

​

ODBC tools

​

Troubleshooting