The problem is you’re not using a query first design. I would recommend first reading chapter 5 of Cassandra: The Definitive Guide by Jeff Carpenter and Eben Hewitt. It’s available free online at this link <https://books.google.com/books?id=uW-PDAAAQBAJ&pg=PA79&lpg=PA79&dq=jeff+carpenter+chapter+5&source=bl&ots=58bUYyNM-J&sig=ACfU3U22U58-QPlz6kzo0zziNF-bP30l4Q&hl=en&sa=X&ved=2ahUKEwi0n-nWzajgAhXnHzQIHf6jBJIQ6AEwAXoECAgQAQ#v=onepage&q=jeff%20carpenter%20chapter%205&f=false> .
Kenneth Brotman From: Peter Heitman [mailto:pe...@heitman.us] Sent: Wednesday, February 06, 2019 6:33 PM To: user@cassandra.apache.org Subject: Re: SASI queries- cqlsh vs java driver Yes, I "know" that allow filtering is a sign of a (possibly fatal) inefficient data model. I haven't figured out how to do it correctly yet On Thu, Feb 7, 2019, 7:59 AM Kenneth Brotman <kenbrot...@yahoo.com.invalid> wrote: Exactly. When you design your data model correctly you shouldn’t have to use ALLOW FILTERING in the queries. That is not recommended. Kenneth Brotman From: Peter Heitman [mailto:pe...@heitman.us] Sent: Wednesday, February 06, 2019 6:09 PM To: user@cassandra.apache.org Subject: Re: SASI queries- cqlsh vs java driver You are completely right! My problem is that I am trying to port code for SQL to CQL for an application that provides the user with a relatively general search facility. The original implementation didn't worry about secondary indexes - it just took advantage of the ability to create arbitrarily complex queries with inner joins, left joins, etc. I am reimplimenting it to create a parse tree of CQL queries and doing the ANDs and ORs in the application. Of course once I get enough of this implemented I will have to load up the table with a large data set and see if it gives acceptable performance for our use case. On Wed, Feb 6, 2019, 8:52 PM Kenneth Brotman <kenbrotman@yahoo.cominvalid> wrote: Isn’t that a lot of SASI indexes for one table. Could you denormalize more to reduce both columns per table and SASI indexes per table? Eight SASI indexes on one table seems like a lot. Kenneth Brotman From: Peter Heitman [mailto:pe...@heitman.us] Sent: Tuesday, February 05, 2019 6:59 PM To: user@cassandra.apache.org Subject: Re: SASI queries- cqlsh vs java driver The table and secondary indexes look generally like this. Note that I have changed the names of many of the columns to be generic since they aren't important to the question as far as I know. I left the actual names for those columns that I've created SASI indexes for. The query I use to try to create a PreparedStatement is: SELECT sql_id, type, cpe_id, serial, product_class, manufacturer, sw_version FROM mytable WHERE serial IN :v0 LIMIT :limit0 ALLOW FILTERING the schema cql statements are: CREATE TABLE IF NOT EXISTS mykeyspace.mytable ( id text, sql_id bigint, cpe_id text, sw_version text, hw_version text, manufacturer text, product_class text, manufacturer_oui text, description text, periodic_inform_interval text, restricted_mode_enabled text, restricted_mode_reason text, type text, model_name text, serial text, mac text, text, generic0 timestamp, household_id text, generic1 int, generic2 text, generic3 text, generic4 int, generic5 int, generic6 text, generic7 text, generic8 text, generic9 text, generic10 text, generic11 timestamp, generic12 text, generic13 text, generic14 timestamp, generic15 text, generic16 text, generic17 text, generic18 text, generic19 text, generic20 text, generic21 text, generic22 text, generic23 text, generic24 text, generic25 text, generic26 text, generic27 text, generic28 int, generic29 int, generic30 text, generic31 text, generic32 text, generic33 text, generic34 text, generic35 int, generic36 int, generic37 int, generic38 int, generic39 text, generic40 text, generic41 text, generic42 text, generic43 text, generic44 text, generic45 text, PRIMARY KEY (id) ); CREATE INDEX IF NOT EXISTS bv_sql_id_idx ON mykeyspace.mytable (sql_id); CREATE CUSTOM INDEX IF NOT EXISTS bv_serial_idx ON mykeyspace.mytable (serial) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'mode': 'CONTAINS', 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive': 'false'}; CREATE CUSTOM INDEX IF NOT EXISTS bv_cpe_id_idx ON mykeyspace.mytable (cpe_id) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'mode': 'CONTAINS', 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive': 'false'}; CREATE CUSTOM INDEX IF NOT EXISTS bv_mac_idx ON mykeyspace.mytable (mac) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'mode': 'CONTAINS', 'analyzer_class': 'orgapache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive': 'false'}; CREATE CUSTOM INDEX IF NOT EXISTS bv_manufacturer_idx ON mykeyspace.mytable (manufacturer) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'mode': 'CONTAINS', 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive': 'false'}; CREATE CUSTOM INDEX IF NOT EXISTS bv_manufacturer_oui_idx ON mykeyspace.mytable (manufacturer_oui) USING 'org.apache.cassandra.index.sasiSASIIndex' WITH OPTIONS = {'mode': 'CONTAINS', 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive': 'false'}; CREATE CUSTOM INDEX IF NOT EXISTS bv_hw_version_idx ON mykeyspace.mytable (hw_version) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'mode': 'CONTAINS', 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive': 'false'}; CREATE CUSTOM INDEX IF NOT EXISTS bv_sw_version_idx ON mykeyspace.mytable (sw_version) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'mode': 'CONTAINS', 'analyzer_class': 'org.apache.cassandra.index.sasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive': 'false'}; CREATE CUSTOM INDEX IF NOT EXISTS bv_household_id_idx ON mykeyspace.mytable (household_id) USING 'org.apache.cassandra.index.sasi.SASIIndex' WITH OPTIONS = {'mode': 'CONTAINS', 'analyzer_class': 'org.apache.cassandra.indexsasi.analyzer.NonTokenizingAnalyzer', 'case_sensitive': 'false'}; On Tue, Feb 5, 2019 at 3:33 PM Oleksandr Petrov <oleksandr.pet...@gmail.com> wrote: Could you post full table schema (names obfuscated, if required) with index creation statements and queries? On Mon, Feb 4, 2019 at 10:04 AM Jacques-Henri Berthemet <jacques-henri.berthe...@genesys.com> wrote: I’m not sure why it`s not allowed by the Datastax driver, but maybe you could try to use OR instead of IN? SELECT blah FROM foo WHERE <indexed column> = :val1 OR <indexed column> = :val2 ALLOW FILTERING It should be the same as IN query, but I don’t if it makes a difference for performance. From: Peter Heitman <pe...@heitman.us> Reply-To: "user@cassandra.apache.org" <user@cassandra.apache.org> Date: Monday 4 February 2019 at 07:17 To: "user@cassandra.apache.org <mailto:u...@cassandraapache.org> " <user@cassandra.apache.org> Subject: SASI queries- cqlsh vs java driver When I create a SASI index on a secondary column, from cqlsh I can execute a query SELECT blah FROM foo WHERE <indexed column> IN ('mytext') ALLOW FILTERING; but not from the java driver: SELECT blah FROM foo WHERE <indexed column> IN :val ALLOW FILTERING Here I get an exception com.datastax.driver.core.exceptions.InvalidQueryException: IN predicates on non-primary-key columns (<indexed column>) is not yet supported at com.datastax.driver.core.exceptions.InvalidQueryException.copy(InvalidQueryException.java:49) ~[cassandra-driver-core-3.6.0.jar:na] Why are they different? Is there anything I can do with the java driver to get past this exception? Peter -- alex p
