How do I change the field type for a column on an existing table?

hanmari
hanmari Member
edited October 2022 in Magic ETL

My company recently released a new version of our online platform and I am mapping its new database fields to my already existing Domo reporting tables. In the previous system we used a simple integer as the primary key for all of our tables. In the new version we have switched to UUID values for many of our tables. Consequently, when I import into Domo I need to put UUID values into what was previously an integer field. The easiest way to make this happen is to alter the existing Domo tables and change the integer fields to text. I'm hoping this will allow me to continue to use the existing Domo tables and minimize any impact on associated data transforms and cards.

Is there a way to edit the schema for an existing Domo table and transform a single column? Changing an integer to text should be simple. I would love to perform a one-time alteration rather than build a separate import and transformation workflow to make my new data schema integrate with my historical data.

Tagged:

Best Answer

  • hanmari
    hanmari Member
    Answer ✓

    It turns out that I did not need to manually alter the table that I was importing into. I changed the SQL in my import for the table to pull from the new data source and tried putting the new UUID values directly into the existing integer field. The Domo importer was smart enough to detect that non-integer values were being added to the integer field. It automatically changed the data definition for the identifier column from an integer to a string to accommodate the UUID values I started passing in.

Answers

  • MichelleH
    MichelleH Coach

    @hanmari You should be able to add an Alter Columns tile to your existing dataflows to change the field from an integer to text. If this field is used in existing joins, then you will need to change the datatype for both sides of the join. I would not anticipate that this would cause a significant impact to existing cards, though I recommend using the "Cards" tab on the output dataset to verify that none of them break.

  • hanmari
    hanmari Member
    Answer ✓

    It turns out that I did not need to manually alter the table that I was importing into. I changed the SQL in my import for the table to pull from the new data source and tried putting the new UUID values directly into the existing integer field. The Domo importer was smart enough to detect that non-integer values were being added to the integer field. It automatically changed the data definition for the identifier column from an integer to a string to accommodate the UUID values I started passing in.

Categories