Changing connections for SQL Query datasets

importthepandas

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

importthepandas

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!

gnaldi62

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...".

Marlan

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

thank you both - will give it one more shot

importthepandas

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

CoreyS

Thank you for sharing your solutuion @importthepandas
!