Data Stores allow you to save query results as a new data source. You can join data sources using multiple layers, and apply transformations to the data in the Data Pipeline to get your data exactly how you want it.
Stored Tables are query results that have been saved as a new data source. A Data Store is a collection of one or more Stored Tables.
You may want to group Stored Tables into separate Data Stores to keep permissions separate, or to keep them organized.
You can create foreign keys between Stored Tables that are within the same Data Store.
Uses for Data Stores
- Join multiple data sources
- Pre-aggregate data to improve query performance
- Create simplified, team-specific data sources
- Apply often-used transformations (calculations, filters) directly to data source
- Write SQL against Google Analytics and Salesforce data
You'll need to be an account Owner to create a Data Store. Granting users Admin access to a Data Store allows them to edit the Data Store settings and create and edit Stored Tables inside that Data Store. Data Store editors can view the schema and query log, as with other data sources.
Creating a Data Store
Creating a Data Store is a two part process. First you'll need to create the Data Store, then you can add Stored Tables to it.
Note: only account Owners can create new Data Stores.
From the top navigation, select Data Sources. Switch to the Data Stores tab, and click the +Add a Data Store button.
Name your Data Store and set other connection settings as desired.
Create a Stored Table
Permissions required: Data Source Admin
Now you're ready to add a Stored Table to your Data Store. From the Data Stores tab, find your Data Store and click the +Stored Table button.
You'll be redirected to the Data Explorer.
Select your data source, and then select data as you would for a chart. Add Layers and Data Pipeline steps as needed.
Editing Column namesWhen editing your Stored Table, be careful when changing column names - doing so will break any existing charts that are using the Stored Table.
If you are creating your Stored Table in SQL Mode, you'll need to use special syntax to apply a limit. At the end of your query, be sure to add:
This ensures that the Query Limit in your Stored Table settings (see below) is applied to your query.
Stored Table Settings
- Table Alias: The display name used for your Stored Table.
- SQL Name: This is the table name you'll use when using the Stored Table in a SQL Mode chart. It is auto-generated from the Table Alias. The SQL Name cannot be changed - this is to prevent breaking any charts that reference the SQL Name.
- Refresh Period: determines how often the query data is refreshed. If your query returns a lot of results or takes a long time to run, you may want to set your Refresh Period to weekly, or nightly.
- Refresh Interval setting: schedule when you would like your Stored Table query to fetch new data.
- Query Limit: limits the number of rows included in your Stored Table. The maximum is 1 million rows. The limit is applied to each layer, and a 1 million row limit is applied to the chart overall.
- Max Query Duration: optional setting that overrides the queried data source's maximum query duration.
A note on Query Duration
If you want to allow your Stored Table query additional time to run, set the Max Query Duration in your Stored Table settings.
The maximum possible query duration for Stored Tables is 30 minutes.
Using your Stored Table
Once you save your Stored Table, you'll be redirected to the full list of Data Stores and their Stored Tables. Your Stored Table will not run until its scheduled time, but if you want to use it immediately, you may manually refresh it here.
Once your Stored Table has finished loading, you can use it to create charts as you would any other data source.
If you would like to use your Stored Tables in SQL Mode, please note that they support PostgreSQL syntax.
Foreign keysTo use more than one Stored Table in the same query, add foreign keys between the two tables in its schema. The two Stored Tables will need to be inside the same Data Store. To access the Data Store's schema, click its name from the full list of Data Stores.
When data is added to a Data Store, it is saved in UTC. To convert the data to another time zone, use the Time Zone Support Data Store setting. This will convert the Data Store data in your chart queries to the desired time zone.
Data Stores Security
Data Stores are stored on Chartio's servers. We have implemented several features to ensure your data is secure.
- SSL encrypted connection
- Each Data Store is its own database, with its own unique user
- Data Stores are only accessible from our internal servers, and are not external-facing
- Upon disconnecting a Data Store, all tables and data are immediately deleted