Migrating from Regular to Big Tables - some tips & advice

Recently I did a rather large migration job - moving tens of thousands of rows from a regular Glide Table to a Big Table. As well as moving the data, there were hundreds of column references, actions, screens and components that needed reconfiguring.

Below are some of the lessons I learned along the way, and some advice for others that might need to do this in future:

Migrating from a regular Glide Table to a Big Table - some lessons learned and a bit of advice:

  • Make sure you have a copy of the App open in the builder on a second monitor. This is basically essential.
  • Make sure you have enabled the “More Computed Columns with Big Tables” preview feature.
  • Spend some time preparing the new Big Table:
    • Create the table, and manually create all the columns including the computed columns. Ensure the column names are identical to those in the source table (copy/paste is a good idea)
    • Export the data from the old table, and prepare it for import into the new table. Depending on the amount of data, you may need to break it up into multiple files (max import size is 4.8MB). In my case, I wrote some Apps Script to break the data up into separate files.
    • Do a few import dry runs to ensure the data imports cleanly. Pay particular attention to dates. Tip: Use ODS format instead of CSV - it seems to give better results when dates are involved.
  • Also spend some prep time examining existing column references. Things to look out for that might not be supported with Glide Big Tables, and might need some creative workarounds:
    • Filters on Collections
    • Relations and/or queries into the Big Table from other tables
    • Pay close attention to all existing computed columns in the table to be migrated. Check all uses on those columns carefully, and uses on the uses.
    • Also pay close attention to computed columns in other tables that reference columns in the table to be migrated - especially those that reference computed columns. Some of these may not be supported in the new Big Table.
  • Reconfiguring things - recommended order:
    • Start with Actions. Find all actions that reference the table, duplicate them one by one and reconfigure the duplicates to use the new table. Use the copy of the App as a reference.
    • Next look for screens and/or collections that use the table (or relations/queries into it) as a source. Work through these one by one and reconfigure. Often they will be using associated actions, so if you’ve already created the actions it will be a simple matter of switching those over.
    • Watch out for custom collections - when you change the source of a custom collection it breaks every single component, so they can be quite tedious (I had a bunch of them to contend with)
    • Next step: go through every column in the source table one by one with Find uses, and reconfigure them. By the time you get to this step there shouldn’t be too many left, as you will have picked up most of them with the Actions & Screen components. Hide each column as you’ve dealt with it so that you don’t get confused about where you are at.
    • Pay careful attention to queries and relations into the table. Reconfiguring these will usually break dependant columns - lookups, rollups, single values, etc. So they will most likely need reconfiguring as well.
    • Make copious notes as you go. Otherwise you will forget. Guaranteed.
    • If you run into the dreaded “This column is not supported” issue, don’t waste time looking for a workaround. Just make a note of it and move on, then discuss options afterwards with the client. In my case, it turned out that several of these weren’t even needed.
12 Likes

Bookmarked.

This kind of sounds like a nightmare. If ever I find myself in this situation, which I both hope and dread for different reasons, then I think I might have to try to bribe you. The client would foot the bill of course :yum:

It was quite tedious. The nice thing is the end result was a huge performance boost for the App, both in the player and the builder. It was almost unusable in the builder beforehand.

1 Like

Glide engineers are keeping an eye on you. “Okay guys, we need to scale our virtual machines, it’s Darren again with another one of his monster apps.”

3 Likes

I’ve run into my first (probably not the last) substantial roadblock toward converting to Big Tables - a relation pointed to a split text column.

image

Items IDs are stored as a CSV in the Trips table, generated from a multi-select choice component. I’m currently doing rollups on this relation and other columns depend on the results of those rollups, so I need a new path forward.

My understanding is that storing the Item IDs this way is a lighter alternative to having a separate table with rows for all the Trip ID - Item ID records, and I don’t really know how I’d manage that. The other ways I can think of would be more advanced trebuchet methods using JSON, but that probably wouldn’t help either, just trading one type of computed column for another.

Update: I used the split Trip items array in a Make array column, and the relation column didn’t balk at that. Back in business

1 Like

I’d be interested in hearing more about any and all workarounds you have employed for Big Table limitations. The structure of the app I’m attempting to convert is not even close to workable within the allowed columns. Also, the video in this post won’t play for me and I’m keen to learn about this approach.

Fixed the video.

Thank you!

1 Like