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

Terraform treats a table as a resource. Therefore, we describe database tables in independent sections (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 to the table in the database. Specify the table name without the trailing slash, /. If there is no directory for 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 that contains the full database path: 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 section contains nested column sections describing individual column properties, 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 fields for them, such as the following:

• DATA: Type of storage device for column data in this family. 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 section at the same level as the column section:

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

Descriptions of the family section 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 row from your table after the specified number of seconds elapses from the time set in the TTL column. You can only define a single TTL column. A TTL column can be one of the following types: Date, Datetime, Timestamp, Uint32, Uint64, DyNumber.

The TTL column is described in the following section:

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

Description of the ttl field values:

Partitioning row-oriented tablesPartitioning row-oriented tables

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

Description of the section fieldsDescription of the partitioning_settings section 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 column-oriented tablesPartitioning 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 split data using special partition keys rather than primary keys. Partition 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: