KB484353: Presto database instance shows the data type of a string column as VarChar (0) in the Warehouse Catalog

Description 

In the Warehouse Catalog, the data type of the string column from a Presto table displays as VarChar (0). The following can be seen via Developer. 
 

Why is this happening?

If the table in Presto defines the column as VarChar (300) (e.g., 

create table testtable (col1 varchar (300)) ;

 create table testtable (col1 string) ;

Workaround

• In the Warehouse Catalog, go to Options. 
  

• Go to Catalog > Read Settings and click Settings.
  

• Replace the column catalog SQL with the following string:
  

  
  SELECT table_schema NAME_SPACE, table_name TAB_NAME, column_name COL_NAME,  
  case when data_type like 'char(%' then 'char' when data_type like 'varchar(%' then 'varchar' when data_type like 'decimal(%' then 'decimal' else data_type end DATA_TYPE,  
  case when data_type = 'varchar' then '335' else split_part(replace(replace(substr(data_type, position('(' in data_type)), '(', ''), ')', ''), ',',1) end DATA_LEN,  
  case when data_type = 'varchar' then '335' else split_part(replace(replace(substr(data_type, position('(' in data_type)), '(', ''), ')', ''), ',',1) end DATA_PREC,  
  split_part(replace(replace(substr(data_type, position('(' in data_type)), '(', ''), ')', ''), ',',2) as DATA_SCALE  
  FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name in (#TABLE_LIST#) and TABLE_SCHEMA = '#Login_Name#' ORDER BY 1, 2, 3
  

  
   
  

• Click OK, refresh the tables, and then read the table structure. The string column has the correct value set in the catalog column SQL: