Creating row-oriented and column-oriented tables in a database

In the Terraform context, a table is a resource. That is why, we describe database tables in independent blocks (resource "yandex_ydb_table"), specifying an internal link to the previously created database.

Description of the yandex_ydb_table resourceDescription of the yandex_ydb_table resource

Example of creating a 4-column test table in an existing database:

resource "yandex_ydb_table" "test_table" {
  path = "test_dir/test_table_4_col"
  connection_string = yandex_ydb_database_serverless.database1.ydb_full_endpoint

  column {
    name = "a"
    type = "Utf8"
    not_null = true
  }
  column {
    name = "b"
    type = "Uint32"
    not_null = true
  }
  column {
    name = "c"
    type = "Int32"
    not_null = false
  }
  column {
    name = "d"
    type = "Timestamp"
  }

  primary_key = ["a","b"]

}

resource "yandex_ydb_table" "test_table" {
  path              = "test_dir/test_table_4_col"
  connection_string = yandex_ydb_database_serverless.database1.ydb_full_endpoint

  column {
    name     = "a"
    type     = "Utf8"
    not_null = true
  }
  column {
    name     = "b"
    type     = "Uint32"
    not_null = true
  }
  column {
    name     = "c"
    type     = "Int32"
    not_null = false
  }
  column {
    name = "d"
    type = "Timestamp"
  }

  primary_key = ["a", "b"]

  store = "column"

  partitioning_settings {
    partition_by = ["b", "a"]
  }
}

Properties of the yandex_ydb_table resource fields:

1. path: Path within the database to where the table will be created. The table name is specified without the trailing slash (/). If there is no directory to host the table, it will be created automatically.
2. connection_string: Path for connecting to the database. It is used together with the ydb_full_endpoint parameter, which contains the full path to the database: grpcs://ydb.serverless.yandexcloud.net:2135/?database=/ru-central1/b1gv7kfcttio********/etn66ecf1qbt********. For brevity and simplicity, you can use a link to the "yandex_ydb_database_serverless" resource with the ID and the ydb_full_endpoint parameter specified, e.g., yandex_ydb_database_serverless.database1.ydb_full_endpoint.
3. primary_key: Primary key of the table. The key can be composite.

Full list of the yandex_ydb_table resource fields:

The yandex_ydb_table block contains nested column blocks that describe individual properties of columns, such as:

Example of a full column description:

column {
  name     = "column_name"
  type     = "Utf8"
  family   = "some_family"
  not_null = true
}

You can group columns into families to set shared parameters for them, such as the following:

• DATA: Type of storage device for column data in this group. The acceptable values are ssd and rot (named after HDD spindle rotation).
• COMPRESSION: Data compression codec. The acceptable values are off and lz4.

To group columns into a family, add the family block at the same level with the column block:

family {
  name        = "my_family"
  data        = "ssd"
  compression = "lz4"
}

Descriptions of family block fields:

Here is an example of grouping two columns into a family:

resource "yandex_ydb_table" "test_table" {
    path = "test_dir/test_table"
    connection_string = yandex_ydb_database_serverless.database1.ydb_full_endpoint
    column {
        name = "a"
        type = "Uint16"
        family = "test_family_group"
        not_null = true
    }
    column {
        name = "b"
        type = "Uint32"
        family = "test_family_group"
        not_null = true
    }
    family {
      name  = "test_family_group"
      data  = "ssd"
      compression = "lz4"
    }
}

YDB allows you to create a TTL column, i.e., a special column type, whose values determine the time-to-live for rows. The TTL mechanism automatically deletes the item from your table after the specified number of seconds elapses from the time set in the TTL column. You can define only a single TTL column. A TTL column comment can be one of the following types: Date, Datetime, Timestamp, Uint32, Uint64, DyNumber.

The TTL column is configured by the following block:

ttl {
  column_name     = "d"
  expire_interval = "PT1H" # 1 hour
  unit            = "seconds" # For numeric column types (non ISO 8601)
}

Description of ttl field values:

Partitioning of row-oriented tablesPartitioning of row-oriented tables

Partitioning is splitting table data into parts to improve query performance and optimize data management. For partitioning of row-oriented YDB tables in Terraform, use the partitioning_settings parameter of the yandex_ydb_table resource.

Description of the partitioning_settings block fieldsDescription of the partitioning_settings block fields

Here is an example:

resource "yandex_ydb_table" "test_table" {
  path = "/test_dir/test_table_4_col"
  connection_string = yandex_ydb_database_serverless.database1.ydb_full_endpoint

  partitioning_settings {
    auto_partitioning_min_partitions_count = 5
    auto_partitioning_max_partitions_count = 8
    auto_partitioning_partition_size_mb    = 256
    auto_partitioning_by_load              = true
    ...
  }
 ...
}

Full description of the partitioning_settings fields for row-oriented tables:

Partitioning of column-oriented tablesPartitioning of column-oriented tables

Partitioning is splitting table data into parts to improve query performance and optimize data management. Unlike YDB row-oriented tables, column-oriented tables partition data not based on primary keys but specially designated partitioning keys. Partitioning keys are a subset of the table's primary keys.

In Terraform, column-oriented tables are partitioned using the parameters in the partitioning_settings section of the yandex_ydb_table resource.

Here is an example:

resource "yandex_ydb_table" "test_table" {
    path              = "test_dir/test_table_4_col"
    connection_string = yandex_ydb_database_serverless.database1.ydb_full_endpoint

    ...

    partitioning_settings {
        partition_by = ["b", "a"]
    }
}

Description of the partitioning_settings fields for column-oriented tables: