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.index.sasi.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" <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 > >
