Alteryx Designer Desktop Ideas

Hello all,

As of today, you must set which database (e.g. : Snowflake, Vertica...) you connect to in your in db connection alias. This is fine but I think we should be able to also define the version, the release of the database. There are a lot of new features in database that Alteryx could use, improving User Experience, performance and security. (e.g. : in Hive 3.0, there is a catalog that could be used in Visual Query Builder instead of querying slowly each schema)

I think of a menu with the following choices :
-default (legacy) and precision of the Alteryx default version for the db
-autodetect (with a query launched every time you run the workflow when it's possible). if upper than last supported version, warning message and run with the last supported version settings.
-manual setting a release (to avoid to launch the version query every time). The choices would be every supported alteryx version.

Best regards,

Simon

Hello all,

Big picture : on Hadoop, a table can be

-internal (it's managed by Hive or Impala, and act like any other database)
-external (it's managed by hadoop, can be shared among the different hadoop db such as hive and impala and you can't delete it by default when dropping the table

for info, about suppression on external table :

https://docs.cloudera.com/HDPDocuments/HDP3/HDP-3.1.4/using-hiveql/content/hive_drop_external_table_...

Alteryx only creates internal tables while it would be nice to have the ability to create external tables that we can query with several tools (Hive, Impala, etc).

It must be implemented

-by default for connection
-by tool if we want to override the default

Best regards,

Simon

Hello,

As of today, we can't choose exactly the file format for Hadoop when writing/creating a table. There are several file format, each wih its specificity.

Therefore I suggest the ability to choose this file format :

-by default on connection (in-db connection or in-memory alias)

-ability to choose the format for the writing tool itself.

Best regards,

Simon

Hello all,

We all love pretty much the in-memory multi-row formula tool. Easy to use, etc. However, the indb counterpart does not exist.

I see that as a wizard that would generate windowing functions like LEAD or LAG
https://mode.com/sql-tutorial/sql-window-functions/

Best regards,

Simon

Sometimes I need to connect to the data in my Database after doing some filtering and modeling with CTEs. To ensure that the connection runs quicker than by using the regular input tool, I would like to use the in DB tool. But is doesn't working because the in DB input tool doesn't support CTEs. CTEs are helpful for everyday life and it would be terribly tedious to replicate all my SQL logic into Alteryx additionally to what I'm already doing inside the tool. 

I found a lot of people having the same issue, it would be great if we can have that feature added to the tool. 

Please add support for Databricks' Unity Catalog

Currently, when selecting a Databricks-connection in the “Connect In-DB”-tool, and opening the “Query Builder”, only tables in the catalog named “hive_metastore” are listed. That is, Alteryx submits the following SQL query to Databricks:

Listing tables 'catalog : hive\_metastore, schemaPattern : %, tableTypes : null, tableName : %'

However, with Unity Catalog in Databricks the namespace is three-tier and there may be multiple catalogs (and not just the "hive_metastore" catalog), see https://docs.microsoft.com/en-gb/azure/databricks/lakehouse/data-objects#--what-is-a-catalog

I reached out to Alteryx support, which replied that you currently have a feature request for implementing this change (ID TDCB-4056) and they furthermore suggested that I post here.

Thanks in advance.

Currently the Databricks in-database connector allows for the following when writing to the database

1. Append Existing
2. Overwrite Table (Drop)
3. Create New Table
4. Create Temporary Table

This request is to add a 5th option that would execute

• Create or Replace Table

Why is this important?

• Create or Replace is similar to the Overwrite Table (Drop) in that it fully replaces the existing table however, the key differences are
  • Drop table completely removes the table and it's data from Databricks
    • Any users or processes connected to that table live will fail during the writing process
    • No history is maintained on the table, a key feature of the Databricks Delta Lake
  • Create or Replace does not remove the table
    • Any users or processes connected to that table live will not fail as the table is not dropped
    • History is maintained for table versions which is a key feature of Databricks Delta Lake

While this request was specific to testing on Azure Databricks the documentation for Azure and AWS for Databricks both recommend using "Replace" instead of "Drop" and "Create" for Delta tables in Databricks. 

Alteryx really needs to show a results window for the InDB processes.  It is like we are creating blindly without it.  Work arounds are too much of a hassle.

We really need a block until done to process multiple calculations inDB without causing errors. I have heard that there is a Control Container potentially on the road map.  That needs to happen ASAP!!!!

Hi all,
Something really interesting I found - and never knew about, is there are actually in-DB predictive tools. You can find these by having a connect-indb tool on the canvas and dragging on one of the many predictive tools.

For instance:
boosted model dragged on empty campus:

Boosted model tool deleted, connect in-db tool added to the canvas:

Boosted Model dragged onto the canvas the exact same:

This is awesome! I have no idea how these tools work, I have only just found out they are a thing. Are we able to unhide these? I actually thought I had fallen into an Alteryx Designer bug, however it appears to be much more of a feature.

Sadly these tools are currently not searchable for, and do not show up under the in-DB section. However, I believe these need to be more accessible and well documented for users to find.

Cheers,
TheOC

For in-DB use, please provide a Data Cleansing Tool.

Hello,


As of today, when you connect o, a database, you go through a batch of queries to retrieve which database it is ( cf https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Smart-Visual-Query-Builder-for-in-db-less-te... where I suggest a solution to speed up the process) and then, Alteryx queries the metadata. In order to get the column in each table, Alteryx use a SHOW TABLES and then loop on each table. This is really slow. 

However, since Hive 3.0, an information_schema with the list of columns for each  table is now available. I suggest to use the information_schema.columns instead of the time-consuming loop.

PS : I don't know if it's linked to the Active Query Builder, the third-party tool behind the Visual Query Builder. In that case, it would be a good idea to update it as suggested here https://community.alteryx.com/t5/Alteryx-Designer-Ideas/Update-Query-Builder-component/idi-p/799086

Best regards,

Simon

Would be great if you could support Snowflake window functions within the In-DB Summarize tool

We're not too happy with the Gallery Data Connections not being available for the IN-DB data input tool but that will hopefully be a feature to be looked at in future product improvements; Let us know if there are reasons not having this feature already.

Thank you.

Hello,

I would like to allow my Gallery User to select the fields in my in database workflow, just like we can do in-memory. As of today, it's just impossible to do that.


Best regards,

Simon

Hi there Alteryx team,

When we load data from raw files into a SQL table - we use this pattern in almost every single loader because the "Update, insert if new" functionality is so slow; it cannot take advantage of SSVB; it does not do deletes; and it doesn't check for changes in the data so your history tables get polluted with updates that are not real updates.

This pattern below addresses these concerns as follows:

- You explicitly separate out the inserts by comparing to the current table; and use SSVB on the connection - thereby maximizing the speed

- The ones that don't exist - you delete, and allow the history table to keep the history.

- Finally - the rows that exist in both source and target are checked for data changes and only updated if one or more fields have changed.

Given how commonly we have to do this (on almost EVERY data pipe from files into our database) - could we look at making an Incremental Update tool in Alteryx to make this easier?    This is a common functionality in other ETL platforms, and this would be a great addition to Alteryx.

Hi Alteryx Team! 

Think an easy/useful tool enhancement would be to add a search bar on the "Tables" tab in the "Choose Table or Specify Query" popup when connecting to an In-DB source.

Current state, you have to scroll through all your tables to find the one you're looking for. Would be a HUGE help and time saver if I could just go in and search for a key word I know is in my table name.

Thanks! 

Hi, 

Standard In-DB connection configuration for PostgreSQL / Greenplum makes "Datastream-In" In-DB tool to load data line by line instead of using Bulk mode.

As a result, loading data in a In-DB stream is very slow.

Exemple

Connection configuration

Workflow

100 000 lines are sent to Greenplum using a "Datastream-in" In-DB tool.

This is a demo workflow, the In-DB stream could be more complex and not replaceable by an Output Data In-Memory.

Load time : 11 minutes.

It's slow and spam the database with insert for each lines.

However, there is a workaround.

We can configure a In-Memory connection using the bulk mode : 

And paste the connection string to the "write" tab of our In-DB Connection : 

Load time : 24 seconds.

It's fast as it uses the Bulk mode.

This workaround has been validated by Greenplum team but not by Alteryx support team.

Could you please support this workaround ? 

Tested on version 2021.3.3.63061

Hi Alteryx,

Can we get the R tools/models to work in database for SNOWFLAKE.

In-Database Overview | Alteryx Help

I understand that Snowflake currently doesn't support R through their UDFs yet; therefore, you might be waiting for them to add it.

I hear Python is coming soon, which is good & Java already available..

However, what about the ‘DPLYR’ package? https://db.rstudio.com/r-packages/dplyr/

My understanding is that this can translate the R code into SQL, so it can run in-DB?

https://docs.snowflake.com/en/release-notes/2015-09.html#snowflake-extension-for-dplyr-pre-productio...

Could this R code package be appended to the Alteryx R models? (maybe this isn’t possible, but wanted ask).

Many Thanks,

Chris