# APPLICATION

## Syntax

```sql
BEGIN APPLICATION application_name
    statement;
    statement;
    ...
    statement;
END APPLICATION 
[WITH (query_property = value [, ...])];
```

## Description <a href="#description" id="description"></a>

By using APPLICATION, you can create a set of DSQL statements (including [ddl](https://docs.deltastream.io/reference/sql-syntax/ddl "mention") and [](https://docs.deltastream.io/reference/sql-syntax/query "mention") statements) and run them as a unit of work with all-or-nothing effect:

* In the case of [ddl](https://docs.deltastream.io/reference/sql-syntax/ddl "mention") statements, either all of them succeed or none do, depending on whether there is a failure in processing statements. This means the metastore is updated only if all statements in the application succeed.
* In the case of [](https://docs.deltastream.io/reference/sql-syntax/query "mention") statements, all queries run in a single job. This helps achieve better efficiency and resource utilization at runtime.

{% hint style="info" %}
**Note** The order of statements in an application matters.
{% endhint %}

### Supported Statements

DeltaStream supports the below statement types in application:

* [use](https://docs.deltastream.io/reference/sql-syntax/command/use "mention") (`USE database`, `USE schema`, `USE store`)
* [create-stream](https://docs.deltastream.io/reference/sql-syntax/ddl/create-stream "mention")
* [create-changelog](https://docs.deltastream.io/reference/sql-syntax/ddl/create-changelog "mention")
* [create-stream-as](https://docs.deltastream.io/reference/sql-syntax/query/create-stream-as "mention") (virtual and non-virtual)
* [create-changelog-as](https://docs.deltastream.io/reference/sql-syntax/query/create-changelog-as "mention") (virtual and non-virtual)
* [insert-into](https://docs.deltastream.io/reference/sql-syntax/query/insert-into "mention")
* [create-table](https://docs.deltastream.io/reference/sql-syntax/ddl/create-table "mention")(Snowflake sink only)
* [create-table-as](https://docs.deltastream.io/reference/sql-syntax/query/create-table-as "mention")(Snowflake sink only)

### Benefits of Application

Applications help you achieve better efficiency and overall cost reduction in two ways:

1. Application enhances the data retrieval process from a [store](https://docs.deltastream.io/overview/core-concepts/store "mention"). It enables you to execute multiple queries through a single job. In turn this optimization enables DeltaStream to read data from a given source [#entity](https://docs.deltastream.io/overview/core-concepts/store#entity "mention") only once, when multiple queries within an application refer to the same relation in their [from](https://docs.deltastream.io/reference/sql-syntax/query/select/from "mention") clause. The retrieved data is shared across all these queries.\
   \
   This can significantly reduce storage and network overhead on the source store. When you run the same set of queries separately, each query accesses the source store separately. The system reads the same records from the source entity multiple times -- once per job.
2. You can define a [#\_stream](https://docs.deltastream.io/overview/core-concepts/databases#_stream "mention") or a [#\_changelog](https://docs.deltastream.io/overview/core-concepts/databases#_changelog "mention") as a virtual relation in an application. You use a CAS statement to do this.\
   \
   A virtual relation is similar to a non-virtual one, except it is not backed by a topic in a persistent store. Use virtual relations to create intermediate results that you can in turn use in multiple queries in the same application. Given that records of a virtual stream or changelog aren't written to a store, they simplify the processing logic and reuse already-computed results without adding an extra cost. See [virtual relations](#virtual-relation) below for more details.

### What Happens During an Application Failure

If a failure occurs when processing a [ddl](https://docs.deltastream.io/reference/sql-syntax/ddl "mention") or [](https://docs.deltastream.io/reference/sql-syntax/query "mention") statement in an application (for example, due to a parse error), then the whole application fails. This means no entities are created and no query will start.

If a runtime failure occurs when the application job is running (for example, due to an authentication error to access a store), then all queries in the application fail. However, any relation(s) created at the beginning of application via [ddl](https://docs.deltastream.io/reference/sql-syntax/ddl "mention") statements remain.

### Resume an Application

Applications follow the same naming and resume semantics as other queries.

* An Application can be launched with an optional name by setting the `query.name` property in the `WITH` block. Check [named-queries](https://docs.deltastream.io/reference/sql-syntax/query/named-queries "mention") for more details.
* When launching an Application, the `resume.from.query.id`, `resume.from.query.name`, and `resume.from.query.version` properties can be used to resume processing from where a previously terminated Application left off. Check [resume-query](https://docs.deltastream.io/reference/sql-syntax/query/resume-query "mention")for more details.

## Virtual Relation

A virtual [#\_stream](https://docs.deltastream.io/overview/core-concepts/databases#_stream "mention") or a [#\_changelog](https://docs.deltastream.io/overview/core-concepts/databases#_changelog "mention") is similar to a non-virtual one, except that it is not backed by an [#entity](https://docs.deltastream.io/overview/core-concepts/store#entity "mention") in a [store](https://docs.deltastream.io/overview/core-concepts/store "mention"). This means records of a virtual relation are not written into a persistent storage layer.

You can only create a virtual relation in an application, and it's available only to use in queries within the same application. A virtual relation does not belong to any [databases](https://docs.deltastream.io/overview/core-concepts/databases "mention") and [#\_schema](https://docs.deltastream.io/overview/core-concepts/databases#_schema "mention") defined by the user in DeltaStream. Every application has a reserved database named `virtual` and a schema under it, named `public`, which DeltaStream manages and uses to store virtual relations' definitions in that application. You must always use a fully-qualified name when you define a new virtual relation or refer to an existing one in a query. For example, if `late_orders` is the name of a virtual relation, its definition (in CSAS) and usage (in a query) should always refer to it as `virtual.public.late_orders.`

Here is the syntax to define a virtual relation:

```sql
BEGIN APPLICATION application_name
...
CREATE VIRTUAL STREAM virtual.public.stream_name 
AS select_statement;
...
CREATE VIRTUAL CHANGELOG virtual.public.changelog_name 
AS select_statement;
...
END APPLICATION;
```

Virtual relations are for defining intermediate computation results used in one or more subsequent queries in the same application. They help simplify the computation logic. They also perform some common computation among queries only once and use it multiple times, at no extra cost.

## Examples

For the following examples, assume a stream named `pageviews` has been created using the below [create-stream](https://docs.deltastream.io/reference/sql-syntax/ddl/create-stream "mention") statement:

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

#### Application with multiple statements

In the example below, two new relations are created:

1. a [#\_stream](https://docs.deltastream.io/overview/core-concepts/databases#_stream "mention") named `pageviews_2`
2. a [#\_changelog](https://docs.deltastream.io/overview/core-concepts/databases#_changelog "mention") named `view_log`

Two queries start and both run in a single job. One query ingests data into `pageviews_2` and the other into `view_log`. Note that since both queries read data from the same source relation (such as `pageviews`), the application job reads `pageviews` records once from its entity and uses that for both queries. This improves the resource utilization. It also reduces the storage and network overhead for the store on which that `pageviews` is defined.

```sql
BEGIN APPLICATION app
  
  CREATE STREAM pageviews_2 AS 
    SELECT * FROM pageviews WHERE userid = 'User_2';
  
  CREATE CHANGELOG view_log AS
    SELECT pageid, count(userid) AS cnt
    FROM pageviews
    GROUP BY pageid;
    
END APPLICATION;
```

#### Application with multiple related statements

The example application below has 5 statements. New streams are defined, some of which are used in other queries in the same application. For example, the `INSERT INTO` statement reads data from `pv_copy,` which you define using a [create-stream-as](https://docs.deltastream.io/reference/sql-syntax/query/create-stream-as "mention") statement and write into `pageviews2`, which is in turn defined with another CSAS in the same application. (Note that the order of statements matter in application.) Therefore, statements defining `pv_copy` and `pageviews2` should appear before the `INSERT INTO` statement that uses them, in the application body. Moreover, the `users` stream is defined via a [create-stream](https://docs.deltastream.io/reference/sql-syntax/ddl/create-stream "mention") statement and is used in the JOIN query and last CSAS statement. Again, data records are read once from `pageviews` and `users` entities and are used in the queries that are referring to them in their FROM clause.

All the queries in the application run within a single job.

```sql
BEGIN APPLICATION app
  
  CREATE STREAM "users" (
    registertime BIGINT,
    userid VARCHAR,
    regionid VARCHAR,
    gender VARCHAR,
    interests ARRAY<VARCHAR>,
    contactinfo STRUCT<phone VARCHAR, city VARCHAR, "state" VARCHAR, zipcode VARCHAR>)
    WITH ('topic'='users', 'value.format'='json');

  CREATE STREAM pv_copy AS 
    SELECT * FROM pageviews;

  CREATE STREAM pageviews2 AS 
    SELECT userid, pageid FROM pageviews WHERE userid = 'User_2' OR userid = 'User_3';

  INSERT INTO pageviews2 SELECT userid, pageid FROM pv_copy;

  CREATE STREAM pvusers AS 
  SELECT p.userid AS pv_uid, u.gender, p.pageid, u.interests[1] AS top_interest  
  FROM pageviews p JOIN "users" u 
  WITHIN 10 seconds ON p.userid = u.userid;

  CREATE STREAM ca_users AS 
  SELECT userid, contactinfo->city, interests[2] AS hobby 
  FROM "users" WHERE contactinfo->state = 'CA';
    
END APPLICATION;
```

#### Application with virtual relations

In the example application below, we assume the `pageviews` stream is already defined in DeltaStream and we have a topic named `users` in our store. In this application, we first create a changelog on the `users` topic to track changes in users' information. Given that we are interested in pageviews done by 3 specific users, we can create a virtual stream: `virtual.public.v1` to filter records for those users from `pageviews` and use them in subsequent queries.

We use a Tumble window function on `virtual.public.v1`first to find pageviews done by these 3 users every 30 seconds. We write the results into a new topic `visit_freq`. Next we create a new virtual stream `virtual.public.v2` using a temporal join between `virtual.public.v1` and `users_log` changelog to extend each record with its user's information. We then use `virtual.public.v2` records in two subsequent queries:

* One query to find the `popular_pages` visited by female users among our users of interest.
* A second query to find pages visited by different users in a given duration and then enrich the results by latest information about the visiting users.

Note that although we have 5 CSAS statements in this application, only 3 new topics are created in the configured store:

1. `visit_freq`
2. `popular_pages`
3. `cross_region_pages`

Virtual relations' records do not persist in any topic and are only fed to subsequent queries that refer them.

```sql
BEGIN APPLICATION app

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

 CREATE VIRTUAL STREAM virtual.public.v1 AS 
   SELECT * FROM pageviews
   WHERE userid = 'User_1' OR userid = 'User_2' OR userid = 'User_3';
 
 CREATE CHANGELOG visit_freq WITH ('topic.partitions' = 1, 'topic.replicas' = 1) AS
  SELECT window_start, window_end, userid, count(pageid) AS cnt
  FROM TUMBLE(virtual.public.v1, SIZE 30 SECONDS)
  GROUP BY window_start, window_end, userid;

 CREATE VIRTUAL STREAM virtual.public.v2 AS
  SELECT v.userid, v.pageid, u.gender, u.interests[1] AS hobby, u.contactinfo->zipcode AS zipcode
   FROM virtual.public.v1 v JOIN users_log u
   ON u.userid = v.userid;

 CREATE CHANGELOG popular_pages WITH ('topic.partitions' = 1, 'topic.replicas' = 1) AS
  SELECT pageid, count(DISTINCT userid) AS cnt
  FROM virtual.public.v2
  WHERE gender = 'FEMALE'
  GROUP BY pageid
  HAVING count(DISTINCT userid) > 2;

  CREATE STREAM cross_region_pages WITH ('topic.partitions' = 1, 'topic.replicas' = 1) AS
   SELECT l.pageid,
    l.zipcode AS zipcode_1, l.hobby AS hobby_1,
    r.zipcode AS zipcode_2, r.hobby AS hobby_2
   FROM virtual.public.v2 l JOIN virtual.public.v2 r
   WITHIN 5 SECONDS ON l.pageid = r.pageid
   WHERE (l.userid <> r.userid);

END APPLICATION;
```