Syntax

CREATE SCHEMA [IF NOT EXISTS] db_name 
with dbproperties (
  CATALOG = <catalog>,
  LOCATION = <endpoint>
)
Parameter Description
CATALOG The type of the database. Valid values:
  • hive
  • mysql
  • sqlserver
  • postgresql
  • oracle
  • ots
  • ads (ADB2.0)
  • adb3 (ADB3.0)
  • adbpg
  • mongodb
  • redis
  • elasticsearch
  • druid
  • kudu
  • odps
LOCATION The location information. Its format varies based on the schema type.

Syntax examples for creating different data sources

Create a hive database

CREATE DATABASE oss_log_schema with DBPROPERTIES(
  catalog='hive',
  location = 'oss://analyticdb-bucket/log/'
);

Create an ApsaraDB RDS for MySQL database

CREATE SCHEMA mysql_db WITH DBPROPERTIES (
  CATALOG = 'mysql', 
  LOCATION = 'jdbc:mysql://rm-2zer0vg58mfofake.mysql.rds.aliyuncs.com:3306/dla_test',
  USER = 'dla_test',
  PASSWORD = 'the-fake-password',
  VPC_ID = 'vpc-2zeij924vxd303kwifake',
  INSTANCE_ID = 'rm-2zer0vg58mfo5fake'
);
Parameter Description
VPC_ID The ID of the VPC to which the ApsaraDB RDS for MySQL instance belongs.
INSTANCE_ID The ID of the ApsaraDB RDS for MySQL instance.

Create an ApsaraDB RDS for SQL Server database

CREATE SCHEMA `sqlserver_db` WITH DBPROPERTIES 
( 
  CATALOG = 'sqlserver', 
  LOCATION = 'jdbc:sqlserver://rm-bp15g1r5jf90hfake.sqlserver.rds.aliyuncs.com:3433;DatabaseName=dla_test',
  USER='dla_test1',
  PASSWORD='this-is-not-a-real-password',
  INSTANCE_ID = 'rm-bp15g1r5jf90fake',
  VPC_ID = 'vpc-bp1adypqlcn535yrdfake'
);
Note LOCATION must include the database name. In this example, DatabaseName=dla_test indicates that the database name is dla_test. The database name is not defined by DLA but by the URL of the JDBC of the ApsaraDB RDS for SQL Server database.

Create a PostgreSQL database

CREATE SCHEMA `postgresql_db` WITH DBPROPERTIES 
( 
  CATALOG = 'postgresql', 
  LOCATION = 'jdbc:postgresql://rm-bp1oo49r6j3hvfake.pg.rds.aliyuncs.com:3433/dla_test',
  USER='dla_test',
  PASSWORD='this-is-not-a-real-password',
  INSTANCE_ID = 'rm-bp1oo49r6j3hfake',
  VPC_ID = 'vpc-bp1adypqlcn535yrfake'
);

Create an Oracle database

CREATE DATABASE `oracle_db`
WITH DBPROPERTIES (
    catalog = 'oracle',
    location = 'jdbc:oracle:thin:@//127.0.0.01:1521/XE',
    instance = 'HR',
    user = 'hello',
    password = 'world'
)

Create a Tablestore database

create database ots_db with dbproperties (
  catalog = 'ots',
  location = 'https://hz-tpch-1x-vol.cn-hangzhou.vpc.tablestore.aliyuncs.com',
  instance = 'hz-tpch-1x-vol'
);
Parameter Description
location The endpoint of the Tablestore database.
instance The name of the instance. You can obtain the instance name based on the DLA schema. This is because the endpoint information may not contain the instance name.

Create an AnalyticDB for MySQL V2.0 database

