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

  1. CACHE [ LAZY ] TABLE table_identifier
  2.     [ 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

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