Create - CREATE-CATALOG - 《Apache Doris v1.2 中文文档》

CREATE-CATALOG

CREATE-CATALOG

Name

SinceVersion 1.2

CREATE CATALOG

Description

该语句用于创建外部数据目录（catalog）

语法：

CREATE CATALOG [IF NOT EXISTS] catalog_name
    [WITH RESOURCE resource_name]
    [PROPERTIES ("key"="value", ...)];

RESOURCE 可以通过 CREATE RESOURCE 创建，目前支持三种 Resource，分别连接三种外部数据源：

hms：Hive MetaStore
es：Elasticsearch
jdbc：数据库访问的标准接口(JDBC), 当前支持 MySQL 和 PostgreSQL

创建 catalog

通过 resource 创建 catalog

1.2.0 以后的版本推荐通过 resource 创建 catalog，多个使用场景可以复用相同的 resource。

CREATE RESOURCE catalog_resource PROPERTIES (
    'type'='hms|es|jdbc',
    ...
);
// 在 PROERPTIES 中指定的配置，将会覆盖 Resource 中的配置。
CREATE CATALOG catalog_name WITH RESOURCE catalog_resource PROPERTIES(
    'key' = 'value'
)

通过 properties 创建 catalog

1.2.0 版本通过 properties 创建 catalog。

CREATE CATALOG catalog_name PROPERTIES (
    'type'='hms|es|jdbc',
    ...
);

Example

新建数据目录 hive

-- 1.2.0+ 版本
CREATE RESOURCE hms_resource PROPERTIES (
    'type'='hms',
    'hive.metastore.uris' = 'thrift://127.0.0.1:7004',
    'dfs.nameservices'='HANN',
    'dfs.ha.namenodes.HANN'='nn1,nn2',
    'dfs.namenode.rpc-address.HANN.nn1'='nn1_host:rpc_port',
    'dfs.namenode.rpc-address.HANN.nn2'='nn2_host:rpc_port',
    'dfs.client.failover.proxy.provider.HANN'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider'
);
CREATE CATALOG hive WITH RESOURCE hms_resource;
-- 1.2.0 版本
CREATE CATALOG hive PROPERTIES (
    'type'='hms',
    'hive.metastore.uris' = 'thrift://127.0.0.1:7004',
    'dfs.nameservices'='HANN',
    'dfs.ha.namenodes.HANN'='nn1,nn2',
    'dfs.namenode.rpc-address.HANN.nn1'='nn1_host:rpc_port',
    'dfs.namenode.rpc-address.HANN.nn2'='nn2_host:rpc_port',
    'dfs.client.failover.proxy.provider.HANN'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider'
);

新建数据目录 es

-- 1.2.0+ 版本
CREATE RESOURCE es_resource PROPERTIES (
    "type"="es",
    "hosts"="http://127.0.0.1:9200"
);
CREATE CATALOG es WITH RESOURCE es_resource;
-- 1.2.0 版本
CREATE CATALOG es PROPERTIES (
    "type"="es",
    "hosts"="http://127.0.0.1:9200"
);

新建数据目录 jdbc mysql

-- 1.2.0+ 版本
CREATE RESOURCE mysql_resource PROPERTIES (
    "type"="jdbc",
    "user"="root",
    "password"="123456",
    "jdbc_url" = "jdbc:mysql://127.0.0.1:3316/doris_test?useSSL=false",
    "driver_url" = "https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/jdbc_driver/mysql-connector-java-8.0.25.jar",
    "driver_class" = "com.mysql.cj.jdbc.Driver"
);
CREATE CATALOG jdbc WITH RESOURCE msyql_resource;
-- 1.2.0 版本
CREATE CATALOG jdbc PROPERTIES (
    "type"="jdbc",
    "jdbc.user"="root",
    "jdbc.password"="123456",
    "jdbc.jdbc_url" = "jdbc:mysql://127.0.0.1:3316/doris_test?useSSL=false",
    "jdbc.driver_url" = "https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/jdbc_driver/mysql-connector-java-8.0.25.jar",
    "jdbc.driver_class" = "com.mysql.cj.jdbc.Driver"
);

postgresql

-- 方式一
CREATE RESOURCE pg_resource PROPERTIES (
    "type"="jdbc",
    "user"="postgres",
    "password"="123456",
    "jdbc_url" = "jdbc:postgresql://127.0.0.1:5432/demo",
    "driver_url" = "file:/path/to/postgresql-42.5.1.jar",
    "driver_class" = "org.postgresql.Driver"
);
CREATE CATALOG jdbc WITH RESOURCE pg_resource;
-- 方式二，注意有jdbc前缀
CREATE CATALOG jdbc PROPERTIES (
    "type"="jdbc",
    "jdbc.user"="postgres",
    "jdbc.password"="123456",
    "jdbc.jdbc_url" = "jdbc:postgresql://127.0.0.1:5432/demo",
    "jdbc.driver_url" = "file:/path/to/postgresql-42.5.1.jar",
    "jdbc.driver_class" = "org.postgresql.Driver"
);

clickhouse

-- 1.2.0+ Version
CREATE RESOURCE clickhouse_resource PROPERTIES (
    "type"="jdbc",
    "user"="default",
    "password"="123456",
    "jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo",
    "driver_url" = "file:///path/to/clickhouse-jdbc-0.3.2-patch11-all.jar",
    "driver_class" = "com.clickhouse.jdbc.ClickHouseDriver"
)
CREATE CATALOG jdbc WITH RESOURCE clickhouse_resource;
-- 1.2.0 Version
CREATE CATALOG jdbc PROPERTIES (
    "type"="jdbc",
    "jdbc.jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo",
    ...
)

oracle

-- 方式一
CREATE RESOURCE oracle_resource PROPERTIES (
    "type"="jdbc",
    "user"="doris",
    "password"="123456",
    "jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin",
    "driver_url" = "file:/path/to/ojdbc6.jar",
    "driver_class" = "oracle.jdbc.driver.OracleDriver"
);
CREATE CATALOG jdbc WITH RESOURCE oracle_resource;
-- 方式二，注意有jdbc前缀
CREATE CATALOG jdbc PROPERTIES (
    "type"="jdbc",
    "jdbc.user"="doris",
    "jdbc.password"="123456",
    "jdbc.jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin",
    "jdbc.driver_url" = "file:/path/to/ojdbc6.jar",
    "jdbc.driver_class" = "oracle.jdbc.driver.OracleDriver"
);

SQLServer

-- 方式一
CREATE RESOURCE sqlserver_resource PROPERTIES (
    "type"="jdbc",
    "user"="SA",
    "password"="Doris123456",
    "jdbc_url" = "jdbc:sqlserver://localhost:1433;DataBaseName=doris_test",
    "driver_url" = "file:/path/to/mssql-jdbc-11.2.3.jre8.jar",
    "driver_class" = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
);
CREATE CATALOG sqlserver_catlog WITH RESOURCE sqlserver_resource;
-- 方式二，注意有jdbc前缀
CREATE CATALOG sqlserver_catlog PROPERTIES (
    "type"="jdbc",
    "jdbc.user"="SA",
    "jdbc.password"="Doris123456",
    "jdbc.jdbc_url" = "jdbc:sqlserver://localhost:1433;DataBaseName=doris_test",
    "jdbc.driver_url" = "file:/path/to/mssql-jdbc-11.2.3.jre8.jar",
    "jdbc.driver_class" = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
);

Keywords

CREATE, CATALOG

CREATE-CATALOG

CREATE-CATALOG

Name

Description

创建 catalog

Example

Keywords

Best Practice