Frequency tables on large teradata table

Dear Alteryx community,

I am trying to get frequencies for 200+ fields in a teradata table that has 100 million records. Is it possible? Because I picked one field and it seemes running very slow.

Is there any other solutions? Any suggestions are welcome!

Hanna

Optimization

Accepted answers

david_fetters

Ohhhhh, ok, i think we've found it.

When your SQL query is returning results, it is typing them appropriately. So the [Value] field that contains all of your unique values is numerical for the first 6 fields, then string for your seventh. This is the schema change it's picking up.

Here's what I would do:

1) Create a select node in between your data input tool and your formula tool. Change the type and size of the [Value] field to "V_String" and "200" (large enough to fit anything that could come pouring out of the DB, hopefully).

Alternatively, you could create a new field using your Formula tool, call that field "CleanValue", set type to "V_String", and size to "200", then (using whatever name you have for the Value field) set the expression to: ToString( [Value] ). Then you would just need to deselect the original value field so that it doesn't get output.

The second approach uses an explicit conversion, while the first uses an implicit conversion. Not sure if there's a difference in your use case, but worth noting.

Give this a shot and let me know! Come on, no whammies...

EDIT: Im not sure of the syntax for a Teradata DB, but in most SQL versions, you can do this type of conversion in your SQL query. E.g., in MS SQL I could use the command CAST and write something like:

SELECT CAST( [Value] as VARCHAR(100)) as [UniqueValues],
   Count(*) as [RecordCount]
FROM MyDatabase
GROUP BY [Value]

All comments

david_fetters

Hanna,

That's likely to take a minute or two to run depending on your specs!

With very large datasets, I find that using the Summarize tool's Group By and Count functions seems to have much less overhead than the Frequency tool. You'll need to iterate column by column over your table to get it to work, but I think comparatively it will be much faster (on the processing side, not necessarily the IO side).

You might also consider writing a batch macro that executes the appropriate SQL query inside the database and then captures the results. Essentially, just run a query like:

SELECT [Field1] as FieldValue, COUNT(*) as RecordCount
FROM theDatabase
GROUP BY [Field1]

You can then have the batch macro replace [Field1] with the appropriate field names, and just add a column inside your macro with the name of the field you're counting. Your results then get spit out as a tall table of the form (FieldName,FieldValue,RecordCount)

While Alteryx is pretty freaking amazing, there's a cost to moving data from a database to the client, and that cost can sometimes exceed the advantages of handling your manipulations inside Alteryx. 100 million records is a decent amount (depending on specs) and unless it was all byte data I don't think i'd want to move it out of the database to do a quick group and count IMHO.

hanna

Thank you very much for your reply, David.

It took 40 minutes to run 9 columns. And I need to work on over 200 columns. I will try both of your suggestions and see which is better. I will let you know my findings.

Thanks again.

Hanna

aahaseebkhan

Hi David,

I've large datasets in csv files, 200M records and over 250 cols. The frequency tool takes forever to get the counts, does the query solution work against csv inputs?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703