Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

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

mhumpher
6 - 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?

 

4 REPLIES 4
HenrietteH
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
Technical Product Manager - Data Connectors
Alteryx, Inc.

mhumpher
6 - 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.

HenrietteH
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
Technical Product Manager - Data Connectors
Alteryx, Inc.

mhumpher
6 - 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