topic Re: Random error related to dynamic variables in SQL - at least one column must be specified (shallowclone table) in Data Engineering

Random error related to dynamic variables in SQL - at least one column must be specified (shallowclone table)

lizou — Sat, 13 Nov 2021 16:20:01 GMT

Environment: Azure

A data transformation template that take s table name as variable. The variable is set in separate notebook %run ../_config

Shallow clone is used to generate a staging table with exact same columns of target table.

A random error occurs when the ETL is running concurrent notebooks at same time.

Error log attached. (May be related to cluster resources?)

Thanks in advance if any insight is provided...

-- Create a temp table for data transformation

drop table if exists ${v.source};

create table if not exists ${v.source}

shallow clone referrals_audit;

truncate table ${v.source}; -- shallow clone also keeps data reference and we should remove it

alter table ${v.source} add columns (Error string);

Error in SQL statement: AnalysisException: org.apache.hadoop.hive.ql.metadata.HiveException: at least one column must be specified for the table

Re: Random error related to dynamic variables in SQL - at least one column must be specified (shallowclone table)

Hubert-Dudek — Sun, 14 Nov 2021 16:53:27 GMT

A random error occurs when the ETL is running concurrent notebooks at same time.

Is both using the same variable value for ${v.source}?

If yes it can be just logic issue as one notebook is dropping temp table used by other notebook.

Re: Random error related to dynamic variables in SQL - at least one column must be specified (shallowclone table)

lizou — Mon, 15 Nov 2021 03:42:13 GMT

@Hubert Dudek thanks for replying

The variable is different in each notebook.

The error only happened once so far, and I may need to keep eye on it.

The idea to use a notebook template with dynamics table name, all merge statements

will be dynamically generated using table column list.

Attached is _config

notebook call _config to set variables that are different because variable 'table' will be different in each notebook.

Re: Random error related to dynamic variables in SQL - at least one column must be specified (shallowclone table)

lizou — Mon, 29 Nov 2021 04:24:53 GMT

The error happens again. I have removed the shallow clone in each notebook run.

My reasoning is there is an overhead of using shallow clone, and there is no need to

recreate the temp table structure each time as the table schema does not change much.

I have out shallow clone in a separate schema management task.

truncate table is the only line remains and the issue should not occur again.

Re: Random error related to dynamic variables in SQL - at least one column must be specified (shallowclone table)

lizou — Wed, 16 Feb 2022 14:04:05 GMT

resolved myself

Re: Random error related to dynamic variables in SQL - at least one column must be specified (shallowclone table)

Anonymous — Wed, 16 Feb 2022 16:10:48 GMT

@lizou - That's great! I'm so glad. Would you be happy to share your solution with us and then mark the answer as best? That will help others find the solution more quickly. 🙂

Re: Random error related to dynamic variables in SQL - at least one column must be specified (shallowclone table)

lizou — Sun, 17 Apr 2022 22:28:56 GMT

The solution is simple, don't include a shallow clone in a ETL that runs frequently. It is not necessary to change the table schema if there is no need to do so. Use a fixed table schema for a temp table in which data get truncated and reloaed.