Skip to main content

ALTER SERVICE IN TABLE

Description​

Modifies table service configurations for an existing Onehouse table.

Note that the SQL statement does not end with ;

Syntax​

ALTER SERVICE IN TABLE <table_name>
  LAKE = <string>
  DATABASE = <string>
  SERVICE = { 'CLUSTER' | 'COMPACT' | 'CLEAN' | 'METASYNC' | 'AUTOSAVEPOINT' | 'RESTORE' }
  ENABLED = { TRUE | FALSE }
  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​

Enable or Update Clustering

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

Enable or Update MetaSync

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

Enable or Update MetaSync with a custom database for iceberg tables in Glue

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

Sample response.

Required parameters​

  • <table_name>: Specify the name of the target Onehouse table to modify table service
  • 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
  • SERVICE: Specify the table service to to be enabled/disabled
  • ENABLED: Specify whether the specified table service should be enabled for the specified table
  • TRIGGER_MODE: Specify the trigger mode for the specified table service. Must be one of 'AUTOMATIC' or 'ON_DEMAND'.

Optional parameters​

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.

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.

Savepoint​

  • savepoint.frequency: Specify the frequency to create savepoints as 'Once', 'Daily', 'Weekly', or 'Monthly'.
  • savepoint.frequency.week.day: For weekly frequency, specify the day of the week to create the savepoint as 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat', or 'Sun'.
  • savepoint.frequency.day: For monthly frequency, specify the day to create the savepoint. Must be an integer between 1 and 31.
  • savepoint.frequency.hour: For daily/weekly/monthly frequency, specify the hour to create the savepoint. Must be an integer between 0 and 23.
  • savepoint.frequency.minute: For daily/weekly/monthly frequency, specify the minute to create the savepoint. Must be an integer between 0 and 59.
  • savepoint.commit.id: Commit to perform the savepoint operation to.

Restore​

  • restore.commit.id: Commit to perform the restore operation to.