In DB : Create View

[saubert]

From Wikipedia :

In a database, a view is the result set of a stored query on the data, which the database users can query just as they would in a persistent database collection object. This pre-established query command is kept in the database dictionary. Unlike ordinary base tables in a relational database, a view does not form part of the physical schema: as a result set, it is a virtual table computed or collated dynamically from data in the database when access to that view is requested. Changes applied to the data in a relevant underlying table are reflected in the data shown in subsequent invocations of the view. In some NoSQL databases, views are the only way to query data.

Views can provide advantages over tables:

    Views can represent a subset of the data contained in a table. Consequently, a view can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.
    Views can join and simplify multiple tables into a single virtual table.
    Views can act as aggregated tables, where the database engine aggregates data (sum, average, etc.) and presents the calculated results as part of the data.
    Views can hide the complexity of data. For example, a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table.
    Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents.
    Depending on the SQL engine used, views can provide extra security.

I would like to create a view instead of a table.

[dataprep]

Could be interesting in order to reduce the load of the DB cluster/Alteryx Server, especially for some largest tables barely consulted.

[jmaschke]

i agree this would be huge!!! rather then replicating data and scheduling refreshes, it allows the database to just keep your view updated.

i know i can do this in SSIS in SQL server, but find alteryx more user friendly for building an ETL. i was wondering if you can add a pre sql statement, but.......

[SeanAdams]

Hey @saubert - are you thinking of using inDB tools to actually create the view?

Reason for asking is that a view is a fixed object - if you create a view during the execution of an InDB canvas, then it'll break the next time you run it.

Additionally - if you drop and recreate the view each time, then you lose all the benefit of the query optimization engine being able to save the query plan.

What Alteryx is doing under the covers most of the time with inDB (especially for SQL Server) is using Common Table Expressions - these are like an on-demand view.    https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sq...

Alternatively - if you've created a view yourself on your underlying server (e.g. a filtered or a persisted view) then you can just use this as if it were a table in any In-DB query.

Let me know what you're thinking @saubert ?

[saubert]

Hello @SeanAdams

Positive 100% my idea is an in db tool that create view.

"Reason for asking is that a view is a fixed object - if you create a view during the execution of an InDB canvas, then it'll break the next time you run it."

I see views exactly the same way I see tables, except you don't store the data in. Of course the "create view" options will be different since you cannot append ;)

"Additionally - if you drop and recreate the view each time, then you lose all the benefit of the query optimization engine being able to save the query plan."

Not all the database work the same on that point and you don't have to overwrite everytime (we can imagine an option : overwrite only if query is different)

"What Alteryx is doing under the covers most of the time with inDB (especially for SQL Server) is using Common Table Expressions - these are like an on-demand view."
It depends a lot of the engine, actually because not on Hive or Vertica.

"Alternatively - if you've created a view yourself on your underlying server (e.g. a filtered or a persisted view) then you can just use this as if it were a table in any In-DB query."
That's already what I do... But I think it's easier to use Alteryx (even with my macro) to create the view than writing the SQL code myself, especially when I have seven tables, hundred fields, unions, join and group by, etc.

Thanks for challenging the idea ;)

[Atabarezz]

+1

[ARich]

Hi All,

Thanks for the feedback! Please continue to star and add comments as we evaluate.

Best,

Alex

[saubert]

Thanks Alex!

[saubert]

A drop view/table mau also be a good idea since we still don't have pre/post sql in db.

[Michal]

Any updates on this feature?