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

Options
Furaly
Furaly Registered Posts: 3 ✭✭✭

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,

Best Answer

  • Sarina
    Sarina Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer Posts: 315 Dataiker
    Answer ✓
    Options

    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

Answers

  • Sarina
    Sarina Dataiker, Dataiku DSS Core Designer, Dataiku DSS Adv Designer Posts: 315 Dataiker
    edited July 17
    Options

    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']

    Screen Shot 2021-11-12 at 3.48.33 PM.png

    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
    Furaly Registered Posts: 3 ✭✭✭
    Options

    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
    Furaly Registered Posts: 3 ✭✭✭
    Options

    Thanks @SarinaS
    It work well !

Setup Info
    Tags
      Help me…