How to get the SQL script from a dataset with a python command ?

Furaly

Hello everyone !

I want to find a way to get the SQL script of a dataset with a python command. As exemple I want to have the recipe data : CREATE TABLE xxxx ( xxx xxx ).

I use jupyter Dataiku notebook, so is it possible to get all these informations on a dataset with a python command ?

Thanks,

Sarina

Hi @Furaly
,

Thank you for the additional details!

In that case, since you are looking at an output dataset with a manually defined create statement, I think that the approach I outlined above should work for you to pull the SQL create statement. Does that work for you? If not, can you let me know what is missing?

Thanks,
Sarina

Sarina

Hi @Furaly
,

Are you referring to an input or an output SQL dataset? And what type of SQL database are you using?

If you are referring to an output dataset, it will also depend on if you are using a manually defined CREATE statement or an automatically defined statement.

For manually defined SQL statements, these are stored in the dataset settings, and can be pulled like so:

import dataiku

client = dataiku.api_client()
project = client.get_default_project()
dataset = project.get_dataset('YOUR_DATASET')
settings = dataset.get_settings()
settings.get_raw()['params']['customCreateStatement']

For output datasets with the table creation mode "Automatically generate", the CREATE statement is generated via code, so there isn't a CREATE statement stored in the dataset settings. It would take a fair amount of effort to replicate the auto-generation process, though I could pass along an example using Postgres if you were interested.

That said, maybe it would be helpful to know a little more about what you are trying to do, in case there is an easier method? Please feel free to share some details on your case.

Thanks,
Sarina

Furaly

Hello @SarinaS

Thanks for your response,

So first of all, I use PostgreSQL for the creation of my table. Then I'm referring to an output SQL dataset. I'm using a manually defined CREATE statement and my goal is to find wich column has been renamed and what was is original name.

Thanks for your time

Furaly

Thanks @SarinaS
It work well !