create Hive External table

Establish mapping from ByteHouse table to Hive table at the table (Table) Dimension. Source table schema changes will No Sync directly. If there are changes, Delete the External table in ByteHouse and create a new External table.

create Hive External Database

Establish mapping from ByteHouse Database to Hive Database at the Database (Database) Dimension. After establishing the Database mapping, Manual table mapping is not required. Table mapping under the Database can be Automatic Sync, and Add or Delete Hive tables can be detected. Source table schema changes can be directly Sync to ByteHouse.

create Hive External Catalog

Establish mapping from ByteHouse Catalog to Hive Catalog at the data directory (Catalog) Dimension. After establishing the Catalog mapping, manual creation of database and table mappings is no longer required. The database and table mappings under the Catalog can be automatically synchronized, and additions or deletions of Hive databases and tables can be detected. Changes to the source table schema can be directly synchronized to ByteHouse. For operation details on creating a Hive external Catalog, please refer to Hive Catalog.

Table creation description

demo_db.hive_table: Creates a Hive external table in the specified ByteHouse database and supports custom table names.
When creating a table, the system will automatically map and create Hive table column names and types, as well as Partition By key, Cluster By key, and other information. Columns are created as nullable type by default, see the General reference: Column Type mappingsection below for column type mapping rules.

Engine parameters

• The engine type is fixed as CnchHive.
• thrift://hive_thrift_server_ip:port: Connection address for the Hive Metastore Service, where <hive_thrift_server_ip> is the IP address of the Hive Thrift Server, <port> is the port number. Refer to the official documentation of the Hive Service you use to obtain the connection address information. Example: thrift:// 101.xxx.xx.xxx:9083.
• default: Specifies the source database name in Hive to which the external table being created will be mapped.
• test_table: Specifies the source table name in Hive to which the external table being created will be mapped.

Settings parameters (SETTINGS)

• meta_type: Fixed as hive.
• Information about the S3-compatible object storage service used by Hive:
  • endpoint: Endpoint of the S3-compatible object storage.
  • ak_id: Access Key ID of the S3-compatible object storage.
  • ak_secret: Secret Access Key of the S3-compatible object storage.

Table creation description

demo_db.hive_las_table: Create a Hive external table in the specified Database of ByteHouse, supporting custom table names.
When creating the table, the system will automatically create the column names and types of the Hive table, as well as information such as Partition By key, Cluster By key, and others. Columns are created as nullable type by default, see the mapping rules for column types in the section below General reference: Column Type mappingchapter.

Engine parameters (Engine)

• Engine type is fixed as CnchHive.
• thrift:// lakeformation.las.region.ivolces.com:48869: Specifies the access domain name of the region where your LAS is located. You can use the content in the Endpoint column of the link address to fill in this parameter according to your region.
• default: Specifies the source database name in Hive to which the external table being created will be mapped.
• test_table: Specifies the source table name in Hive to which the external table being created will be mapped.

Settings parameters (SETTINGS)

• meta_type: Fixed as lf.
• Information about the TOS data storage used by LAS:
  • endpoint: Endpoint of TOS. For the method to obtain it, please refer to the S3 Endpoint column in region and access domain name, and fill in according to your actual region information.
  • ak_id: Access Key ID of TOS. For the method to obtain it, please refer to AccessKey (key) management.
  • ak_secret: Secret Access Key of TOS. For the method to obtain it, please refer to AccessKey (key) management.
• Authentication parameters for LAS:
  • lf_metastore_url: Specifies the access domain name of the region where your LAS is located. You can use the content in the Endpoint column of the link address to fill in this parameter according to your region.
  • lf_metastore_region: The region where LAS is located. For the method to obtain it, please refer to region and available zone selection.
  • lf_metastore_ak_id: Access Key ID of LAS. For the method to obtain it, please refer to AccessKey (key) management.
  • lf_metastore_ak_secret: Secret Access Key of LAS. For the method to obtain it, please refer to AccessKey (key) management.
  • lf_metastore_catalog = 'hive_catalog': Indicates that the external table being created is mapped to the data directory named hive_catalog in LAS. When using, modify the name to the actual data directory in Hive. For example, in BytePlus LAS, log in to the LAS Console, select Data Lake > Metadata management in the left navigation bar, and view and obtain the required data directory name.

DROP statement

(Optional) The following command is used to ensure that any old database with the same name is removed before creating a new database, to avoid creation conflicts. Use this command with caution in production environments.

Database description

hive_db: Create a Hive external database in ByteHouse, supports custom database name.

Engine parameters (Engine)

• The engine type is fixed as Hive.
• thrift://hive_thrift_server_ip:port: Connection address of the Hive metadata storage service (metastore), where <hive_thrift_server_ip> is the IP address of the Hive Thrift server and <port> is the port number. Refer to the official documentation of your Hive service to obtain the connection address information. Example: thrift:// 101.xxx.xx.xxx:9083.
• default: Specifies the source database name in Hive to which the currently created external database is mapped.

Settings parameters (SETTINGS)

• meta_type: Fixed as hive.
• Information about the S3-compatible object storage service used by Hive:
  • endpoint: Endpoint of the S3-compatible object storage.
  • ak_id: Access Key ID of the S3-compatible object storage.
  • ak_secret: Secret Access Key of the S3-compatible object storage.

