Skip to main content

22 posts tagged with "analytics"

View All Tags

· 3 min read

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.vw_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.vw_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.

· 2 min read

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 a simple example using the high-level claude_35_chat interface:

stackql  >>select * from anthropic.messages.claude_35_chat;
|----------------------------|-----------|-------------|---------------|--------------|---------------|--------------------------------|
|           model            |   role    | stop_reason | stop_sequence | input_tokens | output_tokens |            content             |
|----------------------------|-----------|-------------|---------------|--------------|---------------|--------------------------------|
| claude-3-5-sonnet-20240620 | assistant | end_turn    | null          |           13 |            39 | StackQL is a SQL-like query    |
|                            |           |             |               |              |               | language and universal API     |
|                            |           |             |               |              |               | client that allows users to    |
|                            |           |             |               |              |               | query, analyze, and manage     |
|                            |           |             |               |              |               | cloud infrastructure and       |
|                            |           |             |               |              |               | services across multiple       |
|                            |           |             |               |              |               | providers using familiar SQL   |
|                            |           |             |               |              |               | syntax.                        |
|----------------------------|-----------|-------------|---------------|--------------|---------------|--------------------------------|

Or you can use the lower-level messages interface directly:

stackql  >>select * from anthropic.messages.message
stackql  >>where "anthropic-version" = '2023-06-01'
stackql  >>and data__model = 'claude-3-5-sonnet-20240620'
stackql  >>and data__max_tokens = 1024
stackql  >>and data__messages = '[{"role": "user", "content": "Hello, world"}]';
|--------------------------------|------------------------------|----------------------------|-----------|-------------|---------------|---------|----------------------------------------|
|            content             |              id              |           model            |   role    | stop_reason | stop_sequence |  type   |                 usage                  |     
|--------------------------------|------------------------------|----------------------------|-----------|-------------|---------------|---------|----------------------------------------|     
| [{"text":"Hello! How can I     | msg_01MLTLVY6XCTT2cNBeFeJzfj | claude-3-5-sonnet-20240620 | assistant | end_turn    | null          | message | {"input_tokens":10,"output_tokens":30} |     
| assist you today? Feel free    |                              |                            |           |             |               |         |                                        |     
| to ask me any questions or let |                              |                            |           |             |               |         |                                        |     
| me know if you need help with  |                              |                            |           |             |               |         |                                        |     
| anything.","type":"text"}]     |                              |                            |           |             |               |         |                                        |     
|--------------------------------|------------------------------|----------------------------|-----------|-------------|---------------|---------|----------------------------------------|

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.

· 4 min read

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          |
|------------|------------------------------------|--------|-----------------|

here is an example of a chat completion using the provider:

stackql  >>select choices from openai.chat.completions
stackql  >>where data__model = 'gpt-4o'
stackql  >>and data__messages = '[{"role": "system", "content": "what is stackql?"}]';
|----------------------------------------------------------------------------------|
|                                     choices                                      |
|----------------------------------------------------------------------------------|
| [{"finish_reason":"stop","index":0,"logprobs":null,"message":{"content":"StackQL |
| is a query language designed to interact with cloud infrastructure services      |
| such as AWS, Google Cloud Platform, Microsoft Azure, and others. It allows users |
| to manage and provision resources using a SQL-like syntax, enabling familiar     |
| database-style querying for cloud infrastructure management. StackQL simplifies  |
| the process of interacting with APIs from various cloud providers by providing   |
| a unified interface, allowing for efficient resource management and automation   |
| through queries that resemble SQL statements. This can make it easier for those  |
| familiar with SQL to manage cloud resources without needing to dive deep into    |
| the intricacies of each provider's API.","refusal":null,"role":"assistant"}}]    |
|----------------------------------------------------------------------------------|

then run it again...

stackql  >>select choices from openai.chat.completions
stackql  >>where data__model = 'gpt-4o'
stackql  >>and data__messages = '[{"role": "system", "content": "what is stackql?"}]';
|----------------------------------------------------------------------------------|
|                                     choices                                      |
|----------------------------------------------------------------------------------|
| [{"finish_reason":"stop","index":0,"logprobs":null,"message":{"content":"StackQL |
| is a query language and platform designed to simplify the management and         |
| automation of cloud infrastructure. It allows users to interact with cloud       |
| services using SQL-like queries, providing a familiar and powerful interface for |
| developers and IT professionals. By abstracting the complexities of different    |
| cloud providers, StackQL enables users to perform operations such as deploying,  |
| configuring, and managing infrastructure resources across various cloud          |
| platforms through a unified command-line interface or programmatic environment.  |
| This can be particularly useful for tasks like resource provisioning,            |
| monitoring, and compliance auditing, offering an efficient way to manage         |
| multi-cloud environments.","refusal":null,"role":"assistant"}}]                  |
|----------------------------------------------------------------------------------|

its stochastic of course so you will get slightly different answers each time

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

· One min read

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.

· 2 min read

Analyzing firewall rules is crucial for maintaining security in your cloud infrastructure. Using StackQL, you can efficiently query and analyze Google Cloud firewall configurations to ensure that your security policies are correctly implemented and that there are no unexpected open ports or protocols that might pose a security risk. Below is a simple query that retrieves important details about the ingress firewall rules for a specific network in a Google Cloud project.

SELECT 
    name, 
    source_range,
    ip_protocol,
    allowed_ports,
    direction
FROM (
    SELECT 
    name,
    source_ranges.value as source_range,  
    JSON_EXTRACT(allowed.value, '$.IPProtocol') as ip_protocol,
    JSON_EXTRACT(allowed.value, '$.ports') as allowed_ports,
    direction
    FROM google.compute.firewalls, json_each(sourceRanges) as source_ranges, json_each(allowed) as allowed
    WHERE project = 'stackql-k8s-the-hard-way-demo' 
    AND network = 'https://www.googleapis.com/compute/v1/projects/stackql-k8s-the-hard-way-demo/global/networks/kubernetes-the-hard-way-dev-vpc'
) t 
WHERE 
source_range = '0.0.0.0/0' 
and direction = 'INGRESS';

This query provides a comprehensive list of all ingress firewall rules that apply to any IP address (0.0.0.0/0) within the specified Google Cloud project and network. The results include the firewall rule name, the source IP range, the protocol, the allowed ports, and the direction of the traffic, an example is shown below:

|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
|                     name                      | source_range | ip_protocol | allowed_ports | direction |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
| default-allow-icmp                            | 0.0.0.0/0    | icmp        | null          | INGRESS   |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
| default-allow-rdp                             | 0.0.0.0/0    | tcp         | ["3389"]      | INGRESS   |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
| default-allow-ssh                             | 0.0.0.0/0    | tcp         | ["22"]        | INGRESS   |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
| kubernetes-the-hard-way-dev-allow-external-fw | 0.0.0.0/0    | tcp         | ["22"]        | INGRESS   |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
| kubernetes-the-hard-way-dev-allow-external-fw | 0.0.0.0/0    | tcp         | ["6443"]      | INGRESS   |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|                                                                                         
| kubernetes-the-hard-way-dev-allow-external-fw | 0.0.0.0/0    | icmp        | null          | INGRESS   |                                                                                         
|-----------------------------------------------|--------------|-------------|---------------|-----------|

You can use this query to help quickly identify potential security vulnerabilities. Regularly auditing these rules ensures that your cloud environment remains secure and that only the necessary ports and protocols are open to the internet.

Give us your feedback! ⭐ us here!