CREATE TABLE [USING]
Applies to:
Databricks SQL
Databricks Runtime
Defines a managed or external table, optionally using a data source.
Syntax
{ { [CREATE OR] REPLACE TABLE | CREATE [EXTERNAL] TABLE [ IF NOT EXISTS ] }
table_name
[ table_specification ]
[ USING data_source ]
[ table_clauses ]
[ AS query ] }
table_specification
( { column_identifier column_type [ NOT NULL ]
[ GENERATED ALWAYS AS ( expr ) |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( [ START WITH start ] [ INCREMENT BY step ] ) ] |
DEFAULT default_expression ]
[ COMMENT column_comment ]
[ column_constraint ] } [, ...]
[ , table_constraint ] [...] )
table_clauses
{ OPTIONS clause |
PARTITIONED BY clause |
clustered_by_clause |
LOCATION path [ WITH ( CREDENTIAL credential_name ) ] |
COMMENT table_comment |
TBLPROPERTIES clause } [...]
clustered_by_clause
{ CLUSTERED BY ( cluster_column [, ...] )
[ SORTED BY ( { sort_column [ ASC | DESC ] } [, ...] ) ]
INTO num_buckets BUCKETS }
Parameters
REPLACE
If specified replaces the table and its content if it already exists. This clause is only supported for Delta Lake tables.
REPLACEpreserves the table history.Note
Azure Databricks strongly recommends using
REPLACEinstead of dropping and re-creating Delta Lake tables.EXTERNAL
If specified, creates an external table. When creating an external table you must also provide a
LOCATIONclause. When an external table is dropped the files at theLOCATIONwill not be dropped.IF NOT EXISTS
If specified and a table with the same name already exists, the statement is ignored.
IF NOT EXISTScannot coexist withREPLACE, which meansCREATE OR REPLACE TABLE IF NOT EXISTSis not allowed.-
The name of the table to be created. The name must not include a temporal specification. If the name is not qualified the table is created in the current schema.
table_specification
This optional clause defines the list of columns, their types, properties, descriptions, and column constraints.
If you do not define columns the table schema you must specify either
AS queryorLOCATION.-
A unique name for the column.
-
Specifies the data type of the column. Not all data types supported by Azure Databricks are supported by all data sources.
NOT NULL
If specified the column will not accept
NULLvalues. This clause is only supported for Delta Lake tables.GENERATED ALWAYS AS ( expr )
When you specify this clause the value of this column is determined by the specified
expr.exprmay be composed of literals, column identifiers within the table, and deterministic, built-in SQL functions or operators except:- Aggregate functions
- Analytic window functions
- Ranking window functions
- Table valued generator functions
Also
exprmust not contain any subquery.GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( [ START WITH start ] [ INCREMENT BY step ] ) ]
Applies to:
Databricks SQL
Databricks Runtime 10.3 and aboveDefines an identity column. When you write to the table, and do not provide values for the identity column, it will be automatically assigned a unique and statistically increasing (or decreasing if
stepis negative) value. This clause is only supported for Delta Lake tables. This clause can only be used for columns with BIGINT data type.The automatically assigned values start with
startand increment bystep. Assigned values are unique but are not guaranteed to be contiguous. Both parameters are optional, and the default value is 1.stepcannot be0.If the automatically assigned values are beyond the range of the identity column type, the query will fail.
When
ALWAYSis used, you cannot provide your own values for the identity column.The following operations are not supported:
- PARTITIONED BY an identity column
- UPDATE an identity column
Note
Declaring an identity column on a Delta table disables concurrent transactions. Only use identity columns in use cases where concurrent writes to the target table are not required.
DEFAULT default_expression
Applies to:
Databricks SQL SQL warehouse version 2022.35 or higher
Databricks Runtime 11.2 and aboveDefines a
DEFAULTvalue for the column which is used onINSERT,UPDATE, andMERGE ... INSERTwhen the column is not specified.If no default is specified
DEFAULT NULLis applied for nullable columns.default_expressionmay be composed of literals, and built-in SQL functions or operators except:- Aggregate functions
- Analytic window functions
- Ranking window functions
- Table valued generator functions
Also
default_expressionmust not contain any subquery.DEFAULTis supported forCSV,JSON,PARQUET, andORCsources.COMMENT column_comment
A string literal to describe the column.
-
Important
This feature is in Public Preview.
Adds a primary key or foreign key constraint to the column in a Delta Lake table.
Constraints are not supported for tables in the
hive_metastorecatalog.To add a check constraint to a Delta Lake table use ALTER TABLE.
-
Adds an informational primary key or informational foreign key constraints to the Delta Lake table.
Key constraints are not supported for tables in the
hive_metastorecatalog.To add a check constraint to a Delta Lake table use ALTER TABLE.
-
USING data_source
The file format to use for the table.
data_sourcemust be one of:TEXTAVROBINARYFILECSVJSONPARQUETORCDELTA
The following additional file formats to use for the table are supported in Databricks Runtime:
JDBCLIBSVM- a fully-qualified class name of a custom implementation of
org.apache.spark.sql.sources.DataSourceRegister.
If
USINGis omitted, the default isDELTA.For any
data_sourceother thanDELTAyou must also specify aLOCATIONunless the table catalog ishive_metastore.The following applies to: Databricks Runtime
HIVEis supported to create a Hive SerDe table in Databricks Runtime. You can specify the Hive-specificfile_formatandrow_formatusing theOPTIONSclause, which is a case-insensitive string map. Theoption_keysare:FILEFORMATINPUTFORMATOUTPUTFORMATSERDEFIELDDELIMESCAPEDELIMMAPKEYDELIMLINEDELIM
table_clauses
Optionally specify location, partitioning, clustering, options, comments, and user defined properties for the new table. Each sub clause may only be specified once.
-
An optional clause to partition the table by a subset of columns.
Note
Unless you define a Delta Lake table partitioning columns referencing the columns in the column specification are always moved to the end of the table.
clustered_by_clause
Optionally cluster the table or each partition into a fixed number of hash buckets using a subset of the columns.
Clustering is not supported for Delta Lake tables.
CLUSTERED BY
Specifies the set of columns by which to cluster each partition, or the table if no partitioning is specified.
-
An identifier referencing a
column_identifierin the table. If you specify more than one column there must be no duplicates. Since a clustering operates on the partition level you must not name a partition column also as a cluster column.
-
SORTED BY
Optionally maintains a sort order for rows in a bucket.
sort_column
A column to sort the bucket by. The column must not be partition column. Sort columns must be unique.
ASC or DESC
Optionally specifies whether
sort_columnis sorted in ascending (ASC) or descending (DESC) order. The default values isASC.
INTO num_buckets BUCKETS
An INTEGER literal specifying the number of buckets into which each partition (or the table if no partitioning is specified) is divided.
LOCATION path [ WITH ( CREDENTIAL credential_name ) ]
An optional path to the directory where table data is stored, which could be a path on distributed storage.
pathmust be a STRING literal. If you specify no location the table is considered amanaged tableand Azure Databricks creates a default table location.Specifying a location makes the table an external table.
For tables that do not reside in the
hive_metastorecatalog, the tablepathmust be protected by an external location unless a valid storage credential is specified.You cannot create external tables in locations that overlap with the location of managed tables.
For a Delta Lake table the table configuration is inherited from the
LOCATIONif data is present. Therefore, if anyTBLPROPERTIES,table_specification, orPARTITIONED BYclauses are specified for Delta Lake tables they must exactly match the Delta Lake location data.-
Sets or resets one or more user defined table options.
COMMENT table_comment
A string literal to describe the table.
-
Optionally sets one or more user defined properties.
-
AS query
This optional clause populates the table using the data from
query. When you specify aqueryyou must not also specify atable_specification. The table schema is derived from the query.Note that Azure Databricks overwrites the underlying data source with the data of the input query, to make sure the table gets created contains exactly the same data as the input query.
Examples
-- Creates a Delta table
> CREATE TABLE student (id INT, name STRING, age INT);
-- Use data from another table
> CREATE TABLE student_copy AS SELECT * FROM student;
-- Creates a CSV table from an external directory
> CREATE TABLE student USING CSV LOCATION '/mnt/csv_files';
-- Specify table comment and properties
> CREATE TABLE student (id INT, name STRING, age INT)
COMMENT 'this is a comment'
TBLPROPERTIES ('foo'='bar');
-- Specify table comment and properties with different clauses order
> CREATE TABLE student (id INT, name STRING, age INT)
TBLPROPERTIES ('foo'='bar')
COMMENT 'this is a comment';
-- Create partitioned table
> CREATE TABLE student (id INT, name STRING, age INT)
PARTITIONED BY (age);
-- Create a table with a generated column
> CREATE TABLE rectangles(a INT, b INT,
area INT GENERATED ALWAYS AS (a * b));
Related articles
Feedback
Submit and view feedback for