comments

Example for count, timestamps, limiting results by partition.

SOURCE 'keyspace-cycling.cql';

// tag::removedata[]
TRUNCATE TABLE cycling.comments;
// end::removedata[]

// tag::drop[]
DROP TABLE IF EXISTS cycling.comments;
// end::drop[]

/* Support fast retrieval of first record on each partition
   and show upserts of timestamp type fields */

// tag::comments-table[]
CREATE TABLE IF NOT EXISTS cycling.comments (
  record_id timeuuid,
  id uuid,
  commenter text,
  comment text,
  created_at timestamp,
  PRIMARY KEY (id, created_at)
)
WITH CLUSTERING ORDER BY (created_at DESC);
// end::comments-table[]

/* CURRENT DATE */

// tag::now_date[]
INSERT INTO cycling.comments (
  id,
  created_at
) VALUES (
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  toTimeStamp(toDate(now()))
);
// end::now_date[]

// tag::snow_date[]
SELECT created_at
FROM cycling.comments
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
LIMIT 1;
// end::snow_date[]

// tag::now_timestamp[]
INSERT INTO cycling.comments (
  id,
  created_at
) VALUES (
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  toTimeStamp(now())
);
// end::now_timestamp[]

// tag::snow_timestamp[]
SELECT created_at FROM cycling.comments
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
LIMIT 1;
// end::snow_timestamp[]

/* DATE without time or zone */

// tag::string_date_no_tz[]
INSERT INTO cycling.comments (
  id,
  created_at
) VALUES (
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  '2017-04-01'
);
// end::string_date_no_tz[]

// tag::sstring_date_no_tz[]
SELECT created_at FROM cycling.comments
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
  AND created_at = '2017-04-01';
// end::sstring_date_no_tz[]

/* DATE WITH TIMEZONE */

// tag::string_date_tz[]
INSERT INTO cycling.comments (
  id,
  created_at
) VALUES (
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  '2017-04-01+0000'
);
// end::string_date_tz[]

// tag::sstring_date_tz[]
SELECT created_at FROM cycling.comments
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
  AND created_at = '2017-04-01+0000';
// end::sstring_date_tz[]

/* FULL TIMESTAMP */

// tag::string_ts[]
INSERT INTO cycling.comments (
  id,
  created_at
) VALUES (
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  '2017-04-01T11:21:59.001+0000'
);
// end::string_ts[]

// tag::sstring_ts[]
SELECT created_at FROM cycling.comments
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
  AND created_at = '2017-04-01T11:21:59.001-0800';
// end::sstring_ts[]

/* TIMEUUID */

// tag::insert_timeuuid[]
INSERT INTO cycling.comments (
  record_id,
  id,
  created_at
) VALUES (
  now(),
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  '2017-02-14 12:43:20-0800'
);
// end::insert_timeuuid[]

// tag::sinsert_timeuuid[]
SELECT record_id
FROM cycling.comments
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
  AND created_at = '2017-02-14 12:43:20-0800';
// end::sinsert_timeuuid[]

DROP INDEX IF EXISTS cycling.fn_sparse;

/* SASI index */

// tag::fn_sparse[]
CREATE CUSTOM INDEX IF NOT EXISTS fn_sparse
ON cycling.comments (created_at)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = { 'mode': 'SPARSE' };
// end::fn_sparse[]

