Skip to main content

33 posts tagged with "analytics"

View All Tags

Databricks Provider for StackQL Available

· 3 min read
Jeffrey Aven
Technologist and Cloud Consultant

We are pleased to announce the release of the Databricks provider for StackQL today.  The Databricks provider is two different providers, databricks_account and databricks_workspace.

info

Check out the registry docs at databricks_account or databricks_workspace.

To get started, pull the providers from the registry as follows:  

registry pull databricks_account;
registry pull databricks_workspace;

databricks_account provider​

The databricks_account provider is used for account-level operations, including provisioning or managing users, groups, unity catalog metastores, workspaces, and account-level cloud resources used by workspaces (such as networking resources).  Services include:

stackql  >>show services in databricks_account;
|----------------------------|---------------|--------------------------------|
|             id             |     name      |             title              |
|----------------------------|---------------|--------------------------------|
| billing:v00.00.00000       | billing       | Account Billing                |
|----------------------------|---------------|--------------------------------|
| iam:v00.00.00000           | iam           | Identity and Access Management |
|----------------------------|---------------|--------------------------------|
| logging:v00.00.00000       | logging       | Log Delivery                   |
|----------------------------|---------------|--------------------------------|
| oauth:v00.00.00000         | oauth         | OAuth Integrations             |
|----------------------------|---------------|--------------------------------|
| provisioning:v00.00.00000  | provisioning  | Account Provisioning           |
|----------------------------|---------------|--------------------------------|
| settings:v00.00.00000      | settings      | Account Settings               |
|----------------------------|---------------|--------------------------------|
| unity_catalog:v00.00.00000 | unity_catalog | Unity Catalog                  |
|----------------------------|---------------|--------------------------------|

Some example databricks_account queries are shown here:

stackql  >>select *  from  databricks_account.iam.account_users where account_id = 'ebfcc5a9-9d49-4c93-b651-b3ee6cf1c9ce' and active = true;
|--------|--------------|-------------------------------------------------------------|------------|------------------|---------------------------------------------|---------------------------------------------|------------------|
| active | displayName  |                           emails                            | externalId |        id        |                    name                     |                    roles         
 |     userName     |
|--------|--------------|-------------------------------------------------------------|------------|------------------|---------------------------------------------|---------------------------------------------|------------------|
| true   | Jeffrey Aven | [{"primary":true,"type":"work","value":"javen@stackql.io"}] | null       | 5728205706991489 | {"familyName":"Aven","givenName":"Jeffrey"} | [{"type":"direct","value":"account_admin"}] | javen@stackql.io |
|--------|--------------|-------------------------------------------------------------|------------|------------------|---------------------------------------------|---------------------------------------------|------------------|

or..

stackql  >>SELECT applicationId,  displayName
stackql  >>FROM databricks_account.iam.account_service_principals, JSON_EACH(roles)
stackql  >>WHERE account_id = 'ebfcc5a9-9d49-4c93-b651-b3ee6cf1c9ce'
stackql  >>AND JSON_EXTRACT(json_each.value, '$.value') = 'account_admin';
|--------------------------------------|-------------|
|            applicationId             | displayName |
|--------------------------------------|-------------|
| 0b7b23de-3e7d-4432-812c-cf517e079a22 | stackql     |
|--------------------------------------|-------------|

or..

stackql  >>select
stackql  >>workspace_id,
stackql  >>workspace_name,
stackql  >>deployment_name,
stackql  >>workspace_status,
stackql  >>pricing_tier,
stackql  >>aws_region,
stackql  >>credentials_id,
stackql  >>storage_configuration_id
stackql  >>from
stackql  >>databricks_account.provisioning.workspaces where account_id = 'ebfcc5a9-9d49-4c93-b651-b3ee6cf1c9ce';
|------------------|----------------|-------------------|------------------|--------------|------------|--------------------------------------|--------------------------------------|
|   workspace_id   | workspace_name |  deployment_name  | workspace_status | pricing_tier | aws_region |            credentials_id            |       storage_configuration_id       |
|------------------|----------------|-------------------|------------------|--------------|------------|--------------------------------------|--------------------------------------|
| 1583879855205171 | stackql-test   | dbc-ddbc0f51-c9cf | RUNNING          | PREMIUM      | us-west-2  | dcacd875-c782-46ea-9d3e-8307975d758a | e52e029f-24bb-4a75-99c3-7796c202dd89 |
|------------------|----------------|-------------------|------------------|--------------|------------|--------------------------------------|--------------------------------------|

databricks_workspace provider​

The databricks_workspace provider is used for workspace-level operations, such as provisioning and managing clusters, dashboards, and workflow jobs (including delta live table pipelines).  Services include:  

