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%'
);

AlteryxDynamicSQL.xlsx

CharlieS

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/24946 

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. 

20200204-QueryBuild.yxmd