Dynamically create query reading from Excel input

[lasribeiro]

Hi,

I want to build a Hive query based on some parameters, qich are in an Excel file - with some sheets. These parameters are changed every week.

How can I make this dynamic query?

I´m attaching an example of this Excel parameters file and the resultant query would be like this:

SELECT *
FROM SOME_HIVE_TABLE
where COLUMN_DATE BETWEEN '2016-01-01' AND '2019-06-30'
AND UPPER(coalesce(COLUMN_SOME_DESCRIBE,'')) NOT like '%RATE %'
AND UPPER(TRIM(COLUMN_SOME_TYPE)) <> 'TYPE_A'
AND (
CAST(COLUMN_CODE AS BIGINT) IN (123, 456)
OR UPPER(COLUMN_SOME_DESCRIBE) LIKE '%FIRST TEXT%'
OR UPPER(COLUMN_SOME_DESCRIBE) LIKE '%SECOND TEXT%'
);

[CharlieS · Accepted solution]

This sounds like a good use case for the Dynamic Input tool:

https://help.alteryx.com/2018.2/DynamicInput.htm 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Tool-Mastery-Dynamic-Input/ta-p/249... 

This tool allows you to send fields from other tools in the workflow (like Input from Excel) and use those fields to update an existing query by replacing where clause arguments, or specific strings. 

One thing to note is that this will submit queries based on each record input. To have all of theses fields updated in one query, you'll need to have this prepared in one record. Also, if you have a variable number of arguments, you might need to write the entire query before and replace it all at once. 

EDIT: The Dynamic Input tool will only be useful if your queries will always return the same fields. If the output fields will change, a batch macro with an input tool updated by a Control Parameter is more forgiving of field schema changes. 

[demand_james]

Hi -

I've done a lot of these dynamic type queries.   They are a little tricky especially around the quoting - but real powerful.    Tricks to remember - you can create "Variable"  by doing '"'+Variable+'"'  (note single quote enclosing double quote).    

Some tools:   Dynamic In-DB Input.      This little gem accepts a SQL query and an in-db connection - enabling you to run a query in another system and get the result set back into your workflow.   Just plug the name of your in-db connection as one input and the query text as another and output will be the result set.    

then its just a question of manipulating your variables and using a formula to build it out.  

Attached is a sample workflow.    If running a hive query in db back to alteryx isnt what you are after -  I've also had a lot of success using a command line utility and using alteryx to output SQL to a text file and using a run command to execute that.    Let me know if you want to see some examples there.

[lasribeiro]

Thank you so much, @CharlieS!

It fits my case almost perfectly!