0001 ---
0002 layout: global
0003 title: CACHE TABLE
0004 displayTitle: CACHE TABLE
0005 license: |
0006 Licensed to the Apache Software Foundation (ASF) under one or more
0007 contributor license agreements. See the NOTICE file distributed with
0008 this work for additional information regarding copyright ownership.
0009 The ASF licenses this file to You under the Apache License, Version 2.0
0010 (the "License"); you may not use this file except in compliance with
0011 the License. You may obtain a copy of the License at
0012
0013 http://www.apache.org/licenses/LICENSE-2.0
0014
0015 Unless required by applicable law or agreed to in writing, software
0016 distributed under the License is distributed on an "AS IS" BASIS,
0017 WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
0018 See the License for the specific language governing permissions and
0019 limitations under the License.
0020 ---
0021
0022 ### Description
0023
0024 `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.
0025 This reduces scanning of the original files in future queries.
0026
0027 ### Syntax
0028
0029 ```sql
0030 CACHE [ LAZY ] TABLE table_identifier
0031 [ OPTIONS ( 'storageLevel' [ = ] value ) ] [ [ AS ] query ]
0032 ```
0033
0034 ### Parameters
0035
0036 * **LAZY**
0037
0038 Only cache the table when it is first used, instead of immediately.
0039
0040 * **table_identifier**
0041
0042 Specifies the table or view name to be cached. The table or view name may be optionally qualified with a database name.
0043
0044 **Syntax:** `[ database_name. ] table_name`
0045
0046 * **OPTIONS ( 'storageLevel' [ = ] value )**
0047
0048 `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:
0049 * `NONE`
0050 * `DISK_ONLY`
0051 * `DISK_ONLY_2`
0052 * `MEMORY_ONLY`
0053 * `MEMORY_ONLY_2`
0054 * `MEMORY_ONLY_SER`
0055 * `MEMORY_ONLY_SER_2`
0056 * `MEMORY_AND_DISK`
0057 * `MEMORY_AND_DISK_2`
0058 * `MEMORY_AND_DISK_SER`
0059 * `MEMORY_AND_DISK_SER_2`
0060 * `OFF_HEAP`
0061
0062 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`.
0063
0064 * **query**
0065
0066 A query that produces the rows to be cached. It can be in one of following formats:
0067 * a `SELECT` statement
0068 * a `TABLE` statement
0069 * a `FROM` statement
0070
0071 ### Examples
0072
0073 ```sql
0074 CACHE TABLE testCache OPTIONS ('storageLevel' 'DISK_ONLY') SELECT * FROM testData;
0075 ```
0076
0077 ### Related Statements
0078
0079 * [CLEAR CACHE](sql-ref-syntax-aux-cache-clear-cache.html)
0080 * [UNCACHE TABLE](sql-ref-syntax-aux-cache-uncache-table.html)
0081 * [REFRESH TABLE](sql-ref-syntax-aux-refresh-table.html)
0082 * [REFRESH](sql-ref-syntax-aux-cache-refresh.html)
