Data stores connect Appian to external relational databases in order to store and retrieve application data. This page covers how to create data stores, run DDL scripts, and retrieve external data in your application.
Data stores allow you to insert, update, query, and delete data in the format needed by your applications without writing structured queries (SQL). You must have a business data source configured to use this feature.
Custom data types are used by your processes to store data in an external relational database. The relationships between these custom data types are used to create tables and keys in the database that are associated with through the data store.
Once you define a custom data type and map it to a data store entity, you can update data in the database using the Write to Data Store Entity Smart Service. You can also use a!queryEntity() to retrieve information through the data store, without knowing the underlying table schemas of the database or SQL.
To create a data store, complete the following:
Configure the following properties:
Property | Description |
---|---|
Name | Enter a name that follows the recommended naming standard. |
Description | Enter a brief description. |
Data Source | Select a source from the available list. The sources in the list correspond to any relational databases configured for your environment. |
Once you've created the data store, you need to map your custom data types to the external database by adding entities to the data store.
To add an entity to the data store:
Once you've added at least one entity, you can publish the data store.
To publish the data store:
No matching tables found!
@Table
annotation of the XSD (which is lowercase by default), the entity will not be able to map to the table. If you encounter this issue, you can create a synonym in the Appian user schema that points to the tables in the other schema. Please note that the synonym will need to have the same name as the table or view that it points to.When creating or editing a data store entity, the system checks your RDBMS to see if the necessary data structures already exist.
When the data structures in your database do not match, you are given the option to create tables manually with a link to download the SQL needed to generate the necessary structure.
To create tables manually, complete the following:
<DATA_STORE_NAME> DDL.sql
<DATA_STORE_NAME> DDL_<MONTH_DAY_YEAR>_<VERSION_NUMBER>.sql
.Entity mappings verified
displays.All data store attributes can be edited by users with Editor or Administrator permissions. The following list details how to edit each attribute:
After making any of the above edits, you may either save a draft or publish a new version of the data store. To save a draft, click Save Draft. Saving a draft does not affect the environment or execute SQL DDL statements, but you may come back to the data store at any time to view and publish the changes. To publish a new version of the data store, follow these steps:
No matching tables found!
See also: Editing a Custom Data Type
Note: Take care when deleting a data store not to disrupt any active processes that query or write to the data store.
System administrators have the ability to delete data stores (and other objects) in bulk by selecting them and clicking Delete in the toolbar.
Tip: A user must have at least Viewer permissions to a data store in order to use its entities to query, write, or delete data.
The security role map of a data store controls which users can see or modify it and its properties. By default, only the data store creator and system administrators have access to the data store. See Editing Object Security to modify a data store's security.
The following table outlines the actions that can be completed for each permission level in a data store's security role map:
Actions | Administrator | Editor | Viewer | Deny |
---|---|---|---|---|
Retrieve entity to read/write at runtime* | Yes | Yes | Yes | No |
View the data store definition | Yes | Yes | Yes | No |
Update the data store definition | Yes | Yes | No | No |
View the security | Yes | Yes | No | No |
Update the security | Yes | No | No | No |
Save & Publish the data store | Yes | No | No | No |
Delete the data store | Yes | No | No | No |
*Users must have at least Viewer permissions to the data store in order to read or write data using the Write to Data Store smart service.
Use the following guidelines when loading data into a data store from an external RDBMS.
Minimize the number of individual queries to the database.
You can write arrays of data to your data store using the Write to Data Store Entity Smart Service, so there is no need to run multiple instances to write data to a single entity. To write multiple data types, you can use the Write to Multiple Data Store Entities Smart Service.
The amount of data that can be returned by a query is limited to 1MB by default. If you expect to return more than 1MB of data, you can design your process models to retrieve data in smaller batches and loop the process flow to retrieve the desired data.
When using a query to start processes, you may quickly reach the default limit of 1000 activated instances by repeatedly activating your Send Message Event and gateways (which do not provide the option to delete previously completed instances in the same manner as process flow activities).
You may want to configure your Send Message Event in a subprocess and configure the node properties to delete the completed instances when they are no longer needed.
You can also configure the other process activities to automatically delete previously completed instances to avoid reaching the 1000 node limit. This setting also reduces memory (RAM and HD) usage of your processes.
Data Store Object