ALTER TABLE_SERVICE

Description

Modifies table service configurations for an existing Onehouse table.

Note that the SQL statement does not end with ;

Syntax

ALTER TABLE_SERVICE 
  LAKE = <string>
  DATABASE = <string>
  TABLE  = <string>
  SERVICE = { 'CLUSTER' | 'COMPACT' | 'CLEAN' | 'METASYNC' }
  TRIGGER_MODE = { 'AUTOMATIC' | 'ON_DEMAND' } 
  WITH 'key1' = 'value1', 'key2' = 'value2' ....

Important: When using TRIGGER_MODE = 'ON_DEMAND', you must manually trigger the service by running the RUN SERVICE IN TABLE command.

Check out the Table Services Overview for more information about table services.

Examples

Update Clustering

ALTER TABLE_SERVICE
  LAKE = 'my_lake'
  DATABASE = 'my_database'
  TABLE = 'my_table'
  SERVICE = 'CLUSTER'
  TRIGGER_MODE = 'AUTOMATIC'
  WITH 'cluster.field.names' = 'date,field1,field2', 'cluster.layout.strategy' = 'LINEAR', 'cluster.frequency' = 3

Update MetaSync

ALTER TABLE_SERVICE
  LAKE = 'my_lake'
  DATABASE = 'my_database'
  TABLE = 'my_table'
  SERVICE = 'METASYNC'
  TRIGGER_MODE = 'AUTOMATIC'
  WITH 'metasync.catalogs' = 'myGlueCatalog,salesTeamSnowflakeCatalog'

Update MetaSync with a custom database for iceberg tables in Glue

ALTER TABLE_SERVICE
  LAKE = 'my_lake'
  DATABASE = 'my_database'
  TABLE = 'my_table'
  SERVICE = 'METASYNC'
  TRIGGER_MODE = 'AUTOMATIC'
  WITH 'metasync.catalogs' = 'myGlueCatalog'
       'myGlueCatalog.iceberg' = 'true'
       'myGlueCatalog.iceberg.databaseName' = 'my_iceberg_database'
       'myGlueCatalog.iceberg.tableNameSuffix' = '_iceberg'

Sample response.

Required parameters

• LAKE: Specify the name of the lake where the target Onehouse table exists
• DATABASE: Specify the name of the database where the target Onehouse table exists
• TABLE: Specify the name of the target Onehouse table to modify table service
• SERVICE: Specify the table service to be modified
• TRIGGER_MODE: Specify the trigger mode for the specified table service. Must be one of 'AUTOMATIC' or 'ON_DEMAND'.

Special parameters

Include special parameters after WITH as type String.

Clustering

• cluster.field.names: Specify the field names to use as clustering key fields. Multiple field names can be specified as a comma-separated list.
• cluster.layout.strategy: Specify the clustering layout strategy from 'LINEAR', 'HILBERT', or 'Z-ORDER'.
• cluster.frequency: Specify after how many commits to run clustering. Must be an integer. Only valid when TRIGGER_MODE = 'AUTOMATIC'.
• cluster.bootstrap: May be 'True' or 'False' (default is 'False').
  • When enabled, the full table will be clustered.
  • When disabled, only new commits will be clustered.

Compaction

• compaction.max.bytes: Specify the maximum bytes to compact in each compaction job. Must be an integer.
• compaction.frequency: Specify after how many commits to run compaction. Must be an integer. Only valid when TRIGGER_MODE = 'AUTOMATIC'.

Cleaning

• cleaner.retention.days: Specify the retention period for the data in your table.
• cleaner.frequency: Specify after how many commits to run cleaning. Only valid when TRIGGER_MODE = 'AUTOMATIC'.

MetaSync

• metasync.catalogs: Specify a comma-separated list of catalogs where you want to sync the table metadata. Reference catalogs by their name you have defined in the Onehouse console.

For Glue catalog with Iceberg integration

• <catalogName>.iceberg: Specify whether to use Apache Iceberg for the table metadata. Must be 'true'.
• <catalogName>.iceberg.databaseName: Specify the name of the Apache Iceberg database to use for the table metadata. Note that you can have both Hudi and Iceberg tables in the same Glue database.
• <catalogName>.iceberg.tableNameSuffix: Specify the suffix to add to the table name to create the Apache Iceberg table.