Create a tuple column
Tuples are a data type that allow two or more values with mixed data type to be stored together in a column.
A user-defined type is another choice, but for simple groupings, a tuple is a good choice.
A tuple can accommodate many fields (32768), but should generally be created to hold 2 to 5 fields.
Prerequisite
-
Keyspace must exist
Create a table with a tuple column and and index the tuple
Create a table cycling.nation_rank with a primary key of nation and using a tuple to store the rank, cyclist name, and points total for a cyclist.
In the table creation statement, use angle brackets and a comma delimiter to declare the tuple component types.
CREATE TABLE IF NOT EXISTS cycling.nation_rank (
nation text PRIMARY KEY,
info tuple<int, text, int>
);The tuple column can be indexed:
CREATE CUSTOM INDEX info_idx ON cycling.nation_rank (info) USING 'StorageAttachedIndex';A query can be run to retrieve all the data from the table:
SELECT * FROM cycling.nation_rank;Results
nation | info
---------+---------------------------------
Spain | (1, 'Alejandro VALVERDE', 9054)
Belgium | (3, 'Phillippe GILBERT', 6222)
France | (2, 'Sylvain CHAVANEL', 6339)
Italy | (4, 'Davide REBELLINI', 6090)
(4 rows)If a tuple column is indexed, the data in the table can be filtered using the indexed column:
SELECT * FROM cycling.nation_rank
WHERE info = (3, 'Phillippe GILBERT', 6222);Results
nation | info
---------+--------------------------------
Belgium | (3, 'Phillippe GILBERT', 6222)
(1 rows)Create another table with the same data, but with the rank as the primary key
The previous table cycling.nation_rank uses the nation as the primary key.
It is possible to store the same data using the rank as the primary key, as shown in the following example.
The example creates a table named cycling.popular using a tuple to store the country name, cyclist name, and points total for a cyclist, with rank set as the primary key.
CREATE TABLE IF NOT EXISTS cycling.popular (
rank int PRIMARY KEY,
cinfo tuple<text, text, int>
);Create a table using nested tuples for geographic data
Another example creates a table cycling.route using a tuple to store each waypoint location name, latitude, and longitude.
Two tuples are used in the following example, with one tuple nested inside the other tuple.
CREATE TABLE IF NOT EXISTS cycling.route (
race_id int,
race_name text,
point_id int,
lat_long tuple<text, tuple<float, float>>,
PRIMARY KEY (race_id, point_id)
);Insert some data:
INSERT INTO cycling.route (
race_id, race_name, point_id, lat_long
) VALUES (
500, '47th Tour du Pays de Vaud', 1, ('Onnens', (46.8444, 6.6667))
);
INSERT INTO cycling.route (
race_id, race_name, point_id, lat_long
) VALUES (
500, '47th Tour du Pays de Vaud', 2, ('Champagne', (46.833, 6.65))
);
INSERT INTO cycling.route (
race_id, race_name, point_id, lat_long
) VALUES (
500, '47th Tour du Pays de Vaud', 3, ('Novalle', (46.833, 6.6))
);
INSERT INTO cycling.route (
race_id, race_name, point_id, lat_long
) VALUES (
500, '47th Tour du Pays de Vaud', 4, ('Vuiteboeuf', (46.8, 6.55))
);
INSERT INTO cycling.route (
race_id, race_name, point_id, lat_long
) VALUES (
500, '47th Tour du Pays de Vaud', 5, ('Baulmes', (46.7833, 6.5333))
);
INSERT INTO cycling.route (
race_id, race_name, point_id, lat_long
) VALUES (
500, '47th Tour du Pays de Vaud', 6, ('Les Clées', (46.7222, 6.5222))
);Query the data to retrieve all the data from the table:
SELECT * FROM cycling.route;Results
race_id | point_id | lat_long | race_name
---------+----------+----------------------------------+---------------------------
500 | 1 | ('Onnens', (46.8444, 6.6667)) | 47th Tour du Pays de Vaud
500 | 2 | ('Champagne', (46.833, 6.65)) | 47th Tour du Pays de Vaud
500 | 3 | ('Novalle', (46.833, 6.6)) | 47th Tour du Pays de Vaud
500 | 4 | ('Vuiteboeuf', (46.8, 6.55)) | 47th Tour du Pays de Vaud
500 | 5 | ('Baulmes', (46.7833, 6.5333)) | 47th Tour du Pays de Vaud
500 | 6 | ('Les Clées', (46.7222, 6.5222)) | 47th Tour du Pays de Vaud
(6 rows)See also:
