Skip to main content

Creating apps using templates

Templates can make creating Striim applications faster and easier. Apps created with templates may be modified using Flow Designer or by exporting TQL, editing it, and importing the modified TQL.

Striim has templates for many source-target combinations. Which are available in your installation will vary depending on which licenses you have (contact Striim support for more information).

When you use a template, the app's exception store is enabled automatically. If you do not want an exception store, turn it off after you create the app (see CREATE EXCEPTIONSTORE).CREATE EXCEPTIONSTORE

Use the Search Templates tool on the App Wizard page to see which templates are available for a particular source or target.

AutoSchemaConversion.png

Some initial load templates have Auto Schema Conversion, which allows you to automatically create schemas and tables in the target that correspond to those in the source. You must create the target database manually. In this release, tables can be created in the target only when Data type support & mapping for schema conversion & evolution includes mappings between all source and target column data types; other tables will be omitted.

Sources include

Targets include

  • Amazon S3

  • Azure Blob Storage

  • CosmosDB Reader

  • Google Cloud Storage

  • HDFS

  • Incremental Batch Reader

  • MariaDB Initial Load (using Database Reader)

  • MSJet (for initial load for MSJet, use SQL Server Initial Load)

  • MariaDB Initial Load (using Database Reader)

  • MongoDB CDC (using MongoDB Reader in Incremental mode)

  • MongoDB Initial Load (using MongoDB Reader in Initial Load mode)

  • MySQL CDC

  • MySQL Initial Load (using Database Reader)

  • Oracle CDC

  • Oracle Initial Load (using Database Reader)

  • OJet

  • PostgreSQL CDC

  • PostgreSQL Initial Load (using Database Reader)

  • Salesforce

  • Salesforce Pardot

  • SQL Server CDC

  • SQL Server Initial Load (using Database Reader)

  • Amazon Kinesis Data Streams

  • Amazon Redshift

  • Amazon S3

  • Apache Cassandra

  • Apache Hive - Cloudera

  • Apache Kudu

  • Azure Blob Storage

  • Azure Cosmos DB (after you configure the source and select schemas and tables, the wizard will prompt you to select the SQL, Cassandra, or MongoDB API)

  • Azure Data Lake Store Gen1

  • Azure Data Lake Store Gen2

  • Azure Event Hubs

  • Azure PostgreSQL

  • Azure SQL Database

  • Azure Synapse Analytics

  • BigQuery

  • Cloud SQL for MySQL

  • Cloud SQL for PostgreSQL

  • Cloud SQL for SQL Server

  • Database (see Database Writer for supported databases)

  • Databricks in AWS and Azure

    Note: Auto Schema Conversion is not supported when using Databricks' Unity Catalog.

  • File (using File Writer)

  • Google Cloud Storage

  • Hazelcast Writer

  • HBase

  • Hortonworks Hive

  • JMS

  • JPAWriter

  • Kafka 0.8.0, 0.9.0, 0.10.0, 0.11.0, 2.1.0

  • MapR Streams

  • MariaDB

  • MongoDB

  • MQTT

  • MySQL (using DatabaseWriter)

  • Oracle Database (using DatabaseWriter)

  • PostgreSQL (using DatabaseWriter)

  • SAP HANA (using DatabaseWriter)

  • ServiceNow

  • Singlestore (MemSQL)

  • Snowflake

  • Spanner

  • SQL Server (using DatabaseWriter)

  • Striim (app contains only the source)

App template prerequisite checklist

You will need the assistance of a database administrator for some of these tasks.

  1. Configure the source database as detailed in the relevant topic in Sources. You will need to provide the user name and password created during database configuration when configuring the source. If using an initial load template with Auto Schema Conversion, also assign the privileges listed for the source in Database privileges required for Auto Schema Conversion.

  2. Configure the target as detailed in the relevant topic in Targets. If using an initial load template with Auto Schema Conversion, also assign the privileges listed for the target in Database privileges required for Auto Schema Conversion.

  3. If the source cannot be read directly by Striim (for example, if Striim is running in AWS, Azure, or Google Cloud, and the source is on your premises):

    1. Install the Forwarding Agent and appropriate JDBC driver on the source host as detailed in Striim Forwarding Agent installation and configuration.

    2. Before launching a template, make sure the Forwarding Agent is running.

  4. If not using an initial load template with Auto Schema Conversion, create tables in the target that match the tables in the source using any tool you wish.

Database privileges required for Auto Schema Conversion

To use Auto Schema Conversion, a database administrator must assign privileges to the users or service accounts to be specified for the source and target connections using the following commands or procedures:

Azure Synapse - target only
USE <DATABASENAME>; EXEC sp_addrolemember 'db_owner', '<USERNAME>';
BigQuery - target only

Follow the instructions in Create a service account with the necessary BigQuery permissions to assign roles or permissions to the service account to be specified in the target properties.

Cloud Spanner - target only

