Skip to main content

Cache

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​

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.

Syntax​

CACHE [ LAZY ] TABLE table_name
    [ OPTIONS ( 'storageLevel' [ = ] value ) ] [ [ AS ] query ]

Parameters​

LAZY Only cache the table when it is first used, instead of immediately.tablenameThe 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 storageLevel are:

  • NONE
  • DISK_ONLY
  • DISK_ONLY_2
  • MEMORY_ONLY
  • MEMORY_ONLY_2
  • MEMORY_ONLY_SER
  • MEMORY_ONLY_SER_2
  • MEMORY_AND_DISK
  • MEMORY_AND_DISK_2
  • MEMORY_AND_DISK_SER
  • MEMORY_AND_DISK_SER_2
  • OFF_HEAP

An Exception is thrown when an invalid value is set for storageLevel. If storageLevel is not explicitly set using OPTIONS clause, the default storageLevel is set to MEMORY_AND_DISK.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

Examples​

CACHE TABLE testCache OPTIONS ('storageLevel' 'DISK_ONLY') SELECT * FROM testData;

Uncache Table​

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.

Syntax​

UNCACHE TABLE [ IF EXISTS ] table_name

Parameters​

table_name The name of the table or view to be uncached.

Examples​

UNCACHE TABLE t1;

Clear Cache​

CLEAR CACHE removes the entries and associated data from the in-memory and/or on-disk cache for all cached tables and views.

Syntax​

CLEAR CACHE

Examples​

CLEAR CACHE;

Refresh Tables​

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.

Syntax​

REFRESH [TABLE] tableIdentifier

Parameters​

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.

Syntax​

[database_name.]table_name

Examples​

-- 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;

ON THIS PAGE