---

title: “Data Type Functions” slug: “Data Type Functions” parent: “SQL 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   |
   |--------------------|------------|