Search code examples
sqlparquetimpala

Create Table As Select in Impala with NULL column

Context

I select some data from a table and initialize a dummy-column for a join with NULL.

SELECT
    col_a as a,
    NULL  as b
FROM dummy_table;

-- Output
+---+----+
| a | b  |
+---+----+
| 1 |NULL|
|...|NULL|
+---+----+

This works and produces a correct output.

Problem

I want to store that output in a table to do further processing.

CREATE TABLE IF NOT EXISTS temp_table STORED AS parquet AS
    SELECT
        col_a as a,
        NULL  as b
    FROM dummy_table;

While the first query runs, the second does not and fails with the following error:

AnalysisException: Unsupported type 'null_type' in column 'b' of table 'temp_table' CAUSED BY: TableLoadingException: Unsupported type 'null_type' in column 'b' of table 'temp_table'

Why does the table creation fail while the select-part is actually working?

Solution

  • Since you provide only NULL as value your DB can not determince which data type you want for that new table column.

    Just cast the NULL value to the type you want.