// tag::insertall[]
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), e7ae5cf3-d358-4d99-b900-85902fda9bb0, '2017-02-14 12:43:20-0800', 'Raining too hard should have postponed', 'Alex');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), e7ae5cf3-d358-4d99-b900-85902fda9bb0, '2017-02-14 12:43:20.234-0800', 'Raining too hard should have postponed', 'Alex');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), e7ae5cf3-d358-4d99-b900-85902fda9bb0, '2017-03-21 13:11:09.999-0800', 'Second rest stop was out of water', 'Alex');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), e7ae5cf3-d358-4d99-b900-85902fda9bb0, '2017-04-01 06:33:02.16-0800', 'LATE RIDERS SHOULD NOT DELAY THE START', 'Alex');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), c7fceba0-c141-4207-9494-a29f9809de6f, totimestamp(now()), 'The gift certificate for winning was the best', 'Amy');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), c7fceba0-c141-4207-9494-a29f9809de6f, '2017-02-17 12:43:20.234+0400', 'Glad you ran the race in the rain', 'Amy');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), c7fceba0-c141-4207-9494-a29f9809de6f, '2017-03-22 5:16:59.001+0400', 'Great snacks at all reststops', 'Amy');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), c7fceba0-c141-4207-9494-a29f9809de6f, '2017-04-01 17:43:08.030+0400', 'Last climb was a killer', 'Amy');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 8566eb59-07df-43b1-a21b-666a3c08c08a, totimestamp(now()), 'Fastest womens time ever way to go amy!', 'Maryanne');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 8566eb59-07df-43b1-a21b-666a3c08c08a, '2017-02-13 11:20:17.020-0600', 'Great race on a crappy day', 'Maryanne');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 8566eb59-07df-43b1-a21b-666a3c08c08a, '2017-03-20 15:45:10.101-0600', 'Saggers really rocked it', 'Maryanne');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 8566eb59-07df-43b1-a21b-666a3c08c08a, '2017-04-14 05:16:52.009-0600', 'Not bad for a flatlander', 'Maryanne');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), fb372533-eb95-4bb4-8685-6ef61e994caa, totimestamp(now()), 'Great course', 'Michael');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), fb372533-eb95-4bb4-8685-6ef61e994caa, '2017-02-15 18:22:11-0800', 'Some entries complain a lot', 'Michael');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), fb372533-eb95-4bb4-8685-6ef61e994caa, '2017-03-16 19:43:01.030-0800', 'Getting read for the race', 'Michael');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), fb372533-eb95-4bb4-8685-6ef61e994caa, '2017-03-22 1:19:44.060-0800', 'Awesome race glad you held it anyway', 'Michael');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), fb372533-eb95-4bb4-8685-6ef61e994caa, '2017-04-07 11:21:14.001-0800', 'Thanks for waiting for me!', 'Michael');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 9011d3be-d35c-4a8d-83f7-a3c543789ee7, totimestamp(now()), 'Can''t wait for the next race', 'Katarzyna');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 9011d3be-d35c-4a8d-83f7-a3c543789ee7, '2017-01-01 11:20:17.020-0600', 'Gearing up for the seaon', 'Katarzyna');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 5b6962dd-3f90-4c93-8f61-eabfa4a803e2, totimestamp(now()), 'Thanks for all your hard work', 'Marianne');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 220844bf-4860-49d6-9a4b-6b5d3a79cbfb, totimestamp(now()), 'A for effort!', 'Paolo');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), c4b65263-fe58-4846-83e8-f0e1c13d518f, totimestamp(now()), 'Closing ceremony was a little lame', 'Rossella');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 38ab64b6-26cc-4de9-ab28-c257cf011659, totimestamp(now()), 'Next time guys!', 'Marcia');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 38ab64b6-26cc-4de9-ab28-c257cf011659, '2017-02-11 22:09:56+0800', 'First race was amazing, can''t wait for more', 'Marcia');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, totimestamp(now()), 'So many great races thanks y''all', 'Steven');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, '2017-02-02 09:49:00.02+0800', 'Best of luck everybody I can''t make it', 'Steven');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, '2017-04-05 12:01:00.003', 'Bike damaged in transit bummer', 'Steven');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), e7cd5752-bc0d-4157-a80f-7523add8dbcd, totimestamp(now()), 'Go team, you rocked it', 'Anna');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 6d5f1663-89c0-45fc-8cfd-60a373b01622, totimestamp(now()), 'Next year the tour of california!', 'Melissa');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 95addc4c-459e-4ed7-b4b5-472f19a67995, totimestamp(now()), 'Next year for sure!', 'Vera');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 95addc4c-459e-4ed7-b4b5-472f19a67995, '2017-02-13 11:40:16.123-0600', 'I can do without the rain@@@@', 'Vera');
// end::insertall[]

CAPTURE 'select_from_comments.results';
// tag::select[]
SELECT *
FROM cycling.comments
WHERE created_at < '2017-02-13 11:40:16.123';
// end::select[]
CAPTURE OFF;

CAPTURE 'select_mostrecent_from_comments.results';
// tag::select-mostrecent[]
SELECT toDate(created_at) AS Date, comment, commenter
FROM cycling.comments
PER PARTITION LIMIT 1;
// end::select-mostrecent[]
CAPTURE OFF;

