1

I try to work in CartoDB with data I import from CSVs (or xlsx, see below). The data is 24.1 MB in ASCII CSV, 10 MB in xlsx.

I spent quite some time shrinking my data's size, and I was happy to upload less than 25 MB (the limit of my free academic account). I am puzzled by what happened next. I could delete two longish string columns (latitude and longitude, as cartodb georeferenced it automatically). I could convert a column of years to numbers from strings, which should only shrink the size of the table. I could convert a column of dates string to date, which should shrink it further. But when I would do one final operation I need, the conversion of an intensity measure of my temporal heat map from string to number, which can only shrink the size further, THEN cartodb complains that I am over the limit of my account? I spent hours deleting my tables and importing them again, twice in xlsx where I thought numbers will be numbers from the get-go, but that import even fails completely. I would rather not pay for an upgrade until I understand what is going on and why it is necessary.

I have also mixed feelings about going over the limit because of the created_at and updated_at columns, which cartodb creates automatically and does not let us delete…

Any thoughts on how to work around this? Any experience with this behavior?

1

About the DB sizes and Excel import failures:

  • Trying to import the file in XLSX fails after a while. In production we have monitoring services that will kill imports taking too much and/or going out of memory. Importing from Excel is terrible because the internal format forces you to read the whole file into memory before being able to export in CSV (we always export XLSX in CSV). And our XLSX is quite bit, on my dev machine LibreOffice struggled for minutes opening it, and exporting it to CSV. So probably CartoDB killed it.

  • I uploaded the CSV export instead, and it imported in no time successfully. Once uploaded, it takes almost 39MB. Killing the lat/lon columns went down to 35. Changing to numbers actually made them grow, but a mere 2MB. Exporting to shapefile and importing again "with types" gave me around 37MB too.

  • VACUUM xxxx does't do magic, so the table is really taking those MB. In other scenarios might go down in size, but after a vacuum the size keeps the same, so it takes what it takes to store in PostgreSQL numbers, datetimes and Geometries with points of your 243k rows.

  • created_at and updated_at, along with cartodb_id are mandatory fields for cartodb, it's a software limitation/restriction, sorry.

In summary, that dataset is too big for your 25MB account, sorry.

About the Excel failed immport too, we are working on improving the system so it "fails earlier" when the dataset gets too big to fit in memory, or will likely be killed due to excessive resource usage. It is an easy to use format but painful to work with for data-processing.

  • Thanks. It is still frustrating that changing the type of my year and decision_date columns from string to number and date does work, even though the table is over the limit. It is frustrating that the hufvalue columns fails then, but I understand that it is about my luck running out, I already got some leeway over the limit. More importantly, I hope I could cut the URL column substantially with some help along these lines: gis.stackexchange.com/q/91402/28426 – László Mar 31 '14 at 14:53
  • 1
    Yes, in theory should be over quota on all, but somehow allows some. Whenever I have time I'll take a look. About the URL point, I've notified my colleages to see who can help you (I currently only do backend coding). – Kartones Mar 31 '14 at 15:01
  • Thanks again, I am looking forward to the team having a little time and maybe fix my problem with the URL. – László Apr 1 '14 at 7:25
  • FYI, I got help about the URL, thanks! I could shrink the file I upload by 10 MB. I still cannot change the type of the variable I want though, while I can for others. This is frustrating. Btw, I would consider deleting another column or pay for space, but I am not keen, as I have a poor understanding of where all the space went. I upload 14 MB. I even delete two string columns once you make geo out of them. I convert strings to numbers. Where does the space go? In any case, the error message tells me I am 4 MB less above the limit than yesterday. After uploading 10 MB less… – László Apr 1 '14 at 9:58
1

Have you tried doing a PostgreSQL VACUUM?

Usually with that you will regain some space, as for performance CartoDB's Postgres does not automatically perform it after actions like column type change*.

Importing from XLSX is the same as importing from CSV (in fact, we convert first to CSV and then import), so column types are lost. Other formats like Shapefile do keep the types.

If an Excel file is failing please either link it from here or if is private drop me an email to diegom@cartodb.com so I can take a look at why it doesn't works. If is due to going over quota (that is, the import would result in a table too big for your account' space) it should notify you of it after trying to import.

Regards,

*But it does after actions like importing a dataset, or from time to time with a scheduled cron in the servers.

  • I have not tried doing a VACUUM. So it might clean up after the successful type changes, and then maybe the failed one will also succeed? I am a bit confused why this type changed failed, then, if other operations (unless followed by VACUUM) would also result in my being over the limit already, but I'll try. I can send you the Excel file. The two imports never succeeded or failed… – László Mar 31 '14 at 10:05
  • I am getting an "only table or database owner can vacuum it" error after the following: "sandorl.cartodb.com/api/v2/sql?q=VACUUM&api_key={MYKEY}" What is there to do? – László Mar 31 '14 at 10:58
  • 1
    Yes, I didn't specify it, but you should do "VACUUM tablename", else tries with all tables and that's why you get error messages as system ones aren't yours. I'm checking what's the size of the table from a clean upload, etc. – Kartones Mar 31 '14 at 12:15
  • But why didn't any of the tables succeed? – László Mar 31 '14 at 12:26
  • Sorry, I think it does work for my table. The curious thing is, that I am still 16 MB over my quota now and the changing of the data type does not work. This is the last thing I need before I am done with this table. Everything else worked without my upgrading. Do I really have to pay at this point? – László Mar 31 '14 at 13:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.