I am trying to practice Cassandra using this example (under Composite Columns paragraph):
So, I have created table tweets and it looks like following:
cqlsh:twitter> SELECT * from tweets;
tweet_id | author | body
--------------------------------------+-------------+--------------
73954b90-baf7-11e4-a7d0-27983e9e7f51 | gwashington | I chopped...
(1 rows)
Now I am trying to populate timeline, which is a related table using CQL and I am not sure how to do it. I have tried SQL approach, but it did not work:
cqlsh:twitter> INSERT INTO timeline (user_id, tweet_id, author, body) SELECT 'gmason', 73954b90-baf7-11e4-a7d0-27983e9e7f51, author, body FROM tweets WHERE tweet_id = 73954b90-baf7-11e4-a7d0-27983e9e7f51;
Bad Request: line 1:55 mismatched input 'select' expecting K_VALUES
So I have two questions:
Thanks.
EDIT:
This is explanation for my question #2 above (the picture is taken from here):
tldr;
Use cqlsh COPY
to export tweets
, modify the file, use COPY
to import timeline
.
Use cassandra-cli to verify the physical structure.
Long version...
COPY
command in cqlsh.I followed the similar examples found here. After creating the tweets
and timeline
tables in cqlsh, I inserted rows into tweets
as indicated. My tweets
table then looked like this:
aploetz@cqlsh:stackoverflow> SELECT * FROM tweets;
tweet_id | author | body
--------------------------------------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------
05a5f177-f070-486d-b64d-4e2bb28eaecc | gmason | Those gentlemen, who will be elected senators, will fix themselves in the federal town, and become citizens of that town more than of your state.
b67fe644-4dbe-489b-bc71-90f809f88636 | jmadison | All men having power ought to be distrusted to a certain degree.
819d95e9-356c-4bd5-9ad0-8cd36a7aa5e1 | gwashington | To be prepared for war is one of the most effectual means of preserving peace.
I then exported them like this:
aploetz@cqlsh:stackoverflow> COPY tweets TO '/home/aploetz/tweets_20150223.txt'
WITH DELIMITER='|' AND HEADER=true;
3 rows exported in 0.052 seconds.
I then edited the tweets_20150223.txt file
, adding a user_id
column on the front and copying a couple of rows, like this:
userid|tweet_id|author|body
gmason|05a5f177-f070-486d-b64d-4e2bb28eaecc|gmason|Those gentlemen, who will be elected senators, will fix themselves in the federal town, and become citizens of that town more than of your state.
jmadison|b67fe644-4dbe-489b-bc71-90f809f88636|jmadison|All men having power ought to be distrusted to a certain degree.
gwashington|819d95e9-356c-4bd5-9ad0-8cd36a7aa5e1|gwashington|To be prepared for war is one of the most effectual means of preserving peace.
jmadison|819d95e9-356c-4bd5-9ad0-8cd36a7aa5e1|gwashington|To be prepared for war is one of the most effectual means of preserving peace.
ahamilton|819d95e9-356c-4bd5-9ad0-8cd36a7aa5e1|gwashington|To be prepared for war is one of the most effectual means of preserving peace.
ahamilton|05a5f177-f070-486d-b64d-4e2bb28eaecc|gmason|Those gentlemen, who will be elected senators, will fix themselves in the federal town, and become citizens of that town more than of your state.
I saved that file as timeline_20150223.txt
, and imported it into the timeline
table, like this:
aploetz@cqlsh:stackoverflow> COPY timeline FROM '/home/aploetz/timeline_20150223.txt'
WITH DELIMITER='|' AND HEADER=true;
6 rows imported in 0.016 seconds.
timeline
will be a wide-row table, partitioning on user_id
and then clustering on tweet_id
. I verified the "under the hood" structure by running the cassandra-cli tool, and list
ing the timeline
column family (table). Here you can see how the rows are partitioned by user_id
, and each column has the tweet_id
uuid as a part of its name:-
[default@stackoverflow] list timeline;
Using default limit of 100
Using default cell limit of 100
-------------------
RowKey: ahamilton
=> (name=05a5f177-f070-486d-b64d-4e2bb28eaecc:, value=, timestamp=1424707827585904)
=> (name=05a5f177-f070-486d-b64d-4e2bb28eaecc:author, value=676d61736f6e, timestamp=1424707827585904)
=> (name=05a5f177-f070-486d-b64d-4e2bb28eaecc:body, value=54686f73652067656e746c656d656e2c2077686f2077696c6c20626520656c65637465642073656e61746f72732c2077696c6c20666978207468656d73656c76657320696e20746865206665646572616c20746f776e2c20616e64206265636f6d6520636974697a656e73206f66207468617420746f776e206d6f7265207468616e206f6620796f75722073746174652e, timestamp=1424707827585904)
=> (name=819d95e9-356c-4bd5-9ad0-8cd36a7aa5e1:, value=, timestamp=1424707827585715)
=> (name=819d95e9-356c-4bd5-9ad0-8cd36a7aa5e1:author, value=6777617368696e67746f6e, timestamp=1424707827585715)
=> (name=819d95e9-356c-4bd5-9ad0-8cd36a7aa5e1:body, value=546f20626520707265706172656420666f7220776172206973206f6e65206f6620746865206d6f73742065666665637475616c206d65616e73206f662070726573657276696e672070656163652e, timestamp=1424707827585715)
-------------------
RowKey: gmason
=> (name=05a5f177-f070-486d-b64d-4e2bb28eaecc:, value=, timestamp=1424707827585150)
=> (name=05a5f177-f070-486d-b64d-4e2bb28eaecc:author, value=676d61736f6e, timestamp=1424707827585150)
=> (name=05a5f177-f070-486d-b64d-4e2bb28eaecc:body, value=54686f73652067656e746c656d656e2c2077686f2077696c6c20626520656c65637465642073656e61746f72732c2077696c6c20666978207468656d73656c76657320696e20746865206665646572616c20746f776e2c20616e64206265636f6d6520636974697a656e73206f66207468617420746f776e206d6f7265207468616e206f6620796f75722073746174652e, timestamp=1424707827585150)
-------------------
RowKey: gwashington
=> (name=819d95e9-356c-4bd5-9ad0-8cd36a7aa5e1:, value=, timestamp=1424707827585475)
=> (name=819d95e9-356c-4bd5-9ad0-8cd36a7aa5e1:author, value=6777617368696e67746f6e, timestamp=1424707827585475)
=> (name=819d95e9-356c-4bd5-9ad0-8cd36a7aa5e1:body, value=546f20626520707265706172656420666f7220776172206973206f6e65206f6620746865206d6f73742065666665637475616c206d65616e73206f662070726573657276696e672070656163652e, timestamp=1424707827585475)
-------------------
RowKey: jmadison
=> (name=819d95e9-356c-4bd5-9ad0-8cd36a7aa5e1:, value=, timestamp=1424707827585597)
=> (name=819d95e9-356c-4bd5-9ad0-8cd36a7aa5e1:author, value=6777617368696e67746f6e, timestamp=1424707827585597)
=> (name=819d95e9-356c-4bd5-9ad0-8cd36a7aa5e1:body, value=546f20626520707265706172656420666f7220776172206973206f6e65206f6620746865206d6f73742065666665637475616c206d65616e73206f662070726573657276696e672070656163652e, timestamp=1424707827585597)
=> (name=b67fe644-4dbe-489b-bc71-90f809f88636:, value=, timestamp=1424707827585348)
=> (name=b67fe644-4dbe-489b-bc71-90f809f88636:author, value=6a6d616469736f6e, timestamp=1424707827585348)
=> (name=b67fe644-4dbe-489b-bc71-90f809f88636:body, value=416c6c206d656e20686176696e6720706f776572206f7567687420746f206265206469737472757374656420746f2061206365727461696e206465677265652e, timestamp=1424707827585348)
4 Rows Returned.
Elapsed time: 35 msec(s).
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments