Problem

You are trying to cast a string type column to varchar but it isn’t working.

1. Create a simple Delta table, with one column as type string.

   %sql
   
   CREATE OR REPLACE TABLE delta_table1 (`col1` string)
   USING DELTA;

2. Use SHOW TABLE on the newly created table and it reports a string type.

   %sql
   
   SHOW CREATE TABLE delta_table1;

   
3. Create a second Delta table, based on the first, and convert the string type column into varchar.

   %sql
   
   CREATE OR REPLACE TABLE delta_varchar_table1
   USING DELTA
   AS
   SELECT cast(col1 AS VARCHAR(1000)) FROM delta_table1;

4. Use SHOW TABLE on the newly created table and it reports that the table got created, but the column is string type.

   %sql
   
   SHOW CREATE TABLE delta_varchar_table1;

   

Cause

The varchar type can only be used in table schema. It cannot be used in functions or operators.

Please review the Spark supported data types documentation for more information.

Solution

1. You cannot cast string to varchar, but you can create a varchar Delta table.

   %sql
   
   CREATE OR REPLACE TABLE delta_varchar_table2 (`col1` VARCHAR(1000))
   USING DELTA;

2. Use SHOW TABLE on the newly created table and it reports a varchartype.

   %sql
   
   SHOW CREATE TABLE delta_varchar_table2;

   
3. You can now create another varchar Delta table, based on the first, and it keeps the varchartype.

   %sql
   
   CREATE OR REPLACE TABLE delta_varchar_table3
   USING DELTA
   AS
   SELECT * FROM delta_varchar_table2;

4. Use SHOW TABLE on the newly created table and it reports a varchartype.

   %sql
   
   SHOW CREATE TABLE delta_varchar_table3;