CREATE DATABASE
Creates a database.
Syntax
CREATE DATABASE [IF NOT EXISTS] <database_name> [<sync_options>] [PARTITIONS n] [<create_specification>] [{FOR | INTO | ON} <object_store_settings>] <sync_options>: WITH {SYNC | ASYNC} DURABILITY | WITH {SYNC | ASYNC} REPLICATION | WITH SYNC DURABILITY SYNC REPLICATION | WITH ASYNC DURABILITY ASYNC REPLICATION <create_specification>: [DEFAULT] CHARACTER SET [=] charset_name <object_store_settings>: <S3_object_store_configuration> | <AZURE_object_store_configuration> | <GCS_object_store_configuration> <S3_object_store_configuration>: S3 { '<bucket-name>' | '<bucket-name/path>' } [CONFIG '<configuration_json>',<'{"verify_ssl":false|true}'>] CREDENTIALS '<credentials_json>' <AZURE_object_store_configuration>: AZURE { '<container-name>' | '<container-name/object-name>' | '<container-name/prefix/object-name>' } [CONFIG '<configuration_json>'] CREDENTIALS '<credentials_json>' <GCS_object_store_configuration>: GCS {'<bucket-name>' | '<bucket-name/path>' } [CONFIG '<configuration_json>'] CREDENTIALS '<credentials_json>' -- "schema" is an alias for "database" CREATE SCHEMA [IF NOT EXISTS]..
Remarks
database_name
is the name to assign to this new SingleStore database. Do not use the_XX
suffix (such astest_database_01
) while naming a SingleStoreDB database as it may cause a mismatch with the database partition ordinals (sharded databases on each leaf node).Database names are case-sensitive. Databases named
DB
anddb
are not the same objects and will be treated as such when granting permissions to users.CONFIG
andCREDENTIALS
can be specified in either order (CONFIG
followed byCREDENTIALS
orCREDENTIALS
followed byCONFIG
). For configuration examples refer BACKUP DATABASECONFIG '{"verify_ssl":false|true}'
allows you to connect to an unverified SSL certificate in S3 when using the optionverify_ssl:false
To create a local storage database, exclude the
{FOR | INTO | ON} <object_store_settings>
option. To create an unlimited storage database use the{FOR | INTO | ON} <object_store_settings>
option. Note thatFOR
,INTO
, andON
are synonyms and their functionality is identical.Prior to creating an unlimited storage database, the
enable_bottomless
engine variable must be set toON
(the default value). For more information on local storage and unlimited storage databases, see Local and Unlimited Database Storage Concepts.Unlimited storage databases cannot be created in the SingleStoreDB Free Edition.
WITH {SYNC | ASYNC} REPLICATION
specifies whether high availability, redundancy-2 replication will be done synchronously or asynchronously. Synchronous replication from the master partitions will complete on all replicas before the commit of the transaction is acknowledged to the client application. IfWITH {SYNC | ASYNC} REPLICATION
is not specified, synchronous replication is used.See Replication and Durability Concepts for more information.
WITH {SYNC | ASYNC} DURABILITY
specifies whether in-memory database updates you make using DDL and DML commands are also saved to the log on disk synchronously or asynchronously. Synchronous updates to the log on disk will complete before the commit of the transaction is acknowledged to the client application. IfWITH {SYNC | ASYNC} DURABILITY
is not specified, async durability is used.Note
SYNC DURABILITY
cannot be disabled once a database is created.See Replication and Durability Concepts for more information.
This command causes implicit commits. See COMMIT for more information.
You cannot specify
WITH SYNC DURABILITY ASYNC REPLICATION
.PARTITIONS n
allows you to set the total number of partitions that the data will be split into. By default this is controlled by thedefault_partitions_per_leaf
variable. Note thatn
refers to the total number of partitions across all leaves.The
CREATE DATABASE ... DEFAULT CHARSET=
syntax is accepted by SingleStore for compatibility with MySQL, but it has no effect. The database and its schema objects use the server character set and collation. Character set and collation defaults can be set during table creation. See CREATE TABLE for more information.To enable Ceph compatibility, set the
ceph_mode
flag totrue
in theCONFIG
:CREATE DATABASE db INTO S3 { '<bucket-name>' | '<bucket-name/path>' } CONFIG '{"ceph_mode":true}' CREDENTIALS '{<...>}';
This does not apply to the SingleStoreDB Free Edition since this edition does not support the creation of unlimited storage databases.
See the Permission Matrix for the required permission.
Examples
The following example creates a local database that uses all of the default options.
CREATE DATABASE IF NOT EXISTS test;
The following example creates a local database that uses sync durability and sync replication. The latter is enabled by default.
CREATE DATABASE IF NOT EXISTS test WITH SYNC DURABILITY;
The following example creates an unlimited storage database.
CREATE DATABASE bottomless_db ON S3 "bottomless_db_bucket/bottomless_db_folder"
CONFIG '{"region":"us-east-1", "verify_ssl":false}'
CREDENTIALS '{"aws_access_key_id":"your_access_key_id","aws_secret_access_key":"your_secret_access_key"}';