Hive Catalog

Syntax

CREATE EXTERNAL CATALOG <catalog_name>
[COMMENT <comment>]
PROPERTIES
(
    "type" = "hive",                                    -- required
    GeneralParams,
    MetastoreParams
)

Parameters

Examples

The following examples create a Hive catalog named hive_catalog with HDFS as the storage system.

CREATE EXTERNAL CATALOG hive_catalog
PROPERTIES
(
    "type" = "hive",
    "hive.metastore.uris" = "thrift://xx.xx.xx.xx:9083"
);

CREATE EXTERNAL CATALOG hive_catalog
PROPERTIES
(
    "type" = "hive",
    "hive.metastore.type" = "dlf",
    "dlf.catalog.id" = "sr_dlf"
);

Syntax

CREATE TABLE [IF NOT EXISTS] [database.]table_name
(column_definition1[, column_definition2, ...
partition_column_definition1, partition_column_definition2...])
[partition_desc]
[PROPERTIES ("key" = "value", ...)]
[AS SELECT query]
[LIKE [database.]<source_table_name>]

Column definition

col_name col_type [COMMENT 'comment']

Supported data types: TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL, DATE, DATETIME, CHAR, VARCHAR[(length)], ARRAY, MAP, STRUCT

Unsupported data types: LARGEINT, HLL, BITMAP

All non-partition key columns default to NULL. Partition key columns must be declared last and cannot be NULL.

Partition definition

PARTITION BY (par_col1[, par_col2...])

Only identity transforms are supported, meaning one partition is created for each unique partition value.

Partition key columns support all data types except FLOAT, DOUBLE, DECIMAL, and DATETIME. The order of partition keys in PARTITION BY must match the order in the column definition.

Table properties

Examples

Create a non-partitioned table:

CREATE TABLE unpartition_tbl
(
    id int,
    score double
);

Create a partitioned table with id and dt as partition keys:

CREATE TABLE partition_tbl_1
(
    action varchar(20),
    id int NOT NULL,
    dt date NOT NULL
)
PARTITION BY (id, dt);

Create a table from a query result:

CREATE TABLE partition_tbl_2
PARTITION BY (id, dt)
AS SELECT * FROM partition_tbl_1;

Direct query

SELECT * FROM <catalog_name>.<database_name>.<table_name>;

Step-by-step query

1. List databases in the Hive catalog:

      SHOW DATABASES FROM <catalog_name>;

2. Switch to the target catalog and database. See View and switch catalogs.

3. Query the target table:

      SELECT count(*) FROM <table_name> LIMIT 10;

Import Hive data into StarRocks

Transform and load Hive data into a StarRocks internal table (for example, olap_tbl):

INSERT INTO default_catalog.olap_db.olap_tbl SELECT * FROM hive_table;

Syntax

INSERT {INTO | OVERWRITE} <table_name>
[ (column_name [, ...]) ]
{ VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }

-- Insert into a specific partition:
INSERT {INTO | OVERWRITE} <table_name>
PARTITION (par_col1=<value> [, par_col2=<value>...])
{ VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }

Partition key columns cannot be NULL. Make sure all partition keys have values during data import.

Parameters

Examples

Insert rows into partition_tbl_1:

INSERT INTO partition_tbl_1 VALUES
  ("buy", 1, "2023-09-01"),
  ("sell", 2, "2023-09-02"),
  ("buy", 3, "2023-09-03");

Insert a computed result with explicit column order:

INSERT INTO partition_tbl_1 (id, action, dt) SELECT 1+1, 'buy', '2023-09-03';

Insert from a self-referencing query:

INSERT INTO partition_tbl_1
SELECT 'buy', 1, date_add(dt, INTERVAL 2 DAY)
FROM partition_tbl_1
WHERE id = 1;

Insert into a specific partition of partition_tbl_2:

-- Method 1: Values match the partition implicitly.
INSERT INTO partition_tbl_2 SELECT 'order', 1, '2023-09-01';

-- Method 2: Specify the partition explicitly.
INSERT INTO partition_tbl_2 PARTITION(dt='2023-09-01', id=1) SELECT 'order';

Overwrite a partition in partition_tbl_1:

-- Method 1
INSERT OVERWRITE partition_tbl_1 SELECT 'close', 1, '2023-09-01';

-- Method 2
INSERT OVERWRITE partition_tbl_1 PARTITION(dt='2023-09-01', id=1) SELECT 'close';

Manual refresh

To force an immediate refresh after metadata changes, run:

REFRESH EXTERNAL TABLE <table_name> [PARTITION ('partition_name', ...)]

Use manual refresh when:

• Data files in an existing partition have changed (for example, after running INSERT OVERWRITE ... PARTITION ...).

• The Hive table schema has changed.

• A Hive table was dropped and recreated with the same name.

• New partitions were added in Hive while enable_cache_list_names is set to true in the catalog properties.

REFRESH EXTERNAL TABLE only updates tables and partitions already cached in the FE. A manual refresh is needed only when you query new partition data right after it is added.

Read support

StarRocks supports querying Hive data in the following file formats:

Unsupported data types for queries: INTERVAL, BINARY, UNION. For TextFile tables, MAP and STRUCT are also unsupported.

Write support

StarRocks supports writing data to Hive in the following file formats:

Set the compression algorithm using the connector_sink_compression_codec session variable.