DEFINING THE SCHEMA
Dividing the large partitions:
The objective is to create tables that, in the event that a single partition is touched by a query, can yield the necessary data. Nonetheless, it is feasible to create broad partition-style tables that go close to Cassandra’s inherent limitations, as seen by the examples. Table sizing analysis can identify potentially excessively big partitions in terms of number of items, disk size, or both.
The process of dividing a huge partition is as simple as adding a new column to the partition key. For the most part, it will be sufficient to move one of the current columns into the partition key. Adding a column to the table to serve as a sharding key is a third approach, but this requires more application logic.
Keeping with the available rooms example, each partition would then reflect the availability of rooms at a particular hotel on a certain date if the date column was added to the partition key for the table. Since the data for consecutive days will probably be on different nodes, this will undoubtedly result in divisions that are much smaller—possibly too small.
Another method for dividing the data into segments of a manageable size is called bucketing. To bucketize the table, for instance, you could add a month column (perhaps expressed as an integer) to the partition key. The figure below compares the current design to the original. The month column offers a useful method of organizing relevant data in a division that won’t grow too big, even though it partially duplicates the date.
An alternative would be to append the room_id to the partition key, which would allow each partition to reflect the room’s availability on all days, if you were really committed to maintaining a broad partition design. As no query was found that inquired about the availability of a particular room, the first or second design method is best suited for the requirements of the application.
Once you have finished evaluating and refining the physical model, you’re ready to implement the schema in CQL. Here is the schema for the hotel keyspace, using CQL’s comment feature to document the query pattern supported by each table:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 |
CREATE KEYSPACE hotel WITH replication = {‘class’: ‘SimpleStrategy’, ‘replication_factor’ : 3}; CREATE TYPE employe.details ( name, id number, mobile no. salary given, address, country text ); CREATE TABLE employe,details_by_poi ( poi_name text, employe_id text, name text, phone text, address frozen<address>, PRIMARY KEY ((poi_name), employee_id) ) WITH comment = ‘Q1. Find Employee details’ AND CLUSTERING ORDER BY (employee_id ASC) ; CREATE TABLE employee.details ( id text PRIMARY KEY, name text, phone text, address frozen<address>, pois set ) WITH comment = ‘Q2. Find information about a employee’; CREATE TABLE employe.pois_by_employee ( poi_name text, employee_id text, description text, PRIMARY KEY ((employe_id), poi_name) ) WITH comment = Q3. Find a employee’; CREATE TABLE employe_employee.details_text ( employee_id text, date date, id_number smallint, salary given boolean, PRIMARY KEY ((employee_id), date, salary given) ) WITH comment = ‘Q4. Find available employee by salary’; CREATE TABLE employee.details_by_salary ( employee_id text, id_number smallint, salary boolean, description text, PRIMARY KEY ((employee_id, mobile_number), salary) ) WITH comment = ‘Q5. Find salary for a employee’; |
Notice that the elements of the partition key are surrounded with parentheses, even though the partition key consists of the single column poi_name. This is a best practice that makes the selection of partition key more explicit to others reading your CQL.
Author : Neha Kasanagottu |
LinkedIn : https://www.linkedin.com/in/neha-kasanagottu-5b6802272
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates.
KTExperts is always active on social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Note: Please test scripts in Non Prod before trying in Production.
Loading...