DROP statement

(Optional) The following command is used to ensure that any old database with the same name is removed before creating a new database, to avoid creation conflicts. Use this command with caution in production environments.

Database description

hive_db_las: Create a Hive external database in ByteHouse, supports custom database name.

Engine parameters (Engine)

• The engine type is fixed as Hive.
• thrift:// lakeformation.las.region.ivolces.com:48869: Specifies the access domain name of the region where your LAS is located. You can fill in this parameter using the content in the Endpoint column of the link address according to your region.
• default: Specifies the source database name in Hive to which the currently created external table is mapped.

Settings parameters (SETTINGS)

• meta_type: Fixed as lf.
• Information about the data storage TOS (BytePlus object storage service) used by LAS:
  • endpoint: Endpoint of TOS. For the method to obtain it, refer to the S3 Endpoint column content in Region and access domain name, and fill in according to your actual region information.
  • ak_id: Access Key ID of TOS. For the method to obtain it, refer to AccessKey (key) management.
  • ak_secret: Secret Access Key of TOS. For the method to obtain it, refer to AccessKey (key) management.
• LAS authentication parameters:
  • lf_metastore_url: Specifies the access domain name for LAS in your region. You can fill in this parameter using the content in the Endpoint column of the URL based on your region.
  • lf_metastore_region: The region where LAS is located. For how to obtain it, see Region and Available Zone selection, and fill in based on your region.
  • lf_metastore_ak_id: The Access Key ID of LAS. For how to obtain it, see AccessKey (key) management.
  • lf_metastore_ak_secret: The Secret Access Key of LAS. For how to obtain it, see AccessKey (key) management.
  • lf_metastore_catalog = 'hive_catalog': Indicates that the created external database is mapped to the data directory named hive_catalog in LAS. When using, change to the actual data directory name in Hive. For example, in BytePlus LAS, you can log in to the LAS Console, select Data Lake > Metadata Management in the left navigation bar, and view and obtain the required data directory name.

Database and table description

database.table: Specifies the database and table name to query. Here, the database can be the ByteHouse database where only Hive external tables are created, or it can be a Hive external database.

Description of write parameters

• VALUES: Fill in the values to be inserted. The format is (val1, val2, ...), (val1, val2, ...),.... Examples are as follows:

  INSERT INTO demo_db.hive_las_table VALUES (1, 'a', '2021-07-27'), (2, 'b', '2021-07-27')
  

• SELECT: Write data using the result of a SELECT query. Fill in according to the syntax of the SELECT statement. Examples are as follows:

  # SELECT 源表中的所有数据插入目标表
  INSERT INTO demo_db.hive_las_table SELECT * FROM example_table
  
  # SELECT 源表中的指定数据插入目标表
  INSERT INTO demo_db.hive_las_table SELECT s,d,n FROM example_table
  

Database and table description

database.table: Specifies the database and table name to query. Here, the database can be the ByteHouse database where only Hive external tables are created, or it can be a Hive external database.

Description of write parameters

• VALUES: Fill in the values to be inserted. The format is (val1, val2, ...), (val1, val2, ...),.... Examples are as follows:

  INSERT OVERWRITE demo_db.hive_las_table VALUES (1, 'a', '2021-07-27'), (2, 'b', '2021-07-27')
  

• SELECT: Use the result of a SELECT query to write data. You can fill in the statement according to the syntax of the SELECT query. Examples are as follows:

  # SELECT 源表中的所有数据插入目标表
  INSERT OVERWRITE demo_db.hive_las_table SELECT * FROM example_table
  
  # SELECT 源表中的指定数据插入目标表
  INSERT OVERWRITE demo_db.hive_las_table SELECT s,d,n FROM example_table
  

bsp_mode

Bool

0 (off)

Set to 1 to enable BSP mode. After enabling BSP mode, queries are executed in stages. Shuffle connections are used between stages. Task-level retries are performed in the event of failure.

distributed_max_parallel_size

UInt64

Equal to the number of workers

When a single query uses a large amount of memory, increasing this parameter can increase the operator's degree of parallelism, reduce the amount of data processed by each parallel operator, and reduce memory usage per unit time. The value of distributed_max_parallel_size can exceed the number of workers only when BSP mode is enabled. It is recommended to set this parameter as a multiple of the number of workers.

bsp_max_retry_num

UInt64

The default value varies by version.

The maximum number of task retries. ByteHouse supports setting the bsp_max_retry_num parameter starting from version 2.0.4. The default values for different versions are as follows:
In ByteHouse 2.0.4/2.1, counted at the query granularity, the default is 5 times. In ByteHouse 2.2, counted at the segment granularity, the default is 3 times.
You can view the current ByteHouse version via the ByteHouse Console>Tenant Management>Basic Information path.

INT/INTEGER

Nullable INT/INTEGER

None

BIGINT

Nullable BIGINT

None

TIMESTAMP

Nullable DateTime

None

STRING

Nullable String

None

VARCHAR

Nullable FixedString

Internally converted to FixedString

CHAR

Nullable FixedString

Internally converted to FixedString

DOUBLE

Nullable DOUBLE

None

FLOAT

Nullable FLOAT

None

DECIMAL

Nullable DECIMAL

None

MAP

Nullable Map

None

ARRAY

Nullable Array

None