// tag::alt[]
ALTER TABLE cycling.comments 
WITH compression = { 
  'sstable_compression' : 'DeflateCompressor', 
  'chunk_length_kb' : 64
};
// end::alt[]

// tag::cache[]
ALTER TABLE cycling.comments 
WITH caching = {
  'keys' : 'NONE', 
  'rows_per_partition' : 10
};
// end::cache[]

// tag::nocompact[]
ALTER TABLE cycling.comments 
WITH COMPACTION = {
  'class' : 'SizeTieredCompactionStrategy', 
  'enabled' : 'false'
};
// end::nocompact[]

// tag::compaction[]
ALTER TABLE cycling.comments 
WITH COMPACTION = {
  'class' : 'SizeTieredCompactionStrategy', 
  'enabled' : 'true'
};
// end::compaction[]

/* SEARCH INDEX */

// tag::create[]
CREATE SEARCH INDEX IF NOT EXISTS
ON cycling.comments 
WITH COLUMNS record_id { excluded:true }, * {excluded:false};
// end::create[]

// **CHANGING the CONFIG**

// tag::directoryFactory[]
ALTER SEARCH INDEX CONFIG
ON cycling.comments 
SET directoryFactory = 'encrypted';
// end::directoryFactory[]

// tag::active[]
RELOAD SEARCH INDEX
ON cycling.comments;
// end::active[]

// tag::rebuild[]
RELOAD SEARCH INDEX
ON cycling.comments;

REBUILD SEARCH INDEX
ON cycling.comments;
// end::rebuild[]

// tag::cache[]
ALTER SEARCH INDEX CONFIG
ON cycling.comments 
SET autoCommitTime = 10000;
// end::cache[]

// tag::requesth[]
ALTER SEARCH INDEX CONFIG
ON cycling.comments 
ADD requestHandler[@name='/elevate',@class='solr.SearchHandler', @startup='lazy'] 
WITH $$ {"defaults":[{"echoParams":"explicit"}],"last-components":["elevator"]} $$;
// end::requesth[]

// Policy to merge large segements due to deletes
// tag::delete[]
ALTER SEARCH INDEX CONFIG
ON cycling.comments 
SET indexConfig.mergePolicyFactory[@class='org.apache.solr.index.AutoExpungeDeletesTieredMergePolicyFactory'].bool[@name='mergeSingleSegments'] = true;

ALTER SEARCH INDEX CONFIG
ON cycling.comments 
SET indexConfig.mergePolicyFactory[@class='org.apache.solr.index.AutoExpungeDeletesTieredMergePolicyFactory'].int[@name='maxMergedSegmentMB'] = 1005;

ALTER SEARCH INDEX CONFIG
ON cycling.comments 
SET indexConfig.mergePolicyFactory[@class='org.apache.solr.index.AutoExpungeDeletesTieredMergePolicyFactory'].int[@name='forceMergeDeletesPctAllowed'] = 25;
// end::delete[]

// tag::commit[]
COMMIT SEARCH INDEX
ON cycling.comments;
// end::commit[]

// **CHANGING the SCHEMA**

// tag::add_string_field[]
ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
ADD fields.field[@name='fieldname', @type='StrField', @multiValued = 'false', @indexed='true'];
// end::add_string_field[]

// tag::column_name[]
ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
ADD FIELD record_id;
// end::column_name[]

// tag::rename[]
ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
SET field[@name='fieldname']@name = 'anotherFieldName';
// end::rename[]

// tag::type[]
ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
SET field[@name='fieldname']@type = 'UUIDField';
// end::type[]

// tag::drop_another[]
ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
DROP field anotherFieldName;
// end::drop_another[]

// **CHANGING the CONFIG **
// tag::autocommit[]
ALTER SEARCH INDEX CONFIG
ON cycling.comments 
SET autoCommitTime = 1000;
// end::autocommit[]

// tag::add_text_field[]
ALTER SEARCH INDEX SCHEMA
ON cycling.comments
SET types.fieldType[@name='TextField']@class='org.apache.solr.schema.TextField';

ALTER SEARCH INDEX SCHEMA
ON cycling.comments
SET fields.field[@name='comment']@type='TextField';
// end::add_text_field[]

// tag::drop_text_field[]
ALTER SEARCH INDEX SCHEMA
ON cycling.comments
DROP field comment;

