Skip to main content

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.
  • 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.

Cleaning​

  • cleaner.retention.days: Specify the retention period for the data in your table.
  • cleaner.frequency: Specify after how many commits to run cleaning.

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.