stackql  >>show services in databricks_workspace;
|------------------------------|-----------------|-----------------|
|              id              |      name       |      title      |
|------------------------------|-----------------|-----------------|
| apps:v24.12.00279            | apps            | Apps            |
|------------------------------|-----------------|-----------------|
| cleanrooms:v24.12.00279      | cleanrooms      | Cleanrooms      |
|------------------------------|-----------------|-----------------|
| compute:v24.12.00279         | compute         | Compute         |
|------------------------------|-----------------|-----------------|
| dbsql:v24.12.00279           | dbsql           | Dbsql           |
|------------------------------|-----------------|-----------------|
| deltalivetables:v24.12.00279 | deltalivetables | Deltalivetables |
|------------------------------|-----------------|-----------------|
| deltasharing:v24.12.00279    | deltasharing    | Deltasharing    |
|------------------------------|-----------------|-----------------|
| filemanagement:v24.12.00279  | filemanagement  | Filemanagement  |
|------------------------------|-----------------|-----------------|
| iam:v24.12.00279             | iam             | Iam             |
|------------------------------|-----------------|-----------------|
| lakeview:v24.12.00279        | lakeview        | Lakeview        |
|------------------------------|-----------------|-----------------|
| machinelearning:v24.12.00279 | machinelearning | Machinelearning |
|------------------------------|-----------------|-----------------|
| marketplace:v24.12.00279     | marketplace     | Marketplace     |
|------------------------------|-----------------|-----------------|
| realtimeserving:v24.12.00279 | realtimeserving | Realtimeserving |
|------------------------------|-----------------|-----------------|
| repos:v24.12.00279           | repos           | Repos           |
|------------------------------|-----------------|-----------------|
| secrets:v24.12.00279         | secrets         | Secrets         |
|------------------------------|-----------------|-----------------|
| unitycatalog:v24.12.00279    | unitycatalog    | Unitycatalog    |
|------------------------------|-----------------|-----------------|
| vectorsearch:v24.12.00279    | vectorsearch    | Vectorsearch    |
|------------------------------|-----------------|-----------------|
| workflows:v24.12.00279       | workflows       | Workflows       |
|------------------------------|-----------------|-----------------|
| workspace:v24.12.00279       | workspace       | Workspace       |
|------------------------------|-----------------|-----------------|

An example query could be:

stackql  >>select
stackql  >>cluster_id,
stackql  >>aws_attributes,
stackql  >>node_type_id,
stackql  >>state
stackql  >>from
stackql  >>databricks_workspace.compute.clusters
stackql  >>where deployment_name = 'dbc-ddbc0f51-c9cf';
|----------------------|---------------------------------------------------------------------------------------------------------|--------------|------------|
|      cluster_id      |                                             aws_attributes                                              | node_type_id |   state    |
|----------------------|---------------------------------------------------------------------------------------------------------|--------------|------------|
| 1218-233957-q9v9oi86 | {"availability":"SPOT_WITH_FALLBACK","first_on_demand":1,"spot_bid_price_percent":100,"zone_id":"auto"} | m5d.large    | TERMINATED |
|----------------------|---------------------------------------------------------------------------------------------------------|--------------|------------|

To use either provider, set the following environment variables (either locally or as secrets in your preferred CI tool):

  • DATABRICKS_ACCOUNT_ID - a uuid representing your Databricks account id, you can get this from the Databricks UI
  • DATABRICKS_CLIENT_ID - obtained after creating a service principal through the Databricks UI
  • DATABRICKS_CLIENT_SECRET - obtained after creating a service principal secret through the Databricks UI, using the "Generate Secret" function

These are the same variables that Terraform, the Databricks SDKs, and CLI use.  

stackql-deploy examples coming soon, stay tuned!  

⭐ us on GitHub and join our community!

Confluent provider for stackql released

· 4 min read
Jeffrey Aven
Technologist and Cloud Consultant

We're excited to announce the release of the new Confluent provider for StackQL! With this new provider, users can now seamlessly query, manage, and integrate Confluent Cloud resources using familiar SQL syntax. The Confluent provider opens up possibilities for managing Kafka clusters, environments, organizations, and more, providing unparalleled flexibility for building data and event-driven architectures as infrastructure-as-code.

Quick Start Example​

To start, set the CONFLUENT_CLOUD_API_KEY and CONFLUENT_CLOUD_API_SECRET environment variables and then pull the Confluent provider from the StackQL registry:

registry pull confluent;

Querying Confluent Resources​

