CREATE-CATALOG
Name
CREATE CATALOG
Description
This statement is used to create an external catalog
Syntax:
CREATE CATALOG [IF NOT EXISTS] catalog_name
[WITH RESOURCE resource_name]
[PROPERTIES ("key"="value", ...)];
RESOURCE
can be created from CREATE RESOURCE, current supports:
- hms:Hive MetaStore
- es:Elasticsearch
- jdbc: Standard interface for database access (JDBC), currently supports MySQL and PostgreSQL
Create catalog
Create catalog through resource
In later versions of 1.2.0
, it is recommended to create a catalog through resource.
CREATE RESOURCE catalog_resource PROPERTIES (
'type'='hms|es|jdbc',
...
);
CREATE CATALOG catalog_name WITH RESOURCE catalog_resource PROPERTIES (
'key' = 'value'
);
Create catalog through properties
Version 1.2.0
creates a catalog through properties.
CREATE CATALOG catalog_name PROPERTIES (
'type'='hms|es|jdbc',
...
);
Example
Create catalog hive
-- 1.2.0+ Version
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 Version
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'
);
Create catalog es
-- 1.2.0+ Version
CREATE RESOURCE es_resource PROPERTIES (
"type"="es",
"hosts"="http://127.0.0.1:9200"
);
CREATE CATALOG es WITH RESOURCE es_resource;
-- 1.2.0 Version
CREATE CATALOG es PROPERTIES (
"type"="es",
"hosts"="http://127.0.0.1:9200"
);
Create catalog jdbc mysql
-- 1.2.0+ Version
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 Version
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
-- The first way
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;
-- The second way, note: keys have 'jdbc' prefix in front.
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
-- The first way
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;
-- The second way, note: keys have 'jdbc' prefix in front.
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
-- The first way
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;
-- The second way, note: keys have 'jdbc' prefix in front.
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