< Rocky> how do i create a table in an external catalog StarRocks #questions-and-troubleshooting

Join Slack

<@U0A71G31CDV> how do i create a table in an exter...

# questions-and-troubleshooting

Ben Kimpel

04/28/2026, 4:32 AM

@Rocky how do i create a table in an external catalog

Rocky

04/28/2026, 4:32 AM

To create a table in an external catalog in StarRocks, you generally use the standard

CREATE TABLE

syntax, but you must first have an External Catalog created and then either switch to its context or use fully qualified names. Currently, StarRocks supports creating tables (writing metadata and data) primarily for Hive and Iceberg catalogs. Step 1: Create the External Catalog (if not already done) Before creating a table, you must create a catalog that connects StarRocks to your external metadata service (like Hive Metastore or AWS Glue).

Copy code

sql
-- Example: Create a Hive catalog
CREATE EXTERNAL CATALOG my_hive_catalog
PROPERTIES (
    "type" = "hive",
    "hive.metastore.uris" = "thrift://<hms_ip>:9083"
);

-- Example: Create an Iceberg catalog
CREATE EXTERNAL CATALOG my_iceberg_catalog
PROPERTIES (
    "type" = "iceberg",
    "iceberg.catalog.type" = "hive",
    "hive.metastore.uris" = "thrift://<hms_ip>:9083"
);

Step 2: Create the Table You can create the table by switching the catalog context or by using the

catalog.database.table

naming convention. Option A: Switch Context

Copy code

sql
SET CATALOG my_hive_catalog;
USE my_database;

CREATE TABLE my_new_table (
    id INT,
    name VARCHAR(50),
    dt DATE
)
ENGINE = hive  -- Specify the engine
PARTITION BY (dt)
PROPERTIES (
    "file_format" = "parquet",
    "compression_codec" = "snappy"
);

Option B: Fully Qualified Name

Copy code

sql
CREATE TABLE my_iceberg_catalog.my_db.my_iceberg_table (
    id INT,
    data VARCHAR(100),
    category INT
)
ENGINE = iceberg
PARTITION BY (category)
PROPERTIES (
    "location" = "<s3://my-bucket/path/to/table/>", -- Optional if DB has location
    "file_format" = "parquet"
);

Important Considerations 1. Supported Engines: * Iceberg: Supported from v3.1 onwards. * Hive: Supported for Parquet from v3.2, and for ORC/Textfile from v3.3 onwards. * Others: Catalogs like JDBC, Hudi, and Delta Lake are currently read-only. You cannot create tables in them via StarRocks; you must create them in the source system first. 2. Column Definitions: * All non-partition columns must be defined before partition columns. * Non-partition columns must allow

NULL

(or specify

DEFAULT "NULL"

). * Partition columns cannot use

NULL

as a default value. 3. Iceberg Hidden Partitions: For Iceberg, StarRocks supports advanced partitioning like

bucket(id, 10)

year(dt)

month(dt)

, etc. 4. Properties: * `location`: Required if the target database doesn't have a default location (common with AWS Glue). * `file_format`: Defaults to

parquet

. Hive also supports

orc

and

textfile

(v3.3+). 5. CTAS (Create Table As Select): You can also create external tables from existing data using

CREATE TABLE ... AS SELECT

Ben Kimpel

04/28/2026, 4:33 AM

@Rocky how do i create a database in an external catalog

Rocky

04/28/2026, 4:33 AM

Creating a database in an external catalog in StarRocks is supported for Hive and Iceberg catalogs. This allows you to manage the metadata of your external data lake directly from StarRocks. Prerequisites * Version: * Iceberg: Supported from v3.1 onwards. * Hive: Supported from v3.2 onwards. * Privileges: You must have the

CREATE DATABASE

privilege on the target external catalog. Step-by-Step Guide 1. Switch to the External Catalog You must first switch the session context to the target catalog before executing the

CREATE DATABASE

command.

Copy code

sql
SET CATALOG my_hive_catalog;

2. Create the Database The syntax is similar to creating a local database, but you can optionally specify a storage

location

Copy code

sql
CREATE DATABASE [IF NOT EXISTS] <database_name>
[PROPERTIES ("location" = "<prefix>://<path_to_database>/<database_name>.db")];