ALTER SEARCH INDEX SCHEMA
ON cycling.comments
DROP types.fieldType[@name='TextField'];
// end::drop_text_field[]

// tag::add_dynamic_text_field[]
ALTER SEARCH INDEX SCHEMA
ON cycling.comments
ADD types.fieldType[@class='org.apache.solr.schema.TextField', @name='TextField']
WITH '{"analyzer":{"class":"org.apache.lucene.analysis.standard.StandardAnalyzer"}}';

ALTER SEARCH INDEX SCHEMA
ON cycling.comments
ADD dynamicField[@name='*fieldname', @type='TextField'];
// end::add_dynamic_text_field[]

RELOAD SEARCH INDEX
ON cycling.comments;

REBUILD SEARCH INDEX
ON cycling.comments;

Queries against the comment tables

Filter data on the time UUID field.

INSERT INTO cycling.comments (
  record_id,
  id,
  created_at
) VALUES (
  now(),
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  '2017-02-14 12:43:20-0800'
);
SELECT record_id
FROM cycling.comments
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
  AND created_at = '2017-02-14 12:43:20-0800';

Return the most recent records:

SOURCE 'keyspace-cycling.cql';

// tag::removedata[]
TRUNCATE TABLE cycling.comments;
// end::removedata[]

// tag::drop[]
DROP TABLE IF EXISTS cycling.comments;
// end::drop[]

/* Support fast retrieval of first record on each partition
   and show upserts of timestamp type fields */

// tag::comments-table[]
CREATE TABLE IF NOT EXISTS cycling.comments (
  record_id timeuuid,
  id uuid,
  commenter text,
  comment text,
  created_at timestamp,
  PRIMARY KEY (id, created_at)
)
WITH CLUSTERING ORDER BY (created_at DESC);
// end::comments-table[]

/* CURRENT DATE */

// tag::now_date[]
INSERT INTO cycling.comments (
  id,
  created_at
) VALUES (
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  toTimeStamp(toDate(now()))
);
// end::now_date[]

// tag::snow_date[]
SELECT created_at
FROM cycling.comments
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
LIMIT 1;
// end::snow_date[]

// tag::now_timestamp[]
INSERT INTO cycling.comments (
  id,
  created_at
) VALUES (
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  toTimeStamp(now())
);
// end::now_timestamp[]

// tag::snow_timestamp[]
SELECT created_at FROM cycling.comments
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
LIMIT 1;
// end::snow_timestamp[]

/* DATE without time or zone */

// tag::string_date_no_tz[]
INSERT INTO cycling.comments (
  id,
  created_at
) VALUES (
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  '2017-04-01'
);
// end::string_date_no_tz[]

// tag::sstring_date_no_tz[]
SELECT created_at FROM cycling.comments
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
  AND created_at = '2017-04-01';
// end::sstring_date_no_tz[]

/* DATE WITH TIMEZONE */

// tag::string_date_tz[]
INSERT INTO cycling.comments (
  id,
  created_at
) VALUES (
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  '2017-04-01+0000'
);
// end::string_date_tz[]

// tag::sstring_date_tz[]
SELECT created_at FROM cycling.comments
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
  AND created_at = '2017-04-01+0000';
// end::sstring_date_tz[]

/* FULL TIMESTAMP */

// tag::string_ts[]
INSERT INTO cycling.comments (
  id,
  created_at
) VALUES (
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  '2017-04-01T11:21:59.001+0000'
);
// end::string_ts[]

// tag::sstring_ts[]
SELECT created_at FROM cycling.comments
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
  AND created_at = '2017-04-01T11:21:59.001-0800';
// end::sstring_ts[]

/* TIMEUUID */

// tag::insert_timeuuid[]
INSERT INTO cycling.comments (
  record_id,
  id,
  created_at
) VALUES (
  now(),
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  '2017-02-14 12:43:20-0800'
);
// end::insert_timeuuid[]

// tag::sinsert_timeuuid[]
SELECT record_id
FROM cycling.comments
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
  AND created_at = '2017-02-14 12:43:20-0800';
// end::sinsert_timeuuid[]

DROP INDEX IF EXISTS cycling.fn_sparse;

/* SASI index */

// tag::fn_sparse[]
CREATE CUSTOM INDEX IF NOT EXISTS fn_sparse
ON cycling.comments (created_at)
USING 'org.apache.cassandra.index.sasi.SASIIndex'
WITH OPTIONS = { 'mode': 'SPARSE' };
// end::fn_sparse[]

