CREATE-CATALOG

Name

CREATE CATALOG

Description

This statement is used to create an external catalog

Syntax:

1. CREATE CATALOG [IF NOT EXISTS] catalog_name
2. [WITH RESOURCE resource_name]
3. [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.

1. CREATE RESOURCE catalog_resource PROPERTIES (
2. 'type'='hms|es|jdbc',
3. ...
4. );
5. CREATE CATALOG catalog_name WITH RESOURCE catalog_resource PROPERTIES (
6. 'key' = 'value'
7. );

Create catalog through properties

Version 1.2.0 creates a catalog through properties.

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

Example

  1. Create catalog hive

``` 1. -- 1.2.0+ Version 2. CREATE RESOURCE hms_resource PROPERTIES ( 3. 'type'='hms', 4. 'hive.metastore.uris' = 'thrift://127.0.0.1:7004', 5. 'dfs.nameservices'='HANN', 6. 'dfs.ha.namenodes.HANN'='nn1,nn2', 7. 'dfs.namenode.rpc-address.HANN.nn1'='nn1_host:rpc_port', 8. 'dfs.namenode.rpc-address.HANN.nn2'='nn2_host:rpc_port', 9. 'dfs.client.failover.proxy.provider.HANN'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider' 10. ); 11. CREATE CATALOG hive WITH RESOURCE hms_resource;

  1. -- 1.2.0 Version
  2. CREATE CATALOG hive PROPERTIES (
  3. 'type'='hms',
  4. 'hive.metastore.uris' = 'thrift://127.0.0.1:7004',
  5. 'dfs.nameservices'='HANN',
  6. 'dfs.ha.namenodes.HANN'='nn1,nn2',
  7. 'dfs.namenode.rpc-address.HANN.nn1'='nn1_host:rpc_port',
  8. 'dfs.namenode.rpc-address.HANN.nn2'='nn2_host:rpc_port',
  9. 'dfs.client.failover.proxy.provider.HANN'='org.apache.hadoop.hdfs.server.namenode.ha.ConfiguredFailoverProxyProvider'
  10. ); ```
  11. Create catalog es

``` 1. -- 1.2.0+ Version 2. CREATE RESOURCE es_resource PROPERTIES ( 3. "type"="es", 4. "hosts"="http://127.0.0.1:9200" 5. ); 6. CREATE CATALOG es WITH RESOURCE es_resource;

  1. -- 1.2.0 Version
  2. CREATE CATALOG es PROPERTIES (
  3. "type"="es",
  4. "hosts"="http://127.0.0.1:9200"
  5. ); ```
  6. Create catalog jdbc mysql

``` 1. -- 1.2.0+ Version 2. CREATE RESOURCE mysql_resource PROPERTIES ( 3. "type"="jdbc", 4. "user"="root", 5. "password"="123456", 6. "jdbc_url" = "jdbc:mysql://127.0.0.1:3316/doris_test?useSSL=false", 7. "driver_url" = "https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/jdbc_driver/mysql-connector-java-8.0.25.jar", 8. "driver_class" = "com.mysql.cj.jdbc.Driver" 9. ); 10. CREATE CATALOG jdbc WITH RESOURCE msyql_resource;

  1. -- 1.2.0 Version
  2. CREATE CATALOG jdbc PROPERTIES (
  3. "type"="jdbc",
  4. "jdbc.user"="root",
  5. "jdbc.password"="123456",
  6. "jdbc.jdbc_url" = "jdbc:mysql://127.0.0.1:3316/doris_test?useSSL=false",
  7. "jdbc.driver_url" = "https://doris-community-test-1308700295.cos.ap-hongkong.myqcloud.com/jdbc_driver/mysql-connector-java-8.0.25.jar",
  8. "jdbc.driver_class" = "com.mysql.cj.jdbc.Driver"
  9. ); ```

postgresql

``` 1. -- The first way 2. CREATE RESOURCE pg_resource PROPERTIES ( 3. "type"="jdbc", 4. "user"="postgres", 5. "password"="123456", 6. "jdbc_url" = "jdbc:postgresql://127.0.0.1:5432/demo", 7. "driver_url" = "file:/path/to/postgresql-42.5.1.jar", 8. "driver_class" = "org.postgresql.Driver" 9. ); 10. CREATE CATALOG jdbc WITH RESOURCE pg_resource;

  1. -- The second way, note: keys have 'jdbc' prefix in front.
  2. CREATE CATALOG jdbc PROPERTIES (
  3. "type"="jdbc",
  4. "jdbc.user"="postgres",
  5. "jdbc.password"="123456",
  6. "jdbc.jdbc_url" = "jdbc:postgresql://127.0.0.1:5432/demo",
  7. "jdbc.driver_url" = "file:/path/to/postgresql-42.5.1.jar",
  8. "jdbc.driver_class" = "org.postgresql.Driver"
  9. ); ```

clickhouse

``` 1. -- 1.2.0+ Version 2. CREATE RESOURCE clickhouse_resource PROPERTIES ( 3. "type"="jdbc", 4. "user"="default", 5. "password"="123456", 6. "jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo", 7. "driver_url" = "file:///path/to/clickhouse-jdbc-0.3.2-patch11-all.jar", 8. "driver_class" = "com.clickhouse.jdbc.ClickHouseDriver" 9. ) 10. CREATE CATALOG jdbc WITH RESOURCE clickhouse_resource;

  1. -- 1.2.0 Version
  2. CREATE CATALOG jdbc PROPERTIES (
  3. "type"="jdbc",
  4. "jdbc.jdbc_url" = "jdbc:clickhouse://127.0.0.1:8123/demo",
  5. ...
  6. ) ```

oracle

``` 1. -- The first way 2. CREATE RESOURCE oracle_resource PROPERTIES ( 3. "type"="jdbc", 4. "user"="doris", 5. "password"="123456", 6. "jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin", 7. "driver_url" = "file:/path/to/ojdbc6.jar", 8. "driver_class" = "oracle.jdbc.driver.OracleDriver" 9. ); 10. CREATE CATALOG jdbc WITH RESOURCE oracle_resource;

  1. -- The second way, note: keys have 'jdbc' prefix in front.
  2. CREATE CATALOG jdbc PROPERTIES (
  3. "type"="jdbc",
  4. "jdbc.user"="doris",
  5. "jdbc.password"="123456",
  6. "jdbc.jdbc_url" = "jdbc:oracle:thin:@127.0.0.1:1521:helowin",
  7. "jdbc.driver_url" = "file:/path/to/ojdbc6.jar",
  8. "jdbc.driver_class" = "oracle.jdbc.driver.OracleDriver"
  9. ); ```

SQLServer

``` 1. -- The first way 2. CREATE RESOURCE sqlserver_resource PROPERTIES ( 3. "type"="jdbc", 4. "user"="SA", 5. "password"="Doris123456", 6. "jdbc_url" = "jdbc:sqlserver://localhost:1433;DataBaseName=doris_test", 7. "driver_url" = "file:/path/to/mssql-jdbc-11.2.3.jre8.jar", 8. "driver_class" = "com.microsoft.sqlserver.jdbc.SQLServerDriver" 9. ); 10. CREATE CATALOG sqlserver_catlog WITH RESOURCE sqlserver_resource;

  1. -- The second way, note: keys have 'jdbc' prefix in front.
  2. CREATE CATALOG sqlserver_catlog PROPERTIES (
  3. "type"="jdbc",
  4. "jdbc.user"="SA",
  5. "jdbc.password"="Doris123456",
  6. "jdbc.jdbc_url" = "jdbc:sqlserver://localhost:1433;DataBaseName=doris_test",
  7. "jdbc.driver_url" = "file:/path/to/mssql-jdbc-11.2.3.jre8.jar",
  8. "jdbc.driver_class" = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
  9. ); ```

Keywords

CREATE, CATALOG

Best Practice