Examples Example 1: Hive (HDFS) If you are using Hive Metastore (HMS) on HDFS, the location is often optional as HMS will use its default warehouse path.

Copy code

sql
SET CATALOG hive_catalog;
CREATE DATABASE hive_db_test;

Example 2: Iceberg (AWS S3) When using AWS Glue as the metastore, specifying the

location

is required because Glue does not have a default warehouse path like HMS.

Copy code

sql
SET CATALOG iceberg_catalog;
CREATE DATABASE iceberg_db_test
PROPERTIES ("location" = "<s3://my-bucket/iceberg_data/iceberg_db_test.db/>");

Supported Storage Prefixes Depending on your storage system, use the following prefixes for the

location

property: * AWS S3:

s3://

* HDFS:

hdfs://

* Google GCS:

gs://

* Azure Storage:

wasb://

wasbs://

abfs://

, or

abfss://

Key Considerations 1. Read-Only Catalogs: You cannot create databases in catalogs such as JDBC (MySQL/PostgreSQL), Hudi, or Delta Lake via StarRocks. For these, the database must be created in the source system first. 2. Unified Catalog: If you use a Unified Catalog, you can also create Hive or Iceberg databases within it using the same syntax. 3. Metadata Sync: Once created, the database will be visible in both StarRocks and the external metastore (e.g., Hive Metastore or Glue). References • data_source/catalog/hive_catalog.md • data_source/catalog/iceberg/DDL.md • data_source/catalog/unified_catalog.md • sql-reference/sql-statements/Database/CREATE_DATABASE.md References • sql-reference/sql-statements/Database/CREATE_DATABASE.md • data_source/catalog/unified_catalog.md • data_source/catalog/hive_catalog.md • sql-reference/sql-statements/Catalog/CREATE_EXTERNAL_CATALOG.md

Ben Kimpel

04/28/2026, 4:49 AM

@Rocky starrocks failed to load rest catalog

Rocky

04/28/2026, 4:50 AM

If StarRocks fails to load a REST catalog (typically for Apache Iceberg), it is usually due to an authentication handshake failure, a malformed URI, or a specific version bug. Here are the most common causes and how to fix them: 1. Check Authentication Configuration Most REST catalogs (like Polaris, Tabular, or Gravitino) require either OAuth2 or JWT. * OAuth2 Client Credentials: Ensure you have both the credential and the scope defined.

Copy code

sql
    PROPERTIES (
        "iceberg.catalog.type" = "rest",
        "iceberg.catalog.uri" = "http://<host>:8181/api/catalog",
        "iceberg.catalog.security" = "oauth2",
        "iceberg.catalog.oauth2.credential" = "client_id:client_secret",
        "iceberg.catalog.oauth2.scope" = "catalog" -- Often required by Polaris
    )

* JWT Passthrough: If you are using JWT, StarRocks must be configured with a security integration, and the catalog must have

"iceberg.catalog.security" = "jwt"

. 2. Known Bug: OAuth2 Scope (Polaris) There is a known issue in some StarRocks versions where the OAuth2 token request is malformed (missing or incorrectly formatting the

scope

parameter). * Symptoms:

Failed to load rest catalog

fe.log

during the authentication handshake. * Workaround: If you are using Apache Polaris, try setting

"iceberg.catalog.oauth2.scope" = "PRINCIPAL_ROLE:ALL"

or ensuring your StarRocks version is up to date (v3.3.2+ is recommended). 3. Missing Warehouse Property Unlike Hive catalogs, REST catalogs often require an explicit warehouse identifier or path to initialize correctly. * Fix: Add

"iceberg.catalog.warehouse" = "<warehouse_name_or_path>"

to your properties. For Polaris, this is typically the name of the catalog you created in the Polaris UI. 4. Version-Specific "Location" Error In versions prior to 3.3.2, StarRocks would sometimes fail to load a REST catalog database with the error:

ERROR 1064 (HY000): Database <name> doesn't exist location

. * Fix: This was a bug where StarRocks strictly checked for a metadata location that REST catalogs don't always provide. Upgrade to v3.3.2 or later to resolve this. 5. Network and URI Formatting * Trailing Slashes: Some REST servers are sensitive to the URI. Try the URI with and without the