// tag::insertall[]
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), e7ae5cf3-d358-4d99-b900-85902fda9bb0, '2017-02-14 12:43:20-0800', 'Raining too hard should have postponed', 'Alex');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), e7ae5cf3-d358-4d99-b900-85902fda9bb0, '2017-02-14 12:43:20.234-0800', 'Raining too hard should have postponed', 'Alex');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), e7ae5cf3-d358-4d99-b900-85902fda9bb0, '2017-03-21 13:11:09.999-0800', 'Second rest stop was out of water', 'Alex');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), e7ae5cf3-d358-4d99-b900-85902fda9bb0, '2017-04-01 06:33:02.16-0800', 'LATE RIDERS SHOULD NOT DELAY THE START', 'Alex');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), c7fceba0-c141-4207-9494-a29f9809de6f, totimestamp(now()), 'The gift certificate for winning was the best', 'Amy');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), c7fceba0-c141-4207-9494-a29f9809de6f, '2017-02-17 12:43:20.234+0400', 'Glad you ran the race in the rain', 'Amy');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), c7fceba0-c141-4207-9494-a29f9809de6f, '2017-03-22 5:16:59.001+0400', 'Great snacks at all reststops', 'Amy');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), c7fceba0-c141-4207-9494-a29f9809de6f, '2017-04-01 17:43:08.030+0400', 'Last climb was a killer', 'Amy');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 8566eb59-07df-43b1-a21b-666a3c08c08a, totimestamp(now()), 'Fastest womens time ever way to go amy!', 'Maryanne');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 8566eb59-07df-43b1-a21b-666a3c08c08a, '2017-02-13 11:20:17.020-0600', 'Great race on a crappy day', 'Maryanne');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 8566eb59-07df-43b1-a21b-666a3c08c08a, '2017-03-20 15:45:10.101-0600', 'Saggers really rocked it', 'Maryanne');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 8566eb59-07df-43b1-a21b-666a3c08c08a, '2017-04-14 05:16:52.009-0600', 'Not bad for a flatlander', 'Maryanne');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), fb372533-eb95-4bb4-8685-6ef61e994caa, totimestamp(now()), 'Great course', 'Michael');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), fb372533-eb95-4bb4-8685-6ef61e994caa, '2017-02-15 18:22:11-0800', 'Some entries complain a lot', 'Michael');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), fb372533-eb95-4bb4-8685-6ef61e994caa, '2017-03-16 19:43:01.030-0800', 'Getting read for the race', 'Michael');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), fb372533-eb95-4bb4-8685-6ef61e994caa, '2017-03-22 1:19:44.060-0800', 'Awesome race glad you held it anyway', 'Michael');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), fb372533-eb95-4bb4-8685-6ef61e994caa, '2017-04-07 11:21:14.001-0800', 'Thanks for waiting for me!', 'Michael');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 9011d3be-d35c-4a8d-83f7-a3c543789ee7, totimestamp(now()), 'Can''t wait for the next race', 'Katarzyna');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 9011d3be-d35c-4a8d-83f7-a3c543789ee7, '2017-01-01 11:20:17.020-0600', 'Gearing up for the seaon', 'Katarzyna');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 5b6962dd-3f90-4c93-8f61-eabfa4a803e2, totimestamp(now()), 'Thanks for all your hard work', 'Marianne');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 220844bf-4860-49d6-9a4b-6b5d3a79cbfb, totimestamp(now()), 'A for effort!', 'Paolo');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), c4b65263-fe58-4846-83e8-f0e1c13d518f, totimestamp(now()), 'Closing ceremony was a little lame', 'Rossella');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 38ab64b6-26cc-4de9-ab28-c257cf011659, totimestamp(now()), 'Next time guys!', 'Marcia');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 38ab64b6-26cc-4de9-ab28-c257cf011659, '2017-02-11 22:09:56+0800', 'First race was amazing, can''t wait for more', 'Marcia');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, totimestamp(now()), 'So many great races thanks y''all', 'Steven');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, '2017-02-02 09:49:00.02+0800', 'Best of luck everybody I can''t make it', 'Steven');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 6ab09bec-e68e-48d9-a5f8-97e6fb4c9b47, '2017-04-05 12:01:00.003', 'Bike damaged in transit bummer', 'Steven');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), e7cd5752-bc0d-4157-a80f-7523add8dbcd, totimestamp(now()), 'Go team, you rocked it', 'Anna');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 6d5f1663-89c0-45fc-8cfd-60a373b01622, totimestamp(now()), 'Next year the tour of california!', 'Melissa');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 95addc4c-459e-4ed7-b4b5-472f19a67995, totimestamp(now()), 'Next year for sure!', 'Vera');
INSERT INTO cycling.comments (record_id, id, created_at, comment, commenter) VALUES (now(), 95addc4c-459e-4ed7-b4b5-472f19a67995, '2017-02-13 11:40:16.123-0600', 'I can do without the rain@@@@', 'Vera');
// end::insertall[]

