# Create Table without data aggregation

**URL:** <https://forum.confluent.io/t/create-table-without-data-aggregation/4493>\
**Category:** ksqlDB\
**Created:** [17 March 2022 14:35 UTC](https://forum.confluent.io/t/create-table-without-data-aggregation/4493 "2022-03-17T14:35:52Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![dsboudart](https://avatars.discourse-cdn.com/v4/letter/d/d9b06d/32.png) [@dsboudart](https://forum.confluent.io/u/dsboudart)\
**Post date:** [17 March 2022 14:35 UTC](https://forum.confluent.io/t/create-table-without-data-aggregation/4493/1 "2022-03-17T14:35:52Z")

</div>

I just started to use the ksqlDB Confluent feature, and it stood out that it is not possible to proceed with the following command: CREATE TABLE AS SELECT A, B, C FROM [STREAM\_A] [EMIT CHANGES];

I wonder why this is not possible or if there’s a way of doing it? Data aggregation here is feeling a heavy process to a simple solution.

The source is a STREAM and not a TABLE. The field types are:

- String
- Integers
- Record

Let me share an example of the executed command that returns an error as a result.

```auto
CREATE TABLE test_table
WITH (KEY_FORMAT='JSON',VALUE_FORMAT='AVRO')
AS
SELECT id
    , timestamp
    , servicename
    , content->assignedcontent
FROM created_stream
WHERE content->assignedcontent IS NOT NULL
[EMIT CHANGES];

```

@rmoff - Ticket created here to continue the discussion started on Stack Overflow - [HERE](https://stackoverflow.com/questions/71498095/create-table-without-data-aggregation)

Additionally to everything discussed in the stack overflow ticket, I’m starting to wonder if the concept of a Table in ksqlDB is the same as a table in, for example, BigQuery.  
Would be able to clarify this, please?

---

<div class="post-metadata">

**Author:** ![rmoff](https://sea1.discourse-cdn.com/flex019/user_avatar/forum.confluent.io/rmoff/32/3_2.png) [@rmoff](https://forum.confluent.io/u/rmoff)\
**Post date:** [17 March 2022 16:15 UTC](https://forum.confluent.io/t/create-table-without-data-aggregation/4493/2 "2022-03-17T16:15:39Z")

</div>

Hey @dsboudart, thanks for bringing the question here. Let me re-post my StackOverflow answer for completeness and then we can take it from there.

There are two are different types of object in ksqlDB:

- A `STREAM` is an unbounded series of events - just like a Kafka topic. The only difference is that a `STREAM` has a declared schema.
- A `TABLE` is state, for a given key. It’s the same as KTable in Kafka Streams if you’re familiar with that.

Both are backed by Kafka topics.

So you can do this - note that it’s creating a _`STREAM`_ not a _`TABLE`_

```sql
CREATE STREAM test_stream
WITH (KEY_FORMAT='JSON',VALUE_FORMAT='AVRO')
AS
SELECT id
    , timestamp
    , servicename
    , content->assignedcontent
FROM created_stream
WHERE content->assignedcontent IS NOT NULL;

```

If you really want to create a `TABLE` then use the `LATEST_BY_OFFSET` aggregation, assuming you’d using `id` as your key:

```sql
CREATE TABLE test_table
WITH (KEY_FORMAT='JSON',VALUE_FORMAT='AVRO')
AS
SELECT id
    , LATEST_BY_OFFSET(timestamp)
    , LATEST_BY_OFFSET(servicename)
    , LATEST_BY_OFFSET(content->assignedcontent)
FROM created_stream
WHERE content->assignedcontent IS NOT NULL
GROUP BY id;

```

So, over to you - perhaps you can explain why it is you think you need to create a `TABLE` and _not_ a `STREAM`? Can you illustrate with actual data - what data have you got coming in, and how are you wanting to access it after processing with ksqlDB?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex019/uploads/confluentcommunity/original/1X/c49438c90c9df282e9996fdf6971be890c71b65a.svg) [@system](https://forum.confluent.io/u/system)\
**Post date:** [16 April 2022 14:36 UTC](https://forum.confluent.io/t/create-table-without-data-aggregation/4493/3 "2022-04-16T14:36:19Z")

</div>

This topic was automatically closed after 30 days. New replies are no longer allowed.