0001 ---
0002 layout: global
0003 title: CREATE TABLE LIKE
0004 displayTitle: CREATE TABLE LIKE
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 the definition/metadata of an existing table or view.
0025
0026 ### Syntax
0027
0028 ```sql
0029 CREATE TABLE [IF NOT EXISTS] table_identifier LIKE source_table_identifier
0030 USING data_source
0031 [ ROW FORMAT row_format ]
0032 [ STORED AS file_format ]
0033 [ TBLPROPERTIES ( key1=val1, key2=val2, ... ) ]
0034 [ LOCATION path ]
0035 ```
0036
0037 ### Parameters
0038
0039 * **table_identifier**
0040
0041 Specifies a table name, which may be optionally qualified with a database name.
0042
0043 **Syntax:** `[ database_name. ] table_name`
0044
0045 * **USING data_source**
0046
0047 Data Source is the input format used to create the table. Data source can be CSV, TXT, ORC, JDBC, PARQUET, etc.
0048
0049 * **ROW FORMAT**
0050
0051 SERDE is used to specify a custom SerDe or the DELIMITED clause in order to use the native SerDe.
0052
0053 * **STORED AS**
0054
0055 File format for table storage, could be TEXTFILE, ORC, PARQUET, etc.
0056
0057 * **TBLPROPERTIES**
0058
0059 Table properties that have to be set are specified, such as `created.by.user`, `owner`, etc.
0060
0061 * **LOCATION**
0062
0063 Path to the directory where table data is stored, which could be a path on distributed storage like HDFS, etc. Location to create an external table.
0064
0065 ### Examples
0066
0067 ```sql
0068 -- Create table using an existing table
0069 CREATE TABLE Student_Dupli like Student;
0070
0071 -- Create table like using a data source
0072 CREATE TABLE Student_Dupli like Student USING CSV;
0073
0074 -- Table is created as external table at the location specified
0075 CREATE TABLE Student_Dupli like Student location '/root1/home';
0076
0077 -- Create table like using a rowformat
0078 CREATE TABLE Student_Dupli like Student
0079 ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
0080 STORED AS TEXTFILE
0081 TBLPROPERTIES ('owner'='xxxx');
0082 ```
0083
0084 ### Related Statements
0085
0086 * [CREATE TABLE USING DATASOURCE](sql-ref-syntax-ddl-create-table-datasource.html)
0087 * [CREATE TABLE USING HIVE FORMAT](sql-ref-syntax-ddl-create-table-hiveformat.html)
0088
