Caches contents of a table or output of a query with the given storage level. This reduces scanning of the original files in future queries.
CACHE TABLE statement caches contents of a table or output of a query with the given storage level. This reduces scanning of the original files in future queries.
CACHE [ LAZY ] TABLE table_name
[ OPTIONS ( 'storageLevel' [ = ] value ) ] [ [ AS ] query ]
LAZY Only cache the table when it is first used, instead of immediately.table_nameThe name of the table to be cached.
OPTIONS ( 'storageLevel' [ = ] value )OPTIONS clause with
storageLevel key and value pair. A Warning is issued when a key other than storageLevel is used. The valid options for
An Exception is thrown when an invalid value is set for
storageLevel is not explicitly set using
OPTIONS clause, the default
storageLevel is set to
query A query that produces the rows to be cached. It can be in one of following formats:
a SELECT statement
a TABLE statement
a FROM statement
CACHE TABLE testCache OPTIONS ('storageLevel' 'DISK_ONLY') SELECT * FROM testData;
UNCACHE TABLE removes the entries and associated data from the in-memory and/or on-disk cache for a given table or view. The underlying entries should already have been brought to cache by previous
CACHE TABLE operation.
UNCACHE TABLE on a non-existent table throws Exception if
IF EXISTS is not specified.
UNCACHE TABLE [ IF EXISTS ] table_name
table_name The name of the table or view to be uncached.
UNCACHE TABLE t1;
CLEAR CACHE removes the entries and associated data from the in-memory and/or on-disk cache for all cached tables and views.
REFRESH TABLE statement invalidates the cached entries, which include data and metadata of the given table or view. The invalidated cache is populated in lazy manner when the cached table or the query associated with it is executed again.
REFRESH [TABLE] tableIdentifier
tableIdentifier Specifies a table name, which is either a qualified or unqualified name that designates a table/view. If no database identifier is provided, it refers to a temporary view or a table/view in the current database.
-- The cached entries of the table will be refreshed
-- The table is resolved from the current database as the table name is unqualified.
REFRESH TABLE tbl1;
-- The cached entries of the view will be refreshed or invalidated
-- The view is resolved from tempDB database, as the view name is qualified.
REFRESH TABLE tempDB.view1;