CREATE DATABASE `ads2_db`
WITH DBPROPERTIES (
    catalog = 'ads',
    location = 'jdbc:mysql://adb-xxx-vpc.cn-beijing-f.ads.aliyuncs.com:10001/xxx',
    instance = 'adb_tongguan_test_52265',
    user = 'user001',
    password = 'pass001'
)
Parameter Description
location The domain name and port number of the AnalyticDB for MySQL V2.0 server.
instance The instance of the AnalyticDB for MySQL V2.0 database.
user The username that is used to log on to the AnalyticDB for MySQL V2.0 database.
password The password that is used to log on to the AnalyticDB for MySQL V2.0 database.

Create an AnalyticDB for MySQL 3.0 database

CREATE DATABASE `adb3_db`
WITH DBPROPERTIES (
    catalog = 'adb3',
    location = 'jdbc:mysql://am-bp15huqy8t1118x4590650.ads.aliyuncs.com/adb3_it_db',
    user = 'dla_test',
    password = 'openanalytics@2018',
    vpc_id = 'vpc-bp1ll9szezdnbrob9auhs',
    instance_id = 'am-bp15huqy8t1118x45'
);
Parameter Description
location The domain name and port number of the AnalyticDB for MySQL 3.0 server.
user The username that is used to log on to the AnalyticDB for MySQL V3.0 database.
password The password that is used to log on to the AnalyticDB for MySQL V3.0 database.
vpc_id The ID of the VPC to which the AnalyticDB for MySQL V3.0 instance belongs.
instance_id The ID of the AnalyticDB for MySQL V3.0 instance.

Create an AnalyticDB for PostgreSQL database

 CREATE SCHEMA  dla_adbpg_test_db 
 WITH DBPROPERTIES (
     CATALOG = 'adbpg',
     LOCATION = 'jdbc:postgresql://gp-bp13******.gpdb.rds.aliyuncs.com:3432/db-name',
     USER = 'user-name',
     PASSWORD = 'password',
     INSTANCE_ID = 'gp-bp*******',
     VPC_ID = 'vpc-bp********'
 );
Parameter Description
location The domain name and port number of the AnalyticDB for PostgreSQL server.
user The username that is used to log on to the AnalyticDB for PostgreSQL database.
password The password that is used to log on to the AnalyticDB for PostgreSQL database.
instance_id The ID of the AnalyticDB for PostgreSQL instance.
vpc_id The ID of the VPC to which the AnalyticDB for PostgreSQL instance belongs.

Create an ApsaraDB for MongoDB database

CREATE DATABASE `mongo_test`
WITH DBPROPERTIES (
    catalog = 'mongodb',
    location = 'mongodb://<your-user-name>:<your-password>@dds-bp1694axxxxxxxx.mongodb.rds.aliyuncs.com:3717,dds-bp1694ayyyyyyyy.mongodb.rds.aliyuncs.com:3717/admin? replicaSet=zzzzz',
    database = 'mongo_test',
    vpc_id = 'vpc-aaaaaaa',
    instance_id = 'dds-bbbbbbb'
);
Parameter Description
location The connection string of the ApsaraDB for MongoDB database. The string includes the username, password, endpoint of the ApsaraDB for MongoDB database, and database used for authentication.
database The underlying database of the ApsaraDB for MongoDB database.
vpc_id The ID of the VPC to which the ApsaraDB for MongoDB instance belongs.
instance_id The ID of the ApsaraDB for MongoDB instance.

Create an ApsaraDB for Redis database

CREATE DATABASE `redis_db`
WITH DBPROPERTIES (
    catalog = 'redis',
    location = 'r-xxxxx.redis.rds.aliyuncs.com:6379/hello_',
    password = 'xxxxx',
    vpc_id = 'vpc-xxxxx',
    instance_id = 'r-xxxxxx'
)
Parameter Description
location The domain name and port number of the ApsaraDB for Redis server.
password The password that is used to log on to the ApsaraDB for Redis database.
vpc_id The ID of the VPC to which the ApsaraDB for Redis instance belongs.
instance_id The ID of the ApsaraDB for Redis instance.