Automate dropping of local tables in SAP Datasphere with SQL

[sateixeira]

Hi,

I am quite new to SAP Datasphere.

I have some task chains scheduled for daily run.

I have a few local tables scheduled for daily deletion as well.

I'd like to have a way to automate this local tables deletion, in case that i have to manually run the task chains again and they need these local tables to be previously deleted. Its is a risk to delete them manually one by one, as i can miss something.

As i understand, i cannot have this being done within a Task Chain nor have such commands as "drop table" using SQL View as this is a data manipulation.

Is there a way to automate the dropping of tables with SQL inside Datasphere?

Or any other option?

Appreciate your help.

[XaviPolo · Accepted solution]

Tables created in DSP cannot be deleted using SQL commands. In fact, you cannot use DDL commands via  SQL on objects within the datasphere space.
You can do it in the OpenSQL schema if you enable it, but at that point, you will be using SAP HANA to manage them.

The only way to do it is through the DSP interface itself, or if you want to automate the process, the 'datasphere-cli' client allows you to operate on DSP objects and execute batch processes or automations using JavaScript, Python, or command line operations.

So, if you need to create/delete tables frequently, you might consider doing it from the OpenSQL schema (which must be enabled in the space administration, see Integrating Data via Database Users/Open SQL Schemas | SAP Help Portal), or alternatively, using the command-line client (@sap/datasphere-cli - npm)

Regards,

[sateixeira · Accepted solution]

Appreciate your quick response and help. Will try to explore both solutions.