CAPTURE 'select_from_comments.results';
// tag::select[]
SELECT *
FROM cycling.comments
WHERE created_at < '2017-02-13 11:40:16.123';
// end::select[]
CAPTURE OFF;

CAPTURE 'select_mostrecent_from_comments.results';
// tag::select-mostrecent[]
SELECT toDate(created_at) AS Date, comment, commenter
FROM cycling.comments
PER PARTITION LIMIT 1;
// end::select-mostrecent[]
CAPTURE OFF;

// tag::alt[]
ALTER TABLE cycling.comments 
WITH compression = { 
  'sstable_compression' : 'DeflateCompressor', 
  'chunk_length_kb' : 64
};
// end::alt[]

// tag::cache[]
ALTER TABLE cycling.comments 
WITH caching = {
  'keys' : 'NONE', 
  'rows_per_partition' : 10
};
// end::cache[]

// tag::nocompact[]
ALTER TABLE cycling.comments 
WITH COMPACTION = {
  'class' : 'SizeTieredCompactionStrategy', 
  'enabled' : 'false'
};
// end::nocompact[]

// tag::compaction[]
ALTER TABLE cycling.comments 
WITH COMPACTION = {
  'class' : 'SizeTieredCompactionStrategy', 
  'enabled' : 'true'
};
// end::compaction[]

/* SEARCH INDEX */

// tag::create[]
CREATE SEARCH INDEX IF NOT EXISTS
ON cycling.comments 
WITH COLUMNS record_id { excluded:true }, * {excluded:false};
// end::create[]

// **CHANGING the CONFIG**

// tag::directoryFactory[]
ALTER SEARCH INDEX CONFIG
ON cycling.comments 
SET directoryFactory = 'encrypted';
// end::directoryFactory[]

// tag::active[]
RELOAD SEARCH INDEX
ON cycling.comments;
// end::active[]

// tag::rebuild[]
RELOAD SEARCH INDEX
ON cycling.comments;

REBUILD SEARCH INDEX
ON cycling.comments;
// end::rebuild[]

// tag::cache[]
ALTER SEARCH INDEX CONFIG
ON cycling.comments 
SET autoCommitTime = 10000;
// end::cache[]

// tag::requesth[]
ALTER SEARCH INDEX CONFIG
ON cycling.comments 
ADD requestHandler[@name='/elevate',@class='solr.SearchHandler', @startup='lazy'] 
WITH $$ {"defaults":[{"echoParams":"explicit"}],"last-components":["elevator"]} $$;
// end::requesth[]

// Policy to merge large segements due to deletes
// tag::delete[]
ALTER SEARCH INDEX CONFIG
ON cycling.comments 
SET indexConfig.mergePolicyFactory[@class='org.apache.solr.index.AutoExpungeDeletesTieredMergePolicyFactory'].bool[@name='mergeSingleSegments'] = true;

ALTER SEARCH INDEX CONFIG
ON cycling.comments 
SET indexConfig.mergePolicyFactory[@class='org.apache.solr.index.AutoExpungeDeletesTieredMergePolicyFactory'].int[@name='maxMergedSegmentMB'] = 1005;

ALTER SEARCH INDEX CONFIG
ON cycling.comments 
SET indexConfig.mergePolicyFactory[@class='org.apache.solr.index.AutoExpungeDeletesTieredMergePolicyFactory'].int[@name='forceMergeDeletesPctAllowed'] = 25;
// end::delete[]

// tag::commit[]
COMMIT SEARCH INDEX
ON cycling.comments;
// end::commit[]