Grant the Cloud Spanner Database User or higher role (see Cloud Spanner Roles) for the database to the service account to be specified in the target properties.

Databricks - target only
GRANT CREATE ON SCHEMA <SCHEMANAME> TO <USERNAME>;
MariaDB / MySQL - source

Create a user as described in MariaDB setup or MySQL setup.MySQL / MariaDB setup

Alternatively, assign the SELECT privilege:

GRANT SELECT ON *.* TO '<username>'@'<hostname>';
MariaDB / MySQL - target
GRANT CREATE ON *.* TO <USERNAME>@<HOSTNAME>;
Oracle - source

Create a user as described in Create an Oracle user with LogMiner privileges. or Running the OJet setup script on Oracle.

Alternatively, assign the following privileges:

grant CREATE SESSION to <username>;
grant SELECT ANY TABLE to <username>;
grant SELECT ANY DICTIONARY to <username>;
Oracle - target
GRANT CREATE SESSION TO <USERNAME>;
GRANT CREATE USER TO <USERNAME>;
GRANT CREATE ANY TABLE TO <USERNAME>;
GRANT CREATE ANY INDEX TO <USERNAME>;
GRANT UNLIMITED TABLESPACE TO <USERNAME>;
PostgreSQL - source

Create a user as described in PostgreSQL setup.

Alternatively, assign the following privileges:

GRANT CONNECT ON DATABASE <DATABASENAME> TO <USERNAME>;
GRANT SELECT ON <TABLENAMES> to <USERNAME>;
PostgreSQL - target
GRANT CONNECT ON DATABASE <DATABASENAME> TO <USERNAME>;
GRANT CREATE ON <TABLENAMES> to <USERNAME>;
Snowflake - target only
GRANT USAGE ON <DATABASE | WAREHOUSE | SCHEMA | TABLE> TO ROLE <USERNAME>;
SQL Server - source

Create a user as described in SQL Server setup.

Alternatively, give the user the db_owner role:

USE <DATABASENAME>;
EXEC sp_addrolemember 'db_owner', '<USERNAME>';
SQL Server - target
USE <DATABASENAME>; EXEC sp_addrolemember 'db_owner', '<USERNAME>';

Creating an application using a template

  1. Complete the App template prerequisite checklist.

  2. In the Striim web UI, select Apps > Create New.

  3. Find and click the template you want to use. If you don't see it, enter part of the source name in Search for templates, select the one you want, then do the same for the target.

  4. Enter a name for the app, select or enter the namespace in which you want to create it, and click Save.

Continue with Configuring an app template source.

Configuring an app template source

  1. Complete the steps in Creating an application using a template.

  2. Enter the host name, port, the user name and password for Striim, and any other properties required by the wizard. See the relevant reader properties reference in Sources or Change Data Capture (CDC) for more information.

  3. For an Incremental Batch Reader source, also specify the check column and start position as detailed in Incremental Batch Reader.

  4. If using a Forwarding Agent, set Deploy source on Agent on. (This option appears only when an agent is connected to Striim. (For file sources, this option does not appear, but the source components are put in their own flow which can be deployed on the agent.)

  5. Click Next. The wizard will connect to the database to validate the settings. If all the checks pass, click Next.

  6. If using a template with Auto Schema Conversion, continue with Using Auto Schema Conversion. Otherwise, select the tables to be read and click Next.

If your template's target is not to Striim, continue with Configuring an app template target.

If your template's target is to Striim, your new application will open in the Flow Designer.

Using Auto Schema Conversion

Note

Known issue (DEV-31163): when the target is Snowflake, you must create the schemas manually.

  1. Select the schemas you want to copy to the target, then click Next.

  2. If validation is successful, click Next. Otherwise, click Back and resolve any issues.

  3. For each schema, select the tables to copy. Any tables with data types that can not be mapped to the target (see Data type support & mapping for schema conversion & evolution) will be greyed out and can not be copied. When done, click Next.

Continue with Configuring an app template target.

Configuring an app template target

  1. Complete the steps in Configuring an app template source.Configuring an app template source

  2. Enter the appropriate properties. For more information, see the Targets entry for the selected target type.

    By default, the case of source table schema and table names will be preserved in the target. To avoid that, edit the Tables property and remove all double quotes.

    With a CDC source and a to Azure SQL Database, to Azure SQL Data Warehouse, or to Database target, for the Tables property, specify the source and target table names, separated by commas, separating each source-target pair with a semicolon: for example, dbo.Jobs,mydb.Jobs;dbo.Region,mydb.Region. Alternatively, use wildcards (see the discussion of the Tables property in Database Writer).

  3. If using a template with Auto Schema Conversion and you want to start initial load, click Migrate my schemas and move data. Otherwise, click Take me to Flow Designer.

    If using a template without Auto Schema Conversion, click Next. Your new application will open in the Flow Designer.

Before enabling recovery for a template-created app with a to Azure SQL DB or to Database target, follow the instructions for the target DBMS in Creating the checkpoint table.