Cannot view table SerDe properties

SHOW CREATE TABLE only returns the Apache Spark DDL. It does not show the SerDe properties.

Written by saritha.shivakumar

Last published at: July 1st, 2022

Problem

You are trying to view the SerDe properties on an Apache Hive table, but SHOW CREATE TABLE just returns the Apache Spark DDL. It does not show the SerDe properties.

For example, given this sample code:

%sql

SHOW CREATE TABLE <table-identifier>


You get a result that does not show the SerDe properties:
Result showing table without SerDe properties.

Cause

You are using Databricks Runtime 7.3 LTS or later, which uses Spark 3.0 and above.

The usage of SHOW CREATE TABLE changed with Spark 3.0.

Solution

To view a table's SerDe properties in Spark 3.0 and above, you need to add the option AS SERDE at the end of the SHOW CREATE TABLE command.

For example, given this sample code:

SHOW CREATE TABLE <table-identifier> AS SERDE

You get a result that shows the table's SerDe properties:
Result showing table's SerDe properties.