0001 ---
0002 layout: global
0003 title: CREATE HIVEFORMAT TABLE
0004 displayTitle: CREATE HIVEFORMAT 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 The `CREATE TABLE` statement defines a new table using Hive format.
0025
0026 ### Syntax
0027
0028 ```sql
0029 CREATE [ EXTERNAL ] TABLE [ IF NOT EXISTS ] table_identifier
0030 [ ( col_name1[:] col_type1 [ COMMENT col_comment1 ], ... ) ]
0031 [ COMMENT table_comment ]
0032 [ PARTITIONED BY ( col_name2[:] col_type2 [ COMMENT col_comment2 ], ... )
0033 | ( col_name1, col_name2, ... ) ]
0034 [ ROW FORMAT row_format ]
0035 [ STORED AS file_format ]
0036 [ LOCATION path ]
0037 [ TBLPROPERTIES ( key1=val1, key2=val2, ... ) ]
0038 [ AS select_statement ]
0039 ```
0040
0041 Note that, the clauses between the columns definition clause and the AS SELECT clause can come in
0042 as any order. For example, you can write COMMENT table_comment after TBLPROPERTIES.
0043
0044 ### Parameters
0045
0046 * **table_identifier**
0047
0048 Specifies a table name, which may be optionally qualified with a database name.
0049
0050 **Syntax:** `[ database_name. ] table_name`
0051
0052 * **EXTERNAL**
0053
0054 Table is defined using the path provided as LOCATION, does not use default location for this table.
0055
0056 * **PARTITIONED BY**
0057
0058 Partitions are created on the table, based on the columns specified.
0059
0060 * **ROW FORMAT**
0061
0062 SERDE is used to specify a custom SerDe or the DELIMITED clause in order to use the native SerDe.
0063
0064 * **STORED AS**
0065
0066 File format for table storage, could be TEXTFILE, ORC, PARQUET, etc.
0067
0068 * **LOCATION**
0069
0070 Path to the directory where table data is stored, which could be a path on distributed storage like HDFS, etc.
0071
0072 * **COMMENT**
0073
0074 A string literal to describe the table.
0075
0076 * **TBLPROPERTIES**
0077
0078 A list of key-value pairs that is used to tag the table definition.
0079
0080 * **AS select_statement**
0081
0082 The table is populated using the data from the select statement.
0083
0084 ### Examples
0085
0086 ```sql
0087 --Use hive format
0088 CREATE TABLE student (id INT, name STRING, age INT) STORED AS ORC;
0089
0090 --Use data from another table
0091 CREATE TABLE student_copy STORED AS ORC
0092 AS SELECT * FROM student;
0093
0094 --Specify table comment and properties
0095 CREATE TABLE student (id INT, name STRING, age INT)
0096 COMMENT 'this is a comment'
0097 STORED AS ORC
0098 TBLPROPERTIES ('foo'='bar');
0099
0100 --Specify table comment and properties with different clauses order
0101 CREATE TABLE student (id INT, name STRING, age INT)
0102 STORED AS ORC
0103 TBLPROPERTIES ('foo'='bar')
0104 COMMENT 'this is a comment';
0105
0106 --Create partitioned table
0107 CREATE TABLE student (id INT, name STRING)
0108 PARTITIONED BY (age INT)
0109 STORED AS ORC;
0110
0111 --Create partitioned table with different clauses order
0112 CREATE TABLE student (id INT, name STRING)
0113 STORED AS ORC
0114 PARTITIONED BY (age INT);
0115
0116 --Use Row Format and file format
0117 CREATE TABLE student (id INT,name STRING)
0118 ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
0119 STORED AS TEXTFILE;
0120 ```
0121
0122 ### Related Statements
0123
0124 * [CREATE TABLE USING DATASOURCE](sql-ref-syntax-ddl-create-table-datasource.html)
0125 * [CREATE TABLE LIKE](sql-ref-syntax-ddl-create-table-like.html)
