CACHE StatementsCData Power BI Connector for AWS Data Management 2019 - Build 19.0.7362
When caching is enabled, CACHE statements provide complete control over the data that is cached and the table to which it is cached. The CACHE statement executes the SELECT statement specified and caches its results to a table with the same name in the cache database or to table specified in <cached_table_name>. The connector updates or inserts rows to the cache depending on whether or not they already exist in the cache, so the primary key, which is used to identify existing rows, must be included in the selected columns.
This statement can be accessed using the Odbc.Query function in the M formula language.
See Caching Data for more information on different caching strategies.
CACHE Statement Syntax
The cache statement may include the following options that alter its behavior:
CACHE [ <cached_table_name> ] [ WITH TRUNCATE | AUTOCOMMIT | SCHEMA ONLY | DROP EXISTING | ALTER SCHEMA ] <select_statement>
If this option is set, the connector removes existing rows in the cache table before adding the selected rows. Use this option if you want to refresh the entire cache table but keep its existing schema.
If this option is set, the connector commits each row individually. Use this option if you want to ignore the rows that could not be cached due to some reason. By default, the entire result set is cached as a single transaction.
If this option is set, the connector drops the existing cache table before caching the new results. Use this option if you want to refresh the entire cache table, including its schema.
If this option is set, the connector creates the cache table based on the SELECT statement without executing the query.
If this option is set, the connector alters the schema of the existing table in the cache if it does not match the schema of the SELECT statement. This option results in new columns or dropped columns, if the schema of the SELECT statement does not match the cached table.
Use the following cache statement to cache all rows of a table:
CACHE SELECT * FROM NorthwindProducts
Use the following cache statement to cache all rows of a table into the cache table CachedNorthwindProducts:
CACHE CachedNorthwindProducts SELECT * FROM NorthwindProducts
Use the following cache statement for incremental caching. The DateModified column may not exist in all tables. The cache statement shows how incremental caching would work if there were such a column. Also, notice that, in this case, the WITH TRUNCATE and DROP EXISTING options are specifically omitted, which would have deleted all existing rows.
CACHE CachedNorthwindProducts SELECT * FROM NorthwindProducts WHERE DateModified > '2013-04-04'
Use the following cache statements to create a table with all available columns that will then cache only a few of them. The sequence of statements cache only PartitionKey and Name even though the cache table CachedNorthwindProducts has all the columns in NorthwindProducts.
CACHE CachedNorthwindProducts SCHEMA ONLY SELECT * FROM NorthwindProducts CACHE CachedNorthwindProducts SELECT PartitionKey, Name FROM NorthwindProducts