/api/catalog

suffix depending on your provider’s documentation (e.g.,

<http://localhost:8181>

vs `http://localhost:8181/api/catalog`). * Host Resolution: Ensure the StarRocks FE node can resolve the hostname of the REST server. Check

/etc/hosts

if you are using custom internal domains. 6. How to Debug (The
fe.log
) To find the exact reason for the failure, check the FE leader's log file: 1. Navigate to your StarRocks installation:

cd starrocks/fe/log

. 2. Search for "IcebergRESTCatalog":

Copy code

bash
    grep -i "IcebergRESTCatalog" fe.log -A 20

3. Common error messages in logs: * `Unauthorized (401)`: Wrong credentials or scope. * `Connection Refused`: Network/Firewall issue. * `Malformed database URL`: The

iceberg.catalog.uri

format is incorrect. Summary Checklist * [ ] Does the

iceberg.catalog.uri

include the correct port and path? * [ ] If using OAuth2, is the

scope

property defined? * [ ] Is the

iceberg.catalog.warehouse

name correct? * [ ] Are you on StarRocks v3.3.2+? (Highly recommended for REST catalog stability). References • data_source/catalog/iceberg/iceberg_rest_security.md • data_source/catalog/jdbc_catalog.md • data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/iceberg/iceberg_rest_s3.md • faq/loading/Loading_faq.md

Ben Kimpel

04/28/2026, 4:23 PM

@Rocky why am i getting this error when trying to create a table through polaris catalog

Ben Kimpel

04/28/2026, 4:24 PM

@Rocky why am i getting this error when trying to create a table through polaris catalog: Failed to create table using REST catalog, exception: Forbidden: Principal 'root' with activated PrincipalRoles '[service_admin]' and activated grants via '[service_admin, catalog_admin]' is not authorized for op CREATE_TABLE_DIRECT_WITH_WRITE_DELEGATION

Rocky

04/28/2026, 4:24 PM

When using Apache Polaris (an Iceberg REST catalog) with StarRocks, table creation errors are typically caused by specific authentication requirements or missing metadata properties. Here are the most common reasons and how to fix them: 1. Missing or Incorrect OAuth2 Scope Polaris requires a specific

scope

property when authenticating via OAuth2. If this is missing, StarRocks can connect but may fail to perform write operations like

CREATE TABLE

. * Fix: Add the scope property to your catalog definition.

Copy code

sql
PROPERTIES (
    ...
    "iceberg.catalog.oauth2.scope" = 'PRINCIPAL_ROLE:ALL'
)

2. Missing Warehouse Identifier The Iceberg REST specification requires a

warehouse

identifier. In Polaris, this is the name of the catalog you created in the Polaris UI. Without this, StarRocks won't know where to route the table creation request. * Fix: Ensure

iceberg.catalog.warehouse

matches your Polaris catalog name.

Copy code

sql
PROPERTIES (
    ...
    "iceberg.catalog.warehouse" = "my_polaris_catalog_name"
)

3. Missing Privileges in Polaris Even if you are the "root" user in Polaris, the Principal Role you are using must be explicitly granted the

TABLE_CREATE

privilege on the specific namespace (database). * Check: In the Polaris UI or CLI, ensure your role has

TABLE_CREATE

CATALOG_MANAGE

permissions for the target namespace. 4. Vended Credentials Configuration Polaris often uses Credential Vending (providing temporary S3/GCS/Azure tokens to StarRocks). If your Polaris catalog has this enabled, StarRocks must be configured to accept them. * Fix: Ensure this property is set to

true

(it is

true

by default in recent versions):

Copy code

sql
PROPERTIES (
    ...
    "iceberg.catalog.vended-credentials-enabled" = "true"
)

_ _Note: If you are NOT using vended credentials, you must provide the standard cloud credentials (e.g.,

aws.s3.access_key

) in the StarRocks catalog properties.* 5. Nested Namespace Support If you are trying to create a table in a nested namespace (e.g.,

catalog.parent_ns.child_ns.table

), you must explicitly enable nested namespace support. * Fix:

Copy code

sql
PROPERTIES (
    ...
    "iceberg.catalog.rest.nested-namespace-enabled" = "true"
)

