<a id="ksqldb-pull-queries-in-ccloud"></a>

# Pull Queries for ksqlDB on Confluent Cloud

[Pull queries](/platform/current/ksqldb/concepts/queries.html#pull) are a relatively new but
integral feature offered by ksqlDB. In contrast to push queries, which
perpetually stream incremental query results to clients, pull queries follow a
traditional request/response model, which means that a pull query retrieves a
finite result from the ksqlDB server and ends on completion, like the way
queries work with traditional databases.

Pull queries are available to all ksqlDB users. The following sections
provide details about using pull queries in Confluent Cloud and describe the
limitations of pull queries.

## Limitations

Pull queries in Confluent Cloud ksqlDB have the following limitations:

- **Consistency:** Pull query results use an eventually consistent consistency
  model, which means that some writes to the table being queried might not yet
  appear in a pull query’s result, even if these writes occurred before the
  pull query request. In practice, this window of “inconsistency” is
  narrow, but you should account for this in your applications.
- **Performance impact:** Because pull queries are processed by the ksqlDB
  application, pull queries do have the potential to reduce the throughput of
  push queries that are being run by the same application. Any potential
  performance impact depends strongly on the underlying workload, but in
  general, perform realistic benchmarks to understand how a pull query
  workload affects the performance of your specific ksqlDB workload.
- **Bandwidth:** Pull queries can consume up to 48 MB per hour, per CSU of
  bandwidth. Requests that exceed this limit are throttled. For example, a
  4-CSU ksqlDB application can process approximately 192 MB of pull query
  responses per hour. If you’ve reached the 192 MB of throttle limit within a
  sliding window over the last 1 hour, ksqlDB won’t process any new query
  requests until the bandwidth used by pull queries in the last hour has
  dropped below 192 MB. The limit is 192 MB, regardless of the number
  of CSU, because the limit check applies to each node. For more than 4 CSU,
  these limits are applied based on an even distribution of data across
  partitions.

Push queries in Confluent Cloud ksqlDB have the following limitations:

- You can’t create more than **100** push queries.

## CLI configuration

You can issue pull queries directly from the Confluent Cloud Console,
with no extra configuration.

If you want to use pull queries from a CLI session, or via HTTP requests,
you must create an API key resource-scoped for your ksqlDB application
to enable your client to connect with your application.

#### NOTE
An API key resource-scoped for a ksqlDB application is different from
an API key that is resource-scoped for an Kafka cluster and must be created for
your specific ksqlDB application.

1. Log in to Confluent Cloud by using the Confluent CLI:
   ```bash
   confluent login
   ```
2. Use the following command to list your ksqlDB applications:
   ```bash
   confluent ksql cluster list
   ```

   Your output should resemble:
   ```none
          Id      |    Name     | Topic Prefix | Kafka Cluster | Storage |                       Endpoint                        | Status
   +--------------+-------------+--------------+---------------+---------+-------------------------------------------------------+--------+
     lksqlc-ok1yo | ksqldb-app1 | pksqlc-e8086 | lkc-2ry82     |  500    | https://pksqlc-e8086.us-west2.gcp.confluent.cloud:443 | UP
   ```

   Note the **Id**, **Kafka**, and **Endpoint** values, which in this example are
   `lksqlc-ok1yo`, `lkc-2ry82`, and
   `https://pksqlc-e8086.us-west2.gcp.confluent.cloud:443`.
   - The application **ID** is used to create an application-specific API key and secret
     that clients use to connect to the application.
   - **Kafka** specifies the ID of your Kafka cluster.
   - The application **Endpoint** is used to connect to your application.
3. Run the following command to specify the Kafka cluster that your CLI session
   should use:
   ```bash
   confluent kafka cluster use <kafka-cluster-id>
   ```
4. Get your Confluent Cloud service account ID to use for security configuration.
   You can find it in the **Id** column of the following command’s output:
   ```bash
   confluent iam service-account list
   ```
5. After you get your service account ID, run the following command to grant
   the required permissions for the topic:
   ```bash
   confluent kafka acl create --allow --service-account <service-account-id> --operations read,write,create --topic "pq_" --prefix
   ```

   The `--topic 'pq_' --prefix` option applies the ACLs to all topics that have
   a name that is prefixed with `pq_`.
6. Run the following command to create an application-specific API key and
   secret by passing the application ID as the `--resource`.
   ```bash
   confluent api-key create --resource <ksqldb-application-id>
   ```
7. Use the key and secret returned by the previous command to connect to your
   application by using the ksqlDB CLI. Pass the key as the username, and the
   secret as the password.
   ```bash
   $CONFLUENT_HOME/bin/ksql -u <api-key> -p <api-key-secret> <endpoint>
   ```

## Example workload

In this section, you build an example workload that enables experimenting with
pull queries in Confluent Cloud.

1. In the ksqlDB CLI, run the following statement to create an input stream.
   ```sql
   CREATE STREAM pq_pageviews (user_id INTEGER KEY, url STRING, status INTEGER) WITH
    (kafka_topic='pq_pageviews', value_format='json', partitions=1);
   ```
2. Create a materialized view over this input stream. The materialized view
   aggregates events from the `pageviews` stream, grouped on the events’ `url`
   field:
   ```sql
   CREATE TABLE pq_pageviews_metrics AS
    SELECT url, COUNT(*) AS num_views
     FROM pq_pageviews
     GROUP BY url
     EMIT CHANGES;
   ```
3. Populate the materialized view by writing events to the `pageviews` input stream.
   ```sql
   INSERT INTO pq_pageviews (user_id, url, status) VALUES (0, 'https://confluent.io', 200);
   INSERT INTO pq_pageviews (user_id, url, status) VALUES (1, 'https://confluent.io/blog', 200);
   ```
4. Now that the materialized view contains some data, issue a pull query against
   it to retrieve the latest count for a given URL.
   ```sql
   SELECT * FROM pq_pageviews_metrics WHERE url = 'https://confluent.io';
   ```

   Your output should resemble:
   ```none
   +-------------------------------------------------+-------------------------------------------------+
   |URL                                              |NUM_VIEWS                                        |
   +-------------------------------------------------+-------------------------------------------------+
   |https://confluent.io                             |1                                                |
   ```

   This pull query should return precisely one row. Each time the pull query
   runs, it will return the latest value for the targeted row.