 |
Version 10.0.2
|
|
Version 10.0.2
|
While many tables have simple key/value pairs for records, WiredTiger also supports more complex data patterns.
A table is a logical representation of data consisting of cells in rows and columns. For example, a database might have a simple table including an employee identifier, last name and first name, and a salary:
| Employee ID | Last Name | First Name | Salary |
|---|---|---|---|
| 1 | Smith | Joe | 40000 |
| 2 | Jones | Mary | 50000 |
| 3 | Johnson | Cathy | 44000 |
A row-oriented database would store all of the values for the first employee in the first row, then the next employee's values in the next row, and so on:
1,Smith,Joe,40000
2,Jones,Mary,50000
3,Johnson,Cathy,44000
A column-oriented database would store all of the values of a column together, then the values of the next column, and so on:
1,2,3
Smith,Jones,Johnson
Joe,Mary,Cathy
40000,50000,44000
WiredTiger supports both storage formats, and can mix and match the storage of columns within a logical table.
A table in WiredTiger consist of one or more "column groups" that together hold all of the columns in primary key order; and zero or more indices that enable fast lookup of records by columns in orders other than the primary key.
Applications describe the format of their data by supplying a schema to WT_SESSION::create. This specifies how the application's data can be split into fields and mapped onto rows and columns.
By default, WiredTiger works as a traditional key/value store, where the keys and values are raw byte arrays accessed using a WT_ITEM structure. Key and value types may also be chosen from a list, or composed of multiple columns with any combination of types. Keys and values may be up to (4GB - 512B) bytes in size.
See Key/Value pairs for more details on raw key / value items.
WiredTiger's uses format strings similar to those specified in the Python struct module to describe the types of columns in a table: http://docs.python.org/library/struct
| Format | C Type | Python type | Notes |
|---|---|---|---|
x | N/A | N/A | pad byte, no associated value |
b | int8_t | int | signed byte |
B | uint8_t | int | unsigned byte |
h | int16_t | int | signed 16-bit |
H | uint16_t | int | unsigned 16-bit |
i | int32_t | int | signed 32-bit |
I | uint32_t | int | unsigned 32-bit |
l | int32_t | int | signed 32-bit |
L | uint32_t | int | unsigned 32-bit |
q | int64_t | int | signed 64-bit |
Q | uint64_t | int | unsigned 64-bit |
r | uint64_t | int | record number |
s | char[] | str | fixed-length string |
S | char[] | str | NUL-terminated string |
t | uint8_t | int | fixed-length bit field |
u | WT_ITEM * | bytes | raw byte array |
The 'r' type is used for record number keys in column stores. It is otherwise identical to the 'Q' type.
The 's' type is used for fixed-length strings. If it is preceded by a size, that indicates the number of bytes to store; the default is a length of 1 byte.
The 'S' type is encoded as a C language string terminated by a NUL character. When preceded by a size, that indicates the maximum number of bytes the string can store. In a string with characters less than the specified size, the remaining bytes are NUL padded. If the supplied string is longer than the specified size, it will be stored without a trailing NUL.
The 't' type is used for fixed-length bit field values. If it is preceded by a size, that indicates the number of bits to store, between 1 and 8. That number of low-order bits will be stored in the table. The default is a size of 1 bit: that is, a boolean. C applications must always use a uint8_t type (or equivalently, unsigned char) for calls to WT_CURSOR::set_value, and a pointer to the same for calls to WT_CURSOR::get_value.
If a bit field value is combined with other types in a packing format, it is equivalent to 'B', and a full byte is used to store it. However, when standing alone in a table or column group, and referenced by a record number (that is, a key format of 'r'), the 't' type will be stored in a fixed-length column store. This is an optimized on-disk format for efficient storage of small items. (See File formats for further information.) It has an important operational restriction: there is no separate out-of-band value to indicate that deleted records do not exist. Instead, deleted values read back as zero. This means removing a record with WT_CURSOR::remove is equivalent to storing a value of 0 in the record with WT_CURSOR::update. Additionally, creating a record past the end of an object implicitly also creates any missing intermediate records, all with values of 0.
The 'u' type is for raw byte arrays: if it appears at the end of a format string (including in the default "u" format for untyped tables), the size is not stored explicitly. When 'u' appears within a format string, the size is stored as a 32-bit integer in the same byte order as the rest of the format string, followed by the data.
There is a default collator that gives lexicographic (byte-wise) comparisons, and the default encoding is designed so that lexicographic ordering of encoded keys is usually the expected ordering. For example, the variable-length encoding of integers is designed so that they have the natural integer ordering under the default collator.
See Packing and Unpacking Data for details of WiredTiger's packing format.
WiredTiger can also be extended with custom collators by implementing the WT_COLLATOR interface.
Every table has a key format and a value format as describe in Column types. These types are configured when the table is created by passing key_format and value_format keys to WT_SESSION::create.
For example, a simple row-store table with strings as both keys and values would be created as follows:
A simple column-store table with strings for values would be created as follows:
Cursors for a table have the same key format as the table itself. The key columns of a cursor are set with WT_CURSOR::set_key and accessed with WT_CURSOR::get_key. WT_CURSOR::set_key is analogous to printf, and takes a list of value in the order the key columns are configured in key_format.
For example, setting the key for a row-store table with strings as keys would be done as follows:
For example, setting the key for a column-store table would be done as follows:
A more complex example, setting a composite key for a row-store table where the key_format was "SiH", would be done as follows:
The key's values are accessed with WT_CURSOR::get_key, which is analogous to scanf, and takes a list of pointers to values in the same order:
Cursors for a table have the same value format as the table, unless a projection is configured with WT_SESSION::open_cursor. See Projections for more information.
WT_CURSOR::set_value is used to set value columns, and WT_CURSOR::get_value is used to get value columns, in the same way as described for WT_CURSOR::set_key and WT_CURSOR::get_key.
The columns in a table can be assigned names by passing a columns key to WT_SESSION::create. The column names are assigned first to the columns in the key_format, and then to the columns in value_format. There must be a name for every column, and no column names may be repeated.
For example, a column-store table with an employee ID as the key and three columns (department, salary and first year of employment), might be created as follows:
In this example, the key's column name is id, and the value's column names are department, salary, and year-started (where id maps to the column format r, department maps to the column value format S, salary maps to the value format i and year-started maps to the value format H).
Once the table is created, there is no need to call WT_SESSION::create during subsequent runs of the application. However, it's worthwhile making the call anyway as it both verifies the table exists and the table schema matches the schema expected by the application.
Once column names are assigned, they can be used to configure column groups. Column groups are primarily used to define storage in order to tune cache behavior, as each column group is stored in a separate file.
There are two steps involved in setting up column groups: first, pass a list of names for the column groups in the colgroups configuration key to WT_SESSION::create. Then make a call to WT_SESSION::create for each column group, using the URI colgroup:<table>:<colgroup name> and a columns key in the configuration. Every column must appear in at least one column group; columns can be listed in multiple column groups, causing the column to be stored in multiple files.
For example, consider the following data being stored in a WiredTiger table:
If we primarily wanted to access the population information by itself, but still wanted population information included when accessing other information, we might store all of the columns in one file, and store an additional copy of the population column in another file:
Column groups always have the same key as the table. This is particularly useful for column stores, because record numbers are not stored explicitly on disk, so there is no repetition of keys across multiple files. Keys will be replicated in multiple files in the case of row-store column groups.
A cursor can be opened on a column group by passing the column group's URI to the WT_SESSION::open_cursor method. For example, the population can be retrieved from both of the column groups we created:
Key columns may not be included in the list of columns for a column group. Because column groups always have the same key as the table, key columns for column groups are retrieved using WT_CURSOR::get_key, not WT_CURSOR::get_value.
Another example of using column groups is in ex_col_store.c:
In this example the hour and day columns are grouped together in one columns group and the temperature column stored in another. This allows a cursor to be opened on a either of these column groups instead of the entire table.
An operation can then be completed on the values of this column without having to bring the other columns into memory.
Columns are also used to create and configure indices on tables.
Table indices are automatically updated whenever the table is modified.
Table index cursors are read-only and cannot be used for update operations.
To create a table index, call WT_SESSION::create using the URI index:<table>:<index name>, listing a column in the configuration.
Continuing the example, we might open an index on the country column:
Cursors are opened on indices by passing the index's URI to the WT_SESSION::open_cursor method.
Index cursors use the specified index key columns for WT_CURSOR::get_key and WT_CURSOR::set_key. For example, we can retrieve information from the country index as follows:
To create an index with a composite key, specify more than one column to the WT_SESSION::create call:
To retrieve information from a composite index requires a more complicated WT_CURSOR::set_key call, but is otherwise the same:
It is possible to create an index with the immutable configuration setting enabled. This setting tells WiredTiger that the index keys for a record do not change when records are updated. This is an optimization that it saves a remove and insert into the index whenever a value in the primary table is updated.
If immutable is configured when updates should alter the content of the index it is possible to corrupt data.
An example of using an immutable index is:
By default, index cursors return all of the table's value columns from WT_CURSOR::get_value. The application can specify that a subset of the usual columns should be returned in calls to WT_CURSOR::get_value by appending a list of columns to the uri parameter of the WT_SESSION::open_cursor call. This is called a projection, see Projections for more details.
In the case of index cursors, a projection can be used to avoid lookups in column groups that do not hold columns relevant to the operation.
The following example will return just the table's primary key (a record number, in this case) from the index:
Here is an example of a projection that returns a subset of columns from the index:
For performance reasons, it may be desirable to include all columns for a performance-critical operation in an index, so that it is possible to perform index-only lookups where no column group from the table is accessed. In this case, all of the "hot" columns should be included in the index (always list the "real" index key columns first, so they will determine the sort order). Then, open a cursor on the index that doesn't return any value columns, and no column group will be accessed.
Index cursors for column-store objects may not be created using the record number as the index key (there is no use for a secondary index on a column-store where the index key is the record number).
The code included above was taken from the complete example program ex_schema.c.
Here are other example programs, ex_call_center.c,
and ex_col_store.c.