The Confluent provider includes access to a range of services covering resources like billing, catalog, managed Kafka clusters, environments, and more. Let’s look at a few examples of querying these resources.

Listing Organizations​

To view the organization associated with your Confluent account, use the following query:

stackql >> select * from confluent.org.organizations;
|--------------------------------------|----------------|-------------------------------------------------------------------------|-----------------------------|-----------------------------|-------------|-------------|--------------|
|                  id                  |  display_name  |                              resource_name                              |         created_at          |         updated_at          | jit_enabled | api_version |     kind     |
|--------------------------------------|----------------|-------------------------------------------------------------------------|-----------------------------|-----------------------------|-------------|-------------|--------------|
| 73ea43f0-1685-4a78-bc90-fa63ef8102fe | Aven Solutions | crn://confluent.cloud/organization=73ea43f0-1685-4a78-bc90-fa63ef8102fe | 2024-09-06T21:51:43.895116Z | 2024-09-08T09:23:53.147453Z | false       | org/v2      | Organization |
|--------------------------------------|----------------|-------------------------------------------------------------------------|-----------------------------|-----------------------------|-------------|-------------|--------------|

Listing Environments​

To list the available environments in your organization, use this query:

select * from confluent.org.environments;
|------------|--------------|---------------------------|------------------------------------------------------------------------------------------------|-----------------------------|-----------------------------|------------------------------------------------------------|-------------|-------------|
|     id     | display_name | stream_governance_package |                                         resource_name                                          |         created_at          |         updated_at          |                            self                            | api_version |    kind     |
|------------|--------------|---------------------------|------------------------------------------------------------------------------------------------|-----------------------------|-----------------------------|------------------------------------------------------------|-------------|-------------|
| env-1wz7pv | default      | null                      | crn://confluent.cloud/organization=73ea43f0-1685-4a78-bc90-fa63ef8102fe/environment=env-1wz7pv | 2024-09-06T21:51:43.901757Z | 2024-09-06T21:51:43.901757Z | https://api.confluent.cloud/org/v2/environments/env-1wz7pv | org/v2      | Environment |
|------------|--------------|---------------------------|------------------------------------------------------------------------------------------------|-----------------------------|-----------------------------|------------------------------------------------------------|-------------|-------------|
| env-216dqo | stackql      | ESSENTIALS                | crn://confluent.cloud/organization=73ea43f0-1685-4a78-bc90-fa63ef8102fe/environment=env-216dqo | 2024-10-29T03:47:21.577972Z | 2024-10-29T03:47:21.577972Z | https://api.confluent.cloud/org/v2/environments/env-216dqo | org/v2      | Environment |
|------------|--------------|---------------------------|------------------------------------------------------------------------------------------------|-----------------------------|-----------------------------|------------------------------------------------------------|-------------|-------------|

Fetching Kafka Clusters in a Specific Environment​

To list Kafka clusters available within a specific environment, modify the WHERE clause to target your desired environment:

stackql >> select * 
stackql >> from confluent.managed_kafka_clusters.vw_clusters 
stackql >> where environment = 'env-216dqo';
|------------|-------------|--------------|--------------|------------------------------------------------------------------------------------------------------------------------------------------|--------------|--------------|-------|-------------|----------------|------------------------------------------------------------|------------------------------------------------------------------------------------------------|-----------------------------------------------------|---------------------------------------------------------|-----------|-----------------------------|-----------------------------|--------------------------------------------------------|-------------|---------|
|     id     | environment | display_name | status_phase |                                                              resource_name                                                   
            | api_endpoint | availability | cloud | config_kind | environment_id |                    environment_related                     |                                   environment_resource_name                                    |                    http_endpoint                    |                kafka_bootstrap_endpoint                 |  region   |         created_at          |         updated_at          |                          self                          | api_version |  kind   |
|------------|-------------|--------------|--------------|------------------------------------------------------------------------------------------------------------------------------------------|--------------|--------------|-------|-------------|----------------|------------------------------------------------------------|------------------------------------------------------------------------------------------------|-----------------------------------------------------|---------------------------------------------------------|-----------|-----------------------------|-----------------------------|--------------------------------------------------------|-------------|---------|
| lkc-ov720o | env-216dqo  | cluster_0    | PROVISIONED  | crn://confluent.cloud/organization=73ea43f0-1685-4a78-bc90-fa63ef8102fe/environment=env-216dqo/cloud-cluster=lkc-ov720o/kafka=lkc-ov720o |              | LOW          | AWS   | Basic       | env-216dqo     | https://api.confluent.cloud/org/v2/environments/env-216dqo | crn://confluent.cloud/organization=73ea43f0-1685-4a78-bc90-fa63ef8102fe/environment=env-216dqo | https://pkc-p11xm.us-east-1.aws.confluent.cloud:443 | SASL_SSL://pkc-p11xm.us-east-1.aws.confluent.cloud:9092 | us-east-1 | 2024-10-29T03:48:00.562964Z | 2024-10-29T03:48:00.562964Z | https://api.confluent.cloud/cmk/v2/clusters/lkc-ov720o | cmk/v2      | Cluster |
|------------|-------------|--------------|--------------|------------------------------------------------------------------------------------------------------------------------------------------|--------------|--------------|-------|-------------|----------------|------------------------------------------------------------|------------------------------------------------------------------------------------------------|-----------------------------------------------------|---------------------------------------------------------|-----------|-----------------------------|-----------------------------|--------------------------------------------------------|-------------|---------|

With these examples, you can see how StackQL makes it easy to interact with Confluent Cloud resources directly through SQL.

Confluent Services Supported in StackQL​

The new Confluent provider for StackQL includes the following services:

  • Billing: Manage Confluent Cloud billing and view cost metrics.
  • Catalog: Explore available Confluent Cloud components.
  • Managed Kafka Clusters: Query and manage Kafka clusters.
  • Flink Artifacts and Compute Pools: Manage Flink environments and compute resources.
  • IAM: Configure access controls and permissions.
  • Networking: Set up and view networking configurations.
  • Schema Registry and Clusters: Register, manage, and monitor schemas and clusters.
  • Stream Sharing: Configure shared data streams.

See the full provider documentation at Confluent Provider for StackQL for more details on each service.

Building Composable Infrastructure Stacks​

The Confluent provider for StackQL allows you to compose infrastructure stacks with Confluent resources as part of a broader data infrastructure, integrating seamlessly with other cloud providers. With simple SQL queries, you can pull in resources, monitor usage, and manage configurations across Confluent and other clouds for a cohesive multi-cloud or hybrid cloud setup.

More examples to follow. Let us know what you think! ⭐ us on GitHub.

Anthropic Provider for StackQL Available

· 2 min read
Jeffrey Aven
Technologist and Cloud Consultant

The anthropic provider for stackql is now available in the dev stackql provider registry. The anthropic provider for stackql includes services for interacting with Claude models via the Messages API. To get started download stackql, set the ANTHROPIC_API_KEY environment variable and use the dev registry as shown here:

export DEV_REG="{ \"url\": \"https://registry-dev.stackql.app/providers\" }"
./stackql --registry="${DEV_REG}" shell

Then pull the anthropic provider using:

REGISTRY PULL anthropic;

Now you can run some queries. Here's an example running inference against Claude using the Messages API - the request parameters are supplied in the WHERE clause and the response is projected using JSON_EXTRACT:

SELECT
  id,
  model,
  stop_reason,
  JSON_EXTRACT(content, '$[0].text') AS assistant_message,
  JSON_EXTRACT(usage, '$.output_tokens') AS output_tokens
FROM anthropic.messages.messages
WHERE model = 'claude-sonnet-5'
AND max_tokens = 1024
AND messages = '[{"role": "user", "content": "What is StackQL?"}]';
|------------------------------|-----------------|-------------|-------------------------------------------------------------|---------------|
|              id              |      model      | stop_reason |                      assistant_message                      | output_tokens |
|------------------------------|-----------------|-------------|-------------------------------------------------------------|---------------|
| msg_01MLTLVY6XCTT2cNBeFeJzfj | claude-sonnet-5 | end_turn    | StackQL is a SQL-based framework that lets you query and    |            48 |
|                              |                 |             | manage cloud and SaaS resources using familiar SQL syntax.  |               |
|------------------------------|-----------------|-------------|-------------------------------------------------------------|---------------|

You can also discover which models are available and their capabilities using the vw_model_capabilities view:

SELECT id, display_name, thinking, adaptive, xhigh, max_input_tokens, max_tokens
FROM anthropic.models.vw_model_capabilities;
|-------------------|-----------------|----------|----------|-------|------------------|------------|
|        id         |  display_name   | thinking | adaptive | xhigh | max_input_tokens | max_tokens |
|-------------------|-----------------|----------|----------|-------|------------------|------------|
| claude-sonnet-5   | Claude Sonnet 5 | 1        | 1        | 1     |           200000 |      64000 |
| claude-opus-4-8   | Claude Opus 4.8 | 1        | 1        | 1     |           200000 |      32000 |
| claude-haiku-4-5  | Claude Haiku 4.5| 1        | 0        | 0     |           200000 |      64000 |
|-------------------|-----------------|----------|----------|-------|------------------|------------|

