Checking a columns datatype
Does anyone know if there is a way to check a column's datatype? I've noticed sometimes if bad data gets uploaded to a datasource it can change the columns type (for example from an int to a string) and I wanted to write a query to catch that faster. Anyone have any ideas?
Best Answer
-
A few different thoughts here:
* From the Data Center, you can hover over the "rows/columns" for a dataset and a card will pop up with column names + generic types (i.e. "text", "numeric", "dates")
* If you open up a DataFusion (Data Center => click on the little down arrow on the "New DataSet" button, then click "Combine DataSets"), it will show you the "real" type for each column (i.e. "STRING", "LONG", "DOUBLE", "DATE", "DATETIME")
* In a MySQL DataFlow, you can see the column names/types with the following query, obviously replace "my_table" with your own table name:
SELECT
column_name,
column_type # or data_type
FROM information_schema.columns
WHERE table_name='my_table';* If you're having frequent problems with bad data getting uploaded, in a DataFlow or Magic ETL, you could add an explicit step/action/transform to set the column data type. That way if bad data is uploaded, the transform will try to convert it to the correct type and if it can't, you'll get an email notification that there's bad data. If you know the format of the bad data, you could also put a transform that replaces it with a NULL value or converts it manually to the correct type
* You can also do something similar in a Beast Mode; if I have an integer value that sometimes has an empty string that gets uploaded and changes the column type to string, you could do a beast mode like:
`column_that_should_be_integer` + 0
Trying to add "0" to the integer column will automatically try to convert every value in the string column to an integer.
* If the data is coming from a Workbench job, there's also a way to "set" the type for each column and values will be coerced as they are uploaded.Just a few thoughts, hopefully something there works for you!
5
Answers
-
A few different thoughts here:
* From the Data Center, you can hover over the "rows/columns" for a dataset and a card will pop up with column names + generic types (i.e. "text", "numeric", "dates")
* If you open up a DataFusion (Data Center => click on the little down arrow on the "New DataSet" button, then click "Combine DataSets"), it will show you the "real" type for each column (i.e. "STRING", "LONG", "DOUBLE", "DATE", "DATETIME")
* In a MySQL DataFlow, you can see the column names/types with the following query, obviously replace "my_table" with your own table name:
SELECT
column_name,
column_type # or data_type
FROM information_schema.columns
WHERE table_name='my_table';* If you're having frequent problems with bad data getting uploaded, in a DataFlow or Magic ETL, you could add an explicit step/action/transform to set the column data type. That way if bad data is uploaded, the transform will try to convert it to the correct type and if it can't, you'll get an email notification that there's bad data. If you know the format of the bad data, you could also put a transform that replaces it with a NULL value or converts it manually to the correct type
* You can also do something similar in a Beast Mode; if I have an integer value that sometimes has an empty string that gets uploaded and changes the column type to string, you could do a beast mode like:
`column_that_should_be_integer` + 0
Trying to add "0" to the integer column will automatically try to convert every value in the string column to an integer.
* If the data is coming from a Workbench job, there's also a way to "set" the type for each column and values will be coerced as they are uploaded.Just a few thoughts, hopefully something there works for you!
5 -
This worked great! Thanks for the help. I wanted to avoid coercing the data to a specific data type, because then if there was a problem with the data it would be more difficult for me to find, but I can use this to make a solution. Thanks a lot!
1
Categories
- All Categories
- 1.7K Product Ideas
- 1.7K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 295 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.8K Transform
- 97 SQL DataFlows
- 608 Datasets
- 2.1K Magic ETL
- 3.8K Visualize
- 2.4K Charting
- 710 Beast Mode
- 50 App Studio
- 39 Variables
- 668 Automate
- 170 Apps
- 446 APIs & Domo Developer
- 45 Workflows
- 7 DomoAI
- 33 Predict
- 13 Jupyter Workspaces
- 20 R & Python Tiles
- 391 Distribute
- 111 Domo Everywhere
- 274 Scheduled Reports
- 6 Software Integrations
- 116 Manage
- 113 Governance & Security
- Domo Community Gallery
- 31 Product Releases
- 9 Domo University
- 5.3K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 103 Community Announcements
- 4.8K Archive