Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Getting Temp Table In-DB without Data Stream In and Security Permissions

Meteoroid

One of the problems that I've faced is getting a small temporary table from my workflow to In-DB so I can use it with my In-DB tools, particularly when there are permission restrictions on creating temp tables. To address this problem, I've created a macro that uses a SQL Values statement to generate a table from the query which I can use down stream. Currently the macro returns all columns as string columns so numeric columns have to be cast after the output. Also, one disadvantage is that the dynamic input query I generate is limited by ~8000 character limit for a v_string, so it can only be a relatively small table. 

 

Is there any better way to do this?

Is there any ways to improve the macro?

 

Highlighted
Alteryx
Alteryx

Hi @mhumpher

 

That is a nice workaround for an issue users like you run into with the in-db tools! 

 

The field info tool also outputs Type and Size, you could use that information to create the CAST statements in your select. Something like this: 

 

8-14-2017 4-51-49 PM.png

 

Are you running into the character limit with the dynamic input in-db? The formula tool should not be limiting the length of your query to only 8000 characters. 

 

Depending on your database, you might run into limitations on the database side. E.g. MS SQL server limits these types of queries to 1000 rows (see here for more info) 

 

 

Henriette Haigh
Principal Support Engineer -- Knowledge Management Coach
Alteryx, Inc.

Highlighted
Meteoroid

Thanks! Goes to show I should have tested first. I had thought that since strings were limited to ~8000 characters per the Alteryx reference that I would be limited on the size of the SQL statement I could generate as a string. Is it only the String and WString that have the limitation and the V_String and V_WString do not?

 

I'm going to tweak the macro a bit more than put up a final version here.

Highlighted
Alteryx
Alteryx

String and WString are fixed length fields, which means that the text contained in the field will be padded to match the field length. If you have one character in a 100 length field, you will end up with 99 spaces. If you set them to a large field size, you are potentially carrying a lot of blank spaces around with your data. 

V_String and V_WString are variable length, meaning that the length of the string is not defined by the field size but but by the size of the string contained within the field. For those fields, you can think of the field size as the maximum allowed length of the string. 

Variable length strings are limited by the total record size allowed in Alteryx which is 2GB. This translates into 1073741823 characters for V_WString (accommodates Unicode) and 2147483647 for V_String (only Latin-1 characters). If you have a string that is actually 2147483647 characters long, you can't have any other columns in  your record (which doesn't happen very often).

 

Thanks again for sharing the macro with us! Other users run into the same issue so this could be helpful for them as well!  

Henriette Haigh
Principal Support Engineer -- Knowledge Management Coach
Alteryx, Inc.

Highlighted
Meteoroid

@HerietteH

 

I've included an updated macro here. A caveat is that it was designed for use with Impala and so there seem to be some conventions with using the Values Clause there, but the concept is hopefully transferable.

 

Also, I don't know if it is a difference between Impala SQL and T-SQL, but the Values clause isn't limited to 1000 rows (I test on creating two columns with 10,000 rows). In the above link, it seems to relate to using an Insert with a Values clause whereas I'm doing a Select on a Values clause. 

 

Thanks again!

Labels