CREATE DATABASE

Description

Creates a database with the specified name. If database with the same name already exists, an exception will be thrown.

Syntax

  1. CREATE { DATABASE | SCHEMA } [ IF NOT EXISTS ] database_name
  2.     [ COMMENT database_comment ]
  3.     [ LOCATION database_directory ]
  4.     [ WITH DBPROPERTIES ( property_name = property_value [ , ... ] ) ]

Parameters

  • database_name

    Specifies the name of the database to be created.

  • IF NOT EXISTS

    Creates a database with the given name if it does not exist. If a database with the same name already exists, nothing will happen.

  • database_directory

    Path of the file system in which the specified database is to be created. If the specified path does not exist in the underlying file system, this command creates a directory with the path. If the location is not specified, the database will be created in the default warehouse directory, whose path is configured by the static configuration spark.sql.warehouse.dir.

  • database_comment

    Specifies the description for the database.

  • WITH DBPROPERTIES ( property_name=property_value [ , … ] )

    Specifies the properties for the database in key-value pairs.

Examples

  1. -- Create database `customer_db`. This throws exception if database with name customer_db
  2. -- already exists.
  3. CREATE DATABASE customer_db;
  5. -- Create database `customer_db` only if database with same name doesn't exist.
  6. CREATE DATABASE IF NOT EXISTS customer_db;
  8. -- Create database `customer_db` only if database with same name doesn't exist with 
  9. -- `Comments`,`Specific Location` and `Database properties`.
  10. CREATE DATABASE IF NOT EXISTS customer_db COMMENT 'This is customer database' LOCATION '/user'
  11.     WITH DBPROPERTIES (ID=001, Name='John');
  13. -- Verify that properties are set.
  14. DESCRIBE DATABASE EXTENDED customer_db;
  15. +-------------------------+--------------------------+
  16. |database_description_item|database_description_value|
  17. +-------------------------+--------------------------+
  18. |            Database Name|               customer_db|
  19. |              Description| This is customer database|
  20. |                 Location|     hdfs://hacluster/user|
  21. |               Properties|   ((ID,001), (Name,John))|
  22. +-------------------------+--------------------------+