Like other language models, Claude's responses are stochastic, so you'll get slightly different responses each time you query.

Let us know what you think! ⭐ us on GitHub.

OpenAI Provider for StackQL Available

· 3 min read
Jeffrey Aven
Technologist and Cloud Consultant

The openai provider for stackql is now available in the dev stackql provider registry. The openai provider for stackql includes services assistants, audit_logs, batch, chat, completions, embeddings, files, images, models, moderations, projects, uploads, vector_stores, and more. To get started download stackql, set the OPENAI_API_KEY environment variable and use the dev registry as shown here:

export DEV_REG="{ \"url\": \"https://registry-dev.stackql.app/providers\" }"
./stackql --registry="${DEV_REG}" shell

Then pull the openai provider using:

REGISTRY PULL openai;

Now you can run some queries, here are a few simple examples:

stackql  >>select * from openai.models.models;
|------------|------------------------------------|--------|-----------------|
|  created   |                 id                 | object |    owned_by     |
|------------|------------------------------------|--------|-----------------|
| 1712361441 | gpt-4-turbo                        | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1712601677 | gpt-4-turbo-2024-04-09             | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1681940951 | tts-1                              | model  | openai-internal |
|------------|------------------------------------|--------|-----------------|
| 1699053241 | tts-1-1106                         | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1723515131 | chatgpt-4o-latest                  | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1698798177 | dall-e-2                           | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1677532384 | whisper-1                          | model  | openai-internal |
|------------|------------------------------------|--------|-----------------|
| 1706037777 | gpt-4-turbo-preview                | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1727460443 | gpt-4o-audio-preview               | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1692901427 | gpt-3.5-turbo-instruct             | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1727389042 | gpt-4o-audio-preview-2024-10-01    | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1706037612 | gpt-4-0125-preview                 | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1706048358 | gpt-3.5-turbo-0125                 | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1677610602 | gpt-3.5-turbo                      | model  | openai          |
|------------|------------------------------------|--------|-----------------|
| 1692634615 | babbage-002                        | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1692634301 | davinci-002                        | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1727131766 | gpt-4o-realtime-preview-2024-10-01 | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1698785189 | dall-e-3                           | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1727659998 | gpt-4o-realtime-preview            | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1722814719 | gpt-4o-2024-08-06                  | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1715367049 | gpt-4o                             | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1721172741 | gpt-4o-mini                        | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1715368132 | gpt-4o-2024-05-13                  | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1721172717 | gpt-4o-mini-2024-07-18             | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1699046015 | tts-1-hd                           | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1699053533 | tts-1-hd-1106                      | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1698957206 | gpt-4-1106-preview                 | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1671217299 | text-embedding-ada-002             | model  | openai-internal |
|------------|------------------------------------|--------|-----------------|
| 1683758102 | gpt-3.5-turbo-16k                  | model  | openai-internal |
|------------|------------------------------------|--------|-----------------|
| 1705948997 | text-embedding-3-small             | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1705953180 | text-embedding-3-large             | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1698959748 | gpt-3.5-turbo-1106                 | model  | system          |
|------------|------------------------------------|--------|-----------------|
| 1686588896 | gpt-4-0613                         | model  | openai          |
|------------|------------------------------------|--------|-----------------|
| 1687882411 | gpt-4                              | model  | openai          |
|------------|------------------------------------|--------|-----------------|
| 1694122472 | gpt-3.5-turbo-instruct-0914        | model  | system          |
|------------|------------------------------------|--------|-----------------|

The openai provider covers the control plane - models, files, fine-tuning jobs, batches, vector stores and more. Inference endpoints (chat completions, responses, embeddings, images and audio) are out of scope; use the vendor SDKs for invocation.

For example, poll your fine-tuning jobs and batches:

SELECT id, status, model, fine_tuned_model, trained_tokens
FROM openai.fine_tuning.jobs;

SELECT id, status, endpoint, request_counts
FROM openai.batches.batches
LIMIT 10;

Vector stores expose a full CRUD surface with file membership:

SELECT id, name, status, usage_bytes, file_counts
FROM openai.vector_stores.vector_stores;

More to come! Let us know what you think! ⭐ us on GitHub.

Updated Google Provider Available

· One min read
Jeffrey Aven
Technologist and Cloud Consultant

The latest google provider for stackql is available now, and includes a new oracledatabase service, including resources for cloud_vm_clusters, db_nodes, db_servers, cloud_exadata_infrastructures, entitlements, and more.

Summary stats for the new google provider:

Versionv24.09.00254
Total services168
Total resources1941


Let us know what you think! ⭐ us on GitHub.