Insert a record into Cassandra table

user_11985669 0
Viewed 64

I created a table, university_by_person, as the following:

CREATE TABLE university_by_person (
    person_name frozen<name>,
    university_id uuid,
    university_name text,
    university_phone text,
    university_address frozen<address>,
    PRIMARY KEY ((person_name), university_id)
) WITH CLUSTERING ORDER BY (university_id ASC)
    AND comment = 'Q1. Find universities(including university info) near a person';

And I also self-defined the TYPE name and address as the following:

CREATE TYPE name (
    first_name text,
    last_name text
)

CREATE TYPE address (
    street text,
    city text,
    state_or_province text,
    postal_code text,
    country text
)

However, now when I tried to insert a record into this table, and hit ENTER, the cmd prompt only returned '...'. It seems the cmd thought my command was not finished? But I did have a ';' at the end. How could I fix it here?

My insert command:

INSERT INTO  university_by_person
  (person_name, university_id, university_name, university_phone, university_address)
  VALUES (
    {first_name: 'Meryl', last_name: 'Streep'},
    e7ae5cf3-d358-4d99-b900-85902fda9bb0,
    'University of Toronto',
    '416-963-0086',
    {street: '27 King's College Cir', city: 'Toronto', state_or_province: 'Ontario', postal_code: 'M5S 1A1', country: 'Canada'}
  );
cassandra
1 Answers

The issue is that the value for street in the address is a string which contains a single quote (') character:

street: '27 King's College Cir'

The ... line in cqlsh indicates that it is expecting more input since the string got "terminated" prematurely.

To use an apostrophe within a string, you need to escape with another single quote so it looks like:

street: '27 King''s College Cir'

This is the full corrected CQL statement:

INSERT INTO university_by_person
  (person_name, university_id, university_name, university_phone, university_address)
  VALUES (
    {first_name: 'Meryl', last_name: 'Streep'},
    e7ae5cf3-d358-4d99-b900-85902fda9bb0,
    'University of Toronto',
    '416-963-0086',
    {street: '27 King''s College Cir', city: 'Toronto', state_or_province: 'Ontario', postal_code: 'M5S 1A1', country: 'Canada'}
  );

should execute as expected. The value of the column should also include the apostrophe:

cqlsh:stackoverflow> SELECT university_address FROM university_by_person
  WHERE person_name = {first_name: 'Meryl', last_name: 'Streep'};

 university_address
------------------------------------------------------------------------------------------------------------------------------
 {street: '27 King''s College Cir', city: 'Toronto', state_or_province: 'Ontario', postal_code: 'M5S 1A1', country: 'Canada'}

For more info and alernatives, see Escaping characters. Cheers!

user_42695350
Related