A simple query gives a syntax error

ScyllaDB
,
1

I have a query that doesnt execute for some reason. Kindly take a look & let me know whats wrong

query = """
      use ks;
      
      CREATE TYPE themes (
        id uuid PRIMARY KEY,
        name text
      );
    
      CREATE TYPE account_settings (
        visibility_public? boolean,
        theme themes
      );
      
      CREATE TABLE IF NOT EXISTS users (
        id uuid,
        email text,
        name text,
        joined_at timestamp,
        settings account_settings,
        theme themes,
        PRIMARY KEY (id, email)
      ) WITH CLUSTERING ORDER BY (joined_at DESC);
    """

This gives an error as follows-
message: “line 3:2 : syntax error…\n”,

Kindly help me figure out whats wrong

2

I fixed the problems with your query, here is the diff:

$ diff -u query-bad.cql query-good.cql 
--- query-bad.cql       2023-06-15 04:51:33.291053233 -0400
+++ query-good.cql      2023-06-15 04:51:38.787156732 -0400
@@ -1,13 +1,13 @@
 use ks;
 
 CREATE TYPE themes (
-  id uuid PRIMARY KEY,
+  id uuid,
   name text
 );
 
 CREATE TYPE account_settings (
-  visibility_public? boolean,
-  theme themes
+  visibility_public boolean,
+  theme frozen<themes>
 );
 
 CREATE TABLE IF NOT EXISTS users (
@@ -18,4 +18,4 @@
   settings account_settings,
   theme themes,
   PRIMARY KEY (id, email)
-) WITH CLUSTERING ORDER BY (joined_at DESC);
+) WITH CLUSTERING ORDER BY (email DESC);

Let me explain the fixes:

  • User Defined Types cannot have a PRIMARY KEY.
  • ? is invalid character in a field name (of a type or table).
  • Types embedded in another type must be frozen.
  • Only clustering columns may appear in an ORDER BY clause.
3

thankyou so much for your input sir, this helps a lot. I couldnt find the above mentioned points in the docs. Since a UDT as a whole can be used as a Primary Key along the docs I figured individual fields inside UDTs can be the same too. Also I didnt know about the frozen usage in embedded UDTs since the docs suggested they werent needed for higher versions.
I really appreciate the help.
Thankyou once again

Also if possible id also like to ask for some advise. Do you think scylla would suit for a social network applications primary db? Since ive been advised against it. Due to scylla being good at dealing with time based data, hence it was made known to me that the data attributes are what enable high performance & not the db in itself. Say if you use snowflakes as id’s then it gives higher throughput to query against & not in other case. There also might be some query restrictions in scylla due to clustering columns operations being permitted only on things as seen above.
Overall do you have any suggestions?

4

Do you think scylla would suit for a social network applications primary db?

I don’t see why not.

Due to scylla being good at dealing with time based data, hence it was made known to me that the data attributes are what enable high performance & not the db in itself.

I don’t really understand how the data attributes enable high performance. For sure, correct data modelling, done in a way to suit the DB which is used, goes a long way to ensure good performance. But the underlying DB also has a huge part in it.

Say if you use snowflakes as id’s then it gives higher throughput to query against & not in other case.

What data type your ID is and how you compute it, ScyllaDB doesn’t care. You have to choose your keys such that it best fits your use-case and query patterns.

There also might be some query restrictions in scylla due to clustering columns operations being permitted only on things as seen above.

Yes, ScyllaDB has a quite restrictive data-model, as it was designed around performance. Coming from an SQL DB, you might feel it is too restrictive. That said, any use-case can be made to work in it, but it does need deliberate data-modeling design, to fit the underlying model well and to extract maximum performance.

Overall do you have any suggestions?

Your use-case is too broad for me to come up with any specific suggestions. I recommend you check out the ScyllaDB Essentials and the ScyllaDB Data Modeling courses on ScyllaDB University.

1 Like
5

thankyour for your advise. This sure does help a lot. Really appreciate it.
Thanks once again.