Creating a table with a composite partition key

Create a table with a composite partition key and populate it with data.

This section shows how to create a table with a composite primary key and populate it with data.

A composite partition key contains multiple columns.
Using multiple columns in a partition key breaks the data into smaller groups than if using a single partition key column. Smaller row groups reduce cluster hotspots, where one partition is repeatedly written to.

The examples in this section use a table named rank_by_year_and_name. The table stores the ranking and name of cyclists who competed in races.

This CREATE TABLE statement creates the rank_by_year_and_name table. The race_year and race_name columns are the composite partition key. The primary key also has a column named rank, which is a clustering column. Notice the double parentheses around the first two columns in the PRIMARY KEY clause, which identifies the composite partition key.
```
CREATE TABLE cycling.rank_by_year_and_name (
  race_year int,
  race_name text,
  cyclist_name text,
  rank int,
  PRIMARY KEY ((race_year, race_name), rank)
);
```

These INSERT statements add rows to the table:

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2015, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Benjamin PRADES', 1
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2015, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Adam PHELAN', 2
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2015, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Thomas LEBAS', 3
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Benjamin PRADES', 3
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Daniel MARTIN', 1
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, 'Tour of Japan - Stage 4 - Minami > Shinshu', 'Johan Esteban CHAVES', 2
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2015, 'Giro d''Italia - Stage 11 - Forli > Imola', 'Ilnur ZAKARIN', 1
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2015, 'Giro d''Italia - Stage 11 - Forli > Imola', 'Carlos BETANCUR', 2
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, '4th Tour of Beijing', 'Phillippe GILBERT', 1
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, '4th Tour of Beijing', 'Daniel MARTIN', 2
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, '4th Tour of Beijing', 'Johan Esteban CHAVES', 3
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, '4th Tour of Beijing', 'Phillippe GILBERT', 1
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, '4th Tour of Beijing', 'Daniel MARTIN', 2
);

INSERT INTO cycling.rank_by_year_and_name (
  race_year, race_name, cyclist_name, rank
) VALUES (
  2014, '4th Tour of Beijing', 'Johan Esteban CHAVES', 3
);

This SELECT statement returns the sorted rank of the cyclists for each race year and race name:

SELECT *
FROM cycling.rank_by_year_and_name;

 race_year | race_name                                  | rank | cyclist_name
-----------+--------------------------------------------+------+----------------------
      2014 |                        4th Tour of Beijing |    1 |    Phillippe GILBERT
      2014 |                        4th Tour of Beijing |    2 |        Daniel MARTIN
      2014 |                        4th Tour of Beijing |    3 | Johan Esteban CHAVES
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    1 |        Daniel MARTIN
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    2 | Johan Esteban CHAVES
      2014 | Tour of Japan - Stage 4 - Minami > Shinshu |    3 |      Benjamin PRADES
      2015 |   Giro d'Italia - Stage 11 - Forli > Imola |    1 |        Ilnur ZAKARIN
      2015 |   Giro d'Italia - Stage 11 - Forli > Imola |    2 |      Carlos BETANCUR
      2015 | Tour of Japan - Stage 4 - Minami > Shinshu |    1 |      Benjamin PRADES
      2015 | Tour of Japan - Stage 4 - Minami > Shinshu |    2 |          Adam PHELAN
      2015 | Tour of Japan - Stage 4 - Minami > Shinshu |    3 |         Thomas LEBAS

(11 rows)