E-MapReduce:Hive Catalog

Background

A Hive catalog lets you:

• Directly query Hive data without manually creating tables.

• Process and model Hive data and import it into StarRocks using INSERT INTO, or using asynchronous materialized views in v3.1 and later.

• Create or delete Hive databases and tables in StarRocks. You can also write data from StarRocks tables to Hive tables in Parquet (from v3.2), ORC, or TextFile (from v3.3) format using INSERT INTO.

To access data in Hive, the StarRocks cluster must be able to access the storage system and metadata service of the Hive cluster. StarRocks currently supports the following storage systems and metadata services:

• Hadoop Distributed File System (HDFS) or Alibaba Cloud Object Storage Service (OSS).

• Metadata services. Currently, supported metadata services include Data Lake Formation DLF 1.0 (Legacy) and Hive Metastore (HMS).

Limitations

• StarRocks supports querying Hive data in Parquet, ORC, TextFile, Avro, RCFile, and SequenceFile file formats:

  • Parquet files support the SNAPPY, LZ4, ZSTD, GZIP, and NO_COMPRESSION compression formats. Support for the LZO compression format was added in v3.1.5.

  • ORC files support ZLIB, SNAPPY, LZO, LZ4, ZSTD, and NO_COMPRESSION compression formats.

  • TextFile files support the LZO compression format from v3.1.5.

• StarRocks does not support querying Hive data of the INTERVAL, BINARY, or UNION data types. Additionally, for Hive tables in TextFile format, StarRocks does not support the MAP and STRUCT data types.

• StarRocks supports writing data to Hive in Parquet (v3.2 and later), ORC, and TextFile (v3.3 and later) file formats:

  • Parquet and ORC files support NO_COMPRESSION, SNAPPY, LZ4, ZSTD, and GZIP compression formats.

  • TextFile supports the NO_COMPRESSION format.

  You can set the compression algorithm for writing to Hive tables using the connector_sink_compression_codec system variable.

Create a Hive catalog

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

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"
);

View Hive catalogs

You can use SHOW CATALOGS to query all catalogs in the current StarRocks cluster.

SHOW CATALOGS;

You can also use SHOW CREATE CATALOG to query the creation statement of an external catalog. For example, run the following command to query the creation statement for the Hive catalog hive_catalog.

SHOW CREATE CATALOG hive_catalog;

Switch between Hive catalogs and databases​

You can switch to a target Hive catalog and database in the following ways:

• First, specify the Hive catalog for the current session with SET CATALOG. Then, specify the database with USE.

  -- Switch the catalog for the current session.
  SET CATALOG <catalog_name>;
  
  -- Specify the database for the current session.
  USE <db_name>;

• Use USE to directly switch the session to a specific database under the target Hive catalog.

  USE <catalog_name>.<db_name>;

Delete a Hive catalog​

You can use DROP CATALOG to delete an external catalog.

For example, run the following command to delete hive_catalog.

DROP Catalog hive_catalog;

Create a Hive database

Similar to the StarRocks internal catalog, if you have the CREATE DATABASE permission for a Hive catalog, you can create a database in that catalog using the CREATE DATABASE statement. This feature is supported from v3.2 onwards.

CREATE DATABASE <database_name>
[PROPERTIES ("location" = "<prefix>://<path_to_database>/<database_name.db>")]

Delete a Hive database

Similar to StarRocks internal databases, if you have the DROP permission for a Hive database, you can delete it using DROP DATABASE. This feature is supported from v3.2 onwards. You can only delete empty databases.

The delete database operation does not delete the corresponding file path on HDFS or Object Storage Service (OSS).

Switch to the target Hive catalog and run the following statement to delete a Hive database.

DROP DATABASE <database_name>;

Create a Hive table

Similar to StarRocks internal databases, if you have the CREATE TABLE permission for a Hive database, you can create a Managed Table in that database using CREATE TABLE, CREATE TABLE AS SELECT (CTAS), or CREATE TABLE LIKE.

This feature is supported from v3.2 onwards, which initially only supported creating Hive tables in Parquet format. From v3.3, this feature also supports creating Hive tables in ORC and TextFile formats.

Switch to the target Hive catalog and database, and then run the following statement to create a Hive Managed Table.

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>]

View Hive table schema

You can view the schema of a Hive table in the following ways:

• View the table schema

  DESC[RIBE] <catalog_name>.<database_name>.<table_name>;

• View the table schema and file location from the CREATE command

  SHOW CREATE TABLE <catalog_name>.<database_name>.<table_name>;

View Hive table data

• Method 1: Directly query table data

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

• Method 2: View and query step by step

  1. View the databases in the Hive cluster that belong to the specified catalog.

     SHOW DATABASES FROM <catalog_name>;

  2. Switch between Hive catalogs and databases.

  3. Query the data of the target table.

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

Import Hive data

Assume you have an OLAP table named olap_tbl. You can transform the data in this table and import it into StarRocks as follows.

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

Grant permissions on Hive tables and views​

You can use GRANT to grant a role the query permission on all tables and views within a Hive catalog. The command syntax is as follows.

GRANT SELECT ON ALL TABLES IN ALL DATABASES TO ROLE <role_name>

For example, run the following commands to create a role hive_role_table, switch to the Hive catalog hive_catalog, and then grant the query permission on all tables and views in hive_catalog to hive_role_table.

-- Create the role hive_role_table.
CREATE ROLE hive_role_table;

-- Switch to the data catalog hive_catalog.
SET CATALOG hive_catalog;

-- Grant the query permission on all tables and views in hive_catalog to hive_role_table.
GRANT SELECT ON ALL TABLES IN ALL DATABASES TO ROLE hive_role_table;

Insert data into a Hive table

Similar to StarRocks internal tables, if you have the INSERT permission for a Hive table (Managed Table or External Table), you can use INSERT to write data from a StarRocks table into that Hive table.

This feature is supported from v3.2 onwards, which initially only supported writing to Hive tables in Parquet format. From v3.3, this feature also supports writing to Hive tables in ORC and TextFile formats.

Note that the feature to write data to External Tables is disabled by default. You must enable it using the ENABLE_WRITE_HIVE_EXTERNAL_TABLE system variable.

Switch to the target Hive catalog and database, and then use the following syntax to write data from a StarRocks table into a Hive table.

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

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

Delete a Hive table

Similar to StarRocks internal tables, if you have the DROP permission for a Hive table, you can delete it using DROP TABLE. This feature is supported from v3.2 onwards. Note that currently, you can only delete Hive Managed Tables.

To delete a table, you must specify the FORCE keyword in the DROP TABLE statement. This operation deletes the table data on HDFS or Object Storage Service but does not delete the corresponding file path. Run this operation with caution.

Switch to the target Hive catalog and database, and then run the following statement to delete a Hive table.

DROP TABLE <table_name> FORCE

Manually or automatically update metadata cache

By default, StarRocks caches Hive metadata and automatically updates the cache in asynchronous mode to improve query performance. If you make schema changes or other updates to a Hive table, you can manually update its metadata using the following command to ensure that StarRocks can promptly generate efficient query plans.

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

Manual metadata updates are suitable for the following situations:

• Data files within an existing partition have changed, for example, after you run an INSERT OVERWRITE ... PARTITION ... command.

• The schema of the Hive table has changed.

• A Hive table was dropped and then a new Hive table with the same name was created.

• When creating a Hive catalog, you specified "enable_cache_list_names" = "true" in PROPERTIES. In this case, you must query new partitions after they are added on the Hive side.

References

For more information about Hive, see Overview.