Data Type Functions

Introduced in release: 1.14

Drill supports the following functions that return data type information:

sqlTypeOf()
Returns the data type of a column (using the SQL names) whether the column is NULL or not. You can use the SQL name in a CAST statement, for example:

          sqlTypeOf( CAST(x AS <data type> ))  
          //Returns <data type> as the type name.   If the type is DECIMAL, the type also includes precision and scale, for example:  
 
          DECIMAL(6, 3)   **modeOf()**   Returns the cardinality (mode) of the column as "NOT NULL", "NULLABLE", or "ARRAY". Drill data types include a cardinality, for example `Optional Int` or `Required VarChar`.  

drillTypeOf()
Similar to typeOf(), but returns the internal Drill names even if the value is NULL.
_____

Usage Notes

The data type functions are useful for data conversions. For example, if you know a column value is Nullable Int, you can assume that the data type is one that Drill derived. You can then merge the Drill-derived data type with the data type from another file that has actual values.


Usage Examples

The follow examples show you how you can use the data type functions:

Example 1
This example shows the Drill internal type, nullable int, for a missing column:

   SELECT sqlTypeOf(a) AS a_type, modeOf(a) AS a_mode FROM `json/all-null.json`;
   
   |----------|-----------|
   |  a_type  |  a_mode   |
   |----------|-----------|
   | INTEGER  | NULLABLE  |
   |----------|-----------|

Example 2
This example shows arrays (repeated) types:

   SELECT sqlTypeOf(columns) as col_type, modeOf(columns) as col_mode
   FROM `csv/cust.csv`;
   
   |--------------------|-----------|
   |      col_type      | col_mode  |
   |--------------------|-----------|
   | CHARACTER VARYING  | ARRAY     |
   |--------------------|-----------|
   Example 3: This example shows non-null types:
   SELECT sqlTypeOf(`name`) AS name_type, 
   modeOf(`name`) AS name_mode FROM `csvh/cust.csvh`;
   
   |--------------------|------------|
   |     name_type      | name_mode  |
   |--------------------|------------|
   | CHARACTER VARYING  | NOT NULL   |
   |--------------------|------------|