> For the complete documentation index, see [llms.txt](https://docs.deltastream.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.deltastream.io/how-do-i.../relation.md).

# Create DeltaStream Objects to Structure Raw Data

In DeltaStream a [Data Store](/overview/core-concepts/store.md) provides access to the raw data in your external systems. To process that data in queries, we define **DeltaStream objects** that attach metadata and data format information to the underlying store data.

## Understanding the Data

As an example, below is a defined Apache Kafka store that contains several entities:

```sh
demodb.public/msk_public# LIST ENTITIES;
      Entity name       
-----------------------
  ds_syslogs      
  ds_pageviews         
  ds_shipments         
  ds_users             
```

Now assume all entities are in `JSON` format. See [CREATE STORE](/reference/sql-syntax/ddl/create-store.md) and [UPDATE ENTITY](/reference/sql-syntax/ddl/update-entity.md) for using other serialization formats. For information around data formats -- for example, [CREATE STREAM](/reference/sql-syntax/ddl/create-stream.md) -- refer to the relation’s DDL statements.

You can inspect the entities to understand the kind of data you have -- for example the `ds_pageviews` entity:

```sh
demodb.public/msk# PRINT ENTITY ds_pageviews;
{"userid":"User_7"} | {"viewtime":1677196372920,"userid":"User_7","pageid":"Page_82"}
{"userid":"User_3"} | {"viewtime":1677196372962,"userid":"User_3","pageid":"Page_97"}
{"userid":"User_6"} | {"viewtime":1677196373021,"userid":"User_6","pageid":"Page_80"}
{"userid":"User_1"} | {"viewtime":1677196373081,"userid":"User_1","pageid":"Page_73"}
{"userid":"User_2"} | {"viewtime":1677196373122,"userid":"User_2","pageid":"Page_35"}
{"userid":"User_7"} | {"viewtime":1677196373182,"userid":"User_7","pageid":"Page_58"}
```

Here is the `ds_users` entity:

```sh
demodb.public/msk# PRINT ENTITY ds_users;
{"userid":"User_6"} | {"registertime":1677196517022,"userid":"User_6","regionid":"Region_9","gender":"OTHER","interests":["News","Movies"],"contactinfo":{"phone":"6503889999","city":"Palo Alto","state":"CA","zipcode":"94301"}}
{"userid":"User_8"} | {"registertime":1677196517619,"userid":"User_8","regionid":"Region_5","gender":"FEMALE","interests":["News","Movies"],"contactinfo":{"phone":"6502215368","city":"San Carlos","state":"CA","zipcode":"94070"}}
{"userid":"User_1"} | {"registertime":1677196518042,"userid":"User_1","regionid":"Region_3","gender":"FEMALE","interests":["News","Movies"],"contactinfo":{"phone":"9492229999","city":"Irvine","state":"CA","zipcode":"92617"}}
{"userid":"User_4"} | {"registertime":1677196518620,"userid":"User_4","regionid":"Region_6","gender":"OTHER","interests":["News","Movies"],"contactinfo":{"phone":"6503889999","city":"Palo Alto","state":"CA","zipcode":"94301"}}
```

`PRINT ENTITY` reads directly from the underlying entity in the store. For Kafka-type stores, that entity is a Kafka topic.

## Defining DeltaStream Objects

When you know what the data looks like in your entities, you can attach a DeltaStream structure to them for use in queries. In the example below, `ds_pageviews` is the underlying entity and `pageviews` is the DeltaStream [Database](/overview/core-concepts/databases.md#stream) relation defined on top of it:

```sql
CREATE STREAM pageviews (
    viewtime BIGINT, userid VARCHAR, pageid VARCHAR
) WITH ('topic'='ds_pageviews', 'value.format'='JSON');
```

See [CREATE STREAM](/reference/sql-syntax/ddl/create-stream.md) for more information.

Since the `ds_users` entity hosts user information that changes over time, you can define a [Database](/overview/core-concepts/databases.md#changelog) relation to capture ongoing changes to each `userid`:

```sql
CREATE CHANGELOG users_log (
    registertime BIGINT, userid VARCHAR, regionid VARCHAR, gender VARCHAR, interests ARRAY<VARCHAR>, contactinfo STRUCT<phone VARCHAR, city VARCHAR, "state" VARCHAR, zipcode VARCHAR>,
    PRIMARY KEY(userid)
) WITH ('topic'='ds_users', 'key.format'='JSON', 'key.type'='STRUCT<userid VARCHAR>', 'value.format'='JSON');
```

See [CREATE CHANGELOG](/reference/sql-syntax/ddl/create-changelog.md) for more information.

{% hint style="info" %}
**Note** For certain applications, it may be more useful to have access to a snapshot of the resulting data. See [Database](/overview/core-concepts/databases.md#materialized_view) and [CREATE MATERIALIZED VIEW AS](/reference/sql-syntax/query/materialized-view/create-materialized-view-as.md) for more information on how to create a view for the data.
{% endhint %}

When you have defined objects, you can list them through their database and namespace:

```sh
demodb.public/msk# LIST OBJECTS;
          Name         |       Type       |  Owner   |      Created at      |      Updated at       
-----------------------+------------------+----------+----------------------+-----------------------
  users_log            | Changelog        | sysadmin | 2023-01-12T20:41:00Z | 2023-01-12T20:41:00Z  
  pageviews            | Stream           | sysadmin | 2023-01-12T20:39:02Z | 2023-01-12T20:39:02Z  
```

In addition to listing them, you can also use their database and namespace to describe them:

```sh
demodb.public/msk# DESCRIBE OBJECT pageviews;
    Name    |  Type  |                           Metadata                           |                 Columns                  |      Details       | Primary key |  Owner   |      Created at      |      Updated at       
------------+--------+--------------------------------------------------------------+------------------------------------------+--------------------+-------------+----------+----------------------+-----------------------
  pageviews | Stream | {value.format : json,store : msk,topic : pageviews}          | viewtime  BIGINT                         | store=msk          |             | sysadmin | 2023-01-12T20:39:02Z | 2023-01-12T20:39:02Z  
            |        |                                                              | userid  VARCHAR                          | topic=pageviews    |             |          |                      |                       
            |        |                                                              | pageid  VARCHAR                          |                    |             |          |                      |                       
```

## Using DeltaStream Objects

When you define a DeltaStream relation on top of an entity, you can query that relation using DeltaStream SQL. For example, you can use the `pageviews` stream relation in interactive queries:

```sh
demodb.public/msk# SELECT * FROM pageviews;
{"userid":"User_5"} | {"viewtime":1677274911334,"userid":"User_5","pageid":"Page_14"}
{"userid":"User_8"} | {"viewtime":1677274911528,"userid":"User_8","pageid":"Page_65"}
{"userid":"User_9"} | {"viewtime":1677274911766,"userid":"User_9","pageid":"Page_49"}
{"userid":"User_3"} | {"viewtime":1677274911812,"userid":"User_3","pageid":"Page_21"}
{"userid":"User_3"} | {"viewtime":1677274912412,"userid":"User_3","pageid":"Page_25"}
{"userid":"User_1"} | {"viewtime":1677274912569,"userid":"User_1","pageid":"Page_56"}
{"userid":"User_6"} | {"viewtime":1677274912819,"userid":"User_6","pageid":"Page_20"}
```

You can also use that relation in persistent queries, where it is continuously used as a source or sink:

```sql
CREATE STREAM user2_views
    AS SELECT userid, pageid
    FROM pageviews
    WHERE userid = 'User_2';
```