6. Version-Specific OAuth Bug In some StarRocks versions (prior to v3.3.2), there was a known issue where the OAuth2 token request sent the

scope

parameter in a format that Polaris rejected. * Fix: Upgrade to StarRocks v3.3.2 or later. Troubleshooting Step: Check the FE Log If the error in your SQL client is generic (e.g.,

Internal Error

), the real reason is in the StarRocks Frontend (FE) log: 1. Check

fe/log/fe.warn.log

fe.log

. 2. Search for

IcebergRESTCatalog

. 3. Common log errors: * `403 Forbidden`: Polaris permissions issue. * `401 Unauthorized`: OAuth2 credentials or scope issue. * `400 Bad Request`: Missing

warehouse

or malformed REST URI. Example of a Working Polaris Catalog Definition:

Copy code

sql
CREATE EXTERNAL CATALOG polaris_prod
PROPERTIES (
    "type" = "iceberg",
    "iceberg.catalog.type" = "rest",
    "iceberg.catalog.uri" = "http://<polaris-host>:8181/api/catalog",
    "iceberg.catalog.warehouse" = "default_warehouse",
    "iceberg.catalog.security" = "oauth2",
    "iceberg.catalog.oauth2.credential" = "<client_id>:<client_secret>",
    "iceberg.catalog.oauth2.scope" = 'PRINCIPAL_ROLE:ALL',
    "iceberg.catalog.vended-credentials-enabled" = "true"
);

References • data_source/catalog/iceberg/DDL.md • data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/iceberg/iceberg_rest_s3.md

Rocky

04/28/2026, 4:24 PM

This error occurs because of a permission mismatch in Apache Polaris regarding how storage credentials are "vended" (delegated) to StarRocks during a table creation request. Why it happens When StarRocks creates a table in a Polaris catalog, it usually requests Vended Credentials. This means StarRocks asks Polaris for temporary S3/GCS/Azure tokens to write the initial metadata files. In Polaris, the operation

CREATE_TABLE_DIRECT_WITH_WRITE_DELEGATION

requires more than just the

TABLE_CREATE

privilege. It specifically requires the CATALOG_MANAGE_CONTENT
privilege. By default, even the Polaris

root

user or

catalog_admin

role might not have this "Super Privilege" explicitly granted for this specific delegation flow. The Fix: Grant
CATALOG_MANAGE_CONTENT
in Polaris You need to grant the

CATALOG_MANAGE_CONTENT

privilege to your catalog role in Polaris. You can do this using the Polaris CLI or the REST Management API. Run the following

curl

command (replace placeholders with your actual values):

Copy code

bash
# 1. Get your Polaris Token (if not already set)
# 2. Grant the CATALOG_MANAGE_CONTENT privilege to your admin role
curl -X PUT "http://<polaris-host>:8181/api/management/v1/catalogs/<your_catalog_name>/catalog-roles/<your_admin_role_name>/grants" \
  -H "Authorization: Bearer $POLARIS_TOKEN" \
  -H "Content-Type: application/json" \
  -d '{
    "grant": {
      "type": "catalog",
      "privilege": "CATALOG_MANAGE_CONTENT"
    }
  }'

* <your_catalog_name>
: The name of the catalog in Polaris. * <your_admin_role_name>
: Usually

catalog_admin

or the specific role assigned to your

root

principal. Alternative Fix: Manual Credentials (If you don't want Vended Credentials) If you do not want to use Polaris to manage storage tokens and prefer to provide them directly to StarRocks, you can disable vended credentials in the StarRocks catalog properties. However, you must then provide the cloud access keys yourself. 1. Set

"iceberg.catalog.vended-credentials-enabled" = "false"

in your StarRocks catalog. 2. Add your storage credentials to the catalog (e.g.,

"aws.s3.access_key" = "..."

). Summary of required privileges for Polaris + StarRocks To ensure full functionality (Create, Drop, Select, Insert), your Polaris Principal Role should have: 1.

TABLE_CREATE

TABLE_WRITE_DATA

3. CATALOG_MANAGE_CONTENT
(Required for the vended credentials handshake during table creation) References • data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/unified_catalog.md • data_source/catalog/deltalake_catalog.md

Open in Slack

Previous Next