CACHE TABLE

Description

CACHE TABLE statement caches contents of a table or output of a query with the given storage level. If a query is cached, then a temp view will be created for this query. This reduces scanning of the original files in future queries.

Syntax

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


Parameters

• LAZY

Only cache the table when it is first used, instead of immediately.

• table_identifier

Specifies the table or view name to be cached. The table or view name may be optionally qualified with a database name.

Syntax: [ database_name. ] table_name

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