// **CHANGING the SCHEMA**

// tag::add_string_field[]
ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
ADD fields.field[@name='fieldname', @type='StrField', @multiValued = 'false', @indexed='true'];
// end::add_string_field[]

// tag::column_name[]
ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
ADD FIELD record_id;
// end::column_name[]

// tag::rename[]
ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
SET field[@name='fieldname']@name = 'anotherFieldName';
// end::rename[]

// tag::type[]
ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
SET field[@name='fieldname']@type = 'UUIDField';
// end::type[]

// tag::drop_another[]
ALTER SEARCH INDEX SCHEMA
ON cycling.comments 
DROP field anotherFieldName;
// end::drop_another[]

// **CHANGING the CONFIG **
// tag::autocommit[]
ALTER SEARCH INDEX CONFIG
ON cycling.comments 
SET autoCommitTime = 1000;
// end::autocommit[]

// tag::add_text_field[]
ALTER SEARCH INDEX SCHEMA
ON cycling.comments
SET types.fieldType[@name='TextField']@class='org.apache.solr.schema.TextField';

ALTER SEARCH INDEX SCHEMA
ON cycling.comments
SET fields.field[@name='comment']@type='TextField';
// end::add_text_field[]

// tag::drop_text_field[]
ALTER SEARCH INDEX SCHEMA
ON cycling.comments
DROP field comment;

ALTER SEARCH INDEX SCHEMA
ON cycling.comments
DROP types.fieldType[@name='TextField'];
// end::drop_text_field[]

// tag::add_dynamic_text_field[]
ALTER SEARCH INDEX SCHEMA
ON cycling.comments
ADD types.fieldType[@class='org.apache.solr.schema.TextField', @name='TextField']
WITH '{"analyzer":{"class":"org.apache.lucene.analysis.standard.StandardAnalyzer"}}';

ALTER SEARCH INDEX SCHEMA
ON cycling.comments
ADD dynamicField[@name='*fieldname', @type='TextField'];
// end::add_dynamic_text_field[]

RELOAD SEARCH INDEX
ON cycling.comments;

REBUILD SEARCH INDEX
ON cycling.comments;
 date       | comment                                       | commenter
------------+-----------------------------------------------+-----------
 2017-04-01 |        LATE RIDERS SHOULD NOT DELAY THE START |      Alex
 2019-11-05 | The gift certificate for winning was the best |       Amy
 2019-11-05 |       Fastest womens time ever way to go amy! |  Maryanne
 2019-11-05 |                                  Great course |   Michael
 2019-11-05 |                  Can't wait for the next race | Katarzyna
 2019-11-05 |                 Thanks for all your hard work |  Marianne
 2019-11-05 |                                 A for effort! |     Paolo
 2019-11-05 |            Closing ceremony was a little lame |  Rossella
 2019-11-05 |                               Next time guys! |    Marcia
 2019-11-05 |              So many great races thanks y'all |    Steven
 2019-11-05 |                        Go team, you rocked it |      Anna
 2019-11-05 |             Next year the tour of california! |   Melissa
 2019-11-05 |                           Next year for sure! |      Vera

(13 rows)

Date and time queries:

INSERT INTO cycling.comments (
  id,
  created_at
) VALUES (
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  toTimeStamp(toDate(now()))
);
SELECT created_at
FROM cycling.comments
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
LIMIT 1;
INSERT INTO cycling.comments (
  id,
  created_at
) VALUES (
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  toTimeStamp(now())
);
SELECT created_at FROM cycling.comments
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
LIMIT 1;
INSERT INTO cycling.comments (
  id,
  created_at
) VALUES (
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  '2017-04-01'
);
SELECT created_at FROM cycling.comments
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
  AND created_at = '2017-04-01';
INSERT INTO cycling.comments (
  id,
  created_at
) VALUES (
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  '2017-04-01+0000'
);
SELECT created_at FROM cycling.comments
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
  AND created_at = '2017-04-01+0000';
INSERT INTO cycling.comments (
  id,
  created_at
) VALUES (
  e7ae5cf3-d358-4d99-b900-85902fda9bb0,
  '2017-04-01T11:21:59.001+0000'
);
SELECT created_at FROM cycling.comments
WHERE id = e7ae5cf3-d358-4d99-b900-85902fda9bb0
  AND created_at = '2017-04-01T11:21:59.001-0800';

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