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

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:

Was this helpful?

Give Feedback

How can we improve the documentation?

© 2024 DataStax | Privacy policy | Terms of use

Apache, Apache Cassandra, Cassandra, Apache Tomcat, Tomcat, Apache Lucene, Apache Solr, Apache Hadoop, Hadoop, Apache Pulsar, Pulsar, Apache Spark, Spark, Apache TinkerPop, TinkerPop, Apache Kafka and Kafka are either registered trademarks or trademarks of the Apache Software Foundation or its subsidiaries in Canada, the United States and/or other countries. Kubernetes is the registered trademark of the Linux Foundation.

General Inquiries: +1 (650) 389-6000, info@datastax.com