Submit your inspiring success story or innovative use case to the 2022 Dataiku Frontrunner Awards! ENTER YOUR SUBMISSION

Changing connections for SQL Query datasets

importthepandas
Level 3
Changing connections for SQL Query datasets

We are migrating our data warehouse currently and are furiously changing connections, branching flows and validating back to old sources, etc. One thing we've encountered, specifically with Teradata connections, is that the "change connection" capability doesn't work on SQL query datasets. We're trying to follow changing connections guidelines as mentioned here: https://knowledge.dataiku.com/latest/kb/setup-admin/remapping/_shared/connection-remapping.html

 

General error is as follows:

ERR_DATASET_ACTION_NOT_SUPPORTED

Action not supported

Cannot change the connection for any of the specified items

 

Managed datasets work like a charm, though! Am I missing something or is this not supported for SQL query datasets/Teradata specific SQL query datasets?


Operating system used: Ubuntu 18

0 Kudos
6 Replies
importthepandas
Level 3
Author

Just to note: I've used both the GUI and Python API to attempt to change connections. SQL query dataset is all ANSI / standard SQL so attempting to max laziness here!

0 Kudos
gnaldi62
Neuron
Neuron

Hi,

  that's strange. We're changing SQL connection details almost every day and don't find suich problems. Just to be sure, I attached here the steps we're following (with images):

S1) we open the flow and select "connections";

S2) we then select the specific connection we'd like to remap;

S3/S4) then we select "Change connection" from the right menu;

S5/S6) we select the new connection (it should be created, obviously); we also check the "Reuse connection settings...".

S_1.jpgS_2.jpgS_3.jpgS_4.jpgS_5.jpgS_6.jpg

Marlan
Neuron
Neuron

Hi @importthepandas,

We are also in the midst of migrating our data warehouse (Netezza to Snowflake). Sounds like you have already tried changing connections using the API. Nonetheless I thought I would include the code we are using to change connections in case anything there is helpful.

I also compared the settings for a SQL query dataset and a regular managed dataset and it looks like the logic to change connections for regular datasets would work for SQL query datasets. This was for Netezza; maybe there is something unique to Teradata SQL query datasets.  

In the included Python script, the function change_dataset_connections is passed a handle to a project (client.get_project('PROJKEY').  We also use Exec SQL steps in scenarios often and have to change connections there as well. Would like change connections for SQL Notebooks but no way to do that via the API.

Marlan

def swap_connection(connection):
    if connection == 'NZ_CONN1':
        return 'SF_CONN2'
    elif connection == 'NZ_CONNA':
        return 'SF_CONNB'
    else:
        return'SF' + connection[2:999]

def change_dataset_connections(project_obj, save=True):
    
    for ds in project_obj.list_datasets(as_type='objects'):
        s = ds.get_settings()
        raw = s.get_raw()


        if raw['type'] == 'Netezza' and raw['params']['connection'][0:2] == 'NZ':

            old_connection = raw['params']['connection']
            
            raw['type'] = 'Snowflake'
            raw['params']['connection'] = swap_connection(raw['params']['connection'])
            if not raw['managed']:
                raw['params']['schema'] = 'ADMIN' # snowflake requires a fully specified table spec

            if save:
                s.save()

            print('{}: changed connection from {} to {}'.format(ds.dataset_name, old_connection, raw['params']['connection']))

        else:
            pass
            #print('{}: skipped - does not use a Netezza NZ prefixed connection'.format(ds.dataset_name))
        
    return

 

 

importthepandas
Level 3
Author

thank you both - will give it one more shot 🙂

0 Kudos
importthepandas
Level 3
Author

just following up - we got it done via the python API. thanks again!

CoreyS
Community Manager
Community Manager

Thank you for sharing your solutuion @importthepandas

Looking for more resources to help you use Dataiku effectively and upskill your knowledge? Check out these great resources: Dataiku Academy | Documentation | Knowledge Base

A reply answered your question? Mark as ‘Accepted Solution’ to help others like you!
0 Kudos