Skip to main content

CREATE TABLE_SERVICE

Description​

Creates a table service configuration for an existing Onehouse table.

Note that the SQL statement does not end with ;

Syntax​

CREATE 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 documentation for more information about table services.

Examples​

Create Clustering

CREATE TABLE_SERVICE
  LAKE = 'my_lake'
  DATABASE = 'my_database'
  TABLE = 'my_table'
  SERVICE = 'CLUSTER'
  TRIGGER_MODE = 'AUTOMATIC'
  WITH
    'cluster.field.names' = 'col1,col2,col3',
    'cluster.layout.strategy' = 'LINEAR',
    'cluster.frequency' = 3

Create MetaSync

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

Create MetaSync with a custom database for Apache Iceberg tables in Glue

CREATE 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 create the table service for
  • SERVICE: Specify the table service to be created

Optional parameters​

  • TRIGGER_MODE: Specify the trigger mode for the specified table service. Must be one of 'AUTOMATIC' or 'ON_DEMAND'. Note that 'ON_DEMAND' is only supported for 'METASYNC' service.

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.