Alternative to INPUT and IN DB Tools

[amirsemsar]

There are two core ways an end user of Alteryx can interact with a relational database.

What if I wanted to create a macro or a direct connection with the Python tool?

from snowflake.sqlalchemy import URL
from sqlalchemy import create_engine
from sqlalchemy.orm import Session

username = 'LOGIN'
password = 'PASSWORD'

account = 'XXXXXX'
region = 'xxxxxx'
warehouse = 'meowmeow'
database = 'kittykat'
schema ='catnip'

'''
Create an engine
'''
engine = create_engine(URL(
    account = account
    , user = username
    , password = password
    , region = region
    , warehouse = warehouse
    , database = database
    , schema = schema
    )
)

with engine.connect() as connection:
    connection.execute('call meowmeow.kittykat.STORPROCONE()')

My question is can I use DCM to hide the creds and call them in the workflow for python. If so how?

[TheOC]

Hi @amirsemsar 
I don't believe you can pass DCM connections to a Python tool without getting fancy beyond my knowledge with the SDK.

Your best route would be to create a macro/tool that uses text input tools for the users details. This can simply be their username and password, and passwords can be masked within the setup of the text box tool:

You can connect these interface tools directly to the Python tool and have them change parts of the code via action tools. If you are new to creating macros and the interface tools, i'd recommend the help documentation:
https://help.alteryx.com/20223/designer/understanding-interface-tools
and the interactive lesson:
https://community.alteryx.com/t5/Interactive-Lessons/Macro-Interface-Elements/ta-p/657920 

Kind Regards,

Owen

[apathetichell]

What's your use-case here? I mean beyond creating billable hours - I don't really see why you'd use a python bespoke solution vs the Snowflake ODBC....

And also - it would defeat the purpose of the DCM if you could pass the credentials out of the DCM for use inside of workflow tools - no?

[amirsemsar]

I am not a consultant and I have no billable hours. I need to call a stored procedure. I can not do that in Snowflake.

How would you propose I do it? 

[amirsemsar]

This was considered. I think this may be the only way to carry this out. I was hoping DCM would offer some opportunity.

[apathetichell]

For stored procedures - I do not believe the either Alteyrx (or say Tableau) currently support them in Snowflake. Could this change in 6-12 months? yes. But currently there is no support. I'd posit that Snowflake has really started to push Stored Procedures fairly recently so there is some hope that this could be implemented but I've played around with this a bit to see if I could get a work around in place and I haven't been able to get it to work using standard tools. Having said that - you're looking to both combine Alteryx's Snowflake authentication (either in DCM or in standard Designer) with Python's Snowflake package. I do not believe that would work. If your stored procedure is key - this may not be ideal for your Alteryx/Snowflake use-case. 

[amirsemsar]

I am looking to gather information on how this can be done if Alteryx can not do it. I was told however by a senior product manager that this could be done on some level. My other problem is that there is a lot of mystery around this DCM layer. One way or another a workflow will run and a stored procedure will be called. how that takes place is my exploration here. 

However if we are looking to surpass the limitations of Alteryx be it user defined variables, stored procedures or just running SQL from one person to another this alternative can broaden the ability for the platform to carry out data ingestion or manipulation above and beyond the scope it currently can. This also would require no investment by Alteryx. I do not think this is a waste of my time or billable hours as you facetiously implied.