ByteHouse cloud data warehouse edition supports integrating Hive Catalog. You do not need to manually create external databases or tables; you can query Hive data via Hive Catalog. This article will introduce how to create and use Hive Catalog.

When ByteHouse accesses the Hive cluster, it must have privileges to access the Hive metadata storage (metastore) service. Currently, ByteHouse supports the following metadata storage services:

• Standard Hive metastore
• LAS Formation from BytePlus AI Data Lake Service LAS

The method for creating Hive Catalog varies depending on the metadata storage service used; you can select the corresponding method according to your actual service. Additionally, ByteHouse also experimentally supports AWS Glue Datacatalog, which you can create as needed.

Please obtain the following privileges and connection information according to the metadata storage type used by your Hive.

To create an AWS Glue Catalog, obtain the following information in advance:

• Glue service endpoint.
• Region where Glue is located.
• Glue Catalog ID, which is a 12-digit AWS account name; for details, refer to AWS Account ID document.
• Key with Glue access privileges.
• Region of the AWS S3 storage bucket.
• AWS S3 service endpoint.
• Key AK/SK with AWS S3 access privileges.

Hive uses different metadata services, and the corresponding parameter configuration will also be different. This section will introduce the method for creating Hive Catalog by metadata service type.

S3 #

When data is stored in S3-compatible object storage, you can use the following command:

CREATE EXTERNAL CATALOG `{catalogName}`
properties
  type='hive',
  metastore_type='hive',
  hive_metastore_uri = 'thrift://hive_thrift_server_ip:port',
  endpoint = 's3_endpoint',
  ak_id = 's3_ak',
  ak_secret = 's3_sk';

LAS #

CREATE EXTERNAL CATALOG `{your_catalog_name}` 
PROPERTIES 
type='hive',
metastore_type='lf',
endpoint='tos-s3-{REGION}.ivolces.com',
ak_id='xxx',
ak_secret='xxx==',
lf_metastore_ak_id='xxx',
lf_metastore_ak_secret='xxx==',
lf_metastore_catalog='catalogName',
lf_metastore_region='region',
lf_metastore_url = 'thrift://lf_thrift_server_ip:port';

AWS Glue Catalog #

ByteHouse also experimentally supports AWS Glue Datacatalog.

CREATE EXTERNAL CATALOG `{your_glue_catalog_name}` 
PROPERTIES
type='glue',
    aws_glue_endpoint = 'glue_endpoint',
    aws_glue_region='glue_region',
    aws_glue_catalog_id='glue_catalog_id',
    aws_glue_ak_id = 'glue_ak',
    aws_glue_ak_secret = 'glue_sk',
    region= 's3_region',
    endpoint = 's3_endpoint',
    ak_id = 's3_ak',
    ak_secret = 's3_sk';

View all catalogs.

SHOW EXTERNAL CATALOGS;

View the schema information of a catalog.

SHOW CREATE EXTERNAL CATALOG catalog_name;

Suppose you have already created an external catalog named hive_s3.

Query tables in Hive Catalog #

Users can directly access tables in Hive Catalog using the three-part naming convention catalog_name.db_name.table_name, for example:

SELECT * FROM hive_s3.hive_db_name.hive_table_name;

ByteHouse native CnchMergeTree tables can also be accessed with the following SQL. Among them, cnch (abbreviation for cloud-native-clickhouse) is used as the default catalog name for ByteHouse CDW.

SELECT * FROM cnch.cnch_db_name.cnch_db_name;
-- Equivalent to SELECT * FROM cnch_db_name.cnch_db_name;

Cross-catalog query #

By using external catalogs, you can directly join Hive external tables and ByteHouse CnchMergeTree tables.

SELECT * FROM hive_s3.hive_db.hive_table 
UNION ALL 
SELECT (1) FROM cnch.cnch_db.cnch_table;

List databases and tables in Hive Catalog #

List database names in the catalog.

SHOW DATABASES FROM catalog_name;

List table names in the database.

SHOW TABLES FROM catalog_name.database_name;

Obtain the create statement of the table.

SHOW CREATE TABLE catalog_name.database_name.table_name;

Switch catalog #

You can use the following SQL to change the default catalog:

SWITCH CATALOG catalog_name;

At this point, when you run the query statement again, ByteHouse will read data from the specified database and table under the Hive Catalog.

SELECT * FROM hive_db.hive_table;

To switch back to the ByteHouse default Catalog, you can use any of the following commands:

# 方式 1
SWITCH CATALOG default;

# 方式 2
SWITCH CATALOG cnch;

You can also use the following command to directly switch the default database to the specified database under the Hive Catalog:

USE hive_s3.hive_db;

To switch back to ByteHouse internal tables, use the following command:

USE cnch.cnch_database_name;

Query the currently used Catalog #

You can use the currentCatalog() function to query the current Catalog.

SELECT currentCatalog();

Query Hive Catalog metadata #

You can use the following statements to query metadata of databases, tables, and partitions in the Hive Catalog, including database name, storage path, attributes, and so on.

• Query metadata of databases in the Hive Catalog

  SELECT * FROM catalog_name.database_name.__properties__
  

• Query metadata of tables in the Hive Catalog

  SELECT * FROM catalog_name.database_name.table_name$metadata$properties
  

• Query metadata of table partitions in the Hive Catalog

  SELECT * FROM {catalog_name.}database_name.table_name$metadata$partitions
  

You can use the following command to delete the Hive Catalog.

DROP EXTERNAL CATALOG [IF EXISTS] catalog_name;

Use Hive Catalog through the ByteHouse Cloud Data Warehouse Edition Console #

ByteHouse Cloud Data Warehouse Edition also supports creating and using Hive Catalog through the ByteHouse Cloud Data Warehouse Edition Console. For details, see Manage external catalog.

Use Hive external tables #

In addition to accessing Hive data through the Hive Catalog, ByteHouse also supports querying and writing Hive data through external tables. For details, see Hive external table.