0

Suppose I have created a table like this.

CREATE TABLE Vehicle

and insert some documents to this table.

INSERT INTO Vehicle
<< {
    'VIN' : '1N4AL11D75C109151',
    'Type' : 'Sedan',
} >>

So my requirement is to change the table name from Vehicle to VehicleCar and want to change the 'VIN' to 'VID'

How can I do that?

Thanks, Dasun.

dasun_001
  • 139
  • 2
  • 3
  • 12
  • 1
    It looks like Amazon QLDB stores **Documents** rather than using **Columns**. Since `VIN` is just an attribute within that document, you could use `UPDATE` to set a new `VID` value (that is equal to VIN), but it would not remove `VIN`. There also doesn't seem to be an 'Alter Table' command, so you can't rename a table. It's probably easier for you to create a new table and load corrected data into it. – John Rotenstein Jun 10 '21 at 05:19
  • @JohnRotenstein First of all i would like to thank your comment, I just don't want to change the VIN, I just want to change the attribute name. for example i want to change type to CarType something like that. – dasun_001 Jun 10 '21 at 06:39
  • @dasun_001 What John is trying to say is you create a new attribute valled `VID` with the original value from `VIN` and then remove the `VIN` attribute. – Maurice Jun 10 '21 at 07:07
  • Yes, but while copying the `VIN` attribute to `VID` should be easy with an UPDATE, it won't also delete the old `VIN` attribute. Hence, it might be easier to SELECT the data from the table as desired, and INSERT it into a new table with the name you want and the correct fields. – John Rotenstein Jun 10 '21 at 08:11
  • Thank you very much for the knowledge you shared. What I did was just drop the current table and create a new one. – dasun_001 Jun 16 '21 at 02:43

1 Answers1

2

QLDB doesn't currently offer an ALTER TABLE capability. You'd have to DROP the table and re-create it. This counts against your table limits, so don't do it too often.

QLDB is schema-less, so you can change your field names and/or the structure of your documents anytime you want to, simply by writing new revisions to your documents in the new format. The journal will still contain the old revisions, however. If your application has any functionality that uses the history() function to access old revisions, then it needs to be able to gracefully handle variations in the document format.

It is important to note that QLDB is not optimized for scanning large volumes of data. It's optimized for targeted queries against an index using an equality operator. A query like "SELECT * FROM table" will scan the entire table. This is an anti-pattern for QLDB and will not perform well as your ledger grows. So if you change your document format, running a SELECT * and updating every document to the new format may be more work than you realize. First, that SELECT * scan query may time-out or it may be aborted with an Optimistic Concurrency Control exception because another process inserted a document in the table. Second, you'd have to do it in batches of 40 documents at a time because of the limit to the number of documents in a transaction.

All of this is to say that making your application resilient to schema changes is a good idea. :-)

DanB
  • 141
  • 3
  • Thank you very much for the knowledge you shared. What I did was just drop the current table and create a new one. – dasun_001 Jun 16 '21 at 02:42