Calculate statistics after write in DB

[saubert]

Statistics are tools used by a lot of DB to improve speed of queries (Hive, Vertica, etc...). It may be interesting to have an option on the write in db or data stream in to calculate the statistics. (something like a check box for )

Example on Hive : analyse {table} comute statistics; analyse {table} compute statistics for columns;

[SeanAdams]

Hey @saubert

I think you may be able to do this via a post-execution SQL in normal input/output tools, no?

https://docs.microsoft.com/en-us/sql/t-sql/statements/update-statistics-transact-sql?view=sql-server...

... but then you don't have a post SQL option on an IN-DB write tool.

As an aside - I would be cautious about doing synchronous statistics updates - on large tables this can take a long time and if you make this a serialised part of your output job that can lead to large delays in completing your canvas.     What may be better is to investigate how to schedule these kinds of jobs asynchronously on your server (on SQL Server, this is done through the SQL agent, not sure about Hive) and then you can still get the benefit of updated stats; but not the overhead on a write job in a canvas.

[saubert]

Hello @SeanAdams,

I have developped a macro to do that, it takes the same classic alias that I use for the indb connection and then an output data with a post (or pre?) sql.

I did the same thing to create view...

[Tanya]

 Hello @saubert,

Could you please provide some insights on your macro? I need to add 

compute stats <table-name>;

after in-db writing to Hadoop table <table-name>.

Thank you.

[stevenwaddon]

I have the same issue as tanya.

were you able to resolve this. the feedback from my cloudera admin team is that a lot of my queries are taking up too much memory and are not fully optimised, and one of the reasons is because there are no stats against the tables we are creating in alteryx.

[saubert]

@stevenwaddonas I mentioned previously, I wrote a macro for that. But it is suboptimal and I cannot retrieve the medata in Alteryx after the macro.

[saubert]

@ARich: Thanks. It would change a lot for us here.

[saubert]

@stevenwaddon@Tanya

Here the screenshots of my macros
The first is to generate the analyse stat query, the second to send the query
indb macro

macro that sends any query to a database

[Tanya]

For my needs I was able to run a simple workflow: 

[saubert]

Hello @Tanya .

I'm afraid you don't get it. The idea is about in db workflow, that means in database. And that's not the case of your workflow. Of course, it's easy for a standard workflow since you have the pre/post sql option. As you may have understood on my previous post, it's what I use in my macro. But you can't process billions of rows in minute with standard workflow/

[Tanya]

I totally got it!

Your solution is a sophisticated one and I will look into adapting it. Mine is a simple workaround that worked in my case (Hadoop HDFS, 6M rows, run time 40 seconds). The stats are computed after updating the data in-db (previous workflow), and before in-db data consumption (next workflow). It's not an elegant solution, but it takes 5 seconds to create and it does the job.

I hope Alteryx will create a standard solution that can be used without going into macros.