Checking a columns datatype

Steven

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?

quinnj

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!

quinnj

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!

kshah008

@Steven, did quinnj's reply help answer